MySQL索引机制

MySQL索引机制

1.什么是索引?

要学习索引机制前，我们必须明白什么是索引，举个生活中的例子，当我们去逛商场的时候，我们想要买男装，假设男装在最顶层，如果我们一层层的去找，那效率是很低的，这个时候，我们应该找到商场的标牌，就知道了男装所处的位置，很明显，这样效率是很高的。索引在数据库中的作用就相当于商场中的标牌。

2.索引的优点和缺点

优点:

• 使用索引可以提高数据的检索速度(减少检索数据量)。
• 通过唯一性索引，可以保证每一行数据的唯一性

缺点:

• 创建索引和维护索引需要耗费很多的时间，当我们对表中的数据进行增删改操作时，如果数据有索引，索引需要进行动态的修改，会降低SQL的执行效率。
• 索引需要物理存储，需要耗费一定的物理空间。

3.索引的分类

主键索引:

我们在开发中，一般来说建表的时候都会有一个主键(不能为Null，且不能重复)，主键使用的就是主键索引，当MySQL的存储引擎为InnoDB时，当表中没有指定主键时，InnoDB会自动检查表中是否有唯一索引的字段，如果有，则选择该字段为默认的主键，否则InnoDB将会自动创建一个6Byte的自增主键。

辅助索引(二级索引的叶子节点存储的数据是主键):

• 唯一索引:唯一索引的列不能出现重复的数据，但是允许数据为Null，一张表可以有多个唯一索引，一般来说，创建唯一索引并不是为了提升查询效率，而是为了保证这列数据的唯一性。
• 全文索引:全文索引主要是为了检索大文本数据中的关键信息。
• 普通索引:普通索引可以提高查询数据的效率，一张表可以有多个普通索引。
• 前缀索引:前缀索引只适合于字符串类型的数据，前缀类型是对数据的前几个字符创建索引，相比于普通索引建立的数据更小。

4.索引的底层数据结构

Hash:

hash是键值对的集合，可以通过键(key)快速的找到对应的值(value)，时间复杂度接近O(1)

字段值的下标是通过hash算法随机算出来的，有时候可能会出现哈希冲突，为了解决哈希冲突，可以使用拉链法，将hash冲突数据存储到链表中。

面对当索引底层结构为hash表时，假设我们执行以下sql

select * from lstable where name=‘yyp’

对 yyp 进行hash运算，得到数组的下标，然后得到所对应的一列数据，进一步查询那列数据，得到最终的结果。

当我们执行以下sql时，

select * from lstable where name>‘yyp’

则不会有结果，因为哈希表支持精准的定位查询，但不支持范围查询和顺序查询

平衡二叉树:

假设有以下几个数据 12 27 29 35 38 48 55，我们看看这些数据的平衡二叉树

平衡二叉树的特性，左右两个子树的高度差不超过1，且左右子树都为平衡二叉树

当数据量为100w时，树的高度大约为20,这样的话，在最坏的情况下读取一个数组需要进行20次查找，每次查找都是一次IO操作，性能就成为了一个问题。

B-Tree:

同样以上面的数据为例子:

我们可以发现，B-Tree的一个节点可以有多个数据，当数据量越多的时候，那么树就越矮，效率就高

B+Tree

B+Tree是B-Tree基础上的优化，将非叶子节点冗余，提高范围查找的效率

1、所有的非叶子节点只存储关键字信息。

2、所有具体数据都存在叶子结点中。

3、所有的叶子结点中包含了全部元素的信息。

4、所有叶子节点之间都有一个链指针。

Mysql索引默认采取的数据结构就是B+Tree

5.聚集索引以及非聚集索引

聚集索引:

聚集索引是索引结构和数据一起存放的索引。主键索引就是聚集索引

优点:

• 聚集索引的查询速度非常的快，定位到索引的节点，就等于定位到了数据。

缺点:

• 依赖有序的顺序，因为B+Tree是多路平衡树，如果索引的数据不是有序的，那么插入时需要进行排序，如果数据是整型还好，否则类似于字符串或 UUID 这种又长又难比较的数据，插入或查找的速度肯定比较慢。
• 更新代价大,如果对索引列的数据进行修改时，那么对应的索引也要修改，聚集索引的叶子节点存放着data，如果要修改，代价很大。所以一般来说，主键一般是不可修改的。

非聚集索引:

非聚集索引是索引结构和数据分开放的索引。辅助索引就是非聚集索引

非聚簇索引的叶子节点不一定存储的是数据的指针(辅助索引的叶子节点存储的就是主键，然后根据主键在回表查询数据)。

优点:

• 更新代价比较小，因为非聚集索引的叶子节点是不存放数据的。

缺点:

• 依赖有序的数据。
• 可能会二次查询(回表):当查到索引对应的指针或主键时，可能需要根据指针或主键进行二次查询。

在这，我觉得很多朋友肯定有点好奇，为什么只是可能需要回表呢?明明存储的是指针和主键呀，接下来举个例子，大家看了就明白了

执行以下 SQL，假设height 字段建立了索引

select height from table where height=‘178’

索引的key就是height,直接将索引的key返回就可以了，不需要进行回表。

6.覆盖索引

如果一个索引包含了所有需要查询的字段的值，就是覆盖索引，这样就可以避免回表，提高效率

7.创建索引的注意事项

1.选择合适的字段做索引

• 不为Null的字段：索引的字段应该尽量不为Null，因为对于数据为Null的字段，数据库比较难优化。
• 被频繁查询的字段
• 被作为条件查询的字段
• 频繁需要排序的字段:索引已经排序，这样可以利用索引的排序，加快排序查询时间。
• 被经常频繁用于连接的字段，对于频繁被连接查询的字段，可以考虑建立索引，提高多表连接查询的效率。

2.被频繁更新的字段应该慎重建立索引(维护成本高)

3.尽可能的建立联合索引来代替单列索引。

• 索引需要占用磁盘空间，每个索引对应的就是一个B+Tree，如果一个表的列过多，索引过多，那么当字段过多时，将会占用很多空间，而且修改索引时，浪费时间将会很多，如果多个字段在一个索引上，那将会占用更少的空间且减少了修改索引的时间。

4.一个表尽量不要超过5个索引。

5.避免冗余索引

• 冗余索引指的是索引的功能相同，能够命中索引(a,b) 肯定能命中索引(a),索引(a)就是荣誉索引。在大多数情况下，都应该扩展现有的索引，而不是新建索引。

6.考虑在字符串类型上使用前缀索引(索引将会占用更小的空间)

7.索引优化建议

• 避免在where子句中对字段施加函数，这会造成索引无法命中。
• 尽量不要在重复数据多的列上使用索引。
• like的模糊查询以%开头，会导致索引失效。
• 删除长期未使用的索引。

8.MySQL如何为表字段添加索引

1.主键索引

ALTER TABLE `TABALE_NAME` ADD PRIMARY KEY(`column`)

2.唯一索引

ALTER TABLE `TABLE_NAME` ADD UNIQUE (`column`)

3.普通索引

ALTER TABLE `TABLE_NAME` ADD INDEX index_name (`column`)

4.全文索引

ALTER TABLE `TABLE_NAME` ADD FULLTEXT (`column`)

5.前缀索引

ALTER TABLE `TABLE_NAME` ADD INDEX title_pre (`column(number)`)

number代表前缀的长度

6.多列索引

ALTER TABLE `TABLE_NAME` ADD INDEX index_name (`column`,`column2`,`column3`)

ALTER TABLE `TABLE_NAME` ADD INDEX title_pre (`column(number)`)

number代表前缀的长度

6.多列索引

ALTER TABLE `TABLE_NAME` ADD INDEX index_name (`column`,`column2`,`column3`)