MySQL 结构优化

结构设计

  • 过分的反范式化为表建立太多的列
  • 过分的范式化造成太多的表关联
  • 在 OLTP 环境中使用不恰当的分区表
  • 使用外键保证数据的完整性

性能优化顺序

  • 数据库结构设计和SQL语句
  • 数据库存储引擎的选择和参数配置
  • 系统选择及优化
  • 硬件升级

知道常用的数据类型,数据库的范式和反范式,各种字段类型占据的大小,更改字段类型是否锁表,如何处理分布式系统下字段的更改。

1、数据库优化的目的

  • 减少数据冗余,节约数据存储空间
  • 提高查询效率
  • 避免数据维护中的异常:
    • 插入异常: 必须有什么才能有什么
    • 更新异常: 如果更改表中的某个实体的单独属性时,需要对多行进行更新
    • 删除异常: 如果删除表中的某一实体则会导致其他实体的消失

2、数据库结构设计步骤 (1) 需求分析: (2) 逻辑设计: 确定数据实体之间的逻辑关系,逻辑存储结构 (3) 物理设计: 跟据所使用的数据库特点进行表结构设计 (4) 维护优化: 维护好索引

范式与反范式

1、数据库设计范式 (1) 1NF (2) 2NF: 要求一个表中只具有一个业务主键,符合第二范式的表中不存在非主键列,只对部分主键的依赖关系 复合主键 进行表的拆分实现 (3) 3NF: 指每一个非主属性既不部分依赖于也不传递依赖于业务主键,也就是在第二范式的基础上消除了非主属性对主键的传递依赖

范式化优点: 可以尽量的减少数据冗余; 范式化的更新操作比反范式化更快; 范式化的表通常比反范式化更小 范式化缺点: 对于查询需要对多个表进行关联;更难进行索引优化

2、反范式化设计 是为了性能和读取效率的考虑而适当的对数据库设计范式的要求进行违反,而允许存在少量的数据冗余,反范式化就是使用空间来换取时间

反范式化优点: 可以减少表的关联,可以更好的进行索引优化

反范式化缺点: 存在数据冗余及数据维护异常,对数据的修改需要更多的成本

字段选择

当一个列可以选择多种数据类型时, 考虑顺序: 数字类型 ⇒ 日期 / 二进制类型 ⇒ 字符类型

对于相同级别的数据类型,应该优先选择占用空间小的数据类型 == 加载的页(16k)

整数类型

共五种类型

int(2) 不影响占用的字节,使用 tinyint 进行存储

@Q: int(x) x 是什么?

@A: 11 代表的并不是长度,而是字符的显示宽度,navicat 进行格式化显示了

实数类型 共三种类型 FLOAT 4B DOUBLE 8B DECIMAL: 每 4 个字节存 9 个数字,小数点占一个字节

@Q: DECIMAL(18,9)占用几个字节

@A: 需要 9 个字节来存储, 表示 9 位整数,9 位小数, 小数点占一个字节, 2*4+1=9

字符串类型 常用的两种,还支持 blog, text 类型

1、varchar 类型 varchar 特点

  • varchar 用于存储变长字符串,只占用必要的存储空间列的
  • 最大长度小于 255 则只占用一个额外字节用于记录字符串长度
  • 列的最大长度大于 255 则要占用两个额外字节用于记录字符串长度
  • 更改 varchar 小于 255 不会锁表,大于 255 会锁表
  • 使用时需要确定 varchar 的长度

varchar 使用场景:

  • 字符串列的最大长度比平均长度大很多
  • 字符串列很少被更新
  • 使用了多字节字符集存储字符串

2、char 类型

char 类型的特点:

  • CHAR 类型是定长的
  • 字符串存储在 CHAR 类型的列中会删除末尾的空格
  • CHAR 类型的 最大宽度为255

char 的适用场景:

  • CHAR 类型适合存储所长度近似的值,如 MD5 加密的密码
  • CHAR 类型适合存储短字符串,限制 255
  • CHAR 类型适合存储经常更新的字符串列,不会锁表,内存占用固定

日期类型

Untitled

