mysql设计与优化以及数据库表设计与表开发规范

一、设计问题

1、主键是用自增还是UUID？
Innodb引擎中的主键是聚簇索引。如果主键是自增的，那么每次插入新的记录，记录就会顺序添加到当前索引节点的后续位置，当一页写满就会自动开辟一个新的页。如果不是自增主键，那么可能会在中间插入，就会引发页的分裂，产生很多表碎片！

2、表示枚举的字段为什么不用ENUM类型？
在工作中表示枚举的字段，一般用tinyint类型。那为什么不用ENUM类型呢？
有两个原因：

• ENUM类型的ORDER BY操作效率低，需要额外操作；
• 如果枚举值是数值，有陷阱。
  例如表结构如下：CREATE TABLE test (foobar ENUM('0', '1', '2'));
  此时，你执行语句：INSERT INTO test VALUES (1)；
  查询出的结果为foobar 0。插入语句应该像下面这么写，插入的才是1，INSERT INTO test VALUES ('1')；

3、货币字段用什么类型？
如果货币单位是分，可以用Int类型。如果用元，则用Decimal。千万不要用float和double，因为float和double是以二进制存储的，所以有一定的误差。
打个比方，你建一个列如下:
CREATE TABLE t (price float(10,2) DEFAULT NULL) ENGINE=InnoDB DEFAULT CHARSET=utf8
然后insert给price列一个数据为1234567.23，你会发现显示出来的数据变为1234567.25，精度失准！

4、时间字段用什么类型？

• varchar，优点是显示直观。缺点数据首先没有校验，比如一条数据为2019111的数据；其次做时间比较运算，需要STR_TO_DATE等函数转化,导致无法命中索引的，数据量一大，是个坑。
• timestamp，该类型是四个字节的整数，范围为1970-01-01 08:00:01到2038-01-19 11:14:07。2038年以后无法用timestamp类型存储的。但有一个优势，timestamp类型是带有时区信息的。变更数据库时区数据会自己发生变更。
• datetime，datetime储存占用8个字节，范围为1000-01-01 00:00:00 至 9999-12-31 23:59:59。但是存储的是时间绝对值不带有时区。

5、为什么不直接存储图片、音频、视频等大容量内容？

• MySQL内存临时表不支持TEXT、BLOB这样的大数据类型，如果查询中包含这样的数据，在排序等操作时，就不能使用内存临时表，必须使用磁盘临时表进行。导致查询效率缓慢。
• binlog内容太多。因为你数据内容比较大，就会造成binlog内容比较多。大家也知道，主从同步是靠binlog进行同步，binlog太大了，就会导致主从同步效率问题！因此，不推荐使用text和blob类型。

6、为什么尽量避免使用外键、存储过程、分区表、触发器等？
为了防止随着业务的发展以后如果数据量大到一定程度了需要分表时，拆分带有这些特性的表时成本是非常大的。

7、为什么不同的业务使用不同的数据库，禁止混合使用？
原则是要求对业务有长远的规划，不同的业务首先要分表，其次要分库。虽然MySQL很强大，但是单节点的能力是有限的。所以企业级的数据库都是分布式的，要为以后业务的数据增长和访问量增长做好充分的规划。

8、VARCHAR(N) 只分配真正需要的空间？
使用VARCHAR(5)和VARCHAR(20)存储'hello'的空间开销是一样的，但是对这个字段进行聚合操作（group by等）是需要将临时数据存储到内存中的，但是申请内存空间时是按照字段的定义大小来申请的，也就是说ARCHAR(20)申请的内存空间是VARCHAR(5)的4倍。还有当一个表的数据量很大时，要做数据迁移或是大数据分析时，需要抽取全表数据，这个时候读全表数据是无法靠申请内存空间来实现的，MySQL是会在磁盘中建立临时文件表，并且是按照字段定义的大小来占用磁盘空间的，如果一个200G的硬盘，但是表中的数据是50G，在抽取全表数据时会有可能将磁盘占满的。所以，更大的定义列会消耗更多的内存，在使用内存临时表进行排序或操作时会根据定义的长度进行内存分配。

