进阶05-SQL优化

最新推荐文章于 2024-06-03 10:42:48 发布

秦川Code

最新推荐文章于 2024-06-03 10:42:48 发布

阅读量15

点赞数

分类专栏： MySQL 文章标签： sql oracle 数据库

本文链接：https://blog.csdn.net/m0_57288751/article/details/134257541

版权

MySQL 专栏收录该内容

21 篇文章 0 订阅

订阅专栏

插入数据

insert

批量插入数据
1. Insert into tb_test values(1,‘Tom’),(2,‘Cat’),(3,‘Jerry’);
手动控制事务

start transaction;
insert 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,'Jerry');
commit;

主键顺序插入，性能高于乱序插入

主键优化

页分裂：页分裂（Page Split）是在数据库管理系统中发生在数据页（Page）级别的一种操作，它发生在当一个数据页已经满了，无法再容纳更多的数据时，需要将部分数据迁移到一个新的数据页以保证数据的存储完整性。

数据页已满：当一个数据页已经存储了最大数量的数据行（或者达到了数据库引擎的设定的最大容量）时，这个数据页就被认为是满的。
新页创建：当需要在表中插入新的数据行时，但对应的数据页已经满了，数据库引擎会创建一个新的空白数据页。
数据移动：然后，数据库引擎会将原数据页中的一部分数据行移动到新的数据页中，以腾出足够的空间来容纳新的数据行。
更新索引：在数据移动完成后，数据库引擎会更新相关的索引，以保证索引的正确性。

页合并：页合并（Page Merge）是在数据库管理系统中发生在数据页（Page）级别的一种操作，它发生在当一个数据页中的数据行数量变得过少，而且相邻的数据页也具有相对较少的数据行时，数据库引擎会将这两个相邻的数据页合并成一个以节省空间。

数据页过少：当一个数据页中的数据行数量变得过少，可能是因为删除操作导致了数据行的减少，或者是由于之前的页分裂导致的。
相邻数据页：如果相邻的数据页也具有相对较少的数据行，数据库引擎会将这两个相邻的数据页识别为候选页，准备进行合并。
数据移动：数据库引擎会将候选页中的数据行移动到一个新的数据页中，以保证数据的连续性。同时，这两个原数据页也会被标记为可回收的。
更新索引：在数据移动完成后，数据库引擎会更新相关的索引，以保证索引的正确性。

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

注意点：

满足业务需求的情况下，尽量降低主键长度
插入数据时候选择顺序插入，选择 AUTO_INCREMENT 自增主键
不要使用UUID做主键或者其他自然主键比如身份证号
业务操作时避免对主键的修改

order by优化

MySQL的排序，有两种方式：

Using filesort : 通过表的索引或全表扫描，读取满足条件的数据行，然后在排序缓冲区sort buffer中完成排序操作，所有不是通过索引直接返回排序结果的排序都叫 FileSort 排序。
Using index : 通过有序索引顺序扫描直接返回有序数据，这种情况即为 using index，不需要额外排序，操作效率高。
对于以上的两种排序方式，Using index的性能高，而Using filesort的性能低，我们在优化排序操作时，尽量要优化为 Using index。

优化原则：

根据排序字段建立合适的索引，多字段排序时，也遵循最左前缀法则。
尽量使用覆盖索引。
多字段排序, 一个升序一个降序，此时需要注意联合索引在创建时的规则（ASC/DESC）。
如果不可避免的出现filesort，大数据量排序时，可以适当增大排序缓冲区大小 sort_buffer_size(默认256k)。

group by优化

在分组操作时，可以通过索引来提高效率。
分组操作时，索引的使用也是满足最左前缀法则的。

limit 优化

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

explain select * from tb_sku t , (select id from tb_sku order by id
limit 2000000,10) a where t.id = a.id;

count优化

MyISAM 引擎把一个表的总行数存在了磁盘上，因此执行 count(*) 的时候会直接返回这个数，效率很高；但是如果是带条件的count，MyISAM也慢。

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

如果说要大幅度提升InnoDB表的count效率，主要的优化思路：自己计数(可以借助于redis这样的数据库进行,但是如果是带条件的count又比较麻烦了)。

count用法

update优化

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

update course set name = 'javaEE' where id = 1;

当我们在执行删除的SQL语句时，会锁定id为1这一行的数据，然后事务提交之后，行锁释放。

update course set name = 'SpringBoot' where name = 'PHP';

当我们开启多个事务，在执行上述的SQL时，我们发现行锁升级为了表锁。导致该update语句的性能大大降低。
因为name字段上没有索引，所以行锁升级为表锁。