MySQL原理（四）索引（1）介绍

w_t_y_y

已于 2024-02-02 15:30:58 修改

阅读量509

点赞数

分类专栏： # 关系型数据库MySQL 文章标签： mysql

于 2018-04-27 15:37:02 首次发布

本文链接：https://blog.youkuaiyun.com/w_t_y_y/article/details/80108928

版权

关系型数据库MySQL 专栏收录该内容

34 篇文章

订阅专栏

一、介绍：

1、背景：

索引可以优化CRUD的速度，当然主要是针对查询进行优化。索引通俗来讲就相当于书的目录，当我们根据条件查询的时候，没有索引，便需要全表扫描。

注意：索引并不是越多越好，索引会提高查询的效率，但是会降低插入（更新/删除）操作的效率。因为更新数据的同时，还需要更新索引数据。

2、原理：

MySQL官方对索引定义：是存储引擎用于快速查找记录的一种数据结构。需要额外开辟空间和数据维护工作。

索引是物理数据页存储，在数据文件中（InnoDB，ibd文件），利用数据页(page)存储。
索引可以加快检索速度，但是同时也会降低增删改操作速度，索引维护需要代价。

3、判断是否需要创建索引

（1）较频繁的作为查询条件的字段应该创建索引；

（2）唯一性太差的字段不适合单独创建索引，即使频繁作为查询条件。

如性别字段。

（3）更新非常频繁的字段不适合创建索引。

（4）不会出现在where字句中的字段不应该创建索引。

4、mysql中索引的限制

1、MyISAM存储引擎引键的长度综合不能超过1000字节；

2、BLOB和TEXT类型的列只能创建前缀索引；

3、MySQL目前不支持函数索引；

4、使用！= 或者<>的时候MySQL不能使用索引；

5、过滤字段使用了函数运算的时候如 abs(key), sum(key)的时候MySQL无法使用索引；

6、使用LIKE操作的时候以%开始无法使用索引，所以尽量写出key LIKE 'abc%' 而不要写出 '%abc%' ，后一种方法是不会使用到索引的；

7、使用非等值查询的时候，MySQL无法使用Hash索引；

二、从索引存储结构划分

按索引存储结构划分，mysql中主要有四种类型的索引：B-Tree索引、Hash索引、Full-text索引、R-Tree索引。

三、从应用层次划分

Normal普通索引、Unique唯一索引、主键索引、复合索引、Full Text全文索引。

1、主键索引

是一种特殊的唯一索引，一个表只能有一个主键，不允许有空值。一般是在建表的时候同时创建主键索引：

CREATE TABLE `table` (
    `id` int(11) NOT NULL AUTO_INCREMENT ,
    `title` char(255) NOT NULL ,
    PRIMARY KEY (`id`)
);

2、普通索引

是最基本的索引，它没有任何限制。它有以下几种创建方式：

（1）直接创建索引：

CREATE INDEX index_name ON table(column(length))

（2）修改表结构添加：

ALTER TABLE table_name ADD INDEX index_name ON (column(length))

（3）创建表时候添加：

CREATE TABLE `table` (
    `id` int(11) NOT NULL AUTO_INCREMENT ,
    `title` char(255) CHARACTER NOT NULL ,
    `content` text CHARACTER NULL ,
    `time` int(10) NULL DEFAULT NULL ,
    PRIMARY KEY (`id`),
    INDEX index_name (title(length))
)

（4）删除索引：

DROP INDEX index_name ON table

3、唯一索引：

与普通索引类似，不同的就是：索引列的值必须唯一，但允许有空值。如果是组合索引，则列值的组合必须唯一。它有以下几种创建方式：

（1）创建表时添加：

CREATE TABLE `table` (
    `id` int(11) NOT NULL AUTO_INCREMENT ,
    `title` char(255) CHARACTER NOT NULL ,
    `content` text CHARACTER NULL ,
    `time` int(10) NULL DEFAULT NULL ,
    UNIQUE indexName (title(length))
);

（2）直接创建：

CREATE UNIQUE INDEX indexName ON table(column(length))

（3）修改表结构添加：

ALTER TABLE table_name ADD UNIQUE indexName ON (column(length))

4、聚合索引（多列索引）：

4.1、介绍

指多个字段上创建的索引，有组合唯一索引，也有组合普通索引。和单列索引的区别仅在于索引是多个字段组成的。使用组合索引时需遵循最左前缀原则，否则不会命中索引和单列索引的区别仅在于索引是多个字段组成的。创建方法和其他索引类似：

