MySQL进阶-SQL优化

最新推荐文章于 2024-10-25 10:07:12 发布
最新推荐文章于 2024-10-25 10:07:12 发布
阅读量972 收藏 16
点赞数 25
分类专栏： MySQL 文章标签： mysql sql 数据库
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/qq_56098191/article/details/134632498
版权

✨作者：猫十二懿

❤️‍🔥账号：CSDN 、掘金 、语雀 、Github

🎉公众号：猫十二懿

一、SQL优化

主要从以下的几个方面对 SQL 语句进行优化：
SQL优化
插入数据
主键优化
order by 优化
group by 优化
limit 优化
count 优化
update 优化

下面逐一对以上的内容进行讲解。

二、插入数据优化

插入数据我们，首先考虑到的就是 insert 语句，那么当使用此语句的时候向一次性往数据库中插入多条语句，就比较消耗性能，下面有三个优化方式：

2.1 优化方案一：批量插入数据

Insert into tb_test values(1,'Tom'),(2,'Cat'),(3,'Jerry');

2.2 优化方案二：手动控制事务

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;

2.3 优化方案三：顺序插入

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

主键乱序插入 : 8 1 9 21 88 2 4 15 89 5 7 3
主键顺序插入 : 1 2 3 4 5 7 8 9 15 21 88 89

2.4 大批量插入数据

如果一次性需要插入大批量数据(比如：几百万的记录)，使用 insert 语句插入性能较低，此时可以使用 MySQL 数据库提供的 load 指令进行插入。操作如下：

可以执行如下指令，将数据脚本文件中的数据加载到表结构中：

-- 客户端连接服务端时，加上参数 -–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';
load data local infile '插入的数据文件路径' into table 插入的表 fields terminated by '分割符' lines terminated by '换行符';

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

实例演示：

  1. 创建表结构
CREATE TABLE `tb_user` (
	`id` INT(11) NOT NULL AUTO_INCREMENT,
	`username` VARCHAR(50) NOT NULL,
	`password` VARCHAR(50) NOT NULL,
	`name` VARCHAR(20) NOT NULL,
	`birthday` DATE DEFAULT NULL,
	`sex` CHAR(1) DEFAULT NULL,
	PRIMARY KEY (`id`),
	UNIQUE KEY `unique_user_username` (`username`)
) ENGINE=INNODB DEFAULT CHARSET=utf8 ;
  1. 设置参数
-- 客户端连接服务端时，加上参数 -–local-infile
mysql –-local-infile -u root -p

-- 设置全局参数local_infile为1，开启从本地加载文件导入数据的开关
set global local_infile = 1;
  1. load 加载数据

数据文件获取：
链接：https://pan.baidu.com/s/18rao9wEwQFj7ftdYLYk51A?pwd=oyyg
提取码：oyyg

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

mysql> load data local infile '/root/load_user_100w_sort.sql' into table tb_user fields terminated by ',' lines terminated by '\n' ;
Query OK, 1000000 rows affected (15.47 sec)
Records: 1000000  Deleted: 0  Skipped: 0  Warnings: 0

mysql> select count(*) from tb_user;
+----------+
| count(*) |
+----------+
|  1000000 |
+----------+
1 row in set (0.31 sec)

我们看到，插入100w的记录，15.47s就完成了，比起之前使用 insert 语句的性能提升了不少。

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

三、主键优化

在上面的插入数据优化中提到，主键顺序插入的性能是要高于乱序插入的。 现在就来介绍一下具体的原因，然后再分析一下主键又该如何设计。

3.1 数据组织方式

在InnoDB存储引擎中，表数据都是根据主键顺序组织存放的，这种存储方式的表称为索引组织表(index organized table IOT)。

行数据，都是存储在聚集索引的叶子节点上的。而我们之前也讲解过InnoDB的逻辑结构图：

在InnoDB引擎中，数据行是记录在逻辑结构 page 页中的，而每一个页的大小是固定的，默认16K，页中主要存储行数据。那也就意味着， 一个页中所存储的行也是有限的，如果插入的数据行 row 在该页存储不小，将会存储到下一个页中，页与页之间会通过指针连接。

