MySQL索引

索引概述、结构、分类、语法：

一、概述：

索引是高效获取数据的一种数据结构。

二、索引结构：

B+Tree（常用）:

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-nl1AkEIy-1687229710188)(C:\Users\FASQD\AppData\Roaming\Typora\typora-user-images\image-20230611180037632.png)]

Hash

三、索引分类：（InnoDB）

主键索引：针对于表中主键创建的索引，默认自动创建，只能有一个

唯一索引：避免同一个表中某数据列中的值重复，可以有多个

常规索引：快速定位特定数据，可以有多个

全文索引：全文索引查找的是文本中的关键词，而不是比较索引中的值，可以有多个

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

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

四、索引语法：

create [unique] index xxx on xxx(xxx);

show index from xxxx;

drop index xxxx on xxxx

索引使用：

一、索引失效情况：

1、最左前缀法则：

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

如果跳跃某一列，索引将部分失效（后面的字段索引失效）

例如：

联合索引中第一个字段，profession字段必须存在，并且中间不能跳过某一列。

create index idx_user_pro_age_sta on tb_user(profession,age,status);

eg：通过explain查看缺失profession字段联合查询的执行计划可以知道，下面的语句并未使用索引：

EXPLAIN select * from tb_user where age = 31 and status = '0';

eg：通过explain查看跳跃age列的联合查询执行计划可以知道，下面语句的status并未用到索引，但是profession字段使用了索引：

EXPLAIN select * from tb_user where profession = '软件工程' and status = '0';

2、范围查询不加=索引失效：

联合索引中，出现范围查询（>,<）,范围查询右侧的列索引失效：

例如：如下语句中，age>30使用了范围查询，导致后续status列索引失效。

EXPLAIN SELECT * FROM tb_user WHERE profession = '软件工程' and age>30 and status = '0';

如果想要避免如上情况：将age>30改为age>=30

3、索引列运算操作导致索引失效：

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

例如：如下使用substring对phone字段进行部分匹配（函数运算），将导致索引失效。

EXPLAIN SELECT * FROM tb_user where SUBSTRING(phone,10,2) = '15';

4、字符串不加引号索引失效：

字符串类型字段使用时，不加引号，索引将失效。

例如：如下语句，索引并未生效。phone类型为varchar。

EXPLAIN SELECT * FROM tb_user WHERE phone = 17799990015;

5、模糊查询：

如果仅仅是尾部模糊匹配，索引不会失效，如果是头部模糊匹配，索引失效。

索引生效语句：

EXPLAIN select * from tb_user WHERE profession like '软件%';

索引失效语句：

EXPLAIN select * from tb_user WHERE profession like '%工程';

二、使用原则：

1、or链接的条件：

用or分割开的条件，如果or的条件中的列有索引，而后面的列中没有索引，那么设计的索引都不会被用到。

例如：如下语句中or后面的列age没有索引，导致前面id主键索引失效。

EXPLAIN SELECT * FROM tb_user WHERE id = 10 or age = 23;

为age建立索引之后，or前后索引均生效。

2、数据分布影响：

如果MySQL评估使用索引比全表更慢，则不适用索引。

所要查询出的数据集占比较低=>使用索引

所要查询出的数据集占比较高=>不使用索引，使用全局扫描

3、SQL提示：

SQL提示，是优化数据库的一个重要手段，简单来说，就是在SQL语句中加入一些人为的提示来达到优化操作的目的。

use index：（建议）使用某索引，mysql内部会进行评估抉择是否使用你建议的索引

EXPLAIN SELECT * FROM tb_user USE INDEX(idx_user_pro) WHERE profession = '软件工程';

ignore index：忽略某索引

EXPLAIN SELECT * FROM tb_user IGNORE INDEX(idx_user_pro_age_sta)  WHERE profession = '软件工程';

force index：强制使用某索引

EXPLAIN SELECT * FROM tb_user FORCE INDEX(idx_user_pro) WHERE profession = '软件工程';

4、覆盖索引：

尽量使用覆盖索引（查询使用了索引，并且需要返回的列，在该索引中已经全部能够找到），减少select *的使用。

尽量查询二级索引所包含的字段信息，避免回表查询使用聚集索引。

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-Qv142is2-1687229710189)(C:\Users\FASQD\AppData\Roaming\Typora\typora-user-images\image-20230610204214716.png)]

5、前缀索引（处理长字符串或者大文本）：

当字段类型为字符串（varchar、text等）时，有时候需要索引很长的字符串，这会让索引变得很大，查询时浪费大量磁盘IO，影响查询效率。

此时可以只将字符串的一部分前缀，建立索引，这样可以大大节约索引空间，从而提高索引效率。

语法：

create index idx_xxx on table_name(column(n))

前缀长度：

可以根据索引的选择性来决定，而选择性是指不重复的索引值（基数）和数据表的记录总数的壁纸，索引选择性越高则查询效率越高，唯一索引的选择性是1，这是最好的索引选择性，性能也是最好的。、

1、获取选择性：

select count(distinct substring(email,1,5))/count(*) from tb_user;

字段选取长度不同选择性不同，选择性=1则说明无重复，<1说明存在重复。

2、创建索引：

create index idx_email_5 on tb_user(email(5));

3、查看是否使用该索引：

EXPLAIN SELECT * FROM tb_user WHERE email = 'daqiao666@sina.com';

6、单列索引与联合索引：

单列索引：即一个索引只包含单个列

联合索引：即一个索引包含了多个列==>旨在避免回表查询

在业务场景中，如果存在多个查询条件，考虑针对于查询字段建立索引时，建议建立联合索引，而非单列索引。

多条件联合查询时，MySQL优化器会评估那个字段的索引效率更高，会优先选择该索引完成本次查询。

索引使用原则：

1、针对数据量较大，且查询比较频繁的表建立索引。

2、针对于常作为查询条件（where）、排序(order by)、分组(group by)操作的字段建立索引。

3、尽量选择区分度高的列作为索引，尽量建立唯一索引，区分度越高，使用索引的效率越高。

4、如果是字符串类型的字段，字段的长度较长，可以针对于字段的特点，建立前缀索引。

5、尽量使用联合索引，减少单列索引，查询时候，联合索引很多时候可以覆盖索引，节省存储空间，避免回表，提高查询效率。

6、要控制索引的数量，索引并不是多多益善，索引越多，维护索引结构的代价也就越打，会影响增删改查的效率。

7、如果索引列不能存储null值，请在创建表的时候使用not null来约束它。当优化器知道每列是否包含null值时，它可以更好地确定那个索引最有效地用于查询
字符串类型的字段，字段的长度较长，可以针对于字段的特点，建立前缀索引。

5、尽量使用联合索引，减少单列索引，查询时候，联合索引很多时候可以覆盖索引，节省存储空间，避免回表，提高查询效率。

6、要控制索引的数量，索引并不是多多益善，索引越多，维护索引结构的代价也就越打，会影响增删改查的效率。

7、如果索引列不能存储null值，请在创建表的时候使用not null来约束它。当优化器知道每列是否包含null值时，它可以更好地确定那个索引最有效地用于查询