MySQL索引知识总结

MySQL （ 目前最流行的关系型数据库管理系统 ）

---

什么是索引?

• MySQL官方对索引的定义为：索引（Index）是帮助MySQL高效获取数据的数据结构。提取句子主干，就可以得到索引的本质：索引是数据结构，可以帮助我们快速的进行数据的查找

索引是什么样的数据结构？

• 索引的数据结构跟存储引擎有关，在MySql中使用较多的索引有B+数索引和哈希索引，我们常用的MYISAM和InnoDB存储引擎的默认索引实现为：B+树索引

Hash索引和B+树索引有什么区别或者说优劣呢?

• 首先要知道Hash索引和B+树索引的底层实现原理:

  hash索引底层就是hash表，进行查找时，调用一次hash函数就可以获取到相应的键值，之后进行回表查询获得实际数据；B+树底层实现是多路平衡查找树，对于每一次的查询都是从根节点出发，查找到叶子节点方可以获得所查键值，然后根据查询判断是否需要回表查询数据.

• 有以下的不同:

  1、hash索引进行等值查询更快(一般情况下),但是却无法进行范围查询（因为在hash索引中经过hash函数建立索引之后，索引的顺序与原顺序无法保持一致，不能支持范围查询。而B+树的的所有节点皆遵循(左节点小于父节点，右节点大于父节点，多叉树也类似)，天然支持范围）

  2、hash索引不支持使用索引进行排序，原理同上；

  3、hash索引不支持模糊查询以及多列索引的最左前缀匹配，原理也是因为hash函数的不可预测AAAA和AAAAB的索引没有相关性

  4、hash索引任何时候都避免不了回表查询数据，而B+树在符合某些条件(聚簇索引,覆盖索引等)的时候可以只通过索引完成查询

  5、hash索引虽然在等值查询上较快,但是不稳定。性能不可预测，当某个键值存在大量重复的时候，发生hash碰撞，此时效率可能极差。而B+树的查询效率比较稳定，对于所有的查询都是从根节点到叶子节点，且树的高度较低。

因此在大多数情况下，直接选择B+树索引可以获得稳定且较好的查询速度，而不需要使用hash索引

建立索引的原则有哪些？

• 选择唯一性索引，为经常需要查询、排序、分组和联合操作的字段建立索引，限制索引的数目，最左前缀匹配原则（非常重要），尽量选择区分度高的列作为索引，字段尽力设置不为null，索引列上不计算

主键、外键和唯一索引的区别

• 主键：唯一标识一条记录，不能有重复的，不允许为空

• 外键：表的外键是另一表的主键, 外键可以有重复的, 可以是空值

• 唯一索引：该字段没有重复值，但可以有空值

什么是回表查询和覆盖索引？

• 如果使用普通索引，则查询时间先查找到主键值，之后再通过主键值查找到自己想要的数据；需要扫码两遍索引树；这就是回表查询

• 索引覆盖：只需要在一棵索引树上就能获取SQL所需的所有列数据，无需回表

---

MySQL 支持哪些存储引擎？

MySQL 支持的存储引擎主要有四种

• InnoDB 支持事务，行级锁定和外键，是事务型数据库的首选引擎；MySQL5.5.5 之后的默认存储引擎

• MyISAM 拥有较高的插入、查询速度，但不支持事务。MySQL5.5.5 之前的默认存储引擎

• Memory 基于散列，存储在内存中，对临时表有用。常见的应用场景是：临时存放数据，数据量不大，并且不需要较高的数据安全性

• Archive 支持高并发的插入操作，但是本身不是事务安全的。常见的应用场景：存储归档数据，如记录日志信息可以使用 Archive

InnoDB 和 MyISAM 有什么区别呢？

• InnoDB 支持事务；而 MyISAM 不支持事物，强调的是性能，查询速度更快

• InnoDB 支持行级锁和表级锁（默认行级锁），而 MyISAM 只支持表级锁

• InnoDB 支持 MVCC, 而 MyISAM 不支持 MVCC

• InnoDB 支持外键，而 MyISAM 不支持外键

• InnoDB 早期版本不支持全文索引（从 MySQL5.6 开始支持全文索引），而 MyISAM 支持

• InnoDB 不保存表的具体行数，count () 时要扫描一遍整个表来计算有多少行；MyISAM 则内置了一个计数器，count () 时它直接从计数器中读

---

MySQL 索引底层是什么结构？为什么采用此结构？

• mysql 底层采用 B+ 树的存储结构，采用 B+ 树的原因：

  • 索引文件很大，不能全部存储在内存中，只能存储到磁盘上，因此索引的数据结构要尽量减少查找过程中磁盘 I/O 的存取次数（重点）

  • 数据库系统利用了磁盘预读原理和磁盘预读，将一个节点的大小设为等于一个页，这样每个节点只需要一次 I/O 就可以完全载入。而 B + 树的高度是 2~4，检索一次最多只需要访问 4 个节点（4 次，即树的高度）（ B + 树索引并不能直接找到具体的行，只是找到被查找行所在的页，然后 DB 通过把整页读入内存（磁盘预读），再在内存中查找（局部性原理，即当一个数据被用到时，其附近的数据也通常会马上被使用） ）

为什么不用 B 树？

