Mysql面试大全，看完可以吊打面试官

Q1：JDBC 连接数据库步骤(以 MYSQL 为例)

1、加载 JDBC 驱动程序：

通过 Class 类的 forName 方法实现，并将驱动地址放进去

成功加载后，会将 Driver 类的实例注册到 DriverManager 类中。

2、提供 JDBC 连接的 URL 、创建数据库的连接

•要连接数据库，需要向 java.sql.DriverManager 请求并获得 Connection 对象，

该对象就代表一个数据库的连接。

•使用 DriverManager 的 getConnectin()方法传入指定的欲连接的数据库的路径、数

据库的用户名和密码。

Connection con=DriverManager.getConnection(url , username , password);

&&&:"jdbc:mysql://localhost/test?user=root&password=123&useUnicode=tr

ue&characterEncoding=utf-8”;

3、创建一个 Statement •要执行 SQL 语句，必须获得 java.sql.Statement 实例

•执行静态 SQL 语句。通常通过 Statement 实例实现。

•执行动态 SQL 语句。通常通过 PreparedStatement 实例实现。

String sql = “”;

Statement st = con.createStatement() ;

PreparedStatement pst = con.prepareStatement(sql) ;

4、执行 SQL 语句

Statement 接口提供了 executeQuery、executeUpdate、execute 三种方法

executeQuery：执行 select 语句，返回 ResultSet 结果集

ResultSet rst = pst.executeQuery(); • executeUpdate：执行 insert、update、delete 语句

pst.executeUpdate();

5、关闭 JDBC 对象

操作完成以后要把所有使用的 JDBC 对象全都关闭，以释放 JDBC 资源。

Q2:如何进行Mysql的优化

1、SELECT语句务必指明字段名称

SELECT *增加很多不必要的消耗（cpu、io、内存、网络带宽）；增加了使用覆盖索引的可能性；当表结构发生改变时，前断也需要更新。所以要求直接在select后面接上字段名。

2、当只需要一条数据的时候，使用limit 1

这是为了使EXPLAIN中type列达到const类型

3、如果排序字段没有用到索引，就尽量少排序

4、如果限制条件中其他字段没有索引，尽量少用or

or两边的字段中，如果有一个不是索引字段，而其他条件也不是索引字段，会造成该查询不走索引的情况。很多时候使用 union all 或者是union(必要的时候)的方式来代替“or”会得到更好的效果。

5、尽量用union all代替union

union和union all的差异主要是前者需要将结果集合并后再进行唯一性过滤操作，这就会涉及到排序，增加大量的CPU运算，加大资源消耗及延迟。当然，union all的前提条件是两个结果集没有重复数据。

Q3：MySQL 8.0 的新特性，太实用了！

1、隐藏索引

隐藏索引的特性对于性能调试非常有用。在 8.0 中，索引可以被“隐藏”和“显示”。当一个索引隐藏时，它不会被查询优化器所使用。

也就是说，我们可以隐藏一个索引，然后观察对数据库的影响。如果数据库性能有所下降，就说明这个索引是有用的，于是将其“恢复显示”即可；如果数据库性能看不出变化，说明这个索引是多余的，可以删掉了。

隐藏一个索引的语法是：

ALTER TABLE t ALTER INDEX i INVISIBLE;

恢复显示该索引的语法是：

ALTER TABLE t ALTER INDEX i VISIBLE;

当一个索引被隐藏时，我们可以从 show index 命令的输出中看到，该索引的 Visible 属性值为 NO。

注意：当索引被隐藏时，它的内容仍然是和正常索引一样实时更新的，这个特性本身是专门为优化调试使用。如果你长期隐藏一个索引，那还不如干脆删掉，因为毕竟索引的存在会影响插入、更新和删除的性能。

2、设置持久化

MySQL 的设置可以在运行时通过 SET GLOBAL 命令来更改，但是这种更改只会临时生效，到下次启动时数据库又会从配置文件中读取。

MySQL 8 新增了 SET PERSIST 命令，例如：

SET PERSIST max_connections = 500;

MySQL 会将该命令的配置保存到数据目录下的 mysqld-auto.cnf 文件中，下次启动时会读取该文件，用其中的配置来覆盖缺省的配置文件。

3、UTF-8 编码

从 MySQL 8 开始，数据库的缺省编码将改为 utf8mb4，这个编码包含了所有 emoji 字符。

多少年来我们使用 MySQL 都要在编码方面小心翼翼，生怕忘了将缺省的 latin 改掉而出现乱码问题。从此以后就不用担心了。

Q4：MySQL 的逻辑架构了解吗？

第⼀层是服务器层，主要提供连接处理、授权认证、安全等功能。

第⼆层实现了 MySQL 核⼼服务功能，包括查询解析、分析、优化、缓存以及⽇期和时间等所有内置函数，所有跨存储引擎的功能都在这⼀层实现，例如存储过程、触发器、视图等。