3.2 页分裂

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

3.2.1 主键顺序插入

  1. 从磁盘中申请页， 主键顺序插入

  2. 第一个页没有满，继续往第一页插入

  3. 当第一个也写满之后，再写入第二个页，页与页之间会通过指针连接

  4. 当第二页写满了，再往第三页写入

顺序插入，当前页满了，则往下一页写入，页与页之间通过指针链接。

3.2.2 主键乱序插入

  1. 加入1#，2#页都已经写满了，存放了如图所示的数据

  2. 此时再插入 id 为50的记录，我们来看看会发生什么现象。是否会再次开启一个页，写入新的页中吗？

    肯定是不会的。因为，索引结构的叶子节点是有顺序的。按照顺序，应该存储在47之后。

  3. 但是 47 所在的 1# 页，已经写满了，存储不了50对应的数据了。 那么此时会开辟一个新的页 3#。

  4. 但是并不会直接将50存入 3# 页，而是会将 1# 页后一半的数据，移动到 3# 页，然后在 3# 页，插入50。

  5. 移动数据，并插入id为50的数据之后，那么此时，这三个页之间的数据顺序是有问题的。 1# 的下一个 页，应该是3#， 3#的下一个页是2#。 所以，此时，需要重新设置链表指针。

  6. 上述的这种现象，称之为 “页分裂”，是比较耗费性能的操作。

3.2.3 页合并

  1. 目前表中已有数据的索引结构（叶子节点）如下：

  2. 当我们对已有数据进行删除时，具体的效果如下：

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

  4. 当我们继续删除 2# 的数据记录

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

  6. 删除数据，并将页合并之后，再次插入新的数据21，则直接插入 3# 页

  7. 这个里面所发生的合并页的这个现象，就称之为 “页合并”。

知识小贴士：

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

3.2.4 主键索引设计原则

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

四、order by 优化

MySQL的排序，有两种方式：

  • Using filesort ：通过表的索引或全表扫描，读取满足条件的数据行，然后在排序缓冲区sort buffer中完成排序操作，所有不是通过索引直接返回排序结果的排序都叫 FileSort 排序。
  • Using index ：通过有序索引顺序扫描直接返回有序数据，这种情况即为 using index，不需要额外排序，操作效率高。

对于以上的两种排序方式，Using index的性能高，而Using filesort的性能低，我们在优化排序操作时，尽量要优化为 Using index。

接下来，我们来做一个测试：

  1. 数据准备

把之前测试时，为 tb_user 表所建立的部分索引直接删除掉。

drop index idx_user_phone on tb_user;
drop index idx_user_phone_name on tb_user;
drop index idx_user_name on tb_user;

mysql> show index from tb_user;
+---------+------------+----------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| Table   | Non_unique | Key_name             | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression |
+---------+------------+----------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| tb_user |          0 | PRIMARY              |            1 | id          | A         |          23 |     NULL |   NULL |      | BTREE      |         |               | YES     | NULL       |
| tb_user |          0 | idx_user_phone       |            1 | phone       | A         |          24 |     NULL |   NULL |      | BTREE      |         |               | YES     | NULL       |
| tb_user |          0 | idx_user_phone_name  |            1 | phone       | A         |      935064 |     NULL |   NULL |      | BTREE      |         |               | YES     | NULL       |
| tb_user |          0 | idx_user_phone_name  |            2 | name        | A         |      951995 |     NULL |   NULL |      | BTREE      |         |               | YES     | NULL       |
| tb_user |          1 | idx_user_name        |            1 | name        | A         |          24 |     NULL |   NULL |      | BTREE      |         |               | YES     | NULL       |
| tb_user |          1 | idx_user_pro_age_sta |            1 | profession  | A         |          16 |     NULL |   NULL | YES  | BTREE      |         |               | YES     | NULL       |
| tb_user |          1 | idx_user_pro_age_sta |            2 | age         | A         |          22 |     NULL |   NULL | YES  | BTREE      |         |               | YES     | NULL       |
| tb_user |          1 | idx_user_pro_age_sta |            3 | status      | A         |          24 |     NULL |   NULL | YES  | BTREE      |         |               | YES     | NULL       |
| tb_user |          1 | idx_user_pro         |            1 | profession  | A         |          16 |     NULL |   NULL | YES  | BTREE      |         |               | YES     | NULL       |
| tb_user |          1 | idx_email_5          |            1 | email       | A         |          23 |        5 |   NULL | YES  | BTREE      |         |               | YES     | NULL       |
+---------+------------+----------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
10 rows in set (0.00 sec)

