一、索引的类型
索引有很多种类型,为不同的场景提供更好的性能。不同存储引擎的索引工作方式也不一样,也并不是所有的存储引擎都支持所有类型的索引。
1、B-Tree索引
https://www.cs.usfca.edu/~galles/visualization/BTree.html
B-Tree是满足下列条件的数据结构:
d为大于1的一个正整数,称为B-Tree的度。
h为一个正整数,称为B-Tree的高度。
每个非叶子节点由n-1个key和n个指针组成,其中d<=n<=2d。
每个叶子节点最少包含一个key和两个指针,最多包含2d-1个key和2d个指针,叶节点的指针均为null 。
所有叶节点具有相同的深度,等于树高h。
key和指针互相间隔,节点两端是指针。
一个节点中的key从左到右非递减排列。
所有节点组成树结构。
每个指针要么为null,要么指向另外一个节点。
B-Tree特征:
对索引列是按顺序存储(数字顺序、字母顺序、时间顺序)的,所以很适合查询范围数据。
B-Tree索引的限制:
如果不是按照索引最左列开始查询,则无法使用索引;
不能跳过索引中的列;
如果查询中有某个列的查询范围,则其右边所有列都无法使用索引优化查询。
不同的存储引擎实现B-Tree的方式也不一样。MyISam使用前缀压缩技术使得索引更小,InnoDB则按原数据格式进行存储。再如MyISam索引时通过数据的物理位置引用被索引的行,InnoDB则是根据主键引用被索引的行。
2、哈希索引
哈希索引基于哈希表实现,只有精确匹配索引所有列的查询才有效。哈希索引将所有哈希码存储到索引中,同时在哈希表中保存指向每个数据行的指针。
哈希索引特点:
索引自身只需保存哈希码,查询速度非常快。
哈希索引限制:
哈希索引只包含哈希值和行指针,而不存在字段值,所以不能使用索引中的值来避免读行;
不能按索引值顺序存储,无法用于排序;
不支持索引列匹配查询,只支持等值比较查询
其他索引类型:T-Tree索引(空间数据索引)、全文索引等
二、索引的优点
基础优点:快速定位到表的指定位置。根据索引的数据结构不同,还有一些附加优点。
索引优点:
大大减少了服务器需要扫描的数据量;
可以帮助服务器避免排序和临时表;
可以将随机IO变成顺序IO。
判断一个索引是否符合某个查询的“三星系统”:
一星:索引将相关的记录放到一起;
二星:索引中数据的顺序和查询的排列顺序一致;
三星:索引中的列包含查询中需要的全部列
三、高性能的索引策略
高效地选择和使用索引有很多方式。
1、独立的列:值索引列不能是表达式的一部分,也不能是函数的参数;
2、前缀索引和索引选择性
3、多列索引
4、选择合适的索引列顺序
5、聚簇索引:不是一种索引类型,而是一种数据存储方法,在同一结构中保存了B-Tree索引和数据行
6、覆盖索引:设计优秀的索引应该需要考虑到整个查询,不单单是where条件部分。覆盖索引包含了所有需要查询的字段的值(不需要回表查询)
7、使用索引扫描来排序
8、压缩(前缀压缩)索引
9、冗余和重复索引
10、未使用的索引
11、索引和锁:InnoDB只有在访问行的时候才会对其加锁,而索引能够减少InnoDB访问的行数,从而减少行锁的数量。
四、索引案例
1、支持多种过滤条件
2、避免多个范围条件
尽可能将范围查询的列放到索引的后面,以便优化器能使用尽可能多的索引列;
无法使用范围列后面的其他索引列
3、优化排序
将排序列添加到索引中。如果数据量很大,可以限制用户能够翻页的数量,或使用延迟管理(如:先查主键id,然后分页,再查详情)
五、维护索引和表
1、找到并修复损坏的表:通过是由系统崩坏造成的,可以使用CHECK TABLE来检查
2、更新索引统计信息:如果统计信息不准确,优化器可能会做成错误的决定
3、减少索引和数据碎片
碎片类型:行碎片、行间碎片、剩余空间碎片
索引碎片可以通过先删除索引,再重建索引的方法清除索引碎片化。可以通过重建表的方式清除数据碎片。