MySQL索引原理以及优化

索引的优点和缺点

优点

1. 如果没有索引，在查询数据时需要全表扫描判断每一条记录是否符合条件，如果有索引，可以大大**减少需要扫描的行数**，从而缩短查询时间。
2. 通过建立**唯一索引**，可以确保**数据的唯一性**
3. 在表的关联中，给**关联列**建立索引，可以加快表的关联速度，JOIN字段的索引要添加在**被驱动表**中，如果是**左连接**则需要往**右表**的字段添加索引。
4. 减少**GROUP BY**和**ORDER BY**的时间，减少Cpu的损耗，分组和排序使用索引的**前提**是在**Where**条件中使用了索引，如果数据量大时使用索引还需要回表，因此**优化器**会认为不使用索引效率更高。
   

缺点

1. 建立索引需要**占据磁盘空间**，建立越多的索引，占据的磁盘空间越大
2. 索引提高了查询的效率，但是在插入等操作时，因为**需要维护索引**的正确性，会减慢速度
   

索引概念

主键索引、聚簇索引

1. 聚簇索引不是一种单独的索引形式，而是一种数据的存储结构，即所有的数据都存储在索引中，数据存储在索引的**叶子节点**处，主键索引每个表不需要创建，**默认存在**，且**只能够存在一个**。
2. **数据页**中的数据由**单向链表**组成，数据按照**主键**的顺序进行排列。数据页之间也会按照**主键**进行排列，数据页之间的数据由**双向链表**组成。
3. 因为主键是按顺序排列，如果插入的主键不是顺序的，会导致**页分裂**，因为需要重新维护数据页的顺序性，从而导致**插入时间较长**，因此一般主键采用数据库**自增主键**。
4. 主键索引中的**叶子节点**保存的是数据列的**完整数据**，**包括隐藏列**。
5. 主键索引的特点是**查询效率快**，因为所有的数据都在索引中，不需要进行**回表**操作。并且在进行范围查询的时候速度较快，因为都在一个数据块中。

二级索引、非聚簇索引

1. 非聚簇索引**可以建立多个**，每个非聚簇索引都会建立一个自己的B+树，与聚簇索引不同的是，非聚簇索引的B+树是以**索引列**进行排序，同时叶子节点中**只保存索引列的数据和主键列的数据**。
2. 在使用非聚簇索引时，会先根据索引列找到对应的主键信息，因为聚簇索引中没有其他的信息，再从聚簇索引的B+树中去查询信息，这个过程称之为**回表**。
3. **聚簇索引**中叶子节点存储的是**数据记录**，**非聚簇索引**中存储的是**数据位置**。

联合索引

1. 联合索引本质上也是**非聚簇索引**，但一般指的**非聚簇索引**叶子节点只有索引列和主键数据，**联合索引**包含多个索引列和主键数据。
2. **多个**非聚簇索引会产生**多个**B+树，**联合索引**指的是只有**一个**B+树，但含有**多个**索引列，索引列之间**存在顺序**关系。
3. 数据页中的数据会先按照第一个索引进行排序、在第一个索引列的数据相同时，再按照第二个索引列的数据进行排序，以此类推，最后到主键索引列，因为这个原因，所以在使用索引时，需要确保SQL按照**最左前缀原则**进行编写。
4. 如果联合索引中的数据包含了**所有**的查询信息，此时则不需要进行**回表**操作。

Innodb和MyISAM的区别

存储引擎是基于表的，可以给每个表设置不同的存储引擎。

1. Innodb是**支持事务的**，MyISAM**不支持事务**
2. Innodb支持**行级锁**和表级锁，MyISAM**只支持表级锁**，因此MyISAM在并发场景下效率较差。
3. Innodb支持**崩溃恢复机制**，MyISAM不支持崩溃恢复机制
4. Innodb支持**外键**，MyISAM不支持外键
5. Innodb的索引实现方式是 **索引和数据放在一起**，根据索引列查询到叶子节点后，可以直接查询到数据。MyISAM的索引实现方式是**索引和数据分开**，索引树中的叶子节点只有主键和地址，根据地址去获取数据列的详细信息。
   

