第一步:找出哪些sql语句需要优化,我们要在mysql中开启慢查询,查出到底哪些sql语句需要进行优化
第二步:找到这些需要优化的sql语句后,使用explain关键字(在这些sql语句前面增加explain关键字再)来分析这条sql语句的执行计划(比如看看这条sql语句中是否有用到索引,是否真正走了索引,如果没有走索引索引失效的原因是什么)
假设我们定义慢查询:查询时间超过100ms就是慢查询
如果查询时间为0.14577293s。超过了100ms,那就是慢查询
慢查询日志:会记录所有查询时间超过long_query_time秒或者无法使用索引的查询语句。找出这些慢查询语句,然后对这些语句进行优化(默认情况下,这个日志是不开启的)
long_query_time的默认值是10,即认为超过10秒的查询语句就认为是慢查询,但是实际中往往将这个值设置为1
找到慢查询的语句后用explain进行分析,type为all说明进行了全表扫描
表结构:
这张表有一个索引,由city_id和third_cate_id(三级类目id)两个字段组成的联合索引
通过某些条件来查询白名单用户
select * from 'white_user'
where user_id=xxx and status=xxx and level=xxx and type=xxx
limit 0,10;
1.建立合适的索引,使用组合索引的时候一定要遵循最左匹配原则,让查询尽量走索引
比如当你的查询语句是select XXXXX where b=1 and c=1,那么建立的索引就应该是
(b,c,a),而不是(a,b,c)来满足最左匹配原则
2.如果不是要查出所有字段,千万不要写select *,只把你想要的字段select就行
sql优化方法:
1建立索引后,看看索引是否失效了,如果失效了就要相办法不要让索引失效,而是让查询走索引
参考这篇文章:索引失效 的场景
2.排序优化
add index test_index(a,b,c) //添加一个复合索引
select XXXXX from xxxx where a=1 and b>1 order by c desc limit 1
此时索引会用到a,b,但是不会用到c
进行优化:删除索引,重新创建一个新的索引
drop index test_index on 表名
add index test_index(a,c) //重新创建一个复合索引
select XXXXX from xxxx where a=1 and b>1 order by c desc limit 1
3.多表关联查询优化
左连接的时候(一般会将数据量小的表作为左连接的左表,同理,右连接的时候将数据量小的表作为右连接的右表),左表会进行全表扫描(不可避免),所以右表最好加上索引
比如select * from A left join B on A.aaa=B.bbb
给B表的bbb这一列建立索引
另外还有一些优化方法:
1.查询语句中不要使用select *
2.尽量减少子查询,而是使用内连接(inner join),外连接(left join,right join)代替
3.减少使用IN或者NOT IN ,使用exists,not exists或者关联查询(left join,right join,inner join)语句替代
4.尽量避免在where子句中使用or,尽量使用union或者union all代替or,避免使用:
select id from table where num=10 or num=20
5.尽量避免在 where 子句中使用!=或<>操作符,否则将引擎放弃使用索引而进行全表扫描
6.应尽量避免在 where 子句中对字段进行 null 值判断,否则将导致引擎放弃使用索引而进行全表
7.分解联表查询,分解成多个单表查询