MySQL的索引

索引是对数据库表中一列或多列的值进行排序的一种结构，是一个单独的，存储在磁盘上的数据结构，它们包含着对数据表里所有记录的引用指针，使用它可以提高数据库中特定数据的查询速度，索引是在存储引擎中实现的

索引的优点

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

增加索引不利的方面

1. 创建索引和维护索引要耗费时间，并且随着数据量的增加所耗费的时间也会增加
2. 索引需要占用磁盘空间
3. 当对表中的数据进行增加，删除，修改的时候，索引也要动态维护，降低了数据的维护速度

索引的设计原则

1. 索引并非是越多越好，一个表中如果有大量的索引，不仅占用磁盘空间，而且会影响INSERT，DELETE，UPDATE等语句的性能，因为当表中的数据更改的同时，索引也会进行调整和更行
2. 避免对经常更新的表进行过多的索引，并且索引中的列尽可能的少。而对于经常用于查询的字段应该创建索引，但要避免添加不必要的字段
3. 数据量小的表尽量不要使用索引
4. 在条件表达式中经常用到的不同值较多的列上建立索引，在不同值少的列上不要建立索引。比如学生的性比只有男和女，那就没有必要建立索引。如果建立了索引不但不会提高查询效率，反而会严重降低更新速度
5. 当唯一性是某种数据本身的特性时（比如身份证号），指定唯一索引。使用唯一索引需能确保定义的列的数据完整性，以提高查询速度
6. 在频繁进行排序或分组的列上建立索引，如果待排序列有多个，可以在这些列上建立组合索引

索引的分类

1. 普通索引和唯一索引：普通索引是MySQL中的基本索引类型，允许在定义索引的列中插入重复值和空值。唯一索引，索引列的值必须是唯一，但允许有空值。如果是组合索引，则列值的组合必须唯一。主键索引是一种特殊的唯一索引，不允许有空值
2. 单列索引和组合索引：单列索引即一个索引只包含单列，一个表可以有多个单列索引。组合索引是指在表的多个字段上组合上创建的索引，只有在查询条件中使用了这些字段的左边字段时，索引才会被使用。使用组合索引时遵循最左前缀集合
3. 全文索引：类型为FULLTEXT，允许在这些索引列中插入重复值和空值。全文索引可以在CHAR,VARCHAR,或者TEXT类型的列上创建。MySQL中只有MyISAM存储引擎支持全文索引
4. 空间索引：是对空间数据类型的字段建立的索引，MySQL中的空间数据类型有四种，分别是：GEOMETRY，POINT，LINESTRING和POLYGON。MySQL使用SPATIAL关键字进行扩展，使得能够用于创建正规索引类似的语法创建空间索引。创建空间索引的列，必须将其声明为NOT NULL，空间索引只能在存储引擎为MyISAM的表中创建

创建索引

一, 创建表的时候创建索引

CREATE TABLE tb_name[col_name date_type]
 [UNIQUE|FULLTEXT|SPATIAL][INDEX|KEY][index_name](col_name[length])[ASC|DESC]

UNIQUE，FULLTEXT，SPATIAL为可选参数，分别表示唯一索引，全文索引和空间索引
INDEX和KEY为同义词，作用相同，用来指定创建索引
index_name指定索引的名称，可选参数，如果不指定，系统会自己命名
col_name为需要创建索引的字段列，必须从表的字段列中选择
length为可选参数，表示索引的长度，只有字符串类型的字段才能指定索引长度
ASC或DESC指定升序或降序的索引值存储

1. 创建普通索引
   没有唯一性的限制，只是加快对数据的访问速度

创建book表
mysql> CREATE TABLE book(
    -> bookid INT NOT NULL,
    -> bookname VARCHAR(255) NOT NULL,
    -> authors VARCHAR(255) NOT NULL,
    -> info VARCHAR(255),
    -> comment VARCHAR(255),
    -> year_publication YEAR NOT NULL,
    -> INDEX(year_publication)  # 创建的普通索引
    -> );
Query OK, 0 rows affected (0.06 sec)

查看表的结构
mysql> SHOW CREATE TABLE book\G;
*************************** 1. row ***************************
       Table: book
