1.MySQL 问题排查都有哪些手段？

1. 使用 show processlist 命令查看当前所有连接信息；

2. 使用 Explain 命令查询 SQL 语句执行计划；

3. 开启慢查询日志，查看慢查询的 SQL。

2. MySQL高性能优化规范建议总结

数据库命令规范

• 所有数据库对象名称必须使用小写字母并用下划线分割
• 所有数据库对象名称禁止使用 MySQL 保留关键字（如果表名中包含关键字查询时，需要将其用单引号括起来）
• 数据库对象的命名要能做到见名识意，并且最后不要超过 32 个字符
• 临时库表必须以 tmp_ 为前缀并以日期为后缀，备份表必须以 bak_ 为前缀并以日期 (时间戳) 为后缀
• 所有存储相同数据的列名和列类型必须一致（一般作为关联列，如果查询时关联列类型不一致会自动进行数据类型隐式转换，会造成列上的索引失效，导致查询效率降低）

数据库基本设计规范

       所有表必须实现innodb存储引擎

       数据库和表的字符集统一使用utf8

       所有表和字段都需要添加注释

       尽量控制单表数据量的大小，建议控制在500万以内

       谨慎使用MySQL分区表

       经常一起使用的列放到一个表中

       禁止在表中建立预留字段

       禁止在数据库中存储文件这类大的二进制数据

       不要被数据库范式所束缚

       禁止在线上做数据库压力测试

数据库字段设计规范

       优先选择符合存储需要的最小的数据类型

       避免使用 TEXT,BLOB 数据类型，最常见的 TEXT 类型可以存储 64k 的数据

       避免使用 ENUM 类型

       尽可能把所有列定义为 NOT NULL

       使用timestamp(4个字节)或DATETIME类型(8个字节)存储时间

       同财务相关的金额类数据必须使用 decimal 类型

       单表不要包含过多字段

索引设计规范

        限制每张表上的索引数量,建议单张表索引不超过 5 个 

        禁止使用全文索引

        禁止给表中的每一列都建立单独的索引

        每个 InnoDB 表必须有个主键

常见索引列建议  

• 出现在 SELECT、UPDATE、DELETE 语句的 WHERE 从句中的列
• 包含在 ORDER BY、GROUP BY、DISTINCT 中的字段
• 并不要将符合 1 和 2 中的字段的列都建立一个索引， 通常将 1、2 中的字段建立联合索引效果更好
• 多表 join 的关联列

 尽量避免使用外键约束

数据库 SQL 开发规范

   优化对性能影响较大的 SQL 语句

   充分利用表上已经存在的索引

   禁止使用不含字段列表的 INSERT 语句

   禁止使用 SELECT * 必须使用 SELECT <字段列表> 查询

   

• SELECT * 消耗更多的 CPU 和 IO 以网络带宽资源
• SELECT * 无法使用覆盖索引
• SELECT <字段列表> 可减少表结构变更带来的影响

数据库操作行为规范

         超 100 万行的批量写 (UPDATE,DELETE,INSERT) 操作,要分批多次进行操作

         对于大表使用 pt-online-schema-change 修改表结构

 3.MySQL三大日志

   redo log(重做日志)

binlog(二进制内容)

redo log 它是物理日志，记录内容是“在某个数据页上做了什么修改”，属于 InnoDB 存储引擎。

而 binlog 是逻辑日志，记录内容是语句的原始逻辑，类似于“给 ID=2 这一行的 c 字段加 1”，属于MySQL Server 层。

两阶段提交

undo日志

        在 MySQL 中，恢复机制是通过 回滚日志（undo log） 实现的，所有事务进行的修改都会先记录到这个回滚日志中，然后再执行相关的操作

总结

        MySQL InnoDB 引擎使用 redo log(重做日志) 保证事务的持久性，使用 undo log(回滚日志) 来保证事务的原子性。MySQL数据库的数据备份、主备、主主、主从都离不开binlog，需要依靠binlog来同步数据，保证数据一致性。

