mysql 查询优化

末蓝、 2022-12-24 05:59 262阅读 0赞

**MySQL 的查询语句的优化有两种办法：  
第一种使用explain；第二种使用show profile**  
**一：explain的使用**  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2pveV90b20_size_16_color_FFFFFF_t_70_pic_center]  
**type是连接类型，一个好的SQL至少级别要到ranger，杜绝出现all**  
key使用到的索引名，如果没有选择引用，值为null,可以采用强制索引方法  
key\_len列，索引长度  
rows列，扫描行数，该值是预估值  
extra列，详情说明，常见不友好的值：using filesort ,using temporay  
**二：SQL语句中in包含的值不应该过多**  
MySQL对于in做了相应的优化，将in中的常量全部存储在一个数组里面，这个数值是排好序的，如果数值较多，产生的消耗就比较大、  
**三：select语句务必指明字段的名称**  
使用select \* 会把表的所有的数据都取出来，这样会增加数据库的IO消耗  
**四：当只需要一条数据的时候，使用limit 1（这是为了使explain中的type列到达const类型）  
五：使用order by 的时候尽量使用索引的字段  
六：尽量少用or  
七：尽量用union all 代替 union（union和union all 的差异主要是前者需要将结果集合并后进行唯一性过滤操作，这样就会涉及到排序，同时增加大量的CPU运算，union all的前提条件是俩个结果没有重复是得数据）**  
**八：不使用 order by rand()**  
**九：区分in和exists, not in 和not exists** (in和exists主要造成的是驱动顺序的改变，如果是exists，那么外层表为驱动表，先被访问，如果是in 那么闲执行子查询，所以in适合于外表大而内表小的情况；exists适合于外表小而内表达的情况)  
**十：使用合理的分页方式提高分页效率**  
select id,name from table\_name limit 866613, 20  
使用上述 sql 语句做分页的时候，可能有人会发现，随着表数据量的增加，直接使用 limit 分页查询会越来越慢。  
优化的方法如下：可以取前一页的最大行数的 id，然后根据这个最大的id来限制下一页的起点。比如此列中，上一页最大的 id 是 866612。sql 可以采用如下的写法：  
select id,name from table\_name where id> 866612 limit 20  
**十一：分段查询  
十二：避免在where子句中对于字段进行null值判断**  
使用null 判断会导致引擎放弃使用索引转为全表扫描  
**十三：不建议使用%前缀进行模糊查询**  
like ‘%name’ 或者 ‘%name%’ 这样的查询会导致索引失效进行全表查询，但是可以使用like ‘name%’  
如何解决这个问题，这个时候就需要使用全文索引来实现，  
**十四：避免在where语句后使用表达式的操作  
十五： 采用联合索引的时候，要遵循最左匹配原则  
十六：注意查询的访问语句  
十七：优化join的使用**  
（inner join 在没有其他过滤的条件下，MySQL会自动选择小表作为驱动表，但是使用left join 在驱动表的选择上遵循的是左边驱动右边的原则，即left join左边的表名为驱动表）

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2pveV90b20_size_16_color_FFFFFF_t_70_pic_center]: /images/20221120/bb66b906b9af4833a5323f47b3b94f2a.png