时间:2021-07-01 10:21:17 帮助过:19人阅读
优化后
- <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 * 增加很多不必要的消耗(cpu,io,内存,网络带宽等);增加了使用覆盖索引的可能行;当表的结构发生修改时,前端也需要更新。所以要求直接在select后面接上字段名。
这是为了使explain中type列达到const类型
or两边的字段中,如果有一个不是索引字段,而其他
union和union all的差异主要是前者需要将结果集合并后再进行唯一性过滤操作,这就会涉及到排序,增加大量的CPU运算,加大资源消耗和延迟。
前提:union all是在两个结果集没有重复条件下,否则会有重复数据的。
从结果集中随机抽选一些数字。
- <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>
优化为
- <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>;
- <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;"> (
- </span><span style="color: #0000ff;">select</span> id <span style="color: #0000ff;">from</span><span style="color: #000000;"> table_b
- )</span>
相当于
- <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可能存在逻辑问题。
- <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表)
优化后
- <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>
- <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只会越来越慢
- 优化后
- <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>
对于null的判断会导致引擎放弃索引而进行全表扫描
如果要使用like %name% ,这种查询会导致索引失效而进行全表扫描。但是可以使用like ‘name%‘
如果真的要%name%,建议使用全文索引
创建全文索引的sql语句
- <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语句:
- <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进行算术运算,会造成引擎放弃索引
- <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>;
优化为
- <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,>,<等条件时,会造成后面的索引字段失效。
- <span style="color: #000000;">多利用小表去驱动大表</span>
- <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">
- <span style="color: #000000;">从原理图能够直观的看出如果能减少驱动表的话,减少嵌套循环中的循环次数,以减少IO总量及CPU运算的次数</span>
mysql优化
标签:放弃 条件 增加 执行计划 extra 查询 mamicode text tab