MySQL优化-常用方法

小型项目可能对数据库要求没有那么大，但是对于百万千万级以上数据，查询等数据库便会有显著下降，以下是个人项目中用到的方法（持续补充中...）:

1.创建数据库

（1）使用合适的数据类型。能用char就不用varchar，能用mediumint就不用bigint。减少不必要的空间开销。

（2）合理使用外键。

2.查询数据库

（1）使用join代替子查询。

（2）使用union代替临时表创建。

（3）查询需要的字段。

（4）减少比较操作，像like之类的。

3.索引[主键索引|唯一索引|普通索引|全文索引|空间索引]

合理创建索引

4.分区分表（瓶颈为单库容量）

概念

分区（图书的章节）就是把一张表的数据分成N个区块，在逻辑上看最终只是一张表，但底层是由N个物理区块组成的

分表（图书馆的图书）就是把一张表按一定的规则分解成N个具有独立存储空间的实体表。系统读写时需要根据定义好的规则得到对应的字表明，然后操作它。

分库（图书馆，未使用过不做详细介绍）一旦分表，一个库中的表会越来越多。

使用场景

（1）分区

一张表的查询速度已经慢到影响使用的时候。
sql经过优化
数据量大
表中的数据是分段的
对数据的操作往往只涉及一部分数据，而不是所有的数据

（2）分表

一张表的查询速度已经慢到影响使用的时候。
sql经过优化
数据量大
当频繁插入或者联合查询时，速度变慢

操作

（1）分区

新建表：

//range分区方式
drop table if exists staff;
create table staff(
  id int not null,
  fname varchar(30),
  lname varchar(30),
  hired date not null default '1970-01-01',
  separated date not null default '9999-12-31',
  job_code int not null default 0,
  store_id int not null default 0
)engine=myisam default charset=utf8
partition by range(year(separated))(
  partition p0 values less than (1991),
  partition p1 values less than (1996),
  partition p2 values less than (2001),
  partition p4 values less than MAXVALUE
);

//hash分区方式
drop table if exists staff;

create table staff(
  id int not null,
  fname varchar(30),
  lname varchar(30),
  hired date not null default '1970-01-01',
  separated date not null default '9999-12-31',
  job_code int not null default 0,
  store_id int not null default 0
)
partition by hash(store_id)
partitions 4;

less than MAXVALUE一定要注意添加，要不然超出范围将报错。

删除表：

alter table staff drop partition p0;

查询表：

select count(*) from staff where year(separated) = 200 group by store_id;
//自动查询p2分区

5.数据库引擎

6.读写分离(配置)

适用场景：读操作远远多于写操作

目标：有效减轻Master库的压力，又可以把用户查询数据的请求分发到不同的Slave库，从而保证系统的健壮性。

实现方式1：应用内部路由

不推荐！需要应用内切换数据源且实现高可用。个人也没有用过。

实现方式2：代理

个人用的是mysql-proxy代理中间件，可配置

缺点：增加了一层，网络消耗伴随增加

7.预处理

使用Redis/ElasticSearch

这是第三方组件完成的缓存机制

如何定位查询慢的语句：

利用数据库慢查询功能，设置步骤如下

（1）临时开启慢查询（永久开启得改/etc/my.cfg）

set gloabal slow_query_log=on(对应的关闭为off)

（2）设置时间长度，超过的语句将被记录

set long_query_time=5(单位秒)

（3）存储方式设为file

set log_output=file(默认为null，还支持table，数据将放在slow_log表中)

（4）查看存储位置

show variables like '%query%'

文件位置为slow_query_log_file

（5）拿到第4步的sql语句，执行explain+SQL语句

作用：

表的读取顺序
数据读取操作的操作类型
哪些索引可以使用
哪些索引被实际使用
表之间的引用
每张表有多少行被优化器查询

分析得到的数据：

