MYSQL开发规范

Q15.数据库容量系数打分怎么计算的？

容量系数factor计算方法:  factor = 1 / 负载值;  if 80% <= IO利用率 < 90%: factor = factor / 1.2;  else if 90% < IO利用率:  factor = factor / 1.5
容量系数=10为最高分20。太大或太小均扣分，每增加1，减少1分；每降低1，减少2分，减到0为止。

比如：dpstar 库的容量系数昨天是33.33 ，负载太低，所以容量系数0分。
（容量系数和load参数有关，取库所在集群所有机器的最大值，见：http://dom.dp/db_daily/db?create_date=2016-12-28&cur_bu=%E5%B9%B3%E5%8F%B0%E4%BA%8B%E4%B8%9A%E7%BE%A4）

 

 

MYSQL最佳实践：

 

 

1. 命名
   
   (1) 表名：必须使用小写字母，多词之间以”_“（下划线）分隔，表名非复数名词，禁止使用mysql保留字。库名、表名、字段名禁⽌超过32个字符。
   (2) 字段名：必须使用小写字母，多词之间以”_“（下划线）分隔，字段名使用名词，禁止使用mysql保留字。
   (3) 索引名：普通索引以”idx_“ 加上字段名命名，唯一索引以”uk_“加上字段名命名，组合索引的命名须按顺序包含索引中的所有字段。如：”a，b“的组合索引，命名为”idx_a_b“，名称过长可适当采用缩写。
   (4)临时库、表名必须以tmp为前缀，并以⽇期为后缀。(5)临时库、表名必须以tmp为前缀，并以⽇期为后缀。备份库、表必须以bak为前缀，并以日期为后缀。
2. 字段规范

(1) 拆分大字段和访问频率低的字段，分离冷热数据。

(2) 按日期时间分表需符合YYYY[MM][DD][HH]格式。

(3) 建议不使用TEXT、BLOB类型，如果不可避免，则将TEXT、BLOB类型字段拆到单独表中。 代码实现 提示功能

(4) 所有字段均定义为NOT NULL。 代码实现

(5) 使用UNSIGNED存储非负整数（存储的范围更大）。

(6) 使用timestamp存储时间（占用空间更小，4字节，Datetime是8个字节）。 

(7) 使用INT UNSIGNED存储IPV4。

(8) 使用VARBINARY存储大小写敏感的变长字符串。

(9) 不允许使用ENUM （插入非法值的时候，默认会插入一个空值）。 代码实现

(10) 建议避免使用NULL字段。NULL字段很难查询优化，NULL字段的索引需要额外空间，NULL字段的复合索引无效。 代码实现

(11) 建议添加AddTime和UpdateTime 字段，UpdateTime建议设置为：DEFAULT NOW() ON UPDATE CURRENT_TIMESTAMP，并且要有索引。 代码实现 提示

 

1. 表设计
   
   (1) 使用UNSIGNED存储非负数值。TINYINT UNSIGNED可存储的范围是0——255，TINYINT可存储的范围是-128——127。
   (2) 尽量使用正确存储数据的最小数据类型。例：如果只需要存0——200，TINYINT UNSIGNED更好。
   (3) VARCHAR类型用于存储可变长字符串，优先选择。CHAR适合存储很短的字符串，例如CHAR(1)，或所有值都接近一个长度的字符串，例如：MD5值。存储ip最好用int存储而非char(15)，通过MySQL函数inet_ntoa和inet_aton来进行转化。Ipv6地址目前没有转化函数，需要使用DECIMAL或者两个bigINT来存储。
   (4) VARCHAR类型也尽量设置正确存储数据的最小长度。
   (5) 强烈不建议使用mysql存储过大字段，如过大的VARCHAR，CHAR，TEXT，BLOB等订单主表，如：hotel_apt，hotel_zl_order禁止使用大字段。
   (6) 订单主表的字段尽量精简，只存必要的字段，强烈不建议存大字段，如comment，强烈不建议存变更非常频繁的字段，如deal表的deal购买数量，强烈不建议存不经常使用的字段，如comment。
   (7) 建议适当增加冗余字段以避免表关联查询，但冗余字段变更不要太频繁。
   (8) 尽量将字段设置为not null。
   (9) 表名、列名必须有注释。
   (10) 强烈建议使用innodb存储引擎。 
   (11) 表的字符集使用utf8或者UTF8mb4。
    
