MySQL规范设计，千万级数据与慢SQL的优化教程

发表于

说明：该笔记来自高性能可扩展MySQL数据库架构设计与优化，自行整理。

一、项目说明：该笔记的背景为电商平台项目，电商项目由于其高并发、多线程、高耗能等特性，在众多的项目类型中涉及的技术面最广，故以此为例作为案例：

在这里插入图片描述

1、主要功能模块：

在这里插入图片描述

2、对于一个项目的立项，从需求分析到技术栈的决定，其中的业务执行都离不开持久层中对数据库的操作，数据库的库、表设计等规范对项目的高效、可用性都具有很大的影响。以下，对数据库的相关规范进行分析及说明:

数据库设计规范包括：

1
2
3
4
5
6
java复制代码数据库命名规范；
数据库基本设计规范；
数据库索引设计规范；
数据库字段设计规范；
数据库SQL开发规范；
数据库操作行为规范。

二、数据库命名规范

1、所有数据库对象名称必须使用小写字母并用下划线分割

1
2
3
java复制代码不同的数据库名      DbName  dbname

不同的表名         Table   table   tabLe

2、 所有数据库对象名称禁止使用MySQL保留关键字

1
sql复制代码select id , username , `from` ,age from tb_user

注意：如果一定要使用保留字，那么，就在保留字上添加 `` 符号，数据库在编译时会对其进行判定为普通字符，避免错误

3、数据库对象的命名要能做到见名知意，并且组好，不要超过32个字符

1
2
3
sql复制代码例如：  用户数据库 user_db

用户账号表  user_account

4、临时库表必须以tmp为前缀并以日期为后缀

1
sql复制代码备份库，备份表必须以bak为前缀并以日期为后缀

5、所有存储相同数据的列名和列类型必须一致

在这里插入图片描述

三、数据库基本设计规范

1、所有表必须使用Innodb存储引擎

1
2
3
sql复制代码5.6 以后的默认引擎

支持事务，行级锁，更好的恢复性，高并发下性能更好

2、数据库和表的字符集统一使用UTF8

1
2
3
sql复制代码统一字符集可以避免由于字符集转换产生的乱码

MySQL中UTF8字符集汉字点3个字节，ASCII码占用1个字节

3、所有表和字段都需要添加注释

1
sql复制代码使用 comment 从句添加表和列的备注

4、尽量控制单表数据量的大小，建议控制在500万以内

1
2
3
sql复制代码500万并不是MySQL数据库的限制；

可以用历史数据归档，分库分表等手段来控制数据量大小

MySQL最多可以存储多少万数据呢？

1
sql复制代码这种限制取决于存储设置和文件系统。

5、谨慎使用MySQL分区表

1
2
3
4
5
sql复制代码分区表在物理上表现为多个文件，在逻辑上表现为一个表

谨慎选择分区键，跨分区查询效率可能更低

建议采用物理分表的方式管理大数据

6、尽量做到冷热数据分离，减小表的宽度

1
2
3
4
5
sql复制代码减少磁盘IO，保证热数据的内存缓存命中率

更有效的利用缓存，避免读入无用的冷数据

经常一起使用的列放到一个表中

7、禁止在表中建立预留字段

1
2
3
4
5
sql复制代码预留字段的命名很难做到见名识义

预留字段无法确认存储的数据类型，所以无法选择合适的类型

对预留字段类型的修改，会对表进行锁定

8、禁止在数据库中存储图片，文件等二进制数据

9、禁止在线上做数据库压力测试

10、禁止从开发环境，测试环境直连生产环境数据库

四、数据库索引设计规范

1
2
3
java复制代码索引对数据库的查询性能来说是非常重要的

注意： 不要滥用索引

1、限制每张表上的索引数量，建议单张表索引不超过5个

1
2
3
sql复制代码索引并不是越多越好！索引可以提高效率同样可以降低效率

禁止单个表中的每一列都建立单独的索引

2、每个Innodb表必须有一个主键

1
2
3
4
5
sql复制代码不使用更新频繁的列作为主键，不使用多列主键