第三层是存储引擎层，存储引擎负责 MySQL 中数据的存储和提取。服务器通过 API 与存储引擎通信， 这些接⼝屏蔽了不同存储引擎的差异，使得差异对上层查询过程透明。除了会解析外键定义的 InnoDB 外，存储引擎不会解析 SQL，不同存储引擎之间也不会相互通信，只是简单响应上层服务器请求。

Q5：谈⼀谈 MySQL 的读写锁

在处理并发读或写时，可以通过实现⼀个由两种类型组成的锁系统来解决问题。这两种类型的锁通常被称为共享锁和排它锁，也叫读锁和写锁。读锁是共享的，相互不阻塞，多个客户在同⼀时刻可以同时读取同⼀个资源⽽不相互⼲扰。写锁则是排他的，也就是说⼀个写锁会阻塞其他的写锁和读锁，确保在给定时间内只有⼀个⽤户能执⾏写⼊并防⽌其他⽤户读取正在写⼊的同⼀资源。

在实际的数据库系统中，每时每刻都在发⽣锁定，当某个⽤户在修改某⼀部分数据时，MySQL 会通过锁定防⽌其他⽤户读取同⼀数据。写锁⽐读锁有更⾼的优先级，⼀个写锁请求可能会被插⼊到读锁队列的前⾯，但是读锁不能插⼊到写锁前⾯。

Q6：MySQL 的锁策略有什么？

表锁是MySQL中最基本的锁策略，并且是开销最⼩的策略。表锁会锁定整张表，⼀个⽤户在对表进⾏写操作前需要先获得写锁，这会阻塞其他⽤户对该表的所有读写操作。只有没有写锁时，其他读取的⽤户     才能获取读锁，读锁之间不相互阻塞。

⾏锁可以最⼤程度地⽀持并发，同时也带来了最⼤开销。InnoDB 和 XtraDB 以及⼀些其他存储引擎实现了⾏锁。⾏锁只在存储引擎层实现，⽽服务器层没有实现。

Q7：数据库死锁如何解决？

死锁是指多个事务在同⼀资源上相互占⽤并请求锁定对⽅占⽤的资源⽽导致恶性循环的现象。当多个事     务试图以不同顺序锁定资源时就可能会产⽣死锁，多个事务同时锁定同⼀个资源时也会产⽣死锁。

为了解决死锁问题，数据库系统实现了各种死锁检测和死锁超时机制。越复杂的系统，例如InnoDB 存储引擎，越能检测到死锁的循环依赖，并⽴即返回⼀个错误。这种解决⽅式很有效，否则死锁会导致出现⾮常慢的查询。还有⼀种解决⽅法，就是当查询的时间达到锁等待超时的设定后放弃锁请求，这种⽅ 式通常来说不太好。InnoDB ⽬前处理死锁的⽅法是将持有最少⾏级排它锁的事务进⾏回滚。

死锁发⽣之后，只有部分或者完全回滚其中⼀个事务，才能打破死锁。对于事务型系统这是⽆法避免的，所以应⽤程序在设计时必须考虑如何处理死锁。⼤多数情况下只需要重新执⾏因死锁回滚的事务即可。

Q8：事务是什么?

事务是⼀组原⼦性的 SQL

查询，或者说⼀个独⽴的⼯作单元。如果数据库引擎能够成功地对数据库应⽤该组查询的全部语句，那么就执⾏该组查询。如果其中有任何⼀条语句因为崩溃或其他原因⽆法执⾏， 那么所有的语句都不会执⾏。也就是说事务内的语句要么全部执⾏成功，要么全部执⾏失败。

Q9：事务有什么特性？

原⼦性 atomicity

⼀个事务在逻辑上是必须不可分割的最⼩⼯作单元，整个事务中的所有操作要么全部提交成功，要么全部失败回滚，对于⼀个事务来说不可能只执⾏其中的⼀部分。

⼀致性 consistency

数据库总是从⼀个⼀致性的状态转换到另⼀个⼀致性的状态。

隔离性 isolation

针对并发事务⽽⾔，隔离性就是要隔离并发运⾏的多个事务之间的相互影响，⼀般来说⼀个事务所做的修改在最终提交以前，对其他事务是不可⻅的。

持久性 durability

⼀旦事务提交成功，其修改就会永久保存到数据库中，此时即使系统崩溃，修改的数据也不会丢失。

Q10：MySQL 的隔离级别有哪些？

未提交读 READ UNCOMMITTED

在该级别事务中的修改即使没有被提交，对其他事务也是可⻅的。事务可以读取其他事务修改完但未提交的数据，这种问题称为脏读。这个级别还会导致不可重复读和幻读，性能没有⽐其他级别好很多，很 少使⽤。

提交读 READ COMMITTED