二、mysql开发十个问题

1、为什么写的SQL尽可能简单？

• 一条sql只能在一个cpu运算；
• 大语句拆小语句，减少锁时间；
• 一条大sql可以堵死整个库；
• 业务复杂时sql可读性差。

2、为什么尽可能少使用存储过程/触发器/函数？

• 减少MySQL端的数学运算和逻辑判断，提高效率；
• 将业务写入sql后不易于扩展；
• 移植性差；
• 可复用性差。

3、为什么少用select *？

• 数据库需要知道 * 等于什么 ，查数据字典会增大开销；
• 多出一些不用的列，这些列可能正好不在索引的范围之内， select * 杜绝了索引覆盖的可能性；
• 不需要的字段会增加数据传输的时间；
• 大字段，例如很长的varchar、blob、text会把超出的数据放到另外一个地方，因此读取这条记录会增加一次io操作；
• select * 数据库需要解析更多的对象、字段、权限、属性相关、在SQL语句复杂，硬解析较多的情况下，会对数据库造成沉重的负担；
• 额外的io，内存和cpu的消耗，因为多取了不必要的列；
• 用SELECT *需谨慎，因为一旦列的个数或顺序更改，就有可能程序执行失败。

4、为什么字段类型和传入值必须保证：数字对数字，字符对字符？
查询更加精准，速度更快。

5、可不可以对列进行数学运算和函数运算？
索引字段进行数学运算时，不走索引。可以放到后面对值进行运算。例如：where id+1=123时耗时更多，而where id=123+1耗时更少。速度更快。索引字段慎用函数运算，MySQL的优化器对函数运算识别不出来时会直接走全表扫描。

6、到底要怎么写LIKE语句？
like查询百分号前置，并不是100%不会走索引。如果只select索引字段，或者select索引字段和主键，也会走索引的。尽量减少使用 like‘%%’。

7、为什么要减少使用ORDER BY？
索引添加的目的就是为了改善查询效率，添加索引时要避免出现using filesort，出现using filesort是指当查询操作中包含ORDER BY，无法利用索引完成排序操作时MySQL优化器不得不选择相应的排序算法来实现，数据较少时从内存排序否则从磁盘排序。

8、如何减少全表扫描？

• 应尽量避免在where中使用is null、is not null、!=、or、<>操作符，否则将导致引擎放弃使用索引而进行全表扫描；
• in和not in也要慎用，否则会导致全表扫描，对于连续的数值， 能用between就不要用其他；
• 在where子句中用in使用参数，也会全表扫描。因为SQL只有在运行时才会解析局部变量，但优化程序不能将访问计划的选择推迟到运行时；它必须在编译时进行选择。如果在编译时建立访问计划，变量的值还是未知的，因而无法作为索引选择的输入项。
  如下面语句将进行全表扫描： select id from t where num=@num 可以改为强制查询使用索引：select id from t with(index(索引名)) where num=@num

9、exists和in到底应该怎么用？
select cat from goods a where id in(select cat_id from category b);
以上查询使用了in语句，in()只执行一次，它查出b表中的所有id字段并缓存起来，之后检查a表的id是否与b表中的id相等，如果相等则将a表的记录加入结果集中，直到遍历完a表的所有记录，当b表数据较大时不适合使用in()，因为它会把b表数据全部遍历一次。
select cat from goods a where EXISTS(select a.id from category b where id = b.cat_id);
以上查询使用了exists语句，exists()会执行a.length次，它并不缓存exists()结果集，因为exists()结果集的内容并不重要，重要的是结果集中是否有记录，如果有则返回true，没有则返回false，当b表比a表数据大时适合使用exists()，因为它没有那么多遍历操作，只需要再执行一次查询就行。

10、查询结果能不能全量返回给前台？
非必要不允许。
原因如下：

