MySQL索引原理以及SQL优化

一、索引

索引是一种有序的存储结构，按照单个或者多个列的值进行排序，以提升搜索效率。

1.1 索引分类

1.1.1 按数据结构分类

1）B+树索引

使用 B-Tree 数据结构来存储索引数据，适用于等值查询、范围查询和排序操作。B-Tree 的非叶子节点只存储索引信息，叶子节点存储具体数据信息；叶子节点之间互相连接，方便范
围查询。

2）hash索引

hash索引适用于等值查询，但不支持范围查询和排序操作。hash索引通过hash函数将关键字映射到具体的索引位置，因此查找速度非常快。但hash索引无法保证数据的顺序性，也不支持模糊查询。

3）全文索引

全文索引适用于对文本内容进行搜索的场景。它可以在文本列上进行模糊匹配LIKE，并支持关键字的自然语言搜索、词语扩展和排除等功能。但是工作中通常不会使用 mysql 提供的全文索引，而是使用一个开源的分布式搜索和分析引擎Elasticsearch 。

1.1.2 按物理存储分类

1）聚集索引（聚簇索引）
InnoDB的聚簇索引就是按照主键顺序构建 B+Tree结构。叶子节点存储数据行的数据。

2）辅助索引（二级索引）
InnoDB的辅助索引就是按照索引列构建 B+Tree结构。叶子节点存储的是索引列的值以及对应行的主键值

聚簇索引和二级索引的主要区别在于叶子节点是否存储数据。聚簇索引的叶子节点存储数据行的数据，因此找到了索引便找到了具体数据文件。由于数据和索引存储在一起，聚簇索引可以极大地提高主键查找的效率。

需要注意的是，由于聚簇索引决定了数据行的物理存储顺序，因此对聚簇索引的插入和更新操作可能会导致数据页的拆分和重新组织，进而引起额外的性能开销。

例：范围查找聚集索引 primary_key -> (18, 40)

为什么要有二级索引？
聚簇索引的叶子节点存储了一行完整的数据，而二级索引只存储了主键值，相比于聚簇索引，占用的空间要少。当我们需要为表建立多个索引时，如果都是聚簇索引，那将占用大量内存空间，所以InnoDB中主键所建立的是聚簇索引，而唯一索引、普通索引、前缀索引等都是二级索引。
如果需要查询对应的数据行，需要回表查询，即在聚簇索引中进一步查找对应的数据行。这样可以避免在行移动或者插入新数据时出现的页分裂问题。

例：查找辅助索引 key = 33 的行记录

1.1.3 按列属性分类

1）主键索引
非空唯一索引，一个表只有一个主键索引；在 innodb 中，主键索引的 B+ 树包含表数据信息

PRIMARY KEY(key) 

主键选择
innoDB 表是组织索引表，主键对应聚集索引 B+ 树，所有的数据都存储其中。每张表有且仅有一个主键。

• 如果显示设置 PRIMARY KEY ，则该设置的key为该表的主键；
• 如果没有显示设置，则从非空唯一索引中选择；
  $\circ$ 只有一个非空唯一索引，则选择该索引为主键；
  $\circ$ 有多个非空唯一索引，则选择声明的第一个为主键；
• 没有非空唯一索引，则自动生成一个 6 字节的 _rowid 作为主键；

2）唯一索引
不可以出现相同的值，可以有NULL值。

UNIQUE(key) 

3）普通索引
允许出现相同的索引内容。

INDEX(key)
-- OR
KEY(key[,...])

4）前缀索引
在某些情况下，索引列的值可能较长，例如字符串类型的列。如果对整个值进行索引可能会占用大量的存储空间，降低查询性能。为了解决这个问题，可以使用前缀索引。前缀索引允许只对索引列的前几个字符进行索引，而不是对整个值进行索引。

-- 向名为"user"的表中添加名为"name"的列的前四个字符的索引
ALTER TABLE `student` ADD KEY(name(4));
-- 注意：前缀索引不能做 order by 和 group by

1.1.4 按列的个数索引

1）单列索引
对表上的单个列进行索引

2）组合索引
对表上的多个列进行索引

INDEX idx(key1,key2[,...]);
UNIQUE(key1,key2[,...]);
PRIMARY KEY(key1,key2[,...]);

1.2 索引的代价

1）存储空间：索引需要占用额外的存储空间。对于大型表和多个索引的情况，索引可能会占据相当大的存储空间。

2）降低DML效率：当对表进行DML（插入、更新或删除）操作时，索引也需要更新，因为需要动态维护B+树的索引有序性。

3）更新代价：对于具有多个索引的表，在更新操作时，可能需要同时更新多个索引。这可能会增加更新操作的时间和资源消耗。

4）索引维护成本：索引需要定期进行维护，以确保其保持有效和高效。这可能涉及到重新构建索引、重建统计信息等操作。

