|10分钟掌握MySQL的索引查询优化技巧( 三 )

SELECT * from people ORDER BY NAME

SELECT * from people ORDER BY NAME, Age

SELECT * from people GROUP BY Name

以下几个SQL是反面范例：

SELECT * from people where Age = 2
SELECT * from people where NAME like ‘%B’
SELECT * from people where age = 2
SELECT * from people where NAME = ‘ABC’ AND number = 3
SELECT * from people where NAME like ‘B%’ and age = 22

一个使用Hash值创建索引的技巧
如果表中有一列存储较长字符串，假设名字为URL ，在此列上创建的索引比较大，有个办法可以缓解：创建URL字符串的数字哈希值的索引。再新建一个字段，比如叫做URL_CRC ，专门放置URL的哈希值，然后给这个字段创建索引，查询时这样写：
select * from t where URL_CRC = 387695885 and URL = 'www.baidu.com'
如果数据量比较多，为防止哈希冲突，可自定义哈希函数，或用MD5函数返回值的一部分作为哈希值：
SELECT CONV(RIGHT(MD5('www.baidu.com'),16), 16, 10)
前缀索引
如果字符串列存储的数据较长，创建的索引也很大，这时可以使用前缀索引，即：只针对字符串前几个字符做索引，这样可以缩短索引的大小，不过，显然，此类索引在执行order by和group by时不起作用。
创建前缀索引时选择前缀长度很重要，在不破坏原来数据分布的情况下尽可能选择较短的前缀。举个例子，如果如果大部分字符串是以”abc”开头，那么如果限定前缀索引长度为4 ，索引值会包含太多的重复的”abcX” 。
多列索引
上面提到的“People”上创建的索引即为多列索引，多列索引往往比多个单列索引更好。

对多个索引进行and查询时，应该创建多列索引，而不是多个单列索引
可以试试这样写的效果：

select * from t where f1 = 'v1' and f2 <> 'v2' union all select * from t where f2 = 'v2' and f1 <> 'v1'
多列索引的顺序很重要，通常，不考虑排序和分组查询时，应该把选择性（选择性是指某表索引列不同数据的个数/总行数。选择性高意味着重复数据少）大的列放到前面。但也有例外，如果能确认某些查询是频繁执行的，则应该优先照顾这些查询的选择性，比如，如果上面的People表中Name的选择性大于Age ，查询语句应该这样写:
select * from people where name = 'xxx' and age = xx
Name列放了索引中的左侧比较合适，但是如果某个SQL执行的评率最高，比如
select * from people where name = 'xxx' and age = 20
当age=20的记录在数据库中非常少时，反而把age放到索引列的左端效率更高。把age放了索引左端可能对其它age不等于20的查询来说不公平，如果不能确定age=20是最非常频繁的查询条件，还是要综合考虑，把name放了左侧合适。
聚簇索引
聚簇索引是一种数据存储结构， InnoDB在主键的索引的叶子节点中直接保存了数据行，而不是像二级索引那样只是保存了索引列的值和所指向行的主键值。由于这个特性，一个表只能有一个聚簇索引。如果一个表没有定义主键也没有定义具有唯一索引的列，那么InnoDB会生成一个隐藏列，并且在此列设为聚簇索引列。
覆盖索引
简单地说，某些查询只需要查询索引列，那么就不用再根据索引B树节点记录的主键ID进行二次查询了。
重复索引和冗余索引
如果重复在某列创建索引，并不会带来任何好处，只有坏处，应该尽量避免。比如给主键创建唯一索引和普通索引就是多于的，因为InnoDB的主键默认就是聚簇索引了。