mysql优化-sql语句

mysql5.6用户手册读书笔记

使用explain语句查看sql语句的执行计划。

优化查询

1.在结果集不变的情况下尽量简化WHERE子句，比如一些条件的合并，不必要的括号（有时候会因为容易看懂而添加一些括号），常量值的传递。因为如果你自己不简化，mysql优化引擎就得帮你来简化。

2.使用索引覆盖扫描

3.索引有时候会被忽略，如果优化器认为走索引的成本更高，会放弃走索引。比如使用 <> 的时候，此时应该选用其他等价的方式来实现查询。

4.索引条件推送。通过如下例子来说明索引条件推送：
假设people表上存在索引（zipcode,lastname,firstname），看如下查询语句

SELECT * FROM people  WHERE zipcode='95054'  AND lastname LIKE '%etrunia%'  AND address LIKE '%Main Street%';

MYSQL能够根据索引查找到zipcode='95045'的记录，但是由于%开头的LIKE语句是不走索引的，如果没有索引条件推送，MYSQL不能在存储引擎层面把满足WHERE条件的记录找出来，而是必须先找出zipcode='95045'的记录，传回到MYSQL服务器层面，再由服务器层面进行过滤。应用了索引条件推送，MYSQL就能在存储引擎层面完成WHERE条件的过滤，从而避免把无用的记录返回到服务器层。

索引条件推送只能应用在二级索引上。

索引条件推送通过系统变量optimizer_switch中的index_condition_pushdown来控制，默认为启用.

5.使用二级索引扩展

MYSQL自动把主键添加到二级索引中。

在5.6.9之前优化器并不会利用二级索引中的主键来做优化。

是否利用二级索引中的主键来做优化由系统变量optimizer_switch中的use_index_extensions控制

6.外连接的连接字段使用相同的CHARACTER SET和COLLATE

7.嵌套连接的优化

MYSQL针对嵌套连接进行了优化，提供一个buffer pool连临时保存外层循环列表，并把这个buffer pool传入内层循环，以此来减少内层循环表的扫描次数。每次嵌套循环都会分配一个新的buffer pool。

MYSQL提供参数join_buffer_size来控制这个buffer pool的大小。

8.避免全表扫描

在下列情况下，MYSQL会选择全表扫描。

a.表太小了。

b.在查询条件里边没有合适的索引

c.索引的选择性很差，查询结果覆盖索引中的绝大多数行。

如何避免：

a.ANALYZE TABLE更新表的统计信息，使优化器能做出正确的判断。

b.使用FORCE INDEX

c.启动的时候或者运行时设置max_seeks_for_key=1000，告诉优化器走索引最多查询1000次，使优化器选择走索引。

优化insert

1.同一个客户端往同一张表中插入多条记录使用insert...values (...),(...)...比insert...values(...)快。

2.使用load data infile从文件中导入数据会比使用insert语句快20倍以上。

3.往有默认值的列插入数据，如果插入值和默认值一致，则让其取默认值比较快。

4.批量插入数据时，取消唯一性约束和外键约束的检查