一、底层索引优化

——索引是帮助MySQL高效获取数据的排好序的数据结构

哪些情况 需要/适合 创建索引？

1、主键自动建立唯一索引；
2、频繁查找的字段；
3、查询中与其它表关联的字段，外键关系建立索引；
4、查询中排序的字段，通过索引去访问将大大提高排序速度。

哪些情况不适合创建索引？

1、频繁更新的字段；
2、while条件里用不到的字段不创建索引；
3、表记录太少（万把条也没问题，300万条后，性能开始下降）；
4、数据重复且分布平均的表字段，比如性别列不需要（要为最经常排序的数据列建立索引）。

【面试题】：MySQL索引有哪几种？

按数据结构分类：

1、 B+树索引(O(log(n)))；
——主流，InnoDB 建议为大部分表使用默认自增的主键作为主索引。

2、 hash索引：
基于hash表结构实现的索引，mysql中只有 MEMORY/HEAP 和 NDB 存储引擎支持;

按表列属性分类：
1、单列索引
2、联合索引：
3、普通索引：index
4、主键索引：primary key
5、唯一索引：unique
6、全文索引：fulltext

如何创建索引？

• 普通索引：建表的时候创建、建表后创建、修改表结构
  ——ALTER TABLE ‘user’ ADD INDEX index_name(name);
• 唯一索引
  ——ALTER TABLE ‘user’ ADD UNIQUE(name);
• 主键索引
  ——ALTER TABLE ‘user’ ADD PRIMARY KEY(name);
• 组合索引
  ——ALTER TABLE ‘user’ ADD INDEX index_name(name1，name2,…);
• 全文索引
  ——ALTER TABLE ‘user’ ADD FULLTEXT(name);
  以上也可以用create index index_name on user...来创建

删除：DROP INDEX index_name ON table

注意：MySQL索引和建表约束不一样：
（1）索引：作用：快速定位特定数据，提高查询效率，确保数据的唯一性，快速定位特定数据；
（2）约束：作用：是为了保证数据的完整性而实现的摘自一套机制，它具体的根据各个不同的数据库的实现而有不同的工具（约束）；
——总结：约束是为了保证表数据的完整性，索引是为了提高查询效率

（1） 为什么要用索引？

• mysql数据是以文件的形式存储在磁盘上的；
• 那磁盘是有一圈一圈的磁道组成的；
• 磁头移动到不同磁道，磁盘旋转，这样就可以读取到数据；
• 当磁头移动到另一个磁道读取，就是我们常说的一次I/O操作，但是我们知道mysql数据是分布到不同的磁道上的，每次读取数据都要把所有磁道读取一遍，那我们进行I/O次数就很多了，查询效率就很低；
• 那索引就是把索引字段数据的地址保存起来，来帮助mysql直接定位到哪个磁道的哪个扇区，这样就减少I/O操作了，自然查询效率就提高了。

（2）索引数据结构

通过对比下面的各种数据结构来证明官方为何用B+树：

• 二叉树
• 红黑树
• Hash表
• B-Tree

索引是如何让查询能够快速执行的？
——没有索引，会从头开始一行一行地查数据库

①二叉树（搜索树）

跟根节点对比，比root大就查右边，小就查左边（大大减少了查找次数，为O(log N)）；——但是官方没有选择二叉树
为什么？
假如数据是递增的，那么二叉搜索树就会成为递增的单链表结构（单边增长），那么查找起来也会很复杂，就跟没有建立二叉树结构的效率是一样的！——O(n)

查找6，会需要6次。

②红黑树（自平衡二叉查找树）

(红黑树模拟地址)

查找6，只需要3次，直接减少了一半！—— O(log N)
优点： 红黑树会自动调整结构，避免出现搜索树那种几段情况，减少了查找次数。

但是，官方为什么还是没有选择红黑树？

——树的高度会很大（在数据很多的情况下），高度为多少，就要查找多少次（查找方式：磁盘IO，磁盘IO速度是比较慢的）（磁盘查找存取的次数往往由树的高度所决定）
——其它：数据结构维护复杂，插入慢

③Hash表

—— Hash表是根据关键码值(Key value)而直接进行访问的数据结构。

优点：
将索引值做一个哈希运算，hash(6)，值就是哈希表存储位置，对应了磁盘存储位置。根据索引，只要经过一次磁盘IO就能定位到磁盘位置。—— O(1)

但是，性能如此之高，为何没有被使用？
eg：

SELECT * FROM t WHERE t.col1>6

——哈希表不支持范围查找！+ 数据多可能会Hash冲突！

④B-Tree（官方使用）

——让树的每个节点就能存储更多的索引元素，并且还是多叉树，可以把

**是否可以把所有索引放入一个节点中？**因为这样一次磁盘IO就能全部放入内存中，再在内存中去执行查找操作，就会很快了。（内存查找比磁盘查找快得多）

——这样不现实，因为内存有限，数据库很大，会占用大量的内存。而且一次磁盘IO，加载这么多数据到内存中，加载过程也很慢。
MySQL官方将节点大小设置为16K。

