MySQL面试题

1.MySQL优化：

        定位慢查询、SQL执行计划、索引、SQL优化经验。

        索引中包含：存储引擎、索引底层数据结构、聚簇和非聚簇索引、索引创建原则、索引失效场景等。

2.在MySQL中，如何定位慢查询？

        慢查询：聚合查询、多表查询、表数据量过大查询、深度分页查询等

        表象：页面加载过慢或接口压测响应时间过长（超过1秒）

        解决方案：

        （1）使用开源工具来定位：

                ●调试工具：Arthas

                ●运维工具：Prometheus、Skywalking

        （2）MySQL自带慢日志

          慢查询日志记录了所有执行时间超过指定参数（long_query_time,单位：秒，默认10秒）的所有SQL语句的日志，如果要开启慢查询日志，需要在MySQL的配置文件（/etc/my.cnf）中配置如下信息：

#开启MySQL慢日志查询开关
slow_query_log=1
#设置慢日志的时间为2秒，SQL语句执行时间超过2秒就会视为慢查询，记录慢查询日志
long_query_time=2

        配置完毕后，通过以下指令重新启动MySQL服务器进行测试，查看慢日志文件中记录的信息

        /var/lib/mysql/localhost-slow.log 

3.SQL语句执行很慢，你是如何为分析的呢？

        ●聚合查询--新增临时表来查询

        ●多表查询--优化SQL语句的结构

        ●表数据量过大查询--添加索引（添加索引后仍然较慢，则可分析SQL语句）

        注意：以上三种情况都可通过SQL执行计划，找到慢的原因。

        SQL执行计划，即可采用MySQL自带的分析工具EXPLAIN来进行分析。（1）通过key和key_len检查是否命中了索引（如果已经添加了索引，可以判断索引是否有时效的情况）；（2）通过type字段查看sql是否有进一步的优化空间，是否存在全索引扫描或全盘扫描；（3）通过extra建议判断是否出现了回表的情况，如果出现，可以尝试添加索引或修改返回字段来修复。

        ●深度分页查询

4.对索引了解吗？索引是什么？

        索引：帮助MySQL高效获取数据的数据结构（有序）。在数据之外，数据库系统还维护着满足特定查找算法的数据结构（B+树），这些数据结构以某种方式引用（指向）数据，这样就可以在这些数据结构上实现高级查找算法，这种数据结构就是索引。

        回答：索引是帮助MySQL高效获取数据的数据结构（有序）；提高数据检索的效率，降低数据库的IO成本（不需要全表扫描）；通过索引列对数据进行排序，降低数据排序的成本，降低CPU的消耗。

        索引的底层数据结构是什么呢？----索引的底层数据结构是B+树，

        为什么不用二叉树、红黑树或B树呢？----二叉树最坏情况下相当于一条链表，所以二叉树时间复杂度不太稳定；红黑树虽然可以保证节点平衡使时间复杂度稳定，但当数据量较大时，红黑树深度过大导致查找效率不高；B树与B+树相比，B树的非叶子节点存放的是数据，而B+树数据只存放在叶子节点中，非叶子节点存放的为索引指针。查询数据时B树会将非叶子节点的数据也加载出来，而B+树非叶子点为指针，不会被加载。所以B+树的磁盘读写代价更低、查询效率更加稳定、便于扫库和区间查询。

        回答：MySQL的InnoDB引擎采用的B+树的数据结构来存储索引；其阶数更多，路径更短；非叶子节点只存储指针，叶子节点存储数据，磁盘读写代价更低；叶子节点间是以双向链表的形式连接，便于扫库和区间查询。

5.什么是聚簇索引（聚集索引），什么是二级索引（非聚簇索引）？什么是回表？

        聚集索引（又称聚簇索引）：数据与索引放到一块，B+树的叶子节点保存了整行数据，有且只有一个。

        聚集索引选取的规则：如果存在主键，主键索引就是聚集索引；如果不存在主键，将使用第一个唯一（UNIQUE）索引作为聚集索引；如果表没有主键且没有合适的唯一索引，则InnoDB会自动生成一个rowid作为隐藏的聚集索引。

        非聚簇索引（又称二级索引）：数据与索引分开存储，B+树的叶子节点保存对应的主键，可以有多个。

        回表查询：通过二级索引找到对应的主键值，到聚集索引中查找整行数据

