在做项目优化工作，就整理了一些sql优化的总结（sql优化，索引和执行计划）

最近在由于比较闲，就向老大接了一个系统优化的工作。

由于之前有接手一个项目，开发的同事建表没有建索引的习惯，前车之鉴我就直接从sql开始优化。

索引

普通索引：一个索引只包含一个列，一个表可以有多个单一索引。
唯一索引：索引列的值必须唯一，但允许有空值。
复合索引（联合索引）：一个索引包含多个列。
——————————————————————————————
聚簇索引：并不是单独的索引类型，而是一种数据存储方式。按照每张表的主键构造的一颗b+树，同时叶子节点中存放了的就是整张表的行记录数据。每张表只能有一个聚簇索引。

聚簇索引图例：

非聚簇索引（辅助索引）：不是聚簇索引就是非聚簇索引。辅助聚簇索引的索引。辅助索引的叶子节点不是行的物理位置，而是主键值。通过主键值”回表“查询数据。

非聚簇索引图例：

执行计划

简介：使用explain关键字可以模拟优化器执行sql查询语句，从而知道mysql是如何处理你的sql语句的。分析查询语句或是表结构的性能瓶颈。
语法：explain+sql语句

执行计划的作用：
1.查看表的读取顺序
2.数据读取操作的操作类型
3.那些索引可以使用
4.那些索引实际被使用
5.表之间的引用
6.每张表有多少行被优化器查询

执行计划包含的信息

1.id：查询的执行顺序，id相同由上至下执行，id不同，数值越大越先被执行。

2.select_type：
①simple：simple查询中不包含子查询或者union等。
②primary：查询中包含复杂的子部分。
③subquery：在select或者where列表中包含子查询。
④derived：在from列表中包含子查询被标记为derived（衍生），mysql会递归执行这些子查询，把结果放在临时表中。
⑤union：在第二个select出现在union之后，被标记为union。若union包含在from的子查询中，外层select将被标记为derived。
⑥union result：从union表中获取结果的select。

3.table：标记那一张表。

4.partitions：仅在涉及分区表的情况下有，列出读取记录的分区。

5.type：sql执行的指标。
system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL
system：系统表，一般不会出现。
const：通过索引一次就能找到，常见于通过主键查找一次就能完成。
eq_ref：唯一索引扫描，对于每个索引键，表中都只有唯一一条记录与之相匹配。常见于主键或者唯一索引扫描。
ref：非唯一索引扫描，返回匹配某个单独值的所有行。表中可能会存在多条匹配的记录。
range：通过索引给定范围行，使用一个索引来选择行。key列显示使用了那一个索引。一般出现在where的范围查询中。索引扫描要比全表扫面效果好，因为它之开始于索引的某个点，结束于索引的某个点，不用扫描全部的索引。
all：全表扫描找到匹配的行。

6.possible_key：列出所有可能使用的索引。

7.key：Mysql决定使用的索引。可能出现key列列出的索引不在possible_keys当中的情况，通常是所需的结果列满足某个覆盖索引的情况。

8.key_len：使用的索引的长度，也就是索引使用的字节数。在不损失进度的情况下，索引应该越短越好。（注意key_len是记录可能使用额最长长度，并给实际使用长度，key_len是通过表定义得出，并非通过表内检索得出。）根据key_len就可以在组合索引的情况下判断是否所有的索引都使用到。

索引的长度和字符编码也有关系，latin1占用1个字节，gbk占用2个字节，utf8占用3个字节。（不同字符编码占用的存储空间不同）


datetime类型在5.6中字段长度是5个字节，
datetime类型在5.5中字段长度是8个字节

需要注意如下：
char：不为null时

char：可以为null时

varchar：不可为null时。

varchar：可以为null时

9.ref：显示利用索引进行数据筛选时的比较的列或者常量。

10.rows：根据表统计信息及索引选用情况，大致估算出找到所需的记录所需要读取的行数。

11.filtered：指返回结果的行占需要读到的行(rows列的值)的百分比。

12.extra：执行计划
Using filesort：mysql对数据使用一个外部的索引排序，而不是按照表中的索引顺序进行的排序读取。mysql无法利用索引完成的排序称为”文件排序“。

Using temporary：使用了临时表保存中间结果，mysql在对查询结果排序时使用了临时表。常见于order by和group by。

