《面试专题》第五部分 MySQL面试
事务
MySQL 事务四大特性，简单说下
事务的四大特性 ACID
-
原子性 Atomicity：
一个事务必须被事务不可分割的最小工作单元，整个操作要么全部成功，要么全部失败，一般就是通过commit和rollback来控制
-
一致性 Consistency:
数据库总能从一个一致性的状态转换到另一个一致性的状态，比如小滴课堂下单支付成功后，开通视频播放权限，只要有任何一方发生异常就不会成功提交事务
-
隔离性 Isolation:
一个事务相对于另一个事务是隔离的，一个事务所做的修改是在最终提交以前，对其他事务是不可见的
-
持久性 Durability:
一旦事务提交，则其所做的修改就会永久保存到数据库中。此时即使系统崩溃，修改的数据也不会丢失
简单说明下什么是脏读、幻读、不可重复读
-
脏读
一个事务读到了另一个事务未提交的数据
-
不可重复读
同个事务前后多次读取，不能读到相同的数据内容，因为中间有其它事务操作了同一个数据
-
幻读
当某个事务在读取某个范围内数据时，另外一个事务又在该范围内插入新的数据，之前的事务再次读取该范围数据时，两次数据读取结果不一致
事务隔离级别有哪几种，mysql默认数据库隔离级别是什么？
事务的隔离级别越高，事务越安全，但是并发能力越差。
Read Uncommitted(未提交读，读取未提交内容)
事务中的修改即使没有提交，其他事务也能看见，事务可以读到为提交的数据称为脏读
也存在不可重复读、幻读问题
例子：
小滴课堂运营小姐姐配置了一个课程活动，原价500元的课程，配置成50元，但是事务没提交。
你刚好看到这个课程那么便宜准备购买，但是Anna小姐姐马上回滚了事务，重新配置并提交了事务，你准备下单的时候发现价格变回了500元
Read Committed(提交读，读取提交内容)
一个事务开始后只能看见已经提交的事务所做的修改，在事务中执行两次同样的查询可能得到不一样的结果，也叫做不可重复读(前后多次读取，不能读到相同的数据内容)，也存幻读问题
例子：
老王在小滴课堂有1000积分，准备去兑换《面试专题课程》，查询数据库确实有1000积分
但是老王的女友同时也在别的地方登录，把1000积分兑换了《SpringCloud微服务专题课程》，且在老王之前提交事务；当系统帮老王兑换《面试专题课程》是发现积分预计没了，兑换失败。
老王事务A事先读取了数据，他女友事务B紧接了更新了数据且提交了事务，事务A再次读取该数据时，数据已经发生了改变
Repeatable Read(可重复读,mysql默认的事务隔离级别)
解决脏读、不可重复读的问题，存在幻读的问题，使用 MMVC机制 实现可重复读
例子
老王在小滴课堂有1000积分，准备去兑换《面试专题课程》，查询数据库确实有1000积分
老王的女友同时也在别的地方登录先兑换了这个《面试专题课程》，老王的事务提交的时候发现存在了，之前读取的没用了，像是幻觉
幻读问题：MySQL的InnoDB引擎通过MVCC自动帮我们解决,即多版本并发控制
Serializable(可串行化)
解决脏读、不可重复读、幻读，可保证事务安全，但强制所有事务串行执行，所以并发效率低
存储引擎
说说你了解的MySQL存储引擎
常见的有多类，InnoDB、MyISAM、MEMORY、MERGE、ARCHIVE、CSV 等
一般比较常用的有InnoDB、MyISAM
MySQL 5.5以上的版本默认是InnoDB，5.5之前默认存储引擎是MyISAM
| 区别项 | InnoDB | MyISAM |
|---|---|---|
| 事务 | 支持 | 不支持 |
| 锁粒度 | 行锁，适合高并发 | 表锁，不适合高并发 |
| 是否默认 | 默认 | 非默认 |
| 支持外键 | 支持外键 | 不支持 |
| 适合场景 | 读写均衡,写大于读场景，需要事务 | 读多写少场景，不需要事务 |
| 全文索引 | 不支持，可以通过插件实现, 更多使用ElasticSearch | 支持全文索引 |
索引优化
MySQL常用的功能索引有哪些，分别在什么场景下使用，创建语句是怎样的？
| 索引名称 | 特点 | 创建语句 |
|---|---|---|
| 普通索引 | 最基本的索引,仅加速查询 | CREATE INDEX idx_name ON table_name(filed_name) |
| 唯一索引 | 加速查询，列值唯一，允许为空； 组合索引则列值的组合必须唯一 | CREATE UNIQUE INDEX idx_name ON table_name(filed_name_1,filed_name_2) |
| 主键索引 | 加速查询，列值唯一， 一个表只有1个，不允许有空值 | ALTER TABLE table_name ADD PRIMARY KEY ( filed_name ) |
| 组合索引 | 加速查询，多条件组合查询 | CREATE INDEX idx_name ON table_name(filed_name_1,filed_name_2); |
| 覆盖索引 | 索引包含所需要的值，不需要“回表”查询， 比如查询 两个字段，刚好是 组合索引 的两个字段 | |
| 全文索引 | 对内容进行分词搜索，仅可用于MyISAM， 更多用ElasticSearch做搜索 | ALTER TABLE table_name ADD FULLTEXT ( filed_name ) |
索引是最好的解决方案吗？
索引并不总是最好的工具。
总的来说,只有当索引帮助存储引擎快速查找到记录带来的好处大于其带来的额外工作时,索引才是有效的。对于非常小的表,大部分情况下单的全表扫描更高效。
对于中到大型的表,索引就非常有效。
但对于特大型的表,建立和使用索引的代价将随之增长。
这种情况下,则需要一种技术可以直接区分出查询需要的一组数据,而不是一条记录一条记录地匹配。例如可以使用分区技术
为什么主键推荐使用自增ID而不是UUID
推荐使用自增ID,不要使用UUID.
因为在InnoDB存储引擎中,主键索引是作为聚簇索引存在的,也就是说,主键索引的B+树叶子节点上存储了主键索引以及全部的数据(按照顺序),如果主键索引是自增ID,那么只需要不断向后排列即可,如果是UUID,由于到来的ID与原来的大小不确定,会造成非常多的数据插入,数据移动,然后导致产生很多的内存碎片,进而造成插入性能的下降.
总之,在数据量大一些的情况下,用自增主键性能会好一些.
图片来源于《高性能MySQL》: 其中默认后缀为使用自增ID,_uuid为使用UUID为主键的测试,测试了插入100w行和300w行的性能.
关于主键是聚簇索引,如果没有主键,InnoDB会选择一个唯一键来作为聚簇索引,如果没有唯一键,会生成一个隐式的主键.
主键如果使用随机UUID会怎样
因为新行的主键值不一定比之前插入的大,所以InnoDB 无法简单地总是把新行插入到索引的最后,而是需要为新的行寻找合适的位置——通常是已有数据的中间位置—并且分配空间。这会增加很多的额外工作,并导致数据分布不够优化。下面是总结的一些缺点:
-
写的目标页可能已经刷到磁盘上并从缓存中移除,或者是还没有被加载到缓存中,InnoDB 在插入之前不得不先找到并从磁盘读取目标页到内存中。这将导致大量的随机I/O
-
因为写入是乱序的,InnoDB 不得不频繁地做页分裂操作,以便为新的行分配空间。页分裂会导致移动大量数据,一次插入最少需要修改三个页而不是一个页。由于频繁的页分裂,页会变得稀疏并被不规则地填充,所以最终数据会有碎片。
在把这些随机值载入到聚簇索引以后,也许需要做一次OPTIMIZE TABLE 来重建表并优化页的填充
哪些情况下会导致索引失效
- 以 % 开头的 like 查询
- 隐式转换：如果列的类型为字符串类型，而查询入参为数字类型，mysql在比较值是否相等时如果发现两边字段类型不一致，就会进行隐式转换（转换为浮点类型）
- 创建复合索引，但使用时不满足最左匹配原则
- 在数据量较少场景下，如果MySQL分析使用索引比全表扫描查询速度更慢，则不会使用索引
- 以 or 分割的查询条件，尽管or前面的条件列中有索引，而or后面的列中没有索引，那么所涉及的索引也不会被用到
- 列参与了数学运算或者函数
什么是MySQL最左匹配原则？
当b+树的数据项是复合的数据结构，比如(name,age,sex)的时候，b+数是按照从左到右的顺序来建立搜索树的，比如当(张三,20,F)这样的数据来检索的时候，b+树会优先比较name来确定下一步的所搜方向，如果name相同再依次比较age和sex，最后得到检索的数据；但当(20,F)这样的没有name的数据来的时候，b+树就不知道下一步该查哪个节点，因为建立搜索树的时候name就是第一个比较因子，必须要先根据name来搜索才能知道下一步去哪里查询。比如当(张三,F)这样的数据来检索时，b+树可以用name来指定搜索方向，但下一个字段age的缺失，所以只能把名字等于张三的数据都找到，然后再匹配性别是F的数据了， 这个是非常重要的性质，即索引的最左匹配特性。
数据库优化
关心过业务系统里面的sql耗时吗?统计过慢查询吗?对慢查询都怎么优化过?
在业务系统中,除了使用主键进行的查询,其他的我都会在测试库上测试其耗时,慢查询的统计主要由运维在做,会定期将业务中的慢查询反馈给我们.
慢查询的优化首先要搞明白慢的原因是什么? 是查询条件没有命中索引?是load了不需要的数据列?还是数据量太大?
所以优化也是针对这三个方向来的,
- 首先分析语句,看看是否load了额外的数据,可能是查询了多余的行并且抛弃掉了,可能是加载了许多结果中并不需要的列,对语句进行分析以及重写.
- 分析语句的执行计划,然后获得其使用索引的情况,之后修改语句或者修改索引,使得语句可以尽可能的命中索引.
- 如果对语句的优化已经无法进行,可以考虑表中的数据量是否太大,如果是的话可以进行横向或者纵向的分表.
你们线上数据量每天有多少新增，都是存储在mysql库吗，有没做优化?
中型公司或者业务发展好的公司，一天新增几百万数据量
业务核心数据存储在Mysql里面，针对业务创建合适的索引
打点数据、日志等存储在ElasticSearch或者MongoDB里面
你创建索引的时候主要考虑啥，使用索引的优缺点有哪些，使用应该注意些什么
考虑点：结合实际的业务场景，在哪些字段上创建索引，创建什么类型的索引
索引好处：
快速定位到表的位置，减少服务器扫描的数据
有些索引存储了实际的值，特定情况下只要使用索引就能完成查询
索引缺点：
索引会浪费磁盘空间，不要创建非必要的索引
插入、更新、删除需要维护索引，带来额外的开销
索引过多，修改表的时候重构索引性能差
索引优化实践
前缀索引，特别是TEXT和BLOG类型的字段，只检索前面几个字符，提高检索速度
尽量使用数据量少的索引，索引值过长查询速度会受到影响
选择合适的索引列顺序
内容变动少，且查询频繁，可以建立多几个索引
内容变动频繁，谨慎创建索引
根据业务创建适合的索引类型，比如某个字段常用来做查询条件，则为这个字段建立索引提高查询速度
组合索引选择业务查询最相关的字段
数据库查询的指令有多个，说下执行顺序 select、where、from、group by、having、order by
from 从哪个表查询
where 初步过滤条件
group by 过滤后进行分组[重点]
having 对分组后的数据进行二次过滤[重点]
select 查看哪些结果字段
order by 按照怎样的顺序进行排序返回[重点]
select video_id,count(id) num from chapter group by video_id having num >10
order by video_id desc
零散问题
MySQL中的 varchar 和 char 有什么区别，应该怎么选择?
| 对比项 | char(16) | varchar(16) |
|---|---|---|
| 长度特点 | 长度固定，存储字符 | 长度可变，存储字符 |
| 长度不足情况 | 插入的长度小于定义长度时，则用空格填充 | 小于定义长度时，按实际插入长度存储 |
| 性能 | 存取速度比varchar快得多 | 存取速度比char慢得多 |
| 使用场景 | 适合存储很短的,固定长度的字符串,如手机号，MD5值等 | 适合用在长度不固定场景，如收货地址，邮箱地址等 |
MySQL中的 datetime 和 timestamp 有什么区别?
| 类型 | 占据字节 | 范围 | 时区问题 |
|---|---|---|---|
| datetime | 8 字节 | 1000-01-01 00:00:00到 9999-12-31 23:59:59 | 存储与时区无关，不会发生改变 |
| timestamp | 4 字节 | 1970-01-01 00:00:01 到 2038-01-19 11:14:07 | 存储的是与时区有关，随数据库的时区而发生改变 |
为什么timestamp只能到2038年
MySQL的timestamp类型是4个字节，最大值是2的31次方减1，结果是2147483647，
转换成北京时间就是2038-01-19 11:14:07
varchar(10)和int(10)代表什么含义?
varchar的10代表了申请的空间长度,也是可以存储的数据的最大长度
而int的10只是代表了展示的长度,不足10位以0填充.也就是说,int(1)和int(10)所能存储的数字大小以及占用的空间都是相同的,只是在展示时按照长度展示.
MyBatis中的#和$有什么区别?
# 会将传入的内容当做字符串,而$会直接将传入值拼接在sql语句中.
所以#可以在一定程度上预防sql注入攻击.
线上数据库的一个商品表数据量过千万，做深度分页的时候性能很慢，有什么优化思路
现象：千万级别数据很正常，比如数据流水、日志记录等，数据库正常的深度分页会很慢
慢的原因：select * from product limit N,M
MySQL执行此类SQL时需要先扫描到N行，然后再去取M行,N越大,MySQL扫描的记录数越多，SQL的性能就会越差
1、后端、前端缓存
2、使用ElasticSearch分页搜索
3、合理使用 mysql 查询缓存，覆盖索引进行查询分页
select title,cateory from product limit 1000000,100
4、如果id是自增且不存在中间删除数据，使用子查询优化，定位偏移位置的 id
select * from oper_log where type='BUY' limit 1000000,100; //5.秒
select id from oper_log where type='BUY' limit 1000000,1; // 0.4秒
select * from oper_log where type='BUY' and id>=(select id from oper_log where type='BUY' limit 1000000,1) limit 100; //0.8秒
你公司里面产品迭代更新，开发好代码和数据库，上线流程是怎样的
针对线上的数据库，你会做哪些监控，业务性能 + 数据安全 角度分析
大厂一般都有数据库监控后台，里面指标很多，但是开发人员也必须知道
业务性能
1、应用上线前会审查业务新增的sql，和分析sql执行计划
比如是否存在 select * ，索引建立是否合理
2、开启慢查询日志，定期分析慢查询日志
3、监控CPU/内存利用率，读写、网关IO、流量带宽 随着时间的变化统计图
4、吞吐量QPS/TPS，一天内读写随着时间的变化统计图
数据安全
1、短期增量备份，比如一周一次。 定期全量备份，比如一月一次
2、检查是否有非授权用户，是否存在弱口令，网络防火墙检查
3、导出数据是否进行脱敏，防止数据泄露或者黑产利用
4、数据库 全量操作日志审计，防止数据泄露
5、数据库账号密码 业务独立，权限独立控制，防止多库共用同个账号密码
6、高可用 主从架构，多机房部署
MySQL 日志
MySQL 有多少种常见的日志，分别解释日志的作用?
-
redo 重做日志
作用：确保事务的持久性，防止在发生故障，脏页未写入磁盘。重启数据库会进行redo log执行重做，到达事务一致性 -
undo 回滚日志
作用：保证数据的原子性，记录事务发生之前的数据的一个版本，用于回滚。innodb事务的可重复读和读取已提交 隔离级别就是通过mvcc+undo实现 -
errorlog 错误日志
作用：Mysql本身启动、停止、运行期间发生的错误信息 -
slow query log 慢查询日志
作用：记录执行时间过长的sql，时间阈值可以配置，只记录执行成功 -
binlog 二进制日志
作用：用于主从复制，实现主从同步 -
relay log 中继日志
作用：用于数据库主从同步，将主库发送来的binlog先保存在本地，然后从库进行回放 -
general log 普通日志
作用：记录数据库操作明细，默认关闭，开启会降低数据库性能
主从复制
画下流程图说下异步复制原理
搭建数据库主从复制的目的有哪些?
容灾使用，用于故障切换
业务需要，进行读写分离减少主库压力
为什么会有同步延迟问题，怎么解决？
保证性能第一情况下，不能百分百解决主从同步延迟问题，只能增加缓解措施。
现象：主从同步，大数据量场景下，会发现写入主库的数据，在从库没找到。
原因：
1、主从复制是单线程操作，当主库TPS高，产生的超过从库sql线程执行能力
2、从库执行了大的sql操作，阻塞等待
3、服务器硬件问题，如磁盘，CPU,还有网络延迟等
解决办法：
1、业务需要有一定的容忍度，程序和数据库直接增加缓存，降低读压力
2、业务适合的话，写入主库后，再写缓存，读的时候可以读缓存，没命中再读从库
3、读写分离，一主多从，分散主库和从库压力
4、提高硬件配置，比如使用SSD固态硬盘、更好的CPU和网络
5、进行分库分表，减少单机压力
什么场景下会出现主从数据不一致
1、本身复制延迟导致
2、主库宕机或者从库宕机都会导致复制中断
3、把一个从库提升为主库，可能导致从库和主库的数据不一致性
是否有做过主从一致性校验，你是怎么做的，如果没做过，你计划怎么做，如果不一致你会怎么修复
Mysql主从复制是基于binlog复制，难免出现复制数据不一致的风险，引起用户数据访问前后不一致的风险
所以要定期开展主从复制数据一致性的校验并修复，避免这些问题
解决方案之一，使用Percona公司下的工具
pt-table-checksum工具进行一致性校验
原理：
主库利用表中的索引，将表的数据切割成一个个chunk(块)，然后进行计算得到checksum值。
从库也执相应的操作，并在从库上计算相同数据块的checksum，然后对比主从中各个表的checksum是否一致并存储到数据库，最后通过存储校验结果的表就可以判断出哪些表的数据不一致
pt-table-sync(在从库执行)工具进行修复不一致数据，可以修复主从结构数据的不一致，也可以修复非主从结构数据表的数据不一致
原理：在主库上执行数据的更改，再同步到从库上，不会直接更改成从的数据。在主库上执行更改是基于主库现在的数据，也不会更改主库上的数据，可以同步某些表或整个库的数据，但它不同步表结构、索引，只同步不一致的数据
注意：
默认主库要检查的表在从库都存在，并且同主库表有相同的表结构
如果表中没有索引，pt-table-checksum将没法处理，一般要求最基本都要有主键索引
pt-table-sync工具会修改数据，使用前最好备份下数据，防止误操作
pt-table-checksum怎么保证某个chunk的时候checksum数据一致性？
当pt工具在计算主库上某chunk的checksum时，主库可能在更新且从库可能复制延迟，那该怎么保证主库与从库计算的是”同一份”数据，答案把要checksum的行加上for update锁并计算，这保证了主库的某个chunk内部数据的一致性
