MySQL _ 09 _ 索引

最新推荐文章于 2024-11-11 08:41:40 发布

蒹葭苍苍01

最新推荐文章于 2024-11-11 08:41:40 发布

阅读量122

点赞数 2

分类专栏： MySQL 8.0 学习笔记文章标签：数据库 mysql 索引 sql

本文链接：https://blog.csdn.net/jjccblws01/article/details/119463880

版权

MySQL 8.0 学习笔记专栏收录该内容

5 篇文章 1 订阅

订阅专栏

1、索引简介

索引是一个单独的、存储在磁盘上的数据库结构，包含着对数据表里所有记录的引用指针。使用索引可以快速找出在某个或多个列中有一特定值的行，所有MySQL列类型都可以被索引，对相关列使用索引是提高查询操作速度的最佳途径。

例如，数据库中有2万条记录，现在要执行一个查询

SELECT * FROM table where num=10000;

如果没有索引，就必须遍历整个表，直到num等于10000的这一行被找到为止；如果在num列上创建索引，MySQL不需要任何扫描，直接在索引里面找10000，就可以得知这一行的位置。可见，索引的建立可以提高数据库的查询速度。

索引是在存储引擎中实现的，因此，每种存储引擎的索引都不一定完全相同，并且每种存储引擎也不一定支持所有索引类型。根据存储引擎定义每个表的最大索引数和最大索引长度。所有存储引擎支持每个表至少16个索引，总索引长度至少为256字节。大多数存储引擎有更高的限制。MySQL中索引的存储类型有两种，即 BTREE 和 HASH，具体与表的存储引擎相关；MyISAM和InnoDB存储引擎只支持BTREE索引；MEMORY/HEAP存储引擎可以支持HASH和BTREE索引。

索引的优点主要有以下几条：

通过创建唯一索引，可以保证数据库表中每一行数据的唯一性。
可以大大加快数据的查询速度，这也是创建索引的主要原因。
在实现数据的参考完整性方面，可以加速表和表之间的连接。
在使用分组和排序子句进行数据查询时，也可以显著减少查询中分组和排序的时间。

增加索引也有许多不利的方面，主要表现在如下几个方面：

创建索引和维护索引要耗费时间，并且随着数据量的增加所耗费的时间也会增加。
索引需要占磁盘空间，除了数据表占数据空间之外，每一个索引还要占一定的物理空间，如果有大量的索引，索引文件可能比数据文件更快达到最大文件尺寸。
当对表中的数据进行增加、删除和修改的时候，索引也要动态地维护，这样就降低了数据的维护速度。

2、索引的分类

普通索引(Normal)

MySQL中的基本索引类型，允许在定义索引的列中插入重复值和空值。

唯一索引(Unique)

要求索引列的值必须唯一，但允许有空值。如果是组合索引，则列值的组合必须唯一。主键索引是一种特殊的唯一索引，不允许有空值。

主键是一种约束，唯一索引是一种索引，两者在本质上是不同的。
主键创建后一定包含一个唯一性索引，唯一性索引并不一定就是主键。
唯一性索引列允许空值，而主键列不允许为空值。
主键列在创建时，已经默认为非空值 + 唯一索引了。
主键可以被其他表引用为外键，而唯一索引不能。
一个表最多只能创建一个主键，但可以创建多个唯一索引。
主键更适合那些不容易更改的唯一标识，如自动递增列、身份证号等。

单列索引

即一个索引只包含单个列，一个表可以有多个单列索引。

组合索引

在表的多个字段组合上创建的索引，只有在查询条件中使用了这些字段的左边字段时，索引才会被使用。使用组合索引时遵循最左前缀集合。

全文索引(FullText)

在定义索引的列上支持值的全文查找，允许在这些索引列中插入重复值和空值。全文索引可以在CHAR、VARCHAR或者TEXT类型的列上创建。MySQL中只有MyISAM存储引擎支持全文索引。

做全文检索使用的索引，我们有更好的替代品 ElacticSearch，所以实际使用不多，只当了解。

通过数值比较、范围过滤等就可以完成绝大多数我们需要的查询，但是，如果希望通过关键字的匹配来进行查询过滤，那么就需要基于相似度的查询，而不是原来的精确数值比较。全文索引就是为这种场景设计的。

你可能会说，用 like + % 就可以实现模糊匹配了，为什么还要全文索引？like + % 在文本比较少时是合适的，但是对于大量的文本数据检索，是不可想象的。全文索引在大量的数据面前，能比 like + % 快 N 倍，速度不是一个数量级，但是全文索引可能存在精度问题。

全文索引的版本支持

