索引在数据库里的作用可大了，它用在索引列上，能大大提高查询的效率。尤其是数据量特别大、查询涉及好几个表的时候，用索引常常能让查询速度快成千上万倍。

MySQL 提供了好多索引类型，每种索引类型都有它特定的用的地方和优点。

1. B-Tree

索引 B-Tree （平衡树）是 MySQL 默认的索引类型。在大多数数据查询的情况里都能用，能在单列或者多列上创建索引。支持精确查找、范围查找还有部分查找。

应用的场景：

精确查询：根据索引列的精确匹配，像 SELECT * FROM table WHERE col = value 。

范围查询：比如说 BETWEEN 、> 、< 、>= 、<= 这些操作，像 SELECT * FROM table WHERE col BETWEEN value1 AND value2 。

排序：根据索引列来排序，像 SELECT * FROM table ORDER BY col 。

前缀匹配：根据字符串列的前缀匹配，像 LIKE 'prefix%' 。

2. Hash 索引

是基于哈希表的数据结构。只能用来精确查找，不支持范围查询。查询效率高，适合精确等值查询。

应用的场景：

精确查找：像 SELECT * FROM table WHERE col = value 。

内存存储引擎：比如说 Memory 存储引擎的表默认用 Hash 索引。

3. Full-Text 索引

是用来全文搜索的。支持全文搜索的操作（像匹配自然语言的文本字符串）。搜索效率比较高，适合大量的文本数据。

应用的场景：

全文搜索：像新闻、博客、文章这些文本内容的搜索。

搜索记录：像评论、描述字段的搜索等等。

4. Spatial 索引

是基于 R-Tree （矩形树）的数据结构。用来给空间数据做索引。只能在 MyISAM 存储引擎的表上用。

应用的场景：

地理空间查询：像地理位置的存储、查询这些。

GIS 应用：对地理信息系统的数据，做各种空间操作。

5. Bitmap 索引

MySQL 不直接支持 Bitmap 索引，不过在某些情况里能通过别的办法实现跟 Bitmap 索引类似的效果。

应用的场景：

低基数列：像性别、状态这些值比较少的数据列。

分析型应用：在业务分析和数据仓库的情况里常用。

6. Unique 索引

保证列或者列的组合里所有值都是唯一的。能在唯一索引列上插 NULL ，不过只能有一次。

应用的场景： 唯一约束：像邮箱、用户名这些需要唯一的列。

数据完整性：保证某些列的值不重复。

7. Primary Key 索引

主键索引是个特别的唯一索引，一个表只能有一个主键。主键列不能有 NULL 值。通常主键列是表里头最常用的索引列。

应用的场景：

主键约束：表的主键列，用来唯一标识每一行数据。

数据检索：根据主键列进行高效查询。

8. Composite 索引（复合索引）

在好几个列上创建的索引。索引的顺序特别重要，查询条件得遵守最左前缀匹配的原则。能有效地支持多列的查询条件。

应用的场景： 多条件查询：像组合查询，比如说 WHERE col1 = value1 AND col2 = value2 。

优化复杂查询：提高复杂查询的性能。

总结：B-Tree 索引在大多数查询的情况里都能用，支持单列、多列索引还有部分匹配。Hash 索引更适合内存存储引擎，效率高，不过只支持精确查找。Full-Text 索引是针对文字搜索做了优化的，用来做复杂的全文检索操作。Spatial 索引用来做地理空间查询，保证高效处理空间数据。Unique 索引保证数据列是唯一的，对数据完整性和业务逻辑实现有帮助。Primary Key 索引保证主键列是唯一的，是最重要的选择。Composite 索引能优化多条件查询，对多列组合查询效果提升很明显。不同的索引在不同的情况里有不同的优点和应用。按照具体的需求、查询的模式还有数据的特点，合理地选择和配置索引，能大大提高 MySQL 的查询性能。

二、MySQL 设置索引的方法1.添加 PRIMARY KEY（主键索引）

可以使用以下方式添加主键索引：

在创建表时指定主键索引：

CREATE TABLE table_name (  column INT(11) PRIMARY KEY, other_columns...);

修改表时添加主键索引：

ALTER TABLE table_name ADD PRIMARY KEY (column);

添加唯一索引的方法如下：

ALTER TABLE table_name ADD UNIQUE (column);

添加普通索引可以使用以下语句：

ALTER TABLE table_name ADD INDEX index_name (column);

添加全文索引的方式为：

ALTER TABLE table_name ADD FULLTEXT (column);

添加多列索引的语法如下：

