玩技术,爱生活
记录工作与生活的点点滴滴

每个开发者都应该了解的MySQL开发常识

每一个开发者都应该了解的Mysql基本常识:用好字段、建好索引、优化SQL。

写在前面的话:

总是在灾难发生后,才想起容灾的重要性;
总是在吃过亏后,才记得曾经有人提醒过。

一、核心规则

  1. 不在数据库做运算:cpu计算务必移至业务层
  2. 控制单表数据量:单表记录控制在1000w
  3. 控制列数量:字段数控制在20以内
  4. 平衡范式与冗余:为提高效率牺牲范式设计,冗余数据
  5. 拒绝3B:拒绝大sql,大事物,大批量

二、用好字段

1. 用好数值类型

  • TINYINT(1Byte):-128 ~ 127
  • SMALLINT(2Byte):-32768 ~ 32767(-2^16 ~ 2^16-1)
  • MEDIUMINT(3Byte):-8388608 ~ 8388607(-2^24 ~ 2^24-1)
  • INT(4Byte):-2147483648 ~ 2147483647(-2^32 ~ 2^32-1) 10位
  • BIGINT(8Byte):-9223372036854775808 ~ -9223372036854775807(-2^64 ~ 2^64-1)19位

注意:1Byte(字节) = 8bit(位)

所以:INT(1)使用是不合适的,应该为TINYINTINT(11)是错误的,因为INT最大只能表示10位,应该使用BIGINT(11)

2. 字符转化为数字

如果一个字符串全是数字,优先使用数值类型,而不是字符类型,比如:用INT而不是CHAR(15)来存储IP。

3. 优先使用ENUM或SET

对于枚举类型的字段,比如性别,开关等,建议使用ENUM或者SETENUM的内部存储机制是采用TINYINTSMALLINT(并非CHAR/VARCHAR),性能一点都不差,记住千万别用CHAR/VARCHAR 来存储枚举数据。例如:

`sex` enum (‘F’, ‘M’)

4. 避免使用NULL字段

在设计过程中,应该避免使用NULL字段,因为:

NULL字段很难查询优化 NULL字段的索引需要额外空间 NULL字段的复合索引无效

所有字段定义中,默认都加上NOT NULL约束,除非必须为NULL(但我也想不出来什么场景下必须要在数据库中存储NULL值,可以用0来表示)。在对该字段进行COUNT()统计时,统计结果更准确(值为NULL的不会被COUNT统计进去),或者执行 WHERE column IS NULL 检索时,也可以快速返回结果。

错误的示例:

`name` char(32) default null
`age` int not null

正确的用法应该是这样:

`name` char(32) not null default ''
`age` int not null default 0

5. 少用text/blob

应该尽量避免使用TEXT/BLOB字段,可以使用VARCHAR来代替,VARCHAR的性能会比TEXT高很多如果实在避免不了使用BLOB字段请拆表,如果使用了TEXT字段,请不要使用SELECT * FROM这种方式来查询,应该尽量指明使用的字段。

6. 不在数据库里存图片

这个不需要解释。

三、建好索引

1.谨慎合理使用索引

请记住索引会:改善查询、减慢更新 索引一定不是越多越好(能不加就不加,要加的一定得加) 覆盖记录条数过多不适合建索引,例如“性别”

2. 字符字段必须建前缀索引

这一规则适用于字段比较长的字符,如果字符串的长度比较短,且查询时匹配的字符长度跟字符串本身长度差不多,直接建立索引即可。

3. 不在索引做列运算

bad case: select id where age +1 = 10;

4.innodb主键推荐使用自增列

  • 主键建立聚簇索引
  • 主键不应该被修改
  • 字符串不应该做主键

如果不指定主键,innodb会使用唯一且非空值索引代替

注意:这一条有争议,我们可能会见到使用UUID作为主键的表,还比如,有些表的主键为了区分机房或者服务器,都会在产生的数值或字符串加前缀或者后缀,这样的主键也是字符串。实际情况中,其实很多主键都是使用字符串,所以这一原则请根据实际情况使用。

5. 不用外键

请由程序保证约束

6.尽量选择区分度高的列作为索引

区分度的公式是:count(distinct [col])/count(*) 表示字段不重复的比例,比例越大我们扫描的记录数越少,唯一键的区分度是1,而一些状态、性别字段可能在大数据面前区分度就是0,那可能有人会问,这个比例有什么经验值吗?使用场景不同,这个值也很难确定,一般需要join的字段我们都要求是0.1以上,即平均1条扫描10条记录

7.尽量的扩展索引,而不是创建新的索引

比如表中已经有a的索引,现在要加(a,b)的索引,那么只需要修改原来的索引即可

四、优化SQL

1.sql语句尽可能简单

  • 一条sql只能在一个cpu运算
  • 大语句拆小语句,减少锁时间
  • 一条大sql可以堵死整个库

2.简单的事务

事务时间尽可能短,请不要在事物中处理过多复杂的业务逻辑。

3.避免使用TRIG/FUNC

尽量不要使用触发器和函数,应该在客户端程序中处理相关逻辑。

4.不用SELECT *

这个需要解释否?

5.OR改写为IN()

  • OR的效率是n级别
  • IN的消息时log(n)级别

in的个数建议控制在200以内,特别是代码中拼接SQL时,如果in的个数拼接过多,建议分批次查询。

select id from t where phone=’159′ or phone=’136′;
可改为:
select id from t where phone in (’159′, ’136′);

6.limit高效分页

limit越大,效率越低

select id from t limit 10000, 10;
改为:
select id from t where id > 10000 limit 10;

7.其他注意规则

  • 避免负向%
  • 慎用count(*)
  • 使用union all替代union,union有去重开销
  • 少用连接join
  • 使用group by分组时自动排序
  • 请使用同类型比较
  • 使用load data导数据(load data比insert快约20倍)
  • 打散批量更新

8.性能分析工具

  • show profile
  • mysqlsla
  • mysqldumpslow
  • explain
  • show slow log
  • show processlist
  • show query_response_time(percona)

说明:
1. 根据赶集MYSQL军规整理,内容略有增减
2. 规则是死的,人是活的,请根据实际情况,酌情使用

本站文章除注明转载外,均为原创文章。 Creative Commons LicenseMOONDEV创作,采用知识共享署名-非商业性使用-相同方式共享 2.5 中国大陆许可协议进行许可。
分享到:更多 ()

评论 抢沙发

  • 昵称 (必填)
  • 邮箱 (必填)
  • 网址