MySQL中的索引
1.高性能的索引创建策略
正确地创建和使用索引是实现高性能查询的基础。前面我们已经了解了索引相关的数据结构,各种类型的索引及其对应的优缺点。现在我们一起来看看如何真正地发挥这些索引的优势。
1.1.索引列的类型尽量小
我们在定义表结构的时候要显式的指定列的类型,以整数类型为例,有TTNYINT、NEDUMNT、INT、BIGTNT这么几种,它们占用的存储空间依次递增,我们这里所说的类型大小指的就是该类型表示的数据范围的大小。能表示的整数范围当然也是依次递增,如果我们想要对某个整数列建立索引的话,在表示的整数范围允许的情况下,尽量让索引列使用较小的类型,比如我们能使用INT就不要使用BIGINT,能使用NEDIUMINT就不要使用INT,这是因为数据类型越小,在查询时进行的比较操作越快(CPU层次)数据类型越小,索引占用的存储空间就越少,在一个数据页内就可以放下更多的记录,从而减少磁盘/0带来的性能损耗,也就意味着可以把更多的数据页缓存在内存中,从而加快读写效率。
这个建议对于表的主键来说更加适用,因为不仅是聚簇索引中会存储主键值,其他所有的二级索引的节点处都会存储一份记录的主键值,如果主键适用更小的数据类型,也就意味着节省更多的存储空间和更高效的I/0。
1.2.索引的选择性
创建索引应该选择选择性/离散性高的列。索引的选择性/离散性是指,不重复的索引值(也称为基数,cardinality)和数据表的记录总数(N)的比值,范围从1/N到1之间。索引的选择性越高则查询效率越高,因为选择性高的索引可以让MySQL在查找时过滤掉更多的行。唯一索引的选择性是1,这是最好的索引选择性,性能也是最好的。
很差的索引选择性就是列中的数据重复度很高,比如性别字段,不考虑政治正确的情况下,只有两者可能,男或女。那么我们在查询时,即使使用这个索引,从概率的角度来说,依然可能查出一半的数据出来。
哪列做为索引字段最好?当然是姓名字段,因为里面的数据没有任何重复,性别字段是最不适合做索引的,因为数据的重复度非常高。
怎么算索引的选择性/离散性?比如person这个表:
SELECT count(DISTINCT name)/count() FROM person;
SELECT count(DISTINCT sex)/count() FROM person;
SELECT count(DISTINCT age)/count() FROM person;
SELECT count(DISTINCT area)/count() FROM person;
1.3.前缀索引
针对blob、text、很长的varchar字段,mysql不支持索引他们的全部长度,需建立前缀索引。
语法:Alter table tableName add key/index (column(X))
**缺点:**前缀索引是一种能使索引更小、更快的有效办法,但另一方面也有其缺点MySQL无法使用前缀索引做ORDER BY和GROUP BY,也无法使用前缀索引做覆盖扫描。
有时候后缀索引 (suffix
index)也有用途(例如,找到某个域名的所有电子邮件地址)。MySQL原生并不支持反向索引,但是可以把字符串反转后存储,并基于此建立前缀索引。可以通过触发器或者应用程序自行处理来维护索引。
案例:
首先找到最常见的值的列表:
SELECT COUNT(DISTINCT LEFT(order_note,3))/COUNT(*) AS sel3,
COUNT(DISTINCT LEFT(order_note,4))/COUNT(*)AS sel4,
COUNT(DISTINCT LEFT(order_note,5))/COUNT(*) AS sel5,
COUNT(DISTINCT LEFT(order_note, 6))/COUNT(*) As sel6,
COUNT(DISTINCT LEFT(order_note, 7))/COUNT(*) As sel7,
COUNT(DISTINCT LEFT(order_note, 8))/COUNT(*) As sel8,
COUNT(DISTINCT LEFT(order_note, 9))/COUNT(*) As sel9,
COUNT(DISTINCT LEFT(order_note, 10))/COUNT(*) As sel10,
COUNT(DISTINCT LEFT(order_note, 11))/COUNT(*) As sel11,
COUNT(DISTINCT L