mysql索引4种结构_MySQL的索引结构

1.索引基础

索引是存储引擎用于快速找到记录的一种数据结构。存储引擎先在索引中找到对应的值，然后根据匹配的索引记录找到对应的数据行。索引可以包含一个列或者多个列的值，如果索引包含多个列，那么列的顺序就会变得比较重要了。

2.索引的优缺点

2.1优点

1. 提高了查询速度

2. 减少了数据读取操作(IO)

3. 降低排序和分组的成本(CPU)

2.2缺点

1. 占用了大量的存储空间

2. insert、update和delete等操作会消耗大量的系统开销

2.3是否使用索引

1. 对于非常小的表，大部分情况下简单的全表扫描更高效；

2. 对于中到大型的表，索引非常有效；

3. 对于特别大型的表，建立和使用索引的代价就随之增长，需要一种技术可以直接区分出查询需要的一组数据，而不是一条记录一条记录的匹配，可以使用分区技术。

4. 如果表的数量特别多，可以建立一个元数据信息表，用来查询需要用到的某些特征。

3.索引的类型

根据存储结构的不同，将索引分为两种：B-Tree索引和哈希索引。

3.1B-Tree索引

1. 平衡的多路查找树

2. 所有的值都是按顺序存储

3. 每一个叶子页到根的距离相同

4. 查找过程：查找节点+节点内查找，性能等价于在键值的集合中做一次二分查找

5. MyISAM引擎均使用B-Tree索引，InnoDB引擎使用B+Tree索引

6. B-Tree索引能够加快访问数据的速度，因为存储引擎不再需要进行全表扫描来获取需要的数据，取而代之的是从索引的根节点开始进行搜索.

MyISAM:主键与非主键读数据的代价一致。

InnoDB：非主键比主键读多一次查找，但是间接引用使得主数据的物理调整不会导致index的修改。

3.2Hash索引

对于每一行数据，存储引擎都会对所有的索引列计算一个哈希码，哈希码是一个较小的值，并且不同键值的行计算出来的哈希码也不一样。哈希索引将所有的哈希码存储在索引中，同时在哈希表中保存指向每个数据行的指针。

在MySQL中只有Memory存储引擎显示支持哈希索引。

Hash索引的缺点：1. 由于Hash索引数据并不是按照索引值顺序存储的，所以无法使用Hash索引进行排序；2. 哈希索引也不支持部分索引列匹配查找；3. 哈希索引只包含哈希值和行指针，所以不能使用索引中的值来避免读取行。

哈希索引只包含哈希值和行指针，不存储字段值，所以不能使用索引中的值来避免读取行。哈希索引数据并不是按照索引值顺序存储的，所以也就无法用于排序。哈希索引不支持部分索引列匹配查找。因为哈希索引始终是使用索引列的全部内容来计算哈希值的。

4.高效的索引策略

4.1独立的列

1. 索引列不能是表达式的一部分，或者函数的参数

4.2前缀索引和索引选择性

1. 索引开始的部分字符

2. 要选择足够长的前缀以保证较高的选择性，同时又不能太长

前缀索引是一种能使索引更小，更快的有效办法，但是另外一方面也有其缺点，MySQL无法使用前缀索引做ORDER BY和GROUP BY，也无法使用前缀索引做覆盖扫描。

4.3多列索引

1. 在多个列上建立独立的索引并不能提高MySQL的查询性能

2. 多个列建立一个多列索引，可以方便有AND，OR或者排序查询等操作

4.4选择合适的索引列顺序

1. 当不需要考虑排序和分组时，将选择性最高的列放在前面

2. 性能不仅依赖于所有索引列的选择性，也和查询条件的具体值相关，也就是和具体值的分布相关。

4.5聚簇索引

4.5.1聚簇索引的主要特定：

1. 聚簇索引不是一种单独的索引类型，而是一种数据存储方式

2. InnoDB的聚簇索引实际上在同一个结构上保存了B-Tree索引和数据行

3. 聚簇表示数据行和相邻的键值紧凑地存储在一起

4. InnoDB引擎支持聚簇索引，将通过主键聚集数据。

5. 数据文件本身即索引文件