4.为什么 InnoDB 存储引擎选用 B+ 树而不是 B 树呢？

        用 B+ 树不用 B 树考虑的是 IO 对性能的影响，B 树的每个节点都存储数据，而 B+ 树只有叶子节点才存储数据，所以查找相同数据量的情况下，B 树的高度更高，IO 更频繁。数据库索引是存储在磁盘上的，当数据量大时，就不能把整个索引全部加载到内存了，只能逐一加载每一个磁盘页（对应索引树的节点）。

5.谈谈你对覆盖索引的认识？        

如果一个索引包含了满足查询语句中字段与条件的数据就叫做覆盖索引。具有以下优点：

1. 索引通常远小于数据行的大小，只读取索引能大大减少数据访问量。

2. 一些存储引擎（例如：MyISAM）在内存中只缓存索引，而数据依赖于操作系统来缓存。因此，只访问索引可以不使用系统调用（通常比较费时）。

3. 对于 InnoDB 引擎，若辅助索引能够覆盖查询，则无需访问主索引。

6.谈谈你对最左前缀原则的理解？

MySQL 使用联合索引时，需要满足最左前缀原则。

1、 B+ 树的数据项是复合的数据结构，比如：(name, age, sex) 的时候，B+ 树是按照从左到右的顺序来建立搜索树的，比如：当(小明, 22, 男)这样的数据来检索的时候，B+ 树会优先比较 name 来确定下一步的所搜方向，如果 name 相同再依次比较 age 和 sex，最后得到检索的数据。但当 (22, 男) 这样没有 name 的数据来的时候，B+ 树就不知道第一步该查哪个节点，因为建立搜索树的时候 name 就是第一个比较因子，必须要先根据 name 来搜索才能知道下一步去哪里查询。

2、 当 (小明, 男) 这样的数据来检索时，B+ 树可以用 name 来指定搜索方向，但下一个字段 age 的缺失，所以只能把名字等于小明的数据都找到，然后再匹配性别是男的数据了， 这个是非常重要的性质，即索引的最左匹配特性。

关于最左前缀的补充：

1. 最左前缀匹配原则会一直向右匹配直到遇到范围查询（>、<、between、like）就停止匹配，比如：a = 1 and b = 2 and c > 3 and d = 4 如果建立 (a, b, c, d) 顺序的索引，d 是用不到索引的。如果建立 (a, b, d, c) 的索引则都可以用到，a、b、d 的顺序可以任意调整。

2. = 和 in 可以乱序，比如：a = 1 and b = 2 and c = 3 建立 (a, b ,c) 索引可以任意顺序，MySQL 的优化器会优化成索引可以识别的形式。

7.怎么知道创建的索引有没有被使用到？或者说怎么才可以知道这条语句运行很慢的原因?

        使用 Explain 命令来查看语句的执行计划，MySQL 在执行某个语句之前，会将该语句过一遍查询优化器，之后会拿到对语句的分析，也就是执行计划，其中包含了许多信息。可以通过其中和索引有关的信息来分析是否命中了索引，例如：possilbe_key、key、key_len 等字段，分别说明了此语句可能会使用的索引、实际使用的索引以及使用的索引长度。

8.什么情况下索引会失效？即查询不走索引？

   索引列参与表达式计算

SELECT 'sname' FROM 'stu' WHERE 'age' + 10 = 30;

   函数运算

SELECT 'sname' FROM 'stu' WHERE LEFT('date',4) < 1990;

   %词语%–模糊查询

SELECT * FROM 'manong' WHERE `uname` LIKE '码农%' -- 走索引 

SELECT * FROM 'manong' WHERE `uname` LIKE '%码农%' -- 不走索引 

 字符串与数字比较不走索引

查询条件中有 or ，即使其中有条件带索引也不会使用。换言之，就是要求使用的所有字段，都必须建立索引

正则表达式不使用索引

MySQL 内部优化器会对 SQL 语句进行优化，如果优化器估计使用全表扫描要比使用索引快，则不使用索引。