多数数据库系统默认的隔离级别。提交读满⾜了隔离性的简单定义：⼀个事务开始时只能"看⻅"已经提     交的事务所做的修改。换句话说，⼀个事务从开始直到提交之前的任何修改对其他事务都是不可⻅的。也叫不可重复读，因为两次执⾏同样的查询可能会得到不同结果。

可重复读 REPEATABLE READ（MySQL默认的隔离级别）

可重复读解决了不可重复读的问题，保证了在同⼀个事务中多次读取同样的记录结果⼀致。但还是⽆法 解决幻读，所谓幻读指的是当某个事务在读取某个范围内的记录时，会产⽣幻⾏。InnoDB 存储引擎通过多版本并发控制MVCC 解决幻读的问题。

可串⾏化 SERIALIZABLE

最⾼的隔离级别，通过强制事务串⾏执⾏，避免幻读。可串⾏化会在读取的每⼀⾏数据上都加锁，可能导致⼤量的超时和锁争⽤的问题。实际应⽤中很少⽤到这个隔离级别，只有⾮常需要确保数据⼀致性且     可以接受没有并发的情况下才考虑该级别。

Q11：MVCC 是什么？

MVCC 是多版本并发控制，在很多情况下避免加锁，⼤都实现了⾮阻塞的读操作，写操作也只锁定必要的⾏。

InnoDB 的MVCC 通过在每⾏记录后⾯保存两个隐藏的列来实现，这两个列⼀个保存了⾏的创建时间，⼀个保存⾏的过期时间间。不过存储的不是实际的时间值⽽是系统版本号，每开始⼀个新的事务系统版 本号都会⾃动递增，事务开始时刻的系统版本号会作为事务的版本号，⽤来和查询到的每⾏记录的版本号进⾏⽐较。

MVCC 只能在 和 两个隔离级别下⼯作，因为

总是读取最新的数据⾏，⽽不是符合当前事务版本的数据⾏，⽽  所有读取的⾏都加锁。

Q12：谈⼀谈 InnoDB

InnoDB 是 MySQL 的默认事务型引擎，⽤来处理⼤量短期事务。InnoDB 的性能和⾃动崩溃恢复特性使得它在⾮事务型存储需求中也很流⾏，除⾮有特别原因否则应该优先考虑 InnoDB

InnoDB 的数据存储在表空间中，表空间由⼀系列数据⽂件组成。MySQL4.1 后 InnoDB 可以将每个表的数据和索引放在单独的⽂件中。

InnoDB 采⽤ MVCC 来⽀持⾼并发，并且实现了四个标准的隔离级别。其默认级别是READ

，并通过间隙锁策略防⽌幻读，间隙锁使 InnoDB 不仅仅锁定查询涉及的⾏，还会对索引中的间隙进⾏锁定防⽌幻⾏的插⼊。

InnoDB 表是基于聚簇索引建⽴的，InnoDB 的索引结构和其他存储引擎有很⼤不同，聚簇索引对主键查询有很⾼的性能，不过它的⼆级索引中必须包含主键列，所以如果主键很⼤的话其他所有索引都会很⼤，因此如果表上索引较多的话主键应当尽可能⼩。

InnoDB 的存储均存储在B+树的叶子节点上的，可以将数据和索引⽂件从⼀个平台复制到另⼀个平台。

InnoDB 内部做了很多优化，包括从磁盘读取数据时采⽤的可预测性预读，能够⾃动在内存中创建加速读操作的⾃适应哈希索引，以及能够加速插⼊操作的插⼊缓冲区等。

Q13：谈⼀谈 MyISAM

MySQL5.1及之前，MyISAM 是默认存储引擎，MyISAM 提供了⼤量的特性，包括全⽂索引、压缩、空间函数等，但不⽀持事务和⾏锁，最⼤的缺陷就是崩溃后⽆法安全恢复。对于只读的数据或者表⽐较⼩、可以忍受修复操作的情况仍然可以使⽤ MyISAM。

MyISAM 将表存储在数据⽂件和索引⽂件中，分别以  和作为扩展名。MyISAM 表可以包含动态或者静态⾏，MySQL 会根据表的定义决定⾏格式。MyISAM 表可以存储的⾏记录数⼀般受限于可⽤磁盘空间或者操作系统中单个⽂件的最⼤尺⼨。

MyISAM 对整张表进⾏加锁，读取时会对需要读到的所有表加共享锁，写⼊时则对表加排它锁。但是在表有读取查询的同时，也⽀持并发往表中插⼊新的记录。

对于MyISAM 表，MySQL 可以⼿动或⾃动执⾏检查和修复操作，这⾥的修复和事务恢复以及崩溃恢复的概念不同。执⾏表的修复可能导致⼀些数据丢失，⽽且修复操作很慢。

对于 MyISAM 表，即使是 BLOB 和 TEXT 等⻓字段，也可以基于其前 500 个字符创建索引。MyISAM 也⽀持全⽂索引，这是⼀种基于分词创建的索引，可以⽀持复杂的查询。