索引数据结构的选择

Hash 仅Memory存储引擎存在

1. 查询**效率最快**，但是在**范围查询**中只能进行查询全表。
2. 在索引列**重复值较高**的情况下，等值查询的效率会降低。
   

二叉搜索树

1. 确保**左边的节点小于当前节点**，**右边的节点大于当前节点**，在左右子树**高度平衡**时，查询效率近似于**二分查找**，时间复杂度为**O(logN)**
2. 缺点可能节点都在左边或者右边，从而**退换成链表**，则时间复杂度变为**O（N）**
   

二叉平衡搜索树（AVL树）

1. 左右两边的**高度差小于1**，改善了二叉搜索树会退化成链表的问题
2. 每一次查询需要进行一次磁盘IO，IO是一个比较耗时的操作，AVL树的**高度会非常高**，因此**需要进行大量的IO**，非常耗时。
   

红黑树

1. 二叉平衡树。解决二叉树一边倒的可能性。平衡树在插入和删除的时候，会通过**旋转操作**将树的左右节点达到平衡。
2. 与AVL树同理，**高度很高**，需要**进行多次IO**，不能用做于MySQL的索引存储结构。
   

B树

1. 多路平衡查找树，每个节点为一个块，一个块中存储着几条数据，左右节点之间通过双向链表建立连接，B树的**高度远小于AVL树**，因此适合作为索引存储结构。
2. B树中的数据列遍布在每一个数据节点中，因此可能在第一次查询时就查询到数据列。
   

B+树

1. 与B树类似，但B+树只有叶子节点才存放数据列，其余节点只存储目录节点，目录节点包括**索引列的值**、**主键值**、**页号**
2. 因为上层节点只存储目录节点，因此上层的数据页中可以存储更多的目录节点，因此B+树**比B树更为矮胖**，所以在B+树中进行数据的查询，只需要**进行更少的磁盘IO次数**，从而耗时更短。
3. 在范围查询中，B树因为在**每个节点中都存在数据节点**，在进行**范围查询**时，需要进行**中序遍历**，耗时较长。而B+树因为所有的数据列都**存储在叶子节点**，并且数据列之间使用**单向链表**连接，数据页之间使用**双向链表**进行绑定，因此范围查询时只需要，因此B+树在进行范围查询时效率较高。
   

为什么说B+树查询只需要1-3次IO？

1. Innodb中一个**数据页**大小为**16KB**，目录节点的存储的数据有主键值，和对应页的地址，主键一般为**bigint 8个字节**，指针大概为**8个字节**，因此**一个数据页**中大致可以存储**一千个目录节点**
2. 假设当深度为3时，100010001000=10亿，此时已经是一个很大的数字，一般数据库的数据量不会超出这个数量，因此B+树的高度只会在2-4层，从而也只需要进行1-3次的磁盘IO
   

执行计划EXPLAIN

数据准备

• s1和s2两表各一万条记录
• key1，key2，key3分别有三个二级索引，key1和key3是普通索引，key2是唯一索引

CREATE TABLE s1 (
    id INT AUTO_INCREMENT,
    key1 VARCHAR(100),
    key2 INT,
    key3 VARCHAR(100),
    key_part1 VARCHAR(100),
    key_part2 VARCHAR(100),
    key_part3 VARCHAR(100),
    common_field VARCHAR(100),
    PRIMARY KEY (id),
    INDEX idx_key1 (key1),
    UNIQUE INDEX idx_key2 (key2),
    INDEX idx_key3 (key3),
    INDEX idx_key_part(key_part1, key_part2, key_part3)
) ENGINE=INNODB CHARSET=utf8;


CREATE TABLE s2 (
    id INT AUTO_INCREMENT,
    key1 VARCHAR(100),
    key2 INT,
    key3 VARCHAR(100),
    key_part1 VARCHAR(100),
    key_part2 VARCHAR(100),
    key_part3 VARCHAR(100),
    common_field VARCHAR(100),
    PRIMARY KEY (id),
    INDEX idx_key1 (key1),
    UNIQUE INDEX idx_key2 (key2),
    INDEX idx_key3 (key3),
    INDEX idx_key_part(key_part1, key_part2, key_part3)
) ENGINE=INNODB CHARSET=utf8;

