sql索引的介绍及使用规则

1.索引概述

1.1索引介绍

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

1.2索引的优劣势

 2.索引结构

2.1 概述

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

 上述是MySQL中所支持的所有的索引结构，不同的存储引擎对于索引结构的支持情况如下图所示。平常所说的索引，如果没有特别指明，都是指B+树结构组织的索引。

在 MySQL 5.5 之后， InnoDB是默认的 MySQL 存储引擎，InnoDB引擎的默认索引是B+tree。 MySQL 索引数据结构对经典的 B+Tree 进行了优化。在原 B+Tree的基础上，增加一个指向相邻叶子节点 的链表指针，就形成了带有顺序指针的B+Tree，如下图所示。这提高区间访问的性能，利于排序。并且InnoDB 中具有自适应 hash 功能， hash 索引是 InnoDB存储引擎根据 B+Tree 索引在指定条件下自动构建的。

3. 索引分类

3.1 索引分类

在MySQL数据库，将索引的具体类型主要分为以下几类：主键索引、唯一索引、常规索引、全文索引。如下图所示。

3.2 聚集索引&二级索引

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

 

聚集索引选取规则 : 1.如果存在主键，主键索引就是聚集索引。 2.如果不存在主键，将使用第一个唯一（ UNIQUE ）索引作为聚集索引。 3。如果表没有主键，或没有合适的唯一索引，则 InnoDB 会自动生成一个 rowid 作为隐藏的聚集索 引。 聚集索引和二级索引的具体结构如下图所示。 聚集索引的叶子节点下挂的是这一行的数据 ， 二级索引的叶子节点下挂的是该字段值对应的主键值。 执 行如下的 SQL语句时，具体的查找过程如下所示。 具体过程如下: 1. 由于是根据 name 字段进行查询，所以先根据 name='Arm' 到 name 字段的二级索引中进行匹配查 找。但是在二级索引中只能查找到 Arm 对应的主键值 10 。 2. 由于查询返回的数据是 * ，所以此时，还需要根据主键值 10 ，到聚集索引中查找 10 对应的记录，最 终找到10 对应的行 row 。 3  。 最终拿到这一行的数据，直接返回即可。 其中 回表查询是指 ，这种先到二级索引中查找数据，找到主键值，然后再到聚集索引中根据主键值，获取数据的方式，就称之为回表查询。

创建索引

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

2.查看索引

SHOW INDEX FROM table_name ;

3. 删除索引

DROP INDEX index_name ON table_name ;

2创建演示

首先创建一个名为tb_user的表并插入一些数据

create table tb_user(id int primary key auto_increment comment '主键',name varchar(50) not null comment '用户名',phone varchar(11) not null comment '手机号',email varchar(100) comment '邮箱',profession varchar(11) comment '专业',age tinyint unsigned comment '年龄',gender char(1) comment '性别 , 1: 男, 2: 女',status char(1) comment '状态',createtime datetime comment '创建时间') comment '系统用户表';INSERT INTO tb_user (name, phone, email, profession, age, gender, status,createtime) VALUES ('吕布', '17799990000', 'lvbu666@163.com', '软件工程', 23, '1','6', '2001-02-02 00:00:00');INSERT INTO tb_user (name, phone, email, profession, age, gender, status,createtime) VALUES ('曹操', '17799990001', 'caocao666@qq.com', '通讯工程', 33,'1', '0', '2001-03-05 00:00:00');INSERT INTO tb_user (name, phone, email, profession, age, gender, status,createtime) VALUES ('赵云', '17799990002', '17799990@139.com', '英语', 34, '1','2', '2002-03-02 00:00:00');INSERT INTO tb_user (name, phone, email, profession, age, gender, status,createtime) VALUES ('孙悟空', '17799990003', '17799990@sina.com', '工程造价', 54,'1', '0', '2001-07-02 00:00:00');

插入如下的数据

如果需求是 ，name字段为姓名字段，该字段的值可能会重复，为该字段创建索引。

则该创建索引的语法为

CREATE INDEX idx_user_name ON tb_user(name);

phone手机号字段的值，是非空，且唯一的，为该字段创建唯一索引。 则该创建索引的语法为

CREATE UNIQUE INDEX idx_user_phone ON tb_user(phone);

为profession、age、status创建联合索引。

CREATE INDEX idx_user_pro_age_sta ON tb_user(profession,age,status);

为email建立合适的索引来提升查询效率

CREATE INDEX idx_email ON tb_user(email);

查看tb_user表的所有的索引数据

show index from tb_user;

删除索引如删除email的索引

DROP INDEX idx_email ON tb_user ;

5.索引法则

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

2.范围查询

联合索引中，出现范围查询 (>,<)，范围查询右侧的列索引失效。所以，在业务允许的情况下，尽可能的使用类似于 >= 或 <= 这类的范围查询，而避免使用 > 或 <

3.索引失效情况

1.索引列运算 不要在索引列上进行运算操作， 索引将失效，如进行函数运算操作。 2. 字符串不加引号 字符串类型字段使用时，不加引号，索引将失效。 3.  模糊查询 如果仅仅是尾部模糊匹配，索引不会失效。如果是头部模糊匹配，索引失效。 4 .or 连接条件 用 or 分割开的条件， 如果 or 前的条件中的列有索引，而后面的列中没有索引，那么涉及的索引都不会 被用到。 5 . 数据分布影响 如果 MySQL 评估使用索引比全表更慢，则不使用索引。但可以通过sql提示来进行改变。 6.SQL提示 SQL 提示，是优化数据库的一个重要手段，简单来说，就是在 SQL 语句中加入一些人为的提示来达到优 化操作的目的。 1. use index ： 建议 MySQL 使用哪一个索引完成此次查询（仅仅是建议， mysql 内部还会再次进 行评估）。使用代码示例如下。

explain select * from tb_user use index(idx_user_pro) where profession = '软件工程';

2. ignore index ： 忽略指定的索引。使用代码示例如下。

explain select * from tb_user ignore index(idx_user_pro) where profession = '软件工程';

3. force index ： 强制使用索引。使用代码示例如下。

explain select * from tb_user force index(idx_user_pro) where profession = '软件工程';

以上是我在学习mysql中的对sql索引的小结，以下有我学习该方面知识的文档希望可以帮到大家。

链接：https://pan.baidu.com/s/1hY6L44fG4ElGQguWklJzLg?pwd=1234 
提取码：1234

来源地址：https://blog.csdn.net/qq_56578523/article/details/128813621