mysql 参数 调优 mysql8调优

文章目录

• 概述
• 1.数据准备

• 步骤1：创建数据库和建表
• 步骤2：设置参数
• 步骤3：创建函数
• 步骤4：创建存储过程
• 步骤5：调用存储过程
• 步骤6：删除某表上的索引

• 2.索引失效案例

• 2.1 全值匹配我最爱
• 2.2 最佳左前缀法则
• 2.3 主键插入顺序
• 2.4 计算、函数、类型转换(自动或手动)导致索引失效
• 2.5 类型转换导致索引失效
• 2.6 范围条件右边的列索引失效（包含范围查询）
• 2.7 不等于(!= 或者<>)索引失效
• 2.8 is null可以使用索引，is not null无法使用索引
• 2.9 like以通配符%开头索引失效
• 2.10 OR 前后存在非索引的列，索引失效
• 2.11 数据库和表的字符集统一使用utf8mb4
• 2.12 小练习

• 3.关联查询优化

• 3.1 数据准备
• 3.2 采用左外连接
• 3.3 采用内连接
• 3.4 join语句原理

• 3.4.1驱动表和被驱动表
• 3.4.2 simple Nest-Loop Join(简单嵌套循环连接)
• 3.4.3 index Nest-Loop Join(索引嵌套循环连接)
• 3.4.4 Block Nest-Loop Join(块嵌套循环连接)
• 3.4.5 小结
• 3.4.5 hash join

• 4. 子查询优化
• 5. 排序优化

• 5.1 排序优化
• 5.2 测试
• 5.3 案例实战
• 5.4 filesort算法：双路排序和单路排序

• 6. GROUP BY优化
• 7. 优化分页查询
• 8. 优先考虑覆盖索引

• 8.1 什么是覆盖索引？
• 8.2 覆盖索引的利弊

• 9. 如何给字符串添加索引

• 9.2 前缀索引对覆盖索引的影响

• 10. 索引下推

• 10.1 索引下推概述
• 10.2 ICP的开启、关闭
• 10.3 ICP的使用条件
• 10.4 使用前后对比

• 11. 普通索引 vs 唯一索引
• 12. 其它查询优化策略

• 12.1 EXISTS 和 IN 的区分
• 12.2 COUNT(*)与COUNT(具体字段)效率
• 12.3 关于SELECT(*)
• 12.4 LIMIT 1 对优化的影响
• 12.5 多使用COMMIT

• 13. 淘宝数据库，主键如何设计的？

• 13.1 自增ID的问题
• 13.2 业务字段做主键
• 13.3 淘宝的主键设计
• 13.4 推荐的主键设计

概述

1.数据准备

步骤1：创建数据库和建表

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

CREATE DATABASE atguigudb2;