MyISAM 设计简单，数据以紧密格式存储，所以在某些场景下性能很好。MyISAM 最典型的性能问题还是表锁问题，如果所有的查询⻓期处于 Locked 状态，那么原因毫⽆疑问就是表锁。

Q14：谈⼀谈 Memory

如果需要快速访问数据且这些数据不会被修改，重启以后丢失也没有关系，那么使⽤ Memory 表是⾮常有⽤的。Memory 表⾄少要⽐ MyISAM 表快⼀个数量级，因为所有数据都保存在内存，不需要磁盘IO，Memory 表的结构在重启后会保留，但数据会丢失。

Memory 表适合的场景：查找或者映射表、缓存周期性聚合数据的结果、保存数据分析中产⽣的中间数据。

Memory 表⽀持哈希索引，因此查找速度极快。虽然速度很快但还是⽆法取代传统的基于磁盘的表， Memory 表使⽤表级锁，因此并发写⼊的性能较低。它不⽀持 BLOB 和 TEXT 类型的列，并且每⾏的⻓度是固定的，所以即使指定了 VARCHAR 列，实际存储时也会转换成CHAR，这可能导致部分内存的浪费。

如果 MySQL 在执⾏查询的过程中需要使⽤临时表来保持中间结果，内部使⽤的临时表就是 Memory 表。如果中间结果太⼤超出了Memory 表的限制，或者含有 BLOB 或 TEXT 字段，临时表会转换成MyISAM 表。

Q15：查询执⾏流程是什么？

简单来说分为五步：①    客户端发送⼀条查询给服务器。②    服务器先检查查询缓存，如果命中了缓存则⽴刻返回存储在缓存中的结果，否则进⼊下⼀阶段。③ 服务器端进⾏ SQL 解析、预处理，再由优化器

⽣成对应的执⾏计划。④ MySQL 根据优化器⽣成的执⾏计划，调⽤存储引擎的 API 来执⾏查询。⑤ 将结果返回给客户端。

Q16：VARCHAR 和 CHAR 的区别？

VARCHAR ⽤于存储可变字符串，是最常⻅的字符串数据类型。它⽐ CHAR 更节省空间，因为它仅使⽤必要的空间。VARCHAR 需要 1 或 2 个额外字节记录字符串⻓度，如果列的最⼤⻓度不⼤于 255 字节则只需要 1 字节。VARCHAR 不会删除末尾空格。

VARCHAR 适⽤场景：字符串列的最⼤⻓度⽐平均⻓度⼤很多、列的更新很少、使⽤了 UTF8 这种复杂字符集，每个字符都使⽤不同的字节数存储。

CHAR 是定⻓的，根据定义的字符串⻓度分配⾜够的空间。CHAR 会删除末尾空格。

CHAR 适合存储很短的字符串，或所有值都接近同⼀个⻓度，例如存储密码的 MD5 值。对于经常变更的数据，CHAR 也⽐ VARCHAR更好，因为定⻓的 CHAR 不容易产⽣碎⽚。对于⾮常短的列，CHAR 在存储空间上也更有效率，例如⽤ CHAR 来存储只有 Y 和 N 的值只需要⼀个字节，但是 VARCHAR 需要两个字节，因为还有⼀个记录⻓度的额外字节。

Q17：DATETIME 和 TIMESTAMP 的区别？

DATETIME 能保存⼤范围的值，从 1001~9999 年，精度为秒。把⽇期和时间封装到了⼀个整数中，与时区⽆关，使⽤ 8 字节存储空间。

TIMESTAMP 和 UNIX 时间戳相同，只使⽤ 4 字节的存储空间，范围⽐ DATETIME ⼩得多，只能表示1970 ~2038 年，并且依赖于时区。

Q18：数据类型有哪些优化策略？

更⼩的通常更好

⼀般情况下尽量使⽤可以正确存储数据的最⼩数据类型，更⼩的数据类型通常也更快，因为它们占⽤更     少的磁盘、内存和 CPU 缓存。

尽可能简单

简单数据类型的操作通常需要更少的 CPU 周期，例如整数⽐字符操作代价更低，因为字符集和校对规则使字符相⽐整形更复杂。应该使⽤ MySQL 的内建类型 date、time 和 datetime ⽽不是字符串来存储⽇期和时间，另⼀点是应该使⽤整形存储 IP 地址。

尽量避免 NULL

通常情况下最好指定列为 NOT NULL，除⾮需要存储 NULL值。因为如果查询中包含可为 NULL 的列对MySQL 来说更难优化，可为 NULL 的列使索引、索引统计和值⽐较都更复杂，并且会使⽤更多存储空间。当可为 NULL 的列被索引时，每个索引记录需要⼀个额外字节，在MyISAM 中还可能导致固定⼤⼩的索引变成可变⼤⼩的索引。如果计划在列上建索引，就应该尽量避免设计成可为 NULL 的列。