6.什么是覆盖索引？

        覆盖索引：指查询使用了索引，并且需要返回的列在该索引中已经全部能够找到（即不需要额外的查询）。

        回答：覆盖查询是指查询使用了索引，并且需要返回的列在该索引中已经全部能够找到；使用id查询，直接走聚集索引查询，一次索引扫描可直接返回数据，性能高；如果返回的列中没有创建索引，有可能会触发回表查询，所以开发中应尽量避免使用select *来查询。

        MySQL中超大分页怎么处理？

        问题：在数据量很大时，使用limit分页查询需要对数据进行排序，效率低。

        解决方案：覆盖查询+子查询。先分页查询获取表中的id,并对表中的id进行排序，通过排序可筛选出分页后的id集合。由于id为覆盖索引，操作效率较高，最后id集合与原来的表中做关联查询，从而提高效率。

7.索引创建原则有哪些？

        （1）数据量大，且查询比较频繁的表；

        （2）常作为查询条件、排序、分组的字段；

        （3）尽量联合索引；

        （4）要控制索引的数量；

        （5）字段内容区分度高；

        （6）内容较长，使用前缀索引；

        （7）如果索引列不能存储NULL值，请在创建表时使用NOT NULL约束它。

8.什么情况下索引会失效？

       

        （1）违反了最左前缀法则。如果索引了多列，要遵循最左前缀法则，即查询从索引的最左前列开始，并且不跳过索引中的列；

        （2）范围查询右边的列，不能使用索引；

        （3）不要在索引列上进行运算操作，索引将失效；

        （4）字符串不加单引号，造成索引失效；（由于查询时，没对字符串加单引号，MySQL的查询优化器会自动的进行类型转换，造成索引失效。）

        （5）模糊查询有可能造成索引失效。以%开头的Like模糊查询,索引失效。如果仅仅是尾部模糊匹配，索引不会失效。如果是头部模糊匹配，索引失效。

9.谈一谈你对sql优化的经验？

        （1）表的设计优化（参考阿里开发手册《嵩山版》）

                ●比如设置合适的数值（tinyint int bigint），要根据实际情况选择；

                ●比如设置合适的字符串类型（char和Varchar）char定长效率高，varchar可变长度效率低。

        （2）SQL语句优化

                ●SELECT语句务必指明字段名称（避免直接使用select *）；

                ●SQL语句要避免造成索引失效的写法；

                ●尽量用union all代替union，union会多一次过滤，效率低；

                ●避免在where子句中对字段进行表达式操作；

                ●Join优化能用innerjoin就不用left join和right join，如必须使用一定要以小表为驱动，内连接会对两个表进行优化，优化把小表放到外面，把大表放到里面。left join或right join不会重新调整顺序。

        （3）索引优化----参考索引的创建原则和索引失效的注意事项

        （4）主从复制、读写分离

                ●如果数据库的使用场景读的操作比较多的时候，为了避免写的操作所造成的性能影响，可以采用读写分离的架构。读写分离解决的是数据库的写入影响了查询的效率。

        （5）分库分表

10.事务的特性是什么？可以详细说一下吗?

        事务：是一组操作的集合，是一个不可分割的工作单位。事务会把所有操作作为一个整体一起向系统提交或撤销操作请求，即这些操作要么同时成功，要么同时失败。

        ●原子性（Atomicity）：事务是不可分割的最小操作单元，要么全部成功，要么全部失败。

        ●一致性（Consistency）：事务完成时，必须使所有的数据都保持一致状态。

        ●隔离性（Isolation）：数据库系统提供的隔离机制，保证事务在不受外部并发操作影响的独立环境下运行。

        ●持久性（Durability）：事务一旦提交或回滚，它对数据库中数据的改变就是永久的。

