MySQl性能优化
原文地址: https://www.cnblogs.com/huchong/p/10219318.html
文章目录
- MySQl性能优化
- 数据库命令规范
- 数据库基本设计规范
- 数据库字段设计规范
- 索引设计规范
- 数据库 SQL 开发规范
- 1. 建议使用预编译语句进行数据库操作
- 2. 避免数据类型的隐式转换
- 3. 充分利用表上已经存在的索引
- 6. 禁止使用 SELECT * 必须使用 SELECT <字段列表> 查询
- 7. 禁止使用不含字段列表的 INSERT 语句
- 8. 避免使用子查询,可以把子查询优化为 join 操作
- 9. 避免使用 JOIN 关联太多的表
- 10. 减少同数据库的交互次数
- 11. 对应同一列进行 or 判断时,使用 in 代替 or
- 12. 禁止使用 order by rand() 进行随机排序
- 13. WHERE 从句中禁止对列进行函数转换和计算
- 14. 在明显不会有重复值时使用 UNION ALL 而不是 UNION
- 15. 拆分复杂的大 SQL 为多个小 SQL
- 数据库操作行为规范
- 关于数据库中如何存储时间的一点思考
数据库命令规范
-
所有数据库对象名称必须使用小写字母并用下划线分割
-
所有数据库对象名称禁止使用 MySQL 保留关键字(如果表名中包含关键字查询时,需要将其用单引号括起来)
-
数据库对象的命名要能做到见名识意,并且最后不要超过 32 个字符
-
临时库表必须以 tmp_为前缀并以日期为后缀,备份表必须以 bak_为前缀并以日期 (时间戳) 为后缀
-
所有存储相同数据的列名和列类型必须一致(一般作为关联列,如果查询时关联列类型不一致会自动进行数据类型隐式转换,会造成列上的索引失效,导致查询效率降低)数据库基本设计规范
数据库基本设计规范
1. 所有表必须使用 Innodb 存储引擎
没有特殊要求(即 Innodb 无法满足的功能如:列存储,存储空间数据等)的情况下,所有表必须使用 Innodb 存储引擎(MySQL5.5 之前默认使用 Myisam,5.6 以后默认的为 Innodb)。
Innodb 支持事务,支持行级锁,更好的恢复性,高并发下性能更好。
2. 数据库和表的字符集统一使用 UTF8
兼容性更好,统一字符集可以避免由于字符集转换产生的乱码,不同的字符集进行比较前需要进行转换会造成索引失效,如果数据库中有存储 emoji 表情的需要,字符集需要采用 utf8mb4 字符集。
参考文章:MySQL 字符集不一致导致索引失效的一个真实案例(opens new window)
3. 所有表和字段都需要添加注释
使用 comment 从句添加表和列的备注,从一开始就进行数据字典的维护
4. 尽量控制单表数据量的大小,建议控制在 500 万以内。
500 万并不是 MySQL 数据库的限制,过大会造成修改表结构,备份,恢复都会有很大的问题。
5. 谨慎使用 MySQL 分区表
分区表在物理上表现为多个文件,在逻辑上表现为一个表;
谨慎选择分区键,跨分区查询效率可能更低;
建议采用物理分表的方式管理大数据。
6.尽量做到冷热数据分离,减小表的宽度
MySQL 限制每个表最多存储 4096 列,并且每一行数据的大小不能超过 65535 字节。
减少磁盘 IO,保证热数据的内存缓存命中率(表越宽,把表装载进内存缓冲池时所占用的内存也就越大,也会消耗更多的 IO);
更有效的利用缓存,避免读入无用的冷数据;
经常一起使用的列放到一个表中(避免更多的关联操作)。
7. 禁止在表中建立预留字段
预留字段的命名很难做到见名识义。
预留字段无法确认存储的数据类型,所以无法选择合适的类型。
对预留字段类型的修改,会对表进行锁定。
8. 禁止在数据库中存储图片,文件等大的二进制数据
通常文件很大,会短时间内造成数据量快速增长,数据库进行数据库读取时,通常会进行大量的随机 IO 操作,文件很大时,IO 操作很耗时。
通常存储于文件服务器,数据库只存储文件地址信息
9. 禁止在线上做数据库压力测试
10. 禁止从开发环境,测试环境直接连接生产环境数据库
数据库字段设计规范
1. 优先选择符合存储需要的最小的数据类型
原因:
列的字段越大,建立索引时所需要的空间也就越大,这样一页中所能存储的索引节点的数量也就越少也越少,在遍历时所需要的 IO 次数也就越多,索引的性能也就越差。
方法:
a.将字符串转换成数字类型存储,如:将 IP 地址转换成整形数据
MySQL 提供了两个方法来处理 ip 地址
- inet_aton 把 ip 转为无符号整型 (4-8 位)
- inet_ntoa 把整型的 ip 转为地址
插入数据前,先用 inet_aton 把 ip 地址转为整型,可以节省空间,显示数据时,使用 inet_ntoa 把整型的 ip 地址转为地址显示即可。
b.对于非负型的数据 (如自增 ID,整型 IP) 来说,要优先使用无符号整型来存储
原因:
无符号相对于有符号可以多出一倍的存储空间
SIGNED INT -2147483648~2147483647
UNSIGNED INT 0~4294967295
VARCHAR(N) 中的 N 代表的是字符数,而不是字节数,使用 UTF8 存储 255 个汉字 Varchar(255)=765 个字节。过大的长度会消耗更多的内存。
varchar(20)
中的20
表示字符 ,一个汉字或是一个字母表示一个字符。
2. 避免使用 TEXT,BLOB 数据类型,最常见的 TEXT 类型可以存储 64k 的数据
3. 避免使用 ENUM 类型
4. 尽可能把所有列定义为 NOT NULL
原因:
索引 NULL 列需要额外的空间来保存,所以要占用更多的空间
进行比较和计算时要对 NULL 值做特别的处理
5. 使用 TIMESTAMP(4 个字节) 或 DATETIME 类型 (8 个字节) 存储时间
TIMESTAMP 存储的时间范围 1970-01-01 00:00:01 ~ 2038-01-19-03:14:07
TIMESTAMP 占用 4 字节和 INT 相同,但比 INT 可读性高
超出 TIMESTAMP 取值范围的使用 DATETIME 类型存储
经常会有人用字符串存储日期型的数据(不正确的做法)
- 缺点 1:无法用日期函数进行计算和比较
- 缺点 2:用字符串存储日期要占用更多的空间
6. 同财务相关的金额类数据必须使用 decimal 类型
- 非精准浮点:float,double
- 精准浮点:decimal
Decimal 类型为精准浮点数,在计算时不会丢失精度
占用空间由定义的宽度决定,每 4 个字节可以存储 9 位数字,并且小数点要占用一个字节
可用于存储比 bigint 更大的整型数据
索引设计规范
1. 限制每张表上的索引数量,建议单张表索引不超过 5 个
索引并不是越多越好!索引可以提高效率同样可以降低效率。
索引可以增加查询效率,但同样也会降低插入和更新的效率,甚至有些情况下会降低查询效率。
因为 MySQL 优化器在选择如何优化查询时,会根据统一信息,对每一个可以用到的索引来进行评估,以生成出一个最好的执行计划,如果同时有很多个索引都可以用于查询,就会增加 MySQL 优化器生成执行计划的时间,同样会降低查询性能。
2. 禁止给表中的每一列都建立单独的索引
5.6 版本之前,一个 sql 只能使用到一个表中的一个索引,5.6 以后,虽然有了合并索引的优化方式,但是还是远远没有使用一个==联合索引==的查询方式好。
3. 每个 Innodb 表必须有个主键
Innodb 是一种索引组织表:数据的存储的逻辑顺序和索引的顺序是相同的。每个表都可以有多个索引,但是表的存储顺序只能有一种。
Innodb 是按照主键索引的顺序来组织表的
- 不要使用更新频繁的列作为主键,不适用多列主键(相当于联合索引)
- 不要使用 UUID,MD5,HASH,字符串列作为主键(无法保证数据的顺序增长)
- 主键建议使用自增 ID 值
4. 常见索引列建议
- 出现在 SELECT、UPDATE、DELETE 语句的 WHERE 从句中的列
- 包含在 ORDER BY、GROUP BY、DISTINCT 中的字段
- 并不要将符合 1 和 2 中的字段的列都建立一个索引, 通常将 1、2 中的字段建立联合索引效果更好
- 多表 join 的关联列
5.如何选择索引列的顺序
建立索引的目的是:希望通过索引进行数据查找,减少随机 IO,增加查询性能 ,索引能过滤出越少的数据,则从磁盘中读入的数据也就越少。
- 区分度最高的放在联合索引的最左侧(区分度=列中不同值的数量/列的总行数)
- 尽量把字段长度小的列放在联合索引的最左侧(因为字段长度越小,一页能存储的数据量越大,IO 性能也就越好)
- 使用最频繁的列放到联合索引的左侧(这样可以比较少的建立一些索引)
6. 避免建立冗余索引和重复索引(增加了查询优化器生成执行计划的时间)
- 重复索引示例:primary key(id)、index(id)、unique index(id)
- 冗余索引示例:index(a,b,c)、index(a,b)、index(a)
7. 对于频繁的查询优先考虑使用覆盖索引
覆盖索引:就是包含了所有查询字段 (where,select,order by,group by 包含的字段) 的索引
覆盖索引的好处:
- 避免 Innodb 表进行索引的二次查询: Innodb 是以聚集索引的顺序来存储的,对于 Innodb 来说,二级索引在叶子节点中所保存的是行的主键信息,如果是用二级索引查询数据的话,在查找到相应的键值后,还要通过主键进行二次查询才能获取我们真实所需要的数据。而在覆盖索引中,二级索引的键值中可以获取所有的数据,避免了对主键的二次查询 ,减少了 IO 操作,提升了查询效率。
- 可以把随机 IO 变成顺序 IO 加快查询效率: 由于覆盖索引是按键值的顺序存储的,对于 IO 密集型的范围查找来说,对比随机从磁盘读取每一行的数据 IO 要少的多,因此利用覆盖索引在访问时也可以把磁盘的随机读取的 IO 转变成索引查找的顺序 IO。
8.索引 SET 规范
尽量避免使用外键约束
- 不建议使用外键约束(foreign key),但一定要在表与表之间的关联键上建立索引
- 外键可用于保证数据的参照完整性,但建议在业务端实现
- 外键会影响父表和子表的写操作从而降低性能
数据库 SQL 开发规范
1. 建议使用预编译语句进行数据库操作
预编译语句可以重复使用这些计划,减少 SQL 编译所需要的时间,还可以解决动态 SQL 所带来的 SQL 注入的问题。
只传参数,比传递 SQL 语句更高效。
相同语句可以一次解析,多次使用,提高处理效率。
预编译语句举例:
1. String sqlString ="insert into user(id, password, name, email, address) values(?, ?, ?, ?, ?)"; 2. PreparedStatement pstmt = **connection.PreparedStatement(sqlString);** 3. pstmt.setString(1, user.id); 4. pstmt.setString(2, user.**password);** 5. pstmt.setString(3, user.**name);** 6. pstmt.setString(4, user.email); 7. pstmt.setString(5, user.address);
2. 避免数据类型的隐式转换
隐式转换会导致索引失效如:
select name,phone from customer where id = '111';
int变字符串
3. 充分利用表上已经存在的索引
避免使用双%号的查询条件。如:a like '%123%'
,(如果无前置%,只有后置%,是可以用到列上的索引的)
一个 SQL 只能利用到复合索引中的一列进行范围查询。如:有 a,b,c 列的联合索引,在查询条件中有 a 列的范围查询,则在 b,c 列上的索引将不会被用到。
6. 禁止使用 SELECT * 必须使用 SELECT <字段列表> 查询
原因:
- 消耗更多的 CPU 和 IO 以网络带宽资源
- 无法使用覆盖索引
- 可减少表结构变更带来的影响
7. 禁止使用不含字段列表的 INSERT 语句
如:
insert into t values ('a','b','c');
应使用:
insert into t(c1,c2,c3) values ('a','b','c');
8. 避免使用子查询,可以把子查询优化为 join 操作
通常子查询在 in 子句中,且子查询中为简单 SQL(不包含 union、group by、order by、limit 从句) 时,才可以把子查询转化为关联查询进行优化。
子查询性能差的原因:
子查询的结果集无法使用索引,通常子查询的结果集会被存储到临时表中,不论是内存临时表还是磁盘临时表都不会存在索引,所以查询性能会受到一定的影响。特别是对于返回结果集比较大的子查询,其对查询性能的影响也就越大。
由于子查询会产生大量的临时表也没有索引,所以会消耗过多的 CPU 和 IO 资源,产生大量的慢查询。
9. 避免使用 JOIN 关联太多的表
对于 MySQL 来说,是存在关联缓存的,缓存的大小可以由 join_buffer_size 参数进行设置。
在 MySQL 中,对于同一个 SQL 多关联(join)一个表,就会多分配一个关联缓存,如果在一个 SQL 中关联的表越多,所占用的内存也就越大。
如果程序中大量的使用了多表关联的操作,同时 join_buffer_size 设置的也不合理的情况下,就容易造成服务器内存溢出的情况,就会影响到服务器数据库性能的稳定性。
同时对于关联操作来说,会产生临时表操作,影响查询效率,MySQL 最多允许关联 61 个表,建议不超过 5 个。
10. 减少同数据库的交互次数
数据库更适合处理批量操作,合并多个相同的操作到一起,可以提高处理效率。
11. 对应同一列进行 or 判断时,使用 in 代替 or
in 的值不要超过 500 个,in 操作可以更有效的利用索引,or 大多数情况下很少能利用到索引。
12. 禁止使用 order by rand() 进行随机排序
13. WHERE 从句中禁止对列进行函数转换和计算
对列进行函数转换或计算时会导致无法使用索引
不推荐:
where date(create_time)='20190101'
推荐:
where create_time >= '20190101' and create_time < '20190102'
14. 在明显不会有重复值时使用 UNION ALL 而不是 UNION
- UNION 会把两个结果集的所有数据放到临时表中后再进行去重操作
- UNION ALL 不会再对结果集进行去重操作
15. 拆分复杂的大 SQL 为多个小 SQL
- 大 SQL 逻辑上比较复杂,需要占用大量 CPU 进行计算的 SQL
- MySQL 中,一个 SQL 只能使用一个 CPU 进行计算
- SQL 拆分后可以通过并行执行来提高处理效率
数据库操作行为规范
1. 超 100 万行的批量写 (UPDATE,DELETE,INSERT) 操作,要分批多次进行操作
大批量操作可能会造成严重的主从延迟
主从环境中,大批量操作可能会造成严重的主从延迟,大批量的写操作一般都需要执行一定长的时间, 而只有当主库上执行完成后,才会在其他从库上执行,所以会造成主库与从库长时间的延迟情况
binlog 日志为 row 格式时会产生大量的日志
大批量写操作会产生大量日志,特别是对于 row 格式二进制数据而言,由于在 row 格式中会记录每一行数据的修改,我们一次修改的数据越多,产生的日志量也就会越多,日志的传输和恢复所需要的时间也就越长,这也是造成主从延迟的一个原因
避免产生大事务操作
大批量修改数据,一定是在一个事务中进行的,这就会造成表中大批量数据进行锁定,从而导致大量的阻塞,阻塞会对 MySQL 的性能产生非常大的影响。
特别是长时间的阻塞会占满所有数据库的可用连接,这会使生产环境中的其他应用无法连接到数据库,因此一定要注意大批量写操作要进行分批
3. 禁止为程序使用的账号赋予 super 权限
- 当达到最大连接数限制时,还运行 1 个有 super 权限的用户连接
- super 权限只能留给 DBA 处理问题的账号使用
4. 对于程序连接数据库账号,遵循权限最小原则
- 程序使用数据库账号只能在一个 DB 下使用,不准跨库
- 程序使用的账号原则上不准有 drop 权限
关于数据库中如何存储时间的一点思考
我们平时开发中不可避免的就是要存储时间,比如我们要记录操作表中这条记录的时间、记录转账的交易时间、记录出发时间等等。你会发现时间这个东西与我们开发的联系还是非常紧密的,用的好与不好会给我们的业务甚至功能带来很大的影响。所以,我们有必要重新出发,好好认识一下这个东西。
这是一篇短小精悍的文章,仔细阅读一定能学到不少东西!
1.切记不要用字符串存储日期
我记得我在大学的时候就这样干过,而且现在很多对数据库不太了解的新手也会这样干,可见,这种存储日期的方式的优点还是有的,就是简单直白,容易上手。
但是,这是不正确的做法,主要会有下面两个问题:
- 字符串占用的空间更大!
- 字符串存储的日期效率比较低(逐个字符进行比对),无法用日期相关的 API 进行计算和比较。
2.Datetime 和 Timestamp 之间抉择
Datetime 和 Timestamp 是 MySQL 提供的两种比较相似的保存时间的数据类型。他们两者究竟该如何选择呢?
通常我们都会首选 Timestamp。 下面说一下为什么这样做!
2.1 DateTime 类型没有时区信息
DateTime 类型是没有时区信息的(时区无关) ,DateTime 类型保存的时间都是当前会话所设置的时区对应的时间。这样就会有什么问题呢?当你的时区更换之后,比如你的服务器更换地址或者更换客户端连接时区设置的话,就会导致你从数据库中读出的时间错误。不要小看这个问题,很多系统就是因为这个问题闹出了很多笑话。
Timestamp 和时区有关。Timestamp 类型字段的值会随着服务器时区的变化而变化,自动换算成相应的时间,说简单点就是在不同时区,查询到同一个条记录此字段的值会不一样。
下面实际演示一下!
建表 SQL 语句:
CREATE TABLE `time_zone_test` (
`id` bigint(20) NOT NULL AUTO_INCREMENT,
`date_time` datetime DEFAULT NULL,
`time_stamp` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
插入数据:
INSERT INTO time_zone_test(date_time,time_stamp) VALUES(NOW(),NOW());
查看数据:
select date_time,time_stamp from time_zone_test;
结果:
+---------------------+---------------------+
| date_time | time_stamp |
+---------------------+---------------------+
| 2020-01-11 09:53:32 | 2020-01-11 09:53:32 |
+---------------------+---------------------+
现在我们运行
修改当前会话的时区:
set time_zone='+8:00';
再次查看数据:
+---------------------+---------------------+
| date_time | time_stamp |
+---------------------+---------------------+
| 2020-01-11 09:53:32 | 2020-01-11 17:53:32 |
+---------------------+---------------------+
扩展:一些关于 MySQL 时区设置的一个常用 sql 命令
# 查看当前会话时区
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';
2.2 DateTime 类型耗费空间更大
Timestamp 只需要使用 4 个字节的存储空间,但是 DateTime 需要耗费 8 个字节的存储空间。但是,这样同样造成了一个问题,Timestamp 表示的时间范围更小。
- DateTime :1000-01-01 00:00:00 ~ 9999-12-31 23:59:59
- Timestamp: 1970-01-01 00:00:01 ~ 2037-12-31 23:59:59
Timestamp 在不同版本的 MySQL 中有细微差别。
3 再看 MySQL 日期类型存储空间
下图是 MySQL 5.6 版本中日期类型所占的存储空间:
可以看出 5.6.4 之后的 MySQL 多出了一个需要 0 ~ 3 字节的小数位。DateTime 和 Timestamp 会有几种不同的存储空间占用。
为了方便,本文我们还是默认 Timestamp 只需要使用 4 个字节的存储空间,但是 DateTime 需要耗费 8 个字节的存储空间。
4.数值型时间戳是更好的选择吗?
很多时候,我们也会使用 int 或者 bigint 类型的数值也就是时间戳来表示时间。
这种存储方式的具有 Timestamp 类型的所具有一些优点,并且使用它的进行日期排序以及对比等操作的效率会更高,跨系统也很方便,毕竟只是存放的数值。缺点也很明显,就是数据的可读性太差了,你无法直观的看到具体时间。
时间戳的定义如下:
时间戳的定义是从一个基准时间开始算起,这个基准时间是「1970-1-1 00:00:00 +0:00」,从这个时间开始,用整数表示,以秒计时,随着时间的流逝这个时间整数不断增加。这样一来,我只需要一个数值,就可以完美地表示时间了,而且这个数值是一个绝对数值,即无论的身处地球的任何角落,这个表示时间的时间戳,都是一样的,生成的数值都是一样的,并且没有时区的概念,所以在系统的中时间的传输中,都不需要进行额外的转换了,只有在显示给用户的时候,才转换为字符串格式的本地时间。
数据库中实际操作:
mysql> select UNIX_TIMESTAMP('2020-01-11 09:53:32');
+---------------------------------------+
| UNIX_TIMESTAMP('2020-01-11 09:53:32') |
+---------------------------------------+
| 1578707612 |
+---------------------------------------+
1 row in set (0.00 sec)
mysql> select FROM_UNIXTIME(1578707612);
+---------------------------+
| FROM_UNIXTIME(1578707612) |
+---------------------------+
| 2020-01-11 09:53:32 |
+---------------------------+
1 row in set (0.01 sec)
5.总结
MySQL 中时间到底怎么存储才好?Datetime?Timestamp? 数值保存的时间戳?
好像并没有一个银弹,很多程序员会觉得数值型时间戳是真的好,效率又高还各种兼容,但是很多人又觉得它表现的不够直观。这里插一嘴,《高性能 MySQL 》这本神书的作者就是推荐 Timestamp,原因是数值表示时间不够直观。下面是原文:
每种方式都有各自的优势,根据实际场景才是王道。下面再对这三种方式做一个简单的对比,以供大家实际开发中选择正确的存放时间的数据类型:
707612 |
±--------------------------------------+
1 row in set (0.00 sec)
mysql> select FROM_UNIXTIME(1578707612);
±--------------------------+
| FROM_UNIXTIME(1578707612) |
±--------------------------+
| 2020-01-11 09:53:32 |
±--------------------------+
1 row in set (0.01 sec)
### 5.总结
MySQL 中时间到底怎么存储才好?Datetime?Timestamp? 数值保存的时间戳?
好像并没有一个银弹,很多程序员会觉得数值型时间戳是真的好,效率又高还各种兼容,但是很多人又觉得它表现的不够直观。这里插一嘴,《高性能 MySQL 》这本神书的作者就是推荐 Timestamp,原因是数值表示时间不够直观。下面是原文:
![在这里插入图片描述](https://img-blog.csdnimg.cn/e69823960cdc4cbebf8d4422401ebe96.png?x-oss-process=image/watermark,type_d3F5LXplbmhlaQ,shadow_50,text_Q1NETiBA5YaZaGVsbG8gd29ybGTpg73mnIlidWc=,size_20,color_FFFFFF,t_70,g_se,x_16#pic_center)
每种方式都有各自的优势,根据实际场景才是王道。下面再对这三种方式做一个简单的对比,以供大家实际开发中选择正确的存放时间的数据类型:
![在这里插入图片描述](https://img-blog.csdnimg.cn/6ff23ff1376849b982366f249533f0eb.png?x-oss-process=image/watermark,type_d3F5LXplbmhlaQ,shadow_50,text_Q1NETiBA5YaZaGVsbG8gd29ybGTpg73mnIlidWc=,size_20,color_FFFFFF,t_70,g_se,x_16)