不使用UUID,MD5,HASH,字符串列作为主键

主键建议选择使用自增ID值

3、常用索引列建议

1
2
3
4
5
sql复制代码SELECT 、UPDATE、DELETE语句的WHERE从句中的列

包含在ORDER BY、GROUP BY、DISTINCT中的字段

多表JOIN的关联列

4、如何选择索引列的顺序

1
2
3
4
5
sql复制代码区分度最高的列放在联合索引的最左侧

尽量把字段长度小的列放在联合索引的最左侧

使用最频繁的列放到联合索引的左侧

5、避免建立冗余索引和重复索引

1
2
3
sql复制代码primary key(id)、index(id)、unique index(id) --重复索引

index(a,b,c)、index(a,b)、index(a) --联合索引有重复的，造成冗余

6、对于频繁的查询优先考虑使用覆盖索引

1
2
3
4
5
6
sql复制代码覆盖索引：查询走到了索引，并且需要返回的数据刚好是索引的组成字段，换句话说，就是select的字段正好是索引字段，就叫覆盖索引。

好处：
避免Innodb表进行索引的二次查找

可以把随机IO变为顺序IO加快查询效率

7、尽量避免使用外键

1
2
3
sql复制代码外键可用于保证数据的参照完整性，但建议在业务端实现

外键会影响父表和子表的写操作从而降低性能

五、数据库字段设计规范

1、优先选择符合存储需要的最小的数据类型

1
2
3
4
5
sql复制代码将字符串转化为数字类型存储

INET_ATON( `255.255.255.255`) = 4294967295

INET_NTOA( 4294967295) = `255.255.255.255`

2、优先选择符合存储 需要的最小的数据类型

1
2
3
4
5
sql复制代码对于非负数据采用无符号整形进行存储

SIGNED INT -214748~2147483647

UNSIGNED INT 0 ~ 4294967295

3、优先选择符合存储需要的最小的数据类型

1
2
3
4
5
sql复制代码VARCHAR(N) 中的N代表的是字符数，而不是字节数

使用UTF8存储汉字Varchar(255) = 765个字节

过大的长度会消耗更多的内存

4、避免使用TEXT、BLOB数据类型

1
2
3
sql复制代码建议把BLOB或是TEXT列分离到单独的扩展表中

TEXT 或 BLOB类型只能使用前缀索引

5、尽可能把所有列定义为 NOT NULL

1
2
3
sql复制代码索引NULL列需要额外的空间来保存，所以要占用更多的空间

进行比较和计算时要对NULL值做特别的处理

6、避免使用ENUM数据类型

1
2
3
4
5
sql复制代码修改ENUM值需要使用ALTER语句

ENUM类型的OREDR BY操作效率低，需要额外操作

禁止使用数值作为ENUM的枚举值

7、避免在字符串存储日期型的数据（不正确的做法）

1
2
3
sql复制代码缺点1： 无法用日期函数进行计算和比较

缺点2： 用字符串存储日期要占用更多的空间

8、使用TIMESTAMP或DATETIME类型存储时间

1
2
3
sql复制代码TIMESTAMP 1970-01-01 00:00:01 ~ 2038-01-19 03:14:07

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

六、SQL开发规范

1、建议使用预编译语句进行数据库操作

在这里插入图片描述

使用预编译语句的好处：

1
2
3
sql复制代码只传参数，比传递SQL语句更高效；

相同语句可以一次解析，多次使用，提高处理效率

2、避免数据类型的隐式转换

1
2
3
sql复制代码隐式转换会导致索引失效

select name,phone from customer where id = `111`

3、充分利用表上已经存在的索引

1
2
3
sql复制代码避免使用双%号的查询条件。如 a like `%123%`，会导致索引失效

一个SQL只能利用到符合索引中的一列进行范围查询

4、程序连接不同的数据库使用不同的账号，禁止跨库查询

1
2
3
4
5
sql复制代码为数据库迁移和分库分表流出余地

降低业务耦合度

避免权限过大而产生的安全风险

5、禁止使用SELECT *，必须使用 SELECT <字段列表> 查询

