当前位置:Gxlcms > 数据库问题 > mysql优化

mysql优化

时间:2021-07-01 10:21:17 帮助过:19人阅读

id from t where num in (1,2,3)

优化后

  1. <span style="color: #0000ff;">select</span> id <span style="color: #0000ff;">from</span> t <span style="color: #0000ff;">where</span> num <span style="color: #808080;">between</span> <span style="color: #800000; font-weight: bold;">1</span> <span style="color: #808080;">and</span> <span style="color: #800000; font-weight: bold;">3</span>

select语句务必要指明字段名称

select * 增加很多不必要的消耗(cpu,io,内存,网络带宽等);增加了使用覆盖索引的可能行;当表的结构发生修改时,前端也需要更新。所以要求直接在select后面接上字段名。

当只要一条数据的时候,用limit 1

这是为了使explain中type列达到const类型

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

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

or两边的字段中,如果有一个不是索引字段,而其他

尽量用union all而不是union

union和union all的差异主要是前者需要将结果集合并后再进行唯一性过滤操作,这就会涉及到排序,增加大量的CPU运算,加大资源消耗和延迟。
前提:union all是在两个结果集没有重复条件下,否则会有重复数据的。

不使用order by rand()

从结果集中随机抽选一些数字。

  1. <span style="color: #0000ff;">select</span> id <span style="color: #0000ff;">from</span> table_a <span style="color: #0000ff;">order</span> <span style="color: #0000ff;">by</span> <span style="color: #ff00ff;">rand</span>() limit <span style="color: #800000; font-weight: bold;">10</span>

优化为

  1. <span style="color: #0000ff;">select</span> id <span style="color: #0000ff;">from</span> table_a t1 <span style="color: #808080;">join</span> (<span style="color: #0000ff;">select</span> <span style="color: #ff00ff;">rand</span>() <span style="color: #808080;">*</span> (<span style="color: #0000ff;">select</span> <span style="color: #ff00ff;">max</span>(id) <span style="color: #0000ff;">from</span> table_a) <span style="color: #0000ff;">as</span> nid) t2 <span style="color: #0000ff;">on</span> t1.id <span style="color: #808080;">></span> t2.nid limit <span style="color: #800000; font-weight: bold;">1000</span>;

区分in和exists,not in 和 not exists

  1. <span style="color: #0000ff;">select</span> <span style="color: #808080;">*</span> <span style="color: #0000ff;">from</span> table_a <span style="color: #0000ff;">where</span> id <span style="color: #808080;">in</span><span style="color: #000000;"> (
  2. </span><span style="color: #0000ff;">select</span> id <span style="color: #0000ff;">from</span><span style="color: #000000;"> table_b
  3. )</span>

相当于

  1. <span style="color: #0000ff;">select</span> <span style="color: #808080;">*</span> <span style="color: #0000ff;">from</span> table_a <span style="color: #0000ff;">where</span> <span style="color: #808080;">exists</span>(<span style="color: #0000ff;">select</span> <span style="color: #808080;">*</span> <span style="color: #0000ff;">from</span> table_b <span style="color: #0000ff;">where</span> table_b.id<span style="color: #808080;">=</span>table_a.id)

区别in和exists主要是造成了驱动顺序的改变(提高性能的关键),如果是exists,那么以外层表为驱动表,先被访问。如果是in,那么先执行子查询。所以in适合外表大而内表小的情况;exists适合外表小而内表大的情况。
关于not in和not exists,推荐使用not exists,不仅仅是效率问题,not in可能存在逻辑问题。

  1. <span style="color: #0000ff;">select</span> colname … <span style="color: #0000ff;">from</span> A表 <span style="color: #0000ff;">where</span> a.id <span style="color: #808080;">not</span> <span style="color: #808080;">in</span> (<span style="color: #0000ff;">select</span> b.id <span style="color: #0000ff;">from</span> B表)

