MySQL 高级篇 -- 查询优化及索引优化

最新推荐文章于 2024-05-16 22:46:16 发布
最新推荐文章于 2024-05-16 22:46:16 发布
阅读量2.6k 收藏 29
点赞数 1
分类专栏： MySQL 文章标签： 数据库 mysql 索引
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/weixin_42480750/article/details/123655034
版权

文章目录

一、数据准备

学员表 插 50万 条， 班级表 插 1万 条。

  1. 建表
CREATE TABLE `class` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `className` VARCHAR(30) DEFAULT NULL,
  `address` VARCHAR(40) DEFAULT NULL,
  `monitor` INT NULL ,
  PRIMARY KEY (`id`)
) ENGINE=INNODB AUTO_INCREMENT=1;


CREATE TABLE `student` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `stuno` INT NOT NULL ,
  `name` VARCHAR(20) DEFAULT NULL,
  `age` INT DEFAULT NULL,
  `classId` INT DEFAULT NULL,
  PRIMARY KEY (`id`)
  #CONSTRAINT `fk_class_id` FOREIGN KEY (`classId`) REFERENCES `t_class` (`id`)
) ENGINE=INNODB AUTO_INCREMENT=1;
  1. 设置参数
# 命令开启：允许创建函数设置：
set global log_bin_trust_function_creators=1;
  1. 创建函数
#随机产生字符串
DELIMITER //
CREATE FUNCTION rand_string(n INT)
  RETURNS VARCHAR(255)
BEGIN
  DECLARE chars_str VARCHAR(100) DEFAULT 'abcdefghijklmnopqrstuvwxyzABCDEFJHIJKLMNOPQRSTUVWXYZ';
  DECLARE return_str VARCHAR(255) DEFAULT '';
  DECLARE i INT DEFAULT 0;
  WHILE i < n DO
    SET return_str =CONCAT(return_str,SUBSTRING(chars_str,FLOOR(1+RAND()*52),1));
    SET i = i + 1;
  END WHILE;
  RETURN return_str;
END //
DELIMITER ;

#用于随机产生多少到多少的编号
DELIMITER //
CREATE FUNCTION rand_num (from_num INT ,to_num INT)
  RETURNS INT(11)
BEGIN
  DECLARE i INT DEFAULT 0;
  SET i = FLOOR(from_num +RAND()*(to_num - from_num+1)) ;
  RETURN i;
END //
DELIMITER ;
  1. 创建存储过程
# 创建往stu表中插入数据的存储过程
DELIMITER //
CREATE PROCEDURE insert_stu( START INT , max_num INT )
BEGIN
  DECLARE i INT DEFAULT 0;
  SET autocommit = 0; #设置手动提交事务
  REPEAT #循环
    SET i = i + 1; #赋值
    INSERT INTO student (stuno, name ,age ,classId ) VALUES
    ((START+i),rand_string(6),rand_num(1,50),rand_num(1,1000));
  UNTIL i = max_num
  END REPEAT;
  COMMIT; #提交事务
END //
DELIMITER ;

# 执行存储过程，往class表添加随机数据
DELIMITER //
CREATE PROCEDURE `insert_class`( max_num INT )
BEGIN
  DECLARE i INT DEFAULT 0;
  SET autocommit = 0;
  REPEAT
    SET i = i + 1;
    INSERT INTO class ( classname,address,monitor )
    VALUES (rand_string(8),rand_string(10),rand_num(1,100000));
  UNTIL i = max_num
  END REPEAT;
  COMMIT;
END //
DELIMITER ;
  1. 调用存储过程
# 执行存储过程，往class表添加1万条数据
CALL insert_class(10000);

# 执行存储过程，往stu表添加50万条数据
CALL insert_stu(100000,500000);
  1. 删除某表上的索引
DELIMITER //
CREATE PROCEDURE `proc_drop_index`(dbname VARCHAR(200),tablename VARCHAR(200))
BEGIN
  DECLARE done INT DEFAULT 0;
  DECLARE ct INT DEFAULT 0;
  DECLARE _index VARCHAR(200) DEFAULT '';
  DECLARE _cur CURSOR FOR SELECT index_name
  FROM information_schema.STATISTICS WHERE table_schema=dbname AND table_name=tablename
  AND seq_in_index=1 AND index_name <>'PRIMARY' ;
  #每个游标必须使用不同的declare continue handler for not found set done=1来控制游标的结束
  DECLARE CONTINUE HANDLER FOR NOT FOUND set done=2 ;
  #若没有数据返回,程序继续,并将变量done设为2
  OPEN _cur;
  FETCH _cur INTO _index;
  WHILE _index<>'' DO
    SET @str = CONCAT("drop index " , _index , " on " , tablename );
    PREPARE sql_str FROM @str ;
    EXECUTE sql_str;
    DEALLOCATE PREPARE sql_str;
    SET _index='';
    FETCH _cur INTO _index;
  END WHILE;
  CLOSE _cur;
END //
DELIMITER ;

# 执行存储过程
CALL proc_drop_index("dbname","tablename");

二、索引失效

请尽量避免索引失效的情况。

2.1 计算 / 函数

mysql> CREATE INDEX idx_name ON student(NAME);
Query OK, 0 rows affected (3.51 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE student.name LIKE 'abc%';
+----+-------------+---------+------------+-------+---------------+----------+---------+------+------+----------+-----------------------+
| id | select_type | table   | partitions | type  | possible_keys | key      | key_len | ref  | rows | filtered | Extra                 |
+----+-------------+---------+------------+-------+---------------+----------+---------+------+------+----------+-----------------------+
|  1 | SIMPLE      | student | NULL       | range | idx_name      | idx_name | 83      | NULL |   28 |   100.00 | Using index condition |
+----+-------------+---------+------------+-------+---------------+----------+---------+------+------+----------+-----------------------+
1 row in set, 2 warnings (0.03 sec)

mysql> EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE LEFT(student.name,3) = 'abc';
+----+-------------+---------+------------+------+---------------+------+---------+------+--------+----------+-------------+
| id | select_type | table   | partitions | type | possible_keys | key  | key_len | ref  | rows   | filtered | Extra       |
+----+-------------+---------+------------+------+---------------+------+---------+------+--------+----------+-------------+
|  1 | SIMPLE      | student | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 499086 |   100.00 | Using where |
+----+-------------+---------+------------+------+---------------+------+---------+------+--------+----------+-------------+
1 row in set, 2 warnings (0.00 sec)

mysql>

2.2 类型转换（自动或手动）

mysql> EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE name=123;
+----+-------------+---------+------------+------+---------------+------+---------+------+--------+----------+-------------+
| id | select_type | table   | partitions | type | possible_keys | key  | key_len | ref  | rows   | filtered | Extra       |
+----+-------------+---------+------------+------+---------------+------+---------+------+--------+----------+-------------+
|  1 | SIMPLE      | student | NULL       | ALL  | idx_name      | NULL | NULL    | NULL | 499086 |    10.00 | Using where |
+----+-------------+---------+------------+------+---------------+------+---------+------+--------+----------+-------------+
1 row in set, 4 warnings (0.00 sec)

mysql> EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE name='123';
+----+-------------+---------+------------+------+---------------+----------+---------+-------+------+----------+-------+
| id | select_type | table   | partitions | type | possible_keys | key      | key_len | ref   | rows | filtered | Extra |
+----+-------------+---------+------------+------+---------------+----------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | student | NULL       | ref  | idx_name      | idx_name | 83      | const |    1 |   100.00 | NULL  |
+----+-------------+---------+------------+------+---------------+----------+---------+-------+------+----------+-------+
1 row in set, 2 warnings (0.00 sec)

mysql>

2.3 联合索引范围条件右边的列

mysql> ALTER TABLE `student` ADD INDEX (name, age, classid);
Query OK, 0 rows affected (3.74 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> EXPLAIN SELECT * FROM `student` WHERE name = 'IbgDyF';
+----+-------------+---------+------------+------+---------------+------+---------+-------+------+----------+-------+
| id | select_type | table   | partitions | type | possible_keys | key  | key_len | ref   | rows | filtered | Extra |
+----+-------------+---------+------------+------+---------------+------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | student | NULL       | ref  | name          | name | 83      | const |    1 |   100.00 | NULL  |
+----+-------------+---------+------------+------+---------------+------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)

mysql> EXPLAIN SELECT * FROM `student` WHERE name = 'IbgDyF' AND age = 34;
+----+-------------+---------+------------+------+---------------+------+---------+-------------+------+----------+-------+
| id | select_type | table   | partitions | type | possible_keys | key  | key_len | ref         | rows | filtered | Extra |
+----+-------------+---------+------------+------+---------------+------+---------+-------------+------+----------+-------+
|  1 | SIMPLE      | student | NULL       | ref  | name          | name | 88      | const,const |    1 |   100.00 | NULL  |
+----+-------------+---------+------------+------+---------------+------+---------+-------------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)

