load什么意思（MySQL三十六条军规）

总是什意思在灾难发生后，才想起容灾的条军重要性；总是在吃过亏后，才记得曾经有人提醒过。什意思

一、条军核心军规(5)

1.尽量不在数据库做运算

尽量不在数据库做运算复杂运算移到程序端CPU尽可能简单应用MySQL例如: md5() / Order by Rand()

2.控制单表数据量

• 一年内的什意思单表数据量预估 纯INT不超1000W 含CHAR不超500W• 合理分表不超载 USERID DATE AREA ….• 建议单库不超过300-400个表

3.保持表身段苗条

• 表字段数少而精√ IO高效 √ 全表遍历 √ 表修复快√ 提高幵发 √ alter table快• 单表多少字段合适？• 单表1G体积 500W行评估 顺序读1G文件需N秒 单行不超过200Byte 单表不超50个纯INT字段 单表不超20个CHAR(10)字段• 单表字段数上限控制在20~50个

4.平衡范式和冗余

• 平衡是门艺术 严格遵循三大范式？ 效率优先、提升性能 没有绝对的条军对不错 适当时牺牲范式、加入冗余 但会增加代码复杂度

5.拒绝3B

• 数据库幵发像城市交通 非线性增长• 拒绝3B 大SQL (BIG SQL) 大事务 (BIG Transaction) 大批量 (BIG Batch)二、什意思字段类军规(6)

1.用好数值字段，条军尽量简化字段位数

• 三类数值类型： TINYINT(1Byte) SMALLINT(2B) MEDIUMINT(3B) INT(4B)、什意思BIGINT(8B) FLOAT(4B)、条军DOUBLE(8B) DECIMAL(M,D)

2.把字符转化为数字

• 数字型VS字符串型索引 更高效 查询更快 占用空间更小• 举例：用无符号INT存储IP，什意思而非CHAR(15) INT UNSIGNED INET_ATON() INET_NTOA()

3.优先使用Enum或Set

• 优先使用ENUM或SET 字符串 可能值已知且有限• 存储 ENUM占用1字节，条军转为数值运算 SET视节点定，什意思最多占用8字节 比较时需要加’单引号(即使是条军数值)• 距离 `sex` enum('F','M') COMMENT '性别' `c1` enum('0','1','2','3') COMMENT '职介审核'

4.避免使用Null字段

• 避免使用NULL字段 很难进行查询优化 NULL列加索引，需要额外空间 含NULL复合索引无效• 举例 `a` char(32) DEFAULT NULL `b` int(10) NOT NULL `c` int(10) NOT NULL DEFAULT 0

5.少用并拆封Text/Blob

• TEXT类型处理性能远低亍VARCHAR 强制生成硬盘临时表 浪费更多空间 VARCHAR(65535)==>64K (注意UTF-8)• 尽量不用TEXT/BLOB数据类型• 若必须使用则拆分到单独的什意思表• 举例： CREATE TABLE t1 ( id INT NOT NULL AUTO_INCREMENT, data text NOT NULL, PRIMARY KEY (id)) ENGINE=InnoDB;

6.不在数据库中存图片

三、索引类军规(5)

1.谨慎合理添加索引

• 谨慎合理添加索引 改善查询 减慢更新 索引不是越多越好• 能不加的索引尽量不加 综合评估数据密度和数据分布 最好不超过字段数20%• 结合核心SQL优先考虑覆盖索引• 举例 不要给“性别”列创建索引

2.字符字段必须建立前缀索引

• 区分度 单字母区分度：26 4字母区分度：26*26*26*26=456,976 5字母区分度： 26*26*26*26*26=11,881,376 6字母区分度： 26*26*26*26*26*26=308,915,776• 字符字段必须建前缀索引`pinyin` varchar(100) DEFAULT NULL COMMENT '小区拼音',KEY `idx_pinyin` (`pinyin`(8)),) ENGINE=InnoDB

3.不在索引列做运算

• 不在索引列进行数学运算或凼数运算 无法使用索引 导致全表扫描BAD:select * from table WHERE to_days(current_date) – to_days(date_col) <= 10GOOD: select * from table WHERE date_col >= DATE_SUB('2011-10-22',INTERVAL 10 DAY);

4.自增列或全局ID做InnoDB主键

• 对主键建立聚簇索引• 二级索引存储主键值• 主键不应更新修改• 按自增顺序插入值• 忌用字符串做主键• 聚簇索引分裂• 推荐用独立于业务的AUTO_INCREMENT列或全局ID生成器做代理主键• 若不指定主键，InnoDB会用唯一且非空值索引代替

5.尽量不用外键

• 线上OLTP系统（线下系统另论） 外键可节省开发量 有额外开销 逐行操作 可‘到达’其它表，意味着锁 高幵发时容易死锁• 由程序保证约束四、SQL类军规(15)

1.SQL尽可能简单