1
2
3
4
5
sql复制代码消耗更多的CPU和IO以及网络带宽资源

无法使用覆盖索引

可减少表结构变更带来的影响

6、避免使用子查询，可以把子查询优化为JOIN操作

1
2
3
4
5
sql复制代码子查询的结果集无法使用索引

子查询会产生临时表操作，如果子查询数据量大则严重影响效率

消耗过多的CPU及IO资源

7、避免使用JOIN关联太多的表

1
2
3
4
5
sql复制代码每JOIN一个表会多占用一部分内存（join_buffer_size）

会产生临时表操作，影响查询效率

MySQL最多允许关联61个表，建议不超过5个

8、减少同数据库的交互次数

1
2
3
4
5
sql复制代码数据库更适合处理批量操作

合并多个相同的操作到一起，可以提高处理效率

alter table t1 add column c1 int,change column c2 int ...

9、禁止使用 order by rand() 进行随机排序

1
2
3
4
5
sql复制代码a.随机排序会把表中所有符合条件的数据装载到内存中进行排序；

b.随机排序会消耗大量的CPU和IO及内存资源

推荐在程序中获取一个随机值，然后从数据库中获取数据的方式

10、WHERE 从句中禁止对列进行函数转换和计算

1
2
3
4
5
sql复制代码对列进行函数转换或计算会导致无法使用索引

where date(createtime)=`20160901`

where createtime >=`20160901` and createtime <`20160902`

11、在明显不会有重复值时使用UNION ALL 而不是UNION

1
2
3
sql复制代码UNION会把所有数据放到临时表中后再进行去重操作

UNION ALL 不会再对结果及进行去重操作

七、数据库操作行为规范

1、超100万行的批量写操作，要分批多次进行操作

1
2
3
4
5
sql复制代码大批量操作可能会造成严重的主从延迟

binlog日志为row格式时会产生大量的日志

避免产生大失误操作

2、对于大表使用 pt-online-schema-change修改表结构

1
2
3
sql复制代码避免达标修改产生的主从延迟

避免在对表字段进行修改时进行锁表

3、禁止为程序使用的账号赋予super权限

1
2
3
sql复制代码因为当达到最大连接限制时，还允许1个有super权限的用户连接

super权限只能留给DBA处理问题的账号使用

4、对于程序连接数据库账号，遵循权限最小原则

1
2
3
sql复制代码程序使用数据库账号只能在一个DB下使用，不准跨库

程序使用的账号原则上不准有drop权限

Hash分区、RANGE分区、LIST分区

八、MySQL分区表操作

1、定义：数据库表分区是数据库基本设计规范之一，分区表在物理上表现为多个文件，在逻辑上表现为一个表；

2、表分区的弊端： 要谨慎选择分区键，错误的操作可能导致跨分区查询效率降低。

建议 采用物理分表的方式管理大数据。

3、确认MySQL服务器是否支持分区表

使用 SHOW PLUGINS；在mysql命令行查看是否具有分区表的功能：
在这里插入图片描述

查询结果中的 “partition | ACTIVE | STORAGE ENGINE | NULL | GPL “这一行代表当前数据库可以进行数据库分区表操作。

4、普通数据库表的物理结构与分区表的物理结构的区别：

在这里插入图片描述

1
2
3
4
5
6
7
java复制代码分区的sql语句里面多了一句
partition by hash(customer_id) partitions 4;
partition by：根据什么分区
hash：按HASH分区
customer_id：分区键
partitions 4：4个区
图中，左边为普通表的物理结构，右边为分区后的数据库表物理结构。

九、Hash分区表 （按HASH分区）

1、HASH分区的特点

根据MOD（分区键，分区数）的值把数据行存储到表的不同分区中，使数据可以平均的分布在各个分区中。

注意： HASH分区的键值必须是一个INT类型的值，或是通过函数可以转为INT类型。

2、创建HASH分区：

