索引使用策略及优化

MySQL的优化主要分为结构优化（Scheme optimization）和查询优化（Query optimization）
这里讨论的高性能索引策略主要属于结构优化范畴

1 联合索引及最左前缀原理

联合索引（复合索引）

相对于一般索引只有一个字段 联合索引可以为多个字段创建一个索引
比如 我们在（a,b,c）字段上创建一个联合索引
则索引记录会首先按照A字段排序 然后再按照B字段排序然后再是C字段
因此 联合索引的特点就是：
1）第一个字段一定是有序的

2）当第一个字段值相等的时候 第二个字段又是有序的
比如下表中当A=1时所有B的值是有序排列的 依次类推

最左前缀原理

其实联合索引的查找就跟查字典是一样的
先根据第一个字母查 然后再根据第二个字母查 或者只根据第一个字母查
但是不能跳过第一个字母从第二个字母开始查 这就是所谓的最左前缀原理（可以理解为需要按照第一个来比较大小）

还是上面例子 我们在（a,b,c）字段上建了一个联合索引
1）若用以下的查询方式都可以用到索引

select * from table where a=1;
select * from table where a=1 and b=2;
select * from table where a=1 and b=2 and c=3;

上面三个查询按照（a ）、（a，b ）、（a，b，c ）的顺序都可以利用到索引 这就是最左前缀匹配
若是（a，c ）那么只会用到索引a
2）如果用到了最左前缀 但是顺序颠倒也可以用到索引
因为mysql查询优化器会判断纠正这条sql语句该以什么样的顺序执行效率最高 最后才生成真正的执行计划
但我们还是最好按照索引顺序来查询 这样查询优化器就不用重新编译了

select * from table where b=2 and a=1;
select * from table where b=2 and a=1 and c=3;

3）若没有用到最左前缀a 那么这个查询就用不到索引

select * from table where b=2 and c=3;

2 覆盖索引（covering index）

指⼀个查询语句的执行只用从索引中就能够取得 不必从数据表中读取（也叫回表）
辅助索引的叶子节点存放的是主键值和数据行 所以支持覆盖索引

比如表有（a,b,c,d）四个字段 a为主键 在（b,c）字段上建了一个联合索引
那么如果所要查询的列包含在所建的索引列中 就可以直接返回数据
若所要查询的列中有索引列之外列 那么需要回表读取数据

--用到了辅助索引 但没有用到覆盖索引 因为b字段是联合索引的一部分 但d字段不在索引范围中
select * from table where b=2;
--用到了覆盖索引 因为a,b,c都在索引范围中
--(b、c是辅助索引 辅助索引的叶子节点存储了主键值和数据行)
select a,b,c from table where b=2;

2 sql调优

1）要尽量避免全表扫描
a）首先考虑在where、order by及group by所涉及的列上建立索引
b）在经常需要进行检索的列上创建索引
但创建索引个数也不宜过多 虽然提高了查询的效率 但是也降低了插入删除的效率
因为修改表数据时 有可能会重建索引
2）避免在索引列上操作（计算、使用函数） 保持列”干净”
比如

--id为索引列
select * from table where id+1 = 2;  --不能用到索引
select * from table where id = 1;  --不对索引列操作 就能使用到索引

3）select查询语句优化
a）尽量避免使用 来查询 最好用具体的字段列表代替 不要返回任何用不到的字段
b）用like做模糊查询时 不要以”%”开头

--name 为索引列
select * from table where name like "%name";  --不走索引
select * from table where  name like "name%";  --走索引

c）!= 和 null
尽量避免在 where 子句中对字段进行 null 值判断
否则将导致引擎放弃使用索引而进行全表扫描
创建表默认值是 NULL，尽量使用 NOT NULL，或使用特殊值，如 0、-1

--表有三列 id name sex
--id name为索引列
select * from table where id != 2;  --所要查询的列包含非索引列 则不能用到索引 此时sex非索引列 所以不能用索引
select id,name from table where id != 2;  --能使用到索引
SELECT * FROM table where id is not null;  --sex为非索引列 无法使用索引
SELECT id,name FROM table where id is not null;  --id name为索引列 那么此时可以使用索引

d）尽量避免查询时数据类型不匹配的情况
mysql中数字类型转换成字符串类型的可以使用索引
但是其他类型不匹配时 就无法使用索引
所以如果表的字符集不同时 联合查询也无法使用索引

--表有三列 id name sex
--id name为索引列 id为int类型 name为varchar类型
select id,name from table where id = "2";  --走索引
select id,name from table where name = 2;  --不走索引