面试篇：Mysql

Mysql

MySQL 是最流行的关系型数据库管理系统，在 WEB 应用方面 MySQL 是最好的 RDBMS(Relational Database Management System：关系数据库管理系统)应用软件之一。

关系数据库中连接池的机制

前提：为数据库连接建立一个缓冲池。

（1） 从连接池获取或创建可用连接

（2） 使用完毕之后，把连接返回给连接池

（3） 在系统关闭前，断开所有连接并释放连接占用的系统资源

（4） 能够处理无效连接，限制连接池中的连接总数不低于或者不超过某个限定值。

最小连接数是连接池一直保持的数据连接。如果应用程序对数据库连接的使用量不大，将会有大量的数据库连接资源被浪费掉。

最大连接数是连接池能申请的最大连接数。如果数据连接请求超过此数，后面的数据连接请求将被加入到等待队列中，这会影响之后的数据库操作。如果最小连接数与最大连接数相差太大，那么，最先的连接请求将会获利，之后超过最小连接数量的连接请求等价于建立一个新的数据库连接。不过，这些大于最小连接数的数据库 连接在使用完不会马上被释放，它将被放到连接池中等待重复使用或是空闲超时后被释放。

可以这样理解：数据库池连接数量一直保持一个不少于最小连接数的数量，当数量不够时，数据库会创建一些连接，直到一个最大连接数，之后连接数据库就会等待。

SQL 的select 语句完整的执行顺序

（1） from 子句组装来自不同数据源的数据；

（2） where 子句基于指定的条件对记录行进行筛选；

（3） group by 子句将数据划分为多个分组；

（4） 使用聚集函数进行计算；

（5） 使用 having 子句筛选分组；

（6） 计算所有的表达式；

（7） select 的字段；

（8） 使用order by 对结果集进行排序。

MySQL 的事务

事务的基本要素（ACID）

1.原子性（Atomicity）: 强调事务不可分割

事务开始后所有操作，要么全部做完，要么全部不做，不可能停滞在中间环节。事务执行过程中出错，会回滚到事务开始前的状态，所有的操作就像没有发生一样。也就是说事务是一个不可分割的整体，就像化学中学过的原子，是物质构成的基本单位。

2.一致性（Consistency）：强调事务执行前后，数据的完整性保持一致

事务开始前和结束后，数据库的完整性约束没有被破坏 。比如A 向B 转账， 不可能A 扣了钱，B 却没收到。

3.隔离性（Isolation）：一个事务不应受到其他事务的干扰

同一时间，只允许一个事务请求同一数据，不同的事务之间彼此没有任何干扰。 比如 A 正在从一张银行卡中取钱，在 A 取钱的过程结束前，B 不能向这张卡转账。

4.持久性（Durability）：事务一旦提交，数据将永久保存到数据库。

事务完成后，事务对数据库的所有更新将被保存到数据库，不能回滚。

事务的并发问题

1.脏读：事务A读取了事务B更新的数据，然后B回滚操作，那么A读取到的数据是脏数据

2.不可重复读：事务A多次读取同一数据，事务B在事务A多次读取的过程中，对数据作了更新并提交，导致事务 A 多次读取同一数据时，结果不一致。

3.幻读：系统管理员A将数据库中所有学生的成绩从具体分数改为ABCDE等级，但是系统管理员B就在这个时候插入了一条具体分数的记录，当系统管理员A改结束后发现还有一条记录没有改过来，就好像发生了幻觉一样，这就幻读。

不可重复读的和幻读很容易混淆，不可重复读侧重于修改，幻读侧重于新增或删除。解决不可重复读的问题只 需锁住满足条件的行，解决幻读需要锁表。

MySQL 事务隔离级别

行锁，表锁

索引

数据结构：B+Tree

一般来说能够达到 range 就可以算是优化了

口诀：

全值匹配我最爱，最左前缀要遵守；

带头大哥不能死，中间兄弟不能断；

索引列上少计算，范围之后全失效；

LIKE 百分写最右，覆盖索引不写*；

等空值还有OR，索引影响要注意；

VAR 引号不可丢，QL优化有诀窍。

b-tree 和 b+tree

（1） 非叶子节点只存储键值信息

（2） 所有叶子节点之间都有一个链指针

（3） 数据记录都存放在叶子节点中

## MySQL 数据库中 MyISAM 和 InnoDB 的区别

InnoDB 存储引擎

主要面向OLTP(Online Transaction Processing，在线事务处理)方面的应用

特点： 行锁设计、支持外键；

MyISAM 存储引擎

主要面向OLAP(Online Analytical Processing,在线分析处理)方面的应用。

特点： 不支持事务，支持表所和全文索引。操作速度快。

MySQL 性能优化

（1） 尽量选择较小的列

（2） 将where 中用的比较频繁的字段建立索引

（3） select 子句中避免使用‘*’

（4） 避免在索引列上使用计算、not in 和<>等操作

（5） 当只需要一行数据的时候使用 limit 1

（6） 保证单表数据不超过 200W，适时分割表。针对查询较慢的语句，可以使用 explain 来分析该语句具体的执行情况。

（7） 避免改变索引列的类型。