共支持四种类型,分别占8B,4B,3B,xB (1) DATATIME 类型 以格式存储日期时间 DATATIME 类型与时区无关,占用8个字节的存储空间时间范围

1
2
3
4
5
YYYY-MM-DD HH:MM:SS[.fraction]
datetime=YYYY-MM-DD HH:MM:SS
datetime(6)=YYYY-MM-DD HH:MM:SS.fraction
-- 支持的范围
1000-1-1 0:0:0~9999-12-31 23:59:59

(2) TIMESTAMP 类型 由格林尼治时间 1970 年 1 月 1 日到当前时间的秒数以]的格式显示 占用 4个字节 默认使用 第一个 timestamp 进行自动更新

1
YYYY-MM-DD HH:MM:SS.[L.fraction

比较 timestamp 类型显示依赖于所指定的时区 在行的数据修改时可以自动修改 timestamp 列的值

时区比较 存储微秒值

1
2
3
set time_zone='+10:00';
# timestamp 与时区相关
ALERT TABLE t MODIFY d1 DATETIME(6), MODIFY d2 TIMESTAMP(6);

(3) Date 类型 为实现 Date,原来使用 int, datetime 存储 占用的字节数比使用字符串、datetime、int 存储要少,使用 date 类型只需要3个字节 Date 类型还可以利用日期时间函数进行日期之间的计算

(4) time 类型 time 类型用于存储时间数据

日期类型使用注意事项

  • 不要使用字符串类型来存储日期时间数据日期时间类型
  • 通常比字符串占用的存储空间小日期时间类型在进行查找过滤时可以利用日期来进行对比
  • 日期时间类型还有着丰富的处理函数,可以方便的对时期类型进行日期计算
  • 使用 Int 存储日期时间不如使用 Timestamp 类型

使用 int 时间戳保存

1
2
select UNIX_TIMESTAMP('2020-01-11 09:53:32');  
select FROM_UNIXTIME(1578707612);

Recommand

使用 TIMESTAMP(4 个字节) 或 DATETIME 类型 (8 个字节) 存储时间 TIMESTAMP 存储的时间范围 1970-01-01 00:00:01 ~ 2038-01-19-03:14:07

TIMESTAMP 占用 4 字节和 INT 相同,但比 INT 可读性高

超出 TIMESTAMP 取值范围的使用 DATETIME 类型存储

尽量做到冷热数据分离,减小表的宽度 MySQL 限制每个表最多存储 4096 列,并且每一行数据的大小不能超过 65535 字节。 减少磁盘 IO, 保证热数据的内存缓存命中率(表越宽,把表装载进内存缓冲池时所占用的内存也就越大,也会消耗更多的 IO); 更有效的利用缓存,避免读入无用的冷数据; 经常一起使用的列放到一个表中(避免更多的关联操作)。

尽量控制单表数据量的大小, 建议控制在 500 万以内。

500 万并不是 MySQL 数据库的限制,过大会造成修改表结构,备份,恢复都会有很大的问题。 可以用历史数据归档(应用于日志数据),分库分表(应用于业务数据)等手段来控制数据量大小。

临时库表必须以 tmp_ 为前缀并以日期为后缀,备份表必须以 bak_ 为前缀并以日期 (时间戳) 为后缀。

日期相关 timestamp 与时区相关,占用 4个字节 datetime, 1000-01-01 00:00:00 ~ 9999-12-31 23:59:59

1
2
3
4
5
6
7
8
9
10
11
12
13
# 查看当前会话时区
SELECT @@session.time_zone;

# 设置当前会话时区
SET time_zone = 'Europe/Helsinki';
SET time_zone = "+00:00";

# 数据库全局时区设置
SELECT @@global.time_zone;

# 设置全局时区
SET GLOBAL time_zone = '+8:00';
SET GLOBAL time_zone = 'Europe/Helsinki';

索引优化

索引是在存储引擎层实现的, 部分存储引擎不支持索引

B+ 树索引

B+树索引:

1、特点: B-tree 索引能够加快数据的查询速度 B-tree 索引更适合进行范围查找,叶子节点指向的是主键

2、适合使用 B+ 树的索引

全值匹配 最左前缀 匹配列前缀 范围匹配 精确匹配 只访问索引,覆盖索引

3、B+ 树使用限制

查询优化器判定。。。

非最左列开始查找,。。。

不能跳过左边的 Not in 和 <> 无法使用 如果查询中有某个列的范围查询,则其右边所有列都无法使用索引

Hash 索引

Memory 支持,且为默认的 InnoDB 存储引擎支持自适应 Hash 索引

1、特点

精确匹配使用,只用在等值查询

所有列,每一行计算一个Hash码

2、限制

需要两次查找,先找到行,之后读取值

无法进行排序 只支持等值查找,不支持索引查找、范围查找

Hash 冲突可能导致性能问题,不适合选择性差的列

索引概述

1、使用索引的好处:

  • 减少存储引擎需要扫描的数量,16K 每页,内存中存放更多的索引
  • 帮助排序,避免使用临时表,索引存储结构为 B 树,按序存储
  • 将随机 I/O 转化成顺序的 I/O,扫描的行变少

2、索引带来的损耗:

  • 写操作成本,需要对应的索引控制
  • 多个索引会增加查询优化器的选择时间

3、建立索引的策略:

安装演示数据库:

1
2
3
<http://downloads.mysql.com/docs/sakila-db.tar.gztar-zxf> sakila-db.tar.gz
mysql -uroot -p < sakila-schema.sql
mysql-uroot -p < sakila-data.sql

索引优化策略

1、索引列上不能使用函数或表达式

2、索引长度有限,针对字符串

3、通过索引的选择性确定前缀索引的长度(字符串)

4、选取索引列的顺序:

  • 经常使用的放到列的左边(选择性差的例外)
  • 选择性高的优先
  • 宽度小的列优先,I/O 小

5、覆盖索引直接获取:

优点: 可以优化缓存,减少磁盘I/O 减少随机I/O,将磁盘I/O改为内存的顺序 I/O 可避免对 Innodb 主键索引的二次查询, 可以避免 MyISAM 表进行系统调用

缺点: memory 不支持覆盖索引 查询中太多的列。。。 使用双 % 号的 LIKE 查询

1
2
3
4
5
6
7
8
9
10
11
12
-- using index,覆盖索引使用
expalin select id from <mytab> where id=1\\G
-- using where, 非覆盖所以不
explain select * from <mytab> where id=1\\G
-- idx_name name 建立索引查询, using where,using index, 自动增加上主键索引的信息
-- 高版本优化 using indexexplain
select id,name from <mytab> where name='joe'
-- 1. 表达式剔除: out_date 为索引列, 查找近一个月内添加的商品
select ... from productwhere to_days(out_date)-to_days(current_date)<=30
-- 优化后的
select ... from productwhere out_date <= date_add(current_dateinterval 30 day)
-- 2. 前缀索引长度确定

索引优化

优化排序

优化排序: 通过排序操作 按照索引顺序扫描数据

优化的限制:

  • 索引的列顺序和 Order By 子句的顺序完全一致
  • 索引中所有列的方向(升序,降序)和 Order by 子句完全一致
  • Order by 中的字段全部在关联表中的第一张表中

仿 Hash 索引优化

模拟 Hash 索引优化

(1) 使用流程 新增 title_hash 列 设置值 创建索引

使用限制

只能处理键值的全值匹配查找 所使用的Hash函数决定着索引键的大小

1
2
3
4
5
6
7
8
9
alter table <mytab> add title_hash varchar(32)
update <mytab> set title_name=md5(title);
create index idx_title_hash on <mhytab>(title_hash);

-- using condition index,...
explain select *
from <mytab>
where title_hash=md5('sss')
and title='sdfsfds'\\G

优化锁

索引优化锁

避免对表的全部锁定

1
2
3
4
5
6
-- session1
begin;
select * from actor where name='ddd' for update;
-- session 2, 未使用锁住,使用索引不会锁住
begin;
select * from actor where name='eee';

索引自身问题

4、删除重复和冗余的索引

单列的索引类型不同的重复 联合索引的最左匹配原则,形成冗余

1
2
-- todo 工具下载
pt-duplicate-key-checker h=127.9.0.1

5、查找未使用的索引

定期清理 通过 performance_schema,information 数据库查询出信息

1
2
3
4
5
6
7
8
9
-- 查看所有数据中对应的表,对应的索引名称,索引使用的次数
SELECT CONCAT(object_schema, ".", object_name) AS TABLE_NAME,
index_name AS INDEX_NAME,
b.`TABLE_ROWS` AS TABLE_ROWS
FROM performance_schema.table_io_waits_summary_by_index_usage a
JOIN information_schema.tables b
ON a.`OBJECT_SCHEMA`=b.`TABLE_SCHEMA` AND a.`OBJECT_NAME`=b.`TABLE_NAME`
WHERE index_name IS NOT NULL AND count_star = 0
ORDER BY object_schema, object_name;

6、更新索引统计信息及减少索引碎片

1
2
3
analyze table <mytab>
-- 可能锁表
optimize table <mytab>

SQL 查询优化

SQL 性能优化的目标:至少要达到 range 级别,要求是 ref 级别,如果可以是 consts 最好。

获取慢查询的方式:

  • 最终用户、测试人员获取存在性能问题的 SQL, 被动,常用
  • 慢查询日志获取,服务层的日志
  • 实时获取存在性能问题的 SQL

SQL 语句执行流程

查询语句的执行流程

http://img.janhen.com/202102261610131553910277229.png

优化器的作用:选择索引是优化器的⼯作。⽽优化器选择索引的⽬的,是找到⼀个最优的执⾏⽅案,并⽤最⼩的代价去执⾏语句。在数据库⾥⾯,扫描⾏数是影响执⾏代价的因素之⼀。扫描的⾏数 越少,意味着 访问磁盘数据的次数 越少,消耗的CPU资源越少。 当然,扫描⾏数并不是唯⼀的判断标准,优化器还会结合 是否使⽤临时表、是否排序 等因素进⾏综合判断。

1
权限校验--->查询缓存--->分析器--->优化器--->权限校验--->执行器--->引擎

Q: MySQL是怎样得到索引的基数的呢?

MySQL采样统计的⽅法,采样统计的时候,InnoDB 默认会选择 N 个数据⻚,统计这些⻚⾯上的不同值,得到⼀个平均值,然后乘以这个索引的⻚⾯数,就得到了这个索引的基数。 ⽽数据表是会持续更新的,索引统计信息也不会固定不变。所以,当变更的数据⾏数超过 1/M 的时候,会⾃动触发重新做⼀次索引统计。

执行更新语句的流程

http://img.janhen.com/202103072200261553911424532.png

MySQL可以恢复到半个⽉内任意⼀秒的状态;

涉及到两个日志文件 redo log 和 binlog;

两阶段提交,以及 Insert Buffer 插入缓冲

1
2
UPDATE T SET c=c+1 WHERE ID=2;
分析器---->权限校验---->执行器--->引擎---redo log prepare--->binlog--->redo log commit

SQL 解析及执行计划

1、SQL 执行过程

(1) 客户端发送 SQL 请求给服务器

(2) 服务器检查是否可以在查询缓存中命中该 SQL

(3) 服务器端进行 SQL 解析,预处理,再由优化器生成对应的执行计划

(4) 跟据执行计划,调用存储引擎 API 来查询数据将结果返回给客户端

查询缓存

查询缓存:

缓存加锁 对于一个读写频繁的系统使用查询缓存很可能会降低查询处理的效率 所以在这种情况下建议大家不要使用查询缓存

查询缓存的配置参数

1
2
3
4
5
6
query_cache_type
# 所使用的的大小
query_
# 缓存结果的最大值
# 锁住,是否返回缓存中的数据
# 查询缓存内存块的最小

查询优化器

1、可能导致失败的情况:

(1) 统计信息不准确,如 Innodb 中的总条数为抽样的数据

(2) 执行计划中的成本估算不等同于实际的执行计划的成本,无法知道顺序读取与随机读取,是否在内存中

(3) MySQL 基于其成本模型进行。。。

(4) 从不考虑其他并发的查询

(5) 不会考虑不受控制的成本,如存储过程、用户自定义的函数

2、查询优化器的作用

(1) 优化 count(), min() 和 max(), B+树的。。。

(2) 将表达式转换成一个常数,MyISAM中的 Selelct Count(*)

  1. 子查询优化: 转化成关联查询
  2. 提前终止查询:
  3. 对 in() 条件进行优化,对 in 中的数据进行排序,之后通过二分查找方式确定是否满足条件 ### 查询耗时 1、通过 profile 高版本已经不建议使用,建议使用方式二
  4. 开启与执行
1
2
3
4
5
6
7
8
9
-- 开启
set profiling=1;
select count(*) from <mytab>
-- 查看整体信息
show profiles;
-- 查看执行的每个阶段信息
show profile for query <query_id>;
-- 查看 CPU 的信息
show profile cpu for query <query_id>;
  1. 结果数据

2、通过 performance_schema(建议)

在 MySQL5.6 之后建议使用 对数据全局有效 (1) 开启与使用

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
-- 1. 处理的先决条件,开启 performance_schema
UPDATE `setup_instruments`
SET enabled='YES',TIMED='YES'
WHERE NAME LIKE 'stage%';

UPDATE setup_consumers
SET enabled='YES'
WHERE NAME LIKE 'events%';

-- 2. 查询使用
SELECT a.THREAD_ID, SQL_TEXT,c.EVENT_NAME,(c.TIMER_END-c.TIMER_START)/1000000000 AS 'DURATION(ms)'
FROM events_statements_history_long a
JOIN threads b
ON a.THREAD_ID=b.THREAD_IDJOIN events_stages_history_long c
ON c.THREAD_ID=b.THREAD_ID
AND c.EVENT_ID BETWEEN a.EVENT_ID AND a.END_EVENT_ID
WHERE b.PROCESSLIST_ID=CONNECTION_ID()
AND a.EVENT_NAME='statement/sql/select'
ORDER BY a.THREAD_ID,c.EVENT_ID
  1. 信息返回 返回各个阶段的耗时 执行的线程ID

慢查询日志

写日志为顺序存储

慢查询配置

  • 动态参数,可配合脚本定时开关 slow_query_log
  • 存放位置。将日志存储与数据存储分开 slow_query_log_file
  • 查询阈值,秒为单位,建议 0.001 秒 long_query_time
  • 记录未使用索引的 SQL log_queries_not_using_indexes
  1. 实际使用的SQL
1
2
3
4
SHOW VARIABLES LIKE '%quer%'SET GLOBAL slow_query_log=on;
SET GLOBAL slow_query_log_file='/var/log/mysql/mysql-slow.log'
SET GLOBAL long_query_time=0.001;
SET GLOBAL log_queries_not_using_indexes=ON;

2、慢查询记录的内容

用户

查询的时间,精确到毫秒

查询占用的锁时间

返回的数据行数

扫描的行数 执行SQL的时间,

UNIX时间戳

对应的SQL语句

1
2
3
4
5
# Time: 2019-05-30T10:10:02.771744Z
# User@Host: root[root] @ localhost [127.0.0.1] Id: 44
# Query_time: 0.001503 Lock_time: 0.001002 Rows_sent: 12 Rows_examined: 12
SET timestamp=1559211002;
SELECT * FROM exam;

mysqldumpslow 获取

通过自带的工具进行筛选输出 (1) -s order(c,t,l,r,at,al,ar): 指定按哪种排序方式输出结果

  • c:总次数
  • t:总时间
  • 1:锁的时间
  • r:总数据行
  • at,al,ar:t,l,r平均数
  • t top: 指定取前几条作为结束输出
1
mysqldumpslow -s <r> -t <10> <slow-mysql.log>

pt-query-digest 获取

建议使用 还支持对 bin log等日志的进行查看

1
pt-query-digest --explain h=127.0.0.1,u=root,p=password mysql-slow.log

返回结果: 整体的统计信息,total,max,min

执行计划

实时获取

通过 information_schema.proceeslist 表进行查看获取实时数据

1
2
3
4
5
6
7
8
9
10
-- 查询出服务器中执行超过 60s 的SQL
SELECT id,
user,
DB,
command,
time,
state,
info
FROM information_schema.PROCESSLIST
WHERE TIME >= 60;

特定 SQL 的优化

大表的数据修改分批处理:

1000 万行记录的表中删除/更新 100 万行记录 一次只删除/更新 5000 行记录,暂停几秒

方式一: 通过存储过程实现

1
DELIMITER $$USE `<myDB>`$$DROP PROCEDURE IF EXISTS `p_delete rows`$$CREATE DEFINER=`root`@`127.0.0.1` PROCEDURE `p_delete_rows`()BEGIN   DECLARE v_rows INT;  SET v_rows=1;  WHILE v_roWs >0  DO     -- 根据业务修改下面的语句    DELETE FROM sbtest1 WHERE id>=90000 AND id <=19000 LIMIT 5000;    SELECT ROW_COUNT() INTO v_rows;    -- sleep    SELECT SLEEP(5);  END WHILE;END$$DELIMITER;

方式二: 程序中执行类似存储过程的逻辑每次获取少量数据,之后执行处理逻辑

修改大表的表结构

对表的列的字段类型进行修改,改变字段的宽度会锁表,无法解决主从数据库延迟问题

方式一: 主从切换

方式二: 手动创建新表进行迁移,减少主从延迟,操作复杂

可使用 pt-online-schema-change 工具 完成方式二的复杂逻辑

1
2
3
4
5
6
pt-online-schema-change
--alter="MODIFY C VARCHAR(150) NOT NULL DEFAULT ''"
--user=root \\
--password=PassWord D=<mydb>,t=<mytab>
--charset=utf8 \\
--execute

优化 not in 和 <> 查询(#)

通过外连接 + NULL 进行处理,即为将子查询转化成表的连接查询; 常用的调整手段;

1
2
3
4
5
6
7
8
9
10
-- 原始SQL
SELECT customer_id, first_name, last_name, email
FROM customerWHERE customer_id NOT IN
(SELECT customer_id FROM payment)

-- 通过 Left join 配合 null 的优化
SELECT a.customer_id,a.first_name,a.last_name,a.email
FROM customer a
LEFT JOIN payment b
ON a.customer_id =b.customer_idWHERE b.customer_id IS NULL

使用汇总表优化查询(#)

汇总表概述 汇总表就是提前以要统计的数据进行汇总并记录到表中以备后续的查询使用。 汇总表的数据可以使非实时的。

实际使用汇总表的策略

  • 使用汇总表记录从该天开始之前的所有统计信息,之后每天进行更新维护;
  • 查询时借助统计信息表的数据和当天的数据汇总返回;
  • 汇总表被当做一种缓存,当天数据类似增量信息;
  • 从原来的表中提取出汇总信息,不会变更原来的表结构,侵入性低,是一种常用的扩展手段。
1
2
3
4
5
6
7
8
9
10
11
12
13
-- 原始的统计SQL
SELECT COUNT(*) FROM product_ comment WHERE productid=999
-- 创建汇总表
CREATE TABLE product_ comment_cnt(product id INT,cnt INT);
-- 修改后的 SQL, UNION ALL 确定
SELECT SUM(cnt) FROM(
SELECT cnt
FROM product_comment_cnt
WHERE product_id=999
UNION ALL
SELECT COUNT(*)
FROM product_comment
WHERE product_id=999AND timestr>DATE(NOW())) a

只要一行数据时使用 LIMIT 1

已经知道结果只会有一条结果,加上 LIMIT 1 可以增加性能。MySQL 数据库引擎会在找到一条数据后停止搜索,而不是继续往后查少下一条符合记录的数据。

用 Not Exists 代替 Not In

Not Exists 允许用户使用相关子查询已排除一个表中能够与另一个表成功连接的所有记录。Not Exists 用到了连接,能够发挥已经建好的索引的作用,而 Not In 不能使用索引。Not In 是最慢的方式,要同每条记录比较,在数据量比较大的查询中不建议使用这种方式。

1
2
3
4
5
Select a.mobileid
from Log_user a
where not exists (select b.mobileid
from magazineitem b
where b.mobileid=a.mobileid);

Ref

MySQL实战45讲_MySQL_数据库-极客时间

MySQL实战45讲