mysql> EXPLAIN SELECT * FROM `student` WHERE name = 'IbgDyF' AND age = 34 AND classid = 11;
+----+-------------+---------+------------+------+---------------+------+---------+-------------------+------+----------+-------+
| id | select_type | table   | partitions | type | possible_keys | key  | key_len | ref               | rows | filtered | Extra |
+----+-------------+---------+------------+------+---------------+------+---------+-------------------+------+----------+-------+
|  1 | SIMPLE      | student | NULL       | ref  | name          | name | 93      | const,const,const |    1 |   100.00 | NULL  |
+----+-------------+---------+------------+------+---------------+------+---------+-------------------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)

mysql> EXPLAIN SELECT * FROM `student` WHERE name = 'IbgDyF' AND age > 34 AND classid = 11;
+----+-------------+---------+------------+-------+---------------+------+---------+------+------+----------+-----------------------+
| id | select_type | table   | partitions | type  | possible_keys | key  | key_len | ref  | rows | filtered | Extra                 |
+----+-------------+---------+------------+-------+---------------+------+---------+------+------+----------+-----------------------+
|  1 | SIMPLE      | student | NULL       | range | name          | name | 88      | NULL |    1 |    10.00 | Using index condition |
+----+-------------+---------+------------+-------+---------------+------+---------+------+------+----------+-----------------------+
1 row in set, 1 warning (0.00 sec)

mysql>

2.4 不等于（!= 或者 <> ）

mysql> ALTER TABLE student ADD INDEX(name);
Query OK, 0 rows affected (3.05 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> EXPLAIN SELECT * FROM student WHERE name = 'AAaABe';
+----+-------------+---------+------------+------+---------------+------+---------+-------+------+----------+-------+
| id | select_type | table   | partitions | type | possible_keys | key  | key_len | ref   | rows | filtered | Extra |
+----+-------------+---------+------------+------+---------------+------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | student | NULL       | ref  | name          | name | 83      | const |    2 |   100.00 | NULL  |
+----+-------------+---------+------------+------+---------------+------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)

mysql> EXPLAIN SELECT * FROM student WHERE name <> 'AAaABe';
+----+-------------+---------+------------+------+---------------+------+---------+------+--------+----------+-------------+
| id | select_type | table   | partitions | type | possible_keys | key  | key_len | ref  | rows   | filtered | Extra       |
+----+-------------+---------+------------+------+---------------+------+---------+------+--------+----------+-------------+
|  1 | SIMPLE      | student | NULL       | ALL  | name          | NULL | NULL    | NULL | 499086 |    50.00 | Using where |
+----+-------------+---------+------------+------+---------------+------+---------+------+--------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

mysql> EXPLAIN SELECT * FROM student WHERE name != 'AAaABe';
+----+-------------+---------+------------+------+---------------+------+---------+------+--------+----------+-------------+
| id | select_type | table   | partitions | type | possible_keys | key  | key_len | ref  | rows   | filtered | Extra       |
+----+-------------+---------+------------+------+---------------+------+---------+------+--------+----------+-------------+
|  1 | SIMPLE      | student | NULL       | ALL  | name          | NULL | NULL    | NULL | 499086 |    50.00 | Using where |
+----+-------------+---------+------------+------+---------------+------+---------+------+--------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

mysql>

2.5 IS NOT NULL

