MySQL索引原理之慢查询等优化
索引一般查询使用，索引优化那就是查询的效率低了，需要通过调整索引来提供查询效率。那么怎么判断这个查询的效率低了是慢查询呢？MySQL判断一条语句是否为慢查询语句，主要依据SQL语句的执行时间，它把当前语句的执行时间跟 long_query_time 参数做比较，如果语句的执行时间 > long_query_time，就会把这条执行语句记录到慢查询日志里面。long_query_time 参数的默认值是 10s，该参数值可以根据自己的业务需要进行调整。
慢查询如何定位和优化呢？
一、慢查询定位
MySQL提供了慢查询日志以及相关命令和配置进行慢查询的管理。
1、开启慢查询日志
查看 MySQL 数据库是否开启了慢查询日志和慢查询日志文件的存储位置的命令：SHOW VARIABLES LIKE 'slow_query_log%'；
可以看到测试机器上慢查询未开启，通过如下命令开启慢查询日志：
SET global slow_query_log = ON;
SET global slow_query_log_file = 'XXX-slow.log';
SET global log_queries_not_using_indexes = ON;
SET long_query_time = 10;
其中，1）long_query_time：指定慢查询的阀值，单位秒。如果SQL执行时间超过阀值，就属于慢查询记录到日志文件中。
2）log_queries_not_using_indexes：表示会记录没有使用索引的查询SQL。前提是slow_query_log的值为ON，否则不会奏效。
2、查看慢查询日志
可以直接打开slow.log文件进行查看也可以通过命令mysqldumpslow查看。
1）通过打开文件查看
time：日志记录的时间
User@Host：执行的用户及主机
Query_time：执行的时间
Lock_time：锁表时间
Rows_sent：发送给请求方的记录数，结果数量
Rows_examined：语句扫描的记录条数
SET timestamp：语句执行的时间点
select....：执行的具体的SQL语句
2）通过mysqldumpslow查看
mysqldumpslow是MySQL提供的一个慢查询日志分析工具，可以通过该工具分析慢查询日志内容。通过which和whereis命令可以查看到该分析工具的具体路径：
具体使用：
因为未开启慢查询没有该文件，当然就无法读取了。启用慢日志后：
除了MySQL提供的工具mysqldumpslow，也可以使用第三方分析工具，比如pt-query-digest、mysqlsla等。
二、慢查询优化
通过分析定位了慢查询，接下来就该分析判断是否应用了索引了。SQL语句是否使用了索引，可根据SQL语句执行过程中有没有用到表的索引判断。通过 explain命令分析查看，检查结果中的 key 值，是否为NULL(具体可参考MySQL索引原理之索引分析 - 池塘里洗澡的鸭子 - 博客园 (cnblogs.com))。
查询是否使用索引，只是表示一个SQL语句的执行过程；而是否为慢查询，是由它执行的时间决定的，也就是说是否使用了索引和是否是慢查询两者之间没有必然的联系。我们在使用索引时，不要只关注是否起作用，应该关心索引是否减少了查询扫描的数据行数，如果扫描行数减少了，效率才会得到提升。对于一个大表，不止要创建索引，还要考虑索引过滤性，过滤性好，执行速度才会快。
慢查询原因总结：
全表扫描：explain分析type属性all
全索引扫描：explain分析type属性index
索引过滤性不好：靠索引字段选型、数据量和状态、表设计
频繁的回表查询开销：尽量少用select *，使用覆盖索引
找到问题了，如何具体优化呢？下面案例分析：
三、分页查询优化
补充分页查询优化，MySQL中一般的分页查询使用简单的 limit 子句就可以实现，其格式如下：SELECT * FROM 表名 LIMIT [offset,] rows；
第一个参数指定第一个返回记录行的偏移量，注意从0开始；
第二个参数指定返回记录行的最大数目；
如果只给定一个参数，它表示返回最大的记录行数目；
具体优化方案，如下：
第一步：利用覆盖索引优化
第二步：利用子查询优化
原因：使用了id做主键比较(id>=)，并且子查询使用了覆盖索引进行优化
总结索引的使用原则大致如下：
1、在大表上奖励索引才有意义(那这么定义大表呢?)。
2、在WHERE子句或是连接条件经常引用的列上建立索引。
3、索引的层次最好不要超过4层。
4、如果某属性长作为最大值和最小值等聚集函数的参数，那么考虑为该属性建立索引。
5、表的主键、外键必须有索引。
6、创建了主键和唯一约束后回自动创建唯一索引。
7、经常与其他表进行连接的表，在连接字段上建议建立索引。
8、经常出现在WHERE子句中的自动，特别是大表的字段，建议建立索引。
9、要索引的列经常被查询并只返回表中的行的总数的一小部分。
10、对于哪些查询中很少涉及的列、重复值比较多的列尽量不要建立索引。
11、经常出现在关键字order by/group by/distinct后面的自动，最好建立索引。
12、索引应该建在选择性高的字段上。
13、索引应该将在小字段上，对于大的文本自动甚至超长自动，不适合建索引。对于定义为CLOB、TEXT、IMAGE和BIT的数据类型的列亦不适合建立索引。
14、符合索引的建立需要进行仔细分析。正确选择符合索引中的前导列字段，一般是选择性较好的字段。
15、如果单字段查询很少甚至没有，那么可以建立符合索引；否则考虑但字段索引。
16、如果符合索引中包含的字段经常单独出现在WHERE子句中，那么分解为个单字段索引。
17、如果符合索引锁包含的字段超过3个，那么仔细考虑其必要性，考虑减少符合的字段。
18、如果既有单字段索引，又有这几个字段上的复合索引，那么一般可以删除复合索引。
19、频繁进行DML操作的表，不要建立太多的索引。
20、删除无用的索引，避免对执行计划造成负面影响。
更多索引实践可参考MySQL性能优化之SQL语句及索引优化 - 池塘里洗澡的鸭子 - 博客园 (cnblogs.com)。
