mysql面试常见问题及答案：Mysql实战总结面试20问
mysql面试常见问题及答案：Mysql实战总结面试20问1）B 树非叶子节点上是不存储数据的，仅存储键值，而B树节点中不仅存储键值，也会存储数据。innodb中页的默认大小是16KB，如果不存储数据，那么就会存储更多的键值，相应的树的阶数（节点的子节点树）就会更大，树就会更矮更胖，如此一来我们查找数据进行磁盘的IO次数有会再次减少，数据查询的效率也会更快。那为什么不是B树而是B 树呢？如果二叉树特殊化为一个链表，相当于全表扫描。平衡二叉树相比于二叉查找树来说，查找效率更稳定，总体的查找速度也更快。为什么不是平衡二叉树呢？我们知道，在内存比在磁盘的数据，查询效率快得多。如果树这种数据结构作为索引，那我们每查找一次数据就需要从磁盘中读取一个节点，也就是我们说的一个磁盘块，但是平衡二叉树可是每个节点只存储一个键值和数据的，如果是B树，可以存储更多的节点数据，树的高度也会降低，因此读取磁盘的次数就降下来啦，查询效率就快啦。
1、mysql索引使用注意事项
1.1、 索引哪些情况会失效- 查询条件包含or，可能导致索引失效
- 如果字段类型是字符串，where时一定用引号括起来，否则索引失效
- like通配符可能导致索引失效。
- 联合索引，查询时的条件列不是联合索引中的第一个列，索引失效。
- 在索引列上使用mysql的内置函数，索引失效。
- 对索引列运算（如， 、-、*、/），索引失效。
- 索引字段上使用（！= 或者 < >，not in）时，可能会导致索引失效。
- 索引字段上使用is null， is not null，可能导致索引失效。
- 左连接查询或者右连接查询查询关联的字段编码格式不一样，可能导致索引失效。
- mysql估计使用全表扫描要比使用索引快 则不使用索引。
- 数据量少的不适合加索引
- 更新比较频繁的也不适合加索引
- 区分度低的字段不适合加索引（如性别） 对比 部件表 rest_status_id 就不需要添加索引。
- 索引区分度越明显，查询效率越高，可以使用以下公示，预估索引是否需要添加：
区分度 = 用到索引的记录数 / 总数 （select count(*) from a where idx = xxxx / select count(*)）
2、InnoDB与MyISAM的区别- InnoDB支持事务，MyISAM不支持事务
- InnoDB支持外键，MyISAM不支持外键
- InnoDB 支持 MVCC(多版本并发控制)，MyISAM 不支持
- select count(*) from table时，MyISAM更快，因为它有一个变量保存了整个表的总行数，可以直接读取，InnoDB就需要全表扫描。
- Innodb不支持全文索引，而MyISAM支持全文索引（5.7以后的InnoDB也支持全文索引）
- InnoDB支持表、行级锁，而MyISAM支持表级锁。
- InnoDB表必须有主键，而MyISAM可以没有主键
- Innodb表需要更多的内存和存储，而MyISAM可被压缩，存储空间较小，。
- Innodb按主键大小有序插入，MyISAM记录插入顺序是，按记录插入顺序保存。
- InnoDB 存储引擎提供了具有提交、回滚、崩溃恢复能力的事务安全，与 MyISAM 比 InnoDB 写的效率差一些，并且会占用更多的磁盘空间以保留数据和索引
可以从几个维度去看这个问题，查询是否够快，效率是否稳定，存储数据多少，以及查找磁盘次数，为什么不是二叉树，为什么不是平衡二叉树，为什么不是B树，而偏偏是B 树呢？
为什么不是一般二叉树？
如果二叉树特殊化为一个链表，相当于全表扫描。平衡二叉树相比于二叉查找树来说，查找效率更稳定，总体的查找速度也更快。
为什么不是平衡二叉树呢？
我们知道，在内存比在磁盘的数据，查询效率快得多。如果树这种数据结构作为索引，那我们每查找一次数据就需要从磁盘中读取一个节点，也就是我们说的一个磁盘块，但是平衡二叉树可是每个节点只存储一个键值和数据的，如果是B树，可以存储更多的节点数据，树的高度也会降低，因此读取磁盘的次数就降下来啦，查询效率就快啦。
那为什么不是B树而是B 树呢？
1）B 树非叶子节点上是不存储数据的，仅存储键值，而B树节点中不仅存储键值，也会存储数据。innodb中页的默认大小是16KB，如果不存储数据，那么就会存储更多的键值，相应的树的阶数（节点的子节点树）就会更大，树就会更矮更胖，如此一来我们查找数据进行磁盘的IO次数有会再次减少，数据查询的效率也会更快。
2）B 树索引的所有数据均存储在叶子节点，而且数据是按照顺序排列的，链表连着的。那么B 树使得范围查找，排序查找，分组查找以及去重查找变得异常简单。
4、limit 1000000加载很慢的话，怎么解决的呢方案一：如果id是连续的，可以这样，返回上次查询的最大记录(偏移量)，再往下limit
连续id:
select id，name from employee where id>1000000 limit 10
方案二：order by 索引（id为索引）
order by 主键:
select id，name from employee order by id limit 1000000，10
方案三：利用延迟关联或者子查询优化超多分页场景。（先快速定位需要获取的id段，然后再关联）
联表查询：
SELECT a.* FROM employee a (select id from employee where 条件 LIMIT 1000000 10 ) b where a.id=b.id
复合索引，也叫组合索引，用户可以在多个列上建立索引 这种索引叫做复合索引。
当创建一个组合索引的时候，如(k1 k2 k3)，相当于创建了（k1）、(k1 k2)和(k1 k2 k3)三个索引，这就是最左匹配原则。
联合索引：
select * from table where k1=A AND k2=B AND k3=D
举例说明下吧：
假设表A表示某企业的员工表，表B表示部门表，查询所有部门的所有员工，很容易有以下SQL:
in 操作：
select * from A where deptId in (select deptId from B);
等价于：
先查询部门表B
select deptId from B
再由部门deptId，查询A的员工
select * from A where A.deptId = B.deptId
显然，除了使用in，我们也可以用exists实现一样的查询功能，如下：
exists查询：
select * from A where exists (select 1 from B where A.deptId = B.deptId);
因为exists查询的理解就是，先执行主查询，获得数据后，再放到子查询中做条件验证，根据验证结果（true或者false），来决定主查询的数据结果是否得意保留。
执行相当于 ：
select * from A 先从A表做循环
select * from B where A.deptId = B.deptId 再从B表做循环.
结论：
exists 与 in 操作正好相反。。。。。
数据库最费劲的就是跟程序链接释放。假设链接了两次，每次做上百万次的数据集查询，查完就走，这样就只做了两次；相反建立了上百万次链接，申请链接释放反复重复，这样系统就受不了了。
即mysql优化原则，就是小表驱动大表，小的数据集驱动大的数据集，从而让性能更优。
因此，我们要选择最外层循环小的，也就是，如果B的数据量小于A，适合使用in，如果B的数据量大于A，即适合选择exists，这就是in和exists的区别
7、创建索引有什么原则- 最左前缀匹配原则
- 频繁作为查询条件的字段才去创建索引
- 频繁更新的字段不适合创建索引
- 索引列不能参与计算，不能有函数操作
- 优先考虑扩展索引，而不是新建索引，避免不必要的索引
- 在order by或者group by子句中，创建索引需要注意顺序
- 区分度低的数据列不适合做索引列(如性别）
- 定义有外键的数据列一定要建立索引。
- 对于定义为text、image数据类型的列不要建立索引。
- 删除不再使用或者很少使用的索引
- 想要删除百万数据的时候可以先删除索引
- 然后批量删除其中无用数据
- 删除完成后重新创建索引。
Q：没明白为什么要先删除索引。。。。我的理解，先删除，不就锁表了吗？
还有一种删除思路：
删除百万数据：
create table test_a like test_b; 会保留索引相关的信息
create table test_a as test_b; 不推荐
insert into test_a select * from test_b where id >0 and id<50000 # 50000条数据导一次
最后
rename table test_b to test_b_bak 备份
rename table test_a to test_b
- 覆盖索引：查询列要被所建的索引覆盖，不必从数据表中读取，换句话说查询列要被所使用的索引覆盖。
- 回表：二级索引无法直接查询所有列的数据，所以通过二级索引查询到聚簇索引后，再查询到想要的数据，这种通过二级索引查询出来的过程，就叫做回表。
- 在B 树的索引中，叶子节点可能存储了当前的key值，也可能存储了当前的key值以及整行的数据，这就是聚簇索引和非聚簇索引。在InnoDB中，只有主键索引是聚簇索引，如果没有主键，则挑选一个唯一键建立聚簇索引。如果没有唯一键，则隐式的生成一个键来建立聚簇索引。
- 当查询使用聚簇索引时，在对应的叶子节点，可以获取到整行数据，因此不用再次进行回表查询。
|
delete |
truncate |
drop | |
|
类型 |
DML |
DDL |
DDL |
|
回滚 |
可回滚 |
不可回滚 |
不可回滚 |
|
删除内容 |
表结构还在，删除表的全部或者一部分数据行 |
表结构还在，删除表中的所有数据 |
从数据库中删除表，所有的数据行，索引和权限也会被删除 |
|
删除速度 |
删除速度慢，逐行删除 |
删除速度快 |
删除速度最快 |
- Union：对两个结果集进行并集操作，不包括重复行，同时进行默认规则的排序；
- Union All：对两个结果集进行并集操作，包括重复行，不进行排序；
- UNION的效率高于 UNION ALL
图片上传失败。。
14.MySQL数据库cpu飙升，怎么处理
排查过程：
- 使用top 命令观察，确定是mysqld导致还是其他原因。
- 如果是mysqld导致的，show processlist，查看session情况，确定是不是有消耗资源的sql在运行。
- 找出消耗高的 sql，看看执行计划是否准确， 索引是否缺失，数据量是否太大。
处理：
- kill 掉这些线程(同时观察 cpu 使用率是否下降)，
- 进行相应的调整(比如说加索引、改 sql、改内存参数)
- 重新跑这些 SQL。
主从复制原理，简言之，就三步曲，如下：
- 主数据库有个bin-log二进制文件，记录了所有增删改Sql语句。（binlog线程）
- 从数据库把主数据库的bin-log文件的sql语句复制过来。（io线程）
- 从数据库的relay-log重做日志文件中再执行一次这些sql语句。（Sql执行线程）
图片上传失败。。
上图主从复制分了五个步骤进行：
步骤一：主库的更新事件(update、insert、delete)被写到binlog
步骤二：从库发起连接，连接到主库。
步骤三：此时主库创建一个binlog dump thread，把binlog的内容发送到从库。
步骤四：从库启动之后，创建一个I/O线程，读取主库传过来的binlog内容并写入到relay log
步骤五：还会创建一个SQL线程，从relay log里面读取内容，从ExecMasterLog_Pos位置开始执行读取到的更新事件，将更新内容写入到slave的db
16、MySQL的ExplainExplain 执行计划包含字段信息如下：分别是 id、selecttype、table、partitions、type、possiblekeys、key、key_len、ref、rows、filtered、Extra 等12个字段。
重点关注的是type，它的属性排序如下：
system > const > eq_ref > ref > ref_or_null >index_merge > unique_subquery > index_subquery >range > index > ALL
17、Innodb的事务与日志的实现方式innodb两种日志redo和undo
日志的存放形式
- redo：在页修改的时候，先写到 redo log buffer 里面， 然后写到 redo log 的文件系统缓存里面(fwrite)，然后再同步到磁盘文件（ fsync）。
- undo：在 MySQL5.5 之前， undo 只能存放在 ibdata文件里面， 5.6 之后，可以通过设置 innodbundotablespaces 参数把 undo log 存放在 ibdata之外。
事务是如何通过日志来实现的
- 因为事务在修改页时，要先记 undo，在记 undo 之前要记 undo 的 redo， 然后修改数据页，再记数据页修改的 redo。Redo（里面包括 undo 的修改） 一定要比数据页先持久化到磁盘。
- 当事务需要回滚时，因为有 undo，可以把数据页回滚到前镜像的 状态，崩溃恢复时，如果 redo log 中事务没有对应的 commit 记录，那么需要用 undo把该事务的修改回滚到事务开始之前。
- 如果有 commit 记录，就用 redo 前滚到该事务完成时并提交掉。
18、数据库是否支持emoji表情存储，如果不支持，如何操作
更换字符集utf8→utf8mb4 （5.6以上的版本才支持）
19、一个6亿的表a，一个3亿的表b，通过外间tid关联，你如何最快的查询出满足条件的第50000到第50200中的这200条数据记录
1、如果A表TID是自增长 并且是连续的 B表的ID为索引
大数据快速查询01：
select * from a b where a.tid = b.id and a.tid>500000 limit 200;
2、如果A表的TID不是连续的 那么就需要使用覆盖索引.TID要么是主键 要么是辅助索引 B表ID也需要有索引。
大数据快速查询02：
select * from b (select tid from a limit 50000 200) a where b.id = a .tid;
- 原子性：是使用 undo log来实现的，如果事务执行过程中出错或者用户执行了rollback，系统通过undo log日志返回事务开始的状态。
- 持久性：使用 redo log来实现，只要redo log日志持久化了，当系统崩溃，即可通过redo log把数据恢复。
- 隔离性：通过锁以及MVCC 使事务相互隔离开。
- 一致性：通过回滚、恢复，以及并发情况下的隔离性，从而实现一致性
关注不迷路~持续输出~