• 通信时间加长；
• http协议限制，如果JSON超长会通讯失败；
• 前台浏览器内存压力加大。也就是只查需要的数据字段，不需要的尽量不要查询出来，不仅会加大内存损耗，效果也不好。

三、mysql优化问题

1、索引有哪几种？
普通索引(INDEX)：仅加速查询；
唯一索引(UNIQUE INDEX)：加速查询 + 列值唯一（可以有null）；
主键索引(PRIMARY KEY)：加速查询 + 列值唯一（不可以有null）+ 表中只有一个；
组合索引：多列值组成一个索引，专门用于组合搜索，其效率大于索引合并；
全文索引(FULLTEXT INDEX)：对文本的内容进行分词，进行搜索；

2、是不是所有索引对查询都有效？
并不是所有索引对查询都有效，SQL是根据表中数据来进行查询优化的（当数据量比较大时可以使用索引来加快查询速度），当索引列有大量数据重复时，SQL查询可能不会去利用索引。

3、是不是索引越多越好？
索引固然可以提高相应的select的效率，但同时也降低了insert及update的效率，因为insert或update时有可能会重建索引。所以怎样建索引需要慎重考虑，视具体情况而定。一个表的索引数不要超过5个，若太多则应考虑一些不常使用到的列上建的索引是否有必要。

4、索引的匹配原则是什么？
一条sql中，一个表无论其蕴含的索引有多少，但是有且只用一条。对于多列索引来说（a,b,c）其相当于3个索引（a）、（a,b）、（a,b,c）3个索引，又由于MySQL的索引优化器，其where条件后的语句是可以乱序的，比如（b,c,a）也是可以用到索引。如果条件中a,c出现的多，为了更好的利用索引故最好将其修改为（a，c，b）。最左前缀匹配原则mysql会一直向右匹配直到遇到范围查询(>、<、between、like)就停止匹配。所以要尽量把“=”条件放在前面，把这些条件放在最后。
如: 不会用到b的索引：where a=1 and c>0 and b=2； 会用到b的索引：where a=1 and b=2 and c>0；
当查询字段出现次数最多时可以考虑使用索引，并且将它作为条件放在最前面以满足最左前缀匹配原则。

5、为什么索引列要保证not null？
索引是有序的。当一个空值进入索引时，无法确定其在索引中的位置。空值与空值不相等。当检索一个空值时，由于空值与空值并不相等，所以无法在索引中找到期望的空值索引。所以单列索引不存null值，复合索引不存全为null的值，如果列允许为null，可能会得到“不符合预期”的结果集（null的列如果是索引，则为null的列不进入索引里）。

6、业务上具有唯一特性的字段要不要建索引？
不要以为唯一索引影响了insert速度，这个速度损耗可以忽略，但提高查找速度是明显的。另外，即使在应用层做了非常完善的校验控制，即使是多个字段的组合,只要没有唯一索引，根据墨菲定律，必然有脏数据产生。

7、什么是前缀索引？
当索引是很长的字符序列时，这个索引将会很占内存而且会很慢，所以会使用前缀索引。前缀索引就是取索引的前面几个字母作为索引，但是要降低索引的重复率，所以我们还必须要判断前缀索引的重复率。

8、单一索引字段数多少合适？
复合索引中的字段数建议不超过3个，严禁超过5个，因为字段过多实际已经起不到有效过滤数据的作用了。复合索引字段的顺序，区分度大的列放在前，这样命中索引的概率更大。

9、大表建立索引需要注意什么？
表空间和磁盘空间是否足够。索引也是一种数据，在建立索引的时候也会占用大量表空间。因此在对一大表建立索引的时候首先应当考虑的是空间容量问题。在对建立索引的时候要对表进行加锁，因此应当注意操作在业务空闲的时候进行。磁盘I/O物理上，应当尽量把索引与数据分散到不同的磁盘上(不考虑阵列的情况)。逻辑上，数据表空间与索引表空间分开。这是在建索引时应当遵守的基本准则。在建立索引的时候进行全表的扫描工作，因此应当考虑调大初始化参db_file_multiblock_read_count的值。一般设置为16或更大。

