专栏继续更新中：MySQL详解
前语
在数据库规划的时候一般要遵从一些标准，也是工作中总结出来的经验，拿来和大家分享
一、数据库规划的根本原则
- 把具有同一个主题的数据存储在一个数据表中，“一表一用”。
- 尽量消除冗余，提高访问数据库的速度。
- 一般要求数据库规划到达第三范式，多对多，最大限度消除了数据冗余、修正反常、刺进反常、删除反常，根本满足联系标准化的要求。
- 联系数据库中，各个数据表之间联系只能为一对一和一对多的联系。关于多对多的联系有必要转换为一对多的联系来处理。
- 规划数据表结构时，应考虑表结构的动态适应性。
二、数据库规划的首要过程
- 需求分析：了解用户的数据需求、处理需求、安全性及完好性要求；
- 概念规划：通过数据笼统，规划体系概念模型，一般为E-R模型；
- 逻辑结构规划：规划体系的形式和外形式，关于联系模型首要是根本表和视图；
- 物理结构规划：规划数据的存储结构和存取方法，如索引的规划；
- 体系实施：组织数据入库、编制应用程序、试运转；
- 运转保护：体系投入运转，长期的保护工作。
三、数据库规划需求留意什么
3.1、 基础标准
3.1.1、运用InnoDB存储引擎
支撑业务、行级锁、并发功能更好、CPU及内存缓存页优化使得资源利用率更高
3.1.2、新库默认运用utf8mb4字符集
utf8mb4是utf8的超集，emoji表情以及部分不常见汉字在utf8下会表现为乱码。
3.1.3、数据表、数据字段有必要加注释
添加注释能今后更好的知道是干什么用的
3.1.4、制止运用存储过程、视图、触发器、Event
高并发大数据的互联网业务，架构规划思想是“解放数据库CPU，将核算转移到服务层”，并发量大的情况下，这些功能会将数据库拖死，业务逻辑放在服务层具备更好的拓展性，能够简单实现“增机器就加功能”。数据库拿手存储与索引
3.1.5、制止存储大文件或许大相片
大文件和相片存储在文件体系，数据库里存URI更好
3.1.6、制止运用应用程序配置文件内的账号手工访问线上数据库
3.1.7、制止非DBA对线上数据库进行写操作，修正线上数据需求提交工单，由DBA履行，提交的SQL句子有必要通过测验
3.1.8、分配非DBA以只读账号，有必要通过VPN+跳板机访问授权的从库
3.1.9、开发、测验、线上环境隔离
3.1.10、不在数据库做核算，cpu核算有必要移至业务层
3.1.11、平衡范式与冗余，为提高功率能够献身范式规划，冗余数据
3.1.12、拒绝3B，大SQL，大业务，大批量
3.2、 命名标准
3.2.1、只答应运用内网域名，而不是ip衔接数据库
运用域名，在切换数据库服务器的时候，只需求更改DNS域名解析，不需求更改配置文件。不只是数据库，缓存的衔接，服务的衔接都有必要运用内网域名。
线上环境、开发环境、测验环境数据库内网域名命名标准
业务名称：xxx
线上环境：dj.xxx.db
开发环境：dj.xxx.rdb
测验环境：dj.xxx.tdb
3.2.2、库名、表名、字段名：小写，下划线风格，不超越32个字符，制止拼音英文混用
3.2.3、表名t_xxx，非仅有索引名idx_xxx，仅有索引名uniq_xxx（idx：索引文件Index file）
3.3、 表规划标准
3.3.1、单实例表数目有必要小于500
3.3.2、单表列数目有必要小于30
3.3.3、表有必要有主键，例如自增主键
3.3.4、制止运用外键，假如有外键完好性束缚，需求应用程序控制外键会导致表与表之间的耦合，update和delete操作都会涉及相关联的表，影响SQL的功能，甚至会形成死锁。高并发情况下简单形成数据库功能，大数据高并发业务场景数据库运用功能优先。
3.3.5、控制单表数据量，单表记载控制在千万级。
3.4、 字段规划有必要标准
3.4.1、有必要把字段定义为NOT NULL而且提供默认值
- null的列使索引/索引核算/值都比较复杂，对MySQL来说更难优化
- null这种类型MySQL内部需求进行特别处理，添加数据库处理记载的复杂性
3.4.2、制止运用TEXT、BLOB类型
会浪费更多的磁盘和空间内存，非必要的很多的大字段查询会淘汰掉热数据，导致内存射中率急剧下降，影响数据库的功能。
3.4.3、制止运用小数存储钱银
运用整数，小数简单导致钱对不上
3.4.4、有必要运用varchar（20）存储手机号
- 涉及到区号或许国家的代号
- 手机号会去做数学运算么？
- varchar能够支撑含糊查询 例如：like”138%”
3.4.5、制止运用ENUM，可运用TINYINT代替
- 添加新的ENUM值要做DDL操作
- ENUM的内部实践存储便是整数，你以为自己定义的是字符串？
3.4.6、字段选择类型更小的一般更好：小的数据类型更快，由于它们占用更少的磁盘、内存和CPU缓存，而且处理需求的CPU周期更少。
3.5、 索引规划标准
3.5.1、单表索引主张控制在5个内
3.5.2、单索引字段数不答应草超越5个
字段超越5个，实践起不到有用过滤数据的作用
3.5.3、制止在更新非常频频、区分度不高的特点上树立索引
更新会变更B+树，更新频频的字段树立索引会大大下降数据库功能
“性别”这种区分度不大的特点，树立索引是没有什么意义的，不能有用过滤数据，功能与全表扫描类似
3.5.4、树立组合索引，有必要把区分度高的字段放在前面
- MyISAM和INNODB的区别
- 业务安全（MyISAM不支撑业务，INNODB支撑业务）
- 外键MyISAM不支撑外键，INNODB支撑外键
- 锁机制（MyISAM是表锁，INNODB是行锁）
- 查询和添加速度（MyISAM批量刺进速度快）
- 支撑全文索引（MyISAM支撑全文索引，INNODB不支撑全文索引）
- MyISAM内存空间运用率比INNODB低
3.6、SQL句子优化
3.6.1、制止运用select *，只获取必要的字段，需求显现阐明列特点
- 读取不需求的列会添加CPU、IO、NET耗费
- 不能有用的利用掩盖索引
- 运用select *简单在添加或许删除字段后出现程序BUG
3.6.2、制止运用insert into t_xxx values(xxx)，有必要显现履行刺进的列特点
- 简单在添加或许删除字段后出现程序BUG
3.6.3、制止运用特点隐式转换
- Select uid from t_user where phone=13885236846 会导致全表扫描，而不能射中phone索引
3.6.4、制止在where条件的特点上运用函数或许表达式，在特点上进行核算不能射中索引
- Select uid from t_user where from_unixtime(day)>=’2017-02-15’会导致全表扫描
- 正确为：select uid from t_user where day>=unix_timestamp(‘2017-02-15 00:00:00’)
3.6.5、制止负向查询，以及%开头的含糊查询
- 负向查询条件：NOT、!=、<>、!<、!>、NOT IN、NOT LIKE等，会导致全表扫描
- %开头的含糊查询，会导致全表扫描
3.6.6、制止大表运用JOIN查询，制止大表运用子查询
- 会产生暂时表，耗费较多的内存与CPU，极大影响数据库功能
3.6.7、制止运用OR条件，有必要改为IN查询
- 旧版本的MySQL的OR查询是不能射中索引的，即便能射中索引，为何要让数据库耗费更多的CPU帮助实施查询优化？
3.6.8、应用程序有必要捕获SQL反常，并有相应的处理
3.6.9、负向条件查询不能运用索引
Select * from order where status!=0 and status!=1
not in/not exists都不是很好的习惯
能够优化为
Select * from order where status in(2,3)
3.6.10、前导含糊查询不能用索引
Select * from order where desc like ‘%xxx’
而非前导含糊查询则能够：
Select * from order where desc like ‘xxx%’
3.6.11、数据区分度不大的字段不宜运用索引
能过滤80%数据时就能够运用索引
3.6.12、limit高效分页
limit越大，功率越低
select id from t limit 1000,10应改为:select id from t where id>1000 limit 10
3.6.13、假如业务大部分是单条查询，运用Hash索引功能更好
3.6.14、答应为null的列，查询有潜在大坑
单列索引不存null值，复合索引不存全为null的值，假如列答应为null，可能会得到“不符合预期”的成果集。
总结
以上是数据规划的根本原则和首要过程以及规划的时候留意的事项期望对大家有帮助！
