bitsCN.com
mysql的索引结构
1.索引基础
索引是存储引擎用于快速找到记录的一种数据结构。存储引擎先在索引中找到对应的值，然后根据匹配的索引记录找到对应的数据行。索引可以包含一个列或者多个列的值，如果索引包含多个列，那么列的顺序就会变得比较重要了。
2.索引的优缺点
2.1优点
1. 提高了查询速度
2. 减少了数据读取操作（IO）
3. 降低排序和分组的成本（CPU）
2.2缺点
1. 占用了大量的存储空间
2. insert、update和delete等操作会消耗大量的系统开销
3索引的类型
根据存储结构的不同，将索引分为两种：B-Tree索引和哈希索引。
3.1B-Tree索引
1. 平衡的多路查找树
2. 所有的值都是按顺序存储
3. 每一个叶子页到根的距离相同
4. 查找过程：查找节点+节点内查找，性能等价于在键值的集合中做一次二分查找
5. MyISAM引擎均使用B-Tree索引，InnoDB引擎使用B+Tree索引
6. B-Tree索引能够加快访问数据的速度，因为存储引擎不再需要进行全表扫描来获取需要的数据，取而代之的是从索引的根节点开始进行搜索
3.2Hash索引
对于每一行数据，存储引擎都会对所有的索引列计算一个哈希码，哈希码是一个较小的值，并且不同键值的行计算出来的哈希码也不一样。哈希索引将所有的哈希码存储在索引中，同时在哈希表中保存指向每个数据行的指针。
在MySQL中只有Memory存储引擎显示支持哈希索引。
4.高效的索引策略
4.1独立的列
1. 索引列不能是表达式的一部分，或者函数的参数
4.2前缀索引和索引选择性
1. 索引开始的部分字符
2. 要选择足够长的前缀以保证较高的选择性，同时又不能太长
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语句尽量使用已有索引以减少排序成本
bitsCN.com
