一、设计问题？

1.主键是用自增还是UUID ？

Innodb 中的主键是聚簇索引。 如果主键是自增的，那么每次插入新的记录，记录就会顺序添加到当前索引节点的后续位置，当一页写满，就会自动开辟一个新的页。如果不是自增主键，那么可能会在中间插入，就会引发页的分裂，产生很多表碎片！UUID(Universally  Unique  Identifier)全局唯一标识符,是指在一台机器上生成的数字，它保证对在同一时空中的所有机器都是唯一的，是由一个十六位的数字组成,表现出来的形式。由以下几部分的组合：当前日期和时间(UUID的第一个部分与时间有关，如果你在生成一个UUID之后，过几秒又生成一个UUID，则第一个部分不同，其余相同)，时钟序列，全局唯一的IEEE机器识别号（如果有网卡，从网卡获得，没有网卡以其他方式获得），UUID的唯一缺陷在于生成的结果串会比较长。   

2.表示枚举的字段为什么不用ENUM类型 ？

在工作中表示枚举的字段，一般用tinyint类型。那为什么不用ENUM类型呢？

下面两个原因

  (1)ENUM类型的ORDER BY操作效率低，需要额外操作

  (2)如果枚举值是数值，有陷阱

 举个例子，表结构如下

CREATE TABLE test (foobar ENUM('0', '1', '2'));此时，你执行语句

mysql> INSERT INTO test VALUES (1);

查询出的结果为foobar0。

插入语句应该像下面这么写，插入的才是1

mysql> 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.时间字段用什么类型？

(1)varchar，优点是显示直观。缺点数据首先没有校验，比如一条数据为2019111的数据;其次做时间比较运算，需要STR_TO_DATE等函数转化,导致无法命中索引的，数据量一大，是个坑。

(2)timestamp，该类型是四个字节的整数，范围为1970-01-01 08:00:01到2038-01-19 11:14:07。2038年以后无法用timestamp类型存储的。但有一个优势，timestamp类型是带有时区信息的。变更数据库时区数据会自己发生变更。

(3)datetime，datetime储存占用8个字节，范围为1000-01-01 00:00:00 至 9999-12-31 23:59:59。但是存储的是时间绝对值不带有时区。

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

(1)MySQL内存临时表不支持TEXT、BLOB这样的大数据类型，如果查询中包含这样的数据，在排序等操作时，就不能使用内存临时表，必须使用磁盘临时表进行。导致查询效率缓慢。

(2)binlog内容太多。因为你数据内容比较大，就会造成binlog内容比较多。大家也知道，主从同步是靠binlog进行同步，binlog太大了，就会导致主从同步效率问题！因此，不推荐使用text和blob类型！

6.为什么尽量避免使用外键、存储过程、分区表、触发器等？

为了防止随着业务的发展以后如果数据量大到一定程度了需要分表时，拆分带有这些特性的表时成本是非常大的。

7.为什么不同的业务使用不同的数据库，禁止混合使用？

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

8.VARCHAR(N) 只分配真正需要的空间 ？

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

二、mysql开发十个问题

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

  1. 一条sql只能在一个cpu运算；

  2. 大语句拆小语句，减少锁时间；

  3. 一条大sql可以堵死整个库。

  4. 业务复杂可读性差

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

  1. 减少MySQL端的数学运算和逻辑判断，提高效率。

  2. 将业务写入sql后不易于扩展

  3. 移植性差

  4. 可复用性差

3.为什么少用select *？

  1. 数据库需要知道 * 等于什么 ，查数据字典会增大开销

  2. 多出一些不用的列，这些列可能正好不在索引的范围之内， select * 杜绝了索引覆盖的可能性

  3. 不需要的字段会增加数据传输的时间

  4. 大字段，例如很长的 varchar，blob，text会把超出的数据放到另外一个地方，因此读取这条记录会增加一次 io 操作。

  5. select * 数据库需要解析更多的 对象，字段，权限，属性相关，在 SQL 语句复杂，硬解析较多的情况下，会对数据库造成沉重的负担

  6. 额外的 io，内存和 cpu 的消耗，因为多取了不必要的列。

  7. 用 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.如何减少全表扫描？

  1. 应尽量避免在where中使用is null,is not null, !=,or,<>操作符，否则将导致引擎放弃使用索引而进行全表扫描

  2. in 和 not in 也要慎用，否则会导致全表扫描，对于连续的数值， 能用 between 就不要

   3. 在 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.查询结果能不能全量返回给前台？

     非必要不允许。

      1. 通信时间加长

      2. http协议限制,如果JSON超长会通讯失败

      3. 前台浏览器内存压力加大

      4. 业务逻辑决定没必要

      也就是只查需要的数据字段，不需要的尽量不要查询出来，不仅会加大内存损耗，效果也不好。

三、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.创建索引时有哪些错误观念？

      索引越多越好，认为一个查询就需要建一个索引。

      宁缺勿滥，认为索引会消耗空间、严重拖慢更新和新增速度。

      抵制惟一索引，认为业务的惟一性一律需要在应用层通过“先查后插”方式解决

      过早优化，在不了解系统的情况下就开始优化。

四、数据库表设计规范

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) 入库，明确生命周期，保留业务必须数据，定期清理。

24. 唯一索引名为 uk_字段名；普通索引名则为 idx_字段名。

25. 禁止一个字段上建有多个索引

26. 命名以idx_开通，长度控制在15个字符以内。

27. 区分度低的字段，避免创建索引

28. 复合索引原则：a)复合索引中的字段数不超过3个

		        b)复合索引字段的顺序，区分度大的列放在前

29. 禁止一个表上索引超过5个

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

31. 单表字段数上限不得超过30个

32. 表行数不允许超过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. 用户请求传入的任何参数必须做有效性验证。忽略参数校验可能导致：

      1) page size 过大导致内存溢出

      2) 恶意 order by 导致数据库慢查询

      3) 正则输入源串拒绝服务 ReDOS

      4) 任意重定向

      5) SQL 注入，例如sleep(10)

      6) Shell 注入

      7) 反序列化注入

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. order by字段顺序尽量与索引顺序保持一致

36. 禁止分页查询偏移量过大，如limit 10000,10