• SQL 优化笔记（MySQL 版）

    MySQL 优化


    参考：

    基础

    给 mysql 的超级管理员 root 增加密码：/usr/bin/mysqladmin -u root password root

    MySQL 核心目录：

    • 安装目录：/var/lib/mysql
    • 配置文件目录：/usr/share/mysql
    • 命令目录：/usr/bin
      • mysqladmin 是一个执行管理操作的客户端程序，可以用来检查服务器的配置、状态、修改密码、创建删除数据库等
      • mysqldump 是用于迁移和备份数据库的工具
      • …

    MySQL 分层、存储引擎

    • 连接层：提供与客户端连接的服务
    • 服务层：提供各种用户使用的接口，提供 SQL 优化器 (MySQL Query Optimizer)
    • 引擎层：提供各种存储数据的方式 (InnoDB、MyISAM)
    • 存储层：存储数据

    InnoDB 对比 MyISAM：

    • InnoDB：事务优先（适合并发操作，行锁）
    • MyISAM：性能优先（表锁）
    # 查询 MySQL 中支持的引擎
show engines; # 常规表格显示
show engines \G # 清晰显示

# 查询当前使用的引擎
show variables like '%storage_engine%';

+---------------------------------+-----------+
| Variable_name                   | Value     |
+---------------------------------+-----------+
| default_storage_engine          | InnoDB    |
| default_tmp_storage_engine      | InnoDB    |
| disabled_storage_engines        |           |
| internal_tmp_mem_storage_engine | TempTable |
+---------------------------------+-----------+

# 查询当前使用的字符集
show variables like '%character%';

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15
    • 16
    • 17
    • 18

    示例：指定 MyISAM 引擎建表

    CREATE TABLE tb (
	id INT(4) AUTO_INCREMENT,
	name VARCHAR(5),
	dept VARCHAR(5),
	PRIMARY KEY(id)
) ENGINE=MyISAM AUTO_INCREMENT=1
DEFAULT CHARSET=utf8;

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7

    SQL 解析过程、索引、B 树

    SQL 优化的原因：

    • 性能低
    • 执行时间长
    • 等待时间长
    • SQL 语句欠佳（连接查询）
    • 索引失效
    • 服务器参数设置不佳（缓冲、线程数）

    SQL 编写过程和解析过程的差异：

    -- 解析顺序
SELECT DISTINCT
    <select_list>
FROM
    <left_table> <join_type>
JOIN <right_table> ON <join_condition>
WHERE
    <where_condition>
GROUP BY
    <group_by_list>
HAVING
    <having_condition>
ORDER BY
    <order_by_condition>
LIMIT <limit_number>

-- 执行顺序
FROM <left_table>
ON <join_condition>
<join_type> JOIN <right_table>
WHERE <where_condition>
GROUP BY <group_by_list>
HAVING <having_condition>
SELECT
DISTINCT <select_list>
ORDER BY <order_by_condition>
LIMIT <limit_number>

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15
    • 16
    • 17
    • 18
    • 19
    • 20
    • 21
    • 22
    • 23
    • 24
    • 25
    • 26
    • 27

    索引

    索引是帮助 MySQL 高效获取数据的数据结构，一般采用树结构（B+ 树、Hash ）

    索引的优势与弊端

    索引的弊端：

    • 索引本身需要占用空间（一般是硬盘）
    • 索引不适用于以下场景：少量数据、频繁更新的字段、很少使用的字段
    • 索引提高查询效率，但是降低了增删改查效率

    索引的优势：

    • 降低 IO 使用率
    • 降低 CPU 使用率（对于查询时的排序操作，B 树本身就是排好序的，可以直接使用）

    B 树与索引

    B 树与索引：

    • 三层 B 树可以存放百万级别的数据
    • B 树一般指 B+ 树，数据都保存在叶节点（查找数据的次数为 n 次，即 B+ 树的高度）

    索引的分类：

    • 单值索引：单列，一个表可以有多个单值索引
    • 主键索引：不能重复，不能为 null
    • 唯一索引：不能重复，可以为 null
    • 复合索引：多个列构成的索引，相当于二级目录

    注意： 如果一个字段是 primary key，则该字段默认就是 主键索引

    索引相关操作

    创建索引方式一：create 索引类型 索引名 on 表(字段)

    -- 单值索引
CREATE INDEX dept_index ON tb(dept);

-- 唯一索引
CREATE UNIQUE INDEX name_index ON tb(name);

-- 复合索引
CREATE INDEX dept_name_index ON tb(dept, name);

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8

    创建索引方式二：ALTER TABLE 表名 ADD 索引类型 索引名(字段)

    -- 单值索引
ALTER TABLE tb ADD INDEX dept_index(dept)

-- 唯一索引
ALTER TABLE tb ADD UNIQUE INDEX name_index(name)

