面试题MYSQL篇

 

 如何定位慢查询：

        

 方案一：开源工具

        调试工具：Arthas

        运维工具：Prometheus、Skywalking

        

 方案二：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

面试问题：

如何定位慢查询：

1.介绍一下当时产生问题的场景（我们当时的一个接口测试的时候非常的慢，压测的结果大概5秒钟）

2.我们系统中当时采用了运维工具（Skywalking），可以监测出哪个接口，最终是因为sql的问题

3.在mysql中开启了慢日志查询，我们设置的值就是2秒，一旦sql执行超过2秒就会记录到日志中（调试阶段）

 一个SQL语句执行很慢，如何分析

可以采用EXPLAIN或者DESC命令获取MySQL如何执行SELECT语句的信息

语法：

直接在select语句之前加上关键字explain/desc

EXPLAIN SELECT 字段列表 FROM 表名 WHERE 条件；

         

 

面试题：

 

什么是索引？

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

 B-Tree

        B-Tree，B树是一种多叉路衡查找树，相对于二叉树，B树每个节点可以有多个分支，即多叉

 

 B+Tree 

        B+Tree是在BTree基础上的一种优化，使其更适合实现外存储索引结构，InnoDB存储引擎就是用B+Tree实现其索引结构

         

        B树与B+树对比：

        1.磁盘读写代价B+树更低；（只有叶子节点才会存储数据，非叶子节点都是指针）2.查询效率B+树更加稳定；3.B+树便于扫库和区间查询。

面试题：

        了解过索引吗？

1.索引（index）是帮助MySQL高效获取数据的数据结构（有序）

2.提高数据检索的效率，降低数据库的IO成本（不需要全表扫描）

3.通过索引列对数据进行排序，降低数据排序的成本，降低了CPU的消耗。

        索引的底层数据结构了解过吗？

1.阶数更多，路径更短

2.磁盘读写代价B+树更低，非叶子节点只存储指针，叶子节点存储数据

3.B+树便于扫库和区间查询，叶子节点是一个双向链表

什么是聚簇索引什么是非聚簇索引？ 

         聚集索引选取规则（必须有，而且只有一个）：

        如果存在主键，主键索引就是聚集索引

        如果不存在主键，将使用第一个唯一（UNIQUE）索引作为聚集索引

        如果表没有主键，或没有适合的唯一索引，则InnoDB会自动生成一个rowid作为隐藏的聚集索引。

                回表查询 ：

        先通过二级查询，查询到主键，在通过主键进行聚簇查询找到整行的数据。

        面试题 ：

                什么是聚簇索引什么是非聚簇索引？

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

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

        

        知道什么是回表查询么？

        通过二级索引找到对应的主键值，到聚集索引中查找整行数据，这个过程就是回表。

 什么是覆盖索引？

        

        覆盖索引是指使用了索引，并且需要返回的列，在该索引中已经全部能够找到。

        

         覆盖索引是指查询使用了索引，返回的列，必须在索引中能全部找到

        使用id查询，直接走聚集索引查询，一次索引扫描，直接返回数据，性能高。

        如果返回的列中没有创建索引，有可能会触发回表查询，尽量避免使用select*

       

MySQL超大分页怎么处理？

优化思路：一般分页查询时，通过创建覆盖索引能够比较好地提高性能，可以通过覆盖索引加子查询形式进行优化。

面试题： 

                

 索引的创建原则有哪些？ 

                

                1.先陈述自己在实际的工作中是怎么用的

                2.主键索引

                3.唯一索引

                4.根据业务创建的索引（复合索引）

     索引创建原则有哪些？

                1.针对于数据量较大，且查询比较频繁的表建立索引。 单表超过10万数据（增加用户体验）        

                2.针对于常作为查询条件（where），排序（order by）、分组（group by）操作的字段建立索引

                3.尽量选择区分度高的列作为索引，尽量建立唯一索引，区分度越高，使用索引的效率越高

                

                 4.如果是字符串类型的字段，字段的长度比较长，可以针对字段的特点，建立前缀索引。

                

                  5. 尽量使用联合索引，减少单列索引，查询时，联合索引很多时候可以覆盖索引，节省存储空间，避免回表，提高查询效率。

                        

                   6.要控制索引的数量，索引并不是多多益善，索引越多，维护索引结构的代价也就越大，会影响增删改的效率。

                   7.如果索引列不能存储NULL值，请在创建表时使用NOT NULL约束它。当优化器知道每列是否包含NULL值，它可以更好地确定哪个索引最有效的用于查询。 

                面试题：

                

         