5）查询优化器的复杂性：有多个索引可供选择时，查询优化器必须选择最优的索引来执行查询。这可能会增加查询优化的复杂性和计算成本。

1.3 索引的使用场景

1）使用where条件判断时，会使用索引。

2）使用group by 和 order by，会判断后面的列是否创建了索引，如果创建了就会使用索引对应的B+树。

1.4 不使用索引的场景

1）没有涉及到where、grop by、order by的使用，不用创建索引。

2）区分度不高的列，没必要使用索引。

3）经常修改的列，不要创建索引；因为维护代价太高。

4）表的数据量少，没必要创建索引。

二、索引的实现原理

2.1 索引存储

innodb由段、区、页组成；段分为数据段、索引段、回滚段等。区大小为 1 MB（一个区由64个连续页构成）；页的默认值为16k；页为逻辑页，磁盘物理页大小一般为 4K 或者 8K；为了保证区中的页的连续，存储引擎一般一次从磁盘中申请 4~5 个区。

2.2 页

记录是按行存储的，数据是按数据页读写的

页是 innodb 磁盘管理的最小单位；默认16k，对应 4 个物理磁盘页。可通过 innodb_page_size 参数来修改；B+ 树的一个节点的大小就是该页的值。

数据页之间通过双向链表的形式组织起来，逻辑上连续，物理上不连续。数据页内包含用户记录，每个记录之间用单向链表的方式组织起来，为了加快在数据页内高效查询记录，设计了一个页目录，页目录存储各个槽（分组），且主键值是有序的，于是可以通过二分查找法的方式进行检索从而提高效率。

2.3 InnoDB中的B+树

InnoDB 是 MySQL 数据库的默认存储引擎，它使用了 B+ 树作为索引结构来组织和存储数据。
B+树指的是多路平衡搜索树，用来组织磁盘数据，减少磁盘访问次数。B+树组织磁盘数据是以页为单位，物理磁盘页一般为4K，innodb 默认页大小为 16K；对页的访问是一次磁盘io，缓存中会缓存常访问的页。

每个索引对应着一个 B+ 树。B+树的一个节点对应一个数据页；B+树的层越高，那么要读取到内存的数据页越多，io次数越多。

1）InnoDB中B+树的特征

$\circ$ 多路平衡搜索树
$\circ$ 非叶子结点只存储索引信息，叶子节点存储具体数据信息
$\circ$ 所有的叶子节点都在同一层，且叶子节点间构成一个双向链表，方便范围查询。
$\circ$ 节点的大小都是数据页的大小 (16KB) ，对应 4 个物理磁盘页 (4KB)

2）为什么 MySQL InnoDB 选择 B+ 树作为索引的数据结构？

$\circ$ 降低磁盘IO：B+树的非叶节点只包含键，而不包含真实数据，因此每个节点可以存储更多的记录个数。所以B+树的高度更低，访问时所需要的IO次数更少。此外，由于每个节点存储的记录数更多，所以对访问局部性原理的利用更好，缓存命中率更高。相比之下，红黑树在磁盘上的存储方式相对随机，导致磁盘 I/O 操作更频繁。哈希表则不适合直接存储在磁盘上，因为哈希表需要通过哈希函数计算位置，无法充分利用磁盘的顺序读写特性。

$\circ$ 范围查询：B+树的叶子节点间构成一个双向链表，范围查询只需要对链表进行遍历即可。相比之下，红黑树和哈希表无法提供高效的范围查询支持。红黑树虽然能够支持有序访问，但在范围查询时需要遍历整个树，性能较差。而哈希表是基于哈希函数进行快速查找的，适用于单个键值查询，但对于范围查询则需要扫描整个表格，效率较低。

2.4 InnoDB的体系结构

1）Buffer Pool（缓冲池）
Buffer Pool是一个用于存储数据和索引页的内存区域，它以固定大小的页为单位进行管理，通常以16KB为一页。它的作用是采用 LRU 算法将最常用的数据页（热点数据）保留在内存中，以减少对磁盘IO的需求。当数据被查询或更新时，首先通过自适应hash索引查询数据是否在buffer pool中；如果数据不在，则通过mmap将磁盘数据映射到buffer pool中；如果数据存在buffer pool中就直接操作。

2）Change Buffer（变更缓冲）
Change buffer 缓存非唯一索引的数据变更（DML操作），Change buffer 中的数据将会异步merge 到磁盘当中。当执行更新操作（如插入、更新、删除）时，InnoDB会将修改的数据页（包括数据和索引页）首先写入到Change Buffer中，而不是直接写入磁盘。Change Buffer是一个内存中的缓冲区，用于暂时存储待写入的修改操作。

