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)数据库表的水平拆分