type 查询类型属性

• System 仅出现在MyISAM存储引擎中，且表中只有一条记录
• const **主键索引**或**唯一索引**进行**常量等值连接**

EXPLAIN SELECT * FROM s1 WHERE id = 10005;

• eq_ref JOIN时**被驱动表**连接字段为**主键**或是**唯一索引**，如果是LEFT JOIN，则要求右表为被驱动表。

EXPLAIN SELECT * FROM s1 INNER JOIN s2 ON s1.id = s2.id;

• ref 当通过**普通的二级索引**进行**等值查询**时，则type为ref。如果是在**JOIN查询**中，被驱动表的字段为**普通索引**时，被驱动表的type也会为ref

EXPLAIN SELECT * FROM s1 WHERE key1 = 'a';

• fulltext 全文索引
• ref_or_null **普通索引等值查询**，并且允许为NULL值

EXPLAIN SELECT * FROM s1 WHERE key1 = 'a' OR key1 IS NULL;

• range 范围查询

**IN子查询**在数据量较大时，type会由RANGE变为ALL，全表扫描从而导致查询很慢。可以将其转换为**JOIN查询**，或者可以查询出符合条件的**最大Id**，然后z根据Id进行范围查询。
IN查询在数据量大时由于优化器优化为ALL，如果数据内IN中的值较多，属于误判，可以使用**FORCE INDEX **强制使用索引。

mysql> EXPLAIN SELECT * FROM s1 WHERE key1 IN ('a', 'b', 'c');
mysql> EXPLAIN SELECT * FROM s1 WHERE key1 > 'a' AND key1 < 'b';

• index 覆盖索引，但是查询条件没有**遵循最左前缀原则**，只能对**联合索引的B+树**进行**全部扫描**，不能使用ref或range的方式直接使用索引。因此聚簇索引中包含所有的列，使用**覆盖索引**的B+树**包含列较少**进行查询效率较高。

mysql> EXPLAIN SELECT key_part2 FROM s1 WHERE key_part3 = 'a';

• all

system（只有一条记录） > const （主键或唯一二级索引常量等值连接）> 
eq_ref（索引列Join） > ref（普通索引等值连接） > fulltext（全文索引） > 
ref_or_null（普通索引等值连接允许为空） > index_merge（多个单列索引联合） > 
unique_subquery （主键 in优化为exists）> index_subquery （普通索引 in优化为exists）
> range（范围查询） > index （索引覆盖，但不满足最左前缀）> ALL（全表扫描）

其中比较重要的几个提取出来（见上图中的粗体）。SQL 性能优化的目标：至少要达到 range 级别，要求是 ref 级别，最好是 consts级别。（阿里巴巴 开发手册要求）

key_length 使用索引长度

varchar(10)变长字段且允许NULL = 10 * ( character set：utf8=3,gbk=2,latin1=1)+1(NULL)+2(变长字段)
varchar(10)变长字段且不允许NULL = 10 * ( character set：utf8=3,gbk=2,latin1=1)+2(变长字段)
char(10)固定字段且允许NULL = 10 * ( character set：utf8=3,gbk=2,latin1=1)+1(NULL)
char(10)固定字段且不允许NULL = 10 * ( character set：utf8=3,gbk=2,latin1=1)

row 预计查询数据行数

extra 查询额外信息

using where 没有使用索引，或是where条件中除了索引列还有其他列
using index 覆盖索引
Using index condition 出现索引列 但不能使用索引
Using join buffer join条件中没有添加索引
using filesort 排序条件没有添加索引

索引失效场景

1. 没有遵守**最佳左前缀**原则
2. 在where条件中做了**运算**
3. 数值有**隐式转换**，例如数字和字符串之间的转换
4. IS NULL会走索引，而**IS NOT NULL不会走索引**，因此在数据列中一般默认设置为**不能为NULL**，数字采用0代替，字符串采用空串进行代替。
5. **不等值的查询** 例如！= 不能走索引
6. like 如果以通配符开头不能走索引
7. OR的搜索范围两边有**一个列没有索引**
8. 范围查询后的索引失效，范围查询的列是可以走索引的，因为联合索引中下一级的数据仅在数据相同时保证有序，范围查询中不保证数据有序。
   