信息	描述
id	查询的序号，包含一组数字，表示查询中执行select子句或操作表的顺序两种情况 id相同，执行顺序从上往下 id不同，id值越大，优先级越高，越先执行
select_type	查询类型，主要用于区别普通查询，联合查询，子查询等的复杂查询 1、simple ——简单的select查询，查询中不包含子查询或者UNION 2、primary ——查询中若包含任何复杂的子部分，最外层查询被标记 3、subquery——在select或where列表中包含了子查询 4、derived——在from列表中包含的子查询被标记为derived（衍生），MySQL会递归执行这些子查询，把结果放到临时表中 5、union——如果第二个select出现在UNION之后，则被标记为UNION，如果union包含在from子句的子查询中，外层select被标记为derived 6、union result:UNION 的结果
table	输出的行所引用的表
type	显示联结类型，显示查询使用了何种类型，按照从最佳到最坏类型排序 1、system：表中仅有一行（=系统表）这是const联结类型的一个特例。 2、const：表示通过索引一次就找到，const用于比较primary key或者unique索引。因为只匹配一行数据，所以如果将主键置于where列表中，mysql能将该查询转换为一个常量 3、eq_ref:唯一性索引扫描，对于每个索引键，表中只有一条记录与之匹配。常见于唯一索引或者主键扫描 4、ref:非唯一性索引扫描，返回匹配某个单独值的所有行，本质上也是一种索引访问，它返回所有匹配某个单独值的行，可能会找多个符合条件的行，属于查找和扫描的混合体 5、range:只检索给定范围的行，使用一个索引来选择行。key列显示使用了哪个索引，一般就是where语句中出现了between,in等范围的查询。这种范围扫描索引扫描比全表扫描要好，因为它开始于索引的某一个点，而结束另一个点，不用全表扫描 6、index:index 与all区别为index类型只遍历索引树。通常比all快，因为索引文件比数据文件小很多。 7、all：遍历全表以找到匹配的行注意:一般保证查询至少达到range级别，最好能达到ref。
possible_keys	指出MySQL能使用哪个索引在该表中找到行
key	显示MySQL实际决定使用的键(索引)。如果没有选择索引,键是NULL。查询中如果使用覆盖索引，则该索引和查询的select字段重叠。
key_len	表示索引中使用的字节数，该列计算查询中使用的索引的长度在不损失精度的情况下，长度越短越好。如果键是NULL,则长度为NULL。该字段显示为索引字段的最大可能长度，并非实际使用长度。
ref	显示索引的哪一列被使用了，如果有可能是一个常数，哪些列或常量被用于查询索引列上的值
rows	根据表统计信息以及索引选用情况，大致估算出找到所需的记录所需要读取的行数
Extra	包含不适合在其他列中显示，但是十分重要的额外信息 1、Using filesort：说明mysql会对数据适用一个外部的索引排序。而不是按照表内的索引顺序进行读取。MySQL中无法利用索引完成排序操作称为“文件排序” 2、Using temporary:使用了临时表保存中间结果，mysql在查询结果排序时使用临时表。常见于排序order by和分组查询group by。 3、Using index:表示相应的select操作用使用覆盖索引，避免访问了表的数据行。如果同时出现using where，表名索引被用来执行索引键值的查找；如果没有同时出现using where，表名索引用来读取数据而非执行查询动作。 4、Using where :表明使用where过滤 5、using join buffer:使用了连接缓存 6、impossible where:where子句的值总是false，不能用来获取任何元组 7、select tables optimized away：在没有group by子句的情况下，基于索引优化Min、max操作或者对于MyISAM存储引擎优化count（*），不必等到执行阶段再进行计算，查询执行计划生成的阶段即完成优化。 8、distinct：优化distinct操作，在找到第一匹配的元组后即停止找同样值的动作。

posted @ 2020-12-24 19:19 时间会有答案阅读(111) 评论(0) 收藏举报

刷新页面返回顶部