mysql> drop index idx_user_phone on tb_user;
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> drop index idx_user_phone_name on tb_user;
Query OK, 0 rows affected (0.00 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> drop index idx_user_name on tb_user;
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

  1. 执行排序SQL

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

    mysql> explain select id,age,phone from tb_user order by age;
+----+-------------+---------+------------+------+---------------+------+---------+------+--------+----------+----------------+
| id | select_type | table   | partitions | type | possible_keys | key  | key_len | ref  | rows   | filtered | Extra          |
+----+-------------+---------+------------+------+---------------+------+---------+------+--------+----------+----------------+
|  1 | SIMPLE      | tb_user | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 971649 |   100.00 | Using filesort |
+----+-------------+---------+------------+------+---------------+------+---------+------+--------+----------+----------------+
1 row in set, 1 warning (0.00 sec)

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

    mysql> explain select id,age,phone from tb_user order by age, phone;
+----+-------------+---------+------------+------+---------------+------+---------+------+--------+----------+----------------+
| id | select_type | table   | partitions | type | possible_keys | key  | key_len | ref  | rows   | filtered | Extra          |
+----+-------------+---------+------------+------+---------------+------+---------+------+--------+----------+----------------+
|  1 | SIMPLE      | tb_user | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 971649 |   100.00 | Using filesort |
+----+-------------+---------+------------+------+---------------+------+---------+------+--------+----------+----------------+
1 row in set, 1 warning (0.00 sec)

    由于 age，phone 都没有索引，所以此时再排序时，出现 Using filesort， 排序性能较低。

  2. 创建索引

    -- 创建索引
create index idx_user_age_phone_aa on tb_user(age,phone);

  3. 创建索引后，根据age，phone进行升序排序

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

    mysql> explain select id,age,phone from tb_user order by age;
+----+-------------+---------+------------+-------+---------------+-----------------------+---------+------+--------+----------+-------------+
| id | select_type | table   | partitions | type  | possible_keys | key                   | key_len | ref  | rows   | filtered | Extra       |
+----+-------------+---------+------------+-------+---------------+-----------------------+---------+------+--------+----------+-------------+
|  1 | SIMPLE      | tb_user | NULL       | index | NULL          | idx_user_age_phone_aa | 48      | NULL | 971649 |   100.00 | Using index |
+----+-------------+---------+------------+-------+---------------+-----------------------+---------+------+--------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

    建立索引之后，再次进行排序查询，就由原来的Using filesort， 变为了 Using index，性能就是比较高的了。

  4. 创建索引后，根据age，phone进行降序排序

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

    mysql> explain select id,age,phone from tb_user order by age desc , phone desc ;
+----+-------------+---------+------------+-------+---------------+-----------------------+---------+------+--------+----------+----------------------------------+
| id | select_type | table   | partitions | type  | possible_keys | key                   | key_len | ref  | rows   | filtered | Extra                            |
+----+-------------+---------+------------+-------+---------------+-----------------------+---------+------+--------+----------+----------------------------------+
|  1 | SIMPLE      | tb_user | NULL       | index | NULL          | idx_user_age_phone_aa | 48      | NULL | 971649 |   100.00 | Backward index scan; Using index |
+----+-------------+---------+------------+-------+---------------+-----------------------+---------+------+--------+----------+----------------------------------+
1 row in set, 1 warning (0.00 sec)

    也出现 Using index， 但是此时 Extra 中出现了 Backward index scan，这个代表反向扫描索引，因为在 MySQL 中我们创建的索引，默认索引的叶子节点是从小到大排序的，而此时我们查询排序时，是从大到小，所以，在扫描时，就是反向扫描，就会出现 Backward index scan。 在 MySQL8 版本中，支持降序索引，我们也可以创建降序索引。

  5. 根据phone，age进行升序排序，phone在前，age在后。

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

    mysql> explain select id,age,phone from tb_user order by phone , age;
+----+-------------+---------+------------+-------+---------------+-----------------------+---------+------+--------+----------+-----------------------------+
| id | select_type | table   | partitions | type  | possible_keys | key                   | key_len | ref  | rows   | filtered | Extra                       |
+----+-------------+---------+------------+-------+---------------+-----------------------+---------+------+--------+----------+-----------------------------+
|  1 | SIMPLE      | tb_user | NULL       | index | NULL          | idx_user_age_phone_aa | 48      | NULL | 971649 |   100.00 | Using index; Using filesor |
+----+-------------+---------+------------+-------+---------------+-----------------------+---------+------+--------+----------+-----------------------------+
1 row in set, 1 warning (0.00 sec)

    排序时,也需要满足最左前缀法则,否则也会出现 filesort。因为在创建索引的时候， age是第一个字段，phone是第二个字段，所以排序时，也就该按照这个顺序来，否则就会出现 Usingfilesort。

  6. 根据age，phone进行降序一个升序，一个降序

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

    mysql> explain select id,age,phone from tb_user order by age asc , phone desc;
+----+-------------+---------+------------+-------+---------------+-----------------------+---------+------+--------+----------+-----------------------------+
| id | select_type | table   | partitions | type  | possible_keys | key                   | key_len | ref  | rows   | filtered | Extra                       |
+----+-------------+---------+------------+-------+---------------+-----------------------+---------+------+--------+----------+-----------------------------+
|  1 | SIMPLE      | tb_user | NULL       | index | NULL          | idx_user_age_phone_aa | 48      | NULL | 971649 |   100.00 | Using index; Using filesort |
+----+-------------+---------+------------+-------+---------------+-----------------------+---------+------+--------+----------+-----------------------------+
1 row in set, 1 warning (0.00 sec)

    因为创建索引时，如果未指定顺序，默认都是按照升序排序的，而查询时，一个升序，一个降序，此时就会出现Using filesort。

    为了解决上述的问题，我们可以创建一个索引，这个联合索引中 age 升序排序，phone 倒序排序。

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

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

  8. 然后再次执行如下SQL

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

    mysql> explain select id,age,phone from tb_user order by age asc,phone desc;
+----+-------------+---------+------------+-------+---------------+------------------+---------+------+--------+----------+-------------+
| id | select_type | table   | partitions | type  | possible_keys | key              | key_len | ref  | rows   | filtered | Extra       |
+----+-------------+---------+------------+-------+---------------+------------------+---------+------+--------+----------+-------------+
|  1 | SIMPLE      | tb_user | NULL       | index | NULL          | idx_phone_age_ad | 48      | NULL | 971649 |   100.00 | Using index |
+----+-------------+---------+------------+-------+---------------+------------------+---------+------+--------+----------+-------------+
1 row in set, 1 warning (0.01 sec)

    升序/降序联合索引结构图示：

由上述的测试，我们得出 order by优化原则：

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

五、group by 优化

分组操作，我们主要来看看索引对于分组操作的影响。

首先我们先将 tb_user 表的索引全部删除掉 。

drop index idx_user_pro_age_sta on tb_user;
drop index idx_email_5 on tb_user;
drop index idx_user_age_phone_aa on tb_user;
drop index idx_user_age_phone_ad on tb_user;

mysql> show index from tb_user;
+---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| Table   | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression |
+---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| tb_user |          0 | PRIMARY  |            1 | id          | A         |          23 |     NULL |   NULL |      | BTREE      |         |               | YES     | NULL       |
+---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
1 row in set (0.00 sec)

接下来，在没有索引的情况下，执行如下SQL，查询执行计划：

explain select profession , count(*) from tb_user group by profession;

mysql> explain select profession , count(*) from tb_user group by profession ;
+----+-------------+---------+------------+------+---------------+------+---------+------+--------+----------+-----------------+
| id | select_type | table   | partitions | type | possible_keys | key  | key_len | ref  | rows   | filtered | Extra           |
+----+-------------+---------+------------+------+---------------+------+---------+------+--------+----------+-----------------+
|  1 | SIMPLE      | tb_user | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 971649 |   100.00 | Using temporary |
+----+-------------+---------+------------+------+---------------+------+---------+------+--------+----------+-----------------+
1 row in set, 1 warning (0.00 sec)

然后，我们在针对于 profession ， age， status 创建一个联合索引。

create index idx_pro_age_sta on tb_user(profession,age,status);

紧接着，再执行前面相同的SQL查看执行计划。

mysql> explain select profession , count(*) from tb_user group by profession;
+----+-------------+---------+------------+-------+-----------------+-----------------+---------+------+--------+----------+-------------+
| id | select_type | table   | partitions | type  | possible_keys   | key             | key_len | ref  | rows   | filtered | Extra       |
+----+-------------+---------+------------+-------+-----------------+-----------------+---------+------+--------+----------+-------------+
|  1 | SIMPLE      | tb_user | NULL       | index | idx_pro_age_sta | idx_pro_age_sta | 54      | NULL | 971649 |   100.00 | Using index |
+----+-------------+---------+------------+-------+-----------------+-----------------+---------+------+--------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

再执行如下的分组查询SQL，查看执行计划：

mysql> explain select profession , count(*) from tb_user group by profession,age;
+----+-------------+---------+------------+-------+-----------------+-----------------+---------+------+--------+----------+-------------+
| id | select_type | table   | partitions | type  | possible_keys   | key             | key_len | ref  | rows   | filtered | Extra       |
+----+-------------+---------+------------+-------+-----------------+-----------------+---------+------+--------+----------+-------------+
|  1 | SIMPLE      | tb_user | NULL       | index | idx_pro_age_sta | idx_pro_age_sta | 54      | NULL | 971649 |   100.00 | Using index |
+----+-------------+---------+------------+-------+-----------------+-----------------+---------+------+--------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

mysql> explain select age , count(*) from tb_user group by age;
+----+-------------+---------+------------+-------+-----------------+-----------------+---------+------+--------+----------+------------------------------+
| id | select_type | table   | partitions | type  | possible_keys   | key             | key_len | ref  | rows   | filtered | Extra                        |
+----+-------------+---------+------------+-------+-----------------+-----------------+---------+------+--------+----------+------------------------------+
|  1 | SIMPLE      | tb_user | NULL       | index | idx_pro_age_sta | idx_pro_age_sta | 54      | NULL | 971649 |   100.00 | Using index; Using temporary |
+----+-------------+---------+------------+-------+-----------------+-----------------+---------+------+--------+----------+------------------------------+
1 row in set, 1 warning (0.00 sec)

我们发现，如果仅仅根据age分组，就会出现 Using temporary ；而如果是 根据profession,age两个字段同时分组，则不会出现 Using temporary。原因是因为对于分组操作，在联合索引中，也是符合最左前缀法则的。

所以，在分组操作中，我们需要通过以下两点进行优化，以提升性能：

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

六、limit 优化

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

我们一起来看看执行limit分页查询耗时对比：

mysql> select * from tb_user limit 0,10;
10 rows in set (0.00 sec)

mysql> select * from tb_user limit 100,10;
10 rows in set (0.00 sec)

mysql> select * from tb_user limit 1000,10;
10 rows in set (0.00 sec)

mysql> select * from tb_user limit 50000,10;
10 rows in set (0.01 sec)

mysql> select * from tb_user limit 500000,10;
10 rows in set (0.16 sec)

mysql> select * from tb_user limit 900000,10;
10 rows in set (0.28 sec)

通过测试我们会看到，越往后，分页查询效率越低，这就是分页查询的问题所在。

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

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

explain select u.* from tb_user u,(select id from tb_user order by id limit 900000,10) a where u.id = a.id;

mysql> explain select u.* from tb_user u,(select id from tb_user order by id limit 900000,10) a where u.id = a.id;
+----+-------------+------------+------------+--------+---------------+---------+---------+------+--------+----------+-------------+
| id | select_type | table      | partitions | type   | possible_keys | key     | key_len | ref  | rows   | filtered | Extra       |
+----+-------------+------------+------------+--------+---------------+---------+---------+------+--------+----------+-------------+
|  1 | PRIMARY     | <derived2> | NULL       | ALL    | NULL          | NULL    | NULL    | NULL | 900010 |   100.00 | NULL        |
|  1 | PRIMARY     | u          | NULL       | eq_ref | PRIMARY       | PRIMARY | 4       | a.id |      1 |   100.00 | NULL        |
|  2 | DERIVED     | tb_user    | NULL       | index  | NULL          | PRIMARY | 4       | NULL | 900010 |   100.00 | Using index |
+----+-------------+------------+------------+--------+---------------+---------+---------+------+--------+----------+-------------+
3 rows in set, 1 warning (0.00 sec)

七、count 优化

 select count(*) from tb_user;

在之前的测试中，我们发现，如果数据量很大，在执行count操作时，是非常耗时的。

  • MyISAM 引擎把一个表的总行数存在了磁盘上，因此执行 count(*) 的时候会直接返回这个数，效率很高； 但是如果是带条件的count，MyISAM也慢。
  • InnoDB 引擎就麻烦了，它执行 count(*) 的时候，需要把数据一行一行地从引擎里面读出来，然后累积计数。

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

7.1count 用法

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

用法：count（*）、count（主键）、count（字段）、count（数字）

count 用法含义
count(主键)InnoDB 引擎会遍历整张表，把每一行的 主键id 值都取出来，返回给服务层。服务层拿到主键后，直接按行进行累加(主键不可能为null)
count(字段)没有not null 约束 : InnoDB 引擎会遍历整张表把每一行的字段值都取出来，返回给服务层，服务层判断是否为null，不为null，计数累加。有not null 约束：InnoDB 引擎会遍历整张表把每一行的字段值都取出来，返回给服务层，直接按行进行累加。
count(数字)InnoDB 引擎遍历整张表，但不取值。服务层对于返回的每一行，放一个数字“1”进去，直接按行进行累加。
count(*)InnoDB引擎并不会把全部字段取出来，而是专门做了优化，不取值，服务层直接按行进行累加。

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

八、update 优化

我们主要需要注意一下update语句执行时的注意事项。

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

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

但是当我们在执行如下SQL时。

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

当我们开启多个事务，在执行上述的SQL时，我们发现行锁升级为了表锁。 导致该 update 语句的性能大大降低。

InnoDB的行锁是针对索引加的锁，不是针对记录加的锁 ，并且该索引不能失效，否则会从行锁升级为表锁 。也就是说我这边事务没有提交的话，其他关于这个表的update都不会执行成功，导致该update语句的性能大大降低。

noDB 引擎遍历整张表，但不取值。服务层对于返回的每一行，放一个数字“1”进去，直接按行进行累加。 |
| count(*) | InnoDB引擎并不会把全部字段取出来，而是专门做了优化，不取值，服务层直接按行进行累加。 |

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

八、update 优化

我们主要需要注意一下update语句执行时的注意事项。

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

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

但是当我们在执行如下SQL时。

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

当我们开启多个事务，在执行上述的SQL时，我们发现行锁升级为了表锁。 导致该 update 语句的性能大大降低。

InnoDB的行锁是针对索引加的锁，不是针对记录加的锁 ，并且该索引不能失效，否则会从行锁升级为表锁 。也就是说我这边事务没有提交的话，其他关于这个表的update都不会执行成功，导致该update语句的性能大大降低。

猫十二懿
关注
专栏目录
【第15天】MYSQL进阶-查询优化-慢查询日志（SQL 小虚竹)
小虚竹的专栏
09-30 1万+
慢查询日志windows和linux下的解决方案
【第18天】MYSQL进阶-查询优化- SHOW PROFILE（SQL 小虚竹)
热门推荐
小虚竹的专栏
10-08 1万+
show profile
MySQL提高批量insert的性能
eagle89的专栏
12-24 5120
insert的性能
Mysql使用中的性能优化——单次插入和批量插入的性能差异
方亮的专栏
06-08 1280
一般Mysql的客户端和服务端不在一台机器上，所以它们之间的通信需要通过网络进行。我们本次实验，希望抛开网络的影响，测试不同SQL方案在Mysql服务器上的执行效率的对比。于是我们使用“存储过程”来辅助测试。
MySQL—SQL优化：插入数据（insert、大批量插入）、主键优化（数据组织方式、页分裂、页合并、索引设计）、order by 、group by 、limit 、count 、update 优化
放下华子我只抽RuiKe5的博客
05-04 6797
​ 在之前的测试中，我们发现，如果数据量很大，在执行count操作时，是非常耗时的。MyISAM 引擎把一个表的总行数存在了磁盘上，因此执行 count(*) 的时候会直接返回这个数，效率很高；但是如果是带条件的count，MyISAM也慢。nnoDB 引擎就麻烦了，它执行 count(*) 的时候，需要把数据一行一行地从引擎里面读出来，然后累积计数。
mysql快速插入大量数据
m0_58160096的博客
07-26 1万+
最近做性能测试，需要模拟生产环境的数据量，在插入mysql百万、千万级数据的时候发现直接使用插入sql效率极低，百度了一翻，找到几种方式，但用下来还是有很快速的方式，推荐第四种。
MySQL优化：批量插入大数据4种实用、讲究方案的测试
彼岸尚有荣光在，应不断记录总结！！！
03-29 1万+
记录MySQL插入大数据一些方案的测试心得，即for循环插入、拼接SQL语句、批量插入saveBatch()、循环插入 + 开启批处理模式，该4种情况的测试内容，最终以平均耗时来预测各方案的性能问题，可得知开启批处理模式的方案效率是最高的，但是必须注意一个点：一定要开启批处理模式，在URL地址加入rewriteBatchedStatements = true 参数，否则即使是在批量插入情况下，也是无法发挥该方法优点。
MySQL进阶-SQL优化（插入和主键优化）
04-06
MySQL进阶-SQL优化（插入和主键优化）
MySQL进阶-合.zip，进阶一和进阶二的合并版本
04-13
《MySQL进阶深度解析》 在信息技术领域，MySQL作为一个开源的关系型数据库管理系统，因其高效、稳定及易用性，被广泛应用于各种规模的企业和项目中。本资料集“MySQL进阶-合.zip”正是为了帮助用户提升MySQL技能，...
面试必问、工作实用的MYSQL进阶之SQL优化大全
07-27
4. **SQL优化技巧**： - 使用`EXPLAIN`分析SQL执行计划，关注`type`和`extra`，目标是降低全表扫描（All）并提高到ref或range级别。 - 尽量使用`VARCHAR`而非`CHAR`，节省空间。 - 避免使用`SELECT *`，应明确列...
MySQL实现批量插入以优化性能的教程
12-15
对于一些数据量较大的系统，数据库面临的问题除了查询效率低下，还有就是数据入库时间长。特别像报表系统，每天花费在数据导入上的时间可能会长达几个小时或十几个小时之久。因此，优化数据库插入性能是很有意义的。 经过对MySQL innodb的一些性能测试，发现一些可以提高insert效率的方法，供大家参考参考。 1. 一条SQL语句插入多条数据。 常用的插入语句如：   INSERT INTO `insert_table` (`datetime`, `uid`, `content`, `type`) VALUES ('0', 'userid_0', 'content_0', 0); INSER
MySQL批量SQL插入性能优化
12-15
对于一些数据量较大的系统，数据库面临的问题除了查询效率低下，还有是数据入库时间长。特别像报表系统，每天花费在数据导入上的时间可能会长达几个小时或十几个小时之久。因此，优化数据库插入性能是很有意义的。 　　经过对MySQL innodb的一些性能测试，发现一些可以提高insert效率的方法，供大家参考参考。 　　1. 一条SQL语句插入多条数据。 　　常用的插入语句如： 　　INSERT INTO `insert_table` (`datetime`， `uid`， `content`， `type`) 　　VALUES ('0'， 'userid_0'， 'content_0'， 0
MySQL批量SQL插入各种性能优化
chongxiao2168的博客
06-28 85
MySQL批量SQL插入各种性能优化 最近在工作中正好遇到了在数据备份/还原的时候耗时太长， 下面这个方案作为记录。因为线上环境和工作环境网络互相隔绝， 每个星期只能对数据进行人工拷贝还原，现有数据大概在300W左右， 每次导出要1H+，还原也需要1H+。打算重写个导出工具导出增量日...
MySQL批量插入数据详解
良月柒
06-04 6006
批量插入数据是MySQL中提高插入效率、减少开销的重要手段。本文详细介绍了几种常见的批量插入方法，包括使用单条INSERT语句、命令、存储过程以及使用事务保证原子性等。同时，提供了一些优化和最佳实践建议。
数据库大批量SQL插入性能优化
shenliba的博客
04-21 893
我们可以参照InnoDB使用的B+tree索引，如果每次插入记录都在索引的最后面，索引的定位效率很高，并且对索引调整较小；如果插入的记录在索引中间，需要B+tree进行分裂合并等处理，会消耗比较多计算资源，并且插入记录的索引定位效率会下降，数据量较大时会有频繁的磁盘操作。这里提供一些测试对比数据，分别是进行单条数据的导入与转化成一条SQL语句进行导入，分别测试1百、1千、1万条数据记录。下面提供随机数据与顺序数据的性能对比，分别是记录为1百、1千、1万、10万、100万。，在事务内才进行真正插入处理操作。
MySQL 批量插入详解：快速提升大数据导入效率的实战方法
最新发布
jieshenai的博客
10-25 858
使用 Python 和 PyMySQL 库高效地将大量数据批量插入 MySQL 数据库。通过学生信息表的实战示例，详细讲解了批量插入的实现方法，包括生成随机数据、批量执行插入操作、事务管理和性能优化技巧。相比逐行插入，批量插入显著提高了数据导入的效率。还提供了性能优化建议，如调整批次大小、禁用自动提交和索引优化等，帮助读者掌握批量插入的最佳实践，提高大规模数据处理的性能。
MySQL批量插入数据的四种方案（性能测试对比）
<sdffdsfsdfdfs>sfsfsfsdfsdffds</sdfsDS>Fsd
05-16 2748
????????关注后回复“进群”，拉你进程序员交流群????????本文记录个人使用MySQL插入大数据总结较实用的方案，通过对常用插入大数据的4种方式进行测试，即for循环单条、拼接SQL、批量插入saveBatch()、循环 + 开启批处理模式，得出比较实用的方案心得。一、前言最近趁空闲之余，在对MySQL数据库进行插入数据测试，对于如何快速插入数据的操作无从下手，在仅1W数据量的情况下，竟花费接近47s，实在...
mysql 大量数据插入优化
zhoupenghui168的博客
04-04 1954
mysql 大量数据插入优化
MySQL进阶：索引优化与事务原理
MySQL是一个广泛使用的开源关系型数据库管理系统，它提供了多种功能，包括各种存储引擎、索引机制、SQL优化、视图、存储过程、触发器以及锁定策略等，以满足不同场景的需求。在进阶学习过程中，深入理解这些概念和...
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包

打赏作者

猫十二懿

你的支持就是写文章的动力!

¥1 ¥2 ¥4 ¥6 ¥10 ¥20
扫码支付：¥1
获取中
扫码支付

您的余额不足，请更换扫码支付或充值

打赏作者

实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值