ALTER TABLE table_name ADD INDEX index_name (column1, column2, column3);

1.使用场景

那些老是被查询的字段，得创建索引。比如说，在一个用户表里头，如果老是根据用户名去查用户信息，那给用户名字段创建索引就能大大提高查询的速度。

不怎么更新的字段，能创建索引。好比一个商品表里头的商品分类字段，一般不会老是更新，给它创建索引，在查特定分类的商品时就能很快找到位置。

对于创建的多列索引，只要查询条件用了最左边的列，索引一般就能用上。比如说，有个订单表有用户 ID 、订单日期和订单金额这些字段，如果创建了一个多列索引（用户 ID 、订单日期、订单金额），那查询条件里有用户 ID 的时候，索引就能起作用。要是查询条件是按照特定用户的订单来查，这个索引就能很快把符合条件的订单记录给筛出来。

2.注意事项

对于用 like 的查询，要是查询是‘%a'就用不上索引，可 like 'a%'就能用上索引。最前面不能用%和_这种变来变去的值。比如说，在一个用户表里头，如果要查以特定字母开头的用户名，用 like 'a%'这种查询方式能利用索引很快找到符合条件的记录。但要是用 like '%a'这种查询方式，数据库就得把整个表都走一遍来找符合条件的记录，用不上索引，查询效率会低好多。

要是条件里有 or ，就算其中有的条件带着索引也用不上。比如说，在一个商品表里头，如果查询条件是商品价格大于 100 或者商品名称是特定的值，这样的查询就算商品名称字段有索引，也用不上。因为 MySQL 在处理 or 条件的时候，只有所有条件都满足用索引的条件才会用索引。这种情况下，数据库可能就会选全表扫描来拿到结果。

要是列的类型是字符串，那在条件里一定得把数据用引号引起来。好比在一个用户表里头，用户名字段是字符串类型，如果查询条件里没给用户名的值加上引号，数据库可能没法正确用索引来查询。正确的查询方式应该是 SELECT * FROM user WHERE username='特定用户名' 。这样能保证数据库能正确认出查询条件，利用索引提高查询效率。

四、索引的管理

1.查看索引

在 MySQL 里，可以用 SHOW INDEX 语句去查看表里头创建的索引。查看索引的语法格式是这样的：SHOW INDEX FROM [ FROM ] 。语法说明是这样哈：指定要查看索引的那个数据表的名字，还能指定这个数据表所在的数据库，能省略。比如说，SHOW INDEX FROM student FROM test; 这个语句就是查看 test 数据库里 student 数据表的索引。

用这个语句能看到索引的名字、是不是唯一索引、字段在索引里的位置，还有定义索引的列字段名这些东西。

示例的 SQL 语句和运行结果像下面这样。

mysql> SHOW INDEX FROM tb_stu_info2\G  *************************** 1. row ***************************    Table: tb_stu_info2    Non_unique: 0Key_name: heightSeq_in_index: 1Column_name: heightCollation: ACardinality: 0Sub_part: NULLPacked: NULLNull: YESIndex_type: BTREEComment:Index_comment:1 row in set (0.03 sec)

其中各主要参数的说明是这样的：

Table ：表示创建索引的那个数据表的名字，这儿是 tb_stu_info2 数据表。

Non_unique ：表示这个索引是不是唯一索引。要不是唯一索引，这列的值就是 1 ；要是唯一索引，这列的值就是 0 。

Key_name ：表示索引的名字。

Seq_in_index ：表示这列在索引里的位置，要是索引是单列的，这列的值就是 1 ；要是索引是组合索引，这列的值就是每列在索引定义里的顺序。

Column_name ：表示定义索引的列字段。

Collation ：表示列是以啥样的顺序存到索引里的。在 MySQL 里，升序显示值是“A”（升序），要是显示是 NULL ，就表示没分类。

Cardinality ：索引里唯一值数量的估计值。基数是按存成整数的统计数据来计数的，所以就算是小表，这值也没必要特别准。基数越大，联合的时候 MySQL 用这个索引的机会就越大。

Sub_part ：表示列里被编进索引的字符的数量。要是列只是部分被编进索引，这列的值就是被编进索引的字符的数目；要是整列都被编进索引，这列的值就是 NULL 。

Packed ：指示关键字是咋被压缩的。要是没被压缩，值就是 NULL 。

Null ：用来显示索引列里是不是包含 NULL 。要是列里有 NULL ，这列的值就是 YES 。要是没有，这列的值就是 NO 。

Index_type ：显示索引用的类型和方法（BTREE 、FULLTEXT 、HASH 、RTREE ）。