CREATE TABLE `class` (
`id` INT(11) 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 DEFAULT CHARSET=utf8;

CREATE TABLE `student` (
`id` INT(11) NOT NULL AUTO_INCREMENT,
`stuno` INT NOT NULL ,
`name` VARCHAR(20) DEFAULT NULL,
`age` INT(3) DEFAULT NULL,
`classId` INT(11) DEFAULT NULL,
PRIMARY KEY (`id`)
#CONSTRAINT `fk_class_id` FOREIGN KEY (`classId`) REFERENCES `t_class` (`id`)
) ENGINE=INNODB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

步骤2：设置参数

• 命令开启：允许创建函数设置：

set global log_bin_trust_function_creators=1; # 不加global只是当前窗口有效。
show variables like '%log_bin_trust_function_creators%';

步骤3：创建函数

保证每条数据都不同。

#随机产生字符串
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 ;
#假如要删除
#drop function rand_string;

-- 随机产生班级编号
#用于随机产生多少到多少的编号
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 ;
#假如要删除
#drop function rand_num;

步骤4：创建存储过程

#创建往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 ;
#假如要删除
#drop PROCEDURE insert_stu;

创建往class表中插入数据的存储过程

#执行存储过程，往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 ;
#假如要删除
#drop PROCEDURE insert_class;

步骤5：调用存储过程

• class

#执行存储过程，往class表添加1万条数据
CALL insert_class(10000);

• stu

#执行存储过程，往stu表添加50万条数据
CALL insert_stu(100000,500000);

步骤6：删除某表上的索引

• 在这里插入代码片创建存储过程

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.索引失效案例

2.1 全值匹配我最爱

系统中出现的sql语句

EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE age = 30;
EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE age = 30 AND classId = 4;
EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE age = 30 AND classId = 4 AND `name` = 'abcd';

建立索引前执行（关注时间）

-- (286ms) 不使用索引
SELECT SQL_NO_CACHE * FROM student WHERE age = 30 AND classId = 4 AND `name` = 'abcd'; 

-- (48ms) 使用age索引
SELECT SQL_NO_CACHE * FROM student WHERE age = 30 AND classId = 4 AND `name` = 'abcd'; 

-- (1ms) 使用age 和classId 索引
SELECT SQL_NO_CACHE * FROM student WHERE age = 30 AND classId = 4 AND `name` = 'abcd';

-- (1ms) 使用age 和classId 和name 索引
SELECT SQL_NO_CACHE * FROM student WHERE age = 30 AND classId = 4 AND `name` = 'abcd';

添加必要索引

CREATE INDEX idx_age ON student(age);
CREATE INDEX idx_age_classid ON student(age, classId);
CREATE INDEX idx_age_classid_name ON student(age, classId,`name`);

2.2 最佳左前缀法则

拓展：Alibaba《Java开发手册》
索引文件具有 B-Tree 的最左前缀匹配特性，如果左边的值未确定，那么无法使用此索引。

EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE age = 30 AND NAME = 'abcd';
EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE classId = 4 AND NAME = 'abcd';
EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE classId = 4 AND age = 30 AND NAME = 'abcd';

-- 只有idx_age_classid_name 情况下执行以下语句 key_len == 5 , 只使用了 name 索引， classid没用到，name页没用到
EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE age = 30 AND NAME = 'abcd';

2.3 主键插入顺序

可这个数据页已经满了，再插进来咋办呢？我们需要把当前 页面分裂 成两个页面，把本页中的一些记录移动到新创建的这个页中。页面分裂和记录移位意味着什么？意味着： 性能损耗 ！所以如果我们想尽量避免这样无谓的性能损耗，最好让插入的记录的 主键值依次递增 ，这样就不会发生这样的性能损耗了。所以我们建议：让主键具有 AUTO_INCREMENT ，让存储引擎自己为表生成主键，而不是我们手动插入 ，

比如： person_info 表：

CREATE TABLE person_info(
id INT UNSIGNED NOT NULL AUTO_INCREMENT,
name VARCHAR(100) NOT NULL,
birthday DATE NOT NULL,
phone_number CHAR(11) NOT NULL,
country varchar(100) NOT NULL,
PRIMARY KEY (id),
KEY idx_name_birthday_phone_number (name(10), birthday, phone_number)
);

我们自定义的主键列 id 拥有 AUTO_INCREMENT 属性，在插入记录时存储引擎会自动为我们填入自增的主键值。这样的主键占用空间小，顺序写入，减少页分裂。

2.4 计算、函数、类型转换(自动或手动)导致索引失效

create index idx_name on student;
--此语句比下一条好，（能够使用上索引）
EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE student.name LIKE 'abc%';

-- 不能使用索引
EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE LEFT(student.name,3) = 'abc';

第二种：索引优化失效

• type为“ALL”，表示没有使用到索引，查询时间为 3.62 秒，查询效率较之前低很多。
  
  

2.5 类型转换导致索引失效

下列哪个sql语句可以用到索引。（假设name字段上设置有索引）

# 未使用到索引
EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE name=123;
# 使用到索引
EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE name='123';

2.6 范围条件右边的列索引失效（包含范围查询）

• 注意：右边指的是建立索引的顺序的右边

ALTER TABLE student DROP INDEX idx_name;
ALTER TABLE student DROP INDEX idx_age;
ALTER TABLE student DROP INDEX idx_age_classid;
EXPLAIN SELECT SQL_NO_CACHE * FROM student
WHERE student.age=30 AND student.classId>20 AND student.name = 'abc' ;

2.7 不等于(!= 或者<>)索引失效

2.8 is null可以使用索引，is not null无法使用索引

EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE age IS NULL;
EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE age IS NOT NULL;

2.9 like以通配符%开头索引失效

拓展：Alibaba《Java开发手册》
【强制】页面搜索严禁左模糊或者全模糊，如果需要请走搜索引擎来解决。

2.10 OR 前后存在非索引的列，索引失效

or 前后的字段都建立索引

# 未使用到索引
EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE age = 10 OR classid = 100;
#使用到索引
EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE age = 10 OR name = 'Abel';

2.11 数据库和表的字符集统一使用utf8mb4

统一使用utf8mb4( 5.5.3版本以上支持)兼容性更好，统一字符集可以避免由于字符集转换产生的乱码。不
同的 字符集 进行比较前需要进行 转换 会造成索引失效。

2.12 小练习

3.关联查询优化

3.1 数据准备

3.2 采用左外连接

= 左边驱动表， = 右边是被驱动表

-- 无索引情况
EXPLAIN SELECT SQL_NO_CACHE * FROM `type` LEFT JOIN book ON type.card = book.card;

-- 给book添加索引 log(n)
create index Y on book(card)
EXPLAIN SELECT SQL_NO_CACHE * FROM `type` LEFT JOIN book ON type.card = book.card;

-- 给type 加上索引
create index X on `type`(card)
EXPLAIN SELECT SQL_NO_CACHE * FROM `type` LEFT JOIN book ON type.card = book.card;

3.3 采用内连接

drop index X on type;
drop index Y on book;（如果已经删除了可以不用再执行该操作）
EXPLAIN SELECT SQL_NO_CACHE * FROM type INNER JOIN book ON type.card=book.card;

ALTER TABLE book ADD INDEX Y ( card);
EXPLAIN SELECT SQL_NO_CACHE * FROM type INNER JOIN book ON type.card=book.card;

ALTER TABLE type ADD INDEX X (card);
EXPLAIN SELECT SQL_NO_CACHE * FROM type INNER JOIN book ON type.card=book.card;

DROP INDEX X ON `type`;
EXPLAIN SELECT SQL_NO_CACHE * FROM TYPE INNER JOIN book ON type.card=book.card

ALTER TABLE `type` ADD INDEX X (card);
EXPLAIN SELECT SQL_NO_CACHE * FROM `type` INNER JOIN book ON type.card=book.card;

换成 inner join（MySQL自动选择驱动表）

结论： 对于内连接来说，查询优化器决定驱动表和被驱动表 ， 两个表都有索引， 小表驱动大表，

3.4 join语句原理

3.4.1驱动表和被驱动表

3.4.2 simple Nest-Loop Join(简单嵌套循环连接)

3.4.3 index Nest-Loop Join(索引嵌套循环连接)

3.4.4 Block Nest-Loop Join(块嵌套循环连接)

3.4.5 小结

总结3：什么叫作“小表”？

在决定哪个表做驱动表的时候，应该是两个表按照各自的条件过滤，过滤完成之后，计算参与join的各个字段的总数据量，数据量小的那个表，就是“小表”，应该作为驱动表。

3.4.5 hash join

4. 子查询优化

5. 排序优化

5.1 排序优化

问题：在 WHERE 条件字段上加索引，但是为什么在 ORDER BY 字段上还要加索引呢？
优化建议：

1. SQL 中，可以在 WHERE 子句和 ORDER BY 子句中使用索引，目的是在 WHERE 子句中 避免全表扫描 ，在 ORDER BY 子句 避免使用 FileSort 排序 。当然，某些情况下全表扫描，或者 FileSort 排序不一定比索引慢。但总的来说，我们还是要避免，以提高查询效率。
2. 尽量使用 Index 完成 ORDER BY 排序。如果 WHERE 和 ORDER BY 后面是相同的列就使用单索引列；如果不同就使用联合索引。
3. 无法使用 Index 时，需要对 FileSort 方式进行调优

INDEX a_b_c(a,b,c)
order by 能使用索引最左前缀
- ORDER BY a
- ORDER BY a,b
- ORDER BY a,b,c
- ORDER BY a DESC,b DESC,c DESC
如果WHERE使用索引的最左前缀定义为常量，则order by 能使用索引
- WHERE a = const ORDER BY b,c
- WHERE a = const AND b = const ORDER BY c
- WHERE a = const ORDER BY b,c
- WHERE a = const AND b > const ORDER BY b,c
不能使用索引进行排序
- ORDER BY a ASC,b DESC,c DESC /* 排序不一致 */
- WHERE g = const ORDER BY b,c /*丢失a索引*/
- WHERE a = const ORDER BY c /*丢失b索引*/
- WHERE a = const ORDER BY a,d /*d不是索引的一部分*/
- WHERE a in (...) ORDER BY b,c /*对于排序来说，多个相等条件也是范围查询*/
-

5.2 测试

ORDER BY子句，尽量使用Index方式排序，避免使用FileSort方式排序。
执行案例前先清除student上的索引，只留主键：

CALL proc_drop_index('atguigudb2','student');
CALL proc_drop_index('atguigudb2','calss');

SHOW INDEX FROM student;
SHOW INDEX FROM class;

5.3 案例实战

场景:查询年龄为30岁的，且学生编号小于101000的学生，按用户名称排序

EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE age = 30 AND stuno <101000 ORDER BY NAME ;

结论：

1. 两个索引同时存在，mysql自动选择最优的方案。（对于这个例子，mysql选择 idx_age_stuno_name）。但是， 随着数据量的变化，选择的索引也会随之变化的 。
2. 当【范围条件】和【group by 或者 order by】的字段出现二选一时，优先观察条件字段的过 滤数量，如果过滤的数据足够多，而需要排序的数据并不多时，优先把索引放在范围字段 上。反之，亦然。

答案： 可行

5.4 filesort算法：双路排序和单路排序

6. GROUP BY优化

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

7. 优化分页查询

8. 优先考虑覆盖索引

8.1 什么是覆盖索引？

select 的字段已经存在与索引中， 不需要回表操作情况下会使用索引，称为覆盖索引

• 理解方式一：索引是高效找到行的一个方法，但是一般数据库也能使用索引找到一个列的数据，因此它不必读取整个行。毕竟索引叶子节点存储了它们索引的数据；当能通过读取索引就可以得到想要的数据，那就不需要读取行了。一个索引包含了满足查询结果的数据就叫做覆盖索引。
• 理解方式二：非聚簇复合索引的一种形式，它包括在查询里的SELECT、JOIN和WHERE子句用到的所有列（即建索引的字段正好是覆盖查询条件中所涉及的字段）。
• 简单说就是， 索引列+主键 包含 SELECT 到 FROM之间查询的列 。

8.2 覆盖索引的利弊

9. 如何给字符串添加索引

9.2 前缀索引对覆盖索引的影响

结论：
使用前缀索引就用不上覆盖索引对查询性能的优化了，这也是你在选择是否使用前缀索引时需要考 虑的一个因素。

10. 索引下推

icp （索引下推）减少回表次数，提升查询效率

10.1 索引下推概述

10.2 ICP的开启、关闭

10.3 ICP的使用条件

10.4 使用前后对比

11. 普通索引 vs 唯一索引

12. 其它查询优化策略

12.1 EXISTS 和 IN 的区分

12.2 COUNT(*)与COUNT(具体字段)效率

12.3 关于SELECT(*)

在表查询中，建议明确字段，不要使用 * 作为查询的字段列表，推荐使用SELECT <字段列表> 查询。原
因：
① MySQL 在解析的过程中，会通过 查询数据字典 将"*"按序转换成所有列名，这会大大的耗费资源和时
间。
② 无法使用 覆盖索引

12.4 LIMIT 1 对优化的影响

• 针对的是会扫描全表的 SQL 语句，如果你可以确定结果集只有一条，那么加上 LIMIT 1 的时候，当找到一条结果的时候就不会继续扫描了，这样会加快查询速度。
• 如果数据表已经对字段建立了唯一索引，那么可以通过索引进行查询，不会全表扫描的话，就不需要加上 LIMIT 1 了。

12.5 多使用COMMIT

13. 淘宝数据库，主键如何设计的？

13.1 自增ID的问题

13.2 业务字段做主键

13.3 淘宝的主键设计

13.4 推荐的主键设计