3）Change Buffer和Buffer Pool的区别
尽管Change Buffer和Buffer Pool都是内存中的缓存区域，但它们的功能和使用场景有所不同：
Change Buffer主要针对的是更新操作。它通过缓存待写入的修改操作，减少磁盘IO次数，提高写入性能。特别适用于批量插入或大规模更新操作，以及辅助索引的维护。
Buffer Pool主要针对的是读取操作。它通过将常用的数据页保留在内存中，减少磁盘IO需求，提高查询性能。特别适用于频繁的读取操作，可以根据工作负载和系统资源设置合适的大小。

4）innoDB 对于非聚集索引的 DML 操作流程
innoDB 对于非聚集索引的 DML 操作，不是每次直接插入到索引页，而是先判断插入的非聚集索引页（辅助索引页）是否在 buffer pool 中，若存在，则直接插入；若不在，则先放入 change buffer。然后通过定期对 change buffer 和 buffer pool 中辅助索引叶子节点的 merge 操作，将多个插入合并到一个操作（因为在同一非聚集索引页），这样就大大提升了对于非聚集索引插入的性能。

2.5 最左匹配原则

最左匹配原则是指在数据库查询中，当存在联合索引（Composite Index）时，索引会按照从左到右的顺序进行匹配，遇到 > < between like 就停止匹配。

最左匹配原则的好处在于可以充分利用联合索引的有序性，提高查询效率。例如，假设有一个联合索引 (A, B, C)，查询条件为 WHERE A = 1 AND B = 2，那么索引可以直接定位到满足这两个条件的数据。但如果查询条件为 WHERE B = 2 AND C = 3，虽然索引中包含了 B 和 C 这两列，但由于查询条件无法匹配最左前缀 A，索引无法被使用。

需要注意的是，最左匹配原则仅适用于联合索引，对于单列索引则没有影响。另外，尽量将使用频率较高且区分度高的列放在联合索引的最左边，这样可以进一步提高查询效率。

2.6 覆盖索引

覆盖索引是一种数据查询方式，主要针对辅助索引。从辅助索引中就能找到数据，而不需通过聚集索引查找；利用辅助索引树高度一般低于聚集索引树， 可以较少的磁盘 IO。也就是，如果查询的字段是辅助索引，那么查询过程中就不需要回表查询，直接使用辅助索引B+树就可以查询到数据。

DROP TABLE IF EXISTS `covering_index_t`;
CREATE TABLE `covering_index_t` (
	`id` INT(11) NOT NULL AUTO_INCREMENT,
	`name` VARCHAR(255) DEFAULT NULL,
	`cid` INT(11) DEFAULT NULL,
	`age` SMALLINT DEFAULT 0,
	`score` SMALLINT DEFAULT 0,
	PRIMARY KEY (`id`),
	KEY `name_cid_idx` (`name`, `cid`)
)ENGINE = INNODB AUTO_INCREMENT=0 DEFAULT CHARSET = utf8;


INSERT INTO `covering_index_t` (`name`, `cid`, `age`, `score`)
VALUES
	('mark', 10001, 12, 99),
	('jack', 10002, 13, 98),
	('rose', 10003, 14, 97),
	('tom', 10004, 15, 100);

SHOW INDEX FROM `covering_index_t`;
-- 不会走索引
EXPLAIN SELECT * FROM `left_match_t` WHERE `cid` = 10001;
-- 需要回表查询
EXPLAIN SELECT * FROM `covering_index_t` WHERE `name` = 'tom';
-- 查询字段是辅助索引（`name`, `cid`, `id`），不需要回表查询
EXPLAIN SELECT `name`, `cid`, `id` FROM `covering_index_t` WHERE `name` = 'tom';

在使用中，尽量不要使用select * from …来获取数据；因为里面有些字段可能没有创建索引，没有创建索引就需要回表查询，这会增加磁盘IO。所以，在select中尽量写所需的字段。

三、索引失效

1）左模糊：LIKE 模糊查询，通配符 % 开头，则索引失效；例如： select * from user where name like’%Mark’;

2）索引字段参与运算：使用函数、进行表达式运算、隐式类型转换等操作，索引失效

3）在索引字段上使用 NOT <> != 索引失效；如果判断 id <> 0 则修改为 idx > 0 or idx < 0 ；

4）组合索引中，没使用第一列索引，索引失效；如select … where A and B 若 A 和 B 中有一个不包含索引，则索引失效。

5）WHERE子句：OR操作符，如果其中一个条件不可使用索引进行优化，整个条件将被认为是非索引化的。
IN子查询，如果子查询的结果不能使用索引进行匹配，那么整个IN子查询将被认为是非索引化的。