10、负向条件查询不能使用索引
也就是不等于之类的负向条件，可以优化为in查询。记住，索引只能告诉你什么存在于表中，而不能告诉你什么不存在于表中。

11、前导模糊查询不能使用索引，将进行全表扫描，非前导模糊查询则可以使用索引
例如：select * from student where name like '%j'; 应该优化为：select * from student where name like 'j%';

12、在属性上进行计算不能命中索引
例如：select * from student where YEAR(date)< = '2017'; 可以优化为：select * from student where date < = '2017-01-01';

13、如果明确知道只有一条结果返回，limit 1能够提高效率
你知道只有一条结果，但数据库并不知道，明确告诉它，让它主动停止游标移动。

14、善用别名，这样看起来不仅逻辑清晰，而且也能减少由列引起的解析歧义的问题。

15、Select * 一般都会造成全表扫描，显示所有列，select 需要的字段即可。

16、经常出现在where子句后面的列可以考虑创建索引来提升查询速度。

17、尽量不要有空判断的语句，因为空判断将导致全表扫描，而不是索引扫描。
对于空判断这种情况，可以考虑对这个列创建数据库默认值。

18、对于连续的数值，能用 between 就不要用 in。

19、对查询进行优化，应尽量避免全表扫描，首先应考虑在 where 及 order by 涉及的列上建立索引。

20、不要在 where 子句中的“=”左边进行函数、算术运算或其他表达式运算，否则系统将可能无法正确使用索引。

21、创建索引时有哪些错误观念？
索引越多越好，认为一个查询就需要建一个索引。宁缺勿滥，认为索引会消耗空间、严重拖慢更新和新增速度。抵制惟一索引，认为业务的惟一性一律需要在应用层通过“先查后插”方式解决。过早优化，在不了解系统的情况下就开始优化。

更多关于sql优化的意见和建议参见：https://www.aliyun.com/citiao/1472264.html

四、数据库表设计规范

前期数据库设计要尽量扎实，尽量做到后期不动数据库，不然后期修改数据库就很麻烦了。

所有的操作都要跟着业务来进行，以业务为中心。

数据表设计：

（1）字段注释规范；

（2）冗余字段设计；

（3）表之间联系设计；

（4）表字段大小设计；

（5）表索引设计、热数据，冷数据。

sql语句开发规范：

（1）查询尽量走索引查询，只查需要的字段，避免全表或全字段扫描；

（2）删除时精准删除，避免造成删除不必要的记录；

（3）更新时精准更新，避免造成更新了其他记录；

（4）插入时尽量插入所有字段，避免漏了数据。

1、表必须有主键，禁止使用复合索引作为主键，禁止更新主键列，不使用字符串列、UUID、MD5、HASH作为主键 ；

2、禁用外键；

3、禁用视图；

4、禁用分区表；

5、必须使用Innodb引擎，禁用MyISAM引擎；

6、禁用触发器、存储过程、自定义函数、EVENTS；

7、禁止使用查询优化器提示(hint)；

8、禁用数据库保留字，如 desc、range、match、delayed等；

9、禁止一个表上索引超过5个；

10、字符集必须使用UTF8；

11、表和字段应该有必要的注释，如果修改含义或表示的状态追加时，需要及时更新字段注释；

12、库名、表名、字段名必须使用小写字母或数字，长度限制在15个字符以内，禁止出现数字开头，禁止两个下划线中间只出现数字；

13、不同表之间的相同字段或者关联字段，字段类型要保持一致；

14、表必备三字段：id、xxx_create、 xxx_modified。说明：其中id为主键，类型为unsigned bigint，xxx_create、xxx_modified的类型均为datetime类型，分别记录该条数据的创建时间、修改时间；

15、临时表、备份表、历史表要使用后缀tmp、bak、his字段标明，并提供数据生命周期；

16、表达是与否概念的字段，必须使用is_xxx 的方式命名，数据类型是unsigned tinyint（ 1表示是，0表示否），说明：任何字段如果为非负数，必须是unsigned；