2. 索引创建
   
   (1) 根据不同类型的表创建适当的索引。
   (2) 在离散程度较高的字段上创建索引。
   (3) 组合索引按照最左前缀的原则创建索引。
   (4) 禁止创建冗余索引。如：（a,b,c）(a,b)。
   (5) 禁止创建重复索引。如：primary key a;uniq index a;。
   (6) 创建索引时，合理利用覆盖索引。
   (7) 尽量创建短索引。
   
   5.1 索引数量

(1) 单张表中索引数量不超过5个（建议3个左右），如果超时，一般情况下表能够进行二次拆分。代码实现 提示

(2) 单个索引中的字段数不超过5个（建议3个以内）。代码实现 提示

(3) 对字符串使⽤用前缀索引。

(4) 建议优先考虑前缀索引，必要时可添加伪列并建立索引。

5.2 主键准则

(1) 表必须有主键。    

(2) 不使用更新频繁的列作为主键。

(3) 尽量不选择字符串列作为主键。

(4) 不允许UUID MD5 HASH这些作为主键。

(5) 默认使⽤非空的唯一键作为主键。

(6) 建议选择自增。

5.3 优先准则

(1) 频繁SQL：对最频繁的SQL必须要添加索引。

(2) 更新SQL：UPDATE、DELETE语句的WHERE条件列需要添加索引，否则每次更新就是表锁。

(3) 排序SQL：ORDER BY、GROUP BY、DISTINCT的字段。充分利用索引先天顺序避免排序，否则会消耗大量磁盘IO。

(4) 核⼼SQL：优先考虑使用覆盖索引。

5.4 一般准则

(1) 区分度最大的字段放在前面。

(2) 避免冗余和重复索引。

(3) 索引要综合评估数据密度和分布以及考虑查询和更新比例。

(4) 索引命名：非唯一索引必须以 idx_字段1_字段2命名，唯一所以必须以uniq_字段1_字段2命名，索引名称必须全部小写。代码实现 提示

(5) 新建的唯一索引必须不能和主键重复。代码实现 提示

(6) 索引字段的默认值不能为NULL，要改为其他的default或者空。NULL非常影响索引的查询效率。代码实现 提示

(7) 反复查看与表相关的SQL，符合最左前缀的特点建立索引。多条字段重复的语句，要修改语句条件字段的顺序，为其建立一条联合索引，减少索引数量。

(8) 能使用唯一索引就要使用唯一索引，提高查询效率。

(9) 使用EXPLAIN判断SQL语句是否合理使用索引，尽量避免extra列出现：Using File Sort，Using Temporary。

(10) SQL变更需要确认索引是否需要变更并通知DBA。

5.5 索引禁忌

(1) 不在低基数列上建立索引，例如“性别”。

(2) 不在索引列进行数学运算和函数运算。

(3) 如果有外键，外键一定要创建索引。

SQL规范

6.1 SQL禁忌

(1) 禁止使用大事务。整个事务要简单，执行时间不能太长。

(2) 禁止使用触发器、函数、存储过程。 代码实现

(3) 禁止在数据库中进⾏数学运算。

(4) 禁止对大表进行JOIN。

(5) 禁止一次更新太多数据。一般不超过10行。 

(6) 禁止在线上提供访问的数据库中跑大查询。如果需要，请联系DBA。

(7) 禁止使⽤order by rand()。代码实现 提示

(8) 禁⽌单条SQL语句同时更新多个表。代码实现拒绝

6.2 一般规范

(1) SQL语句尽可能简单,尽力避免使用JOIN。

(2) 降低业务耦合度，为scale out、sharding留有余地。

(3) 不要用select *，查询哪几个字段就select 这几个字段。

(4) limit分页注意效率。Limit越大，效率越低。可以改写limit，比如例子改写：eg：select id from t limit 10000, 10;  =>  select id from t where id > 10000 limit10;

SELECT * FROM table ORDER BY TIME DESC LIMIT 10000,10;=》 SELECT * FROM table WHERE TIME<last_TIME ORDER BY TIME DESC LIMIT 10.

                                                                                                         =》 SELECT * FROM table inner JOIN(SELECT id FROM table ORDER BY TIME LIMIT 10000,10) as t USING(id)