1
2
3
4
5
6
7
sql复制代码use hash;
CREATE TABLE `hash`.`customer_login_log`(
	customer_id int UNSIGNED not null,
	login_time TIMESTAMP,
	login_ip int UNSIGNED,
	login_type TINYINT NOT NULL
) PARTITION by hash(login_ip) PARTITIONS 6;--根据ip进行分区，不同的ip，分到的区不同

①、查看 customer_login_log 分区表物理结构：
在这里插入图片描述
②、customer_login_log 普通非分区表物理结构：
在这里插入图片描述
③、向HASH分区表customer_login_log中插入数据：

1
2
sql复制代码INSERT INTO customer_login_log(customer_id,login_time,login_ip,login_type)
VALUES (1,now(),11111,1);

④、查看分区表数据：
在这里插入图片描述

十、RANGE 分区表（按范围分区）

1、RANGE分区特点：

RANGE分区 是根据分区键值的范围把数据行存储到表的不同分区中，并且 多个分区的范围要连续，但是不能重叠。

注意： 默认情况下使用VALUES LESS THAN属性，即每个分区不包括指定的那个值

2、创建RANGE分区表：

1
2
3
4
5
6
7
8
9
10
11
12
sql复制代码create table `customer_login_log`(
 `customer_id` int(10) UNSIGNED not null,
 `login_time` TIMESTAMP not null,
 `login_ip` int(10) UNSIGNED not null,
 `login_type` TINYINT(4) NOT NULL
) ENGINE=INNODB
PARTITION BY RANGE( customer_id)(
	 PARTITION P0 VALUES LESS THAN (10000),
   PARTITION P1 VALUES LESS THAN (20000),
   PARTITION P2 VALUES LESS THAN (30000),
   PARTITION P3 VALUES LESS THAN MAXVALUE
)

在这里插入图片描述
分区范围说明：

当插入的数据为30000到40000分区范围的数据时，没有创建分区范围为40000的分区的情况下，会返回错误提示；但，当存在图中p3分区的MAXVALUE这一分区时，所以没有指明分区范围的数据都会被插入到p3中

3、RANGE分区的使用场景

分区键为日期或是时间类型
所有查询中都包括分区键
定期按分区范围清理历史数据

十一、List分区（按分区键取值分区）

1、LIST分区的特点

定义： LIST分区按分区键取值的列表进行分区，并且同范围分区一样，各分区的列表只不能重复

注意：每一行数据必须能找到对应 分区列表，否则数据插入失败

2、创建LIST分区表：

1
2
3
4
5
6
7
8
9
10
sql复制代码create table `customer_login_log_list`(
 `customer_id` int(10) UNSIGNED not null,
 `login_time` TIMESTAMP not null,
 `login_ip` int(10) UNSIGNED not null,
 `login_type` TINYINT(4) NOT NULL
) ENGINE=INNODB
PARTITION BY LIST (login_type)(
	PARTITION P0 VALUES IN (1,3,5,7,9),
	PARTITION P1 VALUES IN (2,4,6,8)
)

插入包含未建立分区的分区键的值，会返回错误：

1
sql复制代码INSERT INTO customer_login_log_list(customer_id,login_time,login_ip,login_type)VALUES(100,now(),1,10)

错误截图：
在这里插入图片描述
根据login_type 的值进行分区 p0存储login_type为 1,3,5,7,9;p1存储login_type为2,4,6,8的数据，而插入的数据的login_type为10，不包含在p0或p1的login_type范围中，所以插入失败，返回错误提示。

十二、SQL执行计划及分页查询优化、分区键统计

1、执行计划分析

执行计划能告诉我们什么？

SQL如何使用索引
联接查询的执行顺序
查询扫描的数据行数
执行计划中的内容：

在这里插入图片描述

十三、执行计划内容的作用分析及示例

1、 ID列

执行计划中的id列的意义：

1
2
3
java复制代码ID列中的数据为一组数字，表示执行SELECT语句的顺序
ID值相同时，执行顺序由上至下
ID值越大优先级越高，越先被执行

查看执行计划：

1
2
3
4
5
6
7
8
sql复制代码EXPLAIN SELECT
	c.`category_name`,
	a.`product_name`,
	b.`title`
