[数据库] MYSQL 优化总结

0 查询变量参数

• 查询某个参数时 select @@...

-- 查询有哪些变量
-- show variables;
-- show variables like 'autocommit';  show variables like 'max_connections';
-- show session variables like 'max_connections';
-- show global variables like 'max_connections';

-- 查询指定(系统)变量值
-- show [global | session] variables [like 'variable_name'];
select @@date_format;
select @@datetime_format;
select @@innodb_table_locks;

-- 自定义变量
SET @name := 'Jack';
SET @age := 25; 
-- 查询自定义变量 | 后续的查询或操作中可以使用这些变量
SELECT 
    * 
from test.tb_student 
WHERE 1 = 1 and name  = @name and age = @age;

1 数据插入与导入

• 普通插入：

• 采用批量插入（一次插入的数据不建议超过1000条）
• 手动提交事务
• 主键顺序插入

• 大批量插入：

• 如果一次性需要插入大批量数据，使用insert语句插入性能较低，此时可以使用MySQL数据库提供的load指令插入。

# 客户端连接服务端时，加上参数 
--local-infile（这一行在bash/cmd界面输入）
mysql --local-infile -u root -p

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

# 执行load指令将准备好的数据，加载到表结构中 字段分隔符 行分隔符
load data local infile '/root/sql1.log' into table 'tb_user' fields terminated by ',' lines terminated by '\n';

2 主键设计与优化

• 主键优化

• 数据组织方式：在InnoDB存储引擎中，表数据都是根据主键顺序组织存放的，这种存储方式的表称为索引组织表（Index organized table, IOT）

• 页分裂：页可以为空，也可以填充一般，也可以填充100%，每个页包含了2-N行数据（如果一行数据过大，会行溢出），根据主键排列。
• 页合并：当删除一行记录时，实际上记录并没有被物理删除，只是记录被标记（flaged）为删除，并且它的空间变得允许被其他记录声明使用。当页中删除的记录到达 MERGE_THRESHOLD（默认为页的50%），InnoDB会开始寻找最靠近的页（前后）看看是否可以将这两个页合并以优化空间使用。

• MERGE_THRESHOLD：合并页的阈值，可以自己设置，在创建表或创建索引时指定
  CREATE TABLE t1 ( id INT, KEY id_index (id) ) COMMENT='MERGE_THRESHOLD=45';
CREATE TABLE t1 ( id INT, KEY id_index (id) ); ALTER TABLE t1 COMMENT='MERGE_THRESHOLD=40';
CREATE TABLE t1 ( id INT, KEY id_index (id) COMMENT 'MERGE_THRESHOLD=40' );
CREATE TABLE t1 ( id INT, KEY id_index (id) ); ALTER TABLE t1 DROP KEY id_index; ALTER TABLE t1 ADD KEY id_index (id) COMMENT 'MERGE_THRESHOLD=40';
  详情参见： Mysql主键优化之页分裂与页合并 - CSDN
  详情参见： Configuring the Merge Threshold for Index Pages - MYSQL

• 主键设计原则：

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

3 Order By 优化

• Order By 优化

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

如果order by字段全部使用升序排序或者降序排序，则都会走索引，但是如果一个字段升序排序，另一个字段降序排序，则不会走索引， explain 的 extra 信息显示的是 Using index, Using filesort，

如果要优化掉Using filesort，则需要另外再创建一个索引，如：create index idx_user_age_phone_ad on tb_user(age asc, phone desc);，此时使用select id, age, phone from tb_user order by age asc, phone desc;会全部走索引

• 总结：

• 根据排序字段建立合适的索引，多字段排序时，也遵循最左前缀法则
• 尽量使用覆盖索引。

覆盖索引是指select的数据列只用从索引中就能够取得，不必读取数据行。换句话说，查询列要被所建的索引覆盖。

• 多字段排序，一个升序一个降序，此时需要注意联合索引在创建时的规则（ASC/DESC）
• 如果不可避免出现filesort，大数据量排序时，可以适当增大排序缓冲区大小 sort_buffer_size（默认256k）

4 Group By 优化

• Group By 优化

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

如索引为 idx_user_pro_age_stat，则句式可以是select ... where profession order by age，这样也符合最左前缀法则

5 Limit 优化

• Limit 优化

