MySQL(五)之SQL优化

插入数据

• insert优化

• 批量插入

insert into tb_user values (1,'张三'),(2,'李四'),(3,'王五');

• 手动提交事务

start transaction;    //开启事务
imsert into tb test values(1,'Tom'),(2,'Cat'),(3,Jerry');
insert into tb test values(4,'Tom'),(5,'Cat'),(6,Jerry');
insert into tb test values(7,'Tom'),(8,'Cat'),(9,!erry');
commit:    //提交事务

• 主键顺序插入

• 大数据插入数据

如果一次性需要插入大批量数据，使用inset语句插入性能较低，此时可以使用MySQL数据库提供的lad指令进行插入。操作如下:

#客户端连接服务器时，加上参数 --local-infile
mysql --local-infile -u root -p;
#查看全局参数local_infile，0为关闭，1为开启，默认为关闭
select @@local_infile;
#设置全局参数local_infile为1
set global local_infile = 1;
#执行load指令将准备好的数据，加载到表结构中
load data local infile '数据文件路径' into 表名 fields terminated by ',' lines terminated by '\n';

演示如下：

将准备好的100w条数据加载到表中

load data local infile '/root/load_user_100w_sort.sql' into table tb_user fields terminated by ',' lines terminated by '\n';

主键优化

• 数据组织方式

在InnoBDB存储引擎中，表数据都是根据主键顺序组织存放的，这种存储方式的表称为索引组织表。

• 页分裂

页可以为空，也可以填充一半，也可以填充100%，每个页包含了2-N行数据（如果一行数据过大，会行溢出），根据主键排列。

主键顺序插入

主键乱序插入

当你插入50时，此时会开辟一个新的数据页，然后会找到第一个数据页50%的位置，将超出50%的数据移动到新开辟的数据页，然后将需要插入的数据连接到新开辟页的数据的后面，此时，页的数据顺序改变，从而改变链表指针，下图演示，从而形成页分裂。

• 页合并

当删除一行记录时，实际上记录并没有被物理删除，只是记录被标记(flaged)为删除并且它的空间变得允许被其他记录声明使用。

当页中删除的记录达到 MERGE THRESHOLD(默认为页的50%)，InnoDB会开始寻找最靠近的页(前或后)看看是否可以将两个页合并以优化空间使用。

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

当逐渐删除主键13、14、15、16后，返现2#和3#数据页都有50%空闲，于是3#数据页的数据合并到2#页

• 主键设计原则

• 满足业务需求的情况下，尽量降低主键长度。

对于一张表，聚集索引只有一个，二级索引可以有很多个，二级索引的叶子结点存放的是主键，主键越长，磁盘占用越多，并且搜索时消耗大量的磁盘IO；

• 插入数据时，尽量选择顺序插入，选择使用AUTO_INCREMENT自增主键；

• 尽量不要选择UUID（随机字符串）做主键或者是其他自然主键，如身份证号；

• 业务操作时，避免对主键的修改。

order by优化

• 根据排序字段建立合适的索引，多字段排序时，也遵循最左前缀法则

• 尽量使用覆盖索引。

• 多字段排序,一个升序一个降序，此时需要注意联合索引在创建时的规则 (ASC/DESC)

• 如果不可避免的出现filesort，大数据量排序时，可以适当增大排序缓冲区大小 sot buffer size(默认256K)。

创建联合索引(age 升序排序，phone 倒序排序)
create index idx_user_age_phone_ad on tb_user(age asc ,phone desc);

举个栗子

当执行计划中Extra字段出现Using filesort时说明性能低

group by优化

• 在分组操作时，可以通过索引来提高效率。

• 分组操作时，索引的使用也是满足最左前缀法则的

limit优化

在大数据量的情况下，越往后性能越低。

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

count优化

• MyISAM引擎把一个表的总行数存在了磁盘上，因此执行count(*)的时候会直接返回这个数，效率很高；

• InnoDB引擎就麻烦了，它执行 count(*)的时候，需要把数据一行一行地从引警里面读出来，然后累积计数；

• count的几种用法

• count() 是一个聚合函数，对于返回的结果集，一行行地判断，如果ount 函数的参数不是 NULL，累计值就加1，否则不加，最后返回累计值。

• count (*)、count (主键) 、count (字段)、count (1)。

• count(主键)

InnoDB引擎会遍历整张表，把每一行的 主键id 值都取出来，返回给服务层。服务层拿到主键后，直接按行进行累加(主键不可能为nul)。

• count(字段)

没有not nul 约束:InnODB引警会遍历整张表把每一行的字段值都取出来，返回给服务层，服务层判断是否为nul，不为nul，计数累加，有not null 约束: InnoDB 引擎会遍历整张表把每一行的字段值都取出来，返回给服务层，直接按行进行累加。

• count(1)

lnnoDB引擎遍历整张表，但不取值。服务层对于返回的每一行，放一个数字“1”;进去，直接按行进行累加。

• count(*)

innoDB引擎并不会把全部字段取出来，而是专门做了优化，不取值，服务层直接按行进行累加。

按照效率排序的话，count(字段)< count(主键 id) < count(1) count(*)，所以尽量使用 count(*)。

update优化

InnoDB的行锁是针对索引加的锁，不是针对记录加的锁,并且该索引不能失效，否则会从行锁升级为表锁。

---

例如，在之前建的course（id，name）表中，id是主键，innodb引擎默认使用行级锁，进行事务操作时，若在一号客户端进行update course set name = 'b' where id=2;但并未提交，此时在B客户端开启事务进行update course set name=‘c’ where name='C';此时B的客户端可以执行成功，因为在A中，id是主键，此时开启行锁，只把id=2那一行锁住了，允许修改其他行；

重新开启事务，在一号客户端进行update course set name = 'a' where name='A';但未提交，此时在B客户端开启事务进行update course set name=‘c’ where id='3';此时B客户端进入等待，因为A客户端中name没有索引，此时整个表加了表锁，B客户端无法修改，等到A客户端提交后，表锁释放，B客户端才可以修改，。

解决的办法是给name也加入索引，此时第二种情况就可以。

所以更新时要对有索引字段进行更新，否则并发性能降低。

---

小结