MySQL 索引

Question

关于索引的比喻：索引就好比一本书的目录，它会让你更快的找到内容，显然目录（索引) 并不是越多越好，假如这本书 1000 页，有 500 也是目录，它当然效率低，目录是要占纸张的，而索引是要占磁盘空间的。

概念：

MySQL​ 索引两种主要结构：

• hash : hash 索引在 mysql 比较少用，他以把数据的索引以 hash 形式组织起来，因此当查找某一条记录的时候，速度非常快.但是因为是 hash 结构，每个键只对应一个值，而且是散列的方式分布.所以他并 不支持范围查找和排序 等功能.
• B+树 : b+tree 是 mysql 使用 最频繁 的一个索引数据结构，数据结构以 平衡树 的形式来组织，因为是树型结构，所以更适合用来 处理排序 范围查找 等功能.相对 hash 索引，B+树在查找单条记录的速度虽然比不上 hash 索引，但是因为更适合排序等操作，所以他更受用户的欢迎.毕竟不可能只对数据库进行单条记录的操作.

常见的索引类型

• 聚簇索引 主键索引(PRIMARY KEY) : 它是一种特殊的唯一索引， 不允许有空值 ，不能重复，很少改变，经常被检索，不能太长，建议使用 snowflake 生成. ALTER TABLE table_name ADD PRIMARY KEY(column_name)
• 唯一索引(UNIQUE) : 与 普通索引 类似，不同的就是，索引列的值必须 唯一 ，但 允许有空值 . ALTER TABLE table_name ADD UNIQUE(column_name)
• 普通索引(INDEX) : 最基本的索引，没有任何限制 可以有重复 ，对数据按照索引的规则进行排序，加快查询速度. ALTER TABLE table_name ADD INDEX index_name(column_name)
• 全文索引(FULLTEXT) : 仅可用于 MyISAM 表，针对较大的数据，生成全文索引很耗时好空间. ALTER TABLE table_name ADD FULLTEXT(column_name) ，查询的时候应该使用 SELECT * FROM article WHERE MATCH(title, content) AGAINST('查询字符串')
• 组合索引(INDEX) : 为了更多的提高 mysql 效率可建立组合索引，遵循 最左前缀 原则. ALTER TABLE table_name ADD INDEX index_name(column1, column2, column3)

主键和唯一索引的区别

difference-between-key-primary-key-unique-key-and-index-in-mysql

• 主键是一种约束，唯一索引是一种索引，两者 本质 上是不同的
• 主键创建后一定包含一个唯一性索引，称为主键索引（是一个特殊的唯一索引)
• 主键不 能为空 ，唯一所以可以为空
• 一个表 最多 只能创建一个主键，但可以创建多个唯一索引
• 主键更适合那些不容易更改的唯一标识，如自动递增列、身份证号
• 主键可以被其他表引用为外键，而唯一索引不能

主键的相关问题

• InnoDB 建表时，可不可以不声明主键
  • 可以不声明主键，但必须要有聚簇索引（主键和聚集索引不是一个东西，不要混淆)
    • 有主键，主键是聚簇索引
    • 没有主键，首个非空唯一列是聚簇索引
    • 没有符合条件的列，内置生成一个 row_id 是聚簇索引
• InnoDB 建表时，可不可以不声明主键非空
  • 可以不声明主键非空，会自动加上非空限制
  • 如果没有设置自增的话，会使用默认值， int 会是 0
• InnoDB 建表时，可不可以选择多个字段做主键
  • 可以使用联合主键，组合列唯一即可
• InnoDB 插入时，可不可以主动插入自增主键
  • 可以指定自增列的值，但可能导致空洞
• InnoDB 建表时，可不可以使用联合自增主键
  • 可以，但自增 ID 必须在联合主键的第一列

唯一索引相关问题

• 主键不能为空，唯一索引可以为空
• 唯一索引允许有多个空值，参考 这里

几个为什么

为什么要存在索引

