MySQL学习笔记（3）——MySQL的索引

三、MySQL的索引

1、MySQL索引的类型主要有哪些_？

B-Tree索引、哈希索引、空间数据索引（R-Tree）、全文索引、其他索引（聚簇索引、覆盖索引等）。

• B-Tree索引：一般指按照B+树结构存储数据的索引。
• 哈希索引：基于Hash表实现，只有精确匹配索引所有列才有效。
• 空间数据索引：空间数据索引会从所有维度来索引数据，用作地理数据存储。
• 全文索引：全文索引查找的是文中的关键词，而不是直接比较索引列的值。
• 聚簇索引：聚簇索引不是单独的一种索引类型，而是一种数据存储方式。聚簇索引实现依赖于存储引擎，InnoDB的聚簇索引叶子页存放的是数据行，一个表只有一个聚簇索引。
• 覆盖索引：一个索引包含（或覆盖）所有需要查询的字段的值，即为覆盖索引。覆盖索引的叶子节点包含了要查询的数据，就可以减少一次回表查询。

2、常见存储引擎与其支持的索引？

• B-Tree索引：InnoDB，MyISAM，Memory，NDB（大部分存储引擎都支持）
• 哈希索引： Memory、NDB
• 空间数据索引：mysql功能并不完善， PostgreSQL数据库PostGIS较好
• 全文索引：MyISAM
• 聚簇索引： InnoDB
• 覆盖索引：InnoDB

3、MyIASM与InnoDB的B-Tree索引区别

B-Tree索引底层一般通过B+树实现，MyISAM和InnoDB都支持B-Tree索引，但是二者也有不同：

• MyIASM使用前缀压缩技术索引更小，InnoDB按原数据格式存储；
• MyISAM通过数据的物理位置引用被索引的行，InnoDB根据主键引用被索引的行

4、如何创建一个B-Tree索引

4.1 建表时新建索引

-- PRIMARY KEY指定主键，INDEX指定索引
CREATE TABLE IF NOT EXISTS People(
id VARCHAR(64) NOT NULL,
last_name VARCHAR(50) NOT NULL,
first_name VARCHAR(50) NOT NULL,
dob DATE NOT NULL,
gender ENUM('m', 'f') NOT NULL,
PRIMARY KEY (id),
INDEX idx_last_name_first_name_dob (last_name, first_name, dob) USING BTREE
)

4.2 建表后新增索引

ALTER TABLE People ADD INDEX idx_gender (gender) USING BTREE;

4.3 删除索引

DROP INDEX index_name ON table_name ;
ALTER TABLE table_name DROP INDEX index_name ;
ALTER TABLE table_name DROP PRIMARY KEY ;

5、KEY与INDEX关键字的区别

KEY与INDEX都可以创建一个索引，但是二者之前仍有却别

5.1 KEY关键字

KEY包含两层含义：1. 约束（偏重于约束和规范数据库的结构完整性）；2. 索引（辅助查询用），比如

• primary key：一是约束作用（constraint)，用来规范一个存储主键和唯一性，二是同时在此key上建立了一个index。
• unique key：一是约束作用（constraint)，规范数据的唯一性，二是同时在此key上建立了一个index。
• foreign key ：一是约束作用（constraint)，规范数据的引用完整性，二是同时在此key上建立了一个index。

5.2 INDEX关键字

INDEX只有一层意思，即建立索引，它只是辅助查询的，它创建时会在另外的表空间（mysql中的innodb表空间）以一个类似目录的结构存储。上面说的索引都是纯粹指INDEX。

6、 索引的底层实现B+树

B+树是B树升级版，所以先介绍一下B树

6.1 什么是B树？

B树，是一种平衡的多叉查找树（类比于平衡二叉查找树，B树有多个子节点）。

B树特点如下：

• 所有键值分布在整个树中，所有的叶子结点都位于同一层。
• 任何关键字出现且只出现在一个节点中
• 搜索有可能在非叶子节点结束（非叶子节点存放键值+数据）
• 在关键字全集内做一次查找，性能逼近二分查找算法

6.2 什么是B+树？

B+树是B-树的变体，也是一种多路搜索树, 它与 B- 树的不同之处在于:

1. B+树的非叶子节点不保存数据，所有数据被保存在叶子节点中
2. B+树为所有叶子结点增加了一个指针，形成一个链表

6.3 为什么使用B树、B+树这类数据结构？

大量数据通常是存储在磁盘中，每次使用时会加载一部分到内存中。每次从磁盘加载一次数据就会涉及一次磁盘I/O，磁盘I/O的效率比内存I/O低很多。因此要尽可能减少磁盘I/O次数。磁盘读取完需要的数据后，会按顺序再多读一部分数据到内存中，这种磁盘预读是依据计算机中的局部性原理：

