1.高性能索引策略
1.1独立的列
“独立的列”是指索引不能是表达式的一部分也不能使函数的参数
select name from actor where id+1=5;
这种情况索引失效。我们应该简化where条件始终将单独的列放在比较符号的一侧。
1.2前缀索引和索引选择性
当索引是很长的字符列的时候，会让索引变得大且慢，除了模拟哈希索引的方法，通常选择索引的部分开始字符，从而提高索引效率。但是这样会降低索引的选择性，索引的选择性是指，不重复的索引值（也称为基数,cardinality）和数据表总记录的比值,基数越大查询效率越高，唯一索引的选择性是1，这是最好的选择性，性能也是最好的。
前缀索引的选择应当保证较高的选择性同时又不能太长。
- 计算列的完整性
select count(distinct rowname)/count(*) from table;
- 统计不同长度前缀的选择性
select count(distinct left(rowname,num))/count(*) as selnum,
...
from table;
- 创建前缀索引
alter table tablename add key (rowname(num));
在mysql中不能用前缀索引做order by 和 group by,也没有办法做覆盖扫描。
1.3 多列索引
当where有多个and,or条件的时候，应当创建多列索引而不是单列索引，应当将or改写为union更加合适，当explain中的extra 出现using union证明使用了索引合并，说明索引建立的很糟糕。
1.4选择合适的索引列顺序
当不排序或者分组的时候，将选择性最高的列放在前面通常的结果可能是不错的。
1.4聚簇索引
在InnoDB引擎中表数据没有什么聚集，应当尽量的使用自增主键(单调递增的聚簇主键)按主键顺序去插入数据，避免大量的随机IO。
1.5覆盖索引
如果一个索引包含所有需要查询的字段值，我们就称之为覆盖索引。只有B树索引能做覆盖索引，因为覆盖索引必须存储索引列的值。并且不是所有的存储引擎都支持覆盖索引，例如menory就不支持覆盖索引。使用覆盖索引会带来较大的性能提升，因为索引的条目要远远小于数据行大小，可以转化随机IO为顺序IO,减少IO的次数。在InnoDB引擎中二级索引包含了主键值,所以即使索引字段不包含主键，依然能对主键值做覆盖索引。
CREATE TABLE products (
id int(11) NOT NULL AUTO_INCREMENT ,
actor varchar(30) NOT NULL ,
sur_name varchar(30) DEFAULT NULL ,
name varchar(30) NULL DEFAULT NULL ,
title varchar(30) S NOT NULL ,
PRIMARY KEY (id),
INDEX key_1 (actor, title) USING BTREE
);
查询
explain select * from products where actor='tom' and title='%asd%';
此时代表通过索引访问表，看是否需要全表扫描，这时索引的信息推迟使用，必要时读全表。因为虽然索引覆盖了where中的字段，但是没有覆盖整个查询的字段。所以还是会读表，而且"%%"导致只能使用第一列索引。
优化：
explain select id from products where actor='tom' and title like '%asd%';
虽然id没有在where条件的索引范围之内但是依然使用了覆盖索引。
正如上面所说在InnoDB引擎中二级索引包含了主键值,所以即使索引字段不包含主键，依然能对主键值做覆盖索引。
EXPLAIN SELECT * FROM products
JOIN (
SELECT
id
FROM
products
WHERE
actor = 'tom'
AND title LIKE '%asd%'
) AS t1 ON (t1.id = products.id);
这里采用的方式是延迟关联 。
这里虽然外层查询的type是ALL但是如果内层查询的结果集足够小的话。性能会有很大的提升(内层查询前提是结果集足够小)，但是如果本身的数据量就不大的话，使用延迟关联反而会因为子查询而带来额外的开销，性能反而下降。
1.6使用索引扫描做排序
当explain的type列值为index则说明使用索引扫描做排序。
限制：
- 只有当索引列顺序和order by子句顺序完全一致，并且所有列的排序方向一致时，才能使用索引对结果做排序。
- 如果查询要关联多张表，只有当order by子句的字段全部为第一张表时才能用索引做排序。
- order by 子句也需要满足最左前缀要求，否则不能做排序。
- 范围查询可能会导致索引排序失效
当索引为覆盖索引时按索引排序的效率是高的。否则性能将会下降。
总结
- 通常情况下我们使用我们使用选择性高的字段作为索引的第一列，
但是有时候某一个字段的选择性不高但是却经常出现在where子句中，例如sex,这时我们可以选用sex做为索引字段的第一列，在不需要性别的时候通过sex in ('w','m'),过滤这个索引。但是要注意的是in()的列表不能太长。 - 对于范围查询应当避免多个范围条件，而使用IN(),但是滥用IN()会导致优化器组合成指数形式增加，范围条件应当建立在索引的最后一列。
- 在同时使用order by 和 limit 查询的时候即使使用了索引在翻页到最后的面的时候，需要花费大量的时间丢弃数据，是一个严重的问题。
select <cols> from profiles where sex='m' order by rating limit 10000,10;
优化此类索引应当使用延迟关联
select <cols> from profiles
join(
select <primary key> from profiles
where sex='m' order by rating limit 10000,10;
) as t1 on(t1.<primary key>=profiles.<primary key>);
- 减少索引和数据碎片
optimize table
对于不支持optimize table的存储引擎可以先改为InnoDB执行后，再修改回去。
