MySQL高性能 创建高性能的索引

索引能够轻易将查询性能提高几个数量级。

1.如果查询的列不是独立的，mysql就不会使用索引

例如：select actor_id from sakila.actor where actor_id +1=5和select ... where TO_DAYS(CURRENT_DATE) - TO_DATS(date_col) <=10;

2.前缀索引和索引的选择性

有时候需要索引很长的字符列时候，可以模拟哈希索引，特别是对于BLOB，text或者很长的varchar必须使用前缀索引

创建前缀索引首先要需要最适合的前缀:当增加前缀长度，选择性提升幅度很小的时候，则停止增加前缀长度。

选择前缀遵从一个标准是：如果前缀的选择性能够接近0.031则基本可用了。选择性=前n个为前缀的不重复的字符总数/字符总数

创建合适的前缀长度：alter table sakila.city_demo add key(city(7));使用city的前7个字符来创建索引

前缀索引是一种能索引更小、更快的有效办法，但是mysql无法使用前缀索引做order by和group by，也无法使用前缀索引做覆盖扫描。

3.多列索引

老版的select film_id,actor_id from sakila.film_actor where actor_id=1 or film_id=1; 对这个查询会使用全表扫描，除非改写成如下

select film_id,actor_id from sakila.film_actor where actor_id=1

union all

select film_id,actor_id from sakila.film_actor where film_id=1

and actor_id <>1

5.0和更新的版本中，查询能够同时使用这两个单列索引进行扫描，并将结果进行合并，有三种算法:or条件的联合(union)，and条件的相交(intersection),组合前两种情况的联合及相交

如果要查看查询语句使用的索引可以在查询语句前面加上explain

索引合并是优化器做的，如果要关闭。则可以设置optimizer_switch来关闭，也可以使用ignore Index提示让优化器忽略掉这些索引。

4.选择合适的索引列顺序

有个经验法则：将选择性最高的列放到索引最前列

在InnoDB表中按主键顺序插入行，如果使用InnoDB表没有数据需要聚集，那么定义一个代理键(surrogate key)做为主键，最简单的方式是使用auto_increment自增列

比较使用代理键和uuid插入行实验结果得知：uuid主键插入行不仅花费的时间更长，而且索引占用的空间也更大，因为它无法简单地总是把新行插入到索引的最后，而是需要俄日新的行寻找合适的位置。

应该尽可能地按主键顺序插入数据，并且尽可能地使用单调增加的聚簇键的值来插入新行。

5.覆盖索引：如果一个索引包含所有需要查询的字段的值，我们称之为“覆盖索引”