数据库|MySQL数据库--索引（索引的含义、索引的作用和分类及详细介绍） mysql|索引|数据库

文章目录

一、什么是索引？

1.1 索引概述（了解）

1.1.1 索引的作用
1.1.2 索引的查找过程（了解）

二、索引的分类

2.1 普通索引

2.1.1 普通索引格式
2.1.2 创建普通索引
2.1.3 查看索引

2.2 唯一索引
2.3 主键索引
2.4 组合索引（最左前缀）
2.5 全文索引

三、查看及删除索引

3.1 查看索引
3.2 删除索引

一、什么是索引？索引是一个排序的列表，在这个列表中存储着索引的值和包含这个值的数据所在行的物理地址，可以大大加快查询的速度，使用索引后可以不用扫描全表来定位某行的数据，而是先通过索引表找到该行数据对应的物理地址然后访问相应的数据。索引的作用类似于书的目录，可以根据目录中的页码快速找到所需的内容。
1.1 索引概述（了解）当数据保存在磁盘类存储介质上时，它是作为数据块存放。这些数据块是被当作一个整体来访问的，这样可以保证操作的原子性。硬盘数据块存储结构类似于链表，都包含数据部分，以及一个指向下一个节点（或数据块）的指针，不需要连续存储。
记录集只能在某个关键字段上进行排序，所以如果需要在一个无序字段上进行搜索，就要执行一个线性搜索（Linear Search）的过程，平均需要访问 N/2 的数据块，N 是表示所占据的数据块数目。如果这个字段是一个非主键字段（也就是说，不包含唯一的访问入口），那么需要在 N 个数据块上搜索整个表格空间。
但是对于一个有序字段，可以运用二分查找（BinarySearch），这样只需要访问 log2(N)的数据块。这就是为什么数据表使用索引后性能可以得到本质上提高的原因。
索引是对记录集的多个字段进行排序的方法。在一张表中为一个字段创建一个索引，将创建另外一个数据结构，包含字段数值以及指向相关记录的指针，然后对这个索引结构进行排序，允许在该数据上进行二分法排序。索引需要额外的磁盘空间。
对于 MyISAM 引擎而言，这些索引是被统一保存在一张表中的。如果很多字段都建立了索引，那么会占用大量的磁盘空间，这个文件将很快到达底层文件系统所能够支持的大小限制。
1.1.1 索引的作用
索引除了快没有其他的作用，数据库利用各种各样的快速定位技术，能够大大提高查询效率。特别是当数据量非常大，查询涉及多个表时，使用索引往往能使查询速度加快成千上万倍。
1.1.2 索引的查找过程（了解）
举一个例子，三个表t1、t2、t3，每个表中只有一个字段，但是每一个表中都有1000行记录，这些记录都是1~1000的数字。

执行以下的查找语句 mysql>SELECT c1,c2,c3 FROM t1,t2,t3 WHERE c1=c2 AND c1=c3;

在无索引的情况下处理此查询，必须寻找 3 个表所有的组合，以便得出与 WHERE 子句相配的那些行。而可能的组合数目为 1000×1000×1000（十亿）
如果对每个表进行索引，就能极大地加速查询进程，利用索引的查询处理如下。

从表 t1 中选择第一行，查看此行所包含的数据。
使用表 t2 上的索引，直接定位 t2 中与 t1 的值匹配的行。同理，利用表 t3 上的索引，直接定位 t3 中与 t1 的值匹配的行。
扫描表 t1 的下一行并重复前面的过程，直到遍历 t1 中所有的行。

在这样的情况下，对表 t1 执行了一个完全扫描，但能够在表 t2 和 t3 上进行索引查找直接取出这些表中的行，比未用索引时要快一百万倍。
利用索引，MySQL 加速了 WHERE 子句满足条件行的搜索，而在多表连接查询时、在执行连接时加快了与其他表中的行匹配的速度。
二、索引的分类逻辑的角度来划分，索引分为普通索引、唯一索引、主键索引、组合索引和全文索引。
2.1 普通索引 2.1.1 普通索引格式
普通索引是最基本的索引，它没有任何限制，也是大多数情况下用到的索引。

