MySQL面试题经典40问！
https://www.zhihu.com/people/xu-zhi-mo-16-92/posts
1、什么是数据库事务？
数据库事务: 是数据库管理系统执行过程中的一个逻辑单位，由一个有限的数据库操作序列构成，这些操作要么全部执行,要么全部不执行，是一个不可分割的工作单位。
2、Mysql事务的四大特性是什么？
- 原子性： 事务作为一个整体被执行，包含在其中的对数据库的操作要么全部被执行，要么都不执行。
- 一致性： 指在事务开始之前和事务结束以后，数据不会被破坏，假如A账户给B账户转10块钱，不管成功与否，A和B的总金额是不变的。
- 隔离性： 多个事务并发访问时，事务之间是相互隔离的，即一个事务不影响其它事务运行效果。
- 持久性： 表示事务完成以后，该事务对数据库所作的操作更改，将持久地保存在数据库之中。
3、事务ACID特性的实现原理？
原子性：是使用 undo log 来实现的，如果事务执行过程中出错或者用户执行了rollback，系统通过undo log日志返回事务开始的状态。
持久性：使用 redo log 来实现，只要redo log日志持久化了，当系统崩溃，即可通过redo log把数据恢复。
隔离性：通过锁以及 MVCC,使事务相互隔离开。
一致性：通过回滚、恢复，以及并发情况下的隔离性，从而实现一致性。4、事务的隔离级别有哪些？
- 读未提交（Read Uncommitted）最低级别，任何情况都无法保证
- 读已提交（Read Committed）可避免脏读的发生
- 可重复读（Repeatable Read）可避免脏读、不可重复读的发生
- 串行化（Serializable）可避免脏读、不可重复读、幻读的发生
5、什么是脏读、不可重复读、幻读呢？
- 脏读: 脏读指的是读到了其他事务未提交的数据，未提交意味着这些数据可能会回滚，也就是可能最终不会存到数据库中，也就是不存在的数据。读到了并不一定最终存在的数据，这就是脏读。
- 不可重复读: 不可重复读指的是在一个事务内，最开始读到的数据和事务结束前的任意时刻读到的同一批数据出现不一致的情况。
- 幻读: 幻读，并不是说两次读取获取的结果集不同，幻读侧重的方面是某一次的 select 操作得到的结果的数据状态无法支撑后续的业务操作。更为具体一些：select 某记录是否存在，不存在，准备插入此记录，但执行 insert 时发现此记录已存在，无法插入，此时就发生了幻读。
6、datetime和timestamp的区别？
它们和date的区别在于: date存储精度到天，它们存储精度都为秒。
它们的区别在于：
- datetime 的日期范围是 1001——9999 年；timestamp 的时间范围是 1970——2038 年
- datetime 存储时间与时区无关；timestamp 存储时间与时区有关，显示的值也依赖于时区
- datetime 的存储空间为 8 字节；timestamp 的存储空间为 4 字节
- datetime 的默认值为 null；timestamp 的字段默认不为空(not null)，默认值为当前时间(current_timestamp)
7、varchar和char有什么区别？
8、count(1)、count(*) 与 count(列名) 的区别？
- count(*)包括了所有的列，相当于行数，在统计结果的时候，不会忽略列值为NULL
- count(1)包括了忽略所有列，用1代表代码行，在统计结果的时候，不会忽略列值为NULL
- count(列名)只包括列名那一列，在统计结果的时候，会忽略列值为空（这里的空表示null）的计数，即某个字段值为NULL时，不统计。
阿里巴巴Java开发手册有一条强制建议：不要使用count(列名)或count(常量)来代替count(*)。count(*)就是SQL92定义的标准统计行数语法，跟数据库无关。
9、exist和in的区别？
使用in时,sql语句是先执行子查询，也就是先查询子表b，再查主表a。
10、truncate、delete与drop区别？
相同点：
truncate和不带where自居的delete，以及drop都会删除表内的数据。
二、不同点：
truncate和delete只删除数据不删除表的结构(定义)，而drop语句将删除表的结构被依赖的约束(constrain)，
delete命令是DML，删除的数据将存储在系统回滚段中，需要的时候，数据可以回滚恢复。
而truncate，drop命令是DDL，删除的数据是操作立即生效，原数据不放到rollback segment中，不能回滚，数据不可以回滚恢复。
11、 union与union all的区别？
- Union：对两个结果集进行并集操作，不包括重复行，同时进行默认规则的排序；
- Union All：对两个结果集进行并集操作，包括重复行，不进行排序；
从效率上说，union all 要比union快很多，所以，如果可以确认合并的两个结果集中不包含重复的数据的话，那么就使用union all
12、group by 和 distinct 的区别？
distinct 是先获取结果集，再去重复记录。group by 是基于KEY先分组，再返回计算结果。
那为什么，大家都更推崇使用group by？
- group by语义更为清晰
- group by可对数据进行更为复杂的一些处理
13、Blob和text有什么区别？
Blob 用于存储二进制数据，而 Text 用于存储大字符串
14、常见的存储引擎有哪些
MyISAM, InnoDB、MEMORY。Mysql5.5 版本之后的默认存储引擎是 InnoDB
15、InnoDB 与 MyISAM 的区别？
1、InnoDB 支持事务，MyISAM 不支持事务
2、MyISAM 只支持表级锁，InnoDB 支持事务和行级锁。但是 InnoDB 的行锁，只有在索引上可能是行锁，否则还是表锁
3、索引结构
1、InnoDB 引擎使用 B+ 树作为索引结构，InnoDB 的数据文件本身就是索引文件，叶节点 data 完整的保存了数据记录。这个索引的 key 是数据表的主键，因此 InnoDB 表数据本身就是主索引，InnoDB 的辅助索引的 data 域存储相应记录主键的值而不是地址。
2、MyISAM 引擎使用 B+树作为所以结构，叶节点的 data 存放的是 数据记录地址，即 MyISAM 索引文件和数据文件是分离的，MyISAM 的索引文件仅仅保存数据记录的地址。
MyISAM 中索引检索的算法为首先按照 B+ 树搜索算法搜索索引，如果指定的 Key 存在，则取出 data 域的值，然后以 data 域的值为地址，读取相应的数据。MyISAM 的索引方式也叫 做“非聚集”的
4、表主键
1、MyISAM 允许没有任何索引主键的表存在，索引都是保存行的地址。
2、InnoDB ：如果没有设定主键或非空唯一索引，就会自动生成一个6字节主键（用户不可见），数据是主索引的一部分，其他索引保存的是主索引的值。
5、表的具体行数
MyISAM : 保存有表总行数，如果 select count(*) from table; 会直接取出该值
InnoDB : 没有保存表的总行数（只能遍历）。
6、外键
MyISAM : 不支持
InnoDB : 支持
16 bin log、redo log 、undo log 是什么
bin log 是在服务层
redo log 、undo log 是在引擎层
1、bin log
bin log 是 Mysql 数据库级别的文件，记录对 Mysql 数据库执行修改的所有操作，不会记录 select 和 show 语句。使用任何存储引擎的 Mysql 数据库都会记录 binlog 日志。
在实际的应用中，binlog 的主要使用场景有两个，分别是 主从复制 和 数据恢复
主从复制：在 Master 端开启 binlog，然后将 binlog 发送各个 slave 端，从而达到主从数据一致。数据恢复：通过使用 Mysqlbinlog 工具来恢复数据。
2、redo log
redo log 中记录的是要更新的数据，比如一条数据已经提交commit，并不会立即同步到磁盘，而是先记录到 redo log 中，等待何时的机会再刷盘
如果没有 redo log ，那么每次事务提交的时候，将该事物及修改的数据页全部刷新到磁盘中，但是这么做有两个问题：
1、InnoDB 是以页为单位和磁盘交互的，而一个事物可能只有一个数据页的几个字节，这个时候讲完整的数据页刷到磁盘中，太浪费资源了。
2、一个事物可能涉及到多个数据页，而且这些数据页在物理上并不连续，使用随机 IO 写入性能太差
因此 Mysql 设计了 redo log ， 具体来说就是只记录事务对数据页做了哪些修改，这样就能完美地解决性能问题了(相对而言文件更小并且是顺序IO)。
3、undo log
undo log 用于数据的回滚操作，它保证了数据修改前的内容，通过 undo log 可以实现事物的回滚到某个特定的版本
17、bin log 和 redo log 有什么区别
1、bin log会记录所有日志记录，包括InnoDB、MyISAM等存储引擎的日志；redo log只记录innoDB自身的事务日志。
2、bin log只在事务提交前写入到磁盘，一个事务只写一次；而在事务进行过程，会有redo log不断写入磁盘。
3、bin log是逻辑日志，记录的是SQL语句的原始逻辑；redo log是物理日志，记录的是在某个数据页上做了什么修改。
18、说一下数据库的三大范式？
第一范式：数据表中的每一列（每个字段）都不可以再拆分。
第二范式：在第一范式的基础上，非主键列完全依赖于主键，而不能是依赖于主键的一部分。
第三范式：在满足第二范式的基础上，表中的非主键只依赖于主键，而不依赖于其他非主键。
19 什么是存储过程？有哪些优缺点？
存储过程，就是一些编译好了的 SQL 语句，这些 SQL 语句像一个方法一样实现一些功能（对单表或者多表的增删改查）, 然后给这些代码块取一个名字，在用到这个功能的时候调用即可。
优点：
1、存储过程是一个预编译的代码块，执行效率比较高
2、存储过程在服务器端运行，减少客户端压力
3、主要创建一次存储过程，以后在程序中就可以调用任意多次
4、一个存储过程可以代替大量的 SQL 语句，降低网络通信两，提高通信效率
缺点：
1、调试麻烦
2、可移植不灵活
3、重新编译问题
20 主键使用自增ID还是UUID?
推荐使用自增ID，不要使用 UUID
因为在 InnoDB 存储引擎中，主键索引是作为聚簇索引存在的，也就是说，主键索引的 B+树叶子节点上存储主键索引以及全部的数据（按照顺序），如果索引是自增的ID，那么只需要不断向后排列即可，如果是 UUID，由于 UUID 与原来的大小不确定，会造成非常多的数据插入，数据移动，然后导致产生很多问题的内存碎片，进而造成插入的性能下降。
21 超大分页怎么处理?
1、用 id 优化
先找到上次分页的最大 ID， 然后利用 id 上的索引查询
select * from user where id>1000000 limit 100
这样的效率非常快,因为主键上是有索引的,但是这样有个缺点,就是ID必须是连续的,并且查询不能有where语句,因为where语句会造成过滤数据。
2、用覆盖索引
mysql 的查询完全命中索引的时候，称为覆盖索引，是非常快的，因为查询只需要在索引上进行查找，之后可以直接返回，而不用回表去拿数据。因此我们
先查出索引 ID，然后根据 id 拿数据
select * from table where id in (select id from table where age > 20 limit 1000000,10)
3、在业务允许的情况下限制页数
建议跟业务讨论，有没有必要查这么后的分页啦。因为绝大多数用户都不会往后翻太多页。
22 、一个6亿的表a，一个3亿的表b，通过 tid 关联（a.tid = b.id），你如何最快查询出满足表a第 50000条到 50200 中的这 200 条数据记录
这是一道腾讯的面试题，其实这个问题和上面是同一个问题，都是超大分页的问题,这就像读书的时候做数学题一样，上面是公式、定理，下面是题目，所以要学会举一反三。
1、如果 A 表 tid 是自增长，并且是连续的，B表的 id 为索引
select * from a,b where a.tid = b.id and a.tid > 50000 limit 200;
2、如果 a 表的 tid 不是连续的，那么使用覆盖索引
select * from b, (select tid form a limit 50000, 200) a where b.id = a.tid
23、日常开发中你是怎么优化SQL的？
这个问题问的挺大的，那么我们可以也先从几个大的纬度来回答。
- 添加合适索引
- 优化表结构
- 优化查询语句
1、添加合适索引
- 对作为查询条件和order by的字段建立索引。
- 对于多个查询字段的考虑建立组合索引，同时注意组合索引字段的顺序,将最常用作限制条件的列放在最左边，依次递减。
- 索引不宜太多，一般5个以内。
2、优化表结构
选择正确的数据类型，对于提高性能也是至关重要。下面给出几种原则:
- 数字型字段优于字符串类型
若只含数值信息的字段尽量不要设计为字符型，这会降低查询和连接的性能，并会增加存储开销。
- 数据类型更小通常更好
使用最小的数据类型,会减少磁盘的空间，内存和CPU缓存。好比时间类型尽量使用TIMESTAMP类型，因为其存储空间只需要 DATETIME 类型的一半。对于只需要精确到某一天的数据类型，建议使用DATE类型，因为他的存储空间只需要3个字节，比TIMESTAMP还少。
- 尽量使用 NOT NULL
NULL 类型比较特殊，SQL 难优化。如果是一个组合索引，那么这个NULL 类型的字段会极大影响整个索引的效率。此外，NULL 在索引中的处理也是特殊的，也会占用额外的存放空间。
3、优化查询语句
- 分析语句，是否加载了不必要的字段/数据。
- 分析SQl执行计划，是否命中索引等。
- 如果SQL很复杂，优化SQL结构
- 如果表数据量太大，考虑分表
24、关心过业务系统里面的sql耗时吗?统计过慢查询吗?对慢查询都怎么优化过?（explain）？
在业务系统中,除了使用主键进行的查询,其他的我都会在测试库上测试其耗时,慢查询的统计主要由运维在做,会定期将业务中的慢查询反馈给我们。
25 一条sql执行过长的时间，你如何优化，从哪些方面入手？
- 查看是否涉及多表和子查询，优化Sql结构，如去除冗余字段，是否可拆表等
- 优化索引结构，看是否可以适当添加索引
- 数量大的表，可以考虑进行分离/分表（如交易流水表）
- 数据库主从分离，读写分离
- explain分析sql语句，查看执行计划，优化sql
- 查看Mysql执行日志，分析是否有其他方面的问题
上面这3道面试题也是差不多的，只是问的方式不一样，可以总结出自己的话术来表达。
26 产生临时表的原因有哪些？
你在对sql分析语句的执行计划(explain)的时候，发现extra中有Using temporary,那就说明使用了临时表保存中间结果，Mysql在对查询结果排序时使用临时表。出现这种情况我们就需要看下能不能优化一下了。
那首先知道什么情况下会产生临时表
1)ORDER BY子句和GROUP BY子句不同， 例如：ORDERY BY price GROUP BY name；
2)ORDER BY中使用了DISTINCT关键字 ORDERY BY DISTINCT(price)
3）直接使用磁盘临时表的场景
- 表包含TEXT或者BLOB列；
- GROUP BY 或者 DISTINCT 子句中包含长度大于512字节的列；
- 使用UNION或者UNION ALL时，SELECT子句中包含大于512字节的列；
27 如何解决临时表问题呢？
使用临时表一般都意味着性能比较低，特别是使用磁盘临时表，性能更慢，因此我们在实际应用中应该尽量避免临时表的使用。 常见的避免临时表的方法有：
1)创建索引：在ORDER BY或者GROUP BY的列上创建索引；
2）如果你的varvhar2，字节数是否超过512字节，看能否修改。
3)分拆很长的列：一般情况下，TEXT、BLOB，大于512字节的字符串，基本上都是为了显示信息，而不会用于查询条件， 因此表设计的时候，应该将这些列独立到另外一张表。
28 如何选择合适的分布式主键方案呢？
- 数据库自增长序列或字段。
- UUID。
- Redis生成ID
- Twitter的snowflake算法
- 利用zookeeper生成唯一ID
- MongoDB的ObjectId
29 说一下大表查询的优化方案
- 优化shema、sql语句+索引；
- 可以考虑加缓存，memcached, redis，或者JVM本地缓存；
- 主从复制，读写分离；
- 分库分表；
30 百万级别或以上的数据，你是如何删除的？
- 我们想要删除百万数据的时候可以先删除索引
- 然后批量删除其中无用数据
- 删除完成后重新创建索引。
31 为什么要分库分表？
分表
比如你单表都几千万数据了，你确定你能扛住么？绝对不行，单表数据量太大，会极大影响你的 sql 执行的性能，到了后面你的 sql 可能就跑的很慢了。一般来说，就以我的经验来看，单表到几百万的时候，性能就会相对差一些了，你就得分表了。
分表是啥意思？就是把一个表的数据放到多个表中，然后查询的时候你就查一个表。比如按照用户 id 来分表，将一个用户的数据就放在一个表中。然后操作的时候你对一个用户就操作那个表就好了。这样可以控制每个表的数据量在可控的范围内，比如每个表就固定在 200 万以内。
分库
分库是啥意思？就是你一个库一般我们经验而言，最多支撑到并发 2000，一定要扩容了，而且一个健康的单库并发值你最好保持在每秒 1000 左右，不要太大。那么你可以将一个库的数据拆分到多个库中，访问的时候就访问一个库好了。
32 说说分库与分表的设计？
分库分表方案:
水平分库：以字段为依据，按照一定策略（hash、range等），将一个库中的数据拆分到多个库中。
水平分表：以字段为依据，按照一定策略（hash、range等），将一个表中的数据拆分到多个表中。
垂直分库：以表为依据，按照业务归属不同，将不同的表拆分到不同的库中。
垂直分表：以字段为依据，按照字段的活跃性，将表中字段拆到不同的表（主表和扩展表）中。
33 分库分表数据分片规则？
我们在考虑去水平切分表，将一张表水平切分成多张表，这就涉及到数据分片的规则，比较常见的有：Hash取模分表、数值Range分表、一致性Hash算法分表。