（8） 选择最有效的表名顺序，from 字句中写在最后的表是基础表，将被最先处理，在 from 子句中包含多个表的情况下，你必须选择记录条数最少的表作为基础表。

（9） 避免在索引列上面进行计算。

（10）尽量缩小子查询的结果

一些数据库设计规范

（一）基础规范

1、表存储引擎必须使用 InnoD，表字符集默认使用 utf8，必要时候使用utf8mb4

（1） 通用，无乱码风险，汉字 3 字节，英文 1 字节

（2） utf8mb4 是utf8 的超集，有存储 4 字节例如表情符号时，使用它

2、禁止使用存储过程，视图，触发器，Event

（1） 对数据库性能影响较大，互联网业务，能让站点层和服务层干的事情，不要交到数据库层

（2） 调试，排错，迁移都比较困难，扩展性较差

3、禁止在数据库中存储大文件，例如照片，可以将大文件存储在对象存储系统，数据库中存储路径

4、禁止在线上环境做数据库压力测试

5、测试，开发，线上数据库环境必须隔离

（二）命名规范

1、库名，表名，列名必须用小写，采用下划线分隔解读：abc，Abc，ABC 都是给自己埋坑

2、库名，表名，列名必须见名知义，长度不要超过 32 字符解读：tmp，wushan 谁知道这些库是干嘛的

3、库备份必须以 bak 为前缀，以日期为后缀

4、从库必须以-s 为后缀

5、备库必须以-ss 为后缀

（三）表设计规范

1、单实例表个数必须控制在 2000 个以内

2、单表分表个数必须控制在 1024 个以内

3、表必须有主键，推荐使用 UNSIGNED 整数为主键 潜在坑：删除无主键的表，如果是 row 模式的主从架构，从库会挂住

4、禁止使用外键，如果要保证完整性，应由应用程式实现 解读：外键使得表之间相互耦合，影响 update/delete 等 SQL 性能，有可能造成死锁，高并发情况下容易成为数据库瓶颈

5、建议将大字段，访问频度低的字段拆分到单独的表中存储，分离冷热数据

（四）列设计规范

1、根据业务区分使用 tinyint/int/bigint，分别会占用 1/4/8 字节

2、根据业务区分使用 char/varchar

（1）字段长度固定，或者长度近似的业务场景，适合使用 char，能够减少碎片，查询性能高

（2）字段长度相差较大，或者更新较少的业务场景，适合使用 varchar，能够减少空间

3、根据业务区分使用 datetime/timestamp

解读：前者占用 5 个字节，后者占用 4 个字节，存储年使用YEAR，存储日期使用 DATE，存储 时间使用 datetime

4、必须把字段定义为NOT NULL 并设默认值

（1） NULL 的列使用索引，索引统计，值都更加复杂，MySQL 更难优化

（2） NULL 需要更多的存储空间

（3） NULL 只能采用 IS NULL 或者 IS NOT NULL，而在=/!=/in/not in 时有大坑

5、使用 INT UNSIGNED 存储 IPv4，不要用 char(15)

6、使用 varchar(20)存储手机号，不要使用整数

（1）牵扯到国家代号，可能出现+/-/()等字符，例如+86

（2）手机号不会用来做数学运算

（3）varchar 可以模糊查询，例如like ‘138%’

7、使用 TINYINT 来代替ENUM 解读：ENUM 增加新值要进行DDL操作

* （五）索引规范

1、唯一索引使用 uniq_[字段名]来命名

2、非唯一索引使用 idx_[字段名]来命名

3、单张表索引数量建议控制在 5 个以内

（1）互联网高并发业务，太多索引会影响写性能

（2）生成执行计划时，如果索引太多，会降低性能，并可能导致 MySQL 选择不到最优索引

（3）异常复杂的查询需求，可以选择 ES 等更为适合的方式存储

4、组合索引字段数不建议超过5个 解读：如果 5 个字段还不能极大缩小row范围，八成是设计有问题

5、不建议在频繁更新的字段上建立索引

6、非必要不要进行 JOIN 查询，如果要进行 JOIN 查询，被 JOIN 的字段必须类型相同，并建立索引

解读：踩过因为 JOIN 字段类型不一致，而导致全表扫描的坑

7、理解组合索引最左前缀原则，避免重复建设索引，如果建立了(a,b,c)，相当于建立了(a), (a,b), (a,b,c)

（六）SQL规范

1、禁止使用 select *，只获取必要字段

（1）select *会增加 cpu/io/内存/带宽的消耗

（2）指定字段能有效利用索引覆盖

（3）指定字段查询，在表结构变更时，能保证对应用程序无影响

2、insert 必须指定字段，禁止使用insert into T values()

解读：指定字段插入，在表结构变更时，能保证对应用程序无影响

3、隐式类型转换会使索引失效，导致全表扫描

4、禁止在where 条件列使用函数或者表达式

解读：导致不能命中索引，全表扫描

5、禁止负向查询以及%开头的模糊查询解读：导致不能命中索引，全表扫描

6、禁止大表 JOIN 和子查询

7、同一个字段上的 OR 必须改写问 IN，IN 的值必须少于 50 个

8、应用程序必须捕获 SQL 异常 解读：方便定位线上问题

• 说明：本规范适用于并发量大，数据量大的典型互联网业务，可直接参考。