MySQL优化

一:查询执行计划

explain sql语句

参数解释:

1.select_type

 1. SIMPLE：简单的查询，没有子查询和UNION
 2. PRIMARY： 如果有复杂查询的话，标记最外层的查询为primary
 3. UNION：例如eg2中的谓语UNION关键字后面的查询就select_type标记为UNION
 4. DEPENDENT  UNION：UNION中的第二个或后面的SELECT语句，取决于外面的查询
 5. UNION RESULT：UNION的结果
 6. SUBQUERY:子查询中的第一个SELECT
 7. DEPENDENT SUBQUERY：子查询中的第一个SELECT，取决于外面的查询
 8. DERIVED：派生的表 ，例如eg3
 9. MATERIALIZED：具体化的子查询,例如eg4
 10. UNCACHEABLE SUBQUERY ：子查询的结果不能被缓存,必须重新评估为每一行的外部查询
 11. UNCACHEABLE UNION：UNION中的第二个或后面的SELECT语句，而且不能被缓存

2.type:连接类型,一个好的sql语句至少要达到range级别,千万不能出现all级别

连接使用哪些类别，有无使用索引；值从好到坏依次如下
1. system：表里面只有一条数据，是const的一种特殊情况
2. const：表中至多有一条数据符合。优化器将这一行中列当作常量来处理，因为只读一次，所以速度非常快。常见：primary key 或者unique index
3. eq_ref：索引扫描只读取一行数据，常用于primary key或者唯一为空（unique not null index）
    SELECT * FROM ref_table,other_table  WHERE ref_table.key_column=other_table.column;
    SELECT * FROM ref_table,other_table     WHERE ref_table.key_column_part1=other_table.column  AND ef_table.key_column_part2=1;
4. ref：索引扫描返回多行数据，非唯一索引查找。
        SELECT * FROM ref_table WHERE key_column=expr;
        SELECT * FROM ref_table,other_table   WHERE ref_table.key_column=other_table.column;
        SELECT * FROM ref_table,other_table   WHERE ref_table.key_column_part1=other_table.column AND ref_table.key_column_part2=1;
5. fulltext：使用fulltext index
6. ref_or_null：类似于ref,会额外搜索包含NULL的行，常用于解析子查询
    eg:SELECT * FROM ref_table WHERE key_column=expr OR key_column IS NULL
7. index_merge：使用了索引合并优化，在输出key列上包含有多个index.
8. unique_subquery：value IN (SELECT primary_key FROM single_table WHERE some_expr)
9. index_subquery：value IN (SELECT key_column FROM single_table WHERE some_expr)
10. range：检索给定范围的行，并使用index来选择行，例如 =, <>, >, >=, <, <=, IS NULL, <=>, BETWEEN, LIKE, or IN()
11. index：扫描整个index tree，与all 的区别就是all是全表扫描，index是full index 扫描
12. all：全表扫描，实际使用中，如果数据量比较大，应该避免进行全表扫描，因为这种连接是最慢的

3.possible_keys

 表示索引,如果不使用索引就是null

4.key_len

 索引长度

5.ref

 表示为了筛选数据，表中哪些列和常量与index进行对比；如果是函数，则比较的是函数的值而不是表达式

6.rows

 扫描行数,是个预估值

7.extra

 详细说明的意思,最好不要出现using filesort 和 using temporary
 1. using fileSort：使用额外操作来进行排序。（注意：1、排序针对的是order by后面跟的列；2、根据排序后的列，顺序的检索每一行）
 2. using temporary：使用临时表来存储结果，常出现去group by 或者order by （与列存储的顺序不同才会使用临时表）操作     
 3. using  index：在索引树上只使用索引就得到结果，没有根据索引回表来单独查询行。就是覆盖索引查询   
 4. using index  condition：上面的 using index 不需要回表查询，那using index where则需要每条记录都回表查询才得到最后的查询结果
 5. using index for group-by：同using index类似，在group by的时候，可以通过一个索引来检索索引树，无须回表查询
 6. using where：使用了where子句来过滤整个表的数据，如果使用了索引，那就是索引失效了。   
 7. Using sort_union(...),Using union(...), Using intersect(...)：使用特定的算法来显示扫描索引是如何合并的在使用join
    type(index_merge)的时候

二:优化方式

1.sql语句中in包含的值不要太多

mysql中对in做了相应的优化,即将in常量全部存储在一个数组里面,而且这个数组是排序好的,但是如果数据套多,长生消耗比较大,如果是连续的in(…) 可以使用between替代

2.select语句必须得指定字段名,不要使用*

3.当只需要一条数据的时候,就是要limit,这样可以是explain中的type类型可以达到const级别,速度非常快

4.如果排序字段没有用索引,就尽量少用排序

5.如果限制条件中其他字段没有索引,尽量少用or,

 or两边的条件,如果有一个不是索引字段,而其他条件也不是索引字段,会造成该查询不走索引的情况,很多时候使用union all或union 代替or会更好

6.尽量使用union all 提到union

 因为union all不需要全局去重,而union是全局去重,会进行全局排序,消耗资源,可以使用union all 再 group by

7.不使用order by rand()

8.in和exists

 使用in会先执行子查询,适合外表大而内表小的情况
 使用exists会先访问外层表,适合外表小而内表大的情况

9.使用合理的分页

	原sql语句: select * from table_name limit 66666,20;
	优化语句: select * from table_name where id > 66666 limit 20;

10.分段查询

11.避免在where条件中对字段使用null值判断,因为对于null的判断会导致引擎放弃使用索引而进行全表扫描

12.不建议使用%前缀模糊查询,会导致索引失效而进行全表扫描

如果一定要是有%进行模糊查询,建议使用全文索引,在创建全文索引之前询问DBA
创建全文索引方式:(innoDB不支持全文索引)
1.创建表的时候
create table table_name(fields .., fulltext(索引字段1,索引字段2...))
2.插入全文索引
alter table table_name add fulltext index 索引名称(索引字段1,索引字段2...)

使用全文索引
 SELECT * FROM article WHERE MATCH(title, content) AGAINST('查询字符串')

13.避免在where子句中对字段进行表达式操作

原sql:select * from table where age*2 = 36;
优化为:select * from table where age = 36/2;

14.避免隐式类型转换

where 条件中的column字段数据类型和键表的时候不一致,就会进行隐式转换

15.对联合索引来说,要遵守最左前缀法则

例如:
联合索引中有id,name,和age三个字段,可以只使用id字段或者使用id,name字段
但是不可以使用name和age字段,因为无法使用索引
所以联合索引注意顺序

16.必要时可以使用force index来强制查询某个索引

有的时候mysql优化器采用它认为合适的索引来检索sql语句,但是可能它所采用的索引并不是我们想要的,这是就可以采用force index来强制优化器使用我们指定的索引

17.注意范围查询语句

对于联合索引来说,如果存在范围查询,比如between > < 等条件,会造成后面的索引字段失效

18.关于join优化

尽量使用inner join 而不是left / right join
因为inner join 的时候mysql会自动选择小表作为驱动表,而left join会选择左表作为驱动表,right join会选择右表作为驱动表