MySQL数据库（七）——SQL及索引的优化

explain关键字：分析SQL索引执行的过程。

（一）单表查询索引执行过程的分析及优化

通过一条数据获取结果，该过程使用了InnoDB辅助索引和主键索引

（二）单表查询 + 排序 / 分组

添加索引，依据查询条件。

Using filesort是对整个文件进行排序，效率较低应尽量避免。

——避免filesort且只命中关联数据行数（即不进行全表查询）：创建联合索引

userid、date

create index idx_userid_date(userid,date);

create index idx_userid_date(date,userid);

（*问题）一个表中有两个字段：a,b，创建联合索引，表是test

1）select * from test a = 3;(V)

2）select * from test b = 3;(X)

3）select * from test a = 3 and b = 3;(V)

4）select * from test b = 3 and a = 3;(V)

联合索引的顺序遵从从左到右的顺序，是先a后b，3）经过MySQL的查询分析器的优化，索引覆盖a和b。

根据where创建联合索引的依据：where在前，order在后。

（三）多表联合查询SQL

小表总是要进行全表搜索的，即使建立了索引也没用。小表决定查询的次数（行数），大表决定了查询的时间。

（四）慢查询日志

（1）慢查询日志的原理：

SQL蠕虫复制（这种生成数据方式同样适用于数据表中有主键的情况）。

（2）慢查询日志设置：

当语句执行时间较长是，通过日志的方式进行记录，这种方式就是慢查询的日志。

1）临时开启慢查询日志

set global slow_query_log = on;

注：如果想关闭慢查询日志，只需执行set global slow_query_log = off;即可。

2）临时设置慢查询时间临界点

查询时间高于这个临界点的都会被记录到慢查询日志中

set long_query_time = 1;//现在起所有执行时间超过1秒的SQL都将被记录到慢查询日志中。

3）设置慢查询存储的方式

set global log_outfile = file;//慢查询日志通过file体现的，默认为none。可以设置为table或file，如果是table，慢查询信息将会保存到MySQL库下的slow_log中。

4）查询慢查询日志的开启状态和慢查询日志存储的位置

show variables like '%quer%';

参数说明：

（1）slow_query_log（是否已经开启慢查询）

（2）slow_query_log_file（慢查询日志文件路径）

（3）long_query_time（超过多少秒的查询就写入慢查询日志）

（4）log_querirs_not_using_indexes（如果值设置为ON，则会记录所有没有利用索引的查询，性能优化时开启此项，平时不要开启）

（五）优化

1）使用explain查询SQL的执行计划。

参数分析：

（1）table：表示属于哪张数据表

（2）type：最重要的参数，表示连接使用了何种类型，从最好的到最差的连接类型为const, eq_reg, ref, range, index, ALL。

（3）possible_keys：显示可能应用在这张表中的索引，如果为null表示没有可能的索引。

（4）key：实际使用的索引。如果为null表示没有使用索引。

（5）key_len：使用的索引的长度，在不损失精确性的情况下长度越短越好。

（6）ref：表示索引的哪一列被使用了，如果可能的话，是一个常数。

（7）rows：MySQL认为必须检查的用来返回请求数据的行数。

2）count()和Max()的优化方法

如果经常用于count和max操作的字段，可以为其添加索引。

3）子查询的优化

通常情况下，需要将子查询有华为join查询。但在优化时要注意，关联键是否有一对多的关系。如果有时可能会出现重复数据。所以，如果存在一对多关系应该使用distinct进行限制。

例如：select t.id from t where t.id in(selset k.kid from k);------->select disrinct t.id from t join k on t.id = k.kid;

4）group的优化

5）limit的优化

（六）对索引进行优化

（1）选择合适的列建立索引

（2）索引优化SQL的方法

（3）索引维护的方法

（七）数据库结构优化

（1）选择合适的数据类型

（2）数据库表的范式化优化

（3）数据库表的反范式优化

（4）数据库表的垂直拆分

（5）数据库表的水平拆分