Create Table: CREATE TABLE `book` (
  `bookid` int(11) NOT NULL,
  `bookname` varchar(255) NOT NULL,
  `authors` varchar(255) NOT NULL,
  `info` varchar(255) DEFAULT NULL,
  `comment` varchar(255) DEFAULT NULL,
  `year_publication` year(4) NOT NULL,
  KEY `year_publication` (`year_publication`)  # 索引名year_publication
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
1 row in set (0.01 sec)

ERROR:
No query specified

使用EXPLAIN查看索引是否正在使用
mysql> EXPLAIN SELECT * FROM book WHERE year_publication=1998\G;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: book
         type: ref
possible_keys: year_publication  # MySQL在搜索数据时可选用的各个索引
          key: year_publication  # 实际选用的索引
      key_len: 1  # 索引按字节计算的长度，数值越小，表示越快
          ref: const
         rows: 1  # 在执行这个查询时预计会从这个数据表里读出的数据行的个数
        Extra: Using index condition
1 row in set (0.01 sec)

ERROR:
No query specified

2. 创建唯一索引

 mysql> CREATE TABLE t1(
    -> id INT NOT NULL,
    -> name CHAR(30) NOT NULL,
    -> UNIQUE INDEX UniqIdx(id)  # 创建唯一索引，命名UniqIdx
    -> );
Query OK, 0 rows affected (0.03 sec)

3. 创建单列索引（前面两个都为单列索引，这里就不在演示了）
4. 创建组合索引

在表中的id，name和age字段上建立组合索引
mysql> CREATE TABLE t3(
   -> id INT NOT NULL,
   -> name CHAR(30) NOT NULL,
   -> age INT NOT NULL,
   -> info VARCHAR(255),
   -> INDEX MultiIdx(id,name,info(100))  # 三者的顺序可以随意指定，在查询时需要和你定义的一样
   -> );
Query OK, 0 rows affected (0.02 sec)

组合索引可以起几个索引的作用，但是使用时并不是随便查询哪个字段都可使用索引，而是遵循“最左前缀”：利用索引中最左边的列集来匹配行。例如这里由id，name，age三个字段构成的索引，索引行中按id/name/age的顺序存放，索引可以按下面组成：（id，name，age）、（id，name）、（id，）。如果列不构成索引最左面的前缀，MySQL不能使用局部索引，如（age）或者（name，age）组合则不能使用索引查询
5. 创建全文索引
FULLTEXT全文索引可以用于全文搜索。只有MyISAM存储引擎支持FULLTEXT索引，并且只为CHAR、VARCHAR和TEXT列。索引总是对整个列进行，不支持局部（前缀）索引。

mysql> CREATE TABLE t4(
    -> id INT NOT NULL,
    -> name CHAR(30) NOT NULL,
    -> age INT NOT NULL,
    -> info VARCHAR(255),
    -> FULLTEXT INDEX FullTxtIdx(info)  # 创建全文索引
    -> )ENGINE=MyISAM;
Query OK, 0 rows affected (0.02 sec)

全文索引非常适合于大型数据集，对于小的数据集，它的用处可能较小

二、在已经存在的表上创建索引
查看表中的索引

SHOW INDEXES FROM tb_name;

1. 使用 ALTER TABLE 语句创建索引

 ALTER TABLE tb_name ADD [UNIQUE|FULLTEXT|SPATIAL] [INDEX|KEY] [index_name](col_name[length],...)[ASC|DESC]

在book表中的bookname字段上建立名为BkNameIdx的普通索引
mysql> ALTER TABLE book ADD INDEX BkNameIdx(bookname(30));

在book表中的bookId字段上建立名为UniqidIdx的唯一索引
mysql> ALTER TABLE book ADD UNIQUE INDEX UniqidIdx(bookId);

在book表中的authors和info字段上建立组合索引
mysql> ALTER TABLE book ADD INDEX BkAuAndInfoIdx(authors(20),info(50));

2. 使用 CREATE INDEX 创建索引
   CREATE INDEX 语句可以在已经存在的表上添加索引，MySQL中CREATE INDEX 被映射到一个ALTER TABLE 语句上，基本语法结构为：

CREATE [UNIQUE|FULLTEXT|SPATIAL] INDEX index_name ON tb_name(col_name[length],...)[ASC|DESC]

在book表中的bookname字段上建立名为BkNameIdx的普通索引
mysql> CREATE INDEX BkNameIdx ON book(bookname);

在book表中的bookId字段上建立名为UniqidIdx的唯一索引
mysql> CREATE UNIQUE INDEX UniqidIdx ON book(bookId);

在book表中的authors和info字段上建立组合索引
mysql> CREATE INDEX BkAuAndInfoIdx ON book(authors(20),info(50));

删除索引

1. 使用 ALTER TABLE 删除索引
   语法格式：

ALTER TABLE tb_name DROP INDEX index_name;

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

2. 使用 DROP INDEX 语句删除索引
   语法格式：

DROP INDEX index_name ON tb_name;

删除表中的列时，如果要删除的列为索引的组成部分，则该列也会从索引中删除。如果组成所有的列都被删除，则整个索引将被删除。