JAVA面试专题-MySQL

锁

全局锁

对这个数据库实例加锁，加锁后整个实例处于只读状态，DDL和DML阻塞，DQL可以

表级锁

每次操作锁住整张表

表锁

表共享读锁（read lock）：不阻塞其他客户端的读，但会阻塞写

表独占写锁（write lock）：阻塞其他客户端的读写

元数据锁（meta data lock MDL）

如果某一个表存在活动事务，不可以对元数据写入操作，当对一张表进行增删改查的时候，加读锁，当对表结构进行变更操作的时候，加写锁

read和write兼容，和exclusive不兼容

意向锁

意向共享锁（IS）：与read兼容，和write排斥
意向排他锁（IX）：与read和write互斥，意向锁之间不会互斥

行级锁

每次操作锁住对应的行数据，发生锁冲突的概率最低，并发度最高

行锁

锁定单行记录，防止其他事务对此进行update和delete，RC和RR都支持

共享锁S：允许一个事务去读一行，阻止其他事务获得相同数据集的排他锁

排他锁X：运行获取排他锁的事务更新数据，阻止其他事务获得相同数据集的共享锁和排他锁

1. 针对唯一索引进行检索时，对已存在的记录进行等值匹配，将会自动优化为行锁
2. InnoDB的行锁针对索引加的锁，不通过索引条件检索数据，将会升级为表锁

间隙锁

间隙锁：锁定索引记录间隙，确保索引记录间隙不变，防止其他事物在这个间隙进行insert，产生幻读，RR支持

临键锁

临键锁：行锁和间隙锁组合，同时锁住数据和数据前面的间隙，RR支持

SQL优化

如何定位慢查询

常见的慢查询有聚合查询，多表查询，表数据量过大查询，深度分页查询

方案一：开源工具 Skywalking

方案二：MySQL慢查询日志，在MySQL的配置文件开启开关和时间

如何定位慢查询

使用explain或者desc获取MySQL语句信息

possible_keys 当前sql可能会用到的索引
key 当前sql实际命中的索引
key_len 索引占用的大小
extra 额外的优化建议 using where/index 使用索引并且没回表查询 using index condition 使用索引但是回表查询
type system:查询系统中的表；const：主键查询; eq_ref:主键索引查询或唯一索引查询
ref：索引查询；range：范围查询；index：索引树扫描；all：全盘扫描

MySQL支持的存储引擎有哪些，什么区别？

InnoDB

特点：事务外键行级锁

索引

索引：帮助MySQL高效获取数据的数据结构，提高数据检索效率，降低数据库IO成本，通过索引列对数据进行排序，降低数据排序成本，降低了cpu的消耗。

B树与B+树对比：
磁盘读写代价b+树更低：非叶子节点只存储指针，只有叶子结点存储数据
查询效率B+树更稳定：阶数更多，路径更短
B+树便于扫库和区间查询：叶子结点是一个双向链表

聚簇索引和非聚簇索引

聚簇索引（聚集索引）：将数据存储与索引放到了一块，索引结构的叶子结点保存了行数据，有且仅有一个。
非聚簇索引（非聚集索引，二级索引）：将数据与索引分开存储，索引结构的叶子结点关联对应的主键，可以存在多个

聚簇索引选取规则：
存在主键，主键索引就是聚簇索引
不存在主键，第一个唯一索引就是聚簇索引
都没有，InnoDB会自动生成一个rowid作为隐藏的聚簇索引

回表查询

回表查询：通过二级索引找到对应的主键值，到聚集索引中查找整行数据，就是回表

覆盖索引

查询使用了索引，并且需要返回的列，在该索引中已经全部能够找到。

MySQL超大分页处理

数据量较大时，如果进行limit分页查询，在查询时，越往后，分页查询效率越低

优化思路：覆盖索引+子查询：先分页查询id字段，再用子查询来过滤

索引创建原则

1. 数据量较大，且查询比较频繁的表
2. 常作为条件查询、排序、分组的字段
3. 尽量使用联合索引，避免回表
4. 要控制索引的数量
5. 尽量选择区分度高的列作为索引
6. 如果所以不能存储null，要用非空约束
7. 字段名称过长可以使用前缀

索引失效

1. 违反最左前缀法则：如果索引了多列，要遵守最左前缀法则：查询从索引的最左前列开始，并且不能跳过索引中的列，匹配最左前缀法则，走索引。如果符合最左法则，但是出现跳跃某一列，只有最左列索引生效
2. 如果一个索引使用了范围查询，其右面的索引不生效
3. 在索引列上运算操作，索引将失效
4. 字符串不加单引号，索引失效
5. 模糊查询如果有头部匹配，索引失效