• 大SQL VS 多个简单SQL 传统设计思想 BUT MySQL NOT 一条SQL叧能在一个CPU运算 5000+ QPS的高幵发中，1秒大SQL意味着？ 可能一条大SQL就把整个数据库堵死• 拒绝大SQL，拆解成多条简单SQL 简单SQL缓存命中率更高 减少锁表时间，特别是MyISAM 用上多CPU

2.保持事务连接短小

• 保持事务/DB连接短小精悍 事务/连接使用原则：即开即用，用完即关 与事务无关操作放到事务外面, 减少锁资源的占用 不破坏一致性前提下，使用多个短事务代替长事务• 举例 发贴时的图片上传等待 大量的sleep连接

3.尽可能避免使用SP/Trigger/Function

• 线上OLTP系统（线下库另论） 尽可能少用存储过程 尽可能少用触发器 减用使用MySQL凼数对结果进行处理• 由客户端程序负责

4.尽量不用Select

*• 用SELECT * 时 更多消耗CPU、内存、IO、网络带宽 先向数据库请求所有列，然后丢掉不需要列• 尽量不用SELECT * ，只取需要数据列 更安全的设计：减少表变化带来的影响 为使用covering index提供可能性 Select/JOIN减少硬盘临时表生成，特别是有TEXT/BLOB时• 举例SELECT * FROM tag WHERE id = 999184；SELECT keyword FROM tag WHERE id = 999184；

5.改写Or为IN()

• 同一字段，将or改写为in() OR效率：O(n) IN 效率：O(Log n) 当n很大时，OR会慢很多• 注意控制IN的个数，建议n小亍200• 举例Select * from opp WHERE phone=‘12347856' orphone=‘42242233' \GSelect * from opp WHERE phone in ('12347856' , '42242233')；

6.改写Or为Union

• 不同字段，将or改为union 减少对不同字段进行 "or" 查询 Merge index往往很弱智 如果有足够信心：set globaloptimizer_switch='index_merge=off';• 举例Select * from opp WHERE phone='010-88886666' orcellPhone='13800138000';Select * from opp WHERE phone='010-88886666'unionSelect * from opp WHERE cellPhone='13800138000';

7.避免负向查询和%前缀模糊查询

• 避免负向查询 NOT、!=、<>、!<、!>、NOT EXISTS、NOT IN、NOT LIKE等• 避免 % 前缀模糊查询 B+ Tree 使用不了索引 导致全表扫描• 举例MySQL> select * from post WHERE title like ‘北京%' ;298 rows in set (0.01 sec)MySQL> select * from post WHERE title like '%北京%' ;572 rows in set (3.27 sec)

8.Count不要使用在可Null的字段上面

• 几个有趣的例子： COUNT(COL) VS COUNT(*) COUNT(*) VS COUNT(1) COUNT(1) VS COUNT(0) VS COUNT(100)• 结论 COUNT(*)=count(1) COUNT(0)=count(1) COUNT(1)=count(100) COUNT(*)!=count(col) WHY?

9.减少Count(*)

• MyISAM VS INNODB 不带 WHERE COUNT() 带 WHERE COUNT()• COUNT(*)的资源开销大，尽量不用少用• 计数统计 实时统计：用memcache，双向更新，凌晨跑基准 非实时统计：尽量用单独统计表，定期重算

10.Limit高效分页

• 传统分页： Select * from table limit 10000,10;• LIMIT原理： Limit 10000,10 偏移量越大则越慢• 推荐分页： Select * from table WHERE id>=23423 limit 11;#10+1 (每页10条) select * from table WHERE id>=23434 limit 11;• 分页方式二： Select * from table WHERE id >= ( select idfrom table limit 10000,1 ) limit 10;• 分页方式三： SELECT * FROM table INNER JOIN (SELECT idFROM table LIMIT 10000,10) USING (id) ;• 分页方式四： 程序取ID：select id from table limit 10000,10; Select * from table WHERE id in (123,456…) ;• 可能需按场景分析幵重组索引• 示例：MySQL> select sql_no_cache * from post limit 10,10;10 row in set (0.01 sec)MySQL> select sql_no_cache * from post limit 20000,10;10 row in set (0.13 sec)MySQL> select sql_no_cache * from post limit 80000,10;10 rows in set (0.58 sec)MySQL> select sql_no_cache id from post limit 80000,10;10 rows in set (0.02 sec)MySQL> select sql_no_cache * from post WHERE id>=323423 limit 10;10 rows in set (0.01 sec)MySQL> select * from post WHERE id >= ( select sql_no_cache id from post limit80000,1 ) limit 10 ;10 rows in set (0.02 sec)

11.使用Union ALL 而不用Union

• 若无需对结果进行去重，则用UNION ALL UNION有去重开销• 举例 MySQL>SELECT * FROM detail20091128 UNION ALLSELECT * FROM detail20110427 UNION ALLSELECT * FROM detail20110426 UNION ALLSELECT * FROM detail20110425 UNION ALLSELECT * FROM detail20110424 UNION ALLSELECT * FROM detail20110423;