-- 复合索引
ALTER TABLE tb ADD INDEX dept_name_index(dept, name)

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8

    DDL 语句不需要 commit;，会自动提交

    删除索引：DROP INDEX 索引名 on 表

    DROP INDEX name_index ON tb;

    • 1

    查询索引：SHOW INDEX FROM 表名

    SHOW INDEX FROM tb;

    • 1

    执行计划 - EXPLAIN

    SQL 性能问题：

    • 分析 SQL 执行计划：explain 可以模拟 SQL 优化器执行 SQL 语句
    • MySQL 的查询优化会干扰我们自己的优化
    EXPLAIN SELECT * FROM tb;

    • 1

    在这里插入图片描述

    • id 编号
    • select_type 查询类型
    • table 表名
    • type 类型
    • possible_keys 预测用到的索引
    • key 实际用到的索引
    • key_len 实际使用索引的长度
    • ref 表之间的引用
    • rows 通过索引查询到的数据量
    • Extra 额外信息

    数据准备

    create table course (
	cid int(3),
	cname varchar(20),
	tid int(3)
);

create table teacher (
	tid int(3),
	tname varchar(20),
	tcid int(3)
);

create table teacherCard (
	tcid int(3),
	tcdesc varchar(200)
);

insert into course values(1,'java', 1);
insert into course values(2,'html', 1);
insert into course values(3,'sql', 2);
insert into course values(4,'web', 3);

insert into teacher values(1, 'tz', 1);
insert into teacher values(2, 'tw', 2);
insert into teacher values(3, 'tl', 3);
insert into teacher values(4, 'ta', 4);
insert into teacher values(5, 'tb', 5);
insert into teacher values(6, 'tc', 6);

insert into teacherCard values(1, 'tzdesc');
insert into teacherCard values(2, 'twdesc');
insert into teacherCard values(3, 'tldesc');

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15
    • 16
    • 17
    • 18
    • 19
    • 20
    • 21
    • 22
    • 23
    • 24
    • 25
    • 26
    • 27
    • 28
    • 29
    • 30
    • 31
    • 32

    在这里插入图片描述

    id - 编号

    题目：查询课程编号为 2 或教师证编号为 3 的老师信息

    EXPLAIN SELECT teacher.* 
FROM course, teacher, teacherCard 
WHERE course.tid = teacher.tid 
	AND teacher.tcid = teacherCard.tcid   
	AND (course.cid = 2 or teacherCard.tcid = 3);

    • 1
    • 2
    • 3
    • 4
    • 5

    结论：

    • id 值相同，从上往下，顺序执行；数据量小的表优先查询

    题目：查询教授 SQL 课程的老师描述信息

    -- 多表连接形式 - id 值相同
EXPLAIN SELECT teacherCard.tcdesc FROM teacherCard, course, teacher
WHERE course.tid = teacher.tid
	AND teacher.tcid = teacherCard.tcid 
	AND course.cname='sql';

-- 子查询形式 - id 值不同
EXPLAIN SELECT tcdesc FROM teacherCard WHERE teacherCard.tcid = 
(SELECT teacher.tcid FROM teacher WHERE teacher.tid = 
(SELECT course.tid FROM course));

-- 多表 + 子查询 - id 值相同 + 不同
EXPLAIN SELECT teacher.tname,teacherCard.tcdesc FROM teacher, teacherCard 
WHERE teacher.tcid = teacherCard.tcid 
	AND teacher.tid = 
	(SELECT course.tid FROM course WHERE cname = 'sql');

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15
    • 16

    结论：

    • id 值相同，从上往下，顺序执行；数据量小的表优先查询
    • id 值不同，id 值大的优先查询。（本质：在嵌套子查询时，先查内层，再查外层）
    • id 值相同 + 不相同，id 值大的优先执行，id 值相同的的从上往下顺序执行

    select_type - 查询类型

    • PRIMARY 包含子查询 SQL 中的注查询（最外层）
    • SUBQUERY 包含子查询 SQL 中的子查询（非最外层）
    • SIMPLE 简单查询，不包含子查询和 union
    • DERIVED 衍生查询，使用到了临时表
      • FROM 子查询中只有一张表
      explain select cr.cname from (select * from course where tid in (1, 2)) cr;

      • 1
      • from 子查询中，如果有 table1 union table2，table1 就是 derived
      explain select cr.cname from 
(select * from course where tid=1 union select * from course where tid =2) cr;

      • 1
      • 2
    • union result - 告知关联关系的表是哪两张

    type - 索引类型

    索引类型排序：system > const > eq_ref > ref > range > index > all

    • system 和 const 是理想情况，一般无法达到
    • 没有索引必然是 all

    system：只有一条数据的系统表，或者衍生表只有一条数据的主查询

    create table test01
(
	tid int(3),
	tname varchar(20)
);

alter table test01 add constraint tid_pk primary key(tid);

insert into test01 values(1, 'a');

explain select * from (select * from test01) t where tid = 1;

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11

    const：只能查到一条数据的 SQL（只能用于 primary key 或 unique 索引，一般索引不会出现 const）

    -- 此时是 const 索引
explain select tid from test01 where tid = 1;

/* 删除 primary 索引 */
alter table test01 drop primary key;
/* 修改索引为一般索引 */
create index test01_index on test01(tid);

-- 此时查不是 const 索引
explain select tid from test01 where tid = 1;

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10

    eq_ref：对于每个索引键的查询，返回匹配有且只有一行数据（常见于唯一索引和主键索引）

    alter table teacherCard add constraint pk_tcid primary key(tcid);