mysql> ALTER TABLE `student` ADD INDEX (age);
Query OK, 0 rows affected (2.06 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE age IS NULL;
+----+-------------+---------+------------+------+---------------+------+---------+-------+------+----------+-----------------------+
| id | select_type | table   | partitions | type | possible_keys | key  | key_len | ref   | rows | filtered | Extra                 |
+----+-------------+---------+------------+------+---------------+------+---------+-------+------+----------+-----------------------+
|  1 | SIMPLE      | student | NULL       | ref  | age           | age  | 5       | const |    1 |   100.00 | Using index condition |
+----+-------------+---------+------------+------+---------------+------+---------+-------+------+----------+-----------------------+
1 row in set, 2 warnings (0.01 sec)

mysql> EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE age IS NOT NULL;
+----+-------------+---------+------------+------+---------------+------+---------+------+--------+----------+-------------+
| id | select_type | table   | partitions | type | possible_keys | key  | key_len | ref  | rows   | filtered | Extra       |
+----+-------------+---------+------------+------+---------------+------+---------+------+--------+----------+-------------+
|  1 | SIMPLE      | student | NULL       | ALL  | age           | NULL | NULL    | NULL | 499086 |    50.00 | Using where |
+----+-------------+---------+------------+------+---------------+------+---------+------+--------+----------+-------------+
1 row in set, 2 warnings (0.00 sec)

mysql>

2.6 LIKE 以通配符 % 开头

mysql> EXPLAIN SELECT * FROM `student` WHERE name LIKE '%DyF';
+----+-------------+---------+------------+------+---------------+------+---------+------+--------+----------+-------------+
| id | select_type | table   | partitions | type | possible_keys | key  | key_len | ref  | rows   | filtered | Extra       |
+----+-------------+---------+------------+------+---------------+------+---------+------+--------+----------+-------------+
|  1 | SIMPLE      | student | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 499086 |    11.11 | Using where |
+----+-------------+---------+------------+------+---------------+------+---------+------+--------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

mysql> EXPLAIN SELECT * FROM `student` WHERE name LIKE 'DyF%';
+----+-------------+---------+------------+-------+---------------+------+---------+------+------+----------+-----------------------+
| id | select_type | table   | partitions | type  | possible_keys | key  | key_len | ref  | rows | filtered | Extra                 |
+----+-------------+---------+------------+-------+---------------+------+---------+------+------+----------+-----------------------+
|  1 | SIMPLE      | student | NULL       | range | name          | name | 83      | NULL |   66 |   100.00 | Using index condition |
+----+-------------+---------+------------+-------+---------------+------+---------+------+------+----------+-----------------------+
1 row in set, 1 warning (0.02 sec)

mysql>

2.7 OR 前后存在非索引的列

mysql> EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE age = 10 OR classid = 100;
+----+-------------+---------+------------+------+---------------+------+---------+------+--------+----------+-------------+
| id | select_type | table   | partitions | type | possible_keys | key  | key_len | ref  | rows   | filtered | Extra       |
+----+-------------+---------+------------+------+---------------+------+---------+------+--------+----------+-------------+
|  1 | SIMPLE      | student | NULL       | ALL  | age           | NULL | NULL    | NULL | 499086 |    11.88 | Using where |
+----+-------------+---------+------------+------+---------------+------+---------+------+--------+----------+-------------+
1 row in set, 2 warnings (0.00 sec)

mysql> ALTER TABLE `student` ADD INDEX (classid);
Query OK, 0 rows affected (2.37 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE age = 10 OR classid = 100;
+----+-------------+---------+------------+-------------+---------------+-------------+---------+------+-------+----------+---------------------------------------+
| id | select_type | table   | partitions | type        | possible_keys | key         | key_len | ref  | rows  | filtered | Extra                                 |
+----+-------------+---------+------------+-------------+---------------+-------------+---------+------+-------+----------+---------------------------------------+
|  1 | SIMPLE      | student | NULL       | index_merge | age,classId   | age,classId | 5,5     | NULL | 10373 |   100.00 | Using union(age,classId); Using where |
+----+-------------+---------+------------+-------------+---------------+-------------+---------+------+-------+----------+---------------------------------------+
1 row in set, 2 warnings (0.04 sec)

mysql>

三、查询优化

3.1 关联查询

  • 数据准备
# 分类
CREATE TABLE IF NOT EXISTS `type` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `card` INT UNSIGNED NOT NULL,
  PRIMARY KEY (`id`)
);

# 图书
CREATE TABLE IF NOT EXISTS `book` (
  `bookid` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `card` INT UNSIGNED NOT NULL,
  PRIMARY KEY (`bookid`)
);

#向分类表中添加20条记录
INSERT INTO `type`(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO `type`(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO `type`(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO `type`(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO `type`(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO `type`(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO `type`(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO `type`(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO `type`(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO `type`(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO `type`(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO `type`(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO `type`(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO `type`(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO `type`(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO `type`(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO `type`(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO `type`(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO `type`(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO `type`(card) VALUES(FLOOR(1 + (RAND() * 20)));

#向图书表中添加20条记录
INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));

3.1.1 left join

# 1. 驱动表 type 和被驱动表 book 都无索引都做全表扫描
mysql> EXPLAIN SELECT SQL_NO_CACHE * FROM `type` LEFT JOIN book ON type.card = book.card;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+--------------------------------------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra                                      |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+--------------------------------------------+
|  1 | SIMPLE      | type  | NULL       | ALL  | NULL          | NULL | NULL    | NULL |   20 |   100.00 | NULL                                       |
|  1 | SIMPLE      | book  | NULL       | ALL  | NULL          | NULL | NULL    | NULL |   20 |   100.00 | Using where; Using join buffer (hash join) |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+--------------------------------------------+
2 rows in set, 2 warnings (0.02 sec)

# 2. 为被驱动表添加索引，走索引而非全表扫描
mysql> CREATE INDEX Y ON book(card);
Query OK, 0 rows affected (0.22 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> EXPLAIN SELECT SQL_NO_CACHE * FROM `type` LEFT JOIN book ON type.card = book.card;
+----+-------------+-------+------------+------+---------------+------+---------+-----------------------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref                   | rows | filtered | Extra       |
+----+-------------+-------+------------+------+---------------+------+---------+-----------------------+------+----------+-------------+
|  1 | SIMPLE      | type  | NULL       | ALL  | NULL          | NULL | NULL    | NULL                  |   20 |   100.00 | NULL        |
|  1 | SIMPLE      | book  | NULL       | ref  | Y             | Y    | 4       | atguigudb10.type.card |    1 |   100.00 | Using index |
+----+-------------+-------+------------+------+---------------+------+---------+-----------------------+------+----------+-------------+
2 rows in set, 2 warnings (0.00 sec)

# 3. 为驱动表添加索引，虽然走索引但仍要全表扫描（type = index）
mysql> CREATE INDEX X ON `type`(card);
Query OK, 0 rows affected (0.04 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> EXPLAIN SELECT SQL_NO_CACHE * FROM `type` LEFT JOIN book ON type.card = book.card;
+----+-------------+-------+------------+-------+---------------+------+---------+-----------------------+------+----------+-------------+
| id | select_type | table | partitions | type  | possible_keys | key  | key_len | ref                   | rows | filtered | Extra       |
+----+-------------+-------+------------+-------+---------------+------+---------+-----------------------+------+----------+-------------+
|  1 | SIMPLE      | type  | NULL       | index | NULL          | X    | 4       | NULL                  |   20 |   100.00 | Using index |
|  1 | SIMPLE      | book  | NULL       | ref   | Y             | Y    | 4       | atguigudb10.type.card |    1 |   100.00 | Using index |
+----+-------------+-------+------------+-------+---------------+------+---------+-----------------------+------+----------+-------------+
2 rows in set, 2 warnings (0.00 sec)

# ！（此处演示为和内连接做比较）删除被驱动表的索引，被驱动表重新开始全表扫描。
mysql> DROP INDEX Y ON book;
Query OK, 0 rows affected (0.03 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> EXPLAIN SELECT SQL_NO_CACHE * FROM `type` LEFT JOIN book ON type.card = book.card;
+----+-------------+-------+------------+-------+---------------+------+---------+------+------+----------+--------------------------------------------+
| id | select_type | table | partitions | type  | possible_keys | key  | key_len | ref  | rows | filtered | Extra                                      |
+----+-------------+-------+------------+-------+---------------+------+---------+------+------+----------+--------------------------------------------+
|  1 | SIMPLE      | type  | NULL       | index | NULL          | X    | 4       | NULL |   20 |   100.00 | Using index                                |
|  1 | SIMPLE      | book  | NULL       | ALL   | NULL          | NULL | NULL    | NULL |   20 |   100.00 | Using where; Using join buffer (hash join) |
+----+-------------+-------+------------+-------+---------------+------+---------+------+------+----------+--------------------------------------------+
2 rows in set, 2 warnings (0.00 sec)

mysql>

3.1.2 innner join

# 1. 驱动表 type 和被驱动表 book 都无索引都做全表扫描
mysql> DROP INDEX X ON `type`;
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> EXPLAIN SELECT SQL_NO_CACHE * FROM `type` INNER JOIN book ON type.card = book.card;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+--------------------------------------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra                                      |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+--------------------------------------------+
|  1 | SIMPLE      | type  | NULL       | ALL  | NULL          | NULL | NULL    | NULL |   20 |   100.00 | NULL                                       |
|  1 | SIMPLE      | book  | NULL       | ALL  | NULL          | NULL | NULL    | NULL |   20 |    10.00 | Using where; Using join buffer (hash join) |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+--------------------------------------------+
2 rows in set, 2 warnings (0.00 sec)

# 2. 为被驱动表添加索引
mysql> CREATE INDEX Y ON book(card);
Query OK, 0 rows affected (0.04 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> EXPLAIN SELECT SQL_NO_CACHE * FROM `type` INNER JOIN book ON type.card = book.card;
+----+-------------+-------+------------+------+---------------+------+---------+-----------------------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref                   | rows | filtered | Extra       |
+----+-------------+-------+------------+------+---------------+------+---------+-----------------------+------+----------+-------------+
|  1 | SIMPLE      | type  | NULL       | ALL  | NULL          | NULL | NULL    | NULL                  |   20 |   100.00 | NULL        |
|  1 | SIMPLE      | book  | NULL       | ref  | Y             | Y    | 4       | atguigudb10.type.card |    1 |   100.00 | Using index |
+----+-------------+-------+------------+------+---------------+------+---------+-----------------------+------+----------+-------------+
2 rows in set, 2 warnings (0.00 sec)

# 3. 当驱动表和被驱动表都存在索引，查询优化器评估成本后可能会调换两者的角色
mysql> CREATE INDEX X ON `type`(card);
Query OK, 0 rows affected (0.06 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> EXPLAIN SELECT SQL_NO_CACHE * FROM `type` INNER JOIN book ON type.card = book.card;
+----+-------------+-------+------------+-------+---------------+------+---------+-----------------------+------+----------+-------------+
| id | select_type | table | partitions | type  | possible_keys | key  | key_len | ref                   | rows | filtered | Extra       |
+----+-------------+-------+------------+-------+---------------+------+---------+-----------------------+------+----------+-------------+
|  1 | SIMPLE      | book  | NULL       | index | Y             | Y    | 4       | NULL                  |   20 |   100.00 | Using index |
|  1 | SIMPLE      | type  | NULL       | ref   | X             | X    | 4       | atguigudb10.book.card |    1 |   100.00 | Using index |
+----+-------------+-------+------------+-------+---------------+------+---------+-----------------------+------+----------+-------------+
2 rows in set, 2 warnings (0.00 sec)

#向book表中添加数据（20条数据）
INSERT INTO `book`(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO `book`(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO `book`(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO `book`(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO `book`(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO `book`(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO `book`(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO `book`(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO `book`(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO `book`(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO `book`(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO `book`(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO `book`(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO `book`(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO `book`(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO `book`(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO `book`(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO `book`(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO `book`(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO `book`(card) VALUES(FLOOR(1 + (RAND() * 20)));

mysql> EXPLAIN SELECT SQL_NO_CACHE * FROM `type` INNER JOIN book ON type.card = book.card;
+----+-------------+-------+------------+-------+---------------+------+---------+-----------------------+------+----------+-------------+
| id | select_type | table | partitions | type  | possible_keys | key  | key_len | ref                   | rows | filtered | Extra       |
+----+-------------+-------+------------+-------+---------------+------+---------+-----------------------+------+----------+-------------+
|  1 | SIMPLE      | type  | NULL       | index | X             | X    | 4       | NULL                  |   20 |   100.00 | Using index |
|  1 | SIMPLE      | book  | NULL       | ref   | Y             | Y    | 4       | atguigudb10.type.card |    1 |   100.00 | Using index |
+----+-------------+-------+------------+-------+---------------+------+---------+-----------------------+------+----------+-------------+
2 rows in set, 2 warnings (0.00 sec)

# 4. 如果表的连接条件中只能有一个字段有索引，则有索引的字段所在的表会被作为被驱动表出现。
mysql> DROP INDEX X ON `type`;
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> EXPLAIN SELECT SQL_NO_CACHE * FROM `type` INNER JOIN book ON type.card = book.card;
+----+-------------+-------+------------+------+---------------+------+---------+-----------------------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref                   | rows | filtered | Extra       |
+----+-------------+-------+------------+------+---------------+------+---------+-----------------------+------+----------+-------------+
|  1 | SIMPLE      | type  | NULL       | ALL  | NULL          | NULL | NULL    | NULL                  |   20 |   100.00 | NULL        |
|  1 | SIMPLE      | book  | NULL       | ref  | Y             | Y    | 4       | atguigudb10.type.card |    1 |   100.00 | Using index |
+----+-------------+-------+------------+------+---------------+------+---------+-----------------------+------+----------+-------------+
2 rows in set, 2 warnings (0.00 sec)

mysql>

3.1.3 结论

  1. 保证被驱动表的JOIN字段已经创建了索引，需要JOIN 的字段，数据类型保持绝对一致。
  2. LEFT JOIN 时，选择小结果集作为驱动表， 大结果集作为被驱动表 。减少外层循环的次数。
  3. INNER JOIN 时，MySQL会自动将 小结果集的表选为驱动表。选择相信MySQL优化策略。
# 结果集的度量单位 = 表行数 * 每行大小，假设 t1 t2 都存在 200 行数据，每行数据由 5 个相同的字段组成

# 不推荐 t1 = 200 * 5 t2 = 100 * 5
select t1.*, t2.* from t1 straight_join t2 on (t1.b = t2.b) where t2.id <= 100;
# 推荐
select t1.*, t2.* from t2 straight_join t1 on (t1.b = t2.b) where t2.id <= 100;

# 不推荐 t1 = 200 * 1 t2 = 100 * 5
select t1.b, t2.* from t2 straight_join t1 on (t1.b = t2.b) where t2.id <= 100;
# 推荐
select t1.b, t2.* from t1 straight_join t2 on (t1.b = t2.b) where t2.id <= 100;
  1. 开启 block_nested_loop（默认）：即批量将驱动表中的数据加载到内存中，减少被驱动表加载到内存中的次数。

在这里插入图片描述

mysql> SHOW VARIABLES LIKE '%optimizer_switch%'\G
*************************** 1. row ***************************
Variable_name: optimizer_switch
        Value: ... ,block_nested_loop=on, ...
1 row in set (0.01 sec)

mysql>
  1. 关联查询性能不满足使用时，可以适当加大 join_buffer_size。
mysql> SHOW VARIABLES LIKE '%join_buffer%';
+------------------+--------+
| Variable_name    | Value  |
+------------------+--------+
| join_buffer_size | 262144 |
+------------------+--------+
1 row in set (0.00 sec)

mysql>

3.2 子查询

  • 子查询可以一次性完成很多逻辑上需要多个步骤才能完成的SQL操作。

  • 子查询的执行效率不高。

  1. 执行子查询时，MySQL需要为内层查询语句的查询结果建立一个临时表，然后外层查询语句从临时表中查询记录。查询完毕后，再撤销这些临时表。这样会消耗过多的CPU和IO资源，产生大量的慢查询。
  2. 子查询的结果集存储的临时表，不论是内存临时表还是磁盘临时表都不会存在索引，所以查询性能会受到一定的影响。
  3. 对于返回结果集比较大的子查询，其对查询性能的影响也就越大。
  • 连接（JOIN）查询来替代子查询
  1. 连接查询不需要建立临时表，其速度比子查询要快，如果查询中使用索引的话，性能就会更好。
  2. 尽量不要使用 NOT IN 或者 NOT EXISTS，用 LEFT JOIN xxx ON xx WHERE xx IS NULL 替代。

3.3 排序

  • 场景演示：何时走索引，何时使用 filesort ？
# 1. 删除student和class表中的非主键索引
CALL proc_drop_index('atguigudb10','student');
CALL proc_drop_index('atguigudb10','class');

# 2. 无索引
mysql> EXPLAIN SELECT SQL_NO_CACHE * FROM student ORDER BY age,classid;
+----+-------------+---------+------------+------+---------------+------+---------+------+--------+----------+----------------+
| id | select_type | table   | partitions | type | possible_keys | key  | key_len | ref  | rows   | filtered | Extra          |
+----+-------------+---------+------------+------+---------------+------+---------+------+--------+----------+----------------+
|  1 | SIMPLE      | student | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 499086 |   100.00 | Using filesort |
+----+-------------+---------+------------+------+---------------+------+---------+------+--------+----------+----------------+
1 row in set, 2 warnings (0.00 sec)

mysql> EXPLAIN SELECT SQL_NO_CACHE * FROM student ORDER BY age,classid LIMIT 10;
+----+-------------+---------+------------+------+---------------+------+---------+------+--------+----------+----------------+
| id | select_type | table   | partitions | type | possible_keys | key  | key_len | ref  | rows   | filtered | Extra          |
+----+-------------+---------+------------+------+---------------+------+---------+------+--------+----------+----------------+
|  1 | SIMPLE      | student | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 499086 |   100.00 | Using filesort |
+----+-------------+---------+------------+------+---------------+------+---------+------+--------+----------+----------------+
1 row in set, 2 warnings (0.00 sec)

# 2. 创建索引
mysql> CREATE  INDEX idx_age_classid_name ON student (age,classid,NAME);
Query OK, 0 rows affected (3.55 sec)
Records: 0  Duplicates: 0  Warnings: 0

## 2.1 即便有索引，仍需回表操作，不如直接全部加载到内存中然后使用 filesort
mysql> EXPLAIN  SELECT SQL_NO_CACHE * FROM student ORDER BY age,classid; 
+----+-------------+---------+------------+------+---------------+------+---------+------+--------+----------+----------------+
| id | select_type | table   | partitions | type | possible_keys | key  | key_len | ref  | rows   | filtered | Extra          |
+----+-------------+---------+------------+------+---------------+------+---------+------+--------+----------+----------------+
|  1 | SIMPLE      | student | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 499086 |   100.00 | Using filesort |
+----+-------------+---------+------------+------+---------------+------+---------+------+--------+----------+----------------+
1 row in set, 2 warnings (0.00 sec)

## 2.2 查询列全部包含在索引中，使用索引
mysql> EXPLAIN  SELECT SQL_NO_CACHE age,classid,name,id FROM student ORDER BY age,classid;
+----+-------------+---------+------------+-------+---------------+----------------------+---------+------+--------+----------+-------------+
| id | select_type | table   | partitions | type  | possible_keys | key                  | key_len | ref  | rows   | filtered | Extra       |
+----+-------------+---------+------------+-------+---------------+----------------------+---------+------+--------+----------+-------------+
|  1 | SIMPLE      | student | NULL       | index | NULL          | idx_age_classid_name | 93      | NULL | 499086 |   100.00 | Using index |
+----+-------------+---------+------------+-------+---------------+----------------------+---------+------+--------+----------+-------------+
1 row in set, 2 warnings (0.00 sec)

## 2.3 只查询部分行，使用索引
mysql> EXPLAIN  SELECT SQL_NO_CACHE * FROM student ORDER BY age,classid LIMIT 10;
+----+-------------+---------+------------+-------+---------------+----------------------+---------+------+------+----------+-------+
| id | select_type | table   | partitions | type  | possible_keys | key                  | key_len | ref  | rows | filtered | Extra |
+----+-------------+---------+------------+-------+---------------+----------------------+---------+------+------+----------+-------+
|  1 | SIMPLE      | student | NULL       | index | NULL          | idx_age_classid_name | 93      | NULL |   10 |   100.00 | NULL  |
+----+-------------+---------+------------+-------+---------------+----------------------+---------+------+------+----------+-------+
1 row in set, 2 warnings (0.00 sec)

# 3. order by 时顺序错误，索引失效（Y 使用索引 N 不走索引）
CREATE  INDEX idx_age_classid_stuno ON student (age,classid,stuno);

mysql> SHOW CREATE TABLE student\G
*************************** 1. row ***************************
       Table: student
Create Table: CREATE TABLE `student` (
  `id` int NOT NULL AUTO_INCREMENT,
  `stuno` int NOT NULL,
  `name` varchar(20) DEFAULT NULL,
  `age` int DEFAULT NULL,
  `classId` int DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_age_classid_name` (`age`,`classId`,`name`),
  KEY `idx_age_classid_stuno` (`age`,`classId`,`stuno`)
) ENGINE=InnoDB AUTO_INCREMENT=500001 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1 row in set (0.00 sec)

mysql> 

EXPLAIN  SELECT * FROM student ORDER BY classid LIMIT 10; # N
EXPLAIN  SELECT * FROM student ORDER BY classid,NAME LIMIT 10; # N  
EXPLAIN  SELECT * FROM student ORDER BY age,classid,stuno LIMIT 10; # Y 
EXPLAIN  SELECT * FROM student ORDER BY age,classid LIMIT 10; # Y
EXPLAIN  SELECT * FROM student ORDER BY age LIMIT 10; # Y

# 4. order by 时规则不一致, 索引失效 （顺序错，不索引；方向反，不索引）
EXPLAIN  SELECT * FROM student ORDER BY age DESC, classid ASC LIMIT 10; # N
EXPLAIN  SELECT * FROM student ORDER BY classid DESC, NAME DESC LIMIT 10; # N
EXPLAIN  SELECT * FROM student ORDER BY age ASC,classid DESC LIMIT 10; # N
EXPLAIN  SELECT * FROM student ORDER BY age DESC, classid DESC LIMIT 10; # Y

# 5. 无过滤，不索引
EXPLAIN  SELECT * FROM student WHERE age=45 ORDER BY classid; # Y
EXPLAIN  SELECT * FROM student WHERE  age=45 ORDER BY classid,NAME; # Y 
EXPLAIN  SELECT * FROM student WHERE  classid=45 ORDER BY age; # N
EXPLAIN  SELECT * FROM student WHERE  classid=45 ORDER BY age LIMIT 10; # Y
  • 调优实战
  1. 初始
mysql> EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE age = 30 AND stuno <101000 ORDER BY NAME;
+----+-------------+---------+------------+------+---------------+------+---------+------+--------+----------+-----------------------------+
| id | select_type | table   | partitions | type | possible_keys | key  | key_len | ref  | rows   | filtered | Extra                       |
+----+-------------+---------+------------+------+---------------+------+---------+------+--------+----------+-----------------------------+
|  1 | SIMPLE      | student | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 499086 |     3.33 | Using where; Using filesort |
+----+-------------+---------+------------+------+---------------+------+---------+------+--------+----------+-----------------------------+
1 row in set, 2 warnings (0.00 sec)

mysql> SELECT SQL_NO_CACHE * FROM student WHERE age = 30 AND stuno <101000 ORDER BY NAME;
...
22 rows in set, 1 warning (0.16 sec)

mysql>
  1. 方案一：避免 filesort
mysql> CREATE INDEX idx_age_name ON student(age,NAME);
Query OK, 0 rows affected (3.21 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE age = 30 AND stuno <101000 ORDER BY NAME ;
+----+-------------+---------+------------+------+---------------+--------------+---------+-------+-------+----------+-------------+
| id | select_type | table   | partitions | type | possible_keys | key          | key_len | ref   | rows  | filtered | Extra       |
+----+-------------+---------+------------+------+---------------+--------------+---------+-------+-------+----------+-------------+
|  1 | SIMPLE      | student | NULL       | ref  | idx_age_name  | idx_age_name | 5       | const | 18254 |    33.33 | Using where |
+----+-------------+---------+------------+------+---------------+--------------+---------+-------+-------+----------+-------------+
1 row in set, 2 warnings (0.00 sec)

mysql> SELECT SQL_NO_CACHE * FROM student WHERE age = 30 AND stuno <101000 ORDER BY NAME ;
...
22 rows in set, 1 warning (0.03 sec)

mysql>
  1. 方案二：尽量让 where 的过滤条件和排序使用上索引
mysql> CREATE INDEX idx_age_stuno_name ON student(age,stuno,NAME);
Query OK, 0 rows affected (3.35 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE age = 30 AND stuno <101000 ORDER BY NAME\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: student
   partitions: NULL
         type: range
possible_keys: idx_age_name,idx_age_stuno_name
          key: idx_age_stuno_name
      key_len: 9
          ref: NULL
         rows: 22
     filtered: 100.00
        Extra: Using index condition; Using filesort
1 row in set, 2 warnings (0.00 sec)

mysql> SELECT SQL_NO_CACHE * FROM student WHERE age = 30 AND stuno <101000 ORDER BY NAME ;
...
22 rows in set, 1 warning (0.00 sec)

mysql>
  1. 小结：当【范围条件】和【group by 或者 order by】的字段出现二选一时，优先观察条件字段的过滤数量，如果过滤的数据足够多，而需要排序的数据并不多时，优先把索引放在范围字段上。反之，亦然。
  • 为什么在 ORDER BY 字段上还要加索引
  1. SQL 中，可以在 WHERE 子句和 ORDER BY 子句中使用索引，目的是在 WHERE 子句中避免全表扫描，在 ORDER BY 子句避免使用 FileSort 排序。
  2. 尽量使用 Index 完成 ORDER BY 排序。如果 WHERE 和 ORDER BY 后面是相同的列就使用单索引列；如果不同就使用联合索引。
  3. 无法使用 Index 时，需要对 FileSort 方式进行调优。
  • 相关参数调优
  1. 无法避开需要使用 filesort，可以适当增加 sort_buffer_size
mysql> SHOW VARIABLES LIKE '%sort_buffer_size%';
+-------------------------+---------+
| Variable_name           | Value   |
+-------------------------+---------+
| innodb_sort_buffer_size | 1048576 | # 1M
| myisam_sort_buffer_size | 8388608 |
| sort_buffer_size        | 262144  |
+-------------------------+---------+
3 rows in set (0.07 sec)

mysql>
  1. 无法避开需要使用 filesort，可以适当增加 max_length_for_sort_data
# 当查询每行返回的列的长度超过该值，则走双路算法进行排序，否则走单路算法进行排序
# 双路算法首先加载排序列，计算完毕后再据此顺序加载其它列，速度比较慢
# 单路算法直接加载所有列速度比较快，但列的返回长度过大时可能造成 sort_buffer 频繁不足从而效果适得其反
mysql> SELECT @@max_length_for_sort_data;
+----------------------------+
| @@max_length_for_sort_data |
+----------------------------+
|                       4096 |
+----------------------------+
1 row in set, 1 warning (0.00 sec)

mysql>

3.4 GROUP BY 优化

  1. group by 使用索引的原则几乎跟 order by 一致 ，group by 即使没有过滤条件用到索引，也可以直接使用索引。
  2. group by 先排序再分组，遵照索引建的最佳左前缀法则。
  3. 当无法使用索引列，增大 max_length_for_sort_data 和 sort_buffer_size 参数的设置。
  4. where效率高于having，能写在where限定的条件就不要写在having中了。
  5. 减少使用order by，和业务沟通能不排序就不排序，或将排序放到程序端去做。Order by、group by、distinct 这些语句较为耗费CPU，数据库的CPU资源是极其宝贵的。
  6. 包含了order by、group by、distinct这些查询的语句，where条件过滤出来的结果集请保持在1000行以内，否则SQL会很慢。

3.5 分页查询

  • 简介
  1. 在MySQL中做分页查询，MySQL 并不是跳过 offset 行，而是取 offset+N 行，然后返回放弃前 offset 行，返回 N 行。
  2. 那当 offset 特别大的时候，效率就非常的低下。例如“limit 1000,20”，此时MySQL排序出前1020条数据后仅仅需要第1001到1020条记录，前1000条数据都会被抛弃，查询和排序的代价非常高。
  3. MySQL 的分页处理并不是十分完美，需要我们在分页SQL上做一些优化，对超过特定阈值的页数进行 SQL 改写。
  • 优化思路一：在索引上完成排序分页操作，最后根据主键关联回原表查询所需要的其他列内容。
mysql> DESCRIBE SELECT * FROM `student` ORDER BY classid LIMIT 300000, 10;
+----+-------------+---------+------------+------+---------------+------+---------+------+--------+----------+----------------+
| id | select_type | table   | partitions | type | possible_keys | key  | key_len | ref  | rows   | filtered | Extra          |
+----+-------------+---------+------------+------+---------------+------+---------+------+--------+----------+----------------+
|  1 | SIMPLE      | student | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 499086 |   100.00 | Using filesort |
+----+-------------+---------+------------+------+---------------+------+---------+------+--------+----------+----------------+
1 row in set, 1 warning (0.00 sec)

mysql> SELECT * FROM `student` ORDER BY classid LIMIT 300000, 10;
+--------+--------+--------+------+---------+
| id     | stuno  | name   | age  | classId |
+--------+--------+--------+------+---------+
| 129184 | 229184 | qPffjF |   25 |     601 |
| 129660 | 229660 | VCbymR |   21 |     601 |
| 129728 | 229728 | JTrckU |   43 |     601 |
| 129790 | 229790 | sqBLYM |   39 |     601 |
| 131804 | 231804 | mLSgdZ |   36 |     601 |
| 111605 | 211605 | JXWRri |   41 |     601 |
| 113004 | 213004 | SNkKXJ |   15 |     601 |
| 113101 | 213101 | HSuvTg |   50 |     601 |
| 113848 | 213848 | Vxzggm |   44 |     601 |
| 113980 | 213980 | nFjgdZ |   36 |     601 |
+--------+--------+--------+------+---------+
10 rows in set (0.40 sec)

mysql> 

mysql> EXPLAIN SELECT s.* FROM `student` s, (SELECT id FROM `student` ORDER BY classid LIMIT 300000, 10) a WHERE s.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 | 300010 |   100.00 | NULL        |
|  1 | PRIMARY     | s          | NULL       | eq_ref | PRIMARY       | PRIMARY | 4       | a.id |      1 |   100.00 | NULL        |
|  2 | DERIVED     | student    | NULL       | index  | NULL          | classId | 5       | NULL | 300010 |   100.00 | Using index |
+----+-------------+------------+------------+--------+---------------+---------+---------+------+--------+----------+-------------+
3 rows in set, 1 warning (0.00 sec)

mysql> SELECT s.* FROM `student` s, (SELECT id FROM `student` ORDER BY classid LIMIT 300000, 10) a WHERE s.id = a.id;
+--------+--------+--------+------+---------+
| id     | stuno  | name   | age  | classId |
+--------+--------+--------+------+---------+
| 137919 | 237919 | VhbKvX |   25 |     601 |
| 138672 | 238672 | iaFalb |   28 |     601 |
| 139281 | 239281 | kaxkJN |   39 |     601 |
| 140048 | 240048 | WcwZKA |   22 |     601 |
| 144736 | 244736 | IkaxmT |   33 |     601 |
| 144766 | 244766 | KkURAa |   27 |     601 |
| 148125 | 248125 | PRNnbs |   37 |     601 |
| 148250 | 248250 | EnAQAf |   49 |     601 |
| 148393 | 248393 | kvyffm |   44 |     601 |
| 150246 | 250246 | dFOhqk |    3 |     601 |
+--------+--------+--------+------+---------+
10 rows in set (0.08 sec)

mysql>
  • 优化思路二：该方案适用于主键自增的表，可以把Limit 查询转换成某个位置的查询 。
mysql> EXPLAIN SELECT * FROM student WHERE id > 300000 ORDER BY classid LIMIT 10;
+----+-------------+---------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
| id | select_type | table   | partitions | type  | possible_keys | key     | key_len | ref  | rows | filtered | Extra       |
+----+-------------+---------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | student | NULL       | index | PRIMARY       | classId | 5       | NULL |   20 |    50.00 | Using where |
+----+-------------+---------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

mysql> SELECT * FROM student WHERE id > 300000 ORDER BY classid LIMIT 10;
+--------+--------+--------+------+---------+
| id     | stuno  | name   | age  | classId |
+--------+--------+--------+------+---------+
| 300126 | 400126 | yPFJqI |   20 |       1 |
| 300651 | 400651 | gEDgVi |    5 |       1 |
| 301521 | 401521 | sPWpJA |   26 |       1 |
| 302165 | 402165 | JdzlJy |   25 |       1 |
| 302194 | 402194 | VgVieV |   18 |       1 |
| 303312 | 403312 | ZxnVQt |   22 |       1 |
| 306517 | 406517 | kDoHtZ |   40 |       1 |
| 306734 | 406734 | OiAaBJ |   30 |       1 |
| 307851 | 407851 | QxQMne |   33 |       1 |
| 308542 | 408542 | SikBcJ |   49 |       1 |
+--------+--------+--------+------+---------+
10 rows in set (0.01 sec)

mysql>

四、索引优化

4.1 优先考虑覆盖索引

  • 什么是覆盖索引？
  1. 理解方式一：索引是高效找到行的一个方法，但是一般数据库也能使用索引找到一个列的数据，因此它不必读取整个行。毕竟索引叶子节点存储了它们索引的数据；当能通过读取索引就可以得到想要的数据，那就不需要读取行了。一个索引包含了满足查询结果的数据就叫做覆盖索引。
  2. 理解方式二：非聚簇复合索引的一种形式，它包括在查询里的SELECT、JOIN和WHERE子句用到的所有列（即建索引的字段正好是覆盖查询条件中所涉及的字段）。
  3. 简单说就是，索引列+主键包含SELECT 到 FROM之间查询的列。
  • 利弊
  1. 避免Innodb表进行索引的二次查询（回表）；可以把随机IO变成顺序IO加快查询效率。
  2. 索引字段的维护总是有代价的。因此，在建立冗余索引来支持覆盖索引时就需要权衡考虑了。

4.2 索引下推

Index Condition Pushdown(ICP) 是一种在存储引擎层使用索引过滤数据的一种优化方式。ICP可以减少存储引擎访问基表的次数以及MySQL服务器访问存储引擎的次数。

  • 不使用 ICP 索引扫描的过程
  1. storage层：只将满足index key条件的索引记录对应的整行记录取出，返回给server层
  2. server 层：对返回的数据，使用后面的where条件过滤，直至返回最后一行。

在这里插入图片描述

在这里插入图片描述

  • 使用ICP扫描的过程
  1. storage层：首先将index key条件满足的索引记录区间确定，然后在索引上使用index filter进行过滤。将满足的index filter条件的索引记录才去回表取出整行记录返回server层。不满足index filter条件的索引记录丢弃，不回表、也不会返回server层。
  2. server 层：对返回的数据，使用table filter条件做最后的过滤。

在这里插入图片描述

在这里插入图片描述

  • 优化实战
  1. 准备数据
# 创建表
CREATE TABLE `people` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `zipcode` VARCHAR(20) COLLATE utf8_bin DEFAULT NULL,
  `firstname` VARCHAR(20) COLLATE utf8_bin DEFAULT NULL,
  `lastname` VARCHAR(20) COLLATE utf8_bin DEFAULT NULL,
  `address` VARCHAR(50) COLLATE utf8_bin DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `zip_last_first` (`zipcode`,`lastname`,`firstname`)
) ENGINE=INNODB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8mb3 COLLATE=utf8_bin;

# 创建存储过程，向people表中添加1000000条数据，测试ICP开启和关闭状态下的性能
DELIMITER //
CREATE PROCEDURE  insert_people( max_num INT )
BEGIN  
DECLARE i INT DEFAULT 0;   
 SET autocommit = 0;    
 REPEAT  
 SET i = i + 1;  
 INSERT INTO people ( zipcode,firstname,lastname,address ) VALUES ('000001', '六', '赵', '天津市');  
 UNTIL i = max_num  
 END REPEAT;  
 COMMIT; 
END //
DELIMITER ;

# 插入模拟数据
CALL insert_people(1000000);

INSERT INTO `people` VALUES 
('1', '000001', '三', '张', '北京市'), 
('2', '000002', '四', '李', '南京市'), 
('3', '000003', '五', '王', '上海市'), 
('4', '000001', '六', '赵', '天津市');
  1. 使用 ICP
mysql> EXPLAIN SELECT * FROM people WHERE zipcode='000001' AND lastname LIKE '%张%' AND address LIKE '%北京市%';
+----+-------------+--------+------------+------+----------------+----------------+---------+-------+--------+----------+------------------------------------+
| id | select_type | table  | partitions | type | possible_keys  | key            | key_len | ref   | rows   | filtered | Extra                              |
+----+-------------+--------+------------+------+----------------+----------------+---------+-------+--------+----------+------------------------------------+
|  1 | SIMPLE      | people | NULL       | ref  | zip_last_first | zip_last_first | 63      | const | 498578 |     1.23 | Using index condition; Using where |
+----+-------------+--------+------------+------+----------------+----------------+---------+-------+--------+----------+------------------------------------+
1 row in set, 1 warning (0.01 sec)

mysql> SELECT * FROM people WHERE zipcode='000001' AND lastname LIKE '%张%' AND address LIKE '%北京市%';
+----+---------+-----------+----------+-----------+
| id | zipcode | firstname | lastname | address   |
+----+---------+-----------+----------+-----------+
|  1 | 000001  | 三        | 张       | 北京市    |
+----+---------+-----------+----------+-----------+
1 row in set (0.30 sec)

mysql>
  1. 关闭 ICP
mysql> SET optimizer_switch = 'index_condition_pushdown=off';
Query OK, 0 rows affected (0.00 sec)

mysql> EXPLAIN SELECT * FROM people WHERE zipcode='000001' AND lastname LIKE '%张%' AND address LIKE '%北京市%';
+----+-------------+--------+------------+------+----------------+----------------+---------+-------+--------+----------+-------------+
| id | select_type | table  | partitions | type | possible_keys  | key            | key_len | ref   | rows   | filtered | Extra       |
+----+-------------+--------+------------+------+----------------+----------------+---------+-------+--------+----------+-------------+
|  1 | SIMPLE      | people | NULL       | ref  | zip_last_first | zip_last_first | 63      | const | 498578 |     1.23 | Using where |
+----+-------------+--------+------------+------+----------------+----------------+---------+-------+--------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

mysql> SELECT * FROM people WHERE zipcode='000001' AND lastname LIKE '%张%' AND address LIKE '%北京市%';
+----+---------+-----------+----------+-----------+
| id | zipcode | firstname | lastname | address   |
+----+---------+-----------+----------+-----------+
|  1 | 000001  | 三        | 张       | 北京市    |
+----+---------+-----------+----------+-----------+
1 row in set (2.98 sec)

mysql>

4.3 普通索引 vs 唯一索引

  • 查询过程
  1. 对于普通索引来说，查找到满足条件的第一个记录(5,500)后，需要查找下一个记录，直到碰到第一个不满足 k=5 条件的记录。
  2. 对于唯一索引来说，由于索引定义了唯一性，查找到第一个满足条件的记录后，就会停止继续检索。
  3. 那么，这个不同带来的性能差距会有多少呢？答案是，微乎其微。
  • change buffer
  1. 当需要更新一个数据页时，如果数据页在内存中就直接更新，而如果这个数据页还没有在内存中的话，在不影响数据一致性的前提下，InooDB会将这些更新操作缓存在change buffer中，这样就不需要从磁盘中读入这个数据页了。
  2. 在下次查询需要访问这个数据页的时候，将数据页读入内存，然后执行 change buffer 中与这个页有关的操作。通过这种方式就能保证这个数据逻辑的正确性。
  3. 将change buffer中的操作应用到原数据页，得到最新结果的过程称为 merge。除了 访问这个数据页 会触发merge外，系统有 后台线程会定期 merge。在 数据库正常关闭（shutdown） 的过程中，也会执行merge操作。
  4. 如果能够将更新操作先记录在change buffer，减少读磁盘，语句的执行速度会得到明显的提升。而且，数据读入内存是需要占用 buffer pool 的，所以这种方式还能够避免占用内存，提高内存利用率。
  5. 唯一索引的更新就不能使用 change buffer，实际上也只有普通索引可以使用。
  • 抉择场景
  1. 普通索引和唯一索引应该怎么选择？其实，这两类索引在查询能力上是没差别的，主要考虑的是对 更新性能 的影响。所以，建议你 尽量选择普通索引 。
  2. 在实际使用中会发现， 普通索引 和 change buffer 的配合使用，对于 数据量大 的表的更新优化还是很明显的。
  3. 如果所有的更新后面，都 马上伴随着对这个记录的查询 ，那么你应该 关闭change buffer 。而在其他情况下，change buffer都能提升更新性能。
  4. 由于唯一索引用不上change buffer的优化机制，因此如果 业务可以接受 ，从性能角度出发建议优先考虑非唯一索引。
  5. 如果业务不能保证，或者业务就是要求数据库来做约束，那么没得选，必须创建唯一索引。如果碰上了大量插入数据慢、内存命中率低的时候，请排查是否是唯一索引造成的问题。
  6. 在一些 归档库 的场景，比如，线上数据只需要保留半年，然后历史数据保存在归档库。这时候，归档数据已经是确保没有唯一键冲突了。要提高归档效率，可以考虑把表里面的唯一索引改成普通索引。

五、主键设计

5.1 自增 ID

  1. 可靠性不高 存在自增ID回溯的问题，这个问题直到最新版本的MySQL 8.0才修复。
  2. 安全性不高 对外暴露的接口可以非常容易猜测对应的信息。比如：/User/1/这样的接口，可以非常容易猜测用户ID的值为多少，总用户数量有多少，也可以非常容易地通过接口进行数据的爬取。
  3. 性能差 自增ID的性能较差，需要在数据库服务器端生成。
  4. 交互多 业务还需要额外执行一次类似 last_insert_id() 的函数才能知道刚才插入的自增值，这需要多一次的网络交互。在海量并发的系统中，多1条SQL，就多一次性能上的开销。
  5. 局部唯一性 最重要的一点，自增ID是局部唯一，只在当前数据库实例中唯一，而不是全局唯一，在任意服务器间都是唯一的。对于目前分布式系统来说，这简直就是噩梦。

5.2 业务字段做主键

  • 常见业务字段
  1. 会员卡号（cardno）看起来比较合适，因为会员卡号不能为空，而且有唯一性，可以用来标识一条会员记录。
  2. 会员卡号可能存在重复使用 的情况。比如，会员退会后商家重复使用卡号，会造成底层如消费记录错误。
  3. 手机号也存在被运营商收回，身份证号属于个人隐私。

建议尽量不要用跟业务有关的字段做主键。无法预测在项目的整个生命周期中，哪个业务字段会因为项目的业务需求而有重复，或者重用之类的情况出现。

5.3 自增 UUID

mysql> SELECT UUID(), uuid_to_bin(UUID()), uuid_to_bin(UUID(), true);
+--------------------------------------+------------------------------------------+------------------------------------------------------+
| UUID()                               | uuid_to_bin(UUID())                      | uuid_to_bin(UUID(), true)                            |
+--------------------------------------+------------------------------------------+------------------------------------------------------+
| 891741e8-aa54-11ec-99cd-0242ac120002 | 0x891741F4AA5411EC99CD0242AC120002       | 0x11ECAA54891741FD99CD0242AC120002                   |
+--------------------------------------+------------------------------------------+------------------------------------------------------+
1 row in set (0.00 sec)

mysql> SELECT UUID(), uuid_to_bin(UUID()), uuid_to_bin(UUID(), true);
+--------------------------------------+------------------------------------------+------------------------------------------------------+
| UUID()                               | uuid_to_bin(UUID())                      | uuid_to_bin(UUID(), true)                            |
+--------------------------------------+------------------------------------------+------------------------------------------------------+
| 89db265c-aa54-11ec-99cd-0242ac120002 | 0x89DB2667AA5411EC99CD0242AC120002       | 0x11ECAA5489DB267599CD0242AC120002                   |
+--------------------------------------+------------------------------------------+------------------------------------------------------+
1 row in set (0.00 sec)

mysql>
  1. UUID 全局唯一，占用36字节，数据无序，插入性能差。
  2. MySQL8.0 提供的 uuid_to_bin 函数除去了UUID字符串中无意义的 - 字符串，并且将字符串用二进制类型保存，这样存储空间降低为了16字节。同样的，MySQL也提供了 bin_to_uuid 函数。
  3. 通过函数 uuid_to_bin(@uuid, true) 将 UUID 的时间变量后推从而转化为 全局唯一 + 单调递增 序列。

5.4 总结

  • 非核心业务可以使用主键自增ID，核心业务的主键设计至少应该是全局唯一且是单调递增。
L小Ray想有腮
关注
  • 1
    点赞
  • 踩
  • 29
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
专栏目录
MySQL高级知识-查询与索引优化分析
02-20
下面是替代方法leftjoin+union(可去除重复数据)+rightjoin实现如下面代码7、A的独有+B的独有MySQL官方对索引的定义为：索引（Index）是帮助MySQL高效获取数据的数据结构。你可以简单理解为“排好序的快速查找结构”...
MySQL：索引优化、查询优化
xiaozhang960825的博客
03-23 3598
索引选择及优化
【MySQL 第10章_索引优化与查询优化】
cl24的博客
09-28 320
MySQL
MySQL进阶之（八）索引优化与查询优化
最新发布
qq_50994235的博客
05-16 703
提前为下文做数据准备，两张表：学员表(student) 插入 50w 条数据，班级表(class) 插入 1w 条数据。
【MySQL高级篇】 第10章_索引优化与查询优化
博客包含书籍、B站、其他博主博客等内容，只为了记录笔记，作业，问题，软件配置等，为了以后方便查阅，如有侵权，请联系删除
04-03 367
理解方式一：索引是高效找到行的一个方法，但是一般数据库也能使用索引找到一个列的数据，因此它不必读取整个行。毕竟索引叶子节点存储了它们索引的数据；当能通过读取索引就可以得到想要的数据，那就不需要读取行了。一个索引包含了满足查询结果的数据就叫做覆盖索引。理解方式二：非聚簇复合索引的一种形式，它包括在查询里的SELECT、JOIN和WHERE子句用到的所有列 （即建索引的字段正好是覆盖查询条件中所涉及的字段）。简单说就是，索引列+主键包含SELECT 到 FROM之间查询的列。
MySQL高级篇_第10章_索引优化与查询优化
UntifA的博客
03-16 733
MySQL高级篇_第10章_索引优化与查询优化
MYSQL如何优化UUID生成的索引效率
java专栏
04-08 288
有序UUID（也称为时间戳UUID或版本1 UUID）是基于时间和机器MAC地址生成的，它们在一定程度上是有序的。这种有序性意味着新生成的UUID在数值上通常大于旧的UUID，这有助于减少数据库页分裂，提高索引效率。优化UUID生成的索引效率需要从多个方面入手，包括UUID的生成方式、存储格式、索引策略以及数据库配置等。UUID的随机性导致数据在物理存储上的分布较为分散，这可能会降低索引的查找效率。如果UUID的一部分（如时间戳部分）对于查询是有意义的，你可以考虑只对该部分创建索引，即使用前缀索引。
MySQL高级优化之-查询逻辑及where条件提取.pdf
11-07
本篇文章将深入探讨MySQL查询逻辑处理步骤以及如何有效地提取WHERE条件，以实现更高效的查询优化。 首先，MySQL的查询逻辑处理分为11个步骤，这些步骤按照顺序依次执行，从FROM操作开始，至LIMIT操作结束。每一步...
Java高级编程——MySQL索引实现及优化原理解析
02-25
在MySQL中，索引属于存储引擎级别的概念，不同存储引擎对索引的实现方式是不同的，本文主要讨论MyISAM和InnoDB两个存储引擎的索引实现方式。MyISAM索引实现MyISAM引擎使用B+Tree作为索引结构，叶节点的data域存放的...
MySQL高级篇-索引（Index）的数据结构
10-30
八、索引优化 1. 减少索引数量：虽然更多索引可能提高某些查询的性能，但也会增加写操作的开销。 2. 选择合适的索引列：尽量选择区分度高的列作为索引列。 3. 使用复合索引：当多个列一起查询时，考虑创建复合索引，...
MySQL-索引优化和查询优化
kk_lina的博客
08-01 330
mysq查询优化、索引优化大全；mysq索引失效案例详解、汇总，mysql覆盖索引、索引下推原理
MySQL高级优化文档
03-06
关于mysql在linux环境下的配置和安装，以及系统优化等
MySQL Index Condition Pushdown(ICP)性能优化方法实例
09-10
主要介绍了MySQL Index Condition Pushdown(ICP)性能优化方法实例,本文讲解了概念介绍、原理、实践案例、案例分析、ICP的使用限制等内容,需要的朋友可以参考下
Using index condition Using index
net@core的博客
09-27 1800
Using index condition Tables are read by accessing index tuples and testing them first to determine whether to read full table rows. In this way, index information is used to defer (“push down”) readi...
MYSQL优化方案
Bejpse的博客
03-21 352
MYSQL优化方案 Sql语句优化 1、插入优化 2、order by 优化 3、group by优化 4、limit 优化 5、count优化 扩展：explain中Extra字段说明 索引优化 大表拆分优化 分库 分表 集群架构之主从复制 binlog二进制日志 主从复制原理 集群成熟方案 MySQL优化方向： 在设计上：存储引擎、字段类型、范式 在功能上：索引、缓存、分库分表 在架构上：集群、主从复制、读写分离、负载均衡 Sql语句优化 1、插入优化 ● 大量数据采用批量插
MySQL索引优化
m0_61218014的博客
08-02 2545
子查询的执行效率不高。原因：① 执行子查询时，MySQL需要为内层查询语句的查询结果建立一个临时表，然后外层查询语句从临时表中查询记录。查询完毕后，再撤销这些临时表。这样会消耗过多的CPU和IO资源，产生大量的慢查询。② 子查询的结果集存储的临时表，不论是内存临时表还是磁盘临时表都不会存在索引，所以查询性能会受到一定的影响。③ 对于返回结果集比较大的子查询，其对查询性能的影响也就越大。在MySQL中，可以使用连接（JOIN）查询来替代子查询。连接查询不需要建立临时表 ，其速度比子查询。...
深入MySQL（二）索引
weixin_42962634的博客
09-22 338
索引 索引（MySQL中也叫做 “键”）是存储引擎用于快速找到记录的一种数据结构。这是索引的基本功能。 索引对于良好的性能非常关键。尤其是当表中的数据量越来越大时，索引对性能的影响愈发重要。在数据量较小且负载较低时，不恰当的索引对性能的影响可能还不明显，但当数据量逐渐增大时，性能则会急剧下降（对于固态硬盘来说，没有传统硬盘那么糟糕）。 索引基础 我们在看书的时候，想要找到某个主题，会怎么做呢？我们一般会去翻书的目录，然后找到该主题所在的页数，最后快速的找到该主题。那么MySQL的索引所做的工作也是同样的工作
MySQL索引优化与查询优化
shenBaoYun的博客
07-11 844
MySQL索引优化与查询优化
MySql优化——索引优化与查询优化（索引失效）
Alex_cf的博客
12-09 920
索引失效情况，案例
mysql高级篇索引优化
09-06
MySQL高级篇索引优化主要涉及MySQL Query Optimizer（查询优化器）和索引的使用。MySQL Query Optimizer是MySQL中负责优化SELECT语句的模块，通过计算分析系统中收集到的统计信息，为客户端请求的Query提供最优的...

“相关推荐”对你有帮助么？

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

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

余额充值