Q19：索引有什么作⽤？

索引也叫键，是存储引擎⽤于快速找到记录的⼀种数据结构。索引对于良好的性能很关键，尤其是当表中数据量越来越⼤时，索引对性能的影响愈发重要。在数据量较⼩且负载较低时，不恰当的索引对性能 的影响可能还不明显，但数据量逐渐增⼤时，性能会急剧下降。

索引⼤⼤减少了服务器需要扫描的数据量、可以帮助服务器避免排序和临时表、可以将随机 IO 变成顺序    IO

。但索引并不总是最好的⼯具，对于⾮常⼩的表，⼤部分情况下会采⽤全表扫描。对于中到⼤型的表，索引就⾮常有效。但对于特⼤型的表，建⽴和使⽤索引的代价也随之增⻓，这种情况下应该使⽤分区技术。

在MySQL中，⾸先在索引中找到对应的值，然后根据匹配的索引记录找到对应的数据⾏。索引可以包括⼀个或多个列的值，如果索引包含多个列，那么列的顺序也⼗分重要，因为 MySQL 只能使⽤索引的最左前缀。

Q20：谈⼀谈 MySQL 的 B-Tree 索引

⼤多数 MySQL 引擎都⽀持这种索引，但底层的存储引擎可能使⽤不同的存储结构，例如 NDB 使⽤ T- Tree，⽽ InnoDB 使⽤ B+ Tree。

B-Tree 通常意味着所有的值都是按顺序存储的，并且每个叶⼦⻚到根的距离相同。B-Tree 索引能够加快访问数据的速度，因为存储引擎不再需要进⾏全表扫描来获取需要的数据，取⽽代之的是从索引的根节点开始进⾏搜索。根节点的槽中存放了指向⼦节点的指针，存储引擎根据这些指针向下层查找。通过

⽐较节点⻚的值和要查找的值可以找到合适的指针进⼊下层⼦节点，这些指针实际上定义了⼦节点⻚中值的上限和下限。最终存储引擎要么找到对应的值，要么该记录不存在。叶⼦节点的指针指向的是被索引的数据，⽽不是其他的节点⻚。

B-Tree索引的限制：

如果不是按照索引的最左列开始查找，则⽆法使⽤索引。

不能跳过索引中的列，例如索引为 (id,name,sex)，不能只使⽤ id 和 sex ⽽跳过 name。如果查询中有某个列的范围查询，则其右边的所有列都⽆法使⽤索引。

Q21：了解 Hash 索引吗？

哈希索引基于哈希表实现，只有精确匹配索引所有列的查询才有效。对于每⼀⾏数据，存储引擎都会对      所有的索引列计算⼀个哈希码，哈希码是⼀个较⼩的值，并且不同键值的⾏计算出的哈希码也不⼀样。 哈希索引将所有的哈希码存储在索引中，同时在哈希表中保存指向每个数据⾏的指针。

只有 Memory 引擎显式⽀持哈希索引，这也是 Memory 引擎的默认索引类型。

因为索引⾃身只需存储对应的哈希值，所以索引的结构⼗分紧凑，这让哈希索引的速度⾮常快，但它也     有⼀些限制：哈希索引数据不是按照索引值顺序存储的，⽆法⽤于排序。 哈希索引不⽀持部分索引列匹配查找，因为哈希索引始终是使⽤索引列的全部内容来计算哈希值的。例如在数据列(a,b)上建⽴哈希索引，如果查询的列只有a就⽆法使⽤该索引。 哈希索引只⽀持等值⽐较查询，不⽀持任何范围查询。

Q22：什么是⾃适应哈希索引？

⾃适应哈希索引是 InnoDB 引擎的⼀个特殊功能，当它注意到某些索引值被使⽤的⾮常频繁时，会在内存中基于 B-Tree 索引之上再创键⼀个哈希索引，这样就让 B-Tree 索引也具有哈希索引的⼀些优点，⽐如快速哈希查找。这是⼀个完全⾃动的内部⾏为，⽤户⽆法控制或配置，但如果有必要可以关闭该功能。

Q23 ：什么是空间索引？

MyISAM 表⽀持空间索引，可以⽤作地理数据存储。和 B-Tree 索引不同，这类索引⽆需前缀查询。空间索引会从所有维度来索引数据，查询时可以有效地使⽤任意维度来组合查询。必须使⽤ MySQL 的GIS 即地理信息系统的相关函数来维护数据，但 MySQL 对 GIS 的⽀持并不完善，因此⼤部分⼈都不会使⽤这个特性。

Q24：什么是全⽂索引？

通过数值⽐较、范围过滤等就可以完成绝⼤多数需要的查询，但如果希望通过关键字匹配进⾏查询，就需要基于相似度的查询，⽽不是精确的数值⽐较，全⽂索引就是为这种场景设计的。