• 当一个数据被用到时，其附近的数据也通常会马上被使用
• 程序运行期间所需要的数据通常比较集中

磁盘预读可以提高I/O效率，预读的长度一般为页(page)的整倍数，即磁盘按页读取。

B树这类数据结构借助计算机磁盘预读的机制，每次新建节点时，直接申请一个页的空间，这样就保证一个节点物理上也存储在一个页里，加之计算机存储分配都是按页对齐的，就实现了一个结点只需一次磁盘I/O。

假设查找树高度为h，要找到一个节点至少要查找h-1次，因此要减少磁盘I/O次数必须尽可能压缩树的高度。而B树和B+树就满足此类特征，一般一颗b+树索引的高度为3-4，只需经过3-4次磁盘I/O就可以找到数据。

6.4 B+树比B树的优势

• B+树的高度更小：B+树的非叶子节点不存放数据，这样就可以存放更多关键字，压缩树的高度；
• B+树的查询速度稳定：B+树只有叶子节点存放数据，要取得数据必须访问叶子节点，时间复杂度为O(logn)。而B树非叶子节点也存放数据，查询速度不稳定
• B+树具有天然排序能力：B+树所有叶子节点由指针相连构成有序链表
• B+树遍历更快：B+树直接使用叶子节点的链表完成遍历

6.5 B+树比二叉查找树、平衡二叉树、红黑树等的优势

• 二叉查找树：极端退化为链表，查询性能低
• 平衡二叉树：维护代价很高，大数据量下树高度太大
• 红黑树：特殊的平衡二叉树，降低了维护代价，但是大数据量下树高度太大

7、一棵B+树可以存放多少行数据？

大约2千万行数据

• 在计算机中，磁盘存储数据最小单元是扇区，一个扇区的大小是512字节。
• 文件系统中，最小单位是块，一个块大小就是4k;
• InnoDB存储引擎最小储存单元是页，一页大小就是16k。

• 如果一行记录的数据大小为1k，那么单个叶子节点可以存的记录数 =16k/1k =16.
• 非叶子节点内存放多少指针呢?我们假设主键ID为bigint类型，长度为8字节，而指针大小在InnoDB源码中设置为6字节，所以就是8+6=14字节，16k/14B =16*1024B/14B = 1170

因此，一棵高度为2的B+树，能存放1170 * 16=18720条这样的数据记录。同理一棵高度为3的B+树，能存放1170 *1170 *16 =21902400，也就是说，可以存放两千万左右的记录。

一个高度为h的B+树可存放1170 ^(h-1)*16条数据。

8、索引匹配规则

以4中创建索引为例：

• 全值匹配：指和索引中的列全部进行匹配

  -- last_name,first_name和dob是联合索引，按顺序匹配所有列
  SELECT * FROM People WHERE last_name = 'san' AND first_name = 'zhang' AND dob = '2022-04-02';
  

• 最左匹配：可以匹配按顺序从左到右中的一列，也可以匹配一列中的前缀。

  -- 只匹配联合索引中的last_name列
  SELECT * FROM People WHERE last_name = 'san';
  -- 可以只匹配last_name列中的部分前缀
  SELECT * FROM People WHERE last_name LIKE 's%';
  -- 如果LIKE前缀有%索引失效
  
  

• 匹配范围值：匹配在一定范围内的值

  -- last_name从a到z之间的值
  SELECT * FROM People WHERE last_name BETWEEN 'a' AND 'z';
  

• 精确匹配某一列并范围匹配另一列：从左到右顺序，精确匹配一列并范围匹配另一列

  -- 只匹配联合索引中的last_name列
  SELECT * FROM People WHERE last_name = 'san' AND first_name LIKE 'z%';
  

9、常见索引失效场景

• 不符合最左匹配原则

  如果不是按照索引的最左列开始查找，则无法使用索引。

  如前面last_name，first_name和dob共同构成联合索引，如果跳过last_name，直接从first_name开始查找则无法使用索引。可以使用EXPLAIN查看SQL执行过程信息：

  按顺序查找，输出possible_keys为索引信息

  -- 按顺序检索三列
  EXPLAIN (SELECT * FROM People WHERE last_name = 'san' AND first_name = 'zhang' AND dob = '2022-04-02');
  

  

  -- 跳过last_name直接查找first_name和dob，索引失效
  EXPLAIN (SELECT * FROM People WHERE first_name = 'zhang' AND dob = '2022-04-02');
  

  

