1. MySQL explain selete_type类型

select查询的类型,主要是区别普通查询和联合查询、子查询之类的复杂查询。

  • SIMPLE: 简单SELECT(不使用UNION或子查询)
  • PRIMARY: 最外面的SELECT
  • UNION: UNION中的第二个或后面的SELECT语句
  • DEPENDENT UNION: UNION中的第二个或后面的SELECT语句,取决于外面的查询
  • DEPENDENT SUBQUERY: 子查询中的第一个SELECT,取决于外面的查询
  • UNION RESULT: UNION 的结果
  • SUBQUERY: 子查询中的第一个SELECT
  • DERIVED: 导出表的SELECT(FROM子句的子查询)

2. MySQL explain type类型

联合查询所使用的类型,数据访问类型:

  • consts: 单表中最多只有一个匹配行(主键/唯一索引),在优化阶段即可读取到数据
  • ref: 如果每次只匹配少数行,连接字段上的索引是非唯一索引,操作符必须使用=匹配,连接字段值不可为NULL
  • eq_ref: 对于每个来自于前面的表的行组合,从该表中读取一行,一般用于join,单表不适合 a b c nest loop join
  • range: 对索引进行范围扫描,为诸如BETWEEN,IN,>=,LIKE类操作提供支持
  • index: 接近all,但是比all性能好。1).当查询是索引覆盖的,即所有数据均可从索引树获取的时候(Extra中有Using Index); 2).以索引顺序从索引中查找数据行的全表扫描(无 Using Index);3).如果Extra中Using Index与Using Where同时出现的话,则是利用索引查找键值的意思;4).如单独出现,则是用读索引来代替读行,但不用于查找
  • all: 全表扫描或者范围扫描:不使用索引,顺序扫描,直接读取表上的数据(访问数据文件)

平常sql语句需要选择前四种

3. 注意的开发规范

  • 高并发量时,考虑数据冗余;
  • where后面条件顺序,连接条件写最前面,过滤性越强的,越靠前放
  • in后面的括号里数量,尽量限制在500以内,超过的话分批;
  • 不要出现超过3张表的join,连接字段类型保持一致;
  • 多表join时(a left join b ),where后面的条件,尽可能集中到驱动表上; left 100w 1w (where a b order by c a+b +c )
  • 页面上的搜索,禁止使用左模糊和全模糊匹配;
  • order by后面的字段,尽量和where后面的字段一致;
  • order by name1 desc, name2 desc, name3 asc没法用索引;order by a.name1, b.name2 没法用索引;
  • 禁止使用存储过程,触发器,视图等;
  • join条件建索引,过滤性强的字段建索引,其它情况请找DBA讨论。

4. 优化案例

覆盖索引

1
2
select waybill_id from tms_waybill2
where consigner_name like ‘%沈阳1网%';
1
2
3
4
select waybill_id from tms_waybill2
where consigner_name like '东莞1网%'
and create_time >='2016-08-01 00:00:00'
and create_time <='2016-08-25 00:00:00';

分页优化

1
2
SELECT * FROM t ORDER BY id LIMIT 1, 10;
SELECT * FROM t ORDER BY id LIMIT 1000000, 10;

优化一:

1
2
SELECT * FROM t WHERE id
>= (SELECT id FROM t LIMIT 1000000, 1) LIMIT 10;

优化二:

1
2
SELECT * FROM t WHERE id
BETWEEN 1000000 AND 1000010;

优化三:

1
2
3
4
select * from (select id from t
where tname=‘f' order by id limit 300000,10) a
left join t b
on a.id=b.id;

批量数据导出(报表类)

1
2
3
目前的导出方法 limit m,n的方式
建议修改为 create_time>=‘…’ and create_time<‘…’

MySQL的session级优化参数

如果一些sql,短期内没办法优化,可以考虑在session级,加上以下参数:select (….)
SET tmp_table_size=6410241024;
SET sort_buffer_size=3210241024;
SET join_buffer_size=3210241024;
SET read_buffer_size=1610241024;
SET read_rnd_buffer_size=1610241024;
碰到添加session级参数的情况,请一定与DBA确认后,再添加。