MySql的SQL优化

1.插入优化

insert优化

• 一次insert多条数据，减少事务开启关闭消耗 : insert xx values(),(),()
• 开启事务，减少事务开启关闭消耗：start transaction…insert…insert…commit
• 按主键顺序insert

load大量数据插入

• 十万百万级的数据insert插入效率低，可以提供MySql提供的load指令

• 一般100w的数据十几秒就完成了，而insert需要十几分钟

    -- 客户端连接服务端时，加上参数 -–local-infile
    mysql –-local-infile -u root -p
    -- 设置全局参数local_infile为1，开启从本地加载文件导入数据的开关
    set global local_infile = 1;
    -- 执行load指令将准备好的数据，加载到表结构中
    load data local infile '/root/sql1.log' into table tb_user fields
    terminated by ',' lines terminated by '\n' ;
  

2.主键优化

2.2聚簇索引的数据结构

• 主键就是一个聚簇索引，其数据结构是：B+树 + 页间双向指针
• 页是InnoDB的最小存储单元
• 如果不按顺序插入，页可能只存储一半的数据

2.3主键顺序插入

第一页写满了写第二页

2.4主键乱序插入：页分裂

乱序插入时可能导致：页分裂，指针重定向
耗费资源

2.5任意删除：页合并

MERGE_THRESHOLD：合并页的阈值，可以自己设置，在创建表或者创建索引时指定。

2.6主键设计原则

降低主键长度

• 主键过长会导致一个页中能存储的数据减少，因为一个页的大小是固定的16k

顺序插入(AUTO_INCREMENT自增)

顺序插入时不会产生页分裂+指针重定向问题，且id自增直接保证了顺序插入

不要修改主键

• 修改主键时相当于：一次删除，一次插入
• 可能同时造成 页分裂 和 页合并，白白耗费资源
• 改动索引B+树的结构本身也会耗费资源

不要使用自然主键

比如身份证、手机号作为主键时，不能满足有序插入，且长度过长

3.order by优化

• 使用explain select…order by…来查看是否走了索引（using index）
• 单字段索引，无论order by DECS还是ASC都走索引（DECS则Backward index scan）
• 多字段索引要满足：最左前缀、排序顺序不冲突
• 索引的创建默认是升序ACS的

3.1using filesort和using index

构造默认ACS索引

3.2索引生效的场景

1.创建索引

create index idx_user_age_phone_aa on tb_user(age,phone)

2.使用索引

explain select id,age,phone from tb_user order by age;
explain select id,age,phone from tb_user order by age , phone;

因为默认是age ACS，phone ACS，所以全降序也走索引

explain select id,age,phone from tb_user order by age desc , phone desc ;

3.3索引失效的场景

1.不满足最左前缀法则：phone走索引，age不走索引
explain select id,age,phone from tb_user order by phone , age;
2.不满足索引的构建排序：phone不走索引
explain select id,age,phone from tb_user order by age asc , phone desc ;

指定联合索引顺序

创建联合索引(age 升序排序，phone 倒序排序)

create index idx_user_age_phone_ad on tb_user(age asc ,phone desc);

此时

explain select id,age,phone from tb_user order by age asc , phone desc ;

索引就生效了。

联合索引的构建规律

age acs , phone desc：把age字段全部acs排序，再把相同的age中进行phone的desc排序。这种排序就导致了对外表现的：

• 单看age字段是有序，单看phone字段是无序的
• 即最左前缀法则导致索引失效的底层原因

order by优化原则

• 联合索引根据业务指定顺序
• 使用覆盖索引，避免索引失效
• 当不可避免大量数据的using filesort时，可以适当增加排序缓存区大小

排序缓存区sort buffer

• 不走索引时才触发，即：全表扫描数据放入缓存区中进行排序。
• 是内存中的缓存区，默认sort_buffer_size = 256k
• 超出部分走磁盘索引，效率会变低；可以设置大小

4.group by优化

• 索引失效规则参考where索引失效
• where + group by也能触发联合索引（where age … gourp by phone…可以触发age,phone的联合索引）
• 索引失效后using temporary走临时表

补充：
使用group by 时，格式一定为
select A字段 ，count(*) 或其他聚合函数 from table group by A字段

5.limit优化

• ————在数据量比较大时，如果进行limit分页查询，在查询时，越往后，分页查询效率越低。

• 因为，当在进行分页查询时，如果执行 limit 2000000,10 ，此时需要MySQL排序前2000010 记录，仅仅返回 2000000 - 2000010 的记录，其他记录丢弃，查询排序的代价非常大

• 优化思路: 一般分页查询时，通过创建 覆盖索引 能够比较好地提高性能，可以通过覆盖索引加子查询形式进行优化

• 如果不支持子查询，也可以使用内连接join

  explain select * from nxbusers as A join (select id from nxbusers limit 2,2) as B where A.id = B.id

6.count优化

• 如果数据量很大，在执行count操作时，是非常耗时的。
• MyISAM 引擎把一个表的总行数存在了磁盘上，因此执行 count(*) 的时候会直接返回这个数，效率很高； 但是如果是带条件的count，MyISAM也慢
• InnoDB 引擎就麻烦了，它执行 count(*) 的时候，需要把数据一行一行地从引擎里面读出来，然后累积计数。
• 要大幅度提升InnoDB表的count效率，主要的优化思路：自己计数(可以借助于redis这样的数据库进行,但是如果是带条件的count又比较麻烦了)

按照效率排序的话，count(字段) < count(主键 id) < count(1) ≈ count(*)，所以尽
量使用 count(*)。
count(字段)不计算该字段=null的数据，所以只有 主键 和 not null不受影响

7.update优化

• update更新数据的时候会对数据加锁，InnoDB可以加行锁（MyISAM只能加表锁）
• 当且仅当where的字段加了索引才加行锁
• 当where不走索引时，加表锁，并发效率大幅降低

总结

SQL的优化主要是针对索引使用的优化