直接创建索引的方式 mysql>CREATE INDEX index_name ON table_name (column(length)); column 是指定要创建索引的列名 length 是可选项

索引列的长度有一个最大上限 255 个字节（MyISAM 和 InnoDB 表的最大上限为 1000 个字节），如果索引列的长度超过了这个上限，就只能用列的前缀进行索引。另外，BLOB 或 TEXT 类型的列也必须使用前缀索引。
2.1.2 创建普通索引
方法一：

mysql> create table info (id int(4) not null,name varchar(10) not null,address varchar(50) default '未知'); ##创建一个表结构 Query OK, 0 rows affected (0.01 sec)mysql> describe info; +---------+-------------+------+-----+---------+-------+ | Field| Type| Null | Key | Default | Extra | +---------+-------------+------+-----+---------+-------+ | id| int(4)| NO|| NULL|| | name| varchar(10) | NO|| NULL|| | address | varchar(50) | YES|| 未知|| +---------+-------------+------+-----+---------+-------+ 3 rows in set (0.00 sec) mysql> create index id_index on info(id); ##为info表中的id创建索引 Query OK, 0 rows affected (0.00 sec) Records: 0Duplicates: 0Warnings: 0mysql> describe info; +---------+-------------+------+-----+---------+-------+ | Field| Type| Null | Key | Default | Extra | +---------+-------------+------+-----+---------+-------+ | id| int(4)| NO| MUL | NULL|| | name| varchar(10) | NO|| NULL|| | address | varchar(50) | YES|| 未知|| +---------+-------------+------+-----+---------+-------+ 3 rows in set (0.00 sec)

文章图片

方法二：

修改表结构的方式添加索引 mysql>ALTER TABLE table_name ADD INDEX index_name (column(length));

mysql> alter table info add index name_index (name); Query OK, 0 rows affected (0.01 sec) Records: 0Duplicates: 0Warnings: 0mysql> describe info; +---------+-------------+------+-----+---------+-------+ | Field| Type| Null | Key | Default | Extra | +---------+-------------+------+-----+---------+-------+ | id| int(4)| NO| MUL | NULL|| | name| varchar(10) | NO| MUL | NULL|| | address | varchar(50) | YES|| 未知|| +---------+-------------+------+-----+---------+-------+ 3 rows in set (0.00 sec)

文章图片

方法三：
在创建一个表结构时，就创建索引

mysql> create table num (id int(3),index id_index(id)); Query OK, 0 rows affected (0.01 sec)mysql> describe num; +-------+--------+------+-----+---------+-------+ | Field | Type| Null | Key | Default | Extra | +-------+--------+------+-----+---------+-------+ | id| int(3) | YES| MUL | NULL|| +-------+--------+------+-----+---------+-------+ 1 row in set (0.00 sec)

文章图片

2.1.3 查看索引

mysql> show index from info\G; *************************** 1. row *************************** Table: info Non_unique: 1 Key_name: id_index Seq_in_index: 1 Column_name: id Collation: A Cardinality: 0 Sub_part: NULL Packed: NULL Null: Index_type: BTREE Comment: Index_comment: *************************** 2. row *************************** Table: info Non_unique: 1 Key_name: name_index Seq_in_index: 1 Column_name: name Collation: A Cardinality: 0 Sub_part: NULL Packed: NULL Null: Index_type: BTREE Comment: Index_comment: 2 rows in set (0.00 sec)ERROR: No query specified

文章图片

文章图片

2.2 唯一索引唯一索引与普通索引类似，不同的就是：唯一索引的索引列的值必须唯一，但允许有空
值（注意和主键不同）。如果是组合索引，则列值的组合必须唯一。唯一索引创建方法和普
通索引类似。
修改表结构的时候添加唯一索引：

mysql>CREATEUNIQUEINDEXindex_nameON table_name(column(length));

