MySQL索引原理及SQL优化

一、索引定义

索引是一种高效获取数据的存储结构，是一个排序的列表，在这个列表中存储着索引的值和包含这个值的数据所在行的物理地址。在数据十分庞大的时候，索引可以大大加快查询的速度，这是因为使用索引后可以不用扫描全表来定位某行的数据，而是先通过索引表找到该行数据对应的物理地址然后访问相应的数据。

优势：提高查询效率，降低I/O次数；降低CPU使用率，B树本身就是一个排好序的结构，根据索引可以加快分组和排序；
劣势：1、索引本身也是表，因此会占用存储空间，存放在内存/硬盘（通常为硬盘）；
2、索引表的维护和创建需要时间成本，这个成本随着数据量增大而增大；
3、构建索引会降低数据表的修改操作（删除，添加，修改）的效率，因为在修改数据表的同时还需要修改索引表；
4、索引不是所有情况都适用：a、少量数据  b、频繁更新的字段 c、很少使用的字段。

二、索引使用

创建索引：

1、创建表时可增加索引

2、在创建表以后添加索引： create 索引类型 索引名 on 表(字段）

单值：create index name_index on tb(name);

唯一索引：create unique index name_index on tb(name)

复合索引：create dept_name_index on tb(name,dept);

3、alter table 表名 add 索引类型 索引名(字段）

删除索引：drop index 索引名 on 表名；

查询索引：show index from 表名 ；show index from 表名 \G

三、实现原理

MySQL使用B+树作为索引结构，选择B+树而不是其他数据结构的原因主要是因为数据是保存在硬盘上而不是内存中，所以减少磁盘IO次数才是提升效率的关键。B+Tree的高度是可控的，mysql通常是3到5层。三层Btree可以存放上百万条数据，数据全部存放在叶节点中，叶子节点是以链表的形势互相指向的，B+树种查询任意的数据次数：n次（B+树的高度）。

b+树的查找过程
如图所示，如果要查找数据项29，那么首先会把磁盘块1由磁盘加载到内存，此时发生一次IO，在内存中用二分查找确定29在17和35之间，锁定磁盘块1的P2指针，内存时间因为非常短（相比磁盘的IO）可以忽略不计，通过磁盘块1的P2指针的磁盘地址把磁盘块3由磁盘加载到内存，发生第二次IO，29在26和30之间，锁定磁盘块3的P2指针，通过指针加载磁盘块8到内存，发生第三次IO，同时内存中做二分查找找到29，结束查询，总计三次IO。真实的情况是，3层的b+树可以表示上百万的数据，如果上百万的数据查找只需要三次IO，性能提高将是巨大的，如果没有索引，每个数据项都要发生一次IO，那么总共需要百万次的IO，显然成本非常非常高。

其中Innodb和MyISAM引擎，实现有些差异，Innodb存的是索引和主键值，MyISAM叶子节点存的是索引和数据地址，具体可参考https://www.cnblogs.com/wlwl/p/9465583.html

四、SQL优化

原因：性能低、执行时间太长，等待时间他唱、SQL语句欠佳（连接查询）、索引失效、服务器参数设置不合理（缓冲、线程数）。

a、SQL：编写过程：select distinct...from...join...on...where...group by...having...order by...limit...

                 解析过程：from...on...join...where...group by... having...select...order by...limit...

参考文档：https://www.cnblogs.com/qcfeng/p/9779090.html

b、SQL优化，主要就是在优化索引，索引相当于书的目录。索引是帮助MySQL高效获取数据的数据结构。索引是数据结构B+树

优化方法：

a.分析SQL的执行计划：explain，可以模拟SQL优化器执行SQL，从而知道自己编写的SQL状况。

b.MySQL查询优化器会干扰我们的优化，所以尽量模仿优化器的操作。

参考文章：https://blog.csdn.net/tongdanping/article/details/79878302

五、优化方法

1）、避免索引失效：

a、复合索引不能跨列或无序使用，要符合“最佳左前缀”。

b、复合索引尽量使用全索引匹配。

c、不要在索引上进行任何操作，如计算、函数、类型转换等，否则索引会失效。

d、复合索引不能使用不等于(!= <> >)和is null （is not null)，否则自身或者右侧均失效。

e、尽量不要使用or，否则导致索引失效，甚至将or左侧的索引都导致失效。

f、索引建立在经常查询的字段上，比如左外连接给左边加，右外连接给右边加。

h、将含In的范围查询放到where条件的最后，防止失效。

i、like尽量以常量开头，不要以%开头，否则索引失效。

2）、表关联时，将数据量小的表放左边，小表驱动大表。

3)、exists和in 如果主查询的数据集大，则用in，如果子查询的数据集大，则用exists。

