高性能mysql实战 笔记（一）

模拟电商数据库设计及架构优化实战（只设计数据库设计，不包括前后端）

项目说明（模拟电商项目）：

数据库设计规范的指定： 

 

数据库命名规范：

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

二  所有数据库对象名称禁止使用mysql保留关键字（如表名为from）

三  数据库对象的命名要见名识义，并且最好不要超过32个字符

例： 用户数据库表 mc_userdb  用户账号表： user_account

四  临时表必须以tmp_为前缀并以日期为后缀（便于清理） 备份库，备份表必须以bak_为前缀日期为后缀

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

 

数据库基本设计规范:

一  所有表必须使用Innodb存储引擎（如果不是，尽快统一）

原因：5.6以后的默认引擎，支持事务，行级锁，更好的恢复性，高并发下性能更好

二  数据库和表的字符集统一使用UTF8（表的字符集统一可以防止乱码）

三  所有的表和字段都要添加注释   使用comment 从句添加表和列的备注，（目的：维护数据字典)

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

五  谨慎使用mysql分区表

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

七  禁止在表中建立预留字段

八  禁止在数据库中存储图片，文件等二进制数据（应该存路径）

九 禁止在线上做数据库压力测试（应该在压力测试环境下测试）

十  禁止从开发环境，测试环境直接连生产环境

 

数据库索引设计规范

索引对数据库的查询性能来说是非常重要的  ，不要滥用索引

一 限制每张表上的索引数量，建议单张表索引不超过5个（索引不是越多越好，禁止给表中的每一列都建立单独的索引）

二 每个Innodb表必须有一个主键，建议：不使用更新频繁的列作为主键，不使用多列主键，不使用UUID，MD5，HASH，字符串列作为主键，主键建议选择使用自增id值

三 常见索引列建议：

select,update,delete语句的where从句中的列

包含在order by，group by，distinct中的字段

四 如何选择索引多个列的顺序：

区分度最高的列放在联合索引的最左侧，

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

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

五 避免建立冗余索引和重复索引

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

七  尽量避免使用外键约束

 

数据库字段设计规范：

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

将字符串转化为数字类型存储 INET_ATON('255.255.255.255'） = 4294967295

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

1  对于非负数据采用无符号整形进行存储

2  varchar（N） N 代表的是字符数 不是字节数

3  使用utf8存储汉字varchar(255) = 765个字节

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

三  避免使用TEXT，BLOB数据类型：

建议把BLOB或是TEXT列分离到单独的扩展表中

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

四  避免使用ENUM数据类型：

修改ENUM值需要使用alter语句

ENUM类型的order by操作效率低，需要额外的操作

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

五  尽可能把所有列定用为NOT NULL：

索引null列需要额外的空间来保存，所以要占用更多的空间

进行比较和计算时要对null值比较

六 使用TIMESTAMP或DATETIME类型存储时间  需要的字节更少

七  同财务相关的金额类数据，必须使用decimal类型，不丢失精度

 

数据库SQL开发规范

一 建议使用预编译语句进行数据库操作 （prepare）

二 避免数据类型的隐式转换：

隐式转换会导致索引失效  select * from test where id = '111'

三  充分利用表上已经存在的索引：

避免使用双%号的查询条件  a like %123%

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

使用left join 或 not exists 来优化not in 操作

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

为数据库迁移和分库分表流出余地

降低业务耦合

五  禁止使用select * 必须使用select + 字段 查询：

消耗更多cpu和io以及网络带宽资源

无法使用覆盖索引

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

六 禁止使用不含字段列表的insert语句：

例：insert into t values ('a','b','c')  

七  避免使用子查询，可以把子查询优化为join操作：

子查询的结果集无法使用索引

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

消耗过多的cpu和io操作

八  避免使用join关联太多的表：

每join一个表会多占用一部分内存

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

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

九  减少同数据库的交互次数：

数据库更适合处理批量操作

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

十  使用in 代替 or ：

in 的值不要超过500个

in 操作可以有效的利用索引

十一  禁止使用order by rand() 进行随机排序

会把表中所有符合条件的数据装载到内存中进行排序

会消耗大量的cpu和io及内存资源

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

十二 where从句中禁止对列进行函数转换和计算

对列进行函数转换或计算会导致无法使用索引

十三 在明显不会有重复值时使用union all 而不是union 

union会把所有数据放到临时表中后再进行去重操作

union all 不会再对结果集进行去重操作

十四 拆分复杂的大sql为多个小sql

 

未完待续。。。