CREATE INDEX <索引的名字> ON tablename (字段名1，字段名2...);
ALTER TABLE tablename ADD INDEX [索引的名字] (字段名1，字段名2...);
CREATE TABLE tablename ( [...], INDEX [索引的名字] (字段名1，字段名2...) );

4.2、规则

使用组合索引时需遵循最左前缀原则，否则不会命中索引。

5、全文索引Full Text

5.1、介绍

主要用来查找文本中的关键字，而不是直接与索引中的值相比较。fulltext索引跟其它索引大不相同，它更像是一个搜索引擎，而不是简单的where语句的参数匹配。fulltext索引配合match against操作使用，而不是一般的where语句加like。它可以在create table，alter table ，create index使用，不过目前只有char、varchar，text 列上可以创建全文索引。值得一提的是，在数据量较大时候，现将数据放入一个没有全局索引的表中，然后再用CREATE index创建fulltext索引，要比先为一张表建立fulltext然后再将数据写入的速度快很多。目前只有MyISAM引擎支持。全文索引并不是和MyISAM一起诞生的，它的出现是为了解决WHERE name LIKE “%word%"这类针对文本的模糊查询效率较低的问题。查询操作在数据量比较少时，可以使用like模糊查询，但是对于大量的文本数据检索，效率很低。如果使用全文索引，查询速度会比like快很多倍。在MySQL 5.6 以前的版本，只有MyISAM存储引擎支持全文索引，从MySQL 5.6开始MyISAM和InnoDB存储引擎均支持。

5.2、创建全文索引的方法

CREATE FULLTEXT INDEX <索引的名字> ON tablename (字段名);
ALTER TABLE tablename ADD FULLTEXT [索引的名字] (字段名);
CREATE TABLE tablename ( [...], FULLTEXT KEY [索引的名字] (字段名) ;

5.3、使用方法

和常用的like模糊查询不同，全文索引有自己的语法格式，使用 match 和 against 关键字，如

select * from t_user where match(name) against('aaa');

5.4、注意事项

（1）全文索引必须在字符串、文本字段上建立。

（2）全文索引字段值必须在最小字符和最大字符之间的才会有效。（innodb：3-84；myisam：4-84）

（3）全文索引字段值要进行切词处理，按syntax字符进行切割，例如b+aaa，切分成b和aaa

（4）全文索引匹配查询，默认使用的是等值匹配，例如a匹配a，不会匹配ab,ac。如果想匹配可以在布尔模式下搜索a*

select * from user where match(name) against('a*' in boolean mode);

四、从索引键值类型划分

主键索引、辅助索引（二级索引）

五、从数据存储和索引键值逻辑关系划分

在InnoDB存储引擎中，根据索引的存储形式，又可以分为以下两种：，聚集索引（聚簇索引）、非聚集索引（非聚簇索引，也叫二级索引）。

聚集索引的叶子节点下挂的是这一行的数据；二级索引的叶子节点下挂的是该字段值对应的主键值。

1、聚集索引选取规则:

（1）如果存在主键，主键索引就是聚集索引。如果不存在主键，将使用第一个唯一（UNIQUE）索引作为聚集索引。

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

2、回表查询

2.1、介绍

MySQL的回表指的是在查询过程中需要根据索引获取数据时，如果所需的字段不在索引列上，就会进行额外的操作来从主表（也称为数据表）中获取相应的数据。

当我们使用SELECT语句查询数据库时，通常会创建一个或多个索引以提高查询性能。但有些情况下，由于索引只包含部分字段而无法完全满足查询条件，因此还需要回到原始的数据表中去获取其他字段的值。这种情况被称为“回表”。

2.2、原理：

先到二级索引中查找数据，找到主键值，然后再到聚集索引中根据主键值，获取数据的方式，就称之为回表查询。

2.3、发生场景

（1）当查询结果集中包含了非索引列的字段时；

（2）当查询条件中使用了函数、运算符等导致无法利用索引进行过滤时。

例：

-- 创建测试表
CREATE TABLE test_table (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(50),
    age INT,
    address VARCHAR(100)
);

-- 添加索引
ALTER TABLE test_table ADD INDEX idx_name (name);

-- 查询并返回id、age字段
EXPLAIN SELECT id, age FROM test_table WHERE name = 'John';

idx_name索引只包含了name字段，没有包含id和age字段。因此，MySQL将使用idx_name索引来定位到第一个匹配的记录，然后再次回到原始的数据表中获取id和age字段的值。