新建一个表 mysql> create table info2 (id int(4) not null,name varchar(10) not null,address varchar(50) default '未知',primary key(id)); mysql> describe info2; +---------+-------------+------+-----+---------+-------+ | Field| Type| Null | Key | Default | Extra | +---------+-------------+------+-----+---------+-------+ | id| int(4)| NO| PRI | NULL|| | name| varchar(10) | NO|| NULL|| | address | varchar(50) | YES|| 未知|| +---------+-------------+------+-----+---------+-------+ 3 rows in set (0.00 sec) mysql> create unique index unique_name on info2(name); Query OK, 0 rows affected (0.01 sec) Records: 0Duplicates: 0Warnings: 0mysql> describe info2; +---------+-------------+------+-----+---------+-------+ | Field| Type| Null | Key | Default | Extra | +---------+-------------+------+-----+---------+-------+ | id| int(4)| NO| PRI | NULL|| | name| varchar(10) | NO| UNI | NULL|| | address | varchar(50) | YES|| 未知|| +---------+-------------+------+-----+---------+-------+ 3 rows in set (0.00 sec)

文章图片

修改表结构的时候添加唯一索引：

格式 mysql>ALTERTABLEtable_nameADD UNIQUEindex_name(column(length));

创建表的时候同时创建唯一索引：

CREATETABLE`table`( ... UNIQUE index Name (title(length)) );

2.3 主键索引主键索引是一种特殊的唯一索引，一个表只能有一个主键，不允许有空值。一般是在建表的时候同时创建主键索引。
【数据库|MySQL数据库--索引（索引的含义、索引的作用和分类及详细介绍）】主键索引也就是primary key，在之前的创建表的过程中已经演示过，有两种方式。

第一种：在字段外创建 mysql> create table info2 (id int(4) not null,name varchar(10) not null,address varchar(50) default '未知',primary key(id)); 第二种：在字段内创建 mysql> create table info2 (id int(4) not null primary key,name varchar(10) not null,address varchar(50) default '未知');

2.4 组合索引（最左前缀）平时用的 SQL 查询语句一般都有比较多的限制条件，所以为了进一步榨取 MySQL 的效率，就要考虑建立组合索引。在组合索引的创建中，有两种场景，即为单列索引和多列索
引。

特点：
遵从最左原则，从左往右依次执行；

2.5 全文索引对于较大的数据集，将资料输入一个没有 FULLTEXT 索引的表中，然后创建索引，其速度比把资料输入现有 FULLTEXT 索引的速度更快。不过切记对于大容量的数据表，生成全文索引是一个非常消耗时间、非常消耗硬盘空间的做法。

mysql> alter table info2 add fulltext index addr_index(address); ##创建全文索引 Query OK, 0 rows affected, 1 warning (0.04 sec) Records: 0Duplicates: 0Warnings: 1mysql> describe info2; +---------+-------------+------+-----+---------+-------+ | Field| Type| Null | Key | Default | Extra | +---------+-------------+------+-----+---------+-------+ | id| int(4)| NO| PRI | NULL|| | name| varchar(10) | NO| UNI | NULL|| | address | varchar(50) | YES| MUL | 未知|| +---------+-------------+------+-----+---------+-------+ 3 rows in set (0.00 sec) mysql> show index from info2\G; *************************** 1. row *************************** Table: info2 Non_unique: 0 Key_name: PRIMARY Seq_in_index: 1 Column_name: id Collation: A Cardinality: 0 Sub_part: NULL Packed: NULL Null: Index_type: BTREE Comment: Index_comment: *************************** 2. row *************************** Table: info2 Non_unique: 0 Key_name: unique_name Seq_in_index: 1 Column_name: name Collation: A Cardinality: 0 Sub_part: NULL Packed: NULL Null: Index_type: BTREE Comment: Index_comment: *************************** 3. row *************************** Table: info2 Non_unique: 1 Key_name: addr_index Seq_in_index: 1 Column_name: address Collation: NULL Cardinality: 0 Sub_part: NULL Packed: NULL Null: YES Index_type: FULLTEXT Comment: Index_comment: 3 rows in set (0.00 sec)ERROR: No query specified