SQL优化

关联查询

• 关联查询，**小表驱动大表**，LEFT JOIN左边为驱动表，JOIN时会挑选小表作为驱动表，**被驱动表**的JOIN字段需要添加索引。
  

子查询

• IN子查询，需要对IN字段添加索引，最好将索引转换为Id查询，或优化为**JOIN查询**
  

ORDER BY和GROUP BY

1. **ORDER BY需要添加索引**，避免产生**using filesort**，
2. ORDER BY需要使用**LIMIT**，如果没有LIMIT则索引失效
3. ORDER BY需要保证**最左前缀原则**的使用，需要保证排序的顺序和索引顺序相同，同时需要保证ASC和DESC相同。尽量让**WHERE**和**ORDER BY**建立**联合索引**
4. GROUP BY本质是先进行排序后进行分组，因此GROUP BY的索引使用规则与ORDER BY相同
5. ORDER BY、GROUP BY、DISTINCT尽量不使用、消耗CPU、在应用层中做
   

其他

6. 考虑**覆盖索引**，**减少回表**次数
7. 字符串使用**前缀索引**
8. 开启**ICP索引下推**，在WHERE语句中有多个条件时，如果不开启则**每次查询完一个条件**后，都满足该条件的所有数据行**进行回表**。开启后会继续判断后面的条件，等后面判断完后才会统一进行回表操作。
   

索引合并（Index merge）

使用索引合并有什么体现

1. 在EXPLIAN中 type 显示为 **Index merge**
2. 同时extra信息中显示 **Using Intersection**、**Using Union**、**Using Sort Union**
   

Using Intersection（单列索引AND等值查询）

没有索引合并时的查询

1. 此时包含两个普通单列索引A和B，在没有索引合并时会有两种查询情况
2. 使用索引A根据条件筛选出**符合条件的有序id**，再通过id集合进行**回表**查询
3. 使用索引B根据条件筛选出符合条件的有序id，再根据id集合进行回表查询
   

索引合并时

1. 先使用索引A根据条件筛选出**符合条件的有序id**集合
2. 再使用索引B根据条件筛选出符合条件的有序id集合
3. 对集合取**交集**，（本质上MySQL不会保存id的集合而是实时进行判断）
4. 最终id的交集再进行**回表**查询

SELECT * FROM T WHERE A = 1 AND B = 2

Using Union（单列索引OR等值查询）

没有索引合并时的查询

1. 此时包含两个普通单列索引A和B，在没有索引合并的情况只能进行**全表扫描**
   

索引合并时

1. 首先使用索引A根据条件筛选出**符合条件的有序id**
2. 然后再使用索引B根据条件筛选出**符合条件的有序id**
3. 对两个集合取**并集**
4. 最后将最终的集合进行回表查询

SELECT * FROM T WHERE A = 1 OR B = 2

Using Sort Union（单列索引AND 其中一列范围查询）

没有索引合并时的查询

1. 此时包含两个普通单列索引A和B，在没有索引合并的情况下只能进行**全表扫描**
   

索引合并时

1. 使用索引A根据条件筛选出符合条件的**有序id**
2. 根据索引B，因为此时B是范围查询，因为二级索引是先根据二级索引的属性列进行排序，然后再对id进行排序，因此id仅在等值查询时有序，二级属性范围查询时是无序的，因此查询出来的是**无序的id集合**
3. 此时MySQL对索引B查询出来的id集合进行排序，此时**两个id集合都是有序**的
4. 对两个集合取**并集**
5. 最后将最终的集合进行回表查询

SELECT * FROM T WHERE A = 1 OR B > 2

Using Sort Intersection

1. MySQL不支持这种索引合并形式
2. 理解：Using Sort Union为取并集，通常都为小集合取并集，因此可以支持排序
3. Using Sort Intersection为取交集，通常都为大集合取交集，而大集合在排序时会很浪费资源