mysql 索引优化

MYSQL 数据存储页

mysql数据是存放在磁盘页上的，每页16KB 每次拿数据也是一页一页全部拿出来，不会说单条数据拿出来

分为页头 页目录 以及用户数据区域

在插入的时候就按照主键顺序排好序，所以单表最好有自增的主键 避免插入的时候打乱原有的结构，造成影响。

整体B+树结构

下面有双向链表支持范围查找

联合索引

在下面的叶子节点处纪录了主键值
*** 然后这个图其实也解释了为什么联合索引需要遵循最左匹配原则 ， BCD 3个字段的联合索引他是按照B 字段优先排好序，然后在去排C 和 D 如果没有B 字段，索引没有办法去查找走哪边***

索引类型

• 聚簇索引
  聚簇索引又称为主键索引主要是以主键为索引 然后对数据进行查找
  聚簇索引其实就是上面那张b+树的示例，叶子节点处是真实的数据

• 非聚簇索引
  非聚簇索引其实就是非主键索引，在叶子节点处会冗余一份主键值，然后再根据主键值去查询全表数据

执行计划

查看执行过程分析 EXPLAIN SQL 查看是走的索引还是全表扫描

分析不走索引的原因

索引优化

1.索引范围查找的时候 尽量把范围精确，避免回表次数过多,mysql自动优化成全表扫描
2.查询的数据尽量在索引字段里面包含了，避免回表就肯定会走索引
3.看下where条件是否包含隐式转换，如果字段为字符串类型 字段 = 1 这种情况，数据库默认会把字符串转换为数字做比较，就会导致索引失效（会把字段的每个值都转换为数字类型在比较，所以索引失效），同理 对字段做 + -（字段+1 = 1） 或者函数时都会导致索引失效
4.最左匹配原则（上面有介绍），索引是按 从左到右按字段排序的，所以如果不包含左边的字段直接查右边的字段的时候索引也是失效的
5.order by 导致的索引失效 order by 以及group by 也需要遵循 最左匹配原则（需要包含在order by 需要 where条件中）
假设建立索引key a_b_c(a,b,c)
order by 能使用最左前缀原则（以下四种情况索引生效）：

• order by a
• order by a,b
• order by a,b,c
• order by a desc,b desc,c desc

如果where+order by符合最左前缀原则，则索引生效

• where a=1 order by b,c
• where a=1 and b=2 order by c
• where a=1 and b>2 order b,c

以下情况索引失效：

• order by a asc,b desc,c desc // 排序不一致
• where d=1 order by b,c //丢失a索引