DROP TABLE IF EXISTS `index_failure_t`;
CREATE TABLE `index_failure_t` (
	`id` INT(11) NOT NULL AUTO_INCREMENT,
	`name` VARCHAR(255) DEFAULT NULL,
	`cid` INT(11) DEFAULT NULL,
	`score` SMALLINT DEFAULT 0,
	`phonenumber` VARCHAR(20),
	PRIMARY KEY (`id`),
	KEY `name_idx` (`name`, `cid`), -- 尽量使用联合索引
	KEY `phone_idx` (`cid`)
) ENGINE = innoDB AUTO_INCREMENT=0 DEFAULT CHARSET = utf8;

INSERT INTO `index_failure_t` (`name`, `cid`, `score`, `phonenumber`) 
VALUES
	('王思思', 1001, 98, '15801100142'),
	('张三', 1002, 95, '15801101135'),
	('诸葛李四', 1003, 96, '15801100111');
	
SHOW INDEX FROM `index_failure_t`;

SELECT * FROM `index_failure_t`;
-- 1、左模糊匹配
EXPLAIN SELECT * FROM `index_failure_t` WHERE name LIKE '%思'; -- 索引失效
EXPLAIN SELECT * FROM `index_failure_t` WHERE name LIKE '诸%'; -- 右模糊匹配，索引成功

-- 2、索引参与运算
EXPLAIN SELECT * FROM `index_failure_t` WHERE LENGTH(name) = 9; -- 索引失效
EXPLAIN SELECT * FROM `index_failure_t` WHERE `id` + 1 = 3;	    -- 索引失效
EXPLAIN SELECT * FROM `index_failure_t` WHERE `score` BETWEEN 95 AND 96;  -- 索引失效
EXPLAIN SELECT * FROM `index_failure_t` WHERE `id` = 3 - 1;	    
--  隐式转换：mysql 遇到字符串和数字比较时，会自动将字符串转换为数字
EXPLAIN SELECT * FROM `index_failure_t` WHERE `phonenumber` = 15801100142; -- 索引失效
-- 等价于：EXPLAIN SELECT * FROM `index_failure_t` WHERE CAST(`phonenumber` AS SIGNED INT) = 15801100142; 

-- 3、where: or 非索引 | in 子查询
EXPLAIN SELECT * FROM `index_failure_t` WHERE `cid` = '1002' or `score` = 95; -- `score`没有创建索引，索引失效
EXPLAIN SELECT * FROM `index_failure_t` WHERE `cid` = '1002' or `score` BETWEEN 95 AND 96;  -- 范围查询无法索引，索引失效
EXPLAIN SELECT * FROM `index_failure_t` WHERE `cid` IN (SELECT `cid` FROM `index_failure_t` WHERE `score` > 95); -- 索引失效

四、索引优化

4.1 索引原则

1）查询频次较高且数据量大的表建立索引；索引选择使用频次较高，过滤效果好的列或者组合。

2）使用短索引；例如，如果某一列的取值范围很小，可以考虑使用较短的数据类型，如SMALLINT或TINYINT，而不是使用INT或BIGINT。

3）对于很长的动态字符串，考虑使用前缀索引；
有时候需要索引很长的字符串，这会让索引变的大且慢，通常情况下可以使用某个列开始的部分字符串，这样大大的节约索引空间，从而提高索引效率，但这会降低索引的区分度，索引的区分度是指不重复的索引值和数据表记录总数的比值。索引的区分度越高则查询效率越高，因为区分度更高的索引可以让mysql在查找的时候过滤掉更多的行。对于 BLOB , TEXT ,VARCHAR 类型的列，必要时使用前缀索引，因为mysql 不允许索引这些列的完整长度，使用该方法的诀窍在于要选择足够长的前缀以保证较高的区分度。

select count(distinct left(name,3))/count(*) as sel3,
count(distinct left(name,4))/count(*) as sel4,
count(distinct left(name,5))/count(*) as sel5,
count(distinct left(name,6))/count(*) as sel6,
from user;
alter table user add key(name(4));
-- 注意：前缀索引不能做 order by 和 group by

4）对于组合索引，考虑最左侧匹配原则和覆盖索引；

5）尽量选择区分度高的列作为索引；该列的值相同的越少越好；

6）不要 select * ； 尽量只列出需要的列字段；方便使用覆盖索引；

7）索引列，列尽量设置为非空；

8）可选：开启自适应 hash 索引或者调整 change buffer

select @@innodb_adaptive_hash_index;
set global innodb_adaptive_hash_index=1; -- 默认是开启的
select @@innodb_change_buffer_max_size;
-- 默认值为25 表示最多使用1/4的缓冲池内存空间 最大值为50
set global innodb_change_buffer_max_size=30；

4.2 SQL 优化

找到 sql 语句

• show processlist：可以查看此时线上运行的 sql 语句；

• 开启慢查询日志

分析 sql 语句

• 索引 where, group by, order by

• 优化sql 语句：in 优化成联合查询，减少联合查询

工作当中不要使用 age 字段，而是存储生日年月日

---