(5) 使用union all替代union。

(6) 减少与数据库的交互次数。

(7) 注意使用性能分析工具explain、show profile。

(8) SQL语句不可以出现隐式转换，比如 select id from 表 where id='1'。

(9) IN条件里面的数据数量要少。

(10) 能不用NOT IN就不用NOT IN，会把空和NULL给查出来。

(11) 在SQL语句中，禁止使用前缀是%的like。

(12) 不使用负向查询，如not in、not like。

(13) 关于分页查询：程序里建议合理使用分页来提高效率limit，offset较大要配合子查询使用。

(14) 使⽤预编译语句，只传参数，比传递SQL语句更高效；一次解析，多次使用；降低SQL注入概率。

 

1.表需有无意义的自增主键，字段 comment及表 comment

2、需有AddTime和UpdateTime 字段，

UpdateTime建议设置为：DEFAULT NOW() ON UPDATE CURRENT_TIMESTAMP

3、必须有 UpdateTime字段和以 UpdateTime字段开头的索引

 

 

七、流程规范   

(1) 所有的建表操作需要提前告知该表涉及的查询SQL。

(2) 所有的建表需要确定建立哪些索引后才可以建表上线。

(3) 所有的改表结构、加索引操作都需要将涉及到所改表的查询sql发出来告知DBA等相关人员。

(4) 在建新表加字段之前，要求研发至少要提前一天邮件或者大象联系，给dba们评估、优化和审核的时间。

(5) 批量导入、导出数据必须提前通知DBA协助观察。

(6) 禁⽌在线上从库执行后台管理和统计类查询。

(7) 禁⽌有super权限的应用程序账号存在。

(8) 推广活动或上线新功能必须提前通知DBA进⾏行流量评估。

(9) 不在业务高峰期批量更新、查询数据库。

 

1. SQL
   
   (1) WHERE条件中的字段必须使用合适的数据类型，避免进行隐式类型转化或给出不恰当的执行计划。
   (2) 强烈建议使用INNER JOIN代替子查询。
   (3) 大型表避免频繁使用 count(*) 全表的sql，建议使用redis存储表记录数。
   (4) WHERE column = %xxx，百分号在前面的模糊匹配，该字段不能使用索引。
   (5) 字段上使用函数，该字段不能使用索引。
   (6) 字段上使用 <> 或 is not null，该字段不能使用索引。
   (7) UPDATE、DELETE 语句，必须为WHERE条件中离散程度较高的字段创建索引。
   (8) UPDATE、DELETE语句，建议谨慎使用mybatis中拼接sql的方式，尤其是关键表。
   (9) 组合索引中，非mysql5.6支持index condition pushdown的情况下，WHERE条件中顺序靠前的索引字段，使用的非等值条件（IN、BETWEEN、<、<=、>、>=），会导致其后面的字段使用不了索引。
   (10) 不限制多表关联，但建议关注一下执行计划；了解表连接算法，合理使用straight join。
   (11) SQL尽量简单，尽量少的包含业务逻辑，业务逻辑交给应用处理。
    
2. 审核\协助机制
   
   (1) 目前暂时实行酌情审核，一些预估数据量庞大的表、关键的表、操作频繁的表，大家在建表的时候，请把建表sql（包含索引），以及对于该表进行DML操作的SQL（注：一定是工整的SQL，不是mybatis文件），发送给我们的DB小组，大家一起分析审核，必要的时候可以去咨询DBA。
   (2) 一些已经进入慢查询的SQL，或在开发过程中编写较复杂的SQL，大家在没有把握的情况下，可以找DB小组进行讨论。
   (3) 新系统表、新模块DB设计相关，也可以找DB小组进行讨论。
3. 开发规范：

禁止在数据库中存储明文敏感信息（密码、信用卡密码）：加密后存储，避免被拖库之后信息泄露 

避免在数据库中存储照片、附件、视频，建议使用分布式文件系统 

表必须有主键，避免使用md5或者uuid作为主键：

避免人工直接对线上数据库进行DML操作（update、delete数据）：使用自动化运维平台进行操作；特殊场景下分批次进行，添加SLEEP

