优化途径:
1. 获取慢查询sql
2. explain + 慢sql分析
3. show profile 查询sql在数据库服务器里的执行细节和生命周期
4. 数据库服务器参数调优
索引优化
- 索引全值匹配;
- 最佳最前缀法则;
- 不在索引列上做任何操作(计算、函数、自动或者手动的类型转换<如:varchar 类型的值不加单引号>);
- 存储引擎不能使用索引中范围条件右边的列;
- 尽量使用覆盖索引(索引列和查询列一致),减少select * ;
- 使用不等于(!=或者<>)的时候无法使用索引;
- is null ,is not null 也无法使用索引;
- like 以通配符开头(’%haha’)会导致索引失效,必须要使用时尽量利用覆盖索引;
- 字符串不加单引号索引失效;
- 少用or,用它来连接时会索引失效。
带头大哥不能死,中间兄弟不能断,范围之后全失效
sql语句优化
- 小表驱动大表
select * from A where id in (select id from B)
等价于:
for select id from B
for select * from A where A.id = B.id
当B表的数据集小于A表时,用in由于exists
select * from A where exists (select 1 from B where B.id = A.id)
等价于:
for select * from A
for select * from B where B.id = A.id
当B表的数据集小于A表时,用exist是由于in
order by
mysql支持两个方式排序,Filesort和Index。Index效率高,扫描索引本身完成排序,filesort效率低
- Index需满足两种情况:
- order by 语句使用索引最左前列
- 使用where子句和order by子句条件组合满足使用最左前列
尽可能在使用列上完成排序,遵照最佳左前缀
-
Filesort
双路排序
单路排序
max_length_for_sort_data和sor_buffer_size -
为排序使用索引
mysql两种排序方式,文件排序和扫描有序索引排序,它能为排序和查询使用相同的索引
- 能使用索引最左前缀
- 如果where使用索引的最左前缀定义为常量,则order by能使用索引
KEY a_b_c(a,b,c)
where a = const and b > const order by b,c
- 不能使用索引进行排序的情况
ordre by a ASC,b DESC,c DESC /**排序顺序不一致**/
where a = const order by a,d /**d不是索引一部分**/
where a in (...) order by b,c /**对于排序来说多个相等条件也是范围查询**/
丢失第一个或中间索引字段也无法使用
group by
实质是先排序在分组,遵照最佳做前缀;优化同order by
当无法使用使用列时,增大 max_length_for_sort_data和sor_buffer_size参数设置
where高于having,限定条件有限写在where中