文章图片

三、查看及删除索引 3.1 查看索引

查看索引的两种方法 mysql>show index from tablename; mysql>show keys from tablename;

以show keys from tablename为例，两种方法用法相同

mysql> show keys from info2; +-------+------------+-------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ | Table | Non_unique | Key_name| Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | +-------+------------+-------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ | info2 |0 | PRIMARY|1 | id| A|0 |NULL | NULL|| BTREE||| | info2 |0 | unique_name |1 | name| A|0 |NULL | NULL|| BTREE||| | info2 |1 | addr_index|1 | address| NULL|0 |NULL | NULL| YES| FULLTEXT||| +-------+------------+-------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ 3 rows in set (0.00 sec)

文章图片

可以使用 mysql> show keys from info2\G; 使用显示结果更加直观

mysql> mysql> show keys from info2\G; *************************** 1. row *************************** Table: info2 Non_unique: 0 Key_name: PRIMARY Seq_in_index: 1 Column_name: id Collation: A Cardinality: 0 Sub_part: NULL Packed: NULL Null: Index_type: BTREE Comment: Index_comment: *************************** 2. row *************************** Table: info2 Non_unique: 0 Key_name: unique_name Seq_in_index: 1 Column_name: name Collation: A Cardinality: 0 Sub_part: NULL Packed: NULL Null: Index_type: BTREE Comment: Index_comment: *************************** 3. row *************************** Table: info2 Non_unique: 1 Key_name: addr_index Seq_in_index: 1 Column_name: address Collation: NULL Cardinality: 0 Sub_part: NULL Packed: NULL Null: YES Index_type: FULLTEXT Comment: Index_comment: 3 rows in set (0.00 sec)ERROR: No query specified

文章图片

Table：表的名称。
Non_unique：如果索引不能包括重复词，则为 0；如果可以，则为 1。
Key_name：索引的名称。
Seq_in_index：索引中的列序号，从 1 开始。
Column_name：列名称。
Collation：列以什么方式存储在索引中。在 MySQL 中，有值‘A’（升序）或 NULL（无分类）。
Cardinality：索引中唯一值数目的估计值。通过运行 ANALYZETABLE 或 myisamchk-a可以更新。基数根据被存储为整数的统计数据来计数，所以即使对于小型表，该值也没有必要是精确的。基数越大，当进行联合时，MySQL 使用该索引的机会就越大。
Sub_part：如果列只是被部分地编入索引，则为被编入索引的字符的数目。如果整列被编入索引，则为 NULL。
Packed：指示关键字如何被压缩。如果没有被压缩，则为 NULL。
Null：如果列含有 NULL，则含有 YES。如果没有，则该列含有 NO。
Index_type：用过的索引方法（BTREE, FULLTEXT,HASH, RTREE）。
Comment：备注。

3.2 删除索引索引在创建之后，是会占用一定的磁盘空间的，因此表内如果有不再使用的索引，从数据库性能方面考虑，最好是删除无用索引。

删除索引的两种方式 DROP INDEX 索引名 ON 表名; ALTER TABLE 表名 DROP INDEX 索引名;

mysql> drop index addr_index on info2; ##删除刚刚创建的全文索引 Query OK, 0 rows affected (0.00 sec) Records: 0Duplicates: 0Warnings: 0mysql> show keys from info2\G; *************************** 1. row *************************** Table: info2 Non_unique: 0 Key_name: PRIMARY Seq_in_index: 1 Column_name: id Collation: A Cardinality: 0 Sub_part: NULL Packed: NULL Null: Index_type: BTREE Comment: Index_comment: *************************** 2. row *************************** Table: info2 Non_unique: 0 Key_name: unique_name Seq_in_index: 1 Column_name: name Collation: A Cardinality: 0 Sub_part: NULL Packed: NULL Null: Index_type: BTREE Comment: Index_comment: 2 rows in set (0.00 sec)ERROR: No query specified