避免使用外键、存储过程、触发器、定时器等：增加复杂度、维护成本、不稳定

SQL语句避免使用子查询、复杂join、order by rand()，必须走索引 ：使用EXPLAIN判断SQL语句是否合理使用索引，避免extra列出现：Using File Sort，Using Temporary等

避免隐式事务（autocommit=0）；避免大事务，建议拆分小事务：

可以减少死锁，锁等待发生的概率

避免在事务内包含外部操作，如果必须包含则建议设置timeout

主键要保证插入时候递增，避免引起数据碎片和性能问题

禁止先删除索引再加索引，调整索引可以放在同一条SQL上，避免导致线上SQL无索引可用执行很慢，又阻塞添加索引的SQL造成恶性循环。

 

MySQL 常见优化问题

 

1、全表扫描问题

2、未走索引，文件扫描

3、使用了distinct 会产生临时表，distinct字段没有使用索引

4、两个oder by 导致file sort。尽量删除order by，或缩减为单个order by

5、表有5000多万行，全表扫描，limit过大

6、MySQL走了order by字段的索引，没有选择走where字段的索引

 

解决方案：

1、建议先用子查询把符合条件的id找出来，然后再用inner join查找

2、LIMIT优化

3、索引没生效，添加强制索引

4、请使用uninon将一个sql分成两条sql查询、索引进行了union，可以使用union all方式

5、poi_id列表in查询拆分成多次查询

6、goods_id建立索引，去重工作放在业务逻辑中进行

7、子查询改为表连接尽量删除order by

 

 复杂SQL：

 

replace into detail_poi_day (poi_id, date_key, inventory_count, inventory_consume_count, room_night_count, full_goods_count, sold_goods_full, goods_count) ( select aa.poi_id,? date_key,coalesce(aa.inventorycount,?),coalesce(aa.inventoryconsumecount,?),coalesce(aa.roomnightcount,?),coalesce(aa.fullgoodscount,?),coalesce(bb.soldgoodsfull,?),coalesce(aa.goods_count,?) from (select g.poi_id,count(g.goods_id) goods_count,count(if(c.room_status=?,?,?)) fullgoodscount,sum(c.inventory_num) inventorycount ,sum(c.inventory_consume_num) inventoryconsumecount,sum(o.roomnight_confirm_fixed ? o.roomnight_confirm_uncertain) roomnightcount from (select goods_id,container_id,poi_id from detail_goods_realtime_info where poi_id in(?+))g left join detail_goods_order_checkin_day o on g.goods_id=o.goods_id and o.date_key=? left join detail_container_history_day c on g.container_id=c.container_id and c.date_key=? group by g.poi_id)aa left join( select poi_id,if(sum(if(ifsold>?&&ifsold=room_status,?,?))=sum(ifsold)&&sum(ifsold)>?,?,?) soldgoodsfull from( select g.goods_id,g.poi_id,if(sum(o.roomnight_confirm_fixed ? o.roomnight_confirm_uncertain)>?,?,?) ifsold,cc.room_status from(select goods_id,poi_id,container_id from detail_goods_realtime_info where poi_id in(?+))g left join detail_goods_order_checkin_day o on g.goods_id=o.goods_id and o.date_key between ? and ? left join detail_container_history_day cc on g.container_id=cc.container_id and cc.date_key=? group by g.goods_id ) dd group by poi_id)bb on aa.poi_id=bb.poi_id)

 

 

 

mysql> EXPLAIN SELECT `birday` FROM `user` WHERE `birthday` < "1990/2/2";

-- 结果：

id: 1

 

select_type: SIMPLE -- 查询类型（简单查询,联合查询,子查询）

 

table: user -- 显示这一行的数据是关于哪张表的

 

type: range -- 区间索引（在小于1990/2/2区间的数据),这是重要的列,显示连接使用了何种类型。从最好到最差的连接类型为system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL,const代表一次就命中,ALL代表扫描了全表才确定结果。一般来说,得保证查询至少达到range级别,最好能达到ref。

 

possible_keys: birthday  -- 指出MySQL能使用哪个索引在该表中找到行。如果是空的,没有相关的索引。这时要提高性能,可通过检验WHERE子句,看是否引用某些字段,或者检查字段不是适合索引。 

 

