![cover](https://i-blog.csdnimg.cn/direct/f8a68eeeadb8474b97c3aa803f21cb92.jpeg)
MySQL 进阶(三)【SQL 优化】
MySQL SQL 优化
1、SQL 优化
1.1、插入数据优化
1.1.1、Insert 优化
1、批量插入
插入多条数据时,不建议使用单条的插入语句,而是下面的批量插入:
INSERT INTO tb_name VALUES (),(),(),...;
批量插入建议一次批量 500~100 条,如果数据量比较大,建议通过多条批量插入语句来插入;
2、手动提交事务
MySQL 默认会开启事务,但是默认每执行一次插入语句就开启和关闭一次事务,所以会有大量的事务启动关闭开销;建议使用手动提交事务
START TRANSACTIONS;
INSERT INTO tb_name VALUES (),(),(),...;
INSERT INTO tb_name VALUES (),(),(),...;
INSERT INTO tb_name VALUES (),(),(),...;
...
COMMIT;
3、主键顺序插入
在插入数据时,建议尽量顺序插入主键,而不是乱序插入:
主键顺序插入(高效):1 2 3 4 5 ...
主键乱序插入(低效):5 3 4 2 1 ...
1.1.2、load 大批量插入数据
使用 load 指令将本地磁盘文件中的数据插入到数据库表当中:
# 客户端连接MySQL服务器时,加上参数 --local-infile
mysql --local-infile -uroot -p
# 设置全局参数 local_infile = 1
set global local_infile = 1;
# 执行 load 指令将准备好的数据加载到表中
load data local infile '/path_to_data' into table table_name fields terminated by ',' lines terminated by '\n';
这里的 load 指令有点像 HQL 中的 load:
LOAD DATA [LOCAL] INPATH '/opt/module/data/xxx.txt' TO TABLE table_name;
HQL 的 load 命令并不需要指定分隔符,因为在建表的时候我们已经在 row format 中设置的文件的分隔符了;此外,这里想到今天使用 HQL load 命令的一些需要注意的问题:
- 在向分桶表 load 数据的时候,不能从 local 直接 load,而是得先上传到 hdfs 上,再从 hdfs load 到分桶表才行
1.2、主键优化
1.2.1、数据组织方式
在 InnoDB 存储引擎当中,表数据都是根据主键顺序组织存放的,这种存储方式的表称为索引组织表(IOT)
之前我们在学习索引的时候知道,InnoDB 存储引擎中的索引可以分为聚集索引和二级索引,而聚集索引正是由主键构成的一颗B+Tree,它的叶子节点存储的是行数据;
接下来,我们看一下当我们往数据库表中插入数据的时候,它的流程是什么样的?
1.2.2、页分裂
page 可以填满,也可以为空,但在 InnoDB 存储引擎规定,在一个 page 中至少包含 2 行数据;
主键顺序插入
当主键顺序插入时,一切都非常平静:
主键乱序插入
下面,我们看一下主键乱序插入时的情况:
上面,我们的主键都是乱序插入的,可以看到,现在来了一个主键为 50 的 row,按道理它是应该放到主键为 47 的前面,主键为 23 的后面(也就是 page 1)的,但是显然 page 1 现在已经存满了,那么接下来就会发生页分裂:
首先,page 1 (因为主键为 23 的数据当前理应插入到 page 1)会把自己 50% 之后的数据移动到一个新的 page 当中,然后将要主键为 23 的新数据页添加进去,最后将原本页之间的连接断开,重新建立页的连接;
所以,不难想到如果是大量数据的场景下,主键乱序插入时会出现频繁的页分裂现象,性能很低;与新增数据相反的是删除数据,删除数据又会引起页合并:
1.2.3、页合并
在 MySQL 中,当删除一行记录时,实际上数据并不会被物理删除,只是记录被标记为删除并且它的空间变得允许被其它记录回收使用;
当页中被删除的记录达到了 50%,InnoDB 会开始寻找最靠近的页(前或后),看看能否将两个页合并以优化空间使用;
1.2.4、主键设置原则
- 满足业务需求的情况下,尽量降低主键的长度。(因为在一张表中,聚集索引只有一个,而二级索引可以有多个,如果主键很长,二级索引也有很多,那么就会在存储时会消耗大量的磁盘空间,查询时也会消耗大量的磁盘IO)
- 插入数据时,尽量选择顺序插入,选择使用 AUTO_INCREMENT 自增主键;(不会出现页分裂现象)
- 尽量不要使用 uuid 做主键或者是其它自然主键,如身份证号;(主键长度太长,而且无序)
- 尽量避免主键的修改;
1.3、order by 优化
- Using filesort:通过表的索引或全表扫描,读取满足条件的数据行,然后在排序缓冲区 sort buffer 中完成排序操作;所有不是通过索引直接返回排序结果的排序都是 FileSort 排序;
- Using index:通过有序索引顺序扫描直接返回有序数据,这种情况就是 using index,不需要额外排序,操作效率高;
也就是说,我们在优化 order by 的时候,尽量优化成 using index;下面我们看一条普通的 order by 语句:
可以看到,在未建立索引时,order by 语句默认走的是 filesort;下面我们为 age 字段建立一个索引:
CREATE INDEX idx_student_no ON student(no);
注意:这里搜索时,我设置投影的字段是 id 和 no,其中 id 是主键,no 我们刚创建了索引,不能使用 select * ,因为只有使用覆盖索引(查询使用了索引,并且需要返回的列在该索引中全部能够找到对应的值)才能命中索引,不然索引不生效;
可以看到,即使是降序排序也是索引也是可以命中优化的;
现在我们创建一个联合索引:idx_student_no_name,做一个测试:
可以看到,当 order by 的字段顺序和联合索引相反时,只有字段 no 能被索引命中,name 不可以,这是因为违背了最左前缀法则;继续:
可以看到,当 order by 的两个字段分别升序和降序排列时,降序的字段无法被索引命中,这是因为:
这是因为默认创建索引时,索引字段都是按照升序进行排列的,所以我们可以根据之后的需求在创建索引时,根据排序规则进行创建:
CREATE INDEX idx_student_no_name_ad ON student(no asc,name desc);
此时,再次查看查询计划:
可以看到,这样就解决了字段排序规则不同的问题;
注意:
- 只有联合索引需要注意创建时的排序规则,单列索引不需要,因为单列索引默认升序,反向扫描只需要反向扫描即可。
- 如果不可避免 filesort ,大数据排序时,可以适当增加排序缓冲区的大小 sort_buffer_size (默认 256 K)
1.4、group by 优化
这里我们同样讨论的是索引对于 group by 的影响,因为分组也是通过索引来提高查询效率的:
可以看到,在未建立索引前,使用 group by 语句时效率很低(临时表),但是创建索引后就可以走索引了;
可以看到,这次当我们用 age 字段 group by 时,又出现了 temporary ,效率依然不够好,这是因为违背了联合索引的最左前缀法则;可当我们同时使用 profession 和 age 进行 group by 时,索引再次命中;
但是有时候业务逻辑不需要我们去 group by 那么多的字段怎么办?
其实,就像上面这样,我们也可以通过前面给联合索引左边的字段加个 where 条件,让它满足最左前缀法则即可;
1.5、limit 优化
在大数据场景下,比如 limit 10000000,10 ,此时 MySQL 需要排序前 100000010 条记录(存储引擎会把这 100000010 条记录返回给服务层的缓存),并返回后 10 条记录,其它丢弃,查询排序的代价非常大;这一类问题也叫做深度分页:
MySQL 深度分页是指在分页查询数据量比较大的表时,需要访问表中的某一段数据,而这段数据的位置非常靠后,需要通过较大的 offset 来获取目标数据。
阿里巴巴《Java 开发手册》:
1.5.1、覆盖索引 + 子查询
1.5.2、inner join 延迟关联
上面这两种方式没什么区别,下面是 inner join,上面是笛卡尔积,但是因为设置的过滤条件,所以等价于一个 inner join;对于 limit 的优化原理,简单来讲就是:控制返回的总页数。
对于上面的 limit 10000000,10 来说,它会返回给服务端 10000010 条记录,然后再根据 offset 挨个抛弃前 10000000 条记录,返回给客户端剩余的 10 条记录。
可以看出,当offset非0时,server层会从引擎层获取到很多无用的数据,而当select后面是*号时,就需要拷贝完整的行信息,拷贝完整数据跟只拷贝行数据里的其中一两个列字段耗时是不同的,这就让原本就耗时的操作变得更加离谱。
因为前面的offset条数据最后都是不要的,就算将完整字段都拷贝来了又有什么用呢,所以我们可以将sql语句修改成下面这样:
select * from tb_user where id >=(select id from tb_user order by id limit 10000000, 1) order by id limit 10;
上面这条sql语句,里面先执行子查询 select id from page order by id limit 6000000, 1, 这个操作,其实也是将在innodb中的主键索引中获取到6000000+1条数据,然后server层会抛弃前6000000条,只保留最后一条数据的id。
但不同的地方在于,在返回server层的过程中,只会拷贝数据行内的id这一列,而不会拷贝数据行的所有列,当数据量较大时,这部分的耗时还是比较明显的。
在拿到了上面的id之后,假设这个id正好等于10000000,那sql就变成了
select * from tb_user where id >=(10000000) order by id limit 10;
这样 innodb 再走一次主键索引,通过B+树快速定位到id=6000000的行数据,时间复杂度是lg(n),然后向后取10条数据。
关于深度分页,知乎这篇文章讲的很不错,我也是受启发于这篇文章;
1.6、count 优化
- count 是一个聚合函数,对于返回的结果集,一行一行进行判断,只有不是 NULL 才会计数(count(字段)的时候,count(*)或者count(id)依然计算null);
SELECT COUNT(*) FROM table_name;
上面的 SQL 是查询当前表的总行数,不同的存储引擎的效率是不一样的:
- 对于 MyISAM 而言,它会把表的总行数存到磁盘上,所以不加条件直接查询 count(*) 会直接返回结果,O(1)的时间复杂度;
- 而对 InnoDB 而言,它就只能遍历整张表了,性能很低;
可以看到,如果 count(*) 的查询语句中包含 where 过滤条件,不管是 MYISAM 还是 InnoDB ,性能都很差,所以我们需要对它进行优化:
1.6.1、count 的几种用法
对于 count,我们使用的无非就是那几种:
- count(主键)
- InnoDB 会遍历整张表,把每一行主键取出来返回给服务层,服务层拿到主键后直接进行累加(主键不可能为空);
- count(字段)
- InnoDB 会遍历整张表,把每一行字段值取出来返回给服务层,如果字段有 not null 约束,那么就直接按行累加;如果没有,那么就对非 null 的值进行计数
- count(1)
- InnoDB 遍历整张表,但是不取值。服务层会对返回的每一行放一个数字 1 进去,直接按行进行累加
- count(*)
- InnoDB 同样遍历整张表,但是不会把字段取出来,而是专门做了优化。服务层直接按行进行累加
按照排序效率:count(字段) < count(主键) < count(1) < count(*) ,所以尽量使用 count(*),因为数据库专门对它做了优化;
1.7、update 优化
关于 update 语句需要注意的就是,update 的条件一定要是索引字段(比如主键),因为只有更新条件的字段是索引列才会是行锁,否则将是表锁
可以看到,当我们的更新条件是 no 字段时(不是索引列),当另一个客户端去更新数据时直接被阻塞,最后甚至超时更新失败;
所以,当我们在使用 update 语句的时候,一定要注意尽量使用索引字段做为更新条件去更新,否则就会出现行锁升级为表锁,并发性能就会降低;因为 InnoDB 的行锁是针对索引加的锁,而不是针对记录加的锁!
总结
- 插入数据
- inset 语句,大数据量(分成500~1000的记录批量插入)建议使用批量插入,而且建议手动事务(避免频繁创建销毁事务开销)、主键顺序插入(避免页分裂,顺序插入性能高)
- 大批量数据:load data local infile
- 主键优化
- 主键设计应尽量短、顺序插入(建议 auto_increment 而不是 uuid,比如身份证号,不仅长度长,而且无序)
- order by 优化
- using index:直接返回数据(不需要再去服务层缓冲区排序),性能高
- using filesort:需要将查询返回的结果去服务层缓冲区去排序
- 所以在对 order by 进行优化时,其实是使用索引来进行优化的;涉及导排序的字段尽量建立索引,同时注意创建索引时的升序降序问题
- 尽量使用覆盖索引而不是 select *
- group by 优化
- 索引,多字段分组时遵循最左前缀法则
- limit 优化
- 深度分页性能低,使用覆盖索引 + 子查询
- count 优化
- count(字段) < count(主键) < count(1) < count(*)
- update 优化
- update 的条件字段尽量使用索引字段(尽量主键),InnoDB 的行锁是针对索引加的锁,而不是针对记录加的锁!
所以,总之我们在做 SQL 优化的时候,其实基本都是在针对索引进行优化;
更多推荐
所有评论(0)