Java - 3年 MySQL面试被问到的知识点汇总

1. 如何定位并优化慢查询SQL？

根据满日志定位慢查询SQL

# 慢日志的相关配置
show variables like '%quer%';

# 慢查询的数量
show status like '%slow_queries&';

long_query_time 当查询超过该时间时，会记录到slow_query_log_file
slow_query_log 慢日志记录功能是否被打开
slow_query_log_file 慢日志的文件存放位置

使用 explain 等工具分析SQL
修改SQL或者尽量让SQL走索引

2. 联合索引的最左匹配原则的成因？

   最左前缀匹配原则：MySQL 会一直向右匹配直到遇到范围查询（>、<、between、like）就停止匹配，比如 a=3 and b=4 and c>5 and d=6 如果建立(a,b,c,d)顺序的索引，d 是用不到索引的，如果建立(a,b,d,c)的索引则都可以用到，a、b、d的顺序可以任意调整。
   =和in可以乱序，比如 a=1 and b=2 and c=3 建立(a,b,c)索引可以任意顺序,MySQL 的查询优化器会帮你优化成索引可以识别的形式。
   MySQL 对联合索引的处理是以联合索引的第一个索引字段进行排序，然后再以第二个，以此类推，形成B+Tree的索引结构，所以当直接用联合索引第二列进行查询的时候是不走索引，需要全文扫描的。

3. 索引是建立的越多越好吗？

   数据量小的表不需要建立索引，建立会增加额外的索引开销
   数据变更需要维护索引，因此更多的索引意味着更多的维护成本
   更多的索引也意味着需要更多的空间

4. 密集索引和稀疏索引的区别？

   密集索引文件中的每个搜索码值都对应一个索引值
   稀疏索引文件只为索引码的某些值建立索引项

   InnoDB 表有且仅有一个密集索引，而 MyISAM 仅使用稀疏索引。

   InnoDB 密集索引的规则：若一个主键被定义，该主键则作为密集索引；若没有主键被定义，该表的第一个唯一非空索引则作为密集索引；若不满足以上条件，InnoDB 内部会生成一个隐藏主键（密集索引）。

   InnoDB 的数据和文件是存储在同一个文件中的（.ibd），而 MyISAM 的索引和数据是存储在不同的文件中的（.MYI和.MYD）。

5. 索引有哪几种类型？

   • B-Tree 索引
     大多数存储引擎都支持这种索引。InnoDB使用的是B+Tree，不过本质上还是B-Tree。B-Tree 索引适用于全键值、键值范围或键前缀查找。其中键前缀查找只适于根据最左前缀的查找。
   • 哈希索引
     哈希索引（hash index）基于哈希表来实现，只有精确匹配索引所有列的查询才有效。对于每一行数据，存储引擎都会对所有的索引列计算一个哈希码（hash code），哈希码是一个较小的值，并且不同键值的行计算出来的哈希码也是不一样的。在MySQL中，只有 Memory 存储引擎显示的支持哈希索引。InnoDB 引擎有一个功能叫做自适应哈希索引（adaptive hash index）。当InnoDB注意到某些索引值使用频繁时，他会在内存中基于 B-Tree 索引之上再创建一个哈希索引，这样就让 B-Tree 索引具有哈希索引的一些优点。这个功能时完全自动、内部的行为，用户无法控制或者进行设置。
   • 空间数据索引（R-Tree）
     MyISAM 表支持空间索引，可用于地理数据存储。
   • 全文索引
     全文索引不是MySQL擅长的地方，可考虑使用 elasticsearch。

6. MySQL有哪几种存储引擎？

   • InnoDB
     MySQL 默认的事务型存储引擎。采用 MVCC 支持高并发，并且实现了四个标准的隔离级别。其默认的是REPEATABLE READ，并且通过间隙锁（next-key locking）策略防止幻读的出现。InnoDB 表是基于聚簇索引来实现的，所以它对主键查询有很高的性能，不过它的二级索引中必须包含主键列，所以如果主键列很大的话，其它的索引都会很大。
   • MyISAM
     5.1 版本之前默认的存储引擎。支持全文索引、压缩等，不支持事务和行级锁，崩溃后无法安全恢复。

7. 事务有什么特点？

   事务的特点总结下来就是ACID，分别代表原子性（atomicity）、一致性（consistency）、隔离性（isolation）和持久性（durability）。
   原子性是指一个事务必须被视为一个不可分割的最小工作单元，整个事务中的所有操作要么全部提交成功，要么全部失败会滚，对于一个事务来说，不可能只执行其中的一部分操作。
   一致性是指数据库总是从一个一致性的状态转换到另一个一致性的状态。
   隔离线是指一个事务所做的修改在最终的提交前，对其它的事务是不可见的。
   持久性是指事务一旦提交，则其所做的修改就会永久保存到数据库中，即使应用服务崩溃，修改的数据也不会丢失。

8. 事务的隔离级别有哪几种？

   • READ UNCOMMITED（读未提交）
     事务中的修改，即使没有提及，对其它的事务也都是可见的。事务可以读取未提交的数据，会发生脏读。一般在实际中很少使用。
   • READ COMMITED（提交读）/ nonrepeatable read（不可重复读）
     一个事务开始时，只能读取到其它事务提交的数据。也就是说，一个事务不能读取另一个事务没有提交的数据。执行两次相同的查询，可能会得到两个不同的结果。
   • REPEATABLE READ（可重复读）
     可重复读解决了脏读的问题。该级别保证了同一个事务中多次读取同样的记录结果时一致的。理论上避免不了幻读的问题。幻读是指一个事务在查询某一范围内的记录时，另一个事务又在该范围内插入了新的记录，当之前的事务再次读取该范围的记录时，就会产生幻读。InnoDB 存储引擎通过多版本并发控制（MVCC, Multiversion concurrency Control）解决了幻读的问题。
     可重复读是MySQL默认的隔离级别。
   • SERIALIZABLE（可串行化）
     SERIALIZABLE 是最高的隔离级别。。它通过强制事务串行处理，避免了前面说的幻读问题。简单来说，SERIALIZABLE 会在读取的每一行数据上都加锁，所以可能导致大量的超时和锁争用的问题。
     查看 MySQL 的隔离级别： select @@tx_isolation;

9. 数据库的MVCC是什么？

   MVVC的全写是 Multiversion Concurrency Control,翻译过来叫多版本并发控制。不同的存储引擎对MVCC的实现是不同的，典型的有乐观并发控制和悲观并发控制。InnoDB 的MVCC是通过在每行记录后面保存两个隐藏的列来实现的。采用新增两个系统版本号的形式避免对数据加锁操作（乐观锁）。MVCC只在 REPEATABLE READ 和 READ COMMITED 两个隔离级别下工作。

10. select count(id) from person 走的是哪个索引？

    不走主键索引，因为 innoDB 的主键索引采用的聚簇索引的数据存放结构，走主键索引的话，会扫描多的数据，所以走的是其它的index。

11. InnoDB 和 MyISAM 关于锁方面的区别是什么？

    InnoDB 默认用的是行级锁，也支持表级锁；
    给 select 语句加派他锁。 select * from person where id = 3 lock in share mode; 当 select 走索引的时候使用的是行级锁，当不走索引的时候用的表级锁；
    MyISAM 默认用的是表级锁，不支持行级锁。
    给 select 语句加派他锁。 select * from person_myisam where id = 3 for update;