MySQL面试题学习（三）

16.索引有什么作用

索引也叫键，是存储引擎用于快速找到记录的一种数据结构。索引减少了服务器需要扫描的数据量，但索引并不是最好的工具，对于非常小的表，大部分情况下，会采用全表扫描；对于中到大型的表，索引就非常有效；对于特大型的表，建立和使用索引的代价也随之增长，这种情况下应该使用分区技术。

17.索引失效的情况有哪些

1. 如果索引中出现了隐式类型转换，则MySQL不会使用索引。
   常⻅的情况是在 SQL 的 WHERE 条件中字段类型为字符串，其值为数值，如果没有加引号那么 MySQL 不会使⽤索引。
2. 如果WHERE 条件中含有OR，除非OR前使用了索引列而OR之后是非索引列，索引会失效。
3. 执行LIKE操作时，索引会失效。
4. 如果查询中的列不是独⽴的，则 MySQL 不会使⽤索引。独⽴的列是指索引列不能是表达式的⼀部分， 也不能是函数的参数。
5. 如果 MySQL 判断全表扫描⽐使⽤索引查询更快，则不会使⽤索引。

18.如何定位低效 SQL？

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

19.SHOW PROFILE 的作⽤？

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

20.trace 是⼲什么的？

trace是MySQL5.6版本后提供的SQL跟踪工具，通过使用trace可以让我们明白optimizer（优化器）如何选择执行计划。
开启trace工具会影响mysql性能，所以只适合临时分析sql使用，用完之后请立即关闭。

21.EXPLAIN 的字段有哪些，具有什么含义？

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

1. id：表示 SELECT 子句或操作表的顺序，执行顺序从大到小执行，当 id ⼀样时，执行顺序从上往下。
2. select_type：表示查询中每个 SELECT ⼦句的类型。SIMPLE表示不包含⼦查询、表连接或其他复杂语法的简单查询；PRIMARY 表示复杂查询的最外层查询；SUBQUERY 表示在 SELECT 或WHERE 列表中包含了⼦查询。
3. type：表示访问类型。性能由差到好为：ALL 全表扫描、index 索引全扫描、range 索引范围扫描、ref 返回匹配某个单独值得所有行，eq_ref 唯⼀性索引扫描，对于每个索引键只有⼀条记录与之匹配，const 当 MySQL 对查询某部分进⾏优化，并转为⼀个常量时，使⽤这些访问类型，例如将主键或唯⼀索引置于 WHERE 列表就能将该查询转为⼀个 const、system 表中只有⼀⾏数据或空表，只能⽤于 MyISAM 和 Memory 表、NULL 执⾏时不⽤访问表或索引就能得到结果。SQL 性能优化的⽬标：⾄少要达到 range 级别，要求是 ref 级别，如果可以是consts 最好。
4. possible_keys：表示查询时可能⽤到的索引，但不⼀定使⽤。
5. key：显示 MySQL 在查询时实际使⽤的索引，如果没有使⽤则显示为 NULL。
6. 表示使⽤到索引字段的⻓度，可通过该列计算查询中使⽤的索引的⻓度，对于 确认索引有效性以及多列索引中⽤到的列数⽬很重要。
7. ref：表示上述表的连接匹配条件，即哪些列或常量被⽤于查找索引列上的值。
8. rows：表示 MySQL 根据表统计信息及索引选⽤情况，估算找到所需记录所需要读取的⾏数。
9. Extra：表示额外信息，例如 Using temporary 表示需要使⽤临时表存储结果集，常⻅于排序和分组查询。Using filesort 表示⽆法利⽤索引完成的⽂件排序，这是ORDER BY 的结果，可以通过合适的索引改进性能。Using index 表示只需要使
   ⽤索引就可以满⾜查询表得要求，说明表正在使⽤覆盖索引。

22.MySQL 主从复制的作⽤？

复制解决的基本问题是让一台服务器的数据与其他服务器保持同步，一台主库的数据可以同步到多台备库上，备库本身也可以被配置成另外一台服务器的主库。主库和备库之间可以由多种不同的组合方式。
MySQL支持两种复制方式：基于行的复制和基于语句的复制，基于语句的复制也称为逻辑复制，从MySQL3.23版本就已经存在，基于行的复制方式在5.1版本才被加进来。
这两种方式都是通过在主库上记录二进制日志、在备库重放日志的方式来实现异步的数据复制。因此同一时刻备库的数据可能与主库存在不一致，并且无法包装主备之间的延迟。
复制解决的问题：数据分布、负载均衡、备份、高可用性和故障切换、MySQL升级测试。

23.MySQL 主从复制的步骤？

1. 在主库上把数据更改记录到二进制中
2. 备库将主库的日志复制到自己的中继日志中
3. 备库读取中继日志中的事件，将其重放到备库数据之上

第一步是在主库上记录二进制日志，每次准备提交事务完成数据更新前，主库将数据更新的事件记录到二进制日志中。MySQL会按事务提交的顺序而非每条语句的执行顺序来记录二进制日志，在记录二进制日志后，主库会告诉存储引擎可以提交事务了。
下一步，备库将主库的二进制日志复制到本地的中继日志中。备库首先会启动一个工作的IO线程，IO线程跟主库建立一个普通的客户端连接，然后在主库上启动一个特殊的二进制转储线程，这个线程会读取主库上二进制日志中的事件。它不会对事件进⾏轮询。如果该线程追赶上了主库将进⼊睡眠状态， 直到主库发送信号量通知其有新的事件产⽣时才会被唤醒，备库 IO 线程会将接收到的事件记录到中⽇志中。
备库的SQL线程执行最后一步，该线程从中继日志中读取事件并在备库执行，从而实现备库数据的更新。当 SQL 线程追赶上 IO 线程时，中继⽇志通常已经在系统缓存中，所以中继⽇志的开销很低。SQL 线程执⾏的时间也可以通过配置选项来决定是否写⼊其⾃⼰的⼆进制⽇志中。