12.分解链接，保证高并发

• 高幵发DB不建议进行两个表以上的JOIN• 适当分解联接保证高幵发 可缓存大量早期数据 使用了多个MyISAM表 对大表的小ID IN() 联接引用同一个表多次• 举例MySQL> Select * from tag JOIN tag_post on tag_post.tag_id=tag.id JOIN post on tag_post.post_id=post.id WHERE tag.tag=‘二手玩具’;MySQL> Select * from tag WHERE tag=‘二手玩具’;MySQL> Select * from tag_post WHERE tag_id=1321;MySQL> Select * from post WHERE post.id in (123,456,314,141)

13.Group By 去除排序

• GROUP BY 实现 分组 自动排序• 无需排序：Order by NULL• 特定排序：Group by DESC/ASC• 举例MySQL> select phone,count(*) from post group by phone limit 1 ;1 row in set (2.19 sec)MySQL> select phone,count(*) from post group by phone order by null limit 1;1 row in set (2.02 sec)

14.同数据类型的列值比较

•原则：数字对数字，字符对字符•数值列与字符类型比较 同时转换为双精度 进行比对•字符列与数值类型比较 字符列整列转数值 不会使用索引查询•举例：字符列与数值类型比较字段：`remark` varchar(50) NOT NULL COMMENT '备注,默认为空'MySQL>SELECT `id`, `gift_code` FROM gift WHERE `deal_id` = 640 AND remark=115127;1 row in set (0.14 sec)MySQL>SELECT `id`, `gift_code` FROM pool_gift WHERE `deal_id` = 640 AND remark='115127' ;1 row in set (0.005 sec)

15.Load Data导入数据，比Insert快20倍

• 批量数据快导入： 成批装载比单行装载更快，不需要每次刷新缓存 无索引时装载比索引装载更快 Insert values ,values，values 减少索引刷新 Load data比insert快约20倍• 尽量不用 INSERT ... SELECT 延迟 同步出错

16.打散大批量更新，尽量凌晨操作

• 大批量更新凌晨操作，避开高峰• 凌晨不限制• 白天上限默认为100条/秒（特殊再议）• 举例：update post set tag=1 WHERE id in (1,2,3);sleep 0.01;update post set tag=1 WHERE id in (4,5,6);sleep 0.01;……五、约定类军规(5)

1.隔离线上线下

• 构建数据库的生态环境• 开发无线上库操作权限• 原则：线上连线上，线下连线下

online：线上环境，开发人员不允许直接在线上环境进行数据库操作，如果需要操作必须找DBA进行操作并进行相应记录，禁止进行压力测试。

real：生产数据库从库（准实时同步），只读环境，不允许修改数据，不允许修改表结构，供线上问题查找，数据查询等使用。

sim：模拟环境，开发可读写，发起上线请求时，会先在这个环境上进行预执行，这个环境也可供部署上线演练或压力测试使用。

qa：测试环境，开发可读写，开发人员可以通过工具修改表结构。

dev：开发环境，开发可读写，可修改表结构。开发人员可以修改表结构，可以随意修改其中的数据但是需要保证不影响其他开发同事。

2.禁止未经DBA认证的子查询

• MySQL子查询 大部分情况优化较差 特别WHERE中使用IN id的子查询 一般可用JOIN改写• 举例 MySQL> select * from table1 where id in (selectid from table2); MySQL> insert into table1 (select * from table2);//可能导致复制异常

3.永远不在程序段显式加锁

• 永远不在程序端对数据库显式加锁• 外部锁对数据库不可控• 高幵发时是灾难• 极难调试和排查• 幵发扣款等一致性问题• 采用事务• 相对值修改• Commit前二次较验冲突

4.表字符集统一使用UTF8/UTF8MB4

• 统一字符集：UTF8• 校对规则：utf8_general_ci• 乱码：SET NAMES UTF8

5.统一命名规范

• 库表等名称统一用小写 Linux VS Windows MySQL库表大小写敏感 字段名的大小写不敏感• 表命名 同一个模块的表尽可能使用相同的前缀，表名称尽可能表达含义。所有日志表均以 log_ 开头• 字段命名 表达其实际含义的英文单词或简写。布尔意义的字段以“is_”作为前缀，后接动词过去分词。 各表之间相同意义的字段应同名。各表之间相同意义的字段，以去掉模块前缀的表名_字段名命名。 外键字段用表名_字段名表示其关联关系。 表的主键一般都约定成为id，自增类型，是别的表的外键均使用xxx_id的方式来表明。• 索引命名 非唯一索引必须按照“idx_字段名称_字段名称[_字段名]”进行命名 唯一索引必须按照“uniq_字段名称_字段名称[_字段名]”进行命名• 约束命名 主键约束：pk_表名称。 唯一约束：uk_表名称_字段名。（应用中需要同时有唯一性检查逻辑。）• 触发器命名 trg_表名_操作。• 函数过程命名 采用动词+名词的形式表达其含义。• 序列命名 seq_表名等