java面试题MySQL篇

如何定位慢查询？

采用运维工具skywalking，可以检测接口从而判断是哪个SQL的问题
也可以在mysql中开启慢日志查询，自定义值，超过该时间

SQL语句执行很慢如何分析？

explain/desc select * from xxx where xxxx 解释当前SQL语句
会返回一个表来显示当前SQL语句的执行情况，其中：
1. possible_keys列为可能使用的索引，key为当前使用的索引（检查是否命中索引）
2. extra判断是否出现回标
3. type列为这条SQL的连接类型，性能由好到差为：
NULL（不使用表）、system（查询系统中的表）、const（根据主键查询）、eq_ref（主键索引或者唯一索引查询）、range（范围查询）、
index
（索引树查询）、all（全盘扫描）

索引

是帮助MySQL高效获取数据的有序数据结构
MySQL的InnoDB引擎采用的是B+树存储索引
只在叶子结点存储数据，非叶子结点只存储指针
与B树相比 1. 磁盘读写代价更低 2. 查询效率更稳定 3. 便于扫库和区间查询（叶子结点是一个双向链表）

什么是聚簇索引和非聚簇索引（聚集索引和二级索引/非聚集索引）/什么是回表查询

聚簇索引
将数据存储和索引放到了一块，找到了索引也就找到了数据（索引的叶子结点保存了行数据）
一般情况下主键会默认创建聚簇索引，且一张表只允许存在一个聚簇索引。
如果不存在主键的话，会使用第一个唯一（UNIQUE）索引作为聚簇索引
二级索引
将索引和数据分开存储，索引的叶子结点存储的是对应的主键
回表查询
先通过二级索引获取到主键值，在根据主键去聚簇索引中找到整行的数据

什么是覆盖索引？

覆盖索引是指查询使用了索引，并且需要返回的列在索引中都能够找到，非覆盖索引就需要回表查询了

MySQL超大分页处理？

数据量比较大的时候如果使用limit分页查询，在查询时越往后分页查询的效率越低
比如一个表里有十万条数据，我使用select * from db limit 100000,10会很慢
优化思路：通过覆盖索引加子查询的方式进行优化
CREATE INDEX idx_your_columns ON db(column1, column2, column3);
SELECT column1, column2, column3 FROM db ORDER BY id LIMIT 100000, 10;

SELECT * FROM db 
INNER JOIN (SELECT id FROM db ORDER BY id LIMIT 100000, 10) AS t 
USING (id);

索引创建的原则

*1. 数据量较大查询较频繁的表创建索引
*2. 经常使用查询条件where、排序order by、分组group by操作的字段创建索引
3. 尽量选择区分度高的列作为索引
4. 如果某一字段类型是字符串，并且字符串内容比较长，对该字段创建索引会使存储压力变大，可以使用前缀索引（截取前面的一部分内容）
*5. 尽量使用联合索引，减少单列索引，从而节省存储空间避免回表
*6. 控制索引数量，不宜过多
7. 如果索引列不能存储null，那么创建该列的时候用not null来约束它

什么情况下索引会失效？

1. 违反了最左前缀法则。指的是使用联合索引时查询从索引的最左列开始，并且不跳过索引中的列
2. 范围查询右边的列，不能使用索引  where name= xxx and status > 1 and address= xxx 这里address没有用到索引
3. 不要再索引列上进行运算操作，否则索引将失效
4. 字符串不加单引号，可能会造成索引失效（会发生类型转换造成索引失效）
5. 以%开头的like模糊查询可能导致索引失效（%在前边）

你对SQL优化的经验？

表的设计的优化
索引的优化（索引创建原则和索引失效）
SQL语句优化 （避免使用select *、避免索引失效、尽量用union all代替union，union会多一次过滤效率低、避免where子句中对字段进行表达式操作、能用inner join就不要使用left join和right join，必须使用的话尽量以小表为驱动 ）
      union 会自动压缩多个结果集合中的重复结果，而 union all 则将所有的结果全部显示出来，不管是不是重复
主从复制、读写分离
分库分表

事务的特性是什么？

事务是一组操作的集合，它是一个不可分割的工作单位
其特性有ACID:
A 原子性：事务是不可分割的最小操作单元，只能同时成功和同时失败
C 一致性：事务完成时，必须使所有数据保持一致状态
I  隔离性：是数据库提供的隔离机制，保证事务在不受外部并发操作影响的独立环境下运行
D 持久性：事务一旦提交或回滚，对数据库中数据的改变是永久的

并发事务带来哪些问题？怎么解决？MySQL的默认隔离级别是？

并发事务问题：脏读、不可重复读、幻读
                         脏读：一个事物督导另一个事物还没有提交的数据
                         不可重复读：一个事物先后读取同一条记录，但两次读取的数据不同
                         幻读：一个事物按照条件查询数据时，没有对应的数据行，但在插入数据时该数据又已存在，好像出现了“幻影”
解决方案：对事务进行隔离
隔离级别（解决方案）：未提交读（都不能解决一般不使用）、读已提交（只能解决脏读）、Repeatable Read可重复读（通常使用，不能解决幻读，mysql默认隔离级别）
、serializable串行化（一个事物提交后其他事物才可以运行，问题可以全部解决，但是效率比较低）