数据库 —— MySQL 相关的面经。

既可以临阵磨枪，也可以 作为 对知识结构树的补充。

=============================================================

技术用时猛如虎，面试一到三不知 orz

---

👆←🗎^[1]

索引（index）

需求场景：只有 区分度 高的字段，才适合 建立索引（index）。

• 索引类型：

  • 主键索引

  • 唯一索引

  • 普通索引

  • 全文索引

• 索引的基本原理：

• 索引的数据结构：

  • B树（B Tree）索引

  • 哈希（Hash）索引

• 索引的算法：

  • B Tree算法

  • 哈希（Hash）算法

索引的设计原则

索引的创建原则

备注：重中之重。

1. 最左前缀匹配原则。

   在组合索引中，非常重要的原则。

2. 频繁作为查询条件 的字段，才适合创建索引。

3. 频繁更新的字段，不适合创建索引。

4. 区分度 高的字段，才适合 建立索引（index）。

5. 定义有外键的字段，一定要建立索引。

6. 查询中很少涉及的字段，以及 数据重复度较高的字段，不应该建立索引。

7. 定义为text、image 和 bit数据类型的字段，不应该建立索引。

8. 尽量地拓展 已有的索引，而不是创建 新的索引。

性能优化

查询优化

慢查询日志

MySQL的慢查询日志是MySQL提供的一种日志记录，它用来记录在MySQL中响应时间超过阀值的语句，具体指运行时间超过long_query_time值的SQL，则会被记录到慢查询日志中。

long_query_time的默认值为10，意思是运行10S以上的语句。

默认情况下，Mysql数据库并不启动慢查询日志，需要我们手动来设置这个参数，当然，如果不是调优需要的话，一般不建议启动该参数，因为开启慢查询日志会或多或少带来一定的性能影响。慢查询日志支持将日志记录写入文件，也支持将日志记录写入数据库表。

• 慢查询日志相关参数

  MySQL 慢查询的相关参数解释：

  • slow_query_log ：是否开启慢查询日志，1表示开启，0表示关闭。

  • log-slow-queries ：旧版（5.6以下版本）MySQL数据库慢查询日志存储路径。可以不设置该参数，系统则会默认给一个缺省的文件host_name-slow.log

  • slow-query-log-file：新版（5.6及以上版本）MySQL数据库慢查询日志存储路径。可以不设置该参数，系统则会默认给一个缺省的文件host_name-slow.log

  • long_query_time ：慢查询阈值，当查询时间多于设定的阈值时，记录日志。

  • log_queries_not_using_indexes：未使用索引的查询也被记录到慢查询日志中（可选项）。

  • log_output：日志存储方式。log_output=‘FILE’表示将日志存入文件，默认值是’FILE’。log_output=’TABLE’表示将日志存入数据库，这样日志信息就会被写入到mysql.slow_log表中。

    MySQL数据库支持同时两种日志存储方式，配置的时候以逗号隔开即可，如：log_output=‘FILE,TABLE’。日志记录到系统的专用日志表中，要比记录到文件耗费更多的系统资源，因此对于需要启用慢查询日志，又需要能够获得更高的系统性能，那么建议优先记录到文件。

慢查询优化

索引没起作用的情况

• ⤷ 使用LIKE关键字的查询语句

  在使用LIKE关键字进行查询的查询语句中，如果匹配字符串的第一个字符为“%”，索引不会起作用。只有“%”不在第一个位置时，索引才会起作用。

• ⤷ 使用多列索引的查询语句

  MySQL 可以为多个字段 创建索引。一个索引 最多可以包括16个字段。

  对于多列索引，只有 查询条件使用了这些字段中的第一个字段时，该索引 才会被使用。

优化数据库结构

合理的数据库结构不仅可以使数据库占用更小的磁盘空间，而且能够使查询速度更快。

数据库结构的设计，需要考虑 数据冗余、查询和更新的速度、字段的数据类型是否合理 等 多方面的内容。

• 将字段很多的表分解成多个表

对于字段比较多的表，如果有些字段的使用频率很低，可以将这些字段分离出来形成新表。因为当一个表的数据量很大时，会由于使用频率低的字段的存在而变慢。

• 增加中间表

对于需要经常联合查询的表，可以建立中间表以提高查询效率。

通过建立中间表，把需要经常联合查询的数据插入到中间表中，然后将原来的联合查询改为对中间表的查询，以此来提高查询效率。

分解关联查询

将一个大的查询分解为多个小查询是很有必要的。

很多高性能的应用都会对关联查询进行分解，就是可以对每一个表进行一次单表查询，然后将查询结果在应用程序中进行关联。

很多场景下这样会更高效，例如：

1
2
3
4

SELECT * FROM tag
JOIN tag_post ON tag_id = tag.id
JOIN post ON tag_post.post_id = post.id
WHERE tag.tag = ‘mysql’;

分解为：

1
2
3

SELECT * FROM tag WHERE tag = 'mysql';
SELECT * FROM tag_post WHERE tag_id = 1234;
SELECT * FROM post WHERE post.id in (123,456,567);

优化LIMIT分页

在系统中需要分页的操作通常会使用limit加上偏移量的方法实现，同时加上合适的order by子句。如果有对应的索引，通常效率会不错，否则MySQL需要做大量的文件排序操作。

一个非常令人头疼问题就是 当偏移量非常大的时候，例如可能是limit 10000,20这样的查询，这是Mysql需要查询10020条然后只返回最后20条，前面的10000条记录都将被舍弃，这样的代价很高。
优化此类查询的一个最简单的方法是尽可能的使用索引覆盖扫描，而不是查询所有的列。然后根据需要做一次关联操作再返回所需的列。对于偏移量很大的时候这样做的效率会得到很大提升。

日志分析工具

📖参看

※参考和引用

1. ^ 维基百科，自由的百科全书

🔗外部链接

• Wikipedia's external link ltr-icon