MyISAM 的全⽂索引是⼀种特殊的 B-Tree 索引，⼀共有两层。第⼀层是所有关键字，然后对于每⼀个关键字的第⼆层，包含的是⼀组相关的"⽂档指针"。全⽂索引不会索引⽂档对象中的所有词语，它会根据规则过滤掉⼀些词语，例如停⽤词列表中的词都不会被索引。

Q25：什么是聚簇索引？

聚簇索引不是⼀种索引类型，⽽是⼀种数据存储⽅式。InnoDB 的聚簇索引实际上在同⼀个结构中保存了 B-Tree 索引和数据⾏。当表有聚餐索引时，它的⾏数据实际上存放在索引的叶⼦⻚中，因为⽆法同时把数据⾏存放在两个不同的地⽅，所以⼀个表只能有⼀个聚簇索引。

优点：① 可以把相关数据保存在⼀起。② 数据访问更快，聚簇索引将索引和数据保存在同⼀个 B-Tree中，因此获取数据⽐⾮聚簇索引要更快。③ 使⽤覆盖索引扫描的查询可以直接使⽤⻚节点中的主键值。

缺点：① 聚簇索引最⼤限度提⾼了 IO 密集型应⽤的性能，如果数据全部在内存中将会失去优势。② 更新聚簇索引列的代价很⾼，因为会强制每个被更新的⾏移动到新位置。③基于聚簇索引的表插⼊新⾏或主键被更新导致⾏移动时，可能导致⻚分裂，表会占⽤更多磁盘空间。④当⾏稀疏或由于⻚分裂导致数据存储不连续时，全表扫描可能很慢。

Q26：什么是覆盖索引？

覆盖索引指⼀个索引包含或覆盖了所有需要查询的字段的值，不再需要根据索引回表查询数据。覆盖索引必须要存储索引列的值，因此 MySQL 只能使⽤ B-Tree 索引做覆盖索引。

优点：① 索引条⽬通常远⼩于数据⾏⼤⼩，可以极⼤减少数据访问量。② 因为索引按照列值顺序存储，所以对于 IO 密集型防伪查询回避随机从磁盘读取每⼀⾏数据的 IO 少得多。③ 由于 InnoDB 使⽤聚簇索引，覆盖索引对 InnoDB 很有帮助。InnoDB 的⼆级索引在叶⼦节点保存了⾏的主键值，如果⼆级主键能覆盖查询那么可以避免对主键索引的⼆次查询。

Q27：你知道哪些索引使⽤原则？

建⽴索引

对查询频次较⾼且数据量⽐较⼤的表建⽴索引。索引字段的选择，最佳候选列应当从 WHERE ⼦句的条件中提取，如果 WHERE ⼦句中的组合⽐较多，应当挑选最常⽤、过滤效果最好的列的组合。业务上具有唯⼀特性的字段，即使是多个字段的组合，也必须建成唯⼀索引。

使⽤前缀索引

索引列开始的部分字符，索引创建后也是使⽤硬盘来存储的，因此短索引可以提升索引访问的 IO 效率。对于 BLOB、TEXT 或很⻓的 VARCHAR 列必须使⽤前缀索引，MySQL 不允许索引这些列的完整⻓度。前缀索引是⼀种能使索引更⼩更快的有效⽅法，但缺点是 MySQL ⽆法使⽤前缀索引做 ORDER BY 和 GROUP BY，也⽆法使⽤前缀索引做覆盖扫描。

选择合适的索引顺序

当不需要考虑排序和分组时，将选择性最⾼的列放在前⾯。索引的选择性是指不重复的索引值和数据表 的记录总数之⽐，索引的选择性越⾼则查询效率越⾼，唯⼀索引的选择性是 1，因此也可以使⽤唯⼀索引提升查询效率。

删除⽆⽤索引

MySQL 允许在相同列上创建多个索引，重复的索引需要单独维护，并且优化器在优化查询时也需要逐个考虑，这会影响性能。重复索引是指在相同的列上按照相同的顺序创建的相同类型的索引，应该避免 创建重复索引。如果创建了索引 (A,B) 再创建索引 (A) 就是冗余索引，因为这只是前⼀个索引的前缀索引，对于 B-Tree 索引来说是冗余的。解决重复索引和冗余索引的⽅法就是删除这些索引。除了重复索引和冗余索引，可能还会有⼀些服务器永远不⽤的索引，也应该考虑删除。

Q28：索引失效的情况有哪些？

如果索引列出现了隐式类型转换，则 MySQL 不会使⽤索引。常⻅的情况是在 SQL 的 WHERE 条件中字段类型为字符串，其值为数值，如果没有加引号那么 MySQL 不会使⽤索引。

如果 WHERE 条件中含有 OR，除⾮ OR 前使⽤了索引列⽽ OR 之后是⾮索引列，索引会失效。

