经典面试题7（索引的最左原则，InnoDB 和 MyIsam 引擎，优化数据库性能，慢查询，行锁，表锁）

目录

1,MySQL 索引的最左原则

2，InnoDB 和 MyIsam 引擎的区别？

3，有哪些优化数据库性能的方法？

4，如何定位慢查询？

5，MySQL 支持行锁还是表锁？分别有哪些优缺点？

---

1,MySQL 索引的最左原则

        mysql的索引是通过B+树来实现的，不管是普通索引还是联合索引，对于普通索引来说，就是在非节点上记录的是索引的值，叶子节点上记录的是主键索引的值，它的排序条件是根据主键的值。例如：

         该图是一个以num为索引的B+树。

        对于联合索引来说，如果以（num，age）为索引，非叶子节点上就是num，age的值，叶子节点上就是num，age两个字段以及主键count的值。

         当num不同的时候，就按照num排序，当num相同时，就按照age排序。

        所以，最左前缀匹配就是：因为索引的底层是一个B+树，如果是联合索引，在构造B+树的时候，就会先按照最左的索引值排序，当左边的相同，再依次按照右边的索引来排序。

        再通过索引查询的时候，需要遵守最左前缀匹配的原则。

        再MYSQL8.0中，加入了索引跳跃扫描。

        对于range查询（范围扫描)，引入了索引跳跃扫描（Index Skip Scan）优化支持不符合组合索引最左前缀原则条件下的SQL，依然能够使用组合索引，减少不必要的扫描。

        他是将最左边的索引设为查询条件，来进行范围查找，但是同样也有限制条件。

        对于最左边的这个索引，取值范围不可以太多，不然会导致更慢，而且最好将区分度高，查询频繁鹅字段放在索引的最左边。

2，InnoDB 和 MyIsam 引擎的区别？

        我们先将区别列出来：

InnoDB	MyIsam
支持事务	不支持事务
支持外键	不支持外键
支持行锁	不支持行锁，支持表锁
写操作效率高	读操作效率高
支持全文索引	支持全文索引
表占用空间较小	表占用空间较大
聚集索引	非聚集索引

        InnoDB：MYSQL默认的事务型引擎，它成为大量短期的事务，短期事务很少回滚；它的性能和自动崩溃恢复的性能，在非事务存储需求中也很流行。

        MyIsam：在MYSQL5.1之前，MyIsam是默认引擎，提供大量的特性，包括全文索引，压缩，空间函数等，但是不支持事务和行锁，缺点就是崩溃后无法自动恢复。

        行锁，表锁：表锁有两种，表共享读锁和表独占写锁。对于MyIsam引擎的表，多个用户可以对同一个表发出读的请求，但是一个用户对表进行写操作，就会阻塞其他用户对这个表的读写。InnoDB引擎的表是通过索引项来加锁实现的，只有通过索引条件监检测数据的时候，InnoDB才会使用行级锁，否则也会使用表级锁。

        物理空间的存储：数据库文件都在data目录下，一个文件夹对应一个数据库，本质是文件的存储。InnoDB在数据库中只存在一个*.frm文件，以及上级目录的libdata文件。MyIsam在磁盘存储为三个文件：1，*.frm文件（存储表定义）2，MYD（MyData，数据文件）3，MY|（MyIndex，索引文件）。所以MyIsam表占用空间大。

        保存数据表中的行数：InnoDB中不保存表的具体行数，执行select count  from table语句的时候，InnoDB要扫描一遍整个表来查找，但是MyIsam只要简单的读出保存的行数即可。

3，有哪些优化数据库性能的方法？

3.1 从硬件角度思考

        采用固态硬盘（SSD）代替机械硬盘，固态硬盘IO读取速度比机械硬盘快。

3.2 从数据库部署结构，采用集群架构，读写分离

        1，采用多个副本机制，当主库不可用，可以从库中选择一台作为主，减少数据库宕机造成的影响。

        2，主从架构可以水平扩展，提升数据库集群整体的并发影响能力。

        缺点：数据冗余。       

