mysql索引优化细节

1 使用索引列进行查询的时候尽量不要使用表达式

explain select * from menu where mid+1=5; -- 主键索引失效
explain select * from menu where mid=4; -- 使用索引查询

2 尽量使用主键查询，因为主键查询不会触发回表查询

使用普通索引查询时，如果要返回的结果中包含非索引列，会再触发一次主键查询（回表）

3 较长字段列创建索引，可以使用前缀索引

当频繁进行查询操作的列字段较长时，可截取该字段的重复率较低的前n个长度建立索引，以避免在整个字段上建立索引消耗资源较多的情况

• 1.通过计算完整列的选择性，来选择合适的字段长度建立索引

-- 计算city字段的前n个长度的不重复值dv（distinct value），选择dv基本不变的n值建立索引
select 
count(distinct left(city,3))/count(*) as c3,
count(distinct left(city,4))/count(*) as c4,
count(distinct left(city,5))/count(*) as c5,
count(distinct left(city,6))/count(*) as c6,
count(distinct left(city,7))/count(*) as c7,
count(distinct left(city,8))/count(*) as c8 
from emp;

• 2.创建索引

alter table emp add index idx_city_dv(city(7));

4 使用索引排序

• 1.索引的建立最好能兼顾查询和排序，这样可最大程度的利用索引查询的效率
• 2.如果排序列可以和条件查询列组成最左前缀索引，可以提高查询的效率（查询条件要先匹配左才能生效）
• 3.查询和排序组合成最左前缀时，排序中字段的升降也要保持一样（同asc同desc），两者兼顾也会进行全表扫描，导致索引失效
• 4.查询中没有使用到索引，即使排序字段使用了索引也不会生效（会进行全表的扫描，explain中的Extra列会显示Using filesort）

5 union all，in，or都能使用索引，但是推荐使用in

• 查询相同的情况下，union all 实际是进行了两次查询，in和or都是一次查询，但是查询数据较多时in的效率要比or的效率高
• union：对两个结果集进行并集操作，不包括重复行，同时进行默认规则的排序
• union all：对两个结果集进行并集操作，包括重复行，不进行排序（效率比union高）

-- 如果查询的id列都存在，union查询返回三条数据，union all 查询返回四条数据（包含两条eid=1的数据）
select * from emp where eid=1 or eid=2 union all select * from emp where eid=1 or eid=3;
select * from emp where eid=1 or eid=2 union select * from emp where eid=1 or eid=3;

6 范围查询中可能会不使用索引

范围查询： > , <
范围查询中使用一般索引可能不会生效：在sql语句执行时，会先通过分析器优化器进行sql语句的分析优化，当发现使用索引的效率要远大于不使用索引时，索引才会生效；否则索引不会生效，会进行全表的查询。

• 主键索引进行范围查询，索引一直生效
• 范围查询列可以使用索引，但范围列后面的列将无法用到索引（失效）

7 强制类型转换可能会导致索引失效进行全表扫描

-- tel列为varchar类型，且建立有索引
explain select * from emp where tel=15738967250; -- type=all，强制类型转换，索引失效，进行全表扫描
explain select * from emp where tel='15738967250'; -- type=ref，使用索引查询

8 更新十分频繁，或者数据区分度不高的字段上不要建立索引

• 索引的更新会涉及到B+树结构的变化，更新频繁的字段建立索引会大大降低数据库性能
• 类似于性别这类区分不大的属性字段，建立索引没有意义，并不能进行有效的查询过滤
• 一般区分度在80%以上，查询操作较为频繁的字段，可以建立索引
• 区分度可以使用 count(distinct(列名))/count(*) 来计算

9 进行表连接时，最好不要超过三张表；需要join的字段，数据类型必须一致

• Simple Nested-Loop Join Algorithm ：简单嵌套连接算法
  连接表的个数相当于嵌套循环的层数，每次从一个循环中的第一个表中读取一行，然后将每一行传递给一个嵌套循环，该循环处理联接中的下一个表；重复此过程的次数与要连接的表的次数相同，因为NLJ算法从外循环到内循环一次传递一行，所以它通常会多次读取在内循环中处理的表。
• Index Nested-Loop Join Algorithm：索引嵌套循环连接算法
  要求非驱动表的连接列有索引，可以通过索引来较少比较，加速查询；在查询时，驱动表会根据关联字段的索引进行查找，根据索引找到符合的值时，在进行回表查找（只有当匹配到索引才进行回表查询）；如果非驱动表的关联字段是主键，性能会更高；如果不是主键，则要再进行回表查询，性能会比主键索引较低一些。
• Block Nested-Loop Join Algorithm：块嵌套循环连接算法
  如果有索引会采用Index Nested-Loop Join Algorithm，如果没有索引会采用Block Nested-Loop Join Algorithm，该连接方式在每两个表之间会有一个join-buffer缓冲区，会将驱动表的所有关联列以及查询需要的结果列先缓存到join buffer中，然后再用join buffer中的行数据批量与非驱动表进行匹配，将第一种的每次循环匹配合并为一次匹配，降低了非驱动表的访问；join_buffer_size系统默认值是256K，可以自定义参数大小；N个表进行关联查询时，会产生N-1个buffer。

10 其他

• 创建索引的列，不允许为null，为null时可能会得到不符合预期的结果（非强制）
• 能使用limit的时候尽量使用limit，会降低数据库的压力
• 索引并不是越多越好，单个表中索引建议控制在5个以内
• 组合索引字段数不允许超过5个