共计 4837 个字符，预计需要花费 13 分钟才能阅读完成。
摘要: 无论在平时开发中还是面试中咱们都会遇到一些 MySQL 的问题, 最近也在梳理一些 MySQL 的一些问题和整顿了一些学习思维导图，心愿对你有帮忙, 也欢送大家一起交换。本文首发于公众号:
散步 coding
MySQL 学习思维导图
该图以 Markdown 绘制思维导图的开源工具——markmap(https://markmap.js.org/repl)主动渲染进去的, 如果须要 Markdown 原稿(看的更加清晰), 关注公众号, 回复 mysql 即可取得。
面试中 MySQL 一些常见的问题
1、CHAR、VARCHAR 的区别是什么？
VARCHAR 类型用于存储可变长度字符串，是最常见的字符串数据类型。它比固定长度类型更节俭空间，因为它仅应用必要的空间(依据理论字符串的长度扭转存储空间)。
VARCHAR 节俭了存储空间，所以对性能也有帮忙。然而，因为行是变长的，在 UPDATE 时可能使行变得比原来更长，这就导致须要做额定的工作。如果一个行占用的空间增长，并且在页内没有更多的空间能够存储，在这种状况下，不同的存储引擎的解决形式是不一样的。例如，MylSAM 会将行拆成不同的片段存储，InnoDB 则须要决裂页来使行能够放进页内。
CHAR 类型用于存储固定长度字符串：MySQL 总是依据定义的字符串长度调配足够的空间。当存储 CHAR 值时，MySQL 会删除字符串中的开端空格(在 MySQL 4.1 和更老版本中 VARCHAR 也是这样实现的——也就是说这些版本中 CHAR 和 VARCHAR 在逻辑上是一样的，区别只是在存储格局上)。
同时，CHAR 值会依据须要采纳空格进行残余空间填充，以不便比拟和检索。但正因为其长度固定，所以会占据多余的空间，也是一种空间换工夫的策略。
CHAR 适宜存储很短或长度近似的字符串。例如，CHAR 非常适合存储明码的 MD5 值，因为这是一个定长的值。对于常常变更的数据，CHAR 也比 VARCHAR 更好，因为定长的 CHAR 类型不容易产生碎片。对于十分短的列，CHAR 比 VARCHAR 在存储空间上也更有效率。例如用 CHAR(1)来存储只有 Y 和 N 的值，如果采纳单字节字符集只须要一个字节，然而 VARCHAR(1)却须要两个字节，因为还有一个记录长度的额定字节。
2、TRUNCATE 和 DELETE 的区别是什么？
DELETE 命令从一个表中删除某一行，或多行，TRUNCATE 命令永恒地从表中删除每一行。
3、什么是触发器，MySQL 中都有哪些触发器？
触发器是指一段代码，当触发某个事件时，主动执行这些代码。在 MySQL 数据库中有如下六种触发器：1.Before Insert2.After Insert3.Before Update4.After Update5.Before Delete6.After Delete
4、FLOAT 和 DOUBLE 的区别是什么？
- FLOAT 类型数据能够存储至少 8 位十进制数，并在内存中占 4 字节。
- DOUBLE 类型数据能够存储至少 18 位十进制数，并在内存中占 8 字节。
5、如何在 MySQL 种获取以后日期？
SELECT CURRENT_DATE()6、如何查问第 N 高的分数？
这里有几个留神点:
- 因为问题可能有一样的值，所以应用 DISTINCT 进行去重
- 如果不存在第 N 高问题的分数，那么查问应返回 null
- 把下方的 N 换成具体的数值查问
select ifNull(
(select distinct `score`
from students
order by score Desc
limit N-1,1),null
) as target_score7、请阐明 InnoDB 和 MyISAM 的区别
这个在面试过程中过的比拟多。
- InnoDB 反对事务，MyISAM 不反对；
- InnoDB 数据存储在共享表空间，MyISAM 数据存储在文件中；
- InnoDB 反对行级锁，MyISAM 只反对表锁；
- InnoDB 反对解体后的复原，MyISAM 不反对；
- InnoDB 反对外键，MyISAM 不反对；
- InnoDB 不反对全文索引，MyISAM 反对全文索引；
8、innodb 引擎的个性
- 1、插入缓冲（insert buffer)
- 2、二次写(double write)
- 3、自适应哈希索引(ahi)4、预读(read ahead)
9、请阐明 varchar 和 text 的区别
- varchar 可指定字符数，text 不能指定，外部存储 varchar 是存入的理论字符数 + 1 个字节（n<=255）或 2 个字节(n>255)，text 是理论字符数 + 2 个字节。
- text 类型不能有默认值。
- varchar 可间接创立索引，text 创立索引要指定前多少个字符。
- varchar 查问速度快于 text, 在都创立索引的状况下，text 的索引简直不起作用。
- 查问 text 须要创立长期表。
10、简略形容 MySQL 中，索引，主键，惟一索引，联结索引的区别，对数据库的性能有什么影响？
这个在面试过程中过的比拟多。
- 一个表只能有一个主键索引，然而能够有多个惟一索引。
- 主键索引肯定是惟一索引，惟一索引不是主键索引。
- 主键能够与外键形成参照完整性束缚，避免数据不统一。
- 联结索引：将多个列组合在一起创立索引，能够笼罩多个列。（也叫复合索引，组合索引）
- 外键索引：只有 InnoDB 类型的表才能够应用外键索引，保证数据的一致性、完整性、和实现级联操作（根本不必）。
- 全文索引：MySQL 自带的全文索引只能用于 MyISAM，并且只能对英文进行全文检索（根本不必）
11、创立 MySQL 联结索引应该留神什么？
需遵循前缀准则
12、列值为 NULL 时，查问是否会用到索引？
在 MySQL 里 NULL 值的列也是走索引的。当然，如果打算对列进行索引，就要尽量避免把它设置为可空，MySQL 难以优化援用了可空列的查问, 它会使索引、索引统计和值更加简单。
13：以下语句是否会利用索引：SELECT FROM users WHERE YEAR(adddate) < 2019
不会，因为只有列波及到运算，MySQL 就不会应用索引。
14、MyISAM 索引实现？
MyISAM 存储引擎应用 B +Tree 作为索引构造，叶节点的 data 域寄存的是数据记录的地址。MyISAM 的索引形式也叫做非聚簇索引的，之所以这么称说是为了与 InnoDB 的聚簇索引辨别。
15、MyISAM 索引与 InnoDB 索引的区别？
- InnoDB 索引是聚簇索引，MyISAM 索引是非聚簇索引。
- InnoDB 的主键索引的叶子节点存储着行数据，因而主键索引十分高效。
- MyISAM 索引的叶子节点存储的是行数据地址，须要再寻址一次能力失去数据。
- InnoDB 非主键索引的叶子节点存储的是主键和其余带索引的列数据，因而查问时做到笼罩索引会十分高效。
16、MySQL 的关联查问语句你会那些？
六种关联查问
- 1. 穿插连贯（CROSS JOIN）
- 2. 内连贯（INNER JOIN）
- 3. 外连贯（LEFT JOIN/RIGHT JOIN）
- 4. 联结查问（UNION 与 UNION ALL）
- 5. 全连贯（FULL JOIN）
- 6. 穿插连贯（CROSS JOIN）
平时用到比拟多的是内连和外连，以及联结查问
a)、内连贯分为三类:
等值连贯：ON A.id=B.id
不等值连贯：ON A.id > B.id
自连贯：SELECT * FROM A T1 INNER JOIN A T2 ON T1.id=T2.pid
b)、外连贯 分为: 左外连贯、右外连贯
左外连贯：LEFT OUTER JOIN, 以左表为主，先查问出左表，依照 ON 后的关联条件匹配右表，没有匹配到的用 NULL 填充，能够简写成 LEFT JOIN
右外连贯：RIGHT OUTER JOIN, 以右表为主，先查问出右表，依照 ON 后的关联条件匹配左表，没有匹配到的用 NULL 填充，能够简写成 RIGHT JOIN
c)、联结查问（UNION 与 UNION ALL）
就是把多个后果集集中在一起，UNION 前的后果为基准，须要留神的是联结查问的列数要相等，雷同的记录行会合并
如果应用 UNION ALL，不会合并反复的记录行 3. 效率 UNION 高于 UNION ALL
d)、全连贯（FULL JOIN）
1.MySQL 不反对全连贯
2. 能够应用 LEFT JOIN 和 UNION 和 RIGHT JOIN 联结应用
17、UNION 与 UNION ALL 的区别？
如果应用 UNION ALL，不会合并反复的记录行
效率 UNION 高于 UNION ALL
18、如何优化慢 SQL
应用 explain 剖析单条 SQL 语句
优化查问过程中的数据拜访
- 拜访数据太多导致查问性能降落
- 确定应用程序是否在检索大量超过须要的数据，可能是太多行或列
- 确认 MySQL 服务器是否在剖析大量不必要的数据行
- 防止犯如下 SQL 语句谬误
- 查问不须要的数据。解决办法：应用 limit 解决多表
- 关联返回全部列。解决办法：指定列名
- 总是返回全部列。解决办法：防止应用 SELECT * 反复
- 查问雷同的数据。解决办法：能够缓存数据，下次间接读取缓存
是否在扫描额定的记录。解决办法：应用 explain 进行剖析，如果发现查问须要扫描大量的数据，但只返回多数的行，能够通过如下技巧去优化:
应用索引笼罩扫描，把所有的列都放到索引中，这样存储引擎不须要回表获取对应行就能够返回后果。
扭转数据库和表的构造，批改数据表范式
重写 SQL 语句，让优化器能够以更优的形式执行查问。
19、优化长难的查问语句
一个简单查问还是多个简略查问
MySQL 外部每秒能扫描内存中上百万行数据，相比之下，响应数据给客户端就要慢得多应用尽可能小的查问是好的，然而有时将一个大的查问合成为多个小的查问是很有必要的。
切分查问: 将一个大的查问分为多个小的雷同的查问
一次性删除 1000 万的数据要比一次删除 1 万，暂停一会的计划更加损耗服务器开销。
合成关联查问，让缓存的效率更高。
执行单个查问能够缩小锁的竞争。
在应用层做关联更容易对数据库进行拆分。
查问效率会有大幅晋升。
较少冗余记录的查问。
20、优化特定类型的查问语句
count(*)会疏忽所有的列，间接统计所有列数，不要应用 count(列名)
MyISAM 中，没有任何 where 条件的 count(*)十分快。
当有 where 条件时，MyISAM 的 count 统计不肯定比其它引擎快。
能够应用 explain 查问近似值，用近似值代替 count(*)
减少汇总表
应用缓存
21、优化关联查问
确定 ON 或者 USING 子句中是否有索引。
确保 GROUP BY 和 ORDER BY 只有一个表中的列，这样 MySQL 才有可能应用索引。
22、优化子查问
用关联查问代替
优化 GROUP BY 和 DISTINCT
这两种查问据能够应用索引来优化，是最无效的优化办法
关联查问中，应用标识列分组的效率更高
如果不须要 ORDER BY，进行 GROUP BY 时加 ORDER BY NULL，MySQL 不会再进行文件排序。
WITH ROLLUP 超级聚合，能够挪到利用程序处理
23、优化 LIMIT 分页
LIMIT 偏移量大的时候，查问效率较低
能够记录上次查问的最大 ID，下次查问时间接依据该 ID 来查问
24、优化 UNION 查问
UNION ALL 的效率高于 UNION
25、优化 WHERE 子句
解题办法对于此类考题，先阐明如何定位低效 SQL 语句，而后依据 SQL 语句可能低效的起因做排查，先从索引着手，如果索引没有问题，思考以上几个方面，数据拜访的问题，长难查问句的问题还是一些特定类型优化的问题，逐个答复。
26、乐观锁和乐观锁
这个问题个别在面试中都会问到
乐观锁
总是假如最坏的状况，每次取数据时都认为其余线程会批改，所以都会加（乐观）锁。一旦加锁，不同线程同时执行时, 只能有一个线程执行，其余的线程在入口处期待，直到锁被开释。
乐观锁
顾名思义就是在操作时很乐观，认为操作不会产生并发问题 (不会有其余线程对数据进行批改)，因而不会上锁。然而在更新时会判断其余线程在这之前有没有对数据进行批改，个别会应用版本号机制或 CAS(compare and swap) 算法实现。
小结
后续，还会持续更新 MySQL 相干的内容，如果对您有帮忙, 能够珍藏本文或者关注我的公众号: 散步 coding, 一起交换，一起学习。