3.3 从整体的应用架构出发

        减低数据库的压力，可以在数据库层上设置缓存，redis，缓存热点数据。但是要预防缓存穿透，缓存雪崩的问题。

3.4 从连接数据库出发

        为了避免平凡的建立数据库连接，降低IO的开销，可以采用数据库连接池技术，比如druid连接池，初始话一批连接，当需要连接时，直接从数据库连接池获取已经创建好的连接。

        缺点：客户端如果对关闭没做好，导致链接泄漏，会白白占用连接资源。

3.5 从执行SQL出发

        就是对索引的调优，也就是避免索引出现失效的情况：

1.         针对索引字段，模糊检索时，%不能在最左边。
2.         针对索引字段，不可以添加函数表达式。
3.         针对组合索引，需要满足最左匹配原则。、
4.         针对索引字段，索引字段不能包含表达式计算。
5.         针对索引字段，取值与字段类型要匹配。

3.6 垂直拆库，拆表，水平分库，分表

        高效，解耦，扩展，维护，性能，减少了磁盘 IO。

         MySQL 底层是通过数据页存储的，一条记录占用空间过大会导致跨页

        另外数据库以行为单位将数据加载到内存中，这样表中字段长度较短且访问频率较高，内存能加载更多的数据，命中率更高，减少了磁盘 IO，从而提升了数据库性能。

• 垂直分表：可以把一个宽表的字段按访问频次、是否是大字段的原则拆分为多个表，这样既能使业务清晰，还能提升部分性能。拆分后，尽量从业务角度避免联查，否则性能方面将得不偿失
• 垂直分库：可以把多个表按业务耦合松紧归类，分别存放在不同的库，这些库可以分布在不同服务器，从而使访问压力被多服务器负载，大大提升性能，同时能提高整体架构的业务清晰度，不同的业务库可根据自身情况定制优化方案。但是它需要解决跨库带来的所有复杂问题。
• 水平分库：可以把一个表的数据(按数据行)分到多个不同的库，每个库只有这个表的部分数据，这些库可以分布在不同服务器，从而使访问压力被多服务器负载，大大提升性能。它不仅需要解决跨库带来的所有复杂问题，还要解决数据路由的问题(数据路由问题后边介绍)。
• 水平分表：可以把一个表的数据(按数据行)分到多个同一个数据库的多张表中，每个表只有这个表的部分数据，这样做能小幅提升性能，它仅仅作为水平分库的一个补充优化。

4，如何定位慢查询？

        慢查询就是很慢的查询

        1，定位慢查询可以根据慢日志定位慢查询SQL：

• show varlables like‘%query%’ 查询慢日志相关信息
• slow_query_log 默认是off关闭的，使用时，需要改为on打开　　　　　　
• slow_query_log_file 记录的是慢日志的记录文件
• long_query_time 默认是10S，每次执行的sql达到这个时长，就会被记录
• show status like‘%slow_queries%’  查看慢查询状态
• slow_queries 记录的是慢查询数量 当有一条sql执行一次比较慢时，这个vlue就是1 

        打开慢查询：set global slow_query_log=ON;

        将默认时间改为1秒：set global long_query_time=1;

        2，使用explain等工具分析sql

5，MySQL 支持行锁还是表锁？分别有哪些优缺点？

        MyISAM 只支持表锁，InnoDB 支持表锁和行锁，默认为行锁。

        行锁：访问数据库的时候，锁定整个行数据，防止并发错误。

        表锁：访问数据库的时候，锁定整个表数据，防止并发错误。

        优缺点：

        行级锁：开销大，加锁慢，会出现死锁。锁力度小，发生锁冲突的概率小，并发度最高。
        表级锁：开销小，加锁快，不会出现死锁。锁定粒度大，发生锁冲突的概率最高，并发量最低。