用于提升数据库的查找速度，如果没有索引就要通过遍历来查找数据，如果有索引可以通过索引缩小查找的范围

哈希(hash) 比树(tree) 更快 索引结构为什么要设计成树型

• 哈希，例如 HashMap， 查询/插入/修改/删除 的平均时间复杂度都是 O(1)
• 树，例如平衡二叉搜索树， 查询/插入/修改/删除 的平均时间复杂度都是 O(lg(n))

为什么所以不设计成哈希而要设计成树呢?其实和 sql 的需求有关，其本质是 tree 是有序的数据结构 方便范围查询 :

• 如果所有的查询都是进行单行查询 select * from t where name="shenjian"; hash 时间复杂度是 O(1) ，tree 时间复杂度是 O(lg(n)) ，哈希确实比树快多了( 如果业务仅仅需要查询单行记录，确实可以使用 hash 索引，效率更高 )
• 但是如果查询中存在 group by, order by, between, <、> 时,hash 时间复杂度会退化为 O(n) ,而有序型的 tree 时间复杂度是 O(log(n)) ，而范围查询在 sql 的时间里是很多的，所以索引选择了使用 tree 而不是 hash 作为索引的数据格式（另外 InnoDB不支持哈希索引 )

这么多树为什么选择 B+ tree

• 二叉搜索树
  • 当数据量大的时候，树的 高度会比较高 ，数据量大的时候，查询会比较慢
  • 每个节点只存储一个记录，可能导致一次查询有很多次磁盘 IO
• B 树（被创造出来就是为了解决索引的数据结构)( B for balance )
  • 不再是二叉搜索，而是 m 叉搜索
  • 叶子节点，非叶子节点，都存储数据
  • 中序遍历 ，可以获得所有节点
• B+树（在 B-tree 基础上做了优化的数据结构)
  • 和 b-tree 一样是 m 叉搜索
  • 仅叶子节点存储数据，非叶子节点不储存数据（仅储存在同一层的叶子节点中)
  • 叶子之间，增加了链表，获取所有节点，不再需要中序遍历

B+ 树比 B 树更优的原因:

• 范围查找更优: 定位 min 与 max 之后，链表两端的中间叶子节点，就是结果集，不用中序回溯( 范围查询在 SQL 中用得很多，这是 B+树比 B 树最大的优势 )
• 储存更加紧密: 叶子节点存储实际记录行，记录行相对比较 紧密的存储 ，适合大数据量磁盘存储
• 索引更小适合放进内存: 非叶子节点存储记录的 PK，用于查询加速，适合内存存储.不存储实际记录，而只存储记录的 KEY 的话，那么在 相同内存 的情况下，B+树能够存储更多索引

B 树和 B+树的特点

B+ tree 为什么非常适合数据库索引

• 很适合磁盘存储，能够充分利用局部性原理，磁盘预读
  • 局部性原理: 软件设计要尽量遵循"数据读取集中"与"使用到一个数据，大概率会使用其附近的数据"，这样磁盘预读能充分提高磁盘 IO
  • 磁盘预读的思路: 磁盘读写并 不是按需读取 ，而是 按页预读 ，一次会读一页的数据，每次加载更多的数据，以便未来减少磁盘 IO
• 很低的树高度，能够存储大量数据
• 索引本身占用的内存很小
• 能够很好的支持单点查询，范围查询，有序性查询

索引最佳实践

• MySQL best practices to follow as a developer

索引名

• 索引名的作用域是每个表，不能在同一个表中有相同的索引名称，但是可以在不同表中使用相同的索引名
• 索引名对性能没有影响
• 索引名对可读性影响很大，建议使用可读性更好的索引名

更新主键和唯一索引

根据 可知，主键和唯一所以都是可以更新的，原则上说 关系型数据库的全部数据都是允许更新的 ，但是这个操作一般是没有意义的（因为大部分的主键是使用无意义的数字作的)，另外修改主键会导致聚集索引重建浪费性能。