什么情况下索引会失效？ 

        1.违反了最左前缀法则

           如果索引了多列，要遵循最左前缀法则。指的是查询从索引的最左前列开始，并且不跳过索引中的列。匹配最左前缀法则，走索引

      

 

        1.违反最左前缀法则

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

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

        4.字符串不加单引号，造成索引失效。（类型转换）

        5.以%开头的Like模糊查询，索引失效

面试题：

 谈一谈对Sql优化的经验：

        

                1.表的设计优化

                2.索引优化

                3.SQL语句优化

                4.主从复制、读写分离

                5.分库分表

 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.

 面试题：

事务：

        什么是事务？ACID

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

ACID是什么？可以详细说一下吗？

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

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

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

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

 面试题：

 并发事务带来哪些问题？MySQL的默认隔离级别是？

        并发事务问题：脏读、不可重复读、幻读

        隔离级别：读未提交、读已提交、可重复读、串行化

 

        

        

 

 

怎么解决并发事务的问题呢？ 

        x是可以解决 

 

 注意：事务隔离级别越高，数据越安全，但是性能越低。

面试题：

 

undo log redo log的区别 ：

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

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

 

 redo log：

         重做日志，记录的是事务提交时数据页的物理修改，是用来实现事务的持久性。

         该日志文件由两部分组成：重做日志缓冲（redo log buffer）以及重做日志（redo log file），前者实在内存中，后者在磁盘中。当事务提交之后会把所有修改信息都存在该日志文件中，用于在刷新脏页到磁盘页，发生错误时，进行数据恢复使用。

                

undo log ：

回滚日志，用于记录数据被修改前的信息，作用包含两个：提供回滚和MVCC（多版本并发控制）。undo log 和redo log记录物理日志不一样，它是逻辑日志。

undo log 可以实现事务的一致性和原子性 

面试题：

        

MVCC： 

        

 解释一下MVCC：

                全称Multi-Version Concurrency Control，多版本并发控制。指维护一个数据的多个版本，使得读写操作没有冲突。

                MVCC的具体实现，主要依赖于数据库记录中的隐式字段、undo log日志、readView。        

 undo log：

        回滚日志，在insert、update、delete的时候产生的便于数据回滚的日志。

        当insert的时候，产生的undo log日志只在回滚时需要，在事务提交后，可被立即删除。

        而update、delete的时候，产生的undo log日志不仅在回滚时需要，mvcc版本访问也需要，不会立即被删除。

 

 

 不同隔离级别，生成ReadView的时机不同；

        READ COMMITTED：在事务中每一次执行快照时生成ReadView。

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

面试题：

 

MySQL主从同步原理： 

        

分库分表： 

        

拆分策略： 

        

垂直拆分： 

        垂直分库：

                以表为依据，根据业务将不同表拆分到不同库中。

 

特点：

        1.按业务对数据分级管理、维护、监控、扩展

        2.在高并发下，提高磁盘IO和数据量连接数。

        垂直分表：

                以字段为依据，根据字段属性将不同字段拆分到不同表中。

        拆分规则：

                1.把不常用的字段单独放在一张表

                2.把text，blob等大字段拆分出来放在附表中      

 特点：

        1.冷热数据分离

        2.减少IO过度争抢，两表互不影响

水平拆分：

        水平分库：

                将一个库的数据拆分到多个库中

        路由规则：

                根据id节点取模

                按id也就是范围路由，节点1（1-100万），节点2（100万-200万）...

         特点：

                1.解决了单库大数量，高并发的性能瓶颈问题

                2.提高了系统的稳定性和可用性。

水平分表：

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

         特点：

                1.优化单一数据量过大而产生的性能问题

                2.避免IO争抢并减少锁表的几率。

 面试题：