17、小数类型为decimal，禁止使用float和double；

18、合适的字符存储长度，节约数据库表空间、节约索引存储，提升检索速度；

19、如果存储的字符串长度几乎相等，使用CHAR定长字符串类型；

20、varchar是可变长字符串，不预先分配存储空间，长度不要超过255，如果存储长度大于此值，建议不入库直接存成文件；

21、禁止在数据库中使用大字段（blob/text）；

22、日志类表：a) 不入库，存成文件；b) 入库，明确生命周期，保留业务必须数据，定期清理；

23、唯一索引名为```uk_字段名``；普通索引名则为idx_字段名；

24、禁止一个字段上建有多个索引；

25、命名以idx_开头，长度控制在15个字符以内；

26、区分度低的字段，避免创建索引；

27、复合索引原则：a)复合索引中的字段数不超过3个；b)复合索引字段的顺序，区分度大的列放在前；

28、在varchar字段上建立索引时，必须指定索引长度，没必要对全字段建立索引，根据实际文本区分度决定索引长度；

29、单表字段数上限不得超过30个；

30、表行数不允许超过500W，超过500W时，推荐分表或冷数据下线。

五、数据库表开发规范

1、禁止三个表以上的关联；

2、禁止跨库操作（比如：select、update等）；

3、禁止无条件或永真条件的update、delete操作；

4、禁止频繁的commit；

5、禁止SQL语句检索行数超过100万，否则中断该数据库连接；

6、禁止SQL语句返回行数超过1万，否则截取仅返回1万行；

7、禁止DDL语句（truncate、alter、drop等）；

8、使用explain的rows判断扫描行数，key是否使用索引；

9、避免使用子查询、or，将子查询转化为表连接方式，or转化为in；

10、SQL语句书写要规范：1) SQL语句统一使用小写，每个词只允许有一个空格；2) 避免同一SQL书写格式的不同而导致多次语法分析；

11、用户请求传入的任何参数必须做有效性验证。
忽略参数校验可能导致：

• page size 过大导致内存溢出；
• 恶意 order by 导致数据库慢查询；
• 正则输入源串拒绝服务 ReDOS ；
• 任意重定向；
• SQL 注入，例如sleep(10)；
• Shell 注入；
• 反序列化注入；

12、insert时建议指定字段名，避免字段顺序变动后数据插入错误；

13、当有大量insert操作时，批量进行操作；

14、禁止delete、update语句无where条件或有where条件但没使用索引；

15、更新、删除大量数据时，使用limit和order by主键进行必要的分批提交；

16、明确查询的字段，禁止使用select *；

17、禁止使用select …for update；

18、不要使用count(列名)或count(常量)来替代count(*)，```count()``就是SQL92定义的标准统计行数的语法跟数据库无关跟null和非null无关。 count()会统计值为null的行，而count(列名)不会统计此列为null值的行。

19、count(distinct col)计算该列除null之外的不重复数量；

20、当某一列的值全是null时，count(col)的返回结果为0，但sum(col)的返回结果null，因此使用sum()时需注意NPE问题。

21、避免使用right/left join，改成使用表连接；

22、禁止使用开关条件，如1=1、1=0、1>2等；

23、多表关联所涉及的字段必须有索引；

24、禁止出现隐式转换，保持字段和变量类型一致；

25、条件所涉及的字段需有索引；

26、禁止对“=”左侧字段使用函数、运算；

27、避免使用 not、<>、is null, is not null条件；

28、like子句尽量前置匹配，前置不加%，以便能够利用索引，如 ”%zx”；

29、字段顺序尽量与索引顺序保持一致；

30、多用=操作><操作转换为>=、<=；

31、in操作能避免则避免，若实在避免不了，需要仔细评估in后边的集合元素数量，控制在500个之内。

32、尽可能减少group by操作；

33、尽可能使用where条件替代having子句；

34、避免无意义order by操作，禁止使用order by rand()；

35、禁止分页查询偏移量过大，如limit 10000,10。