java面试题-MySQL篇

一、如何定位慢查询语句

1.运维工具：面试官应该不想听这个

2.mysql方面解决：开启慢日志查询，mysql自带慢日志，需要在配置文件中配置slow_query_log=1 (开启慢日志查询开关) long_query_time=2设置慢sql时间为2秒，超过两秒会记录

二、如何分析一个执行很慢的SQL

建议：直接使用explain关键字，加在自己的sql前面即可，介绍一下部分重要的字段

possible_keys：当前sql可能用到的索引

key：当前sql实际命中的索引

key_len：索引占用的大小 （通过查看key和key_len来查看是否命中了索引）

Extra：额外的优化建议（如果发生了Using index condition 说明使用了索引但是需要回表查询数据，可以优化）

type：这条sql的连接类型，性能由好到差为Null,system,const,eq_ref,ref,range,index,all

具体介绍：null:说明没有用到表，几乎不会见到

system:说明用到了系统中的表，几乎不会见到

const：根据主键查询

eq_ref:根据主键索引或者唯一索引查询

ref：根据索引查询

range：范围查询

index：索引树扫描

all：全盘扫描

实际开发中，最少要做到range，如果是index或者all，则需要优化sql。

三、索引

索引是帮助mysql高效获取数据的数据结构。在数据之外，数据库还维护者满足特定查找算法的数据结构，就是B+树，这些数据结构以某种方式指向数据，这样就可以在这些数据结构上实现高级查找算法，这种数据结构就是索引。

索引底层数据结构：mysql的InnoDB默认是B+树

选择B+树的原因：1.相比于普通的二叉树，极端情况下的二叉树会形成链表

2.大数据量的情况下使用红黑树会降低查询时间

3.B+树相比于B树的优点是：1：B树的非叶子节点和叶子节点都存放了数据，B+树只有叶子结点存放了数据，非叶子节点存放的是指针，所以磁盘读写代价更低。2：B+树的叶子结点之间是双向链表，所以更便于扫库和区间查询，不需要从头进行查询。3.B+树的阶级更多，路径更短。

聚簇索引：数据和索引放到一起，叶子结点保存了完整的数据，有且只有一个，一般为主键。

非聚簇索引：数据和索引分开存储，叶子结点保存的是对应的主键，可以有多个。

回表查询：通过二级索引找到对应的主键值，到聚簇索引中查找整行数据，这个过程叫回表。

覆盖索引：覆盖索引是指查询使用了索引，并且需要返回的列，在该索引中已经全部能够找到。（如果返回的列中没有创建索引，会触发回表查询，所以尽量不使用select *）

超大分页问题：数据量较大时，如果使用普通的分页查询，效率会很低，因此建议写法是先找到对应的id，然后根据id查询数据，例如：select * from table where id = (select id from table limit 90000 100)，先找到id，在查询数据。

索引创建原则：数据量较大，并且查询比较频繁的表，常作为查询条件，排序，分组的字段，尽量使用联合索引，要控制索引的数量。

索引失效的情况：违反最左前缀法则，索引失效（最左前缀是指聚合索引下，跳过了最左边建立的索引）

范围查询右边的列，不能使用索引（例如进行比较，右面的索引会失效）

不要再索引上进行运算操作，会失效

字符串不加单引号，会导致索引失效

模糊查询，%在前面会导致索引失效

四、sql优化

表的设计优化

设置合适的数值（tinyint int bigint）根据实际情况选择。

设置合适的字符串类型，char定长效率高，varchar可变长度，效率低。

SQL语句优化

不要使用select *。

避免造成索引失效的写法。

尽量用union all 代替union union会多一次过滤，效率低。

避免在where中对字段进行表达式操作。

能用inner join就不要使用左右连接，如果一定要使用要以小表为驱动，内连接会对两个表进行优化，左右连接不会。

主从复制，读写分离

五、事务

事务四大特性：原子性，一致性，隔离性，持久性。

并发事务问题：脏读，幻读，不可重复读。

脏读：一个事物读取到另一个事务还没有提交的数据。

不可重复读：一个事务先后读取同一条记录，但是两次读取的数据不同。

幻读：一个事务按照条件查询数据时，没有对应的数据行，但是在插入数据时，又发现这行数据已经存在。

事务隔离级别：读未提交（什么问题都没有解决），读已提交（解决脏读问题），可重复读（解决脏读和不可重复读问题）（默认），串行化（所有问题都解决，效率较低）。

undo log和redo log

缓冲池：内存中的一个区域，里面可以缓存磁盘上经常操作的真实数据，在执行增删改查操作时，先操作缓冲池中的数据，在刷新到磁盘里面。

数据页：是InnoDB存储引擎磁盘管理的最小单元，每个页大小为16kb，存储的是行数据。

redo log：重做日志，记录的是事务提交时数据页的物理修改，用来实现事务的持久性。该日志由两部分组成：重做日志缓冲以及重做日志文件，前者是在内存中，后者在磁盘中，当事务提交之后会把所有修改信息都存到该日志文件中，用于在刷新脏页到磁盘发生错误时，进行数据恢复。（用于恢复数据，保证事务的持久性）

undo log：记录的是逻辑日志，用户回滚数据，比如delete一条数据，会产生insert语句，用于回滚

六、主从同步原理

1.主库在事务提交时，会把数据变更记录在二进制文件Binlog中。

2.从库读取主库的二进制文件Binlog，写入到从库的中继日志Relay log。

3.从库重新执行文件，变化为自己的数据。

七、分库分表策略

1.垂直分库：以表为依据，根据业务不同将不同的表拆分到不同的库中。

2.垂直分表：以字段为依据，根据字段属性将不同字段拆分到不同表中。

3.水平分库：将一个库的数据拆分到不同的库中。

4.水平分表，将一个表的数据拆分到多个表中。

水平分库分表遇到的问题：分布式事务问题，跨节点关联查询，跨节点分页问题，主键不能相同问题，推荐使用中间件mycat。