MySQL 索引是什么

时间：12-14来源：作者：点击数：

关于索引的比喻：索引就好比一本书的目录，它会让你更快的找到内容，显然目录（索引) 并不是越多越好，假如这本书 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)

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

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

为什么所以不设计成哈希而要设计成树呢?其实和 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不支持哈希索引 )

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

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

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

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