MySQL的索引

最新推荐文章于 2024-06-21 11:44:55 发布

小扬同学的笔记

最新推荐文章于 2024-06-21 11:44:55 发布

阅读量336

点赞数

分类专栏： MySQL 文章标签： java 数据库数据结构 mysql 开发语言

本文链接：https://blog.csdn.net/aibai666/article/details/128195152

版权

MySQL 专栏收录该内容

1 篇文章 0 订阅

订阅专栏

概念

使用场景

概念

用于提高查询效率的数据结构，在默认的存储引擎中采取的数据结构为B+tree 创建索引虽会提高查询效率 但是会提高内存空间 是一种空间换时间的优化方式

使用场景

在大量数据以及高频率查询的情况下，可以对该表的字段创建索引，以此达到查询优化的效果

使用规则

extra字段存在的值

extra字段是通过explain关键字查询SQL语句的索引使用情况返回的表的字段

#### NULL: 没有用到额外的附加条件

#### Using filesort：查询语句中包含了oder by语句，索引无法完成排序，数据量小的时候在内存中完成排序，否者在磁盘中完成排序。

#### Using temporary：使用到了临时表。

#### Using Index：使用覆盖索引进行数据返回(Innodb引擎才存在)，不会进行回表查找。

#### Using Where：查询语句中存在where范围查找，会回表查找数据。

#### Using Where Using Index：覆盖索引中存在范围查找，但是也不会进行回表，直接在索引上就能拿到数据。

#### Using Index Condition：跟Using Where Using Index有一些差别，它的查找用到了联合索引，查找的数据有不在索引中的字段，所以会进行回表查找数据。

最左前缀法则

最左前缀法则在联合索引中会出现，当使用联合索引时 必须确保联合索引的最左字段会被使用 否则索引失效
如果跳过了某一列 索引将部分失效

联合索引的创建也将依照最左前缀法则去构建

最左前缀法则只要确保在执行SQL语句的时候条件中包含最左字段即可 这和最左字段在条件中的所在顺序没有关系

假设我创建了一个联合索引 该联合索引的字段从左到右为phone name age;

select phone,name from tb_user where phone='17812345689' and name = '张三';

那么如果返回的列中都包含在索引中的话，那么该语句满足联合索引的最左前缀法则 且不会触发回表查询

注意

当联合索引的最左字段使用了like语句无论是前部模糊还是尾部模糊都有几率触发全表查询

可以通过在select 前面添加explain关键字查看该SQL语句的索引使用情况如果在extra该列中出现了using where

那么就表示该语句中使用了全表扫描

explain select phone,name from tb_user where phone like '178123%' and name = '张三';

索引失效情况

1.联合索引不满足最左前缀法则

2.使用运算符操作索引字段 (这将导致和B+tree树的不匹配从而无法触发索引)

3.索引字段为字符串类型在操作的时候不加引号

4.索引字段使用模糊查询但是采取的是前部模糊查询而非尾部模糊查询前部则索引失效尾部则不然

5.使用or进行条件筛选,or的前面使用了索引字段而后面没有使用则索引失效

6.如果MySQL的评估表示全表扫描的效率高于索引那么就不会使用索引

or的索引失效情况

select * from tb_user where id = 2 or age =23;


//在该语句中id为聚集索引 而age为联合索引 联合索引需要遵守最左前缀法则 而age非最左字段 所以单独使用不具备索引的条件 也就是说在上述的SQL语句中只有id的聚集索引被使用了 所引整条SQL语句的索引调用失效

覆盖索引

所谓覆盖索引就是尽量避免select *的情况

也就是说最终的返回字段不是 * 而是索引中的字段并且返回的字段在索引中全部具有这样就会避免回表查询提高查询效率

假设我创建了一个联合 从左至右为phone name age
select id,phone,name,age from tb_user where id=20;

//这里就通过id这个聚集索引完成了SQL查询 通过explain关键字即可查出该语句的索引执行情况为null 即不触发回表查询

前缀索引

当字段类型为字符串类型的时候，如果直接使用该字段创建索引的话，那么就将占用大量的磁盘空间，会占用大量的内存空间严重影响查询效率。此时就可以使用前缀索引，即只存储一部分前缀作为索引

前缀索引的创建语法

create index indexName on tableNam(columnName(n));

//n表示截取的前缀长度

前缀长度的选择

select count(distinct substring(column,start,end)) / count(*) from tableName;

//column 表示需要设置前缀索引的字段
//start 表示截取的的起始值
//end 表示截取的长度

当得出的值为1或者最接近1则该start和len的截取范围的内容是最佳的前缀索引