• 常见问题：如limit 2000000, 10，此时需要 MySQL 排序前2000000条记录，但仅仅返回2000000 - 2000010的记录，其他记录丢弃，查询排序的代价非常大。

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

-- 此语句耗时很长
select * from tb_sku limit 9000000, 10;

-- 通过覆盖索引加快速度，直接通过主键索引进行排序及查询
select id from tb_sku order by id limit 9000000, 10;

-- 下面的语句是错误的，因为 MySQL 不支持 in 里面使用 limit -- 
select * from tb_sku where id in (select id from tb_sku order by id limit 9000000, 10);

-- 通过连表查询即可实现第一句的效果，并且能达到第二句的速度
select * from tb_sku as s, (select id from tb_sku order by id limit 9000000, 10) as a where s.id = a.id;

6 Count 优化

• Count 优化

• MyISAM 引擎，把一个表的总行数存在了磁盘上，因此执行 count() 的时候会直接返回这个数，效率很高（前提是不适用where）；
• InnoDB 引擎，在执行 count() 时，需要把数据一行一行地从引擎里面读出来，然后累计计数。

• 优化方案：自己计数，如创建key-value表存储在内存或硬盘，或者是用redis

• count的几种用法：

• 如果count函数的参数（count里面写的那个字段）不是NULL（字段值不为NULL），累计值就加一，最后返回累计值
• 用法：count(*)、count(主键)、count(字段)、count(1)
• count(主键)跟count()一样，因为主键不能为空；

• count(字段)只计算字段值不为NULL的行；
• count(1)引擎会为每行添加一个1，然后就count这个1，返回结果也跟count()一样；
• count(null)返回0

• 各种用法的性能：

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

• 按效率排序：count(字段) < count(主键) < count(1) < count()

即：尽量使用 count()

7 update优化（避免行锁升级为表锁）

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

如以下语句：

-- 这句由于id有主键索引，所以只会锁这一行；
update student set no = '123' where id = 1;，

-- 这句由于name没有索引，所以会把整张表都锁住进行数据更新，解决方法是给name字段添加索引。
update student set no = '123' where name = 'test';，

Y 表引擎 := 存储引擎

• 存储引擎就是存储数据、建立索引、更新/查询数据等技术的实现方式。
• 存储引擎是基于表而不是基于库的。所以，存储引擎也可以被称为表引擎。
• MYSQL的默认存储引擎是InnoDB。

-- 查询建表语句
show create table account;
-- 建表时指定存储引擎
CREATE TABLE 表名(
    ...
) ENGINE=INNODB;

-- 查看当前数据库支持的存储引擎
show engines;

K 索引

优缺点

• 优点：

• 提高数据检索效率，降低数据库的IO成本
• 通过索引列对数据进行排序，降低数据排序的成本，降低CPU的消耗

• 缺点：

• 索引列也是要占用存储空间的
• 索引大大提高了查询效率，但降低了更新速度，比如 INSERT、UPDATE、DELETE

数据结构

• 存储：

• 用二叉树和红黑树会有层级越深，检索速度变慢缺点。

• B Tree

• B+ Tree

• 与 B-Tree 的区别：

• 所有的数据都会出现在叶子节点
• 叶子节点形成一个单向链表

• MySQL 索引数据结构对经典的 B+Tree 进行了优化。在原 B+Tree 的基础上，增加一个指向相邻叶子节点的链表指针，就形成了带有顺序指针的 B+Tree，提高区间访问的性能。

• Hash

• 哈希索引就是采用一定的hash算法，将键值换算成新的hash值，映射到对应的槽位上，然后存储在hash表中。
• 如果两个（或多个）键值，映射到一个相同的槽位上，他们就产生了hash冲突（也称为hash碰撞），可以通过链表来解决。

• 特点

• Hash索引只能用于对等比较（=、in），不支持范围查询（betwwn、>、<、…）
• 无法利用索引完成排序操作
• 查询效率高，通常只需要一次检索就可以了，效率通常要高于 B+Tree 索引

• 存储引擎支持：

• Memory
• InnoDB: 具有自适应hash功能，hash索引是存储引擎根据 B+Tree 索引在指定条件下自动构建的

为什么 InnoDB 存储引擎选择使用 B+Tree 索引结构？

