mysql 开发规范

来源：互联网发布：英语口语书籍推荐知乎编辑：程序博客网时间：2024/06/15 05:17

索引规范
1. 尽量使用复合索引避免重复索引
2. 尽量选择区分度高的字段作为索引

sql规范
1.尽量避免不用*,select 使用具体的列名

表结构如下
id varchar(32) NOT NULL,
title varchar(255) DEFAULT NULL ,
content longtext ,
accessory_name varchar(255) DEFAULT NULL ,
accessory_id varchar(32) DEFAULT NULL ,
mail_type varchar(255) DEFAULT NULL ,
mail_level varchar(255) DEFAULT NULL ,
create_by varchar(32) DEFAULT NULL,
create_date datetime DEFAULT NULL,
update_by varchar(32) DEFAULT NULL,
update_date datetime DEFAULT NULL,
del_flag varchar(1) DEFAULT NULL,
is_send varchar(1) DEFAULT NULL,
admin_type varchar(255) DEFAULT NULL,
send_time datetime DEFAULT NULL ,
admin_target_id varchar(333) DEFAULT NULL,
is_revoke varchar(10) DEFAULT NULL,
json_data longtext,
PRIMARY KEY (id)

50w数据测试5次结果:

select * from tablename 所用秒数 3.23,3.22,3.20,3.18,3.20

select id from tablename 使用聚族索引所用秒数 0.24,0.19,0.19,0.18,0.20

select content from tablename 非索引字段类型为longtext 全表 2.17,2.25,2.10,1.99,1.92

select id from tablename 查询类型都是varchar(255) 非索引
1. select title from tablename 所用秒数 0.64,0.69,0.71,0.75,0.69
2. select title,accessory_name from tablename 所用秒数0.68,0.70,0.73,0.66,0.76
3.select title,accessory_name,mail_type from tablename 所用秒数0.76,0.75,0.84,0.76,0.81
以上结果证明再不用索引的情况下查询的字段越多, 字段的长度越大,查询速度会越慢

2.查询时应尽量避免全表扫描，首先应考虑在 where 及 order by 涉及的列上建立索引

select * from tablename where title =’(无主题)’ order by create_by where 不加索引 orderby 不加索引类型为全表扫描所用秒数 4.89,4.76,4.47,4.51,4.52

select * from tablename where title =’(无主题)’ order by create_by where 加索引 orderby 不加索引使用mysql icp机制所用秒数 6.27,6.13,6.14,6.13,6.10

select * from tablename where title =’(无主题)’ order by create_by where 加索引 orderby 加索引 index查询所用秒数 3.80,3.75,3.70,3.62,3.62

如果where 和order by 都加了索引却还是使用全表扫描或者出现filesort 可能是以下问题
1. 检查的行数过多，且没有使用覆盖索引, 因为select中的字段不在索引中
例: select * from tablename where title =’(无主题)’ order by create_by
2. 使用了不同的索引，order by出现二个索引也不会走索引
例: select create_by from tablename where title =’(无主题)’ order by create_by, mail_type(title和 create_by建立两个索引)
3. 当 orderby 语句中出现了 asc 和desc 还是会出现 Using filesort
例: select create_by from tablename where title =’(无主题)’ order by create_by asc,id desc
4. 当where 等号前的字段使用了表达式,和 orderby 语句中也使用了表达式也不会走索引
5. 当where 语句与ORDER BY语句组合满足最左前缀但where语句中使用了条件查询索引走了 create_data 也会出现 filesort ，orderby 的索引应该在最前
例: select * from tablename where create_date >’2017-06-12’ order by create_by
6. order by子句中加入了非索引列,且非索引列不在where子句中。出现 filesort 全表扫描
例: select create_by from tablename where mail_type=’ ’ order by create_by,mail_type
7. 当使用left join，使用非驱动表来排序依然会使用filesort排序

3.尽量避免在where句中使用is null 或者is not null,会不走索引
4.in和not in 最好也是尽量避免使用使用 exists 替代

例: select * from sys_mail where EXISTS (select id from sys_mail where mail_type = ‘mail_type_private’)
5次所用时间 3.60,3.47,3.43,3.36,3.40
select * from sys_mail where id in (select id from sys_mail where mail_type = ‘mail_type_private’
5次所用时间 5.58,5.44,5.24,5.50,5.27

5.尽量避免使用前置%
6.子查询尽量使用join代替
7.分页的偏移量如果过大,可先取id,然后用主键id关联
8.禁止使用order by rand()
9.尽量避免使用反向查询如 not in，而会进行全表扫描
10.尽量避免where字句中使用or，如果or的字段有一个不是索引会引起全表扫描，最好使用in 或者union替代
11. order by 优化方式

1. 给order by 字段增加索引2. 去掉不必要的返回字段3. 增大 sort_buffer_size 参数设置orderby的原理由于没有可以利用的有序索引取得有序的数据,MySQL需要通过相应的排序算法,将取得的数据在sort_buffer_size系统变量所设置大小的排序区进行排序,这个排序区是每个Thread 独享的,所以说可能在同一时刻在 MySQL 中可能存在多个 sort buffer 内存区域。在MySQL中filesort 的实现算法有两种:1) 双路排序:是首先根据相应的条件取出相应的排序字段和可以直接定位行数据的行指针信息，然后在sort buffer 中进行排序。2) 单路排序:是一次性取出满足条件行的所有字段，然后在sort buffer中进行排序。