9.查询性能的优化方法？

减少请求的数据量

1. 只返回必要的列：最好不要使用 SELECT * 语句。

2. 只返回必要的行：使用 LIMIT 语句来限制返回的数据。

3. 缓存重复查询的数据：使用缓存可以避免在数据库中进行查询，特别在要查询的数据经常被重复查询时，缓存带来的查询性能提升将会是非常明显的。

减少服务器端扫描的行数

1. 最有效的方式是使用索引来覆盖查询。

10. 添加索引的原则

索引虽好，但也不是无限制使用的，以下为添加索引时需要遵循的几项建议性原则：

• 在 查询中很少使用 或者参考的列不要创建索引。由于这些列很少使用到，增加索引反而会降低系统的维护速度和增大空间需求。
• 只有很少数据值的列 也不应该增加索引。由于这些列的取值很少，区分度太低，例如人事表中的性别，在查询时，需要在表中搜索的数据行的比例很大。增加索引，并不能明显加快检索速度。
• 定义为 text、image 和 bit 数据类型的列不应该增加索引。这是因为，这些列的数据量要么相当大，要么取值很少。
• 当 修改性能远远大于检索性能 时，不应该创建索引。这时因为，二者是相互矛盾的，当增加索引时，会提高检索性能，但是会降低修改性能。
• 定义有 外键 的数据列一定要创建索引。

11.什么是死锁？如何解决死锁？

死锁是指两个或多个事务在同一资源上相互占用，并请求锁定对方的资源，从而导致恶性循环的现象。

常见的解决死锁的方法

• 如果不同程序并发存取多个表，尽量约定 以相同的顺序访问表，可以大大降低死锁机会；
• 在同一个事务中，尽可能做到 一次锁定所需要的所有资源，减少死锁产生概率；
• 对于非常容易产生死锁的业务部分，可以尝试使用 升级锁定颗粒度，通过 表级锁 定来减少死锁产生的概率。

12.DROP、DELETE 与 TRUNCATE 的区别

三种都可以表示删除，其中的细微区别之处如下：

	DROP	DELETE	TRUNCATE
SQL 语句类型	DDL	DML	DDL
回滚	不可回滚	可回滚	不可回滚
删除内容	从数据库中 删除表，所有的数据行，索引和权限也会被删除	表结构还在，删除表的 全部或者一部分数据行	表结构还在，删除表中的 所有数据
删除速度	删除速度最快	删除速度慢，需要逐行删除	删除速度快

因此，在不再需要一张表的时候，采用 DROP；在想删除部分数据行时候，用 DELETE；在保留表而删除所有数据的时候用 TRUNCATE。

13.分库分表存在哪些问题

事务问题：分库分表后，就成了分布式事务。如果依赖数据库本身的分布式事务管理功能去执行事务，将付出高昂的性能代价； 如果由应用程序去协助控制，形成程序逻辑上的事务，又会造成编程方面的负担。

跨库跨表的 JOIN 问题：在执行了分库分表之后，难以避免会将原本逻辑关联性很强的数据划分到不同的表、不同的库上，这时，表的关联操作将受到限制，我们无法 JOIN 位于不同分库的表，也无法 JOIN 分表粒度不同的表，结果原本一次查询能够完成的业务，可能需要多次查询才能完成。

额外的数据管理负担和数据运算压力：额外的数据管理负担，最为常见的是数据的 定位问题 和数据的 增删改查 的重复执行问题，这些都可以通过应用程序来解决，但必然会引起额外的逻辑运算

14.什么是乐观锁和悲观锁？如何实现？

DBMS 中的 并发控制 的任务是确保在 多个事务同时存取数据库中同一数据 时不破坏事务的隔离性和统一性以及数据库的统一性。乐观并发控制（乐观锁）和悲观并发控制（悲观锁）是并发控制主要采用的技术手段。