FROM
	product_info a
JOIN product_comment b ON a.`product_id` = b.`product_id`
JOIN product_category c ON c.`category_id` = a.`one_category_id`;

截图：在这里插入图片描述
复杂sql查看执行计划：

1
2
3
4
5
6
7
8
sql复制代码EXPLAIN
select title
from product_comment
WHERE product_id in (
	SELECT max(product_id)
	from product_info
	WHERE one_category_id in (select min(category_id) from product_category)
  )

截图：在这里插入图片描述

1
java复制代码id为1，2，3

分组查询sql查看执行计划：

1
2
3
4
5
6
7
sql复制代码EXPLAIN
SELECT title
from (
	SELECT one_category_id,max(product_id)AS pid
		from product_info
	  GROUP BY one_category_id
		) a  JOIN product_comment b on a.pid = b.`product_id`

截图：在这里插入图片描述

1
java复制代码id两个为1，一个为2

2、SELECT_TYPE列

在这里插入图片描述
执行计划案例1：

1
2
3
4
5
6
7
8
9
10
11
12
13
sql复制代码EXPLAIN 
SELECT title
FROM product_comment
WHERE
  product_id IN (
	SELECT max(product_id)
	FROM product_info
	WHERE 
	one_category_id IN (
		SELECT min(category_id)
		FROM	product_category
		)
	);

在这里插入图片描述
在这里插入图片描述

3、TABLE列

作用： 输出数据行所在的表的名称

1
2
sql复制代码<unionM,N>由ID为M,N查询union产生的结果集
<derivedN>/<subqueryN>由ID为N的查询产生的结果

执行计划案例1：

1
2
3
4
5
sql复制代码EXPLAIN
select product_category.`category_name`,product_info.`product_name`,product_comment.`title`
from product_info
JOIN product_comment on product_info.`product_id`=product_comment.`product_id`
JOIN product_category on product_category.`category_id`=product_info.`one_category_id`

在这里插入图片描述

4、PARTITIONS列

1
2
java复制代码作用： 对于分区表，显示查询的分区ID
对于非分区表，显示为NULL

执行计划案例：

1
2
3
4
sql复制代码EXPLAIN
SELECT * 
FROM `crn`.`customer_login_log`
where customer_id = 1

在这里插入图片描述
以用户登录日志为例，应该使用用户表的用户id作为分区条件进行数据的存储和归档，这样有利于将同一个用户的所有数据写入到同一个分区区间，有利于避免查询登录日志时会对大表进行查询过程中对其他用户的登录日志进行过滤而导致的效率损耗！

5、TYPE列

在这里插入图片描述

6、Extra列

在这里插入图片描述

7、POSSIBLE_KEYS列

①指出MySQL能使用那些索引来优化查询

②查询列所涉及到的列上的索引都会被列出，但不一定会被使用

8、KEY列

①查询优化器优化查询实际所使用的索引

②如果没有可用的索引，则显示为NULL

③如查询使用了覆盖索引，则该索引仅出现在Key列中

9、KEY_LEN 列

①表示索引字段的最大长度

②Key_len的长度由字段定义计算而来，并非数据的实际长度

10、Ref列

表示哪些列或常量被用于查找索引列上的值

11、Rows列

①表示MySQL通过索引统计信息，估算的所需读取的行数

②Rows值的大小是个统计抽样结果，并不十分准确

12、Filtered列

①表示返回结果的行数占需读取行数的百分比

②Filtered列的值越大越好

③Filtered列的值依赖说统计信息

十四、执行计划的限制

1、无法展示存储过程，触发器，UDF对查询的影响

2、无法使用EXPLAIN对存储过程进行分析

3、早期版本的MySQL只支持对SELECT语句进行分析

十五、优化分页查询示例

需求: 根据audit_status及product_id 创建联合索引，这里需要明确哪一个值放在联合索引的左侧，使用product_id作为索引放在组合索引左侧是最合适的。

创建执行计划：

