sql索引优化

索引介绍

索引是帮助mysql高效获取数据的数据结构，在数据之外，数据库系统还维护着满足特定查找算法的数据结构，(B+树)

这些数据结构以某种方式引用(指向)数据，这样就可以在这些数据结构上实现高级查找算法，这种数据结构就是索引

B树

B-Tree,B树是一种多叉路横查找树，B树就是B-树，这个-是连字符号而不是减号。

假设我们的数据量达到了亿级别，主存存储不下，我们就只能以块的形式从磁盘读取数据，与主存的访问时间相比，磁盘的I/O操作相当耗时，儿B-树的主要目的就是减少磁盘的I/O操作，大多数平衡树的操作(增删改查)都需要O(h)次磁盘访问操作，h为树的高度，但是对于B-树而言，树的高度将不再是logn，而是一个我们可控的高度h 。

在实际应用的时候，B树的阶数都非常大，通常大于100，即使存储大量数据，B树的高度很低

B+树

Mysql默认使用的索引底层数据结构是B+树。

B+树是BTree基础上的一种优化，使其更适合实现外存储索引结构，InnoDB存储引擎就是用B+树实现索引结构

相比于B树，B+树磁盘读写代价更低，查询效率B+树更加稳定，B+树便于扫库和区间查询

Mysql索引语法

创建索引语法

create [uninque] index 索引名 on 表名 (字段名,...)

列如给tb表的name字段建立一个索引

create index idx_emp_name on tb(name);

• 常作为搜索条件的字段，适合做索引

查看索引语法

show index from 表名

索引类型

• 聚集索引（聚簇索引）
• 二级索引（非聚簇索引）

聚集索引：将数据存储与索引放到一块，索引结构的叶子节点保存了行数据

二级索引：将数据与索引分开存储，索引结构的叶子节点关联的是对应的主键

sql检查是否命中索引

• possible_key 当前sql可能会使用到的索引
• key 当前sql实际命中的索引
• key_len 索引占用的大小
• Extra额外的优化建议

执行语句前面加上 expain

explain 语句

执行之后可以看见该语句是否命中索引情况

​

sql语句失效情况

构建场景

给tb_seller创建联合索引，字段顺序:name,status,address

违反最左前缀法则

如果索引了多列，要遵循最左前缀法则，指的是查询从索引的最左前列开始，并且不跳过索引中的列。

列如

exploan select * from tb_seller where status ="1" and address ='北京市'

不能跳过name直接查询后面，必须按顺序查询，不能跳跃查询

范围查询右边的列，不能使用索引

explain select * from tb_seller where name ="小米科技" and statue>'1' and address = ''北京市

当你在where中使用范围查找的时候，索引会发生失效

不要在索引列上进行运算操作，索引将会失效

select * from tb_seller where substring(name,3,2)='科技'

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

explan select * from tb_seller where name = ''科技 and status = '0'

explan select * from tb_seller where name = ''科技 and status = 0

由于status是字符串类型，而查询语句中没有使用单引号

在mysql的查询优化中，会自动的进行类型转换，造成索引失效

以%开头的like模糊查询，索引失效。

如果仅仅是尾部模糊匹配，索引不会失效，但如果是%开头的模糊查询，索引就会失效