alter table teacher add constraint uk_tcid unique index(tcid);
delete from teacher where tcid>3;
explain select t.tcid from teacher t, teacherCard tc where t.tcid = tc.tcid;

    • 1
    • 2
    • 3
    • 4

    上述语句用到的索引是 teacher 表的 tcid 字段
    如果 teacher 表的数据个数和连接查询的数据个数一致，才有可能满足 eq_ref 级别

    ref：非唯一索引，对于每个索引键的查询，返回匹配的所有行

    insert into teacher values(4, 'tz', 4);
insert into teacherCard values(4, 'tzc');

alter table teacher add index index_name(tname);

explain select * from teacher where tname='tz';

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6

    range：检索指定范围的行，where 后面是一个范围查询

    • between、in、<、>、>=、<=
    • in 查询，有时会失效，从 range 级别转为 all 无索引级别
    alter table teacher add index tid_index(tid);

explain select t.* from teacher t where t.tid < 3;

    • 1
    • 2
    • 3

    index：查询全部索引数据

    all：查询全部数据

    总结：

    • system / const：结果只有一条
    • eq_ref：结果有多条，每条数据唯一
    • ref：结果有多条，每调数据可能是多条

    possible_keys、key

    • possible_keys 是对可能用到的索引的预测
    • key 是实际用到的索引

    NULL 表示无索引

    key_len - 索引的长度

    key_len 代表索引的长度，用于判断复合索引是否被完全使用

    • utf8 编码下，对于 char(20) 有 key_len = 60（1 个字符占 3 个字节）
    • 如果索引字段可以为 null，MySQL 底层会用 1 个字节用于标识
    • 索引字段为 Varchar，MySQL 用 2 个字节代表可变长度
    • utf8mb4 中，一个字符占 4 个字节
    • utf8 中，1 个字符占 3 个字节
    • gbk 中，1 个字符 2 个字节
    • latin 中，一个字符 1 个字节
    create table test_kl
(
	name char(20) not null default ''
);

alter table test_kl add index index_name(name);

explain select * from test_kl where name='';

alter table test_kl add column name1 char(20);
alter table test_kl add index index_name1(name1);

explain select * from test_kl where name1='';

drop index index_name on test_kl;
drop index index_name1 on test_kl;

alter table test_kl add index name_name1_index (name, name1);

explain select * from test_kl where name1='';

alter table test_kl add column name2 varchar(20);
alter table test_kl add index name2_index(name2);

/* key_len = 83 = 80 + 1(null) + 2(varchar) */
explain select * from test_kl where name2='';

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15
    • 16
    • 17
    • 18
    • 19
    • 20
    • 21
    • 22
    • 23
    • 24
    • 25
    • 26

    ref - 当前表参照的字段

    与 type 中的 ref 进行区分

    ref 指明当前表所参照的字段

    • select ... where a.c=b.x，其中 b.x 可以是常量
    alter table course add index tid_index(tid);

explain select * from course c, teacher t where c.tid = t.tid and t.tname='tw';

    • 1
    • 2
    • 3

    rows - 通过索引查询到的数据量

    rows：扫描的行数

    explain select * from course c, teacher t where c.tid = t.tid and t.tname='tz';

    • 1

    Extra - 额外信息

    Using filesort

    • 性能消耗大，需要额外一次排序或查询
    • 如果排序和查找不是同一个字段，则会出现 Using filesort
    • 如果符合索引跨列，会出现 Using filesort
      • where 和 order by 按照符合索引的顺序使用，不要跨列或无序
    • 常见于 order by
    create table test02
(
	a1 char(3),
	a2 char(3),
	a3 char(3),
	index idx_a1(a1),
	index idx_a2(a2),
index idx_a3(a3)
);

/* 排序和查找不是同一个字段 Using filesort */
explain select * from test02 where a1 = '' order by a2;

drop index idx_a1 on test02;
drop index idx_a2 on test02;
drop index idx_a3 on test02;

alter table test02 add index idx_a1_a2_a3(a1, a2, a3);
/* 复合索引跨列 */
explain select * from test02 where a1='' order by a3;
explain select * from test02 where a2='' order by a3;
explain select * from test02 where a1='' order by a2;

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15
    • 16
    • 17
    • 18
    • 19
    • 20
    • 21
    • 22

    Using temporary

    • 用到了临时表
    • 常用于 groub by
    • 避免方法：查询哪列就使用哪列 group by
    explain select a1 from test02 where a1 in ('1', '2', '3') group by a2;

    • 1

    Using index

    • 使用到的列都在索引中，称为索引覆盖
    • 性能提升
    • 不读取原文件，只从索引文件中获取数据
    • 不需要回表查询
    • 索引覆盖对 possible_keys 和 key 的影响
      • 如果没有 where，则索引只出现在 key 中
      • 如果有 where，则索引出现在 key 和 posiible_keys 中
    explain select a1, a2 from test02 where a1='' or a2='';

drop index idx_a1_a2_a3 on test02;
alter table test02 add index id_a2_a2(a1, a2);
explain select a1, a3 from test02 where a1='' or a3='';

/* 对 possible_keys 和 key 的影响 */
explain select a1, a2 from test02 where a1='' or a2='';
explain select a1, a2 from test02;

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9

    Using where

    • 回表查询
    explain select a1, a3 from test02 where a3 = '';

    • 1

    Impossible WHERE

    • where 子句永远为 false
    explain select * from test02 where a1='x' and a1='y';

    • 1

    Using join buffer

    • MySQL 引擎使用了连接缓存

    SQL 优化

    优化示例

    create table test03
