SQL优化常用的几种方式

MySQL运行机理

mysql 客户端/服务端通信阶段 -》查询缓存阶段-》查询优化处理阶段-》查询执行引擎阶段-》返回客户端阶段。

定位慢SQL：

- 业务驱动：根据业务反馈来确定哪些sql可能出现问题。

- 测试驱动：通过测试确定哪些sql出现问题。

- 慢查询日志：通过日志记录的方式查找执行效率慢的sql。

慢日志查询配置：

show variables like 'slow_query_log' //--查看是否开启慢日志保存set global slow_query_log = on //-- 打开慢日志set global slow_query_log_file = '/var/lib/mysql/gupaoedu-slow.log' //--慢日志保存位置set global log_queries_not_using_indexes = on //-- 没有命中索引的是否要记录慢日志set global long_query_time = 0.1 (秒) //-- 执行时间超过多少为慢日志

SQL优化，最重要的就是优化SQL索引。

SQL索引的好处

1.提高查询效率

2.降低CPU使用率。

SQL索引的弊端

1.当数据量很大的时候，索引也会很大(当然相比于源表来说，还是相当小的)

2.索引并不适用于所有情况：a.少量数据；b.频繁进行改动的字段，不适合做索引；c.很少使用的字段，不需要加索引；

3.索引会提高数据查询效率，但是会降低“增、删、改”的效率。

索引相当于字典的目录。利用字典目录查找汉字的过程，就相当于利用SQL索引查找某条记录的过程。

索引是帮助MySQL高效获取数据的一种【数据结构】。MySQL中一般用的是【B+树】。

B+树图示说明

最下面的第3层，属于叶子节点，真实数据部份都是存放在叶子节点当中的。第1、2层中的数据用于分割指针块儿的，比如说小于26的找P1，介于26-30之间的找P2，大于30的找P3。

增加“节点数”可以是这三层【B+树】存放上百万条数据。

【B+树】中查询任意数据的次数，都是n次，n表示的是【B+树】的高度。

索引的增删改查

索引分普通索引、唯一索引、复合索引，主键索引不能有null值，唯一索引可以有null值。

Ⅰ 创建普通索引（create、alter两种方式，下同）

create index `索引名称` on `表名`(`字段名`);alter table `表名` add index `索引名称`(`字段名`);

Ⅱ 创建唯一索引

create unique index `索引名称` on `表名`(`字段名`);alter table `表名` add unique index `索引名称`(`字段名`);

Ⅲ 创建复合索引

create index `索引名称` on `表名`(dept,name);alter table `表名` add index `索引名称`(`字段名`,`字段名2`);

索引删除和索引查询

drop index `索引名称` on 表名`;show index from 表名`;

执行计划explain

explain+ SQL语句

执行计划中需要知道的几个“关键字”

id ：编号select_type ：查询类型table ：表type ：类型possible_keys ：预测用到的索引key ：实际使用的索引key_len ：实际使用索引的长度ref ：表之间的引用rows ：通过索引查询到的数据量Extra ：额外的信息

explain执行计划常用关键字详解

id关键字，id值越大越优先；id值相同，从上往下顺序执行。

select_type：查询类型

 simple：简单查询，不包含子查询，不包含union查询。 primary：包含子查询的主查询(最外层) subquery：包含子查询的主查询(非最外层) derived：衍生查询(用到了临时表) union：union之后的表称之为union表，如上例 union result：告诉我们，哪些表之间使用了union查询

type：访问类型，sql查询优化中一个很重要的指标，结果值从好到坏依次是：system > const > eq_ref > ref > range > index > ALL

system：表只有一行记录（等于系统表），const类型的特例，基本不会出现，可以忽略不计const：表示通过索引一次就找到了，const用于比较primary key 或者 unique索引eq_ref：唯一索引扫描，对于每个索引键，表中只有一条记录与之匹配。常见于主键 或 唯一索引扫描ref：非唯一性索引扫描，返回匹配某个单独值的所有行，本质是也是一种索引访问range：只检索给定范围的行，使用一个索引来选择行（至少要这个级别）index：Full Index Scan，索引全表扫描，把索引从头到尾扫一遍ALL：Full Table Scan，遍历全表以找到匹配的行possible_keys：查询过程中有可能用到的索引key：实际使用的索引，如果为NULL，则没有使用索引 rows，根据表统计信息或者索引选用情况，大致估算出找到所需的记录所需要读取的行数。filtered：它指返回结果的行占需要读到的行(rows列的值)的百分比。表示返回结果的行数占需读取行数的百分比，filtered的值越大越好。

Extra ：额外信息

       Using filesort ：mysql对数据使用一个外部的文件内容进行了排序，而不是按照表内的索引进行排序读取 order by xxx desc这样子的，如果是索引字段的排序则不是这样的，就不需要使用外部文件了        Using temporary：使用临时表保存中间结果，也就是说mysql在对查询结果排序时使用了临时表，常见于order by 或 group by        Using index：表示相应的select操作中使用了覆盖索引（Covering Index），避免了访问表的数据行，效率高        Using where ：使用了where过滤条件        select tables optimized away：基于索引优化MIN/MAX操作或者MyISAM存储引擎优化COUNT(*)操作，不必等到执行阶段在进行计算，查询执行。计划生成的阶段即可完成优化

几个优化小技巧

> 最佳字段做前缀，复合索引顺序和使用顺序一致，不要跨字段使用

> 添加索引的时候，要根据MySQL解析顺序添加索引

编写过程

select dinstinct  ..from  ..join ..on ..where ..group by ..having ..order by ..limit ..

解析过程

from .. on.. join ..where ..group by ..having ..select dinstinct ..order by ..limit ..

> 使用了in有时候会导致索引失效，尽量将in字段放在最后面。

> 不要在索引上进行任何操作(计算、函数、类型转换)，否则索引失效

> 索引不能使用不等于（!= <>）或is null (is not null)，否则自身以及右侧所有全部失效(针对大多数情况)。复合索引中如果有>，则自身和右侧索引全部失效。

> like尽量以“常量”开头，不要以’%'开头，否则索引失效

select * from ... where name like 'x%';

> 尽量不要使用类型转换（显示、隐式），否则索引失效

   索引对列类型为varchar，但给定的值为数值，涉及隐式类型转换

> 尽量不要使用or，否则索引失效

>如果主查询的数据集大，则使用in关键字，效率高。如果子查询的数据集大，则使用exist关键字,效率高。

select ..from ... where exist (子查询)
select ..from ... where 字段 in (子查询)

> 避免使用select * …（select后面写所有字段，也比写*效率高)

使用select * 取出全部列，会让优化器无法完成索引覆盖扫描这类优化，会影响优化器对执行计划的选择，也会增加网络带宽消耗，更会带来额外的I/O,内存和CPU消耗。

> 用where字句替换HAVING字句

避免使用HAVING字句，因为HAVING只会在检索出所有记录之后才对结果集进行过滤，而where则是在聚合前刷选记录，如果能通过where字句限制记录的数目，那就能减少这方面的开销。