MySQL学习笔记-04(索引)

MySQL学习笔记-01(索引)

索引

索引介绍

• 索引是一种用于快速查询和检索数据的数据结构（有序）;

• 使用索引后可以不用读取全表来定位数据，而是先通过索引找到对应数据对应的物理地址然后访问相应的数据，因此能加快数据库的查询速度；

• 用户看不到索引，只能用来加速检索或查询。

索引作用

• 设置了合适的索引之后，数据库利用各种快速定位技术，能够大大加快查询速度；

• 可以降低数据库的IO成本，并且索引还可以降低数据库的排序成本；

• 可以加快表与表之间的连接；

• 通过创建唯一性索引，可以保证数据表中每一行数据的唯一性；

• 在使用分组和排序时，可大大减少分组和排序的时间。

索引优缺点

• 优势：

1. 提高数据检索效率，降低数据库的IO成本；

2. 通过索引列对数据进行排序，降低数据排序的成本，降低CPU的消耗。

• 劣势：

1. 索引列也是要占用空间的；

2. 降低了更新的速度，比如 INSERT、UPDATE、DELETE操作。

索引结构

介绍

• 不同的存储引擎有不同的索引结构，主要有以下几种：

索引结构	描述
B+Tree	最常见的索引类型，大部分引擎都支持B+树索引
Hash	底层数据结构是用哈希表实现，只有精确匹配索引列的查询才有效，不支持范围查询
R-Tree(空间索引)	空间索引是 MyISAM 引擎的一个特殊索引类型，主要用于地理空间数据类型（通常使用较少）
Full-Text(全文索引)	是一种通过建立倒排索引，快速匹配文档的方式，类似于 Lucene, Solr, ES（通常使用较少）

• 不同的存储引擎对索引的支持情况：

索引结构	InnoDB	MyISAM	Memory
B+Tree	支持	支持	支持
Hash	不支持	不支持	支持
R-Tree	不支持	支持	不支持
Full-Text	5.6版本后支持	支持	不支持

B+Tree

• B+Tree：插入9,11,7,1,20,5,3,6,4,10,12,18,13,2,14,17,19,16

• MySQL索引数据结构(对B+Tree进行了优化)

索引的分类介绍

• 索引分类

分类	含义	特点	关键字
主键索引	针对于表中主键创建的索引	默认自动创建，只能有一个	PRIMARY
唯一索引	避免同一个表中某数据列中的值重复	可以有多个	UNIQUE
常规索引	快速定位特定数据，没有唯一性之类的限制	可以有多个	-
全文索引	查找的是文本中的关键词，而不是比较索引中的值	可以有多个	FULLTEXT

• InnoDB存储引擎索引分类：

在InnoDB存储引擎中，根据索引的存储形式，又可以分为以下两种：

分类	含义	特点
聚集索引(Clustered Index)	将数据存储与索引放一块，索引结构的叶子节点保存了行数据	必须有，而且只有一个
二级索引(Secondary Index)	将数据与索引分开存储，索引结构的叶子节点关联的是对应的主键	可以存在多个

• 聚集索引选取规则：

1. 如果存在主键，主键索引就是聚集索引；

2. 如果不存在主键，将使用第一个唯一(UNIQUE)索引作为聚集索引；

3. 如果表没有主键或没有合适的唯一索引，则 InnoDB 会自动生成一个rowid作为隐藏的聚集索引。

• 示例：

id字段为聚集索引，name字段为二级索引，使用select * from user where name = 'Cname’查询name为Cname的数据

1. 先到二级索引中查询name = Arm的数据,查询到name=Cname的id为3;
2. 然后再去聚集索引中查询id=10的数据。

流程图如下：

索引的使用

创建索引

CREATE [ UNIQUE | FULLTEXT ] INDEX index_name ON table_name (index_col_name, ...);


ALTER TABLE table_name ADD [ UNIQUE | FULLTEXT ] INDEX index_name (index_col_name, ...);

• CREATE INDEX：创建索引的关键字

• [ UNIQUE | FULLTEXT ]：可选参数，指定索引类型

1. UNIQUE表示创建唯一索引；
2. FULLTEXT表示创建全文索引，用于全文搜索；
3. 如果不指定，默认为普通索引。

• index_name：指定索引的名称

• table_name：表名

