数据库建立索引规则 SQL索引介绍及使用规则详解

SQL索引介绍及使用规则详解

1. 指数概述 1.1 指数简介

索引是一种数据结构（有序），可帮助 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还具有自适应哈希功能，在指定条件下，由InnoDB存储引擎基于B+Tree索引自动构建哈希索引。

3. 指标分类 3.1 指标分类

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

3.2 聚集索引和二级索引

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

聚集索引选择规则：

1.如果有主键，主键索引就是聚集索引。

2.如果不存在主键，则第一个唯一（UNIQUE）索引将被用作聚集索引。

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

聚簇索引和二级索引的具体结构如下图所示，聚簇索引的叶子节点是本行的数据，二级索引的叶子节点是字段值对应的主键值。

当执行下面的SQL语句时，具体的搜索过程如下。

具体过程如下：

1. 由于是按照name字段进行查询，因此首先在name字段的二级索引中进行匹配查找，但在二级索引中只能找到Arm对应的主键值10。

2、由于查询返回的数据是*，所以此时还需要根据主键值10去聚集索引中查找10对应的记录，最终找到10对应的行。

3.最后得到这一行的数据，直接返回。回表查询是指先在二级索引中查找数据，找到主键值，再根据主键值去聚集索引中获取数据的方法，这就叫回表查询。

索引使用 4.1 索引语法

1. 创建索引

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 ;

4.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');

插入以下数据

1、如果要求name字段是名称字段，则该字段的值可能会重复，针对该字段建立索引。

创建索引的语法是

CREATE INDEX idx_user_name ON tb_user(name);

2、phone字段的值非空且唯一，因此给该字段建立唯一索引，创建索引的语法为：

CREATE UNIQUE INDEX idx_user_phone ON tb_user(phone);

3.建立职业、年龄、地位的联合索引。

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

4.对邮件建立合适的索引，提高查询效率

CREATE INDEX idx_email ON tb_user(email);

5.查看tb_user表所有索引数据

show index from tb_user;

6.删除索引，如删除邮箱索引

DROP INDEX idx_email ON tb_user ;

5. 索引规则

1.最左前缀规则

如果索引有多列（联合索引），必须遵循最左前缀规则。最左前缀规则是指查询从索引最左边的列开始，不跳过索引中的列。如果跳过某一列，索引将部分失效（后续字段索引将失效）。

2. 范围查询

在联合索引中，范围查询（>、= 或 <

3. 索引失败

1.索引列操作

不要对索引列进行任何操作，否则索引将失效，比如执行函数操作。

2. 字符串没有加引号

当使用字符串类型字段时如果不使用引号，索引将无效。

3.模糊查询

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

4. 或连接条件

对于以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 = '软件工程';

总结

关于sql索引的介绍和使用规则的文章就到此结束了，更多sql索引使用规则的相关内容请搜索编程宝库往期文章，希望大家以后多多支持编程宝库！

下一节：MySQL学习事务与并发控制MySQL教程

事务概念 事务可以理解为一组操作数据库建立索引规则，要么所有操作都执行，要么所有操作都不执行。 特点 原子性 事务是一个独立的原子单位，事务中所有操作要么全部执行，要么全部都不执​​行。 关注的是一组操作的执行结果（要么全部执行）…