• B+ 树所有的 Data 域在叶子节点，其余节点用来索引，而 B 树是每个索引节点都会有 Data 域；并且 B + 树所有叶子节点之间都有一个链指针。 这样遍历叶子节点就能获得全部数据，从而支持区分查询。在数据库中基于范围的查询是非常频繁的，而 B 树不支持这样的遍历操作

为什么不用哈希表？

• 哈希表只适用与查找等值查询， 不能支持区分条件（大于小于查询）、模糊查询等

• hash 索引虽然在等值查询上较快，但是不稳定，性能不可预测，当某个键值存在大量重复的时候，发生 hash 碰撞，此时效率可能极差，而 B + 树的查询效率比较稳定

为什么不用红黑树？

• 红黑树往往高度过大，从页造成磁盘 IO 读写过于频繁，效率低下。而且逻辑上很近的节点（父子）物理上可能很远，无法利用局部性原理

---

MySQL 支持的索引类型是哪些？

• 普通索引：用表中的普通列构建的索引，没有任何限制
• 唯一索引：唯一索引列的值必须唯一，但允许有空值。如果是组合索引，则列值的组合必须唯一
• 主键索引：是一种特殊的唯一索引，根据主键建立索引，不允许重复，不允许空值
• 全文索引：通过建立倒排索引，快速匹配文档的方式。MySQL 5.7.6 之前仅支持英文，MySQL 5.7.6 之后支持中文
• 组合索引：又叫联合索引。用多个列组合构建的索引，这多个列中的值不允许有空值。可以在创建表的时候指定，也可以修改表结构

聚集索引和非聚集索引？

• 聚集索引 (clustered index)，又称为主索引， MySQL里主键就是聚集索引 ，该索引中键值的逻辑顺序决定了表中相应行的物理顺序。因为数据真正的数据只能有一种排序方式，所以一个表上只能有一个聚簇索引

• 非聚集索引 (secondary index)，又称为辅助索引、普通索引，该索引的逻辑顺序与磁盘上行的物理存储顺序不同，一个表可以包含多个非聚集索引

• 在 InnoDB 中它们还有一个非常重要的区别：聚集索引的叶子节点的的 data 域包含了完整的数据记录，而非聚集索引的叶子节点的 data 域记录着主键的值，因此在使用非聚集索引进行查找时，需要先查找到主键值，然后再到聚集索引中进行查找，这称之为回表查询

索引什么时候会失效？

索引失效通常有以下原因：

• 条件中有 or

• like 查询且以 % 开头

• 如果列类型是字符串，那一定要在条件中将数据使用引号引用起来，否则不使用索引

• 对列进行函数运算（如 where md5 (password) = “xxxx”）

• 负向查询条件会导致无法使用索引，比如 NOT IN,NOT LIKE,!= 等

• 对于联合索引，不是使用的第一部分 (第一个)，则不会使用索引（最左匹配原则）

• 如果 mysql 评估使用全表扫描要比使用索引快，则不使用索引

索引有什么缺点？

• 索引需要额外的占用物理空间，索引越多，所以空间占用越多
• 当对表中的数据进行增加、删除和修改的时候，索引也要动态的维护，降低了数据的写入速度

使用索引查询一定能提高查询的性能吗？为什么

• 通常,通过索引查询数据比全表扫描要快，但是我们也必须注意到它的代价

• 索引需要空间来存储,也需要定期维护, 每当有记录在表中增减或索引列被修改时,索引本身也会被修改. 这意味着每条记录的INSERT,DELETE,UPDATE将为此多付出4,5 次的磁盘I/O。因为索引需要额外的存储空间和处理,那些不必要的索引反而会使查询反应时间变慢。使用索引查询不一定能提高查询性能，索引范围查询(INDEX RANGE SCAN)适用于两种情况:

  • 基于一个范围的检索,一般查询返回结果集小于表中记录数的30%
  • 基于非唯一性索引的检索

---

MySQL 中 Float、Decimal 类型存储金额的区别？

• Float 是非标准数据类型，存储金额时存储的是近似值，存在精度问题；Decimal 是标准数据类型，存储金额时存储的是精确值（以字符串的形式保存数值）
• 扩展： 在 MySQL 中，Float,Double 属于非标准数据类型，在 DB 中保存的是近似值；Decimal、Numeric 属于标准数据类型，在 DB 中以字符串的形式保存数值

Datetime、Timestamp 存储时间的区别？

• 两者的存储方式不一样 :

  • 对于TIMESTAMP，它把客户端插入的时间从当前时区转化为UTC（世界标准时间）进行存储。查询时，将其又转化为客户端当前时区进行返回。（ 对于跨时区的业务，TIMESTAMP更为合适 ）

  • 对于DATETIME，不做任何改变，基本上是原样输入和输出

  

• 两者所能存储的时间范围不一样:

  • timestamp所能存储的时间范围为：‘1970-01-01 00:00:01.000000’ 到 ‘2038-01-19 03:14:07.999999’
  • datetime所能存储的时间范围为：‘1000-01-01 00:00:00.000000’ 到 ‘9999-12-31 23:59:59.999999’

文章强烈推荐：

聚集索引与非聚集索引的总结
MySQL索引背后的数据结构及算法原理
MySQL索引原理及慢查询优化（美团）
InnoDB 简介
MySQL优化：如何避免回表查询？什么是索引覆盖？
官方文档解释MySQL最左匹配(最左前缀)原则