key: birthday -- 实际使用到的索引。如果为NULL,则没有使用索引。如果为primary的话,表示使用了主键。

 

key_len: 4 -- 最长的索引宽度。如果键是NULL,长度就是NULL。在不损失精确性的情况下,长度越短越好

 

ref: const -- 显示哪个字段或常数与key一起被使用。 

 

rows: 1 -- 这个数表示mysql要遍历多少数据才能找到,在innodb上是不准确的。

 

Extra: Using where; Using index -- 执行状态说明,这里可以看到的坏的例子是Using temporary和Using

 

 

select_type

• simple 简单select(不使用union或子查询)
• primary 最外面的select
• union union中的第二个或后面的select语句
• dependent union union中的第二个或后面的select语句,取决于外面的查询
• union result union的结果。
• subquery 子查询中的第一个select
• dependent subquery 子查询中的第一个select,取决于外面的查询
• derived 导出表的select(from子句的子查询)

Extra与type详细说明

• Distinct:一旦MYSQL找到了与行相联合匹配的行,就不再搜索了
• Not exists: MYSQL优化了LEFT JOIN,一旦它找到了匹配LEFT JOIN标准的行,就不再搜索了
• Range checked for each Record（index map:#）:没有找到理想的索引,因此对于从前面表中来的每一个行组合,MYSQL检查使用哪个索引,并用它来从表中返回行。这是使用索引的最慢的连接之一
• Using filesort: 看到这个的时候,查询就需要优化了。MYSQL需要进行额外的步骤来发现如何对返回的行排序。它根据连接类型以及存储排序键值和匹配条件的全部行的行指针来排序全部行
• Using index: 列数据是从仅仅使用了索引中的信息而没有读取实际的行动的表返回的,这发生在对表的全部的请求列都是同一个索引的部分的时候
• Using temporary 看到这个的时候,查询需要优化了。这里,MYSQL需要创建一个临时表来存储结果,这通常发生在对不同的列集进行ORDER BY上,而不是GROUP BY上
• Where used 使用了WHERE从句来限制哪些行将与下一张表匹配或者是返回给用户。如果不想返回表中的全部行,并且连接类型ALL或index,这就会发生,或者是查询有问题不同连接类型的解释（按照效率高低的顺序排序
• system 表只有一行：system表。这是const连接类型的特殊情况
• const:表中的一个记录的最大值能够匹配这个查询（索引可以是主键或惟一索引）。因为只有一行,这个值实际就是常数,因为MYSQL先读这个值然后把它当做常数来对待
• eq_ref:在连接中,MYSQL在查询时,从前面的表中,对每一个记录的联合都从表中读取一个记录,它在查询使用了索引为主键或惟一键的全部时使用
• ref:这个连接类型只有在查询使用了不是惟一或主键的键或者是这些类型的部分（比如,利用最左边前缀）时发生。对于之前的表的每一个行联合,全部记录都将从表中读出。这个类型严重依赖于根据索引匹配的记录多少—越少越好+
• range:这个连接类型使用索引返回一个范围中的行,比如使用>或<查找东西时发生的情况+
• index: 这个连接类型对前面的表中的每一个记录联合进行完全扫描（比ALL更好,因为索引一般小于表数据）+
• ALL:这个连接类型对于前面的每一个记录联合进行完全扫描,这一般比较糟糕,应该尽量避免

其中type:

• 如果是Only index,这意味着信息只用索引树中的信息检索出的,这比扫描整个表要快。
• 如果是where used,就是使用上了where限制。
• 如果是impossible where 表示用不着where,一般就是没查出来啥。
• 如果此信息显示Using filesort或者Using temporary的话会很吃力,WHERE和ORDER BY的索引经常无法兼顾,如果按照WHERE来确定索引,那么在ORDER BY时,就必然会引起Using filesort,这就要看是先过滤再排序划算,还是先排序再过滤划算。

 

 

 

创建索引的技巧

1.维度高的列创建索引

数据列中不重复值出现的个数,这个数量越高,维度就越高
如数据表中存在8行数据a ,b ,c,d,a,b,c,d这个表的维度为4
要为维度高的列创建索引,如性别和年龄,那年龄的维度就高于性别
性别这样的列不适合创建索引,因为维度过低

