MySQL学习(四)之索引篇

小谢%同学

已于 2023-01-20 11:45:26 修改

阅读量2k

点赞数

分类专栏： MySQL学习文章标签： mysql 学习数据库 Powered by 金山文档

于 2023-01-19 15:05:26 首次发布

本文链接：https://blog.csdn.net/weixin_65743593/article/details/128727531

版权

MySQL学习专栏收录该内容

6 篇文章 2 订阅

订阅专栏

介绍：

索引是是帮助MySQL高效获取数据的数据结构(有序)。在数据之外，数据库系统还维护着满足特定查找算法的数据结构，这些数据结构以某种方式引用(指向)数据，这样就可以在这些数据结构上实现高级查找算法，这种数据结构就是索引。

概述

在数据库的搜索语句中，select * from 表名 where 条件;通常情况下，会进行全表扫描，效率极低

优缺点

索引结构

MySQL的索引是在存储引擎层实现的，不同的存储引擎有不同的结构，主要包含以下几种：

我们平常说的索引，如果没有特别指明，都是指B+树结构组织的索引。

几种数据结构

二叉树

若顺序插入

二叉树的缺点：顺序插入时，会形成一个链表，查询性能大大降低，大数据情况下，层级较深，检索速度慢。

AVL树（平衡二叉树）

特点：

本身首先是一棵二叉搜索树。

带有平衡条件：每个结点的左右子树的高度之差的绝对值（平衡因子）最多为1

AVL树，本质上是带了平衡功能的二叉查找树（二叉排序树，二叉搜索树）

缺点

插入数据需要做旋转操作，插入效率低。查询效率高。

节点过深。

红黑树（一种含有红黑结点并能自平衡的二叉树查找树）：

红黑树缺点：因为书的高度随着数据量增加而增加，所以大数据量情况下，层级较深，检索速度较慢。

B-Tree（多路平衡查找树）

特点：每个结点都会存储对应数据。

例如一颗最大度数为5的b-tree（每个结点最多存储4个key，5个指针）。

B+Tree

特点：只有叶子结点会存储数据，叶子结点形成了一个单向链表，且链表数据自小而大连接。

只有叶子结点存储数据的好处，方便扫库，只需要扫描一遍叶子节点即可。B+树更适合再区间查询的情况，所以通常用于数据库索引。

例如最大度数为4的b+tree

MySQL中的索引结构

B+Tree

MySQL索引数据结构对经典的b+Tree进行优化，在原B+Tree的基础上，增加了一个指向相邻叶子结点的链表指针，就形成了带有顺序指针的B+Tree，提高区间访问的性能。

Hash

哈希索引就是采用一定的hash算法，将键值换算成新的hash值，映射到对应的桔位上，然后存储在hash表中。

假如我们想要为表的name创建哈希索引的数据结构，首先算出每一行的哈希值，再拿到name的值，通过hash函数计算出对应的哈希值，然后存储到对应位置，如果两个(或多个)键值，映射到一个相同的槽位上，他们就产生了hash冲突(也称为hash碰撞)，可以通过链表来解决。

特点

Hash索引只能用于对等比较(=，in)，不支持范围查询 (between，>，<，...)

无法利用索引完成排序操作。

查询效率高，通常只需要一次检索就可以了，效率通常要高于B+tree索引

Hash索引在查询等值时非常快。

如果有大量重复键值得情况下，哈希索引的效率会很低，因为存在哈希碰撞问题

存储引擎支持

在MySQL中，支持hash索引的是Memory引，而lnnoDB中具有自适应hash功能，hash索引是存储引警根据B+Tre索引在指定条件下自动构建的。

思考：为什么InnoDB存储引擎选择使用B+Tree索引结构？

B+树的数据都存储在叶子结点中，分支结点均为索引，方便扫库，只需要扫一遍叶子结点即可，而B树因为其分支结点同样存储着数据，我们要找到具体的数据，需要进行一次中序遍历按序来扫，所以B+树更加适合在区间查询的情况，所以通常B+树用于数据库索引。

B+树的磁盘读写代价更低：B+树的内部节点并没有指向关键字具体信息的指针，因此其内部节点相对B(B-)树更小，如果把所有同一内部节点的关键字存放在同一盘块中，那么盘块所能容纳的关键字数量也越多，一次性读入内存的需要查找的关键字也就越多，相对IO读写次数就降低了。

