MySql索引优化

MySql 索引优化

数据结构:

B+树, 通常叫B树. mysql B+树的高度为3.

监测工具：

1.执行计划：

explain sql语句 ;

2.profile 查看执行时间：

set profiling = 1;
执行sql xxx;
show profiles;

sql编写和解析过程

编写过程

select distinct .. from .. join .. on .. where .. group by .. having .. order by .. limit .. 

解析过程

from .. on .. join .. where .. group by .. having .. select distinct .. order by .. limit ..

编写索引的基本原则

1. 联合索引时保证 最佳左前缀, 使用和定义顺序一致

2. 逐步添加索引 ，索引建立在经常查询的字段上。从经常查询的sql中 根据 where , on , group by , order by . 使用的字段有添加。（添加新索引，考虑删除旧索引）

3. 一个表里总索引最多不要超过五个

4. 单个联合索引字段不要超过五个

5. 避免索引失效

   • 索引上使用计算、函数、类型转换。 索引会失效

   • 索引使用 不等于（!= ， <>）. 或 is null , is not null 。 符合索引的右侧和自身全部失效

   • like 以%开头索引失效。

   • 使用 or 索引失效

   • 跨列，右侧索引会失效

   • in 在前面可能会导致索引失效.(in 放到 where 最后。 防止索引失效)

其他优化

1. join注意 小表驱动大表
   join …on… 时小表在左边。保证数据量小的表在外循环 ， 数据量大的表在内循环。

2. exists 和 in 的优化

   ​ 主查询的数据集大 用 in . 子查询的数据集大 用 exists

3. order by 优化

   ​ 使用单路排序可以减少io

   ​ using filesort 两种排序算法： 单路排序，双路排序。

   ​ max_length_for_sort_data 默认1M.

   ​ 数据大于max_length_for_sort_data 系统会自动变为双路排序. 可以根据实际情况调整 max_length_for_sort_data.

   ​
   据大于max_length_for_sort_data 系统会自动变为双路排序. 可以根据实际情况调整 max_length_for_sort_data.

   ​