1）B树（也叫B-树）：

每个结点都存储了索引和数据，每个节点内的值，都是从左到右递增的，其二叉树也是搜索树结构。
优点：一次磁盘IO可以将更多数据存入内存，且多叉结构避免了高度过大的情况，减少磁盘IO次数。

不使用原因：

1、b+树的数据都集中在叶子节点，分支节点只负责索引。 b树的分支节点也有数据 。 B+树的层高会小于B树，B树平均的IO次数会远大于 B+树；

2、b+树更擅长范围查询。叶子节点 数据是按顺序放置的双向链表。b树范围查询只能中序遍历；

3、B+树的非叶子节点只有索引没有数据，占用空比较小，而B树把节点加载到内存中，所占用的空间较大。

实际上，官方使用的是B+树来实现。

2）B+树：

—— 存放更多数据 + 解决范围查找问题 + 查询更快

• 所有数据和索引，都放了一份到叶子节点上；

• 非叶子节点只存储索引，不存放data（这样可以存储更多索引）；

• 索引从左到右递增

• 中间索引在非叶子节点上进行冗余存储，这样可以快速地通过二分查找找到相应主键id的数据。

• 非主键索引叶子节点记录的是索引-主键key，然后通过主键key去回表查主键索引树,最终拿到数据。
  
  B+树演示地址：
  每个节点的索引之间都有一个指针，指针指向了其子节点的数据data。

为什么一个节点的大小是16K？

假设根节点存满了，MySQL的bigint占用8字节，两个索引数据之间还有一个指针，占用6字节，所以一个索引总共占14个字节，即：14Byte，16KB=102416Byte，总共可存储：
161024/14≈1170个索引
对于三层的B+树，根节点有1170个，根节点的子节点有11701170个。如果将叶子结点设置为最多存两个元素，则一个索引加data有8KB，叶子节点共16KB。共11701170个叶子结点。

查找：比如查找索引为30的元素：

3次查找：
第一次查找：30在15~ 56之间，就从第一个指针往下找；
第二次查找：30在20~49之间，就从第二个指针往下找；
第三次查找：在叶子结点中直接找到30，再根据索引找到data.

两次磁盘IO：
磁盘IO：根节点的16K一开始就是存入内存中的，只需要把子节点的16K存入，再把叶子结点存入内存，只需要两次磁盘IO！
——性能高效。

范围查找的实现：

因为叶子结点之间也有指针，叶子结点存储了索引元素，可以直接把后面的元素找到。

例如查找索引>20的数据，只需要找到索引20，再把之后的元素找出。

（3）聚集索引和非聚集索引

① InnoDB存储引擎（聚集索引）

.frm文件：存储表结构文件
.ibd文件：索引数据index date

——InnoDB中索引文件和数据文件是同一个文件（聚集）

InnoDB的表数据文件，本身就是按B+树组织的一个索引结构文件：

——可以通过聚集索引查到数据，叶子节点包含了完整的数据记录

【面试题一】InnoDB表必须有主键

为什么InnoDB表必须有主键，并且推荐使用整数型的自增主键？

1） 因为MySQL的InnoDB表数据，必须用B+树来维护它，B+树要使用主键（primary key）来维护，如果没有，就会自动生成（没有主键的话，数据无法有序组织）。

2） 假如用UUID作为自增主键的话，占用的存储空间更大（UUID一大串字符比较，要转ASCII码再比，效率低）；而整数型占用的空间小。

3） 自增主键：插入数据的时候，索引是最大的 ，可以直接插入到节点的最后面。如果不是自增主键，那系统为了维护B+树的结构，会分裂重建构建结构。

② MyISAM存储引擎（非聚集索引）

——MyISAM索引文件（MYI文件）和数据文件（MYD文件）是分离的（非聚集）：

【非聚集索引和聚集索引的区别】：

通过聚集索引可以查到需要查找的数据，因为索引域和数据域在同一个文件；而通过非聚集索引可以查到记录对应的主键值 ， 再使用主键的值查找到需要的数据（索引文件（MYI文件）和数据文件（MYD文件）是分离的，索引的数据域存储指向数据文件的指针）

根本区别：
聚集索引和非聚集索引的根本区别是表记录的排列顺序和与索引的排列顺序是否一致。

• （1）聚集索引
  聚集索引表记录的排列顺序和索引的排列顺序一致，所以查询效率快，只要找到第一个索引值记录，其余就连续性的记录。聚集索引对应的缺点就是修改慢，因为为了保证表中记录的物理和索引顺序一致，在记录插入的时候，会对数据页重新排序；

• （2）非聚集索引
  非聚集索引制定了表中记录的逻辑顺序，但是记录的物理和索引不一定一致，两种索引都采用B+树结构，非聚集索引的叶子层并不和实际数据页相重叠，而采用叶子层包含一个指向表中的记录在数据页中的指针方式。非聚集索引层次多，不会造成数据重排。

【InnoDB、Mysaim存储引擎的区别】