Using index：使用了覆盖索引，避免访问了表的数据行，指我们要取的数据即为索引节点的数据，不需要通过回表取得数据，这类情况下效率都不错。
如果同时出现using where，表明索引被用来执行索引键值的查找;
如果没有同时出现using where，表明索引用来读取数据而非执行查找动作。

Using where：表明使用了where过滤。

using join buffer：使用了连接缓存。

impossible where：where子句的值总是false，不能用来获取任何元组。

注意

1.在where子句中使用！=或者<>操作符，查询引用会放弃索引而进行全表扫描。

2.不能使用前导模糊查询，"%xx"或者"%xx%"，前导模糊不能利用索引排序，必须一个个匹配看是否复合条件，这样会导致全索引扫描或者全表扫描。

3.条件中含有or即使其中有条件带索引也不会正常使用索引，要想使用or又想让索引生效，只能将or条件中每个列都加上索引才能正常使用。（我推荐配到这种情况可以使用union代替。）

4.在where子句中对字段进行表达式操作，这样会导致索引失效。

建议一

1.尽量全值匹配，即复合索引（联合索引），在where中我们要全部使用，而不是使用其中一个。

2.对于复合索引，我们要使用"最左前缀"原则，即使用复合索引（联合索引）必须从索引的第一个列开始，且不能跳过任何列，跳过了后续的列就会失效。

3.不在索引列上做任何操作（计算、函数、(自动or手动)类型转换），会导致索引失效而转向全表扫描

4.范围条件放到最后，范围条件会使后续的索引列失效。

5.覆盖索引尽量使用，但不是说要查的列你都建索引。
6.不等于要谨慎使用，这有可能会导致全表扫描。
7.null和not null也要谨慎使用。有些表已经定义不能为null了，你就不要在where加这个条件了，会导致全表扫描。
8.字符型索引要加引号，不加引号会导致索引失效。
9.尽量使用主键查询，而非其它索引，主键查询不会出发回表查询。
10.查询语句尽可能简单，大语句拆小语句，减少锁时间。
11.尽量使用数字型字段，若只包含数值信息的字段尽量不要设计为字符型。
12.用exists代替in查询。
13.避免在索引列上使用is null和is not null，这样会导致全索引扫描。

建议二

1.使用可以存下数据量最小的数据类型
2.使用简单的数据类型，int要比varchar类型在mysql处理简单。
3.尽量使用tinyint、smallint、mediumint作为整数类型而非int。
4.尽可能使用not null字段，因为null占用4个字节空间。
5.尽量少用text类型，非用不可最好考虑分表。
6.尽量使用timestamp，而非datetime。也是字节空间问题。
7.单表不要有太多字段，建议在20个字段以内。

整个sql运行都慢

可能数据库的承载能力已经达到了峰值

解决办法

1.读写分离（分为应用层解决和中间件解决）

2.数据拆分
2.1垂直拆分：
①将一些text和blob等大字段拆分出来放在附表中。
②把不常用的字段单独放在一张表中。
③经常组合查询的列放在一张表中。
2.2水平拆分：
分表分库，网上较多，我这里就不多做介绍了。

没有实战都是纸上谈兵，让我们看一个实战实例

由于查询的字段比较多，select下查询的字段我就不展示了，看下图，注意看框和箭头的标记。

mysql5.7以后这样写from直接跟表似乎对查询影响不大，但是我还是不推荐这样写，我们使用left join小表前置原则。

箭头标记的说明可能这些字段都是只保存数字型数据，但是他们表设计用了字符型，需要改的。范围索引没有放在最后，这也是需要改的。

请看这几张表数据量和大小：



我们再看看它的sql执行了多长时间，很糟糕查询了5s。

好的我们开始做修改吧，不要再线上环境修改。我们把表copy到本地。这里注意一下mysql5.5与5.7有些不同，并且查询速度再机械硬盘与固态硬盘的查询速度也有天壤之别。为此我还被弄蒙过，不过后来我还是发现了问题。咱么的测试环境尽量与线上环境保持一致。为此我将mysql数据保存到了固态硬盘，并且升级本地mysql到5.7.

好的按照我的最初的想法开始，将一些字段改为tinyint类型，将sql优化一下

很糟糕，本地固态硬盘环境执行了10s


问题不大，咱们看看执行计划

使用了using filesort，那咱们就给表加个排序索引，这里建了好多索引，有一些还是要删掉的，这个还要找同事说一下。我这里给建议索引建立不要超过8个。

咱们再次执行sql，我的渣机都只跑了1.2s

看看执行计划

嗯，可以去吃饭了，找老板升职加薪。