悲观锁：假定会发生并发冲突，屏蔽一切可能违反数据完整性的操作。在查询完数据的时候就把事务锁起来，直到提交事务。这对于长事务来讲，可能会严重影响系统的并发处理能力。实现方式：使用数据库中的锁机制。

乐观锁：假设不会发生并发冲突，只在提交操作时检查是否违反数据完整性。乐观锁适用于 读多写少 的应用场景，这样可以提高吞吐量。实现方式：一般会使用版本号机制或 CAS 算法实现。

 15.B 树& B+树两者有何异同呢？

• B 树的所有节点既存放键(key) 也存放数据(data)，而 B+树只有叶子节点存放 key 和 data，其他内节点只存放 key。
• B 树的叶子节点都是独立的;B+树的叶子节点有一条引用链指向与它相邻的叶子节点。
• B 树的检索的过程相当于对范围内的每个节点的关键字做二分查找，可能还没有到达叶子节点，检索就结束了。而 B+树的检索效率就很稳定了，任何查找都是从根节点到叶子节点的过程，叶子节点的顺序检索很明显。
• 在 B 树中进行范围查询时，首先找到要查找的下限，然后对 B 树进行中序遍历，直到找到查找的上限；而 B+树的范围查询，只需要对链表进行遍历即可。

综上，B+树与 B 树相比，具备更少的 IO 次数、更稳定的查询效率和更适于范围查询这些优势。

16.聚簇索引与非聚簇索引

        聚簇索引（Clustered Index）即索引结构和数据一起存放的索引，并不是一种单独的索引类型。InnoDB 中的主键索引就属于聚簇索引。

聚簇索引的优缺点

优点：

• 查询速度非常快：聚簇索引的查询速度非常的快，因为整个 B+树本身就是一颗多叉平衡树，叶子节点也都是有序的，定位到索引的节点，就相当于定位到了数据。相比于非聚簇索引， 聚簇索引少了一次读取数据的 IO 操作。
• 对排序查找和范围查找优化：聚簇索引对于主键的排序查找和范围查找速度非常快。

缺点：

• 依赖于有序的数据：因为 B+树是多路平衡树，如果索引的数据不是有序的，那么就需要在插入时排序，如果数据是整型还好，否则类似于字符串或 UUID 这种又长又难比较的数据，插入或查找的速度肯定比较慢。
• 更新代价大：如果对索引列的数据被修改时，那么对应的索引也将会被修改，而且聚簇索引的叶子节点还存放着数据，修改代价肯定是较大的，所以对于主键索引来说，主键一般都是不可被修改的。

非聚簇索引介绍

非聚簇索引(Non-Clustered Index)即索引结构和数据分开存放的索引，并不是一种单独的索引类型。二级索引(辅助索引)就属于非聚簇索引。MySQL 的 MyISAM 引擎，不管主键还是非主键，使用的都是非聚簇索引。

非聚簇索引的叶子节点并不一定存放数据的指针，因为二级索引的叶子节点就存放的是主键，根据主键再回表查数据。

# 非聚簇索引的优缺点

优点：

更新代价比聚簇索引要小 。非聚簇索引的更新代价就没有聚簇索引那么大了，非聚簇索引的叶子节点是不存放数据的

缺点：

• 依赖于有序的数据：跟聚簇索引一样，非聚簇索引也依赖于有序的数据
• 可能会二次查询(回表)：这应该是非聚簇索引最大的缺点了。 当查到索引对应的指针或主键后，可能还需要根据指针或主键再到数据文件或表中查询。

17.谈谈你对水平切分和垂直切分的理解？

• 水平切分

水平切分是将同一个表中的记录拆分到多个结构相同的表中。当一个表的数据不断增多时，水平切分是必然的选择，它可以将数据分布到集群的不同节点上，从而缓存单个数据库的压力。

• 垂直切分

垂直切分是将一张表按列切分成多个表，通常是按照列的关系密集程度进行切分，也可以利用垂直切分将经常被使用的列和不经常被使用的列切分到不同的表中。例如：将原来的电商数据库垂直切分成商品数据库、用户数据库等。