(
	a1 int(4) not null,
	a2 int(4) not null,
	a3 int(4) not null,
	a4 int(4) not null
);

alter table test03 add index idx_a1_a2_a3_4(a1, a2, a3, a4);

/* Using index */
/* 推荐按照复合索引的顺序查询 */
explain select a1, a2, a3, a4 from test03 where a1=1 and a2=2 and a3=3 and a4=4;

/* Using index */
/* 经过 SQL 优化器后，效果与上一个查询语句一致 */
explain select a1, a2, a3, a4 from test03 where a4=1 and a3=2 and a2=3 and a1=4;

/* Using where; Using index */
/* a4 跨列，索引失效，造成回表查询 */
/* where a1=1 and a2=2 ... order by a3 仍然遵循复合索引的顺序，因此有 Using index */
explain select a1, a2, a3, a4 from test03 where a1=1 and a2=2 and a4=4 order by a3;

/* Using where; Using index; Using filesort */
/* where a1=1 ... order by a3 跨列，多了一次查找/排序，出现 Using filesort */
explain select a1, a2, a3, a4 from test03 where a1=1 and a4=4 order by a3;

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15
    • 16
    • 17
    • 18
    • 19
    • 20
    • 21
    • 22
    • 23
    • 24
    • 25
    • 26

    总结：

    • 如果复合索引使用顺序完全一致，索引全部使用；部分一致，索引部分使用
    • where 和 order 的拼接不要跨列

    单表优化及总结

    数据准备：

    create table book
(
	bid int(4) primary key,
	name varchar(20) not null,
	authorid int(4) not null,
	publicid int(4) not null,
	typeid int(4) not null
);

insert into book values(1, 'java', 1, 1, 2);
insert into book values(2, 'html', 2, 1, 2);
insert into book values(3, 'sql', 3, 2, 1);
insert into book values(4, 'C', 4, 4, 3);

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13

    SQL 优化过程：

    -- 默认不进行优化，进行查询
/* type:All*/
/* Using where; Using filesort */
explain select bid from book where typeid in(2, 3) and authorid=1 order by typeid desc;

-- 优化：给每个字段设置索引，再进行查询
/* type:index */
/* Using where; Using index; Using filesort */
alter table book add index idx_bta(bid, typeid, authorid);

/* 为避免干扰，优化之前删除老的索引 */
drop index idx_bta on book;

/* 根据 sql 实际解析的顺序，调整索引顺序 */
/* type:index */
/* Using where; Using index */
alter table book add index idx_tab(typeid, authorid, bid);

/* 删除索引，创建新索引测试 */
drop index idx_tab on book;

/* 将出现范围查询的字段 typeid 放到后面 */
alter table book add index idx_atb(authorid, typeid, bid);

/* 将范围查询 typeid in (2, 3) 放到 authorid=1 后面 */
/* type:ref */
/* Using where; Using index */
/* key_len: 4 */
explain select bid from book where authorid=1 and typeid in(2, 3) order by typeid desc;

/* Using index */
/* key_len: 8 */
/* typeid in(2, 3) 改为 typeid=3，不使用范围查询，typeid 索引有效 */
/* 通过 key_len 也可以佐证，此处有 2 个索引，typeid 索引有效 */
explain select bid from book where authorid=1 and typeid=3 order by typeid desc;

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15
    • 16
    • 17
    • 18
    • 19
    • 20
    • 21
    • 22
    • 23
    • 24
    • 25
    • 26
    • 27
    • 28
    • 29
    • 30
    • 31
    • 32
    • 33
    • 34
    • 35

    总结：

    • 索引不能跨列使用，保持索引定义和使用顺序一致性
    • 索引需要逐步优化
    • 将含 in 的范围查询放到条件最后，防止整个索引失效

    同时出现了 Using index 和 Using where：
    Using index，由于 where authorid = 1 ...，authorid 在索引中，不需要回原表
    Using where，由于... and typeid in (2, 3)，typeid 在索引中，但是使用了 in 范围查询，索引失效，需要回原表

    多表优化及总结

    数据准备：

    create table teacher2
(
	tid int(4) primary key,
	cid int(4) not null
);

insert into teacher2 values(1, 2);
insert into teacher2 values(2, 1);
insert into teacher2 values(3, 3);

create table course2
(
	cid int(4),
	cname varchar(20)
);

insert into course2 values(1, 'java');
insert into course2 values(2, 'python');
insert into course2 values(3, 'kotlin');

/* 左连接，将数据量少的表放到左边 */
/* type:All */
/* Extra:  */
/* type:All */
/* Extra: Using where; Using join buffer  */
explain select * from teacher2 t left outer join course2 c 
on t.cid=c.cid where c.cname='java';

/* 增加索引 */
/* type: index */
/* Extra: Using index */
/* type: All */
/* Extra: Using where; Using join buffer*/
alter table teacher2 add index index_teacher2_cid(cid);

