mysql数据库优化学习--day01

一：数据设计规范

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

select id,username,from,age from tb_user   

属性中有from--表示来源，但是数据库中不会识别，此处应该采用加双引号 ----'from'

2.数据库对象的命名要见名知意，最好不要超过32个字符。

3.临时表必须以tmp为前缀并且以日期为后缀。

4.备份库，备份表必须以bak为前缀并以日期为后缀。

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

6.所有表必须使用Innodb存储引擎-mysql 5.6以后的默认引擎（支持事务，行级锁，更好的恢复性，高并发下性能更好！）

7.数据库和表的字符集统一使用UTF-8

8.所有的表和字段都要添加注释

9.尽量控制单表的数据量的大小，建议控制在500W以内

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

11.禁止在表中建立预留字段。

12.静止在数据库中存储图片，文件等二进制的数据

二：索引设计规范

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

2.Innodb是按照主键索引的顺序来组织表的。-----每个Innodb表必须有一个主键--不使用UUID,MD5,HASH字符串列作为主键

3.常见的建立索引的地方：

SELECT UPDATE DELETE 语句的WHERE从句中的列--包含ORDERY BY  GROUP BY  DISTINCT中的字段。 多表JOIN的关联列

4.索引列的顺序：从左到右的顺序来使用的。

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

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

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

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

6.对于频繁的查询优先考虑使用覆盖索引  -- 覆盖索引：就是包含了所有查询字段的缩影

7.尽量避免使用外键约束--不建议使用外键约束，但是一定要在表与表之间的关联键上建立索引

三：数据库字段设计规范

1.字段类型的选择，会直接影响数据库的性能

2.优先选择符合存储需要的最小的数据类型--将字符串转化为数字类型存储

3.对于非负型的数据来说，要优先使用无符号整型来存储--无符号相对于有符号可以多出一倍的存储空间。

4.VARCHAR(N)中的N表示的是字节数，而不是字节数。 使用UFT8存储汉字Varchar（255）=765个字节

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

6.避免使用TEXT BLOB数据类型，避免使用ENUM数据类型

修改ENUM值需要使用alert语句、ENUM类型的ORDER BY操作效率低，需要额外操作。

7.尽可能把所有列定义为NOT NULL

8.字符串存储日期型的数据（不正确的做法）---使用TIMESTAMP或者DATETIME类型存储时间。

无法使用日期函数进行计算和比较

用字符串存储日期要占用更多的空间

9.同财务相关的金额类数据，必修使用decimal类型--decimal类型为精准浮点数，在计算时不会丢失精度

四：数据库SQL开发规范

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

2.避免数据类型的隐式转换--会导致索引失效

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

--避免使用双%号的查询条件。如 like ‘%123%’

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

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

4.禁止使用SELECT * 必须使用SELECT <字段列表> 查询

5.禁止使用不含字段列表的INSERT语句

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

7.避免使用JOIN关联太多的表--每个Join一个表多占用一部分内存

8.使用in代替or

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

9.禁止使用order by rand（）进行随机排序

10.WHERE从句中禁止对列进行函数转换和计算--导致无法使用索引

11.拆分复杂的大SQL为多个小SQL

五：数据库操作行为规范

1.超过100W行的批量写操作，要分批次进行操作

2.大批量操作可能会造成严重的主从延迟，避免产生大事物操作。

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

4.当达到最大连接数限制的时候，只允许一个有super权限的用户连接。

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

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

7.程序使用数据库账号只能在一个DB下使用，不能跨库。

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