MySQL 5.6 以前的版本，只有 MyISAM 存储引擎支持全文索引；
MySQL 5.6 及以后的版本，MyISAM 和 InnoDB 存储引擎均支持全文索引;

使用全文索引的注意

使用全文索引前，搞清楚版本支持情况；
全文索引比 like + % 快 N 倍，但是可能存在精度问题；
如果需要全文索引的是大量数据，建议先添加数据，再创建索引；
对于中文，可以使用 MySQL 5.7.6 之后的版本，或者第三方插件。

空间索引

对空间数据类型的字段建立的索引，MySQL中的空间数据类型有4种，分别是GEOMETRY、POINT、LINESTRING 和POLYGON。MySQL使用 SPATIAL关键字 进行扩展，使得能够用创建正规索引类似的语法创建空间索引。创建空间索引的列，必须将其声明为NOT NULL，空间索引只能在存储引擎为 MyISAM 的表中创建。

3、创建索引

1、创建表的时候创建索引

Create Table table_name 
[col_name data_type]  
[UNIQUE|FULLTEXT|SPATIAL]  [INDEX|KEY]  [index_name](col_name [length]) 
[ASC|DESC]

UNIQUE、FULLTEXT和SPATIAL为可选参数，分别表示唯一索引、全文索引和空间索引；
INDEX与KEY为同义词，两者作用相同，用来指定创建索引；
col_name 为需要创建索引的字段列，该列必须从数据表中定义的多个列中选择；
index_name 指定索引的名称，为可选参数，如果不指定，MySQL默认col_name为索引值；
length 为可选参数，表示索引的长度，只有字符串类型的字段才能指定索引长度；
ASC或DESC指定升序或者降序的索引值存储。

2、在已有表上创建索引 —— Alter Table

Alter Table `table_name` 
Add [Unique|Fulltext|Spatial] [Index|Key] [`index_name`] (col_name[length], ...) 
[ASC|DESC]

3、在已有表上创建索引 —— Create Index

Create [Unique|Fulltext|Spatial] Index  `index_name` 
on `table_name` (col_name[length], ...)  
[ASC|DESC]

Create Index语句可以在已经存在的表上添加索引。在MySQL中，Create Index被映射到一个Alter Table语句上

4、删除索引

使用Alter Table删除索引

Alter Table `table_name` Drop Index `Index`;

使用Drop Index语句删除索引

Drop Index `index_name` on `table_name`;

注意：添加AUTO_INCREMENT 约束字段的唯一索引不能被删除。

5、`Explain` 关键字

Explain 关键字可以模拟MySQL优化器执行SQL语句，可以很好的分析SQL语句或表结构的性能瓶颈。

使用 Explain 语句查看索引是否正在使用：

Create table book(
	book_id       INT NOT NULL,
	book_name     Varchar(255) NOT NULL,
	authors       Varchar(255) NOT NULL,
	info          Varchar(255) NULL,
	comment       Varchar(255) NULL,
	year_publication     Year NOT NULL,
	INDEX(year_publication)
);

mysql> EXPLAIN Select * From book Where year_publication = 1990
****************************** 1.row ******************************
              id: 1
     select_type: SIMPLE
           table: book
      partitions: NULL
            type: ref
   possible_keys: year_publication
             key: year_publication
         key_len: 1
             ref: const
            rows: 1
        filtered: 100.00
           Extra: Using index condition
           
1 row in set, 1 warning (0.00 sec)

Exolain 语句输出结果的各个行解释如下：

id 行， select查询的序列号，包含一组数字，表示查询中执行select子句或操作表的顺序，id的值越大优先级越高，越先被执行；id 值相同，顺序执行。
select_type 行，指定所使用的SELECT查询类型，这里值为SIMPLE，表示简单的SELECT，不使用UNION或子查询。其他可能的取值有PRIMARY、UNION、SUBQUERY等。
table 行，指定数据库读取的数据表的名字，它们按被读取的先后顺序排列。
partitions 行，匹配的分区
type 行，指定了本数据表与其他数据表之间的关联关系（访问类型），可能的取值有system、const、eq_ref、ref、range、index和All。
possible_keys 行，给出了MySQL在搜索数据记录时可选用的各个索引。
key 行，是MySQL实际选用的索引。
key_len 行，给出索引按字节计算的长度，key_len数值越小，表示越快。
ref 行，显示使用哪个列或常数与key一起从表中选择行。
rows 行，是MySQL在执行这个查询时预计会从这个数据表里读出的数据行的个数。
filtered行，查询的表行占表的百分比
Extra 行，提供了与关联操作有关的信息。