/* type: ref */
/* Extra: Using where */
/* type: ref */
/* Extra: Using index*/
alter table course2 add index index_course2_cname(cname);

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15
    • 16
    • 17
    • 18
    • 19
    • 20
    • 21
    • 22
    • 23
    • 24
    • 25
    • 26
    • 27
    • 28
    • 29
    • 30
    • 31
    • 32
    • 33
    • 34
    • 35
    • 36
    • 37
    • 38
    • 39
    • 40

    索引添加原则：

    • 小表驱动大表
    • 索引建立在经常使用的字段上
    • 左外连接，给左表加索引；右外连接，给右表加索引

    三表或更多表使用相同的原则

    避免索引失效的原则

    /* 2 个索引都有效 */
/* type:ref */
/* Extra: */
/* key_len: 8 */
explain select * from book where authorid=1 and typeid=2;

/* 只有 1 个索引有效 */
/* type:ref */
/* Extra: using where */
/* key_len: 4 */
explain select * from book where authorid=1 and typeid*2=2;

/* 2 个索引都失效 */
/* type:All */
/* Extra: using where */
/* key_len: NULL */
explain select * from book where authorid*2=1 and typeid*2=2;

/* 2 个索引都失效，复合索引左边失效，整个索引失效 */
/* type:All */
/* Extra: using where */
/* key_len: NULL */
explain select * from book where authorid*2=1 and typeid=2;

/* 删除复合索引 */
drop index idx_atb on book;

alter table book add index idx_authorid(authorid);
alter table book add index idx_typeid(typeid);

/* 1 个索引都失效，独立索引，第 1 个索引失效，不影响后面的索引 */
/* type:ref */
/* Extra: using where */
/* key_len: 4 */
explain select * from book where authorid*2 = 1 and typeid=2;

/* 索引有效 */
explain select * from book where authorid =1 and typeid =2;
/* 使用了不等于，索引失效 */
explain select * from book where authorid !=1 and typeid =2;

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15
    • 16
    • 17
    • 18
    • 19
    • 20
    • 21
    • 22
    • 23
    • 24
    • 25
    • 26
    • 27
    • 28
    • 29
    • 30
    • 31
    • 32
    • 33
    • 34
    • 35
    • 36
    • 37
    • 38
    • 39
    • 40

    避免索引失效的原则：

    • 复合索引，不要跨列或无序使用
    • 尽量使用全索引匹配
    • 不要在索引上进行任何操作（计算、函数、类型转换、如 ... where a.x * 3）
    • 复合索引，左边索引失效，所有索引失效
    • 复合索引使用不等于 (!=、<>) 或者 is null，自身索引会失效，右侧索引可能会失效
    • MySQL 本身有 sql 优化器，实际优化效果并非百分之百达到预期

    索引优化与预期不符合的情况

    drop index idx_typeid on book;
drop index idx_authorid on book;

alter table book add index idx_book_at(authorid, typeid);

/* 复合索引全部使用 */
/* key_len:8 */
/* type: ref */
explain select * from book where authorid =1 and typeid =2;

/* where 中最左侧的索引字段有 > 号，复合索引中自身及右侧全部失效 */
/* type:All */
/* Extra: Using where */
/* key_len: NULL */
explain select * from book where authorid >1 and typeid =2;

/* 最右侧索引使用了 > 号，复合索引没有失效 */
/* type: range */
/* Extra: Using where */
/* key_len: 8 */
explain select * from book where authorid =1 and typeid>2;

/* 复合索引只有 1 个生效 */
/* type: range */
/* key_len: 4 */
/* Extra: Using where */
explain select * from book where authorid <1 and typeid=2;

/* 相比上一条 SQL，只将 authorid<1 改为 authorid<4，右侧索引也失效 */
/* type: ALL */
/* key_len: NULL */
/* Extra: Using where */
explain select * from book where authorid <4 and typeid=2;

/* 使用百分号开头，索引失效 */
/* type: ALL */
/* key_len: NULL */
/* Extra: Using where */
explain select * from teacher where tname like '%x%';

/* 不使用百分号开头，索引仍然有效 */
/* type: range */
/* key_len: NULL */
/* Extra: Using where */
explain select * from teacher where tname like 'x%';

/* 使用百分号开头，但是实现索引覆盖，仍然起到了一定的优化作用 */
/* type: index */
/* key_len: 63 */
/* Extra: Using where; Using index */
explain select tname from teacher where tname like '%x%';

/* tname 和 'abc' 都是字符形式，索引有效 */
/* type: ref */
/* key_len: 63 */
/* Extra: Using where */
explain select * from teacher where tname = 'abc';

/* tname 是字符类型，123 是整数，查找时有类型转换操作，导致索引失效 */
/* type: ALL */
/* key_len: NULL */
/* Extra: Using where */
explain select * from teacher where tname = 123;

/* 使用 and，索引仍然有效 */
/* type: ref */
/* key_len: 63 */
/* Extra: Using where */
explain select * from teacher where tname = '' and tcid>1;