• (index_col_name, …)：指定要创建索引的列名，可以指定一个或多个列作为索引的键，多个列之间用逗号分隔。

查看索引

  SHOW INDEX FROM table_name;

删除索引

  DROP INDEX index_name ON table_name;

索引使用规则

最左前缀法则

• 介绍：最左前缀法则是指在使用联合索引进行查询时，必须从索引的最左列开始，并且不能跳过中间的列。如果跳过了某一列，那么索引将只能部分生效，后续字段的索引将会失效。

• 规则的原因：因为联合索引的存储方式是按照索引的多个列依次排序的。当查询时，数据库系统会根据索引的最左列进行查找，并按照索引的顺序逐渐向右查找，直到找到满足所有条件的数据或者无法再继续匹配。如果我们跳过了某一列进行查询，那么在该列之后的列将无法按照索引的顺序进行查找，导致索引失效。这样就会导致数据库需要扫描更多的数据页来满足查询条件，进而降低查询性能。

• 总结：在使用联合索引进行查询时，应该遵守最左前缀法则，按照索引列的顺序进行查询，这样可以最大程度地利用索引提供的性能优势。如果需要对多个列进行灵活的查询，可以考虑创建更合适的索引或者使用其他查询优化手段来提高性能。

联合索引避免范围查询

• 问题：当使用联合索引进行范围查询（<, >）时，范围查询右侧的列索引将失效。

• 原因：因为范围查询需要按照一定的顺序扫描索引，从而无法完全利用索引的有序性。

• 解决方案：为了规避这个索引失效问题，可以考虑改用>=或者<=来代替范围查询。通过使用>=或者<=操作符，可以将范围查询转化为等值查询或者单值查询，从而使得整个联合索引仍然保持有效。但拆分后的查询条件可能会增加一些逻辑复杂性，需要谨慎设计和测试。

覆盖索引

• 一个索引包含了满足查询结果的数据就叫做覆盖索引，不需要回表等操作。尽量使用覆盖索引，减少 select *。

前缀索引

• 当字段类型为字符串（varchar, text等）时，有时候需要索引很长的字符串，这会让索引变得很大，查询时，浪费大量的磁盘IO，影响查询效率，此时可以只降字符串的一部分前缀，建立索引，这样可以大大节约索引空间，从而提高索引效率。

• 创建前缀索引

CREATE INDEX FROM ON table_name(index_col_name(n));

• 前缀长度

1. 可以根据索引的选择性来决定。索引的选择性：指的是不重复的索引值（基数）和表记录数的比值。选择性是索引筛选能力的一个指标。索引的取值范围是 0—1 ，当选择性越大，索引价值也就越大。

2. 求选择性公式

SELECT count(DISTINCT field_name) / count(*) FROM table_name;

SELECT count(DISTINCT substring(field_name,1,n)) / count(*) FROM table_name;

单列索引与联合索引

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

• 联合索引：即一个索引包含了多个列；

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

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

SQL提示

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

• 用法：

1. USE INDEX：使用特定的索引来执行查询。
2. IGNORE INDEX：忽略特定的索引，而选择其他可用的索引来执行查询。
3. FORCE INDEX：强制使用特定的索引来执行查询，并忽略其他可能更适合的索引。

索引设计规则

• 针对于数据量较大，且查询比较频繁的表建立索引；

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

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

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

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

• 要控制索引的数量，索引并不是多多益善，索引越多，维护索引结构的代价就越大，会影响增删改的效率；

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

索引失效情况

• 在索引列上进行运算操作，索引将失效。

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

• 模糊查询：模糊查询中，如果仅仅是尾部模糊匹配，索引不会是失效；如果是头部模糊匹配，索引失效。

• or连接的条件：OR操作符将多个条件组合在一起时，如果其中一个条件的列没有索引，那么涉及的索引不会被用到。

NULL约束它。当优化器知道每列是否包含NULL值时，它可以更好地确定哪个索引最有效地用于查询。

索引失效情况

• 在索引列上进行运算操作，索引将失效。

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

• 模糊查询：模糊查询中，如果仅仅是尾部模糊匹配，索引不会是失效；如果是头部模糊匹配，索引失效。

• or连接的条件：OR操作符将多个条件组合在一起时，如果其中一个条件的列没有索引，那么涉及的索引不会被用到。

• 数据分布影响：当MySQL评估使用索引比全表扫描更慢时，会选择不使用索引。