Comment ：显示评注。

还能这么查看索引：

查所有表的索引：SELECT a.TABLE_SCHEMA,a.TABLE_NAME,a.index_name,GROUP_CONCAT(column_name ORDER BY seq_in_index) AS Columns FROM information_schema.statistics a GROUP BY a.TABLE_SCHEMA,a.TABLE_NAME,a.index_name 。

查单个表的索引信息：show keys from table_name; ，结果列表里各字段的意思是这样：

Non_unique ：要是索引不能有重复的词，就是 0 。要是能有，就是 1 。

Key_name ：索引的名字。

Seq_in_index ：索引里列的序列号，从 1 开始。

Column_name ：列的名字。

Collation ：列是以啥方式存到索引里的。在 MySQL 里，有值‘A’（升序）或者 NULL （没分类）。

Cardinality ：索引里唯一值数量的估计值。通过运行 ANALYZE TABLE 或者 myisamchk -a 能更新。基数是按存成整数的统计数据来计数的，所以就算是小表，这值也没必要特别准。基数越大，联合的时候 MySQL 用这个索引的机会就越大。

Sub_part ：要是列只是部分被编进索引，就是被编进索引的字符的数目。要是整列都被编进索引，就是 NULL 。

Packed ：指示关键字是咋被压缩的。要是没被压缩，就是 NULL 。

Null ：要是列里有 NULL ，就有 YES 。要是没有，这列就有 NO 。

Index_type ：用过的索引方法（BTREE, FULLTEXT, HASH, RTREE ）。

2.删除索引

删除索引就是把表里头已经有的索引给删掉。不用的索引建议给删了，因为它们会让表的更新速度变慢，影响数据库的性能。像这样的索引，就该给删掉。

用 DROP INDEX 语句：

语法格式：DROP INDEX <索引名> ON <表名> 。语法说明是这样：<索引名> ：要删掉的索引的名字。<表名> ：指定这个索引所在的表的名字。

比如说：删掉 address 表中的索引。

首先查看address表：

| Table   | Create Table                                       |  +---------+-------------------------------------------------------+| address | CREATE TABLE `address` (`id` int(11) NOT NULL AUTO_INCREMENT,`name` varchar(50) DEFAULT NULL,`address` varchar(200) DEFAULT NULL,PRIMARY KEY (`id`),UNIQUE KEY `address` (`id`)) ENGINE=InnoDB DEFAULT CHARSET=utf8 |

输入删除语句：

DROP INDEX address ON address;

查看是否被删除：

| Table   | Create Table                                                 |  +---------+---------------------------------------------------------------+| address | CREATE TABLE `address` (`id` int(11) NOT NULL AUTO_INCREMENT,`name` varchar(50) DEFAULT NULL,`address` varchar(200) DEFAULT NULL,PRIMARY KEY (`id`)) ENGINE=InnoDB DEFAULT CHARSET=utf8 |

根据 ALTER TABLE 语句的语法可知，该语句也可以用于删除索引。具体使用方法是将 ALTER TABLE 语句的语法中部分指定为以下子句中的某一项。

DROP PRIMARY KEY：表示删除表中的主键。一个表只有一个主键，主键也是一个索引。DROP INDEX index_name：表示删除名称为 index_name 的索引。DROP FOREIGN KEY fk_symbol：表示删除外键。

注意：如果删除的列是索引的组成部分，那么在删除该列时，也会将该列从索引中删除；如果组成索引的所有列都被删除，那么整个索引将被删除。

例如：删除score表中的名为id的普通索引。首先查看score表：

| Table | Create Table                                                              |  +-------+-----------------------------------------------------+| score | CREATE TABLE `score` (`id` int(11) NOT NULL AUTO_INCREMENT,`name` varchar(50) NOT NULL,`math` int(5) NOT NULL,`English` int(5) NOT NULL,`Chinese` int(5) NOT NULL,PRIMARY KEY (`id`),KEY `id` (`id`)) ENGINE=InnoDB DEFAULT CHARSET=utf8 |

输入删除语句：

ALTER TABLE score drop INDEX id;

查看是否被删除：

| Table | Create Table                                           |  +-------+---------------------------------------------------------+| score | CREATE TABLE `score` (`id` int(11) NOT NULL AUTO_INCREMENT,`name` varchar(50) NOT NULL,`math` int(5) NOT NULL,`English` int(5) NOT NULL,`Chinese` int(5) NOT NULL,PRIMARY KEY (`id`)) ENGINE=InnoDB DEFAULT CHARSET=utf8

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