mysql日志表索引设计_MySql设计规范及SQL索引优化【呕心之作】

数据库及表结构基本设计规范

1. 所有表必须使用Innodb存储引擎

没有特殊要求(即Innodb无法满足的功能如：列存储，存储空间数据等)的情况下，所有表必须使用Innodb存储引擎(mysql5.5之前默认使用Myisam，5.6以后默认的为Innodb)。Innodb 支持事务，支持行级锁，更好的恢复性，高并发下性能更好。

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

列的字段越大，建立索引时所需要的空间也就越大，索引的性能也越差。

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

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

进行比较和计算时要对NULL值做特别的处理

4. 使用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：用字符串存储日期要占用更多的空间

5. 同财务相关的金额类数据必须使用decimal类型

非精准浮点：float,double

精准浮点：decimal

Decimal类型为精准浮点数，在计算时不会丢失精度

占用空间由定义的宽度决定，每4个字节可以存储9位数字，并且小数点要占用一个字节

可用于存储比bigint更大的整型数据

6. 数据库和表的字符集统一使用UTF8

兼容性更好，统一字符集可以避免由于字符集转换产生的乱码；如果有存储emoji表情的需要，采用utf8mb4字符集。

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

500万并不是Mysql数据库的限制，过大会造成修改表结构，备份，恢复都会有很大的问题。

可以用历史数据归档(应用于日志数据)，分库分表(应用于业务数据)等手段来控制数据量大小

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

预留字段的命名很难做到见名识义。预留字段无法确认存储的数据类型，所以无法选择合适的类型。

对预留字段类型的修改，会对表进行锁定。

9. 禁止在数据库中存储图片，文件等大的二进制数据；通常文件存储于文件服务器，数据库只存储文件地址信息

10. 尽量避免使用外键约束(这个各有见解，不强制)

不建议使用外键约束(foreign key)，但一定要在表与表之间的关联键上建立索引

外键会影响父表和子表的写操作从而降低性能

SQL优化细节规范：

做MySQL优化，我们要善用EXPLAIN查看SQL执行计划。

type列，连接类型。一个好的SQL语句至少要达到range级别。杜绝出现all级别。

key列，使用到的索引名。如果没有选择索引，值是NULL。可以采取强制索引方式。

key_len列，索引长度。

rows列，扫描行数。该值是个预估值。

extra列，详细说明。注意，常见的不太友好的值，如下：Using filesort，Using temporary。

1、当只需要一条数据的时候，使用limit 1，为了使EXPLAIN中type列达到const类型

2.、禁止使用SELECT * 必须使用SELECT 查询；避免消耗更多的CPU和IO以网络带宽资源

3、 避免使用JOIN关联太多的表

4、 减少同数据库的交互次数

5、使用合理的分页方式以提高分页的效率

如 select id,name from product limit 800000, 20；  替换为 select id,name from product where id> 800000 limit 20；

6、不建议使用%前缀模糊查询

7、对应同一列进行or判断时，使用in代替or

in 的值不要超过500个，in 操作可以更有效的利用索引，or大多数情况下很少能利用到索引。很多时候使用union all或者是union(必要的时候)的方式来代替“or”会得到更好的效果。

8、尽量用union all代替union

9、 WHERE从句中禁止对列进行函数转换和计算

select user_id,user_project from user_base where age*2=36; 替换为 select user_id,user_project from user_base where age=36/2;

10、避免产生大事务操作

11、在明显不会有重复值时使用UNION ALL 而不是UNION

UNION 会把两个结果集的所有数据放到临时表中后再进行去重操作

UNION ALL 不会再对结果集进行去重操作

索引设计规范

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

索引并不是越多越好！索引可以提高效率同样可以降低效率。

索引过多会增加mysql优化器生成执行计划的时间，同样会降低查询性能。

2. 对于联合索引来说，要遵守最左前缀法则

字段长度小、区分度最高及使用最频繁的列放在联合索引的最左侧

3. 避免建立冗余索引和重复索引(增加了查询优化器生成执行计划的时间)

重复索引示例：primary key(id)、index(id)、unique index(id)

冗余索引示例：index(a,b,c)、index(a,b)、index(a)

4. 每个Innodb表必须有个主键

Innodb是按照主键索引的顺序来组织表的

不要使用UUID,MD5,HASH,字符串列作为主键(无法保证数据的顺序增长)

主键建议使用自增ID值

5.常见索引列建议

出现在SELECT、UPDATE、DELETE语句的WHERE从句中的列

包含在ORDER BY、GROUP BY、DISTINCT中的字段

并不要将符合1和2中的字段的列都建立一个索引， 通常将1、2中的字段建立联合索引效果更好

多表join的关联列

6、注意范围查询语句

对于联合索引来说，如果存在范围查询，比如between、>、

7、尽量使用inner join，避免left join：利用小表去驱动大表

参与联合查询的表至少为2张表，一般都存在大小之分。如果连接方式是inner join，在没有其他过滤条件的情况下MySQL会自动选择小表作为驱动表，但是left join在驱动表的选择上遵循的是左边驱动右边的原则，即left join左边的表名为驱动表。

8、区分in和exists、not in和not exists

关于not in和not exists，推荐使用not exists。  更多比较移步：

参考文章：