MySQL 不能在索引中执⾏ LIKE 操作，这是底层存储引擎 API 的限制，最左匹配的 LIKE ⽐较会被转换为简单的⽐较操作，但如果是以通配符开头的 LIKE 查询，存储引擎就⽆法做⽐较。这种情况下 MySQL 只能提取数据⾏的值⽽不是索引值来做⽐较。

如果查询中的列不是独⽴的，则 MySQL 不会使⽤索引。独⽴的列是指索引列不能是表达式的⼀部分， 也不能是函数的参数。

对于多个范围条件查询，MySQL ⽆法使⽤第⼀个范围列后⾯的其他索引列，对于多个等值查询则没有这种限制。

如果 MySQL 判断全表扫描⽐使⽤索引查询更快，则不会使⽤索引。

索引⽂件具有 B-Tree 的最左前缀匹配特性，如果左边的值未确定，那么⽆法使⽤此索引。

Q29：如何定位低效 SQL？

可以通过两种⽅式来定位执⾏效率较低的 SQL

语句。⼀种是通过慢查询⽇志定位，可以通过慢查询⽇志定位那些已经执⾏完毕的 SQL 语句。另⼀种是使⽤ SHOW PROCESSLIST 查询，慢查询⽇志在查询结束以后才记录，所以在应⽤反应执⾏效率出现问题的时候查询慢查询⽇志不能定位问题，此时可以使⽤SHOW PROCESSLIST 命令查看当前 MySQL 正在进⾏的线程，包括线程的状态、是否锁表等，可以实时查看 SQL 的执⾏情况，同时对⼀些锁表操作进⾏优化。找到执⾏效率低的 SQL 语句后，就可以通过SHOW PROFILE、EXPLAIN 或 trace 等丰富来继续优化语句。

Q30：SHOW PROFILE 的作⽤？

SHOW PROFILE CPU/MEMORY/BLOCK

通过 SHOW PROFILE 可以分析 SQL 语句性能消耗，例如查询到 SQL 会执⾏多少时间，并显示 CPU 、内存使⽤量，执⾏过程中系统锁及表锁的花费时间等信息。例如

IO FOR QUERY N

分别查询 id 为 N 的 SQL 语句的 CPU 、内存以及 IO 的消耗情况。

Q31：trace 是⼲什么的？

从 MySQL5.6 开始，可以通过 trace ⽂件进⼀步获取优化器是是如何选择执⾏计划的，在使⽤时需要先打开设置，然后执⾏⼀次 SQL，最后查看 information_schema.optimizer_trace 表⽽都内容，该表为联合i表，只能在当前会话进⾏查询，每次查询后返回的都是最近⼀次执⾏的 SQL 语句。

Q32：EXPLAIN 的字段有哪些，具有什么含义？

执⾏计划是 SQL 调优的⼀个重要依据，可以通过 EXPLAIN 命令查看 SQL 语句的执⾏计划，如果作⽤在表上，那么该命令相当于 DESC。EXPLAIN 的指标及含义如下：

Q33：有哪些优化 SQL 的策略？

优化 COUNT 查询

COUNT 是⼀个特殊的函数，它可以统计某个列值的数量，在统计列值时要求列值是⾮空的，不会统计NULL 值。如果在 COUNT 中指定了列或列的表达式，则统计的就是这个表达式有值的结果数，⽽不是NULL。

COUNT 的另⼀个作⽤是统计结果集的⾏数，当 MySQL 确定括号内的表达式不可能为 NULL 时，实际上就是在统计⾏数。当使⽤ COUNT(*) 时，* 不会扩展成所有列，它会忽略所有的列⽽直接统计所有的⾏数。

某些业务场景并不要求完全精确的 COUNT 值，此时可以使⽤近似值来代替，EXPLAIN 出来的优化器估算的⾏数就是⼀个不错的近似值，因为执⾏ EXPLAIN 并不需要真正地执⾏查询。

通常来说 COUNT 都需要扫描⼤量的⾏才能获取精确的结果，因此很难优化。在 MySQL 层还能做的就只有覆盖扫描了，如果还不够就需要修改应⽤的架构，可以增加汇总表或者外部缓存系统。

优化关联查询

确保 ON 或 USING ⼦句中的列上有索引，在创建索引时就要考虑到关联的顺序。

确保任何 GROUP BY 和 ORDER BY 的表达式只涉及到⼀个表中的列，这样 MySQL 才有可能使⽤索引来优化这个过程。

在 MySQL 5.5 及以下版本尽量避免⼦查询，可以⽤关联查询代替，因为执⾏器会先执⾏外部的 SQL 再执⾏内部的 SQL。

优化 GROUP BY

如果没有通过 ORDER BY ⼦句显式指定要排序的列，当查询使⽤ GROUP BY 时，结果***⾃动按照分组的字段进⾏排序，如果不关⼼结果集的顺序，可以使⽤ ORDER BY NULL 禁⽌排序。

