项目中常用的19条MySQL优化
Hollis
共 3245字,需浏览 7分钟
·
2020-10-01 03:49
作者:zhangqh
segmentfault.com/a/1190000012155267
![](https://filescdn.proginn.com/60de39dee405aee48f8fd4116eca8faa/c5625143c40d16124422abdc23c75086.webp)
type列,连接类型。一个好的sql语句至少要达到range级别。杜绝出现all级别 key列,使用到的索引名。如果没有选择索引,值是NULL。可以采取强制索引方式 key_len列,索引长度 rows列,扫描行数。该值是个预估值 extra列,详细说明。注意常见的不太友好的值有:Using filesort, Using temporary
二、SQL语句中IN包含的值不应过多
select id from table_name where num in(1,2,3)
对于连续的数值,能用 between 就不要用 in
了;再或者使用连接来替换。三、SELECT语句务必指明字段名称
四、当只需要一条数据的时候,使用limit 1
五、如果排序字段没有用到索引,就尽量少排序
六、如果限制条件中其他字段没有索引,尽量少用or
七、尽量用union all代替union
union
和union all
的差异主要是前者需要将结果集合并后再进行唯一性过滤操作,这就会涉及到排序,增加大量的CPU运算,加大资源消耗及延迟。当然,union all
的前提条件是两个结果集没有重复数据。八、不使用ORDER BY RAND()
select id from `table_name`
order by rand() limit 1000;
select id from `table_name` t1 join
(select rand() * (select max(id) from `table_name`) as nid) t2
on t1.id > t2.nid limit 1000;
九、区分in和exists, not in和not exists
select * from 表A where id in (select id from 表B)
select * from 表A where exists
(select * from 表B where 表B.id=表A.id)
关于not in和not exists,推荐使用not exists,不仅仅是效率问题,not in可能存在逻辑问题。如何高效的写出一个替代not exists的sql语句?
select colname … from A表
where a.id not in (select b.id from B表)
select colname … from A表 Left join B表 on
where a.id = b.id where b.id is null
十、使用合理的分页方式以提高分页的效率
select id,name from table_name limit 866613, 20
select id,name from table_name where id> 866612 limit 20
十一、分段查询
![](https://filescdn.proginn.com/94ee8770d8e55b808646789c766a686b/111c69f90d924f3c2e444e53aadb785a.webp)
十二、避免在 where 子句中对字段进行 null 值判断
十三、不建议使用%前缀模糊查询
LIKE “%name”
或者LIKE “%name%”
,这种查询会导致索引失效而进行全表扫描。但是可以使用LIKE “name%”
。%name%
?![](https://filescdn.proginn.com/a12928453892028541ec15d44155e842/fa33714bb38d8ea6268e899e49571b06.webp)
select id,fnum,fdst from table_name where user_name like '%zhangsan%';
。这样的语句,普通索引是无法满足查询需求的。庆幸的是在MySQL中,有全文索引来帮助我们。ALTER TABLE `table_name` ADD FULLTEXT INDEX `idx_user_name` (`user_name`);
select id,fnum,fdst from table_name where match(user_name) against('zhangsan' in boolean mode);
注意:在需要创建全文索引之前,请联系DBA确定能否创建。同时需要注意的是查询语句的写法与普通索引的区别
十四、避免在where子句中对字段进行表达式操作
select user_id,user_project from table_name where age*2=36;
select user_id,user_project from table_name where age=36/2;
十五、避免隐式类型转换
![](https://filescdn.proginn.com/5e712bad79412c140e777fc1e4eab90d/40b6b939fd54a529bee72773189dfccf.webp)
十六、对于联合索引来说,要遵守最左前缀法则
id
,name
,school
,可以直接用id字段,也可以id
,name
这样的顺序,但是name
,school
都无法使用这个索引。所以在创建联合索引的时候一定要注意索引字段顺序,常用的查询字段放在最前面十七、必要时可以使用force index来强制查询走某个索引
十八、注意范围查询语句
between
,>
,<
等条件时,会造成后面的索引字段失效。十九、关于JOIN优化
![](https://filescdn.proginn.com/f3ec4a9bef9534422fc4a6916eb7deec/6fce755095615f9fb752b135aac2eaa6.webp)
LEFT JOIN A表为驱动表 INNER JOIN MySQL会自动找出那个数据少的表作用驱动表 RIGHT JOIN B表为驱动表
注意:MySQL中没有full join,可以用以下方式来解决
select * from A left join B on B.name = A.name where B.name is null
union all
select * from B;
尽量使用inner join,避免left join
合理利用索引
被驱动表的索引字段作为on的限制字段。
利用小表去驱动大表
![](https://filescdn.proginn.com/8660a6780111133f009f4245d28856f0/88e9344e6f0489d6f404f852be41dbf9.webp)
巧用STRAIGHT_JOIN
inner join
是由mysql选择驱动表,但是有些特殊情况需要选择另个表作为驱动表,比如有group by
、order by
等「Using filesort」
、「Using temporary」
时。STRAIGHT_JOIN
来强制连接顺序,在STRAIGHT_JOIN
左边的表名就是驱动表,右边则是被驱动表。在使用STRAIGHT_JOIN
有个前提条件是该查询是内连接,也就是inner join
。其他链接不推荐使用STRAIGHT_JOIN
,否则可能造成查询结果不准确。![](https://filescdn.proginn.com/f8247d7ad3777d0c0c85d9427ce7130e/838a252c6d37329b32db78801fed0039.webp)
有道无术,术可成;有术无道,止于术
欢迎大家关注Java之道公众号
好文章,我在看❤️
评论