• （1）MyISAM使用表锁，在高并发下会出现严重的锁问题。InnoDB使用行锁，适合高并发；
• （2）InnoDB支持事务，适合执行大量的insert或update操作。
• （3）MyISAM是非事务表，提供高速存储检索，适合做大量的select查询。
• （4）InnoDB采用聚集型索引，索引域和数据域在同一个文件；MyISAM采用非聚集型索引，索引文件（MYI文件）和数据文件（MYD文件）是分离的，索引的数据域存储指向数据文件的指针。聚集索引修改数据慢，因为要对数据页重排。
• （5）一个MyISAM表有三个文件：索引文件、表结构文件、数据文件。InnoDB有表结构文件（.frm文件）和索引文件（.ibd文件）

（4）联合索引

联合索引底层数据结构（见得最多）：
通过多个索引来查找，可能有相同的索引。
比较相等时，先比较第一列的值，如果相等，再继续比较第二列

使用联合索引时，索引列的定义顺序将会影响到最终查询时索引的使用情况。例如联合索引（a,b,c），mysql会从最左边的列优先匹配，如果最左边的带头大哥没有使用到，在未使用覆盖索引的情况下，就只能全表扫描。

索引最左前缀：

Mysql的索引为什么使用B+树而不使用跳表？

B+树是多叉树结构，每个结点都是一个16k的数据页，能存放较多索引信息，所以扇出很高。三层左右就可以存储2kw左右的数据（知道结论就行，想知道原因可以看之前的文章）。也就是说查询一次数据，如果这些数据页都在磁盘里，那么最多需要查询三次磁盘IO。

跳表是链表结构，一条数据一个结点，如果最底层要存放2kw数据，且每次查询都要能达到二分查找的效果，2kw大概在2的24次方左右，所以，跳表大概高度在24层左右。最坏情况下，这24层数据会分散在不同的数据页里，也即是查一次数据会经历24次磁盘IO。

因此存放同样量级的数据，B+树的高度比跳表的要少，如果放在mysql数据库上来说，就是磁盘IO次数更少，因此B+树查询更快。

而针对写操作，B+树需要拆分合并索引数据页，跳表则独立插入，并根据随机函数确定层数，没有旋转和维持平衡的开销，因此跳表的写入性能会比B+树要好。

redis为什么使用跳表而不使用B+树或二叉树呢？

redis支持多种数据结构，里面有个有序集合，也叫ZSET。内部实现就是跳表。那为什么要用跳表而不用B+树等结构呢？

大家知道，redis 是纯纯的内存数据库。
进行读写数据都是操作内存，跟磁盘没啥关系，因此也不存在磁盘IO了，所以层高就不再是跳表的劣势了。
并且前面也提到B+树是有一系列合并拆分操作的，换成红黑树或者其他AVL树的话也是各种旋转，目的也是为了保持树的平衡。
而跳表插入数据时，只需要随机一下，就知道自己要不要往上加索引，根本不用考虑前后结点的感受，也就少了旋转平衡的开销。
因此，redis选了跳表，而不是B+树。

二、SQL语句优化

2.1 如何发现有问题的语句

（1）方式一

——通过 MySQL慢查询日志 对有效率问题的SQL进行监控

MySQL的慢查询日志是MySQL提供的一种日志记录，它用来记录在MySQL中响应时间超过阀值的语句，具体指运行时间超过long_query_time值的SQL，则会被记录到慢查询日志中。
long_query_time的默认值为10，意思是运行10s以上的语句。

（2）方式二

——通过explain查询和分析SQL的执行计划
（见第三部分）

点击此处查看此块内容所参考的文章

三、MySQL性能分析 explain

3.1 MySQL常见瓶颈：

1、CPU饱和：一般发生在数据存入内存或从磁盘读取数据时。
2、磁盘IO：发生在转入数据大于内存容量时；
3、服务器硬件性能瓶颈：通过top、free、iostat和vmstat来查看系统性能状态

3.2 Explain（查询执行计划）

——可以模拟优化器执行SQL查询语句，从而知道MySQL是如何处理你的SQL语句的，

使用：Explain + SQL语句

explain select* from XX；

3.2.1 explain的用处：

1、获取表的读取顺序；
2、数据读取操作的操作类型；
3、哪些索引可以使用，和被实际使用；
4、表之间的引用；
5、每张表有多少行被优化器查询。

3.2.2 执行计划包含的信息

id select_type table type possible_keys key key_len ref rows Extra

通过explain+sql语句可以知道如下内容：

• id:选择标识符：SELECT的查询序列号
• select_type:表示查询的类型。
• table:输出结果集的表
• partitions:匹配的分区
• type:表示表的连接类型
• possible_keys:表示查询时，可能使用的索引
• key:表示实际使用的索引
• key_len:索引字段的长度
• ref:列与索引的比较
• rows:扫描出的行数(估算的行数)
• filtered:按表条件过滤的行百分比
• Extra:执行情况的描述和说明

3.2.3 Explain具体用法

https://blog.csdn.net/lvhaizhen/article/details/90763799