摘要: 无论在平时开发中还是面试中咱们都会遇到一些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_score

7、请阐明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, 一起交换，一起学习。