高性能MySQL-第五章创建高性能的索引(2)
高性能的索引策略
一、独立的列
如果查询中的列不是独立的,则MySQL就不会使用索引。“独立的列”是指索引列不能是表达式的一部分,也不能是函数的函数。
二、前缀索引和索引选择性
索引部分字符会降低索引的选择性,索引的选择性是指,不重复的索引值(也称为基数,cardinality)和数据表的记录总数(#T)的比值,范围从1/#T到1之间。索引的选择性越高则查询效率越高,因为选择性高的索引可以让MySQL在查找时过滤掉更多的行。唯一索引的选择性是1,这是最好的索引选择性,性能也是最好的。
一般情况下某个列前缀的选择性也是足够高的,足以满足查询性能。对于BLOB、TEXT或者很长的VARCHAR类型的列,必须使用前缀索引,因为MySQL不允许索引这些列的完整长度。
三、多列索引
union的方式可能更好。
四、选择合适的索引列顺序
五、聚簇索引
聚簇索引并不是一种单独的索引类型,而是一种数据存储方式。
当表有聚簇索引时,它的数据行实际上存放在索引的叶子页(leaf page)中。术语“聚簇”表示数据行和相邻的键值紧凑的存储在一起。因为无法同时把数据行存放在两个不同的地方,所以一个表只能有一个聚簇索引。
InnoDB和MyISAM的数据分布对比
在InnoDB表中按主键顺序插入行
六、覆盖索引
如果一个索引包含(或者说覆盖)所有需要查询的字段的值,我们就称之为“覆盖索引”。
不是所有类型的索引都可以成为覆盖索引,覆盖索引必须要存储索引列的值,而哈希索引、空间索引和全文索引等都不存储索引列的值,所以MySQL只能使用B-Tree索引做覆盖索引。
七、使用索引扫描来做排序
MySQL有两种方式可以生成有序的结果:通过排序操作;或者按索引顺序扫描;如果EXPLAIN出来的type列的值为“index”,则说明MySQL使用了索引扫描来做排序。
只有当索引的列顺序和ORDER BY子句的顺序完全一致,并且所有列的排序方向(倒序或正序)都一样时,MySQL才能够使用索引来对结果做排序。如果查询需要关联多张表,则只有当ORDER BY子句引用的字段全部为第一个表时,才能使用索引做排序。
八、压缩(前缀压缩)索引
MyISAM使用前缀压缩来减少索引的大小,从而让更多的索引可以放入内存中,这在某些情况下能极大的提高性能。默认只压缩字符串,但通过参数设置也可以对整数做压缩。
九、冗余和重复索引
十、未使用的索引
十一、索引和锁