/* 使用了 or，导致 or 左侧的索引也失效 */
/* type: ALL */
/* key_len: NULL */
/* Extra: Using where */
explain select * from teacher where tname = '' or tcid>1;

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15
    • 16
    • 17
    • 18
    • 19
    • 20
    • 21
    • 22
    • 23
    • 24
    • 25
    • 26
    • 27
    • 28
    • 29
    • 30
    • 31
    • 32
    • 33
    • 34
    • 35
    • 36
    • 37
    • 38
    • 39
    • 40
    • 41
    • 42
    • 43
    • 44
    • 45
    • 46
    • 47
    • 48
    • 49
    • 50
    • 51
    • 52
    • 53
    • 54
    • 55
    • 56
    • 57
    • 58
    • 59
    • 60
    • 61
    • 62
    • 63
    • 64
    • 65
    • 66
    • 67
    • 68
    • 69
    • 70
    • 71
    • 72
    • 73
    • 74
    • 75
    • 一般情况，范围查询之后的索引失效
    • 使用索引覆盖，索引优化会完全符合预期
    • like 尽量以常量开头，不以 '%' 开头，否则索引失效
    • 使用类型转换（显式或隐式），会导致索引失效
    • 使用 or 会导致索引失效，甚至会影响左侧的索引

    常见的优化方法及慢 SQL 排查

    exists 和 in

    /* 有数据 */
select tname from teacher where exists(select * from teacher);

/* 无数据 */
select tname from teacher where exists(select * from teacher where tid=9999);

    • 1
    • 2
    • 3
    • 4
    • 5
    • 如果主查询数据集大，使用 in
    • 如果子查询数据集大，使用 exist

    exists 的作用：将主查询的结构放到子查询结果中进行条件校验
    - 如果子查询有数据，则校验成功
    - 如果符合校验，则保留数据

    order by 优化

    双路排序： MySQL 4.1 之前的默认策略，扫描 2 次磁盘

    • 第 1 次：从磁盘读取排序字段，对排序字段进行排序，在 buffer 中进行排序
    • 第 2 次：扫描其他字段

    单路排序：一次读取全部磁盘，在 buffer 中进行排序

    • 不一定是真正的单路，仍然有可能是多次 IO（数据量过大时，分片读取）

    单路排序比双路排序占用更多 buffer

    调整 buffer 大小：set max_length_for_sort_data=1024

    单路自动切换到双路的条件：需要排序的列总大小超过 set max_length_for_sort_data=1024 定义的字节数

    提高 order by 效率的策略：

    • 选择使用单路，双路
    • 调整 buffer 容量大小
    • 避免使用 select *
    • 复合索引避免跨列
    • 保证全部排序字段顺序的一致性

    慢查询日志

    慢查询日志：MySQL 用于记录响应时间超过阈值的 SQL 语句

    • long_query_time - 阈值（默认 10 秒）
    • 慢查询日志模式是关闭的，需要手动开启（建议调优时打开，部署上线时关闭）

    开启慢查询日志：

    • 检查是否开启了慢查询日志：show variables like '%slow_query_log%';
    • 临时开启：set global slow_query_log =1;，mysql 服务重启后失效
    • 永久开启：
    vi /etc/my.cnf

# 配置文件中添加以下内容
[mysqld]
slow_query_log=1 
slow_query_log_file=/var/lib/mysql/localhost-slow.log

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6

    慢查询阈值修改：

    • 查询慢查询阈值：show variables like '%long_query_time%';
    • 临时修改：set global long_query_time=5;，重新登录后生效
    • 永久修改：
    vi /etc/my.cnf
[mysqld] long_query_time=3

    • 1
    • 2

    相关基础语句：

    • 休眠模拟慢查询：select sleep(4);
    • 查询超过阈值的 SQL 数量：show global status like '%slow_queries%';
    • 在 Linux 命令行，通过日志查看慢查询 SQL 的详情：cat /var/lib/mysql/localhost-slow.log

    使用 mysqldumpslow 工具分析慢查询：

    mysqldumpslow 各种参数 慢查询日志文件路径
- s 排序方式
- r 逆序
- l 锁定时间
- g 正则匹配模式

    • 1
    • 2
    • 3
    • 4
    • 5
    /* 模拟慢查询 */
select sleep(5);
select sleep(4);
select sleep(3);

/* 获取返回记录最多的 3 个 SQL */
mysqldumpslow -s r -t 3 /var/lib/mysql/bigdata01-slow.log

/* 获取访问次数最多的 3 个 SQL */
mysqldumpslow -s c -t 3 /var/lib/mysql/bigdata01-slow.log

/* 按照时间排序，前 10 条包含 left join 查询语句的 SQL */
mysqldumpslow -s t -t 10 -g "left join" /var/lib/mysql/bigdata01-slow.log

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13

    模糊并通过 profiles 分析海量数据

    1、建表：

    create database testdata;
use testdata;

create table dept 
(
	dno int(5) primary key default 0,
	dname varchar(20) not null default '',
	loc varchar(30) default ''
) engine=innodb default charset=utf8;

create table emp
(
	eid int(5) primary key,
	ename varchar(20) not null default '',
	job varchar(20) not null default '',
	deptno int(5) not null default 0
)engine=innodb default charset=utf8;

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15
    • 16
    • 17

    2、创建存储函数：

    use testdata;
delimiter $
create function randstring(n int) returns varchar(255)
begin 

    declare all_str varchar(100) default 'abcdefghijklmnopqrestuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ';
    declare return_str varchar(255) default '';
    declare i int default 0;
    while i<n
    do
        set return_str=concat(return_str, substring(all_str, FLOOR(1+rand()*52), 1));
        set i=i+1;
    end while;
    return return_str;