2.对 where,on,group by,order by 中出现的列使用索引

3.对较小的数据列使用索引,这样会使索引文件更小,同时内存中也可以装载更多的索引键

4.为较长的字符串使用前缀索引

5.不要过多创建索引,除了增加额外的磁盘空间外,对于DML操作的速度影响很大,因为其每增删改一次就得从新建立索引

6.使用组合索引,可以减少文件索引大小,在使用时速度要优于多个单列索引

 

前缀索引

如果索引列长度过长,这种列索引时将会产生很大的索引文件,不便于操作,可以使用前缀索引方式进行索引
前缀索引应该控制在一个合适的点,控制在0.31黄金值即可(大于这个值就可以创建)

SELECT COUNT(DISTINCT(LEFT(`title`,10)))/COUNT(*) FROM Arctic; -- 这个值大于0.31就可以创建前缀索引,Distinct去重复

 

ALTER TABLE `user` ADD INDEX `uname`(title(10)); -- 增加前缀索引SQL,将人名的索引建立在10,这样可以减少索引文件大小,加快索引查询速度

 

 

什么样的sql不走索引

要尽量避免这些不走索引的sql

 

SELECT `sname` FROM `stu` WHERE `age`+10=30;-- 不会使用索引,因为所有索引列参与了计算

 

SELECT `sname` FROM `stu` WHERE LEFT(`date`,4) <1990; -- 不会使用索引,因为使用了函数运算,原理与上面相同

 

SELECT * FROM `houdunwang` WHERE `uname` LIKE'后盾%' -- 走索引

 

SELECT * FROM `houdunwang` WHERE `uname` LIKE "%后盾%" -- 不走索引

 

-- 正则表达式不使用索引,这应该很好理解,所以为什么在SQL中很难看到regexp关键字的原因

 

-- 字符串与数字比较不使用索引;

CREATE TABLE `a` (`a` char(10));

EXPLAIN SELECT * FROM `a` WHERE `a`="1" -- 走索引

EXPLAIN SELECT * FROM `a` WHERE `a`=1 -- 不走索引

 

select * from dept where dname='xxx' or loc='xx' or deptno=45 --如果条件中有or,即使其中有条件带索引也不会使用。换言之,就是要求使用的所有字段,都必须建立索引, 我们建议大家尽量避免使用or 关键字

 

-- 如果mysql估计使用全表扫描要比使用索引快,则不使用索引

 

 

我们建议在多表join的时候尽量少join几张表,因为一不小心就是一个笛卡尔乘积的恐怖扫描,另外,我们还建议尽量使用left join,以少关联多.因为使用join 的话,第一张表是必须的全扫描的,以少关联多就可以减少这个扫描次数.

 

 

 

第一范式：

属性(字段)的原子性约束，要求属性具有原子性，不可再分割；

 

第二范式：

记录的惟一性约束，要求记录有惟一标识，每条记录需要有一个属性来做为实体的唯一标识，即每列都要和主键相关。

 

第三范式：

属性(字段)冗余性的约束，即任何字段不能由其他字段派生出来，在通俗点就是：主键没有直接关系的数据列必须消除(消除的办法就是再创建一个表来存放他们，当然外键除外)。即：确保每列都和主键列直接相关,而不是间接相关。

 

如果数据库设计达到了完全的标准化，则把所有的表通过关键字连接在一起时，不会出现任何数据的复本(repetition)。标准化的优点是明显的，它避免了数据冗余，自然就节省了空间，也对数据的一致性(consistency)提供了根本的保障，杜绝了数据不一致的现象，同时也提高了效率。

 

尤其是正确字段类型的选择：（先列出所有字段类型再写建议）

 

关于浮点数与定点数有点看法：

浮点数相对于定点数的优点是在长度一定的情况下，浮点数能够表示更大的数据范围；它的缺点是会引起精度问题。

使用时我们要注意：

1. 浮点数存在误差问题；

2. 对货币等对精度敏感的数据，应该用定点数表示或存储；

3. 编程中，如果用到浮点数，要特别注意误差问题，并尽量避免做浮点数比较；

4. 要注意浮点数中一些特殊值的处理。