索引分类

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

从存储结构上来划分：BTree索引（B-Tree或B+Tree索引），Hash索引，full-index全文索引，R-Tree索引。这里所描述的是索引存储时保存的形式，

聚集索引选取规则:

如果存在主键，主键索引就是聚集索引。

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

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

例如：

当我们进行 select * from user where name='Arm';

首先二级索引，找到Arm保存的id，然后进行回表操作，进行聚集索引，然后找到对应的行数据。

InnoDB中每张表有且仅有一个聚簇索引(就是主键索引)，InnoDB中的二级索引是非聚簇索引。

InnoDB中的二级索引的叶子结点中存的是索引列的值和主键值，所以在使用二级索引查询的时候，首先通过二级索引查找到主键值，然后再根据主键值到主键索引的叶子结点中查到对应的整行数据。

思考：是否非聚簇索引一定会进行回表查询吗？？？

这肯定是不一定的，加入二级索引对应的叶子结点上存有想要查找的值，则不需要回表查询。

例如对上表进行 select id from user where name='Arm';，在索引的结点查询到id则不会进行回表。

索引语法：

创建语法

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

一个索引可以关联多个字段，多个字段同时建立一个索引，叫做联合索引。

在联合索引中，如果想要命中索引，需要按照建立索引时的字段顺序挨个使用，否则无法命中索引。

思考：为什么需要注意联合索引中的顺序？

MySQL可以使用多个字段同时建立一个索引，叫做联合索引。在联合索引中，如果想要命中索引，需要按照建立索引时的字段顺序挨个使用，否则无法命中索引。

具体原因为:

MySQL使用索引时需要索引有序，假设现在建立了"name，age，phone"的联合索引，那么索引的排序为: 先按照name排序，如果name相同，则按照age排序，如果age的值也相等，则按照phonel进行排序。

当进行查询时，此时索引仅仅按照name严格有序，因此必须首先使用name字段进行等值查询，之后对于匹配到的列而言，其按照age字段严格有序，此时可以使用age字段用做索引查找，以此类推。因此在建立联合索引的时候应该注意索引列的顺序，一般情况下，将查询需求频繁或者字段选择性高的列放在前面。此外可以根据特例的查询或者表结构进行单独的调整。

思考：为什么要建立联合索引？或者说联合索引的好处？

减少建立索引的开销。建一个（a，b，c）的联合索引，相当于建立了（a)，（a，b），（a，b，c）三个索引。

覆盖索引，无需回表。童谣呦复合索引（a，b，c），如果有如下的select a,b,c from table where a=1 and b = 1。那么MySQL可以直接通过遍历索引取得数据，而无需回表，这减少了很多的随机io操作。减少io操作，特别的随机io其实是dba主要的优化策略。所以，在真正的实际应用中，覆盖索引是主要的提升性能的优化手段之一。

缩小筛选范围。有1000W条数据的表，有如下select * from table where a = 1 and b =2 and c = 3,假设假设每个条件可以筛选出10%的数据，如果只有单值索引，那么通过该索引能筛选出1000W*10%=100w 条数据，然后再回表从100w条数据中找到符合b=2 and c= 3的数据，然后再排序，再分页；如果是复合索引，通过索引筛选出1000w *10% *10% *10%=1w，然后再排序、分页。

创建索引

CREATE [ UNIOUE | FULLTEXT] INDEX index name ON table name (index col name,... )

查看索引

SHOW INDEX FROM table name；

删除索引

DROP INDEX index name ON table name

SQL执行频率

MySQL客户端连接成功后，通过 show sesioniglobal] status 命令可以提供服务器状态信息。通过如下指令，可以查看当前数据库的INSERT、UPDATE、DELETE、SELECT的访问频次:

SHOW GLOBAL STATUS LIKE ’Com_______';后面是七个下划线

思考：什么是最左前缀法则？