优化 LIMIT 分⻚

在偏移量⾮常⼤的时候，需要查询很多条数据再舍弃，这样的代价⾮常⾼。要优化这种查询，要么是在⻚⾯中限制分⻚的数量，要么是优化⼤偏移量的性能。最简单的办法是尽可能地使⽤覆盖索引扫描，⽽ 不是查询所有的列，然后根据需要做⼀次关联操作再返回所需的列。

还有⼀种⽅法是从上⼀次取数据的位置开始扫描，这样就可以避免使⽤ OFFSET。其他优化⽅法还包括使⽤预先计算的汇总表，或者关联到⼀个冗余表，冗余表只包含主键列和需要做排序的数据列。

优化 UNION 查询

MySQL 通过创建并填充临时表的⽅式来执⾏ UNION 查询，除⾮确实需要服务器消除重复的⾏，否则⼀定要使⽤ UNION ALL，如果没有 ALL 关键字，MySQL 会给临时表加上 DISTINCT 选项，这会导致对整个临时表的数据做唯⼀性检查，这样做的代价⾮常⾼。

使⽤⽤户⾃定义变量

在查询中混合使⽤过程化和关系化逻辑的时候，⾃定义变量可能会⾮常有⽤。⽤户⾃定义变量是⼀个⽤来存储内容的临时容器，在连接 MySQL 的整个过程中都存在，可以在任何可以使⽤表达式的地⽅使⾃定义变量。例如可以使⽤变量来避免重复查询刚刚更新过的数据、统计更新和插⼊的数量等。

优化 INSERT

需要对⼀张表插⼊很多⾏数据时，应该尽量使⽤⼀次性插⼊多个值的 INSERT 语句，这种⽅式将缩减客户端与数据库之间的连接、关闭等消耗，效率⽐多条插⼊单个值的 INSERT 语句⾼。也可以关闭事务的⾃动提交，在插⼊完数据后提交。当插⼊的数据是按主键的顺序插⼊时，效率更⾼。

Q34：MySQL 主从复制的作⽤？

复制解决的基本问题是让⼀台服务器的数据与其他服务器保持同步，⼀台主库的数据可以同步到多台备      库上，备库本身也可以被配置成另外⼀台服务器的主库。主库和备库之间可以有多种不同的组合⽅式。

MySQL ⽀持两种复制⽅式：基于⾏的复制和基于语句的复制，基于语句的复制也称为逻辑复制，从MySQL 3.23 版本就已存在，基于⾏的复制⽅式在 5.1 版本才被加进来。这两种⽅式都是通过在主库上记录⼆进制⽇志、在备库重放⽇志的⽅式来实现异步的数据复制。因此同⼀时刻备库的数据可能与主库     存在不⼀致，并且⽆法包装主备之间的延迟。

MySQL 复制⼤部分是向后兼容的，新版本的服务器可以作为⽼版本服务器的备库，但是⽼版本不能作为新版本服务器的备库，因为它可能⽆法解析新版本所⽤的新特性或语法，另外所使⽤的⼆进制⽂件格 式也可能不同。

复制解决的问题：数据分布、负载均衡、备份、⾼可⽤性和故障切换、MySQL 升级测试。

Q35：MySQL 主从复制的步骤？

① 在主库上把数据更改记录到⼆进制⽇志中。 ② 备库将主库的⽇志复制到⾃⼰的中继⽇志中。 ③ 备库读取中继⽇志中的事件，将其重放到备库数据之上。

第⼀步是在主库上记录⼆进制⽇志，每次准备提交事务完成数据更新前，主库将数据更新的事件记录到

⼆进制⽇志中。MySQL 会按事务提交的顺序⽽⾮每条语句的执⾏顺序来记录⼆进制⽇志，在记录⼆进制⽇志后，主库会告诉存储引擎可以提交事务了。

下⼀步，备库将主库的⼆进制⽇志复制到其本地的中继⽇志中。备库⾸先会启动⼀个⼯作的 IO 线程， IO    线程跟主库建⽴⼀个普通的客户端连接，然后在主库上启动⼀个特殊的⼆进制转储线程，这个线程会读取主库上⼆进制⽇志中的事件。它不会对事件进⾏轮询。如果该线程追赶上了主库将进⼊睡眠状态，  直到主库发送信号量通知其有新的事件产⽣时才会被唤醒，备库 IO 线程会将接收到的事件记录到中⽇志中。

备库的 SQL 线程执⾏最后⼀步，该线程从中继⽇志中读取事件并在备库执⾏，从⽽实现备库数据的更新。当 SQL 线程追赶上 IO 线程时，中继⽇志通常已经在系统缓存中，所以中继⽇志的开销很低。SQL 线程执⾏的时间也可以通过配置选项来决定是否写⼊其⾃⼰的⼆进制⽇志中。