1
2
3
4
5
6
sql复制代码EXPLAIN
SELECT customer_id,title,content
from product_comment
where audit_status=1
AND product_id=199727
limit 0,5

在这里插入图片描述
初步优化，创建联合索引：

1
sql复制代码CREATE INDEX idx_productID_auditStats on product_comment(product_id,audit_status)

经过添加索引优化后的执行计划执行结果：
在这里插入图片描述
其查询效率明显提高，由type列可知，由原来的ALL进行全表扫描查询降为非唯一索引查询。

进一步优化分页查询

1
2
3
4
5
6
7
sql复制代码SELECT t.customer_id,t.title,t.content
from (
    SELECT `comment_id`
       from product_comment
       where product_id=199727 AND audit_status=1 LIMIT 0,5
) a JOIN product_comment t
ON a.comment_id = t.comment_id;

优化说明： 先通过分页查询获取到对应数据的comment_id，此时的查询不会对其他字段进行查询返回，默认可以通过主键索引进行查询，效率极高；然后再讲查询到的a.commetn_id作为临时子表再与product_comment进行comment_id的匹配查询，此时直接通过comment_id进行查询返回包含comment_id在内的其他的字段。这种查询方式在IO上能节约很多的资源，当数据量上万时，效率依然不会受到太大影响。

十六、如何删除重复数据

删除评论表中对同一订单同一商品的重复评论，只保留最早的一条

1、步骤一：查看是否存在对于一订单同一商品的重复评论

1
2
3
sql复制代码select order_id,product_id,COUNT(*)
from product_comment
GROUP BY order_id,product_id HAVING COUNT(*)>1

2、步骤二：备份product_comment表

创建备份表：

1
2
sql复制代码CREATE TABLE `mc_productdb`.bak_product_comment_200815
LIKE `mc_productdb`.product_comment;

同步表数据：

1
2
sql复制代码INSERT INTO `mc_productdb`.`bak_product_comment_200815`
select * from `mc_productdb`.`product_comment`;

3、步骤三：删除同一订单的重复评论

1
2
3
4
5
6
7
8
9
sql复制代码DELETE a
FROM product_comment a
JOIN (
	SELECT order_id,product_id,MIN(comment_id) AS comment_id 
	FROM product_comment
	GROUP BY order_id,product_id
	HAVING COUNT(*)>=2
) b ON a.order_id=b.order_id AND a.product_id=b.product_id
AND a.comment_id> b.comment_id

十七、进行分区间统计

需求：统计消费总金额大于1000元的，800到1000元的，500到800元的，以及500元以下的人数

1
2
3
4
5
6
7
8
9
10
11
sql复制代码SELECT count(CASE WHEN IFNULL(total_money,0) >=1000 THEN a.customer_id END) AS '大于1000'
    ,count(CASE WHEN IFNULL(total_money,0) >=800 AND IFNULL(total_money,0)<1000  
    THEN a.customer_id END) AS '800-1000'
    ,count(CASE WHEN IFNULL(total_money,0) >=800 AND IFNULL(total_money,0)<800  
    THEN a.customer_id END) AS '500-800'
    ,count(CASE WHEN IFNULL(total_money,0) <500 THEN a.customer_id END) '小于500'
from mc_userdb.customer_login a
LEFT JOIN
( SELECT customer_id,SUM(order_money) AS total_money
    from mc_orderdb.`order_master` GROUP BY customer_id) b
ON a.customer_id=b.customer_id

在这里插入图片描述

十八、捕获有问题的SQL

核心：利用执行计划优化查询

如何找到需要优化的SQL呢？ 答案：慢查询日志

1
2
3
4
5
6
7
8
9
10
11
12
13
sql复制代码启用mysql慢查日志

set global slow_query_log_file  = /sql_log/slow_log.log;

set global log_queries_not_using_indexes = on;

未使用索引的SQL记录日志

set global long_query_time = 0.001;

抓取执行超过多少时间的SQL(秒)

set global low_query_log = on;

在这里插入图片描述)在这里插入图片描述
在这里插入图片描述

本文转载自: 掘金

开发者博客 – 和开发相关的 这里全都有

0%