12.join优化

1. 使用EXPLAIN 查看 位于第一行的为驱动表 其他为被驱动表， 如果搞不清join 可以让mysql 自己判断使用非join 方式2. 驱动表可以直接排序，非驱动表排序则需要通过回表然后进行排序，但是就会使用到临时表操作，会降低性能3. join 优化目标是尽可能减少JOIN中Nested Loop的循环次数，保证用小结果集驱动大结果集

13. 应尽量避免在 where 子句中的=左边进行函数、算术运算或其它表达运算
14. sum(),min(),max() 优化考虑建立组合索引
15. 避免冗余索引和重复索引
16. 经常修改的数据最好使用较短的主键id
17. 避免查询生成临时表
explain 看到Using temporary 就是用到了临时表
原因：sql执行会生成一个巨大的临时表，当内存放不下时，要全部copy 到磁盘，导致IO飙升，时间开销增大。
使用临时表的场景
1）ORDER BY子句和GROUP BY子句不同，例如：ORDERY BY price GROUP BY name；
2）在JOIN查询中，ORDER BY或者GROUP BY使用了不是第一个表的列例如：SELECT * from TableA, TableB ORDER BY TableA.price GROUP by TableB.name
3）ORDER BY中使用了DISTINCT关键字 ORDERY BY DISTINCT(price)
4）SELECT语句中指定了SQL_SMALL_RESULT关键字 SQL_SMALL_RESULT的意思就是告诉MySQL，结果会很小，请直接使用内存临时表，不需要使用索引排序 SQL_SMALL_RESULT必须和GROUP BY、DISTINCT或DISTINCTROW一起使用一般情况下，我们没有必要使用这个选项，让MySQL服务器选择即可。
直接使用磁盘临时表的场景
1）表包含TEXT或者BLOB列；
2）GROUP BY 或者 DISTINCT 子句中包含长度大于512字节的列；
3）使用UNION或者UNION ALL时，SELECT子句中包含大于512字节的列；
18. 清理mysql磁盘碎片,当数据太大时请每周或每月使用 optimize table tablename 语句重新组织表并压缩浪费的空间
19. 如果出现 join buffer 给关联字段增加索引
20. Using index condition 代表使用了mysql 的 index_condition_pushdown （ICP）可以减少存储引擎访问基表的次数和mysql server访问存储引擎的次数。但不一定会提高性能所以具体场景具体分析
ICP的优化策略可用于range、ref、eq_ref、ref_or_null 类型的访问数据方法；
21. gourpby 优化

1. 松散索引扫描                                                                                                                                                                                                                                                                            当查询中没有where条件的时候，松散索引扫描读取的索引元组的个数和groups的数量相同，如果where条件包含范围预测，松散索引扫描查找每个group中第一个满足范围条件，然后再读取最少可能数的keys。松散索引扫描只需要读取很少量的数据就可以完成group by操作，因而执行效率非常高.使用松散索引扫描需要满足以下条件：1）查询在单一表上。2）group by指定的所有列是索引的一个最左前缀，并且没有其它的列。比如表t1（ c1,c2,c3,c4）上建立了索引（c1,c2,c3）。如果查询包含“group by c1,c2”，那么可以使用松散索引扫描。但是“group by c2,c3”(不是索引最左前缀)和“group by c1,c2,c4”(c4字段不在索引中)无法使用。3）如果在选择列表select list中存在聚集函数，只能使用min()和max()两个聚集函数，并且指定的是同一列（如果min()和max()同时存在），这一列必须在索引中，且紧跟着group by指定的列。比如，select t1,t2,min(t3),max(t3) from t1 group by c1,c2。4）如果查询中存在除了group by指定的列之外的索引其他部分，那么必须以常量的形式出现（除了min()和max()两个聚集函数）。比如，select c1,c3 from t1 group by c1,c2不能使用松散索引扫描。而select c1,c3 from t1 where c3 = 3 group by c1,c2可以使用松散索引扫描。5）索引中的列必须索引整个数据列的值(full column values must be indexed)，而不是一个前缀索引。比如，c1 varchar(20), INDEX (c1(10))，这个索引没发用作松散索引扫描。2. 紧凑索引扫描                                                                                                                                                                                              紧凑索引扫描实现GROUP BY 和松散索引扫描的区别主要在于他需要在扫描索引的时候，读取所有满足条件的索引键，然后再根据读取的数据来完成GROUP BY 操作得到相应结果。使用紧凑索引扫描需要满足以下条件：在查询中存在常量相等的where条件字段（索引中的字段），且该字段在group by指定的字段的前面或者中间

命名规范
1. 命名不超过32个字符
2. 库名避免使用db_ 开头,用业务名称命名
3. 表名禁止使用驼峰,用业务名称命名
4. 列名避免db关键字
5. 索引使用缩写按照顺序来命名

表设计规范
1. 只使用innodb 字符编码使用utf-8mb4
2. 表必须带有物理主键,主键最好使用bigint类型
3. 禁止使用联合主键
4. 不在数据库存储图片文件大文件

列设计规范
1. 尽量减少存储空间
2. 禁用text和blob
3. 禁止使用null值建议都加上默认值

    1. null值需要额外空间    2. null查询很难优化    3. null字段复合索引无效

4. 禁止使用外键
5. varchar的长度尽可能小

阅读全文

0 0