MySQL查询优化

慢查询

查询查询日志开关

show variables like '%slow_query_log%';

 开启慢查询

-- 0为关闭，1为开启
set global slow_query_log = 1;

慢查询时间阈值

-- 慢查询时间阈值，单位：秒
show variables like 'long_query_time';
-- 设置慢查询的时间为1秒
set global long_query_time=1;

慢查询日志输出格式

-- 慢查询日志数据格式，'FILE'-文件（默认）,'TABLE'-表
SHOW VARIABLES LIKE '%log_output%';

-- 设置慢查询输出格式为表
set global log_output = 'TABLE'

查询未走索引记录慢查询日志（可选）

-- 查询未走索引时记录到慢查询日志中
show variables like 'log_queries_not_using_indexes';

-- 开启查询未走索引时记录慢查询日志
set global log_queries_not_using_indexes = 1;

配置到文件中

上述配置都是通过global设置参数，如果mysql重启参数就要重新设置。可以将其设置到配置文件my.cnf中，重启自动生效

-- 编辑配置
vim /etc/my.cnf

-- 添加如下内容
slow_query_log =1
slow_query_log_file=/var/lib/mysql/ruyuan-slow.log

explain执行计划

一条查询sql的执行流程

1.如果是查询语句（select语句），首先会查询缓存是否已有相应结果，有则返回结果，无则进行下一步（如果不是查询语句，同样调到下一步）

2.解析查询，创建一个内部数据结构（解析树），这个解析树主要用来SQL语句的语义与语法解析；

3.优化：优化SQL语句，例如重写查询，决定表的读取顺序，以及选择需要的索引等。这一阶段用户是可以查询的，查询服务器优化器是如何进行优化的，便于用户重构查询和修改相关配置，达到最优化。这一阶段还涉及到存储引擎，优化器会询问存储引擎，比如某个操作的开销信息、是否对特定索引有查询优化等。

通过使用EXPLAIN关键字可以模拟优化器执行SQL查询语句，从而知道MySQL是如何处理你的SQL语句的。分析查询语句或是表结构的性能瓶颈，总的来说通过EXPLAIN我们可以：

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

重点关注的指标：type、extra

type

完整的连接类型比较多，效率逐渐变低
system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL

常用的typte值

system > const > eq_ref > ref > range > index > ALL

• system：不进行磁盘IO，查询系统表,仅仅返回一条数据。
• const：查找主键索引，最多返回1条或0条数据，属于精确查找。
• eq_ref：查找唯一性索引，返回数据最多一条， 属于精确查找。
• ref：查找非唯一性索引,返回匹配某一条件的多条数据，属于精确查找，数据返回可能是多条。

• range：查找某个索引的部分索引，只检索给定范围的行，属于范围查找。 比如: > 、 < 、in 、between。

• index：查找所有索引树,比ALL快一些，因为索引文件要比数据文件小。

• all：不使用任何索引，直接进行全表扫描。

extra

• Using filesort：MySQL中无法利用索引完成的排序操作称为  “文件排序”。
• Using index：表示直接访问索引就能够获取到所需要的数据（覆盖索引），不需要通过索引回表。
• Using index condition：搜索条件中虽然出现了索引列，但是有部分条件无法使用索引，会根据能用索引的条件先搜索一遍再匹配无法使用索引的条件。
• Using join buffer：使用了连接缓存，会显示join连接查询时，MySQL选择的查询算法。
• Using temporary：表示MySQL需要使用临时表来存储结果集，常见于排序和分组查询。
• Using where：意味着全表扫描或者在查找使用索引的情况下，但是还有查询条件不在索引字段当中。
   

 查询优化技巧

索引失效场景

• 不要再索引上进行运算（如，+、-、*、/）和函数（如：max()、str_to_date()）。
• 查询条件包含 or可能会导致索引失效。
• 隐式类型转换，会导致索引失效，例如 age 字段类型是 int，我们 where age = “1”，这样就会触发隐式类型转换
• like 通配符会导致索引失效，注意:”ABC%” 不会失效，会走 range 索引，”% ABC” 索引会失效。
• 联合索引，查询时的条件列不是联合索引中的第一个列，索引失效（不符合最左匹配）。
• 索引字段上使用!= 或者 < >、not in时，会导致索引失效。
• 索引字段上使用 is null， is not null，可能导致索引失效。
• 相 join 的两个表的字符编码不同，不能命中索引，会导致笛卡尔积的循环计算
• mysql 优化器觉得使用全表扫描要比使用索引快，则不使用索引。
• 联合索引进行排序时，要么各个列都是ASC规则排序，要么都是DESC规则排序。

优化分页查询

通常来说，较大的偏移量和返回较多的行数，都会导致分页查询效率降低。

select * from table limit 100000,1000;

优化的技巧就是，再where条件上增加过滤条件

增加条件过滤

select * from table where create_time > 'yyyy-MM-dd HH:mm:ss' limit 10000,10;

先用子查询得到主键id，再查询具体的字段

SELECT id, name FROM (select id from table limit 10000,10) b limit 10000,10;


SELECT id, name FROM (select id from table where create_time > 'yyyy-MM-dd HH:mm:ss' limit 10000,10) b limit 10000,10;


-- 主键如果是自增的，也可以使用主键进行范围过滤

SELECT id, name FROM (select id from table where id > 100 limit 10000,10) b limit 10000,10;

count(*)、count(1)、count(列名)区别

执行效果

• count(*) 包括了所有的列，在统计时 不会忽略列值为null的数据。
• count(1) 用1表示代码行，在统计时，不会忽略列值为null的数据。
• count(列名)在统计时，会忽略列值为空的数据，就是说某个字段的值为null时不统计。

执行效率上：

• InnoDB引擎：count（字段) < count(1) = count(*)，InnoDB通过遍历最小的可用二级索引来处理select count(*) 语句，除非索引或优化器提示指示优化器使用不同的索引。如果二级索引不存在，则通过扫描聚集索引来处理，InnoDB已同样的方式处理count(1)和count(*)。
• MyISAM引擎：count（字段) < count(1) <= count(*)，MyISAM存储了数据的准确行数，使用 count(*)会直接读取该行数， 只有当第一列定义为NOT NULL时，count（1），才会执行该操作，所以优先选择 count(*)。
• count(列名) 会遍历整个表，但不同的是，它会先获取列，然后判断是否为空，然后累加，因此count(列名)性能不如前两者。