查询性能优化
如何设计最优的库表结构、如何建立最好的索引，这些对于高性能来说是必不可少的。但这些还不够—还需要合理的设计查询。如果查询写得很糟糕，即使库表结构再合理、索引再合适，也无法实现高性能。
什么是慢查询日志？
慢查询日志，顾名思义，就是查询花费大量时间的日志，是指MySQL记录所有执行超过long_query_time 参数设定的时间阈值的SQL语句的日志。该日志能为SQL语句的优化带来很好的帮助。
默认情况下，慢查询日志是关闭的，要使用慢查询日志功能，首先要开启慢查询日志功能。
慢查询基础-优化数据访问
查询性能低下最基本的原因是访问的数据太多。大部分性能低下的查询都可以通过减少访问的数据量的方式进行优化。
对于低效的查询，一般通过下面两个步骤来分析总是很有效:
- 确认应用程序是否在检索大量超过需要的数据。如：访问了太多行或者太多列；
- 确认MySQL服务器层是否在分析大量超过需要的数据行。
请求了不需要的数据
有些查询会请求超过实际需要的数据，然后这些多余的数据会被应用程序丢弃。这会给MySQL服务器带来额外的负担，并增加网络开销，另外也会消耗应用服务器的CPU和内存资源。
总是取出全部列
每次使用 SELECT* 的时候总是取出全部列，会让优化器无法完成索引覆盖扫描这类优化,还会为服务器带来额外的I/O、内存和CPU的消耗。
尤其是使用二级索引，使用 SELECT * 的方式会导致回表，导致性能低下。
如果发现查询需要扫描大量的数据但只返回少数的行，那么通常可以尝试下面的技巧去优化它:
- 使用索引覆盖扫描，把所有需要用的列都放到索引中，这样存储引擎无须回表获取对应行就可以返回结果了;
- 改变库表结构。例如使用单独的汇总表。
- 重写这个复杂的查询，让MySQL优化器能够以更优化的方式执行这个查询。
慢查询
慢查询配置
通过该语句查看慢查询日志是否开启：
show VARIABLES like 'slow_query_log';
通过该语句来设置慢查询日志 开启 or 关闭
-- 开启1，关闭0
set GLOBAL slow_query_log=1;
MySQL中可以设定一个阈值，将运行时间超过该值的所有SQL语句都记录到慢查询日志中。
通过该语句查看阈值：
-- 查看阈值
show VARIABLES like '%long_query_time%';
-- 设置阈值 单位（秒）
set global long_query_time=10;
同时对于运行的SQL语句没有使用索引，则MySQL数据库也可以将这条SQL语句记录到慢查询日志文件；
-- 查看是否开启
show VARIABLES like '%log_queries_not_using_indexes%';
-- 设置开启 or 关闭 ---开启1，关闭0（默认）
SET GLOBAL log_queries_not_using_indexes = 1;
查看慢查询SQL日志文件位置语句
show VARIABLES like '%slow_query_log_file%';
Explain执行计划
什么是执行计划
一条查询语句在经过MySQL查询优化器的各种基于成本和规则的优化会后生成一个所谓的执行计划，这个执行计划展示了接下来具体执行查询的方式，比如多表连接的顺序是什么，对于每个表采用什么访问方法来具体执行查询等等。
执行计划语法
在SQL查询的前面加上EXPLAIN关键字；
如： EXPLAIN select * from table1；
执行计划详解
id:在一个大的查询语句中每个SELECT关键字都对应一个唯一的id;
select_type：该关键字对应的是查询的类型；
table ：表名
partitions：匹配的分区信息；
type：针对单表的访问方法；
possible_keys：可能用到的索引；
key：实际上使用的索引；
key_len：实际使用到的索引长度；
ref：当使用索引列等值查询时，与索引列进行等值匹配的对象信息；
rows：预估的需要读取的记录条数；
filtered：某个表经过搜索条件过滤后剩余记录条数的百分比；
Extra：—些额外的信息；
type
执行计划的一条记录就代表着MySQL对某个表的执行查询时的访问方法/访问类型，其中的type列就表明了这个访问方法/访问类型是个什么东西，是较为重要的一个指标，结果值从最好到最坏依次是：
出现比较多的是system>const>eq_ref>ref>range>index>ALL
一般来说，得保证查询至少达到range级别，最好能达到ref。
system
当表中只有一条记录并且该表使用的存储引擎的统计数据是精确的，比如MyISAM、Memory，那么对该表的访问方法就是system。
const
就是当我们根据主键或者唯一二级索引列与常数进行等值匹配时，对单表的访问方法就是const。因为只匹配一行数据，所以很快。
EXPLAIN SELECT * FROM student s1 WHERE id = '3'
eq_ref
ref
当通过普通的二级索引列与常量进行等值匹配时来查询某个表，那么对该表的访问方法就可能是ref。
EXPLAIN SELECT * FROM student s1 WHERE `name` = "李四"
由于普通二级索引并不限制索引列值的唯一性，所以可能找到多条对应的记录，也就是说使用二级索引来执行查询的代价取决于等值匹配到的二级索引记录条数。如果匹配的记录较少，则回表的代价还是比较低的，所以MySQL可能选择使用索引而不是全表扫描的方式来执行查询。这种搜索条件为二级索引列与常数等值比较，采用二级索引来执行查询的访问方法称为：ref。
对于普通的二级索引来说，通过索引列进行等值比较后可能匹配到多条连续的记录，而不是像主键或者唯一二级索引那样最多只能匹配1条记录，所以这种ref访问方法比const要差些，但是在二级索引等值比较时匹配的记录数较少时的效率还是很高的（如果匹配的二级索引记录太多那么回表的成本就太大了）。
range
如果使用索引获取某些范围区间的记录，那么就可能使用到range访问方法，一般就是在where语句中出现了between、<、>、in等的查询。
这种范围扫描索引扫描比全表扫描要好，因为它只需要开始于索引的某一点，而结束语另一点，不用扫描全部索引。
EXPLAIN SELECT * FROM student WHERE id > "5";
EXPLAIN SELECT * FROM student WHERE id IN ("1", "3","5");
利用索引进行范围匹配的访问方法称之为：range。
索引可以是聚簇索引，也可以是二级索引；
index
当我们可以使用索引覆盖，但需要扫描全部的索引记录时，该表的访问方法就是index。
EXPLAIN SELECT `name` FROM student WHERE `subject` = "语文";
all
全表扫描，将遍历全表以找到匹配的行;
EXPLAIN SELECT * FROM student;
possible_keys与key
- possible_keys：表示在某个查询语句中，对某个表执行单表查询时可能用到的索引有哪些;
- key:表示实际用到的索引有哪些，如果为NULL，则没有使用索引。
key_len
key_len列表示当优化器决定使用某个索引执行查询时，该索引记录的最大长度;计算方式如下：
- 对于使用固定长度类型的索引列来说，它实际占用的存储空间的最大长度就是该固定值；
- 对于指定字符集的变长类型的索引列来说，如某个索引列的类型是VARCHAR(100)，使用的字符集是utf8，那么该列实际占用的最大存储空间就是100 x 3 = 300个字节；
- 如果该索引列可以存储NULL值，则key_len比不可以存储NULL值时多1个字节。
- 对于变长字段来说，都会有2个字节的空间来存储该变长列的实际长度。
rows
如果查询优化器决定使用全表扫描的方式对某个表执行查询时，执行计划的rows列就代表预计需要扫描的行数;
如果使用索引来执行查询时，执行计划的rows列就代表预计扫描的索引记录行数。
rows:表示查询语句在执行查询时，预计扫描的行数；
filtered
查询优化器预测有多少条记录满⾜其余的搜索条件
Extra
Extra列是用来说明一些额外信息的，我们可以通过这些额外信息来更准确的理解MySQL到底将如何执行给定的查询语句
查询优化器
高性能的索引使用策略
不在索引列上做任何操作
如使用表达式 和 函数。都是不能使用索引的都应该避免；
EXPLAIN SELECT * FROM order_exp WHERE id + 1 = 17;
EXPLAIN SELECT * from order_exp WHERE YEAR(insert_time)=YEAR(DATE_SUB(NOW(),INTERVAL 1 YEAR));
尽量使用全值匹配
建立了联合索引列后，如果我们的搜索条件中的列和索引列一致的话，这种情况就称为全值匹配；
当建立了联合索引列后，能在where条件中使用索引的尽量使用;
最佳左前缀法则
建立了联合索引列，如果搜索条件不够全值匹配。我们的搜索语句中也可以不用包含全部联合索引中的列，但要遵守最左前缀法则。查询从索引的最左前列开始并且不跳过索引中的列。
范围条件放最后
覆盖索引尽量用
索引条目通常远小于数据行大小，所以如果只需要读取索引，那 MySQL就会极大地减少数据访问量。覆盖索引对于I/O密集型的应用也有帮助，因为索引比数据更小,更容易全部放入内存中。
尽量使用覆盖索引(只访问索引的查询(索引列和查询列一致))，不是必要的情况下减少select*，除非是需要将表中的全部列检索后，进行缓存。
不等于要慎用
MySQL在使用不等于(!= 或者<>)的时候无法使用索引会导致全表扫描；
解释一下Extra中的Using where:
当我们使用全表扫描来执行对某个表的查询，并且该语句的WHERE子句中有针对该表的搜索条件时，在 Extra 列中会提示上述额外信息。
NULL / Not NULL 有影响
- is not null容易导致索引失效；
- is null则会区分被检索的列是否为null。 如果是null则会走ref类型的索引访问；如果不为null，也是全表扫描。
like查询谨慎使用
like以通配符开头(‘%abc…’)，MySQL索引失效会变成全表扫描的操作；
使用or关键字时要注意
使用索引扫描来做排序和分组
MySQL有两种方式可以生成有序的结果；通过排序操作 或者 按索引顺序扫描时；如果EXPLAIN出来的type列的值为“index”，则说明MySQL使用了索引扫描来做排序。
MySQL可以使用同一个索引既满足排序，又用于查找行。
只有当索引的列顺序和ORDER BY子句的顺序完全一致，并且所有列的排序方向（倒序或正序）都一样时，MySQL才能够使用索引来对结果做排序。如果查询需要关联多张表，则只有当0RDER BY子句引用的字段全部为第一个表时，才能使用索引做排序。
排序时ASC、DESC别混用
对于使用联合索引进行排序的场景，我们要求各个排序列的排序顺序是一致的，也就是要么各个列都是ASC规则排序，要么都是DESC规则排序。
尽可能按主键顺序插入行
如果写入主键是乱序的，InnoDB不得不频繁地做页分裂操作，以便为新的行分配空间。页分裂会导致移动大量数据，一次插入最少需要修改三个页而不是一个页。
所以使用InnoDB时应该尽可能地按主键顺序插入数据，并且尽可能地使用单调增加的聚簇键的值来插入新行。
优化Count查询
COUNT()是一个特殊的函数，有两种非常不同的作用:它可以统计某个列值的数量，也可以统计行数。
在统计列值时要求列值是非空的（不统计NULL)。
COUNT()的另一个作用是统计结果集的行数。常用的就是就是当我们使用COUNT(*)。实际上，它会忽略所有的列而直接统计所有的行数。
优化limit分页
在系统中需要进行分页操作的时候，通常会使用LIMIT加上偏移量的办法实现，同时加上合适的ORDER BY子句。
如果在偏移量非常大的时候，如：
select * from order_exp limit 10000,10;
这时MySQL需要查询10010条记录然后只返回最后10条，前面10 000条记录都将被抛弃，这样的代价非常高。
优化
会先查询翻页中需要的N条数据的主键值，然后根据主键值回表查询所需要的N条数据，在此过程中查询N条数据的主键id在索引中完成，所以效率会高一些。
EXPLAIN SELECT * FROM (select id from order_exp limit 10000,10) b,order_exp
a where a.id = b.id;
首先执行子查询中的order_exp表，根据主键做索引全表扫描，然后与a表通过id做主键关联查询，相比传统写法中的全表扫描效率会高一些。
关于NULL的特别说明
- 1、有的认为NULL值代表一个未确定的值，MySQL认为任何和NULL值做比较的表达式的值都为NULL；
所以每一个NULL值都是独一无二的。
- 2、有的认为其实NULL值在业务上就是代表没有，所有的NULL值和起来算一份；
- 3、有的认为这NULL完全没有意义，所以在统计数量时压根儿不能把它们算进来。
拓展：
在对统计索引列不重复值的数量时如何对待NULL值，MySQL专门提供了一个 innodb_stats_method 的系统变量;
这个系统变量有三个候选值：
- 1、nulls_equal：认为所有NULL值都是相等的【默认值】。
- 2、nulls_unequal：认为所有NULL值都是不相等的。
- 3、nulls_ignored：直接把NULL值忽略掉。
在MySQL5.7.22以后的版本，对这个innodb_stats_method的修改不起作用，MySQL把这个值在代码里写死为nulls_equal。
扫一扫
1156
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
