【面试题】MySQL数据库优化

1、mysql为什么选择B+树，而不用红黑树、B树或hash

同等条件下，B+树的非叶子节点存放的指针最多，树的高度最低
(1) 红黑树：红黑树的本质是二叉树，每个节点只能存放两个指针
(2) B树：B树的非叶子节点除了指针外还存放了记录信息，但每个节点最多存16k的信息，所以B树的非叶子节点能存放的指针比B+树少
(3) hash：hash索引只支持等值查询，不支持范围查询

2、聚集索引、辅助索引

(1) 聚集索引：一张表有且只有一个，根据“主键|第一个非空索引|虚拟行号”建立的B+树；
(2) 辅助索引：一个索引对应一个，根据除主键外索引建立的B+树

3、创建和使用索引的注意事项

(1) 使用最左前缀法则减少索引数量，每个表索引最多不超过 6个
(2) where中，尽量使用>=，不用>。范围查询会让范围查询条件右侧的条件全都索引失效，但是用≥和≤时，不会导致右侧索引失效
(3) where中，不要在索引字段上进行运算
(4) where中，模糊查询的%要放在右侧，放左侧会使索引失效
(5) where中，or左右两边都走索引时，才会使用索引，有一边走不了索引，则都不走索引
(6)  可以人为干预数据库的索引选择，在from表名后加use/ignore/force index(索引名)
(7) 尽量使用覆盖索引，避免回表查询。覆盖索引就是，select中的字段都能在使用的索引中找到
(8) 在对长varchar和text字段建立索引时，可以使用前缀索引，这样可以节省索引的磁盘io，提高查询效率

4、insert优化

(1) 手动提交事务
(2) 批量插入，insert into values (), (), (),...
(3) 保存成文件，直接使用数据库的load工具
(4) 使用mybatis的批量插入时，要在jdbc连接串后加上**rewriteBatchedStatements=true**

5、order优化

按order条件建索引有以下4种情况
(1) 条件asc, asc  ->  索引asc, asc或desc, desc
(2) 条件desc, desc  ->  索引asc, asc或desc, desc
(3) 条件asc, desc  ->  索引asc, desc
(4) 条件desc, asc  ->  索引desc, asc

6、limit优化

千万级数据表越往后翻越慢，可以使用覆盖索引加子查询进行优化

select t1.*
from table1 t1, 
     (select id from table1 order by id limit 1000000,10) t2 
where t1.id = t2.id;

7、count优化

执行效率：count(1) > count(主键) > count(非空索引字段)

8、update优化

update时数据库引擎会对记录或表加锁，where条件使用索引时，数据库引擎会使用行锁，而不使用索引时会使用表锁，所以where条件一定要使用索引，否则会从行锁升级为表锁，严重影响性能

9、平时怎么做数据库优化

(1) 通过慢sql日志或show profile收集慢sql
(2) 使用explan查看执行计划，看是不是没有建索引，或者是索引失效了
(3) 使用覆盖索引
(4) 优化执行计划的访问类型：

1. NULL：无需访问表或者索引，比如获取一个索引列的最大值或最小值。
2. system/const：当查询最多匹配一行时，常出现于where条件是＝的情况。system是const的一种特殊情况，既表本身只有一行数据的情况。
3. eq_ref：多表关联查询时，根据唯一非空索引进行查询的情况。
4. ref：多表查询时，根据非唯一非空索引进行查询的情况。
5. range：在一个索引上进行范围查找。
6. index：遍历索引树查询，通常发生在查询结果只包含索引字段时。
7. ALL：全表扫描，没有任何索引可以使用时。这是最差的情况，应该避免