索引能够轻易将查询性能提高几个数量级。
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.覆盖索引:如果一个索引包含所有需要查询的字段的值,我们称之为“覆盖索引”