1、索引的声明与使用
1.1. 索引的分类
先介绍下索引的分类，方便后续介绍索引的创建与设计。
按照功能逻辑划分，索引主要有：普通索引、唯一索引、主键索引、全文索引、空间索引(并非所有数据库都有空间索引)
按照物理实现划分，索引主要有：聚簇索引、非聚簇索引。
按照作用字段个数划分，索引可以分为单列索引和联合索引。
🍜 普通索引
对于普通字段，也没有加特殊约束的索引，就是普通索引。比如对Stuedent类里面的name属性创建一个索引，不需要限制非空、唯一等，就是普通索引哟。
🍞 唯一性索引
声明了Unique唯一性约束的字段，会自动添加唯一性索引，并且删除唯一性约束就是通过删除唯一性索引来实现的。
🎂 主键索引
顾名思义，主键的索引。主键除了唯一性约束还有非空约束。一张表只能有一个主键索引（因为主键索引即数据，数据在物理上只能有一种存放排列方式）
🥜单列索引
只加在一列的索引。
🥟 多列(联合、组合)索引
作用在多个字段的索引。比如联合id,name,gender建立联合索引idx_id_name_gender，联合索引在使用时遵循最左前缀原则.
🍫 全文索引
利用分词技术等多种算法计算出文本中关键字出现的频率和重要性，是当前搜索引擎的关键技术，非常适合与大型的数据集，比如长文本。通过关键字FULLTEXT进行设置。Mysql3.23.23开始支持全文索引，Mysql5.6.4之前只有MyIsam存储引擎支持全文索引，Mysql5.6.4以后Innodb存储引擎也开始支持全文索引了。Mysql5.7.6以后内置了亚洲语种解析器，开始支持中文分词(之前可以引入第三方插件实现支持)。但是随着大数据时代的到来，基本上使用Solr，ElasticSearch等实现全文检索，很少使用Mysql内置的存储引擎实现全文检索了(关系型数据库对于大数据的检索力不从心)。
🥥 空间索引
之前我们提到过空间类型的数据(主要用于三维地理空间)，可以使用参数SPATIAL建立空间索引,提高系统获取空间数据的效率。目前只有MyIsam支持空间索引，并且索引的字段不能为空值。
下图总结了不同存储引擎对于不同索引类型的支持情况。可以稍微了解。
1.2 创建索引
可以在创建表时使用CREATE_TABLE来添加索引，也可以使用ALTER_TABLE或者CREATE_INDEX在已经存在的表上添加索引。
1.2.1 创建表时添加索引
🐇 先看看隐式的索引创建。
# 创建数据库 CREATE DATABASE dbtest2; # 使用数据库 USE dbtest2; # 创建数据表.隐式的添加索引(在添加有主键约束、唯一性约束或者外键约束的字段会自动的创建索引) CREATE TABLE dept( dept_id INT PRIMARY KEY AUTO_INCREMENT, dept_name VARCHAR(20) ); CREATE TABLE emp( emp_id INT PRIMARY KEY AUTO_INCREMENT, emp_name VARCHAR(20) UNIQUE, dept_id INT, CONSTRAINT emp_dept_id_fk FOREIGN KEY(dept_id) REFERENCES dept(dept_id) );
再来显示的创建索引。基本语法格式如下：
CREATE TABLE table_name [col_name data_type] [UNIQUE | FULLTEXT | SPATIAL] [INDEX | KEY] [index_name] (col_name [length]) [ASC |DESC]
下面创建一个普通的索引
CREATE TABLE book( book_id INT , book_name VARCHAR(100), authors VARCHAR(100), info VARCHAR(100) , comment VARCHAR(100), year_publication YEAR, INDEX(year_publication) );
查看下有没有创建成功
SHOW CREATE TABLE book\G
也可以使用如下的语句查看。
SHOW INDEX FROM book;
使用EXPLAIN 来分析下查询语句可能使用到的索引。
EXPLAIN SELECT * FROM book WHERE book_name = "mysql"; • 1
显然，book_name 没有建索引，再看看下面的查询语句。
EXPLAIN SELECT * FROM book WHERE year_publication = 1998; • 1
🐢 再显示的创建一个唯一索引。
CREATE TABLE book1( book_id INT , book_name VARCHAR(100), authors VARCHAR(100), info VARCHAR(100) , comment VARCHAR(100), year_publication YEAR, INDEX(year_publication), UNIQUE INDEX uq_comments(comment) );
创建了唯一性约束，会自动添加唯一索引，同样的，创建了唯一索引，也会自动为该字段添加一个唯一约束。不允许添加重复的数据，但允许添加Null值，而且可以添加多个Null值。
🐎 再看看主键索引。就是通过添加主键约束来完成。
CREATE TABLE student ( id INT(10) UNSIGNED AUTO_INCREMENT , student_no VARCHAR(200), student_name VARCHAR(200), PRIMARY KEY(id) );
通过删除主键约束的方式可以删除主键索引。
ALTER TABLE student DROP PRIMARY KEY; • 1
不过上面的语句会报错，因为student的主键声明了AUTO_INCREMENT 必须是主键。
再来。
DROP TABLE student; CREATE TABLE student ( id INT(10), student_no VARCHAR(200), student_name VARCHAR(200), PRIMARY KEY(id) ); ALTER TABLE student DROP PRIMARY KEY;
🐂 创建联合索引。
CREATE TABLE test3( id INT(11) NOT NULL, name CHAR(30) NOT NULL, age INT(11) NOT NULL, info VARCHAR(255), INDEX multi_idx(id,name,age) );
我们发现，同样只创建了一个索引，但是显示的时候有三行。
注意上面三行依次是id,name,age，与我们创建索引时指定的顺序是严格对应的。在查询时会遵守最左索引原则，因此注意把最常用的查询字段放在索引的最左边。
🦋 创建全文索引。注意全文索引只能够在CHAR,VAECHAR,TEXT等类型上创建。了解下就行了
CREATE TABLE test4( id INT NOT NULL, name CHAR(30) NOT NULL, age INT NOT NULL, info VARCHAR(255), FULLTEXT INDEX futxt_idx_info(info) ) ENGINE=MyISAM;
可以限定对于文本信息建立全文索引的范围,比如我们对info的前50个字节建立全文索引。这样就避免了我们页中存放的单条数据过大而存放不了太多的数据
CREATE TABLE test4( id INT NOT NULL, name CHAR(30) NOT NULL, age INT NOT NULL, info VARCHAR(255), FULLTEXT INDEX futxt_idx_info(info(50)) ) ENGINE=MyISAM;
也可以通过名字和正文等来建立联合的全文索引
CREATE TABLE articles ( id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, title VARCHAR (200), body TEXT, FULLTEXT index (title, body) ) ENGINE = INNODB ;
全文索引建立以后在查询时不用再用like来进行模糊匹配了。而是使用Match。他的效率比like高很多倍。
SELECT * FROM articles WHERE MATCH(title,body) AGAINST ('hello'); • 1
📕 注意点:使用全文索引前，搞清楚版本支持情况；
全文索引比 like + % 快 N 倍，但是可能存在精度问题；
如果需要全文索引的是大量数据，建议先添加数据，再创建索引。因为索引需要随着数据变。
🐏 空间索引，了解即可。
CREATE TABLE test5( geo GEOMETRY NOT NULL, SPATIAL INDEX spa_idx_geo(geo) ) ENGINE=MyISAM;
1.2.2 创建表后添加索引
🏃 先看看第一种方法
ALTER TABLE table_name ADD [UNIQUE | FULLTEXT | SPATIAL] [INDEX | KEY][index_name] (col_name[length],...) [ASC | DESC]
CREATE TABLE book2( book_id INT , book_name VARCHAR(100), authors VARCHAR(100), info VARCHAR(100) , comment VARCHAR(100), year_publication YEAR ); ALTER TABLE book2 ADD INDEX idx_cmt(comment);
🏊 第二种方法。
CREATE UNIQUE INDEX uk_bkname_idx ON book2(book_name); • 1
1.3删除索引
再有些场景我们需要删除索引，比如一张数据表建了许多索引，在需要进行大量的增、删、改之前可以先删除索引。但批量操作完成后，再把索引加回来。
其实这与索引的添加方法是对应的，不信您可以回过头去对比着看。
🌹 第一种方式:ALTER TABLE [tablename] DROP INDEX [indexname];
mysql> SHOW INDEX FROM book2; +-------+------------+---------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression | +-------+------------+---------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+ | book2 | 0 | uk_bkname_idx | 1 | book_name | A | 0 | NULL | NULL | YES | BTREE | | | YES | NULL | | book2 | 1 | idx_cmt | 1 | comment | A | 0 | NULL | NULL | YES | BTREE | | | YES | NULL | +-------+------------+---------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+ 2 rows in set (0.00 sec) mysql> ALTER TABLE book2 DROP INDEX idx_cmt; Query OK, 0 rows affected (0.02 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> SHOW INDEX FROM book2; +-------+------------+---------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression | +-------+------------+---------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+ | book2 | 0 | uk_bkname_idx | 1 | book_name | A | 0 | NULL | NULL | YES | BTREE | | | YES | NULL | +-------+------------+---------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+ 1 row in set (0.00 sec)
🌳 法二,不演试了。
DROP INDEX index_name ON table_name; • 1
🌷 提示:删除表中的列时，如果要删除的列为索引的组成部分(建立了联合索引)，则该列也会从索引中删除。如果组成索引的所有列都被删除，则整个索引将被删除。
mysql> SHOW INDEX FROM test3; +-------+------------+-----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression | +-------+------------+-----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+ | test3 | 1 | multi_idx | 1 | id | A | 0 | NULL | NULL | | BTREE | | | YES | NULL | | test3 | 1 | multi_idx | 2 | name | A | 0 | NULL | NULL | | BTREE | | | YES | NULL | | test3 | 1 | multi_idx | 3 | age | A | 0 | NULL | NULL | | BTREE | | | YES | NULL | +-------+------------+-----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+ ALTER TABLE DROP COLUMN name; mysql> SHOW INDEX FROM test3; +-------+------------+-----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression | +-------+------------+-----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+ | test3 | 1 | multi_idx | 1 | id | A | 0 | NULL | NULL | | BTREE | | | YES | NULL | | test3 | 1 | multi_idx | 2 | age | A | 0 | NULL | NULL | | BTREE | | | YES | NULL | +-------+------------+-----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+ 2 rows in set (0.00 sec)
2.Mysql8.0的索引新特性
2.1降序索引
Mysql4开始就支持降序索引的语法，但实际上只是语法糖，数据库仍然创建的是升序索引，在使用时进行反向扫描。这样无疑会降低数据库的效率。Mysql8.0开始真正支持降序索引了(InnoDB存储引擎)。
创建一个demo吧。在8.0中。
mysql> CREATE TABLE ts1(a int,b int,index idx_a_b(a,b desc)); mysql> SHOW CREATE TABLE ts1\G *************************** 1. row *************************** Table: ts1 Create Table: CREATE TABLE `ts1` ( `a` int DEFAULT NULL, `b` int DEFAULT NULL, KEY `idx_a_b` (`a`,`b` DESC) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3 1 row in set (0.00 sec)
在5.7中,发现他们的区别吗？5.7中显示的表结构在b后面没有DESC。
mysql> CREATE TABLE ts1(a int,b int,index idx_a_b(a,b desc)); mysql> SHOW CREATE TABLE ts1\G *************************** 1. row *************************** Table: ts1 Create Table: CREATE TABLE `ts1` ( `a` int DEFAULT NULL, `b` int DEFAULT NULL, KEY `idx_a_b` (`a`,`b`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3 1 row in set (0.00 sec)
下面对两个版本的mysql执行如下语句，插入799条数据。
DELIMITER // CREATE PROCEDURE ts_insert() BEGIN DECLARE i INT DEFAULT 1; WHILE i <800 DO insert into ts1 select rand()*80000,rand()*80000; SET i = i + 1; END WHILE; commit; END // DELIMITE ; #调用 CALL ts_insert();
