MySQL经典面试题

MySQL怎么定位慢查询

• 开源工具
  • 调试工具：Arthas
  • 运维工具：Prometheus、Skywalking
• MySQL自带慢日志
  • 慢查询日志记录了所有执行时间超过指定参数(long_query_time，单位:秒，默认10秒)的所有SQL语句的日志，如果要开启慢查询日志，需要在MySQL的配置文件(/etc/mycnf)中配置如下信息
  • 
  • 配置完毕之后，通过以下指令重新启动MySQL服务器进行测试，查看慢日志文件中记录的信息
    /var/lib/mysql/localhost-slow.log

SQL语句执行慢，如何分析和优化

可以采用explain或者desc命令获取Mysql如何执行select语句信息的。

• possible_key：当前sql可能会用到的索引。

• key:当前sql实际命中的索引。

• key_len：索引占用的大小。

• Extra：额外的优化建议。
  

• type：这条sql的连接的类型，性能由好到差NULL、system、const、eq_ref、ref、range、index、all

  • system:查询系统中的表
  • cosnt：根据主键查询
  • eq_ref：主键索引查询或者唯一索引查询
  • ref:索引查询
  • range：范围查询
  • index：索引树扫描
  • all：全盘扫描
    

了解过索引吗？（什么是索引）

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

• 索引（index）是帮助Mysql高效获取数据的数据结构（有序）。
• 提高数据检索的效率，降低数据库的IO成本（不需要全表扫描）。
• 通过索引列对数据进行排序，降低数据排序的成本，降低CPU的消耗。
  • 扩展 ->什么是B+树
    • B+Tree是在BTree基础上的一种优化，使其更适合实现外存储索引结构，InnoDB存储引擎就是用B+Tree实现其索引结构
    • 
  • 追问 ->索引的底层数据结构了解过吗？
    • MySQL的InnoDB引擎采用的B+树的数据结构来存储索引。
    • 阶级更多，路径更短。
    • 磁盘读写代价B+树更低，非叶子节点只存储指针，叶子阶段存储数据。
    • B+树便于扫库和区间查询，叶子节点是一个双向链表。

什么是聚簇索引（聚集索引）和非聚簇索引（二级索引）

• 聚簇索引（聚集索引）：数据与索引放到一块，B+树的叶子节点保存， 整行数据，有且只有一个。
• 非聚簇索引（二级索引）：数据与索引分开存储，B+树的叶子节点保存对应的主键，可以有多个。
• 
• 追问：什么是回表查询
  • 通过二级索引找到对应的主键值，到聚集索引中查找整行数据，这个过程就是回表。

什么叫覆盖索引

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

• 使用id查询，走的是聚簇索引查询，一次索引扫描，直接返回数据，性能高。
• 如果返回的列中没有创建索引，有可能触发回表查询，尽量避免使用select*。
• 追问 ->MySQL超大分页怎么处理
  • 
  • 
  • 解决方案：覆盖索引+子查询

索引创建的原创有哪些

• 针对于数据量较大，且查询比较频繁的表建立索引。（单表超过10万数据）
• 针对于常作为查询条件（where）排序（order_by）、分组（group_by）操作的字段建立索引。
• 尽量选择区分度高的列作为索引，尽量建立唯一索引，区分度越高，使用索引效率越高。
• 如果是字符串类型的字段，字段的长度教长，可以针对于字段的特点，建立前缀索引。
• 尽量使用联合索引，减少单列索引，查询时，联合索引很多时候可以覆盖索引，节省存储空间，避免回表，提高查询效率。
• 要控制索引的数量，索引并不是越多越好，索引越多，维护索引结构的代价也就越大，会影响增删改的效率。
• 如果索引列不能存储NULL，请在创建表的时候使用NOT_NULL约束它。当优化器知道每列是否包含NULL值时，它可以更好的确定哪个索引最有效的用于查询。

什么情况下索引会失效

• 违反最左前缀法则：如果索引了多列，要遵守最左前缀法则。指的是查询从索引的最左前列开始，并且不跳过索引中的列。匹配最左前缀
  法则，走索引。
• 范围查询右边的列，不能使用索引。
• 不要在索引列上进行运算操作，用了索引就会失效。
• 字符串不加单引号，会造成索引失效（类型转换）。
• 以%开头的like模糊查询，索引失效。如果仅仅是尾部模糊匹配，索引不会失效。

谈谈你对sql的优化经验

• 表的设计优化，数据类型的选择。
  • 
• 索引的优化，创建索引原则。
• sql语句优化，避免索引失效
  • 
• 主从复制、读写分离，不让数据的写入，影响读操作。
  • 
• 分库分表。

事务的特性是什么(ACID)？可以详细说一下吗？

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

ACID是什么？

• 原子性（Atomicity）：事务是不可分割的最小操作单元，要么全部成功，要么全部失败。
• 一致性（Consistency）：事务完成时，必须使所有的数据都保持一致状态。
• 隔离性（Isolation）：数据库系统提供的隔离机制，保证事务在不受外部并发操作影响的独立环境下运行。
• 持久性（Durability）：事务一旦提交回滚，它对数据库中的数据的改变就是永久。

并发事务问题

• 脏读：一个事务读到另一个事务还没提交的数据。
• 不可重复读：一个事务先后读取同一条数据，但是两次读取到的数据不同，称之为不可重复读。
• 幻读：一个事务按照条件查询的时候，没有对应的数据行，但是在插入数据的时候，又发现这行数据已经存在了，好像出现了"幻影"。
• 解决方案:
  • 
  • 补充：越往下安全级别越高，性能也越差，综合考虑，所以mysql的默认事务隔离级别为Repeatable Read（可重复读）。

undo log和redo log的区别

• redo log：记录的是数据页的物理变化，服务宕机可以用来同步数据。
• undo log：记录的是逻辑日志，当事务回滚时，可以通过逆操作恢复原来的数据。
• redo log保证了事务的持久性，undo log保证了事务的原子性和一致性。

事务中的隔离性是如何保证的？（你解释一下MVCC）

MySQL主从同步原理

MySQL主从复制的核心就是二进制日志：二进制日志(BINLOG)记录了所有的 DDL(数据定义语言)语句和 DML(数据操纵语言)语句，但不包括数据查询(SELECT、SHOW)语句。

分库分表

• 垂直拆分
  • 垂直分库
    
  • 垂直分表
    
• 水平拆分
  • 水平分库
    
  • 水平分表