end $

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15

    慢查询日志 与 存储过程/存储函数 冲突错误：

    /* 开启慢查询日志，再创建存储过程/存储函数，报如下错误   */
/* ERROR 1418 (HY000): 
This function has none of DETERMINISTIC, NO SQL, or READS SQL DATA 
in its declaration and binary logging is enabled 
(you *might* want to use the less safe log_bin_trust_function_creators variable) */

/* 临时解决 */
set global log_bin_trust_function_creators=1;

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    # 永久解决
vi /etc/my.cnf
[mysqld] log_bin_trust_function_creators=1

    • 1
    • 2
    • 3

    3、通过存储函数插入随机整数：

    use testdata;
create function ran_num() returns int(5)
begin 

declare i int default 0;
set i=floor(rand()*100);
return i;

end$

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9

    4、通过存储过程插入海量数据：

    emp 表：

    create procedure insert_emp(in eid_start int(10), in data_times int(10))
begin 
declare i int default 0;
set autocommit =0;

repeat 
insert into emp values(eid_start+i, randstring(5), 'other', ran_num());
set i=i+1;
until i=data_times
end repeat;

commit;

end $

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14

    dept 表：

    create procedure insert_dept(in dno_start int(10), in data_times int(10))
begin 
declare i int default 0;
set autocommit =0;

repeat 
insert into dept values(dno_start+i, randstring(6), randstring(8));
set i=i+1;
until i=data_times
end repeat;

commit;

end $

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14

    调用存储函数，插入数据：

    delimiter ;

call insert_emp(1000, 800000);
call insert_dept(10, 30);

/* 验证插入数据量 */
select count(1) from emp;

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7

    5、分析海量数据

    show variables like '%profiling%';

/* profiling 影响性能，在部署实施前，应关闭此项 */
set profiling=on;

/* 记录 profiling 打开之后的所有 SQL 语句消耗的时间 */
show profiles;

/* 精确查询更多详情，Query_Id 参考上个语句的查询结果 */
show profile all for query 2;
show profile cpu, block io for query 2;

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11

    全局查询日志

    show variables like '%general_log%';

/* 开启全局日志，记录开启之后的所有 SQL 语句 */
set global general_log=1;
/* 将日志记入表中 */
set global log_output='table';

/* 设置后执行一条查询 */
select count(1) from dept;

/* 显示日志信息 */
select * from mysql.general_log;

/* 将日志记入文件 */
set global log_output='file';

/* 通过默认保存地址查看日志文件 */
cat /var/lib/mysql/bigdata01.log;

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15
    • 16
    • 17
    • 18

    开启 general_log 后，所有 SQL 会被记录到系统自带的 mysql.general_log 表中

    锁机制详解

    锁机制：解决因资源共享造成的并发问题

    按 操作类型 进行分类：

    • 读锁（共享锁）：对同一条数据，多个读操作可以同时进行，互不干扰
    • 写锁（互斥锁）：如果当前写操作没有完毕，则无法进行其他读操作

    按 操作范围 进行分类：

    • 表锁：
      • 对整张表加锁
      • 开销小，加锁快
      • 无死锁
      • 容易发生锁冲突（同时操作一条数据的概率增高）
      • 并发度低
      • MyISAM 采用表锁
    • 行锁：
      • 对一条数据加锁
      • 开销大，加锁慢
      • 容易出现死锁
      • 锁的范围较小，不易发生锁冲突
      • 高并发概率低
      • InnoDB 采用行锁行锁
    • 页锁

    表锁

    /* MYSQL/SQLSERVER 支持自增，Oracle 需要借助于序列来实现自增 */
create table tablelock
(
	id int primary key auto_increment,
	name varchar(20)
) engine myisam;

insert into tablelock(name) values('a1');
insert into tablelock(name) values('a2');
insert into tablelock(name) values('a3');
insert into tablelock(name) values('a4');
insert into tablelock(name) values('a5');

/* 查看加锁情况 */
show open tables;

/* 加锁 */
lock table tablelock read;

/* 加锁后可以读 */
select * from tablelock;

/* 加锁后不能写 */
/* ERROR 1099 (HY000): Table 'tablelock' was locked with a READ lock and can't be updated */
delete from tablelock where id=1;

/* 加锁后，当前会话不能对其他表进行读操作 */
/* ERROR 1100 (HY000): Table 'dept' was not locked with LOCK TABLES */
select count(1) from dept;

/* 加锁后，当前会话不能对其他表进行写操作 */
/* ERROR 1100 (HY000): Table 'dept' was not locked with LOCK TABLES */
insert into dept values(39,'xxxxxx', 'yyyyyyyy');

/* 释放锁 */
unlock tables;

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15
    • 16
    • 17
    • 18
    • 19
    • 20
    • 21
    • 22
    • 23
    • 24
    • 25
    • 26
    • 27
    • 28
    • 29
    • 30
    • 31
    • 32
    • 33
    • 34
    • 35
    • 36

    会话：每一个访问数据库的 dos 命令行、数据库客户端工具，都是一个会话

    当前会话，对 A 表加了 read 锁：

    • 该会话对 A 表：可以读，不能写
    • 该会话对其他表：不能读，不能写

    此时其他会话：

    • 对 A 表：可以读，需要等待锁释放后可以写
    • 对其他表：可以读，可以写
    写锁
    /* 加写锁 */
