MySQL学习笔记-01(索引)
索引
索引介绍
-
索引是一种用于快速查询和检索数据的数据结构(有序);
-
使用索引后可以不用读取全表来定位数据,而是先通过索引找到对应数据对应的物理地址然后访问相应的数据,因此能加快数据库的查询速度;
-
用户看不到索引,只能用来加速检索或查询。
索引作用
-
设置了合适的索引之后,数据库利用各种快速定位技术,能够大大加快查询速度;
-
可以降低数据库的IO成本,并且索引还可以降低数据库的排序成本;
-
可以加快表与表之间的连接;
-
通过创建唯一性索引,可以保证数据表中每一行数据的唯一性;
-
在使用分组和排序时,可大大减少分组和排序的时间。
索引优缺点
- 优势:
-
提高数据检索效率,降低数据库的IO成本;
-
通过索引列对数据进行排序,降低数据排序的成本,降低CPU的消耗。
- 劣势:
-
索引列也是要占用空间的;
-
降低了更新的速度,比如 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) | 将数据与索引分开存储,索引结构的叶子节点关联的是对应的主键 | 可以存在多个 |
- 聚集索引选取规则:
-
如果存在主键,主键索引就是聚集索引;
-
如果不存在主键,将使用第一个唯一(UNIQUE)索引作为聚集索引;
-
如果表没有主键或没有合适的唯一索引,则 InnoDB 会自动生成一个rowid作为隐藏的聚集索引。
- 示例:
id字段为聚集索引,name字段为二级索引,使用select * from user where name = 'Cname’查询name为Cname的数据
- 先到二级索引中查询name = Arm的数据,查询到name=Cname的id为3;
- 然后再去聚集索引中查询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 ]:可选参数,指定索引类型
- UNIQUE表示创建唯一索引;
- FULLTEXT表示创建全文索引,用于全文搜索;
- 如果不指定,默认为普通索引。
-
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));
- 前缀长度
-
可以根据索引的选择性来决定。索引的选择性:指的是不重复的索引值(基数)和表记录数的比值。选择性是索引筛选能力的一个指标。索引的取值范围是 0—1 ,当选择性越大,索引价值也就越大。
-
求选择性公式
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语句中加入一些人为的提示来达到优化操作的目的,是优化数据库的一个重要手段。
-
用法:
- USE INDEX:使用特定的索引来执行查询。
- IGNORE INDEX:忽略特定的索引,而选择其他可用的索引来执行查询。
- FORCE INDEX:强制使用特定的索引来执行查询,并忽略其他可能更适合的索引。
索引设计规则
-
针对于数据量较大,且查询比较频繁的表建立索引;
-
针对于常作为查询条件(where)、排序(order by)、分组(group by)操作的字段建立索引;
-
尽量选择区分度高的列作为索引,尽量建立唯一索引,区分度越高,使用索引的效率越高;
-
如果是字符串类型的字段,字段长度较长,可以针对于字段的特点,建立前缀索引;
-
尽量使用联合索引,减少单列索引,查询时,联合索引很多时候可以覆盖索引,节省存储空间,避免回表,提高查询效率;
-
要控制索引的数量,索引并不是多多益善,索引越多,维护索引结构的代价就越大,会影响增删改的效率;
-
如果索引列不能存储NULL值,请在创建表时使用NOT NULL约束它。当优化器知道每列是否包含NULL值时,它可以更好地确定哪个索引最有效地用于查询。
索引失效情况
-
在索引列上进行运算操作,索引将失效。
-
字符串不加引号:字符串类型字段使用时,不加引号,索引将失效。
-
模糊查询:模糊查询中,如果仅仅是尾部模糊匹配,索引不会是失效;如果是头部模糊匹配,索引失效。
-
or连接的条件:OR操作符将多个条件组合在一起时,如果其中一个条件的列没有索引,那么涉及的索引不会被用到。
NULL约束它。当优化器知道每列是否包含NULL值时,它可以更好地确定哪个索引最有效地用于查询。
索引失效情况
-
在索引列上进行运算操作,索引将失效。
-
字符串不加引号:字符串类型字段使用时,不加引号,索引将失效。
-
模糊查询:模糊查询中,如果仅仅是尾部模糊匹配,索引不会是失效;如果是头部模糊匹配,索引失效。
-
or连接的条件:OR操作符将多个条件组合在一起时,如果其中一个条件的列没有索引,那么涉及的索引不会被用到。
-
数据分布影响:当MySQL评估使用索引比全表扫描更慢时,会选择不使用索引。