mysql索引优化

简单总结索引优化

1. 
   

2. 对于where条件进行优化

where条件意味着从表中取少来那个数据进行select，update，delete，where条件对应的列一定要加索引

• 多表连接主外键必须要有索引

  在实际生产中有些情况会将表的主外键关系删除，只保留主外键的列，在软件代码层实现数据一致性，主外键一定要有索引

• 使用索引优化order by

  典型使用场景：
  order要对大量数据进行排序，如果超出sort buffer size，产生磁盘排序，需要用索引去排序
  官方文档排序优化https://dev.mysql.com/doc/refman/5.5/en/order-by-optimization.html

• 多列索引的生效

  where条件一定要出现复合索引的前导列
  最佳实践：where条件中and连接所有的复合索引条件并且顺序和复合索引一致

• where…or…

  各列建立单列索引

• select max,min（列）from

  特别是统计有多少行，一般不用select count1 from table
  在求最大、最小的列上建立索引;

• 优化limit order by

  分页查询
  1、使用order by 列进行limit输出——针对访问limit前部分数据
  2、使用join来进行limit输出——针对访问limit数值很大的情况
  3、总页数
  —-增加额外的标志页数的列
  —-在列上建立索引select max(col1)from table_name;

• gap锁对索引的要求

  gap锁 next key锁
  解决幻影读
  针对dml，一定要有主键和索引
  针对insert要有主键，只要有索引和主键，就不会出现严重的gap锁
  针对delete和update
  delete t1 where name=’abc’;
  需要再name 列上有索引
  update t1 set name=’abd’ where h_date=’2016-01-01’;
  需要再h_date 列上有索引

• 查看没有建立索引的列的选择性
  

  select count（distinct name） from （select * from t limit 10000,10000） bieming

• 表上索引的数量不要超过6 个，唯一键不要超过1 个，一定要有有意义的主
  键
• 通过主键从表中取数据，不受百分比的限制
  通过主键从表中访问数据，效果永远是最好的
• group by 会使用到tmp 空间，用索引优化group by
  优化大型结果集的group by：
  1、增加会话级别的max_heap_table_size 和tmp_table_size
  2、使用索引——在group by 的列上添加索引（列变成有序后，自然就好判
  断相等、分组）

• explain 会出现using temporary：
  
  mysql> explain select * from xuanke1 group by tea_id;
  +----+-------------+---------+------+---------------+------+---------+------+--------+----------
  -----------------------+
  | id | select_type | table | type | possible_keys | key | key_len | ref | rows |
  Extra |
  +----+-------------+---------+------+---------------+------+---------+------+--------+----------
  -----------------------+
  | 1 | SIMPLE | xuanke1 | ALL | NULL | NULL | NULL |
  NULL | 149650 | Using temporary; Using filesort |
  +----+-------------+---------+------+---------------+------+---------+------+--------+----------
  -----------------------+
  1 row in set (0.00 sec)

  判断是否进行tmp 和sort 优化：
  1、explain 看rows 和extra，如果rows 很高，并且extra 中有using temporary
  和using filesort，说明可能会使用磁盘的临时空间，需要进行优化。
  2、出现了Created_tmp_disk_tables、Sort_merge_passes 值增加的情况。

  • using index，不需要回表

    mysql> explain select s_name,stu_id from student;
    +----+-------------+---------+-------+---------------+------------+---------+------+--------
    +-------------+
    | id | select_type | table | type | possible_keys | key | key_len | ref |
    rows | Extra |
    +----+-------------+---------+-------+---------------+------------+---------+------+--------
    +-------------+
    | 1 | SIMPLE | student | index | NULL | i_stu_name | 22
    | NULL | 194737 | Using index |
    +----+-------------+---------+-------+---------------+------------+---------+------+--------
    +-------------+

    1、我们需要访问的数据都在索引中，不需要回表，覆盖索引
    例如：表中name 列上有索引，select id,name from t where name=’abc’;
    id 是主键，name 列上有索引，要访问的数据在索引中就能访问到，
    不需要回表中取数据。
    2、覆盖索引不受访问的行数的限制

  • 从表中取大量数据
    1、走主键索引
    2、覆盖索引（索引默认带主键）
  • using where，回表访问数据
    需要回表访问，如果rows 很高，效果不会很好，可以采用覆盖索引来优化。
    索引的访问成本很低，一般索引在内存中，但是回表的访问成本很高，
    因为有可能通过索引要访问1000 行数据，但是在表中，这1000 行数据存放
    在1000 个块中，也有可能在5 个块中，但是回表的成本是物理读，成本还
    是高的。
  • 覆盖索引

  a) 访问数据量比较大，只能走全表扫描，不希望走全表扫描可以使用覆盖
  索引
  b) 覆盖索引就是我们访问的所有的列都在索引中
  select …
  from ..
  on …
  where ..
  group by ..
  having ..
  order by ..;

  索引失效场景

  没有where条件

  有where列无索引

  数据量超过20%

  多列索引没有使用前导列，where出现or条件

  索引本身失效，尝试重建索引

  where条件，列上有函数，运算，移到=右边

  表很小

  使用了ignore index hints

  隐式类型转换导致索引失效 字符串 后面用数字或者日期来表示 字符串列忘记加引号 字符串列=数字或日期

  数据倾斜（使用hints来强子走索引或者不走索引）

  隐式类型转换

  字符串列where name =123 ； where name='123'   不要讲数字列定义成varchar
  

  不等于号

  where like ‘%’ （统计信息不真实，手工收集统计信息，手工修改统计信息 包含索引列上唯一值的数量，表的行数）

  not in 如果失效 改成not exist 互相转换
  in exist 可以转换成distinct+join
  not in not exist 可以转换成left join+where is null