mysql数据库常见面试题

• 慢查询排查优化
  • 排查
    slow_query_log设置为on，就会记录慢查询sql；long_query_time可以设置慢查询sql的阈值时间；slow_query_log_file表示记录慢查询sql的日志路径。即我们可以通过打开记录慢查询的开关，设置慢查询的时间阈值，查看日志就能看到慢查询的执行sql。然后使用explain查看sql的执行计划，主要看type字段判断是否走索引了。
  • 优化
    1. 子查询优化，使用join代替in，子查询的话mysql会建立临时表，再把临时表销毁，效率会很低
    2. 字段优化，尽量使用整形，不要设置太长，避免between、like、<、>范围查询导致的全表查询
    3. 经常使用的字段创建索引
    4. 事先准备好报表，避免查询时计算数据导致的性能下降
    5. 分库分表
    6. 历史数据迁移
    7. 使用redis缓存数据，减少查询次数
• 聚集索引和非聚集索引
  • 聚集索引
    聚集索引指的就是主键索引，索引和数据是存放在一起的，故名聚集。mysql的数据在磁盘上是以页的形式进行存储的，每页中的数据按主键的值从小到大进行排列，因此可以通过目录页对数据查询进行维护，每个目录都记录了数据的页数以及该页的最小键值。在查询时根据先可以目录项定位要查询的数据所在的页数，再到数据页中按主键值通过二分法进行查询。
  • 非聚集索引
    非聚集索引的数据结构与聚集索引类似，不过在数据项中存储的是主键，查询的时候先通过非聚集索引查询得到主键，再通过主键回表查询具体的数据。
  • 区别
    主键索引叶子节点存储的是具体的数据，而非聚集索引叶子节点存储的主键。聚集索引每张表只能有一个，非聚集索引每张表可以有多个。聚集索引在内存上是线性的，而非聚集索引在内存上不是线性的，逻辑上是线性的。
• limit查询变慢的原因
  limit语句会先扫描offset+n行，然后再丢弃掉前offset行，返回后n行数据。当limit后面跟的值比较大，mysql查询时扫描的行数变多，导致查询的时间变长。因此limit的第一个参数不要设置的太大，可以通过主键id找到要查询的位置再进行limit。
• mysql事务隔离级别
  读未提交；读已提交；可重复读；序列化。可重复读底层是通过mvcc实现的。
• mysql的char和varchar的区别
  1. varchar 类型的长度是可变的，而 char 类型的长度是固定的
  2. char 长度最大为 255 个字符，varchar 长度最大为 65535 个字符
  3. varchar 类型的查找效率比较低，而 char 类型的查找效率比较高
• DATETIME 和 TIMESTAMP 的区别
  1. DATETIME比TIMESTAMP的范围要广
  2. TIMESTAMP占4个字节，DATETIME占用8个字节
  3. TIMESTAMP会时间在当前时间和UTC之间进行转化，DATETIME直接保存和返回
• 什么情况下会创建索引
  1. 字段的值是唯一
  2. 表的数据量比较大并且频繁作为where的查询条件
• 索引失效的情况
  1. 不遵循最左前缀原则
  2. 索引上有通配符
  3. 索引的类型和匹配类型不一致
  4. 索引上存在内置函数
  5. or连接了非索引字段
  6. 范围查询时数据量过大
• 索引优化
  1. 避免回表
  2. 索引最好设置为not null
• explain调试sql