• 不能跳过索引中的列

  如果跳过索引中间的列，则只能使用索引的第一列

  -- 只使用了last_name索引查询，然后对结果集查找，如果last_name过滤后结果集很大，性能很慢
  SELECT * FROM People WHERE last_name = 'san' AND dob = '2022-04-02'
  

• 某列进行范围查询，其后的列都无法再使用索引优化

  -- 对first_name使用范围查询，dob列无法再使用索引
  SELECT * FROM People WHERE last_name = 'san' AND first_name LIKE 'z%' AND dob = '2022-04-02'
  

• 表达式和函数参数不能使用索引

  -- last_name作为表达式，无法处理索引，索引失效
  SELECT * FROM People WHERE CONCAT(last_name, '_test') =  'san_test'
  

  

• or语句前后没有同时使用索引，索引失效

  当or左右查询字段只有一个是索引，该索引失效，只有当or左右查询字段均为索引时，才会生效;

• where中在索引字段上使用not，<>，!=。

  不等于操作符是永远不会用到索引的，因此对它的处理只会产生全表扫描。

10、高性能索引策略

• 独立的列

  索引的列不能是表达式或参数的一部分，否则索引失效

• 前缀索引

  如果索引的列很长，可以考虑使用前缀索引。

  前缀索引无法使用ORDER BY和GROUP BY，且无法使用覆盖扫描，减少回表次数。

• 多列索引

  如果多个字段的区分性更好可以考虑使用多列索引（联合索引）。

  因为为多个列分别建立索引大多数情况下不能提高mysql性能。因为多个单列索引最后结果还需要进行合并（取交集或并集）。

• 选择合适的索引顺序

  1. 将区分度（索引选择性）最高的列放在索引顺序前面
  2. 满足最左匹配规则

• 索引顺序和排序顺序一致

  只有当索引的列顺序和ORDER BY顺序一致时，并且所有列的排序方向都一致（都是正序或倒序），MYSQL才能使用索引进行排序。否则，每次扫描到一次索引都需要回表查询一次对应的行，属于随机I/O性能低。

• 删除冗余和未使用的索引

  维护索引消耗一些性能，应该及时识别并删除未使用索引。

11、聚簇索引

11.1 什么是聚簇索引？

聚簇索引是InnoDB的一种数据存储策略，即InnoDB将所有的数据存储在聚簇索引的叶子节点，聚簇索引的键值是主键，主键选取策略如下：

• 当指定PRIMARY KEY时，选取该列作为主键
• 未指定PRIMARY KEY但指定UNIQUE KEY时，选择UNIQUE KEY作为主键
• 都未指定时，使用隐藏字段DB_ROW_ID作为主键

11.2 聚簇索引与普通索引区别？

聚簇索引的叶子节点存放的是数据行；

普通索引的叶子节点存放的是索引列和主键ID，当使用普通索引查询数据行时，先从普通索引查询到主键，再根据主键到聚簇索引做一次回表查询，得到数据行。

12、覆盖索引

一个索引包含（或覆盖）所有需要查询的字段的值，即为覆盖索引。覆盖索引的叶子节点包含了要查询的数据，就可以减少一次回表查询。

覆盖索引必须要存储索引的值，而哈希索引，空间索引和全文索引都不存储索引的值，索引无法使用覆盖索引。使用EXPLAIN命令可以查看是否是覆盖索引查询，如果Extra字段中包含USING INDEX则表明使用覆盖索引

EXPLAIN (SELECT last_name, first_name, dob FROM People WHERE last_name = 'san' AND first_name = 'zhang' AND dob = '2022-04-02');

13、哈希索引

哈希索引基于hash表实现，只有精确匹配才能生效。Memory引擎支持

13.1 创建hash索引

CREATE TABLE testhash(
	fname VARCHAR(50) NOT NULL,
    lname VARCHAR(50) NOT NULL,
    KEY USING HASH(fname)
) ENGINE=MEMORY;

13.2 hash索引的限制

• 哈希索引只存储哈希值和行指针，必须回表才能读取行
• 哈希索引不是按索引值顺序存储，无法进行排序
• 哈希索引不支持最左匹配原则，必须按列全部内容的哈希值查找。
• 哈希索引只支持等值查找（=、IN、!=），不支持范围匹配。
• 哈希冲突后必须遍历链表的行指针进行查询（类似于HashMap）

13.3 InnoDB如何使用哈希索引？

InnoDB不支持哈希索引，但是可以使用哈希函数（如CRC32()函数值作为哈希值），单独增加一列模拟哈希索引