MySQL 使用规范及优化

Posted by icoding168 on 2020-03-15 21:13:57

分类: MySQL  

存储引擎统一使用Innodb

现在Innodb各方面都很完善,从5.5版本开始MySQL就将默认的存储引擎从MyISAM换成了InnoDB,它比MyISAM多了一些很重要的功能如事务、行锁、数据恢复等,因此不建议使用MyISAM

字符集统一使用UTF8mb4

现在都移动互联网时代了,emoji表情人人都会用,UTF8不支持emoji

表名和字段名都要添加注释

注释可以方便大家协作开发和维护。

禁止在数据库中存储文件

文件会占用大量存储空间,从而拖慢数据库的查询速度,数据库存储文件路径就行了,文件应该存储在专用的文件服务器上。

尽可能给字段加默认值以避免null

因为值为null的数据在按条件查询和排序这两个场景需要做一些额外的判断。

网上很多文章说null会导致索引失效,这其实并不对,MySQL官网文档明确指出可以用索引查询值为null的数据。另外,我看到网上很多文章说null会额外占用存储空间。其实准确来说是用了MyIsam存储引擎才会出现这种情况,如果用的是Innodb存储引擎,null并不会占用任何空间,关于这方面的资料可参考MySQL官网文档:

金额类数据使用decimal类型

floatdouble这两种数据类型存储较大的数字会损失精度,涉及到金额的或者要求精确计算的业务推荐使用decimal类型。

避免使用enum枚举类型

修改enum时需要使用alter语句重构整个表非常耗时。

enum类型的数据排序很麻烦且效率低。

优先选择占用空间小的数据类型

列的字段越大,建立索引时所需要的空间也就越大,能存储的索引节点的数量也就越少,索引的性能就会下降。因此要优先选择占用空间小的数据类型,比如整数类型尽量使用tinyint、smallint、mediumint而不是int,字符串类型尽量使用varchar不是text。对于非负的数据如自增id,可以使用unsigned ,因为无符号相对于有符号可以多出一倍的存储空间。

限制每张表的索引数量

创建索引之后,数据入库时还需要额外的时间去更新索引,索引是牺牲了插入数据和更新数据的性能去提升查询数据的性能。像性别、业务状态这类取值范围很小的字段就没必要加索引,一般来说这一类字段往往一个状态值就可以匹配到很多数据,MySQL遇到这种情况时会用全表扫描代替索引,因为这时候全表扫描的查询速度比索引更快。关于这方面的资料可参考MySQL官网文档:

创建联合索引时应考虑列的顺序

在两个或多个列上创建的索引叫联合索引,联合索引遵循最左前缀匹配原则。例如某联合索引有a,b,c三列,MySQL可以支持按 “a”,“a,b”,“a,b,c” 3种组合进行查找,但不支持按 “b,c” 进行查找。所以创建联合索引的时候应该考虑列的顺序,但SQL语句里面的查询条件不需要考虑顺序。关于这方面的资料可参考MySQL官网文档:

不要在索引列上做计算

在索引列上做计算无法利用索引提升查询速度,例如select id where age + 1 = 10

避免数据类型的隐式转换

隐式转换会导致索引失效,比如idint类型,但id作为查询条件时的值却是字符串类型:

select name,phone from customer where id = '111';

避免用like做全文搜索

以通配符结尾的like查询可以利用索引,但是以通配符开头的like查询,如select * from user where username like '%admin%',无法利用索引,只能做全表扫描,如果数据量大,推荐使用全文搜索引擎。

避免使用select *

select *会把整个表的字段都查出来返回给客户端,需要更多的网络传输时间,特别是某些字段占用存储空间比较大的时候。

使用预编译语句

预编译语句在编译之后只需要传参数,比传输整个SQL语句更高效,还可以避免SQL注入的问题。

合并exists子查询语句

对于某些使用了exists子查询语句的查询条件,能合并就合并,减少查询次数。

使用limit提升查询速度

一些子查询可以用limit提前降低数据规模,以提升查询速度。

拆分复杂语句

SQL语句应尽可能保持简单,对于很复杂的查询应该进行拆分。一条SQL搞不定就用两条,不要有一条SQL搞定所有查询的想法。如果某个功能模块需要查询的表比较多,可以考虑开多几条线程执行不同的SQL语句,压榨MySQL的性能。

开启慢查询日志功能

开启慢查询日志功能让MySQL记录那些执行速度较慢的SQL语句,帮助我们分析定位问题所在。具体如何配置参考官网文档:

不要使用MySQL的查询缓存

MySQL会监测使用了查询缓存的表,只要表的结构或者数据被修改,例如对某个表使用了insert、 update、delete、truncate table、alter table等语句,这个表相关的缓存都会失效。虽然查询缓存可以提升系统性能,但是MySQL是以SQL语句 为key来做缓存的,即使SQL语句功能相同,然而只要多了一个空格或者大小写有差异都会导致匹配不到缓存。因此MySQL的查询缓存命中率低不够智能,让使用者维护起来费心费力得不偿失,从8.0版本开始MySQL的开发者就已经删除了这个功能。

谨慎使用分区表

分区表在物理上表现为多个文件,在逻辑上表现为一个表。

分区表的分区键设计不太灵活,如果不走分区键,很容易出现全表锁。一旦数据量并发量上来,如果在分区表实施关联,就是一个灾难。自己分库分表,自己掌控业务场景与访问模式,更加可控。

大表优化方案

一般按以下步骤来优化MySQL,成本由低到高

  1. 优化查询SQL和索引
  2. Redis做数据缓存
  3. 做读写分离
  4. 做垂直拆分,将一个大的系统分为多个小的系统,比如将后台管理模块跟业务逻辑模块的表拆分成两个库
  5. 做水平切分,这一步最麻烦。水平拆分是通过某种策略将数据分片来存储,有库内分表和分库两部分,每片数据会分散到不同的MySQL表或库,达到分布式的效果,能够支持非常大的数据量。MySQL自带的分区表也是一种库内分表,库内分表仅仅是解决了单一表数据过大的问题,由于没有把表的数据分布到不同的机器上,因此对于减轻MySQL服务器的压力来说,并没有太大的作用,大家还是竞争同一个物理机上的IO、CPU、网络,这个就要通过分库来解决。分库分表是解决数据库瓶颈的葵花宝典,9成业务都是读多写少,所以应该优先考虑优化索引,优化表结构,缓存,读写分离这些。