6. 叶子节点数据域保存的是完整的数据行

7. InnoDB通过主键聚集数据，如果没有主键，会选择一个唯一的非空索引代替，如果没有这种索引，会隐式定义一个主键来作为聚簇索引

8. InnoDB的二级索引中保存的不是指向行的物理位置的指针，而是行的主键值

4.5.2聚簇索引的优点：

1. 可以把相关数据保存在一起

2. 数据访问更快

3. 使用覆盖索引扫描的查询可以直接使用叶节点中的主键值

4.5.3聚簇索引的缺点：

1. 插入速度严重依赖于插入顺序

2. 更新聚簇索引列的代价很高，会强制InnoDB将每个被更新的行移动到新的位置

3. 二级索引访问需要两次索引查找，而不是一次，首先存储引擎需要找到二级索引的叶子节点获得对应的主键值，然后根据这个值去聚簇索引中查找到对应的行。

4.6覆盖索引

1.覆盖索引是指建立索引的字段正好是覆盖查询条件中所涉及的字段。

2.索引字段和条件字段满足最左前缀相同的原则

3.在EXPLAIN的EXtra列可以看到“Using index”的信息，表示可以进行覆盖索引

4.所需要的数据都在叶子层，找到正确的索引键值后不需要再利用指针做额外的查找

4.7未使用的索引

对于服务器永远用不到的索引，建议考虑删除。

5.索引的限制

1. 索引不是表达式的一部分，也不是函数的参数

2. 对于BLOG，TEXT或者VARCHAR类型的列，必须使用前缀索引，因为mysql不允许使用这些索引的完整长度

3. 只有当索引的列顺序和order by字句的顺序完全一致，而且所有列的排序方向都一样时，MySQL才能够使用索引对结果进行排序

4. 范围条件查询，mysql无法再使用范围列后面的其他索引列；多个等值条件查询，则没有这个限制

5. Mysql不能在索引中执行like '%A'的操作

6.合理的设计索引

1.查询频繁的列适合建立索引

2. 频繁更新的列不适合建立索引

3. 索引选择性低的列不适合建立索引

4. 避免重复、冗余索引

5. 索引键值不宜过长，可以使用前缀索引

6. 联合索引需要考虑列的顺序，最好利用最左前缀

7.使用索引优化查询

1.优化方式：通过explain查看执行计划

2. 最左前缀原则

3. 查询得到的数据行数过多时，不适用索引

4. ORDER BY语句尽量使用已有索引以减少排序成本

8.使用SQL提示(SQL HINT)控制索引的使用

USE INDEX，在查询语句中表名的后面，添加USE INDEX来提供希望MySQL去参考的索引列表，就可以让MySQL不再考虑其他可用的索引。

IGNORE INDEX，如果用户只是单纯地想让MySQL忽略一个或者多个索引，则可以使用IGNORE INDEXz作为HINT。

FORCE INDEX，为强制MySQL使用一个特定的索引，可在查询中使用FORCE INDEX作为HINT。

9.查看索引的使用情况

root@read 02:28:07>show status like ‘Handler_read%’;

+———————–+——-+

| Variable_name | Value |

+———————–+——-+

| Handler_read_first | 0 |

| Handler_read_key | 0 |

| Handler_read_next | 0 |

| Handler_read_prev | 0 |

| Handler_read_rnd | 0 |

| Handler_read_rnd_next | 61 |

+———————–+——-+

6 rows in set (0.41 sec)

Handler_read_first 代表读取索引头的次数，如果这个值很高，说明全索引扫描很多。

Handler_read_key代表一个索引被使用的次数，如果我们新增加一个索引，可以查看Handler_read_key是否有增加，如果有增加，说明sql用到索引。

Handler_read_next 代表读取索引的下列，一般发生range scan。

Handler_read_prev 代表读取索引的上列，一般发生在ORDER BY … DESC。

Handler_read_rnd 代表在固定位置读取行，如果这个值很高，说明对大量结果集进行了排序、进行了全表扫描、关联查询没有用到合适的KEY。

Handler_read_rnd_next 代表进行了很多表扫描，查询性能低下。

SHOW INDEX FROM tbl_name [FROM db_name]查看表的索引。