一、索引列上不能使用表达式或函数
比如:
select ... from product
where to_days(out_date) - to_days(current_date) <= 30;
该SQL用于查找30天内就过期的商品。而它违反了“索引列上不能使用表达式或函数”这条策略,这项查询就不能使用到索引。
要想使用到索引,则改为:
select ... from product
where out_date <= date_add(current_date, interval 30 day)
这样就能正确使用到该列上的索引。
二、前缀索引
MySQL中B树索引对键值大小是有限制的,根据存储引擎的不同而不同。
- 对于InnoDB,索引键的大小不能超过764个字节
- 对于MyISAM,不能超过1000个字节
这对与整型、浮点型,日期这样的类型足够了,但对字符串可能就不够用。
MySQL支持对字符串的前缀建立索引,大大节约索引的空间,从而提高索引的查询效率。
可以使用下面这个语法对字符串前缀建立索引:
CREATE INDEX index_name ON table(col_name(n));
三、索引列的选择性
索引的选择性是不重复的索引值和表的记录数的比值。
唯一性越高,选择性越高,查找效率越快。
item1 | item2 | item3 |
---|---|---|
abcd | abcd | abcd |
abde | abde | abde |
bcdef | bcdef | bcdef |
bcaef | bcaef | bcaef |
既要保证索引尽可能小,选择性也不能太差。
四、联合索引
5.0之前,每个查询只能使用到一个列上的索引,5.0之后虽然有了多个索引合并的概念,使用多个列的独立索引来进行合并过滤,但是通常意味着需要更多的内存和磁盘IO。
建立联合索引可能才是一个更好的优化方法。
B树索引是按照索引列的顺序来进行存放的,这个先后顺序决定了查询是否能够使用到这个索引,所以列的顺序选择尤为重要。
联合索引列的顺序选择原则:
- 经常会被优先使用到的列优先
- 选择性高的列优先
- 宽度小的列优先
五、覆盖索引
使用B树索引直接获取到想要的数据。
覆盖索引的优点:
- 可以优化缓存,减少磁盘IO
- 可以减少随机IO,变随机IO为顺序IO
- 可以避免对InnoDB主键索引的二次查询
- 可以避免MyISAM表进行系统调用
无法使用覆盖索引的情况:
- 不是所有存储引擎,索引类型都支持覆盖索引(比如memory)
- 查询使用了太多列
- 使用了双%号的like查询