sql优化

最新推荐文章于 2022-04-06 22:35:36 发布

熙翎

最新推荐文章于 2022-04-06 22:35:36 发布

阅读量167

点赞数

文章标签：数据库 mysql

本文链接：https://blog.youkuaiyun.com/qq_42853570/article/details/116016648

版权

1、应尽量避免在 where 子句中使用!=或<>操作符，否则将引擎放弃使用索引而进行全表扫描。

2、最左前缀匹配原则。
mysql会一直向右匹配直到遇到范围查询(>、<、between、like)就停止匹配。所以要尽量把“=”条件放在前面，把这些条件放在最后。

3、当取出的数据超过全表数据的20%时，不会使用索引。

4、尽量将or 转换为 union all。

5、很多时候用 exists 代替 in 是一个好的选择。用not exists代替not in。

6、不要写一些没有意义的查询，如需要生成一个空表结构。
select col1,col2 into #t from t where 1=0 这类代码不会返回任何结果集，但是会消耗系统资源的，应改成这样。

7、尽量避免使用is null或is not null。

8、ORDER BY 子句只在以下的条件下使用索引：
ORDER BY中所有的列必须包含在相同的索引中并保持在索引中的排列顺序。
ORDER BY中不能既有ASC也有DESC。

9、索引不是越多越好。mysql需要资源来维护索引，任何数据的变更（增删改）都会连带修改索引的值。所以，需要平衡考虑索引带来的查询加速和增删改减速。
其他注意事项
1.尽量避免使用select *
2.尽量使用表连接（join）代替子查询select * from t1 where a in (select b from t2)
3.性能方面，表连接 > (not) exists > (not) in

10、除非确实需要去掉重复的行，否则尽量使用union all而不是union。因为union会自带distinct操作，代价很大

使用explain查看sql性能

1、explain用法：在select之前加上explain即可。

例如：explain select * from test;

注意：explain并不会真正运行语句，而是只返回执行计划。
2、explain执行计划各个字段的意义：

1）id：语句的执行顺序，倒序执行

2）select_type：主要有以下几个类型：

lsimple：表示简单的select，没有union和子查询

lprimary：最外层的select。在有子查询的语句中，最外面的select查询就是primary

lunion：union语句的第二个或者说是后面那一个

lunion result：union的结果

lsubquery: 子查询中的第一个 select

3）table：涉及的表。

4）type：连接类型。主要有以下几个：（重点查看）

lconst：说明只有一个匹配行，使用了主键或唯一性索引。通常是最优化的情况。

leq_ref，ref，ref_or_null：表示走了简单索引

lindex_merge：表示使用了多个索引的组合

lrange：表示通过索引取出了一个范围内的值。例如where a in (1,2)

lindex：表示对索引进行了全扫描

lALL：表示全表扫描

注意：以上类型从上到下性能越来越差。

5）possible_keys：可供使用的索引

6）keys：实际使用的索引

7）key_gen：索引长度

8）ref：显示使用哪个列或常数与索引一起从表中选择行

9）rows：读取的行数。（重点查看）

10）Extra：备注

具体sql语句优化：
https://blog.youkuaiyun.com/u011277123/article/details/78904569?utm_source=blogxgwz0

https://blog.youkuaiyun.com/enweitech/article/details/52669664?utm_source=blogxgwz0

具体sql语句从分析到优化：
https://www.cnblogs.com/lfs2640666960/p/9147768.html

show variables like “sql_mode”;
– 设置sql_mode
set sql_mode=’’;
set sql_mode=‘NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES’;

show variables like “sql_mode”;

set sql_mode=(select REPLACE(@@sql_mode,‘ONLY_FULL_GROUP_BY’,’’));

– //普通索引
– alter table busi_waybill_cargo add index busi_waybill_cargo_waybill_id (waybill_id) ;
– //唯一索引
– alter table busi_waybill_cargo add unique (waybill_id) ;
– //主键索引
– alter table busi_waybill_cargo add primary key (waybill_id) ;

– 删除索引
– alter table busi_waybill_cargo drop index busi_waybill_cargo_waybill_id ;
– drop index create_time_index on busi_waybill;

1.你有索引index1（a,b,c)，这是是联合索引，也叫复合索引、多列索引；
2.你有索引index2（a），index3（b），index4（c），
   然后你的sql中有and、or或union之类的条件对三个索引分别进行条件扫描，
   然后将各自的结果进行合并，这叫索引合并，这时你用explain查看执行计划会看到有“index merge”字样。