4）、全部的排序字段，排序要一致，都是升序或降序。

5）、oder by优化

双路排序：Mysql4.1之前是使用双路排序，字面的意思就是两次扫描磁盘，最终得到数据，读取行指针和ORDER BY列，对他们进行排序，然后扫描已经排好序的列表，按照列表中的值重新从列表中读取对数据输出。也就是从磁盘读取排序字段，在buffer进行排序，再从磁盘读取其他字段。文件的磁盘IO非常耗时的，所以在Mysql4.1之后，出现了第二种算法，就是单路排序。

单路排序：从磁盘读取查询所需要的所有列，按照ORDER BY在buffer对它进行排序，然后扫描排序后的列表进行输出，它的效率更快一些，避免了第二次读取数据。并且把随机IO变成了顺序IO，但是它会使用更多的空间，因为它把每一行都保存在了内存里。但是，有可能单路排序算法一次拿不出数据，那么就还比双路排序更消耗IO，效率更慢！

1、ORDER BY时不要使用SELECT *，只查需要的字段。
   a：当查询的字段大小综合小于max_length_for_sort_data而且排序字段不是TEXT|BLOB类型时，会用改进后的算法---单路排序，否则用老算法---多路排序。假设只需要查10个字段，但是SELECT *会查80个字段，那么就容易把sort_buffer缓冲区用满。
   b：两种算法的数据都有可能超出sort_buffer的容量，超出之后，会创建tmp文件进行合并排序，导致多次I/O，但是用单路排序算法的风险会更大一些，所以要提高sort_buffer_size大小。
2：增大sort_buffer_size参数大小
不管用哪种算法，提高这个参数都会提高效率。当然要根据系统能力去提高，因为这个参数是针对每个进程的。
3：增大max_length_for_sort_data参数大小
提高这个参数，会增加用改进算法的概率。但是如果设的太高，数据总量超出sort_buffer_size的概率就增大，明显症状是高的磁盘I/O活动和低的处理器使用率。

6）、MySQL需要为内层查询语句的查询结果建立一个临时表。然后外层查询语句在临时表中查询记录。查询完毕后，MySQL需要插销这些临时表。所以在MySQL中可以使用连接查询来代替子查询。连接查询不需要建立临时表，其速度比子查询要快。
7）、select into和create table：新建临时表时，如果一次性插入数据量很大，使用select into代替create table，避免造成大量log，以提高速度。如果数据量不大，为了缓和系统表的资源，先create table，再insert。

8）、拆分大的DELETE和INSERT语句
因为这两个操作是会锁尽量用 union all 代替 union表的，对于高访问量的站点来说，锁表时间内积累的访问数、数据库连接、打开的文件数等等，可能不仅仅让WEB服务崩溃，还会让整台服务器马上挂了。所以，一定要拆分，使用LIMIT条件休眠一段时间，批量处理。

9）、尽量用 union all 代替 union
union 和 union all 的差异主要是前者需要将两个(或者多个)结果集合并后再进行唯一性过滤操作，这就会涉及到排序，增加大量的 CPU 运算，加大资源消耗及延迟。所以当我们可以确认不可能出现重复结果集或者不在乎重复结果集的时候，尽量使用 union all 而不是 union。