优化后

  1. <span style="color: #0000ff;">select</span> colname … <span style="color: #0000ff;">from</span> A表 <span style="color: #808080;">Left</span> <span style="color: #808080;">join</span> B表 <span style="color: #0000ff;">on</span> <span style="color: #0000ff;">where</span> a.id <span style="color: #808080;">=</span> b.id <span style="color: #0000ff;">where</span> b.id <span style="color: #0000ff;">is</span> <span style="color: #0000ff;">null</span>

使用合理的分页方式以提高分页的效率

  1. <span style="color: #0000ff;">select</span> id,name <span style="color: #0000ff;">from</span> product limit <span style="color: #800000; font-weight: bold;">866613</span>, <span style="color: #800000; font-weight: bold;">20</span>

随着表的数据量增大,直接使用limit只会越来越慢

  1. 优化后
  1. <span style="color: #0000ff;">select</span> id,name <span style="color: #0000ff;">from</span> product <span style="color: #0000ff;">where</span> id<span style="color: #808080;">></span> <span style="color: #800000; font-weight: bold;">866612</span> limit <span style="color: #800000; font-weight: bold;">20</span>

避免在where子句中对字段进行null的判断

对于null的判断会导致引擎放弃索引而进行全表扫描

不建议使用%前缀的模糊查询

如果要使用like %name% ,这种查询会导致索引失效而进行全表扫描。但是可以使用like ‘name%‘
如果真的要%name%,建议使用全文索引
创建全文索引的sql语句

  1. <span style="color: #0000ff;">ALTER</span> <span style="color: #0000ff;">TABLE</span> table_a <span style="color: #0000ff;">ADD</span> FULLTEXT <span style="color: #0000ff;">INDEX</span> `idx_user_name` (`<span style="color: #ff00ff;">user_name</span>`);

使用全文索引的sql语句:

  1. <span style="color: #0000ff;">select</span> id,fnum,fdst <span style="color: #0000ff;">from</span> dynamic_201606 <span style="color: #0000ff;">where</span> match(<span style="color: #ff00ff;">user_name</span>) against(<span style="color: #ff0000;">‘</span><span style="color: #ff0000;">zhangsan</span><span style="color: #ff0000;">‘</span> <span style="color: #808080;">in</span> boolean mode);

注意:在需要创建全文索引之前,要联系DBA确定是否能创建。同时还要注意查询语句的写法和普通索引的区别

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

比如在where进行算术运算,会造成引擎放弃索引

  1. <span style="color: #0000ff;">select</span> <span style="color: #ff00ff;">user_id</span>,user_project <span style="color: #0000ff;">from</span> user_base <span style="color: #0000ff;">where</span> age<span style="color: #808080;">*</span><span style="color: #800000; font-weight: bold;">2</span><span style="color: #808080;">=</span><span style="color: #800000; font-weight: bold;">36</span>;

优化为

  1. <span style="color: #0000ff;">select</span> <span style="color: #ff00ff;">user_id</span>,user_project <span style="color: #0000ff;">from</span> user_base <span style="color: #0000ff;">where</span> age<span style="color: #808080;">=</span><span style="color: #800000; font-weight: bold;">36</span><span style="color: #808080;">/</span><span style="color: #800000; font-weight: bold;">2</span>;

避免隐式类型转换

比如a=1还是a=‘1‘
要提前确认数据格式,避免转换格式

注意范围查询语句

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

关于join优化

技术图片

  • left join A表为驱动表
  • inner join mysql会自动找出数据了少的表作为驱动表
  • right join B表为驱动表
  1. <span style="color: #000000;">多利用小表去驱动大表</span>
  1. <img alt="技术图片" style="margin: 0px; border: 0px currentcolor; border-image: none; display: inline; background-image: none;" title="Image [2][4]" src="https://img.gxlcms.com//Uploads-s/new/2020-10-11-qlqqti/20190707145428096417.png" width="362" height="277" border="0">
  1. <span style="color: #000000;">从原理图能够直观的看出如果能减少驱动表的话,减少嵌套循环中的循环次数,以减少IO总量及CPU运算的次数</span>

mysql优化

标签:放弃   条件   增加   执行计划   extra   查询   mamicode   text   tab   

人气教程排行