lock table tablelock write;

/* 不能对其他表进行任何操作 */
/* ERROR 1100 (HY000): Table 'dept' was not locked with LOCK TABLES */
select count(1) from dept;

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6

    当前会话，对 A 表加 write 锁：

    • 当前会话对 A 表：可以进行任何操作
    • 当前会话对其他表：不能进行任何操作
    • 其他会话：对 A 表进行操作的前提是等待写锁释放
    MyISAM 模式特征
    • MyISAM 在执行查询语句前，会自动给涉及的所有表加读锁
    • MyISAM 在执行更新操作 (DML) 前，会自动给涉及的表加写锁

    对 MyISAM 表进行读操作：

    • 其他进程对同一表的操作
      • 读：不阻塞
      • 写：阻塞
    • 只有读锁释放后，才会执行其他进程的写操作

    对 MyISAM 表进行写操作：

    • 其他进程对同一表操作
      • 读：阻塞
      • 写：阻塞
    • 只有写锁释放后，才会执行其他进程的写操作
    表锁情况分析

    查看哪些表加了锁：show open tables;

    分析表锁定的严重程度：show status like '%table%'

    • Table_locks_immediate - 能够获取到的锁
    • Table_locks_waited - 需要等待的锁

    Table_locks_immediate / Table_locks_waited> 5000

    • 满足上面的情况建议使用 InnoDB 引擎，否则建议使用 MyISAM 引擎
    • 获取到的资源充分时，使用行锁，因此采用 InnoDB

    行锁

    create table linelock
(
	id int(5) primary key auto_increment,
	name varchar(20)
)engine=innodb;

insert into linelock(name) values('1');
insert into linelock(name) values('2');
insert into linelock(name) values('3');
insert into linelock(name) values('4');
insert into linelock(name) values('5');

set autocommit=0;

/* 当前会话操作第 6 行 */
insert into linelock values(6, 'a6');

/* 其他会话操作第 6 行 */
/* 无法操作，需要等待锁释放 */
update linelock set name='ax' where id=6;

/* 其他会话操作第 8 行，没有锁，可以操作 */
insert into linelock values(8, 'a8');

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15
    • 16
    • 17
    • 18
    • 19
    • 20
    • 21
    • 22
    • 23

    某个会话对一行数据进行 DML 操时，其他会话需要等待锁释放

    释放锁的方法：

    • 表锁：unlock tables; 或 commit / rollback 事务提交
    • 行锁：commit / rollback 事务提交
    行锁的注意事项
    行锁转为表锁

    如果没有索引，行锁会转为表锁

    show index from linelock;

/* 为 name 列增加索引 */
alter table linelock add index idx_linelock_name(name);

/* 当前会话操作 name='3' 的行 */
update linelock set name='a3x' where name='3';

/* 其他会话操作 name='4' 的行 */
/* name 列索引有效，不同的行操作互不影响 */
update linelock set name='a4x' where name='4';

/* 当前会话操作 name=3 的行 */
/* name 列是 varchar 类型，而 3 是整数类型，类型转换时索引失效，行锁转为表锁 */
update linelock set name='a3x' where name=3;

/* 其他会话操作 name='4' 的行 */
/* name 列索引失效，表被锁定，无法操作 name='4' 行，需要等待锁释放 */
update linelock set name='a4x' where name='4';

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15
    • 16
    • 17
    • 18
    • 19
    间隙锁

    间隙锁是行锁的一种特殊情况，MySQL 会自动给间隙加锁

    /* 不存在 id=7 的数据，此时 MySQL 会自动加上间隙锁 */
update linelock set name='x' where id>1 and id<9;

/* 其他会话操作 id=7 需要等待锁释放 */
insert into linelock value(7, 'a7');

    • 1
    • 2
    • 3
    • 4
    • 5
    行锁分析
    • 如果加锁时有 where 语句，where 范围内的数据都会被加锁
    • 并发能力强，效率高
    show status like '%innodb_row_lock%';

    • 1
    类型说明
    Innodb_row_lock_current_waits当前正在等待锁的进程数量
    Innodb_row_lock_time从系统启动到现在，等待总时长
    Innodb_row_lock_time_avg从系统启动到现在，平均等待时长
    Innodb_row_lock_time_max从系统启动到现在，最大等待时长
    Innodb_row_lock_waits从系统启动到现在，等待次数
    查询行锁
    /* for update 为查询语句加锁 */
select * from linelock where id=2 for update;

/* 其他会话操作该行要等待锁释放 */
update linelock set name='x' where id=2;

    • 1
    • 2
    • 3
    • 4
    • 5

    关闭事务自动提交的三种方式

    • set autocommit =0;
    • start transaction;
    • begin;
  • 相关阅读:
    Feign源码解析7：nacos loadbalancer不支持静态ip的负载均衡
    基础化学试题A卷
    MySQL远程链接踩坑
    SQL必知必会 笔记第二章
    【Python人工智能】Python全栈体系（二十二）
    JavaScript 编程的内部方法有哪些？
    动态规划-杨辉三角（leetcode）
    java ssm德育分活动报名系统springboot+vue
    SpringBoot源码 | prepareContext方法解析
    矩阵分析与应用+张贤达
  • 原文地址：https://blog.csdn.net/weixin_43734095/article/details/124991111