如果索引了多列(联合索引)，要遵守最左前缀法则。最左前缀法则指的是查询从索引的最左列开始，并且不跳过索引中的列。如果跳跃某一列，索引将部分失效(后面的字段索引失效。

最左前缀原则就是最左优先，在创建多列索引时，要根据业务需求，where子句中使用最频繁的一列放在最左边。mysql会一直向右匹配直到遇到范围查询(>、<、between、like)就停止匹配，比如a = 1 and b = 2 and c > 3 and d = 4 如果建立(a,b,c,d)顺序的索引，d是用不到索引的，如果建立(a,b,d,c)的索引则都可以用到，a,b,d的顺序可以任意调整。

思考：什么情况索引会失效？

范围查询

联合索引中，出现范围查询（>,<)，范围查询右侧的列索引失效。例如select * from tb_user where profrssion='软件工程' and age>30 and status=6;（假设profession，age，status是联合索引），这时候发现status索引失效，那么能不能规避这个情况呢？？？如果业务允许的情况下，我们可以用(>=,<=)代替(>,<)。

索引列操作

不要再索引列上进行运算操作，索引将失效。例如select * from user where phone='11111111111'，

此时pbone的单列索引生效，但是如果我们想要进行select * from user where substring(phone,10,2)=15;

假如有字符串数据，在输入时没有加单引号，可以查询出来，但是索引失效。

模糊匹配

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

OR连接

用OR分隔开的条件，如果OR前的条件中的列有索引，而后面没有索引，那么涉及的索引都没有被用到。也就是

数据分布影响

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

简而言之就是当查询时，MySQL内部会自动分析是否用索引会更快，如果会更快则用索引，反之放弃索引进行全表扫描。

怎么查看MySQL语句有没有用到索引？

explain或者desc命令获取MySQL如何执行

直接在select语句之前加上关键字
explain / desc SELECT 字段列表 FROM 表名 WHERE 条件;

EXPLAIN执行计划各字段意义

id：SQL查询中的序列号。id列数字越大越先执行，如果说数字一样大，那么就从上往下依次执行。

select_type：查询的类型，可以是以下类型：

table：每个查询对应的表名

possible_key：查询中可能用到的索引 * (可以把用不到的删掉，降低优化器的优化时间) * 。

type（重要）：这是最重要的字段之一，显示查询使用了何种类型。从最好到最差的连接类型依次为：NULL，system，const，eq_ref，ref，fulltext，eq_ref，ref，index，range，index，ALL

此时注意，

虽然NULL是效率最高的，但是业务中一般不可能为NULL，

访问系统表是system，

根据主键或者唯一索引是const，

使用非唯一性索引是ref，

range：索引范围查询，常见于使用 =, <>, >, >=, <, <=, IS NULL, <=>, BETWEEN, IN()或者like等运算符的查询中。

用了索引，但是还是对索引进行扫描时index，

all是全表扫描。

possible_keys：查询可能使用到的索引都会在这里列出来

key：查询真正使用到的索引。

key_len：查询用到的索引长度（字节数）

SQL提示

use index（给MySQL提供建议使用某索引，但MySQL不一定接受）
explain select* from tb user use indexidx user pro) where profession = 软件工程;
ignoreindex（给MySQL提供建议忽略某索引，但MySQL不一定接受）
explain select* from tb userignore index(idx user pro) where profession = 软件工程
force index（给MySQL强制使用某索引，但MySQL不一定接受）
explain select * from tb userforce index(idx user pro) where profession = 软件工程

前缀索引

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

语法

create index idx xxxx on table name(column(n)); n表示截取字符串前面的n个字符来构建索引

前缀长度

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

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

索引设计原则

1. 针对于数据量较大，且查询比较频繁的表建立索引。
2. 针对于常作为查询条件 (where)、排序 (order by)、分组 (group by)操作的字段建立索引。
3. 尽量选择区分度高的列作为索引，尽量建立唯一索引，区分度越高，使用索引的效率越高。
4. 如果是字符串类型的字段，字段的长度较长，可以针对于字段的特点，建立前缀索引。
5. 尽量使用联合索引，减少单列索引，查询时，联合索引很多时候可以覆盖索引，节省存储空间，避免回表，提高查询效率。
6. 要控制索引的数量，索引并不是多多益善，索引越多，维护索引结构的代价也就越大，会影响增删改的效率。
7. 如果索引列不能存储NULL值，请在创建表时使用NOT NULL约束它。当优化器知道每列是否包含NULL值时，它可以更好地确定哪个2索引最有效地用于查询。