11.并发事务带来了哪些问题？怎么解决这些问题？MySQL的默认隔离级别是什么？

        并发事务问题：脏读、不可重复读（读取了其他事务更改的数据，针对Update操作）、幻读（读到了其他事务新增的数据，针对insert和delete操作）。

        隔离级别：读未提交、读已提交、可重复读（默认隔离级别，不能解决幻读）、串行化。

12.undo log和redo log的区别？

        ●缓冲池（buffer pool）：主内存中的一个区域，里面可以缓存磁盘上经常操作的真实数据，在执行增删改查操作时，先操作缓冲池中的数据（若缓冲池没有数据，则从磁盘加载并缓存），并以一定频率刷新到磁盘，从而减少磁盘IO加快处理速度。

        ●数据页（page）：是InnoDB存储引擎磁盘管理的最小单元，每个页的默认大小为16KB，页中存储的是行数据。

        回答：

                ●redo log：记录的是数据也的物理变化，当服务宕机时，可用来同步数据。

                ●undo log：记录的是逻辑日志，当事务回滚时，通过逆操作恢复原来的数据。

                ●redo log保证了事务的持久性，undo log保证了事务的原子性和一致性。

13.事务中的隔离性是如何保证的呢？

        锁：排他锁（如果一个事务获取了一个数据行的排他锁，则其他事务就不能在获取该行的其他锁）。

        mvcc：多版本并发控制。指维护一个数据的多个版本，使得读写操作没有冲突。

                ●隐藏字段

                        ①trx_id(事务id)，记录每一次操作的事务id，是自增。

                        ②roll_pointer(回滚指针)，指向上一个版本的事务版本记录地址。

                ●undo log

                        ①回滚日志，存储老版本数据。

                        ②版本链：多个事务并行操作某一行记录，记录不同事务修改数据的版本，通过roll_pointer指针形成一个链表。

                ●readView解决的是一个事务查询选择版本问题

                        ①根据readView的匹配规则和当前的一些事务id判断该访问那个版本的数据。

                        ②不同的隔离级别快照读是不一样的，最终的访问结果不一样：

                        RC：每一次执行快照读时生成ReadView。

                        RR：仅在事务中第一次执行快照读时生成ReadView，后续复用。

14.MySQL主从同步原理是什么？

        MySQL主从复制的核心就是二进制日志（又称BINGLOG，记录所有的DDL语句和DML语句，但不包括数据查询语句）。

        回答：

                （1）Master主库在事务提交时，会把数据变更记录在二进制日志文件Binglog中。

                （2）从库读取主库的二进制日志文件Binglog，写入到从库的中继日志Relay Log。

                （3）slave重做中继日志中事件，将改变反映它自己的数据。

15.你们项目中用过分库分表吗？

        拆分策略：

        ●垂直分库：以表为依据，根据业务将不同的表拆分到不同库中。

          特点：按业务对数据分级管理、维护、监控和扩展；在高并发下，提高磁盘IO和数据量连接数。

        ●垂直分表：以字段为依据，根据字段属性将不同字段拆分到不同的表中。（拆分规则：把不常用的字段单独放在一张表中；把text、blob等大字段拆分出来放在附表中。）

          特点：冷热数据分离；减少IO过渡争抢，两表互不影响。

        ●水平分库：将一个库的数据拆分到多个库中。

          特点：解决了单库大数量高并发的性能瓶颈问题；提高了系统的稳定性和可用性。

       ● 水平分表：将一个表的数据拆分到多个表中（可以在同一个库内）。

          特点：优化单一表数据量过大而产生的性能问题；避免IO争抢并减少锁表的几率。

        分库之后的问题：

                （1）分布式事务一致性问题；

                （2）跨节点关联查询；

                （3）跨节点分页、排序函数；

                （4）主键避重。

        分库中间件（水平分库分表需要使用）：sharding-sphere、mycat