• 相对于二叉树，层级更少，搜索效率高
• 对于 B-Tree，无论是叶子节点还是非叶子节点，都会保存数据，这样导致一页中存储的键值减少，指针也跟着减少，要同样保存大量数据，只能增加树的高度，导致性能降低
• 相对于 Hash 索引，B+Tree 支持范围匹配及排序操作

索引的类别

分类	含义	特点	关键字
主键索引	针对于表中主键创建的索引	默认自动创建，只能有一个	PRIMARY
唯一索引	避免同一个表中某数据列中的值重复	可以有多个	UNIQUE
常规索引	快速定位特定数据	可以有多个
全文索引	全文索引查找的是文本中的关键词，而不是比较索引中的值	可以有多个	FULLTEXT

• 在 InnoDB 存储引擎中，根据索引的存储形式，又可以分为以下两种：

分类	含义	特点
聚集索引(Clustered Index)	将数据存储与索引放一块，索引结构的叶子节点保存了行数据	必须有，而且只有一个
二级索引(Secondary Index)	将数据与索引分开存储，索引结构的叶子节点关联的是对应的主键	可以存在多个

• 聚集索引选取规则：

• 如果存在主键，主键索引就是聚集索引
• 如果不存在主键，将使用第一个唯一(UNIQUE)索引作为聚集索引
• 如果表没有主键或没有合适的唯一索引，则 InnoDB 会自动生成一个 rowid 作为隐藏的聚集索引

索引语法

• 创建索引：

CREATE [ UNIQUE | FULLTEXT ] INDEX index_name ON table_name (index_col_name, ...);

如果不加 CREATE 后面不加索引类型参数，则创建的是常规索引

• 查看索引

SHOW INDEX FROM table_name;

• 删除索引：

DROP INDEX index_name ON table_name;

最左前缀法则

• 如果索引关联了多列（联合索引），要遵守最左前缀法则，最左前缀法则指的是查询从索引的最左列开始，并且不跳过索引中的列。
• 如果跳跃某一列，索引将部分失效（后面的字段索引失效）。如果最左侧不存在则索引失效。
• 联合索引中，出现范围查询（<, >），范围查询右侧的列索引失效。可以用>=或者<=来规避索引失效问题。

SQL 解释器(explain)

• SQL 解释器，是优化数据库的一个重要手段。简单来说，就是在SQL语句中加入一些人为的提示来达到优化操作的目的。

例如：

-- 主键字段 : id int(11) | 唯一索引: id_card varchar(20)

-- 使用索引
explain 
select 
	* 
from test.tb_student 
use index(idx_student_idcard) 
where name="jane" and id_card = '110101199908281112';

不使用哪个索引：
explain 
select 
	* 
from test.tb_student 
ignore index(idx_student_idcard) 
where name="jane" and id_card = '110101199908281112';

必须使用哪个索引：
explain 
select 
	* 
from test.tb_student 
force index(idx_student_idcard) 
where name="jane" and id_card = '110101199908281112';

use 是建议，实际使用哪个索引 MySQL 还会自己权衡运行速度去更改，force就是无论如何都强制使用该索引。

索引失效情况

• 在索引列上进行运算操作，索引将失效。

如：explain select * from tb_user where substring(phone, 10, 2) = '15';

• 字符串类型字段使用时，不加引号，索引将失效。

如：explain select * from tb_user where phone = 17799990015;此处，phone的值没有加引号

• 模糊查询中，如果仅仅是尾部模糊匹配，索引不会失效；如果是头部模糊匹配，索引失效。

如：explain select * from tb_user where profession like '%工程';，前后都有 % 也会失效。

• 用 or 分割开的条件，如果 or 其中一个条件的列没有索引，那么涉及的索引都不会被用到。
• 如果 MySQL 评估使用索引比全表更慢，则：不使用索引。

X 参考文献

• MySQL笔记 - CSDN
• [数据库] MySQL之数据库管理篇 - 博客园/千千寰宇
• [数据库] MYSQL之InnoDB存储引擎 VS MYISAM存储引擎 - 博客园/千千寰宇
• [数据库] MYSQL之存储过程 - 博客园/千千寰宇
• [数据库] 排查MySQL锁表情况及解决思路 - 博客园/千千寰宇
• [数据库/Java] MYSQL之数据库错误代码及解决思路 - 博客园/千千寰宇
• [数据库] MySQL之SQL查询 - 博客园/千千寰宇
• [Flink] Flink CDC Connectors FAQ - 博客园/千千寰宇