1、索引的作用
（1）如果索引为唯一索引，可以保证数据库中每一行数据的唯一性
（2）索引如果创建的合适，会大幅度提高数据库的查询性能，这也是索引最大的作用
（3）索引能够使得在查询过程中，使用到数据库的查询优化器，极大提高系统的性能
2、索引的分类
（1）按照数据结构和使用的算法划分：
B+Tree索引:
内部实现采用了B+Tree数据结构，数据全部存在叶子节点上。本质上是一棵平衡排序树，由二叉树进化而来，各个叶结点由指针相连，按照从左到右的顺序读取叶子结点上的数据，会得到一个有序的数列。
—————————————
Hash索引:
内部实现采用了Hash算法，数据的保存方式为一对一，一个键对应一条唯一的记录，类似于Redis或者Memcached中的K-V存储结构。
—————————————
R-Tree索引:
内部实现采用了R-Tree数据结构，R-Tree是一种空间索引的数据结构，它是B树向多维空间发展的另外一种形式，在地理位置测绘领域有所应用，其他场景几乎没有应用，了解即可。
（2）按照类型划分：
普通索引:
普通索引是一种最基本的索引，只是为了提高数据的查询效率，也是开发中使用比较多的一种索引，允许索引列重复。
—————————————
主键索引:
用来唯一标识数据库中的一条记录，常用于保证数据库中记录的参照完整性，既不可为空，也不能重复。
—————————————
唯一索引:
用来唯一标识数据库中的一条记录，但是与主键索引稍有不同，唯一索引允许索引列的值为空，但是不允许索引列的值发生重复。
—————————————
联合索引/组合索引:
指在数据库表中的某几个字段上同时建立的索引，即：这个索引会关联不止一个列。使用的时候需要特别注意，这种索引遵循最左前缀匹配规则，在下面的索引使用中会详细介绍。
—————————————
全文索引:
用来完成某一段文字中的关键字查找，可以简单理解为like的加强版，不过使用方法和like不同，全文索引比较像一个搜索引擎。它目前支持的数据类型有：char，varchar和text类型。
3、索引的创建和查看
（1）索引的创建
语法：
方法一：使用CREATE INDEX方法
CREATE INDEX index_name ON table_name();
方法二：使用修改表结构的方法
ALTER TABLE table_name ADD INDEX index_name ON table_name();
方法三：在创建表的时候指定
CREATE TABLE table_name ( field1 INT NOT NULL AUTO_INCREMENT, field2 INT , field3 INT , PRIMARY KEY(field_name), UNIQUE index_name(field(len)), INDEX index_name(field(len)) );
示例：
示例1：创建一张t_user测试表，字段包含：[id（主键），user_no（用户编号），login_name（登录名称），login_pass（登录密码），phone（手机号）]，要求：id作为主键，user_no列上建立唯一索引，login_name和login_pass两个列上建立联合索引，phone列上建立普通索引：
方法一：创建表的时候指定
CREATE TABLE t_user( id INT NOT NULL AUTO_INCREMENT, user_no VARCHAR(30) NOT NULL, login_name VARCHAR(50) NOT NULL, login_pass VARCHAR(50) NOT NULL, phone VARCHAR(15) NOT NULL, PRIMARY KEY(id), #主键索引 UNIQUE user_no_ind(user_no), #唯一索引 INDEX name_pass_ind(login_name,login_pass), #联合索引 INDEX phone_ind(phone) #普通索引 )ENGINE = InnoDB DEFAULT CHARSET = UTF8;
方法二：使用CREATE INDEX创建索引，此种方式不能创建主键索引
#创建表的时候先不指定索引： CREATE TABLE t_user( id INT NOT NULL AUTO_INCREMENT, user_no VARCHAR(30) NOT NULL, login_name VARCHAR(50) NOT NULL, login_pass VARCHAR(50) NOT NULL, phone VARCHAR(15) NOT NULL, PRIMARY KEY (id) ) ENGINE = InnoDB DEFAULT CHARSET = UTF8;
#使用CREATE INDEX命令创建表上的索引
CREATE UNIQUE INDEX user_no_ind ON t_user(user_no);
CREATE INDEX name_pass_ind ON t_user(login_name,login_pass);
CREATE INDEX phone_ind ON t_user(phone);
方法三：使用ALTER TABLE修改表结构的方式创建索引
#创建表结构 CREATE TABLE t_user( id INT NOT NULL, user_no VARCHAR(30) NOT NULL, login_name VARCHAR(50) NOT NULL, login_pass VARCHAR(50) NOT NULL, phone VARCHAR(15) NOT NULL ) ENGINE = InnoDB DEFAULT CHARSET = UTF8;
#使用ALTER TABLE命令创建索引
ALTER TABLE t_user ADD PRIMARY KEY(id);
ALTER TABLE t_user ADD UNIQUE INDEX user_no_ind(user_no);
ALTER TABLE t_user ADD INDEX name_pass_ind(login_name,login_pass);
ALTER TABLE t_user ADD INDEX phone_ind(phone);
示例2：创建一张帖子内容表，并在帖子内容列创建全文索引
方法一：创建表结构的时候指定索引
CREATE TABLE t_note( id BIGINT NOT NULL AUTO_INCREMENT, note_content TEXT NOT NULL, create_time DATETIME, PRIMARY KEY(id), FULLTEXT(note_content) #添加全文索引 ) ENGINE = InnoDB DEFAULT CHARSET = UTF8;
方法二：使用CREATE INDEX方式创建全文索引
CREATE TABLE t_note( id BIGINT NOT NULL AUTO_INCREMENT, note_content TEXT NOT NULL, create_time DATETIME, PRIMARY KEY(id) ) ENGINE = InnoDB DEFAULT CHARSET = UTF8;
#添加全文索引
CREATE FULLTEXT INDEX note_ind ON t_note(note_content);
方法三：使用ALTER TABLE修改表结构的方式创建全文索引
CREATE TABLE t_note( id BIGINT NOT NULL AUTO_INCREMENT, note_content TEXT NOT NULL, create_time DATETIME, PRIMARY KEY(id) ) ENGINE = InnoDB DEFAULT CHARSET = UTF8;
#添加全文索引
ALTER TABLE t_note ADD FULLTEXT note_ind(note_content);
（2）索引信息的查看
语法：
SHOW INDEX FROM table_name ;
SHOW INDEXES FROM table_name ;
注意：SHOW后面可以为INDEX或者INDEXES，可以使用WHERE条件根据索引名称查看索引信息
示例：查看t_user表上所创建的索引
SHOW INDEXES FROM t_user \G
*************************** 1. row ***************************
Table: t_user
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:
…其他行略…
结果字段解释：
Table：索引所在的表名称
Non_unique：是否为非唯一的索引，对唯一索引和主键索引，此值为0，因为主键和唯一键必须唯一
Key_name：索引的名称
Seq_in_index：索引中该列的位置，在
Column_name：索引所在列的列名称
Collation：列使用哪种方式存储在索引中，B+数索引总是A，表示排过序的。对于其他的如Hash索引，此处可能为NULL，因为Hash索引并未排序
Cardinality：索引中非胃一直的数目的估计值，通常用于优化器去判断是否查询时使本索引
Sub_part：是否只是用了列的一部分作为索引。比如：在某个非常长的字段上的前多少个字符上创建索引的情况
Packed：关键字如何被压缩，Null表示未被压缩
Null：索引的列中是否含有Null值，主键索引，此处为空，表示不含有Null值
Index_type：索引类型，InnoDB存储引擎，此处为B+树
Comment：索引列的注释
Index_comment：索引的注释
注意：上述字段中，Cardinality字段相对来说比较重要，可以通过该字段来判断当前的索引是否最优，通常如果索引的利用率比较高的话，这个值会比较接近于表中的记录数，即：和表中的记录数接近于1:1，但是这个值并不是实时维护，索引当相差比较大的时候，可以使用”ANALYZE TABLE table_name”命令去更新下这个值，有利于优化器对索引使用的判断。
4、索引的修改和删除
（1）索引的删除
语法：
DROP INDEX index_name ON table_name;
示例：
示例1：删除t_user表中phone列上的phone_ind索引
DROP INDEX phone_ind ON t_user;
示例2：删除t_user表中的id列上的主键索引
ALTER TABLE t_user DROP PRIMARY KEY;
（2）索引的修改
索引的修改过程其实是先删除索引，在重新创建索引，可以按照上述的删除索引和创建索引步骤完成。
5、索引使用注意事项
（1）使用场景
a.业务场景中，读多写少的场景
b.SQL查询场景中，常用于WHERE语句之后的过滤条件；区分度大于80%；WHERE语句之后的过滤字段在过滤时不参与运算；
（2）以下的情况，对于有索引的列，查询时也不会使用索引
a.当优化器判断使用索引和不适用索引差别不大时，将不会使用索引，比如：性别列创建的索引
b.查询条件中发生计算，优化器将不使用索引，比如：WHERE SUBSTR(name,5) = ‘BING’
c.查询条件中包含了隐士类型转换，比如：WHERE phone = 13520277199
d.反向查询不会使用索引，比如：!=,<>,NOT IN,NOT LIKE等，比如：WHERE name != ‘BING’;
e.LIKE的左模糊匹配，将不会使用索引，比如：WHERE name LIKE ‘%BING’，右匹配查询会走索引;
f.联合索引中，不满足左前缀规则，则MySQL不会使用索引。比如：对于name,pass,user_no列的联合索引，下述情况将不会使用索引：
WHERE pass = ‘value’
WHERE user_no = ‘value’
WHERE pass = ‘123’ AND user_no = ‘123’
而如下的情况将会使用到索引：
WHERE name = ‘bing’
WHERE name = ‘bing’ AND pass = ‘123’;
WHERE name = ‘bing’ AND user_no = ‘021250’;
WHERE pass = ‘123’ AND name = ‘bing’;
WHERE name = ‘bing’ AND pass = ‘123’ AND user_no = ‘021250’;
g.某个带索引的列和不带索引的列中间使用OR连接，则带索引的列也不会使用索引，如：user_no列带有索引，phone_未带索引，则：
不会使用索引：WHERE user_no = ‘123’ OR phone = ‘13520277898’
会使用索引：WHERE user_no = ‘123’ AND phone = ‘15265648758’
h.如果在联合索引中有范围查询，如果字段之间使用OR连接，则整个查询条件不会使用索引，如果字段之间使用AND连接，则从第一个范围查询开始之后的条件都不会使用索引
比如：name,score,usre_no列上的联合索引，则：
不会使用索引：WHERE name = ‘bing’ OR score = 123 OR user_no = ‘02311’;
不会使用索引：WHERE name = ‘bing’ AND score = 123 OR user_no = ‘01231’;
会使用索引：WHERE name = ‘bing’ AND score = 123 AND user_no = ‘021321’;
name列会使用索引，name之后的列不会使用索引：
WHERE name = ‘bing’ AND score > 120 AND user_no = ‘021321’;
6、执行计划查看
语法：
EXPLAIN
示例：
示例1：查看t_user表上面的id列查询执行计划：
EXPLAIN SELECT * FROM t_user WHERE id = 1 \G
********** 1. row ******
id: 1
select_type: SIMPLE
table: t_user
type: const
possible_keys: PRIMARY
key: PRIMARY
key_len: 4
ref: const
rows: 1
Extra: NULL
1 row in set (0.00 sec)
输出字段说明：
id:查询编号，如果有多个大小相同的编号，则执行顺序为从上到下，如果大小相同，则编号大的先执行
select_type:表示查询类型，有以下几种值：
a.SIMPLE:表示该查询为一个简单查询，如上述的根据id查询就是一个简单查询
b.SUBQUERY:表示该查询是WHERE字句中的子查询
c.DERIVED:表示该查询是FROM字句中的子查询
d.PRIMARY:表示一个负责嵌套查询最外层的查询
e.UNION:表示UNION查询的第二个SELECT子查询
f.UNION RESULT:表示该结果是从UNION表中查询出的结果
table:表示查询时所关联的表
type:表示关联类型或者访问类型，常见的几种值如下：
ALL:表示未使用索引，扫描全表
index:表示扫描所有的索引，通过扫描索引树去定位所有待查询数据
range:表示按照索引的范围扫描，即：从某索引的某个位置开始，到索引的另外一个位置结束，找出这个范围内每个索引对应的数据，范围查询会出现range
ref:非唯一索引扫描，MySQL将返回匹配这个索引的所有行
eq_ref:唯一索引扫描，即：通过该索引只能定位到表中的一条数据，主键索引和唯一索引属于这种类型
possible_keys:查询优化器可能使用到的索引，但是不一定使用
key:查询优化器真正使用的索引
key_len:使用的索引长度，比如：在某个比较长的列上，通常只会给前多少个字符创建索引，这个长度就表示索引字符的长度
ref:表示表的连接匹配条件中，哪些列或者常量被用于查找索引列上的值。可以理解为如果要完成这个查询，需要关联其他表中的哪个列或者常量
rows:要查询到目标数据，需要扫描的行数
Extra:其他额外信息，常见的有以下几种：
a.Using Where:表示查询结果需要在存储引擎层通过Where条件完成过滤
b.Using index:表示该Where条件的查询使用到了覆盖索引，即：该索引包括了满足查询所有数据的需求；
c.Using tempory:表示该查询使用到了临时表来存储中间结果集，通常在排序或者分组查询中会出现
d.Using filesort:表示该查询使用到了写磁盘的方式来存储结果集，出现这种情况，表示查询性能极差，已经发生了磁盘IO
以上的查询计划中主要关注的列：
type:查询类型，该列出现了ALL的查询类型，就表示查询语句有问题，需要根据索引使用的注意事项来排查
key:表示查询是否用到了索引，如果该列为NULL，表示索引未起到实际作用
rows:查询行数，如果特别大，和表中的数据条数相差不大，则表示索引利用率特别低，需要优化索引
Extra:如果发现有Using filesort，表示索引的效率特别差，已经发生了磁盘IO，需要排查对应的语句和索引使用情况
示例2：查询t_user表中id大于4的记录，观察其执行计划
EXPLAIN SELECT * FROM t_user WHERE id > 4\G
********* 1. row *********
id: 1
select_type: SIMPLE
table: t_user
type: range
possible_keys: PRIMARY
key: PRIMARY
key_len: 4
ref: NULL
rows: 1
Extra: Using where
1 row in set (0.00 sec)
从上述输出结果看出：type列为range，possible_keys和key都为PRIMARY，表示该查询使用到了范围查询，而且用到了主键索引。
示例3：查看t_user表中login_name列的索引使用情况
EXPLAIN SELECT * FROM t_user WHERE login_name = ‘aaa’ \G
*********** 1. row **********
id: 1
select_type: SIMPLE
table: t_user
type: ref
possible_keys: name_pass_ind
key: name_pass_ind
key_len: 152
ref: const
rows: 1
Extra: Using index condition
1 row in set (0.00 sec)
由结果可以看出，login_name列的条件查询用到了login_name,login_pass列的联合索引。
示例4：查看t_user表中id最大的记录的执行计划
EXPLAIN SELECT * FROM t_user WHERE id = (SELECT MAX(id) FROM t_user) \G
*********** 1. row ********
id: 1
select_type: PRIMARY
table: t_user
type: const
possible_keys: PRIMARY
key: PRIMARY
key_len: 4
ref: const
rows: 1
Extra: NULL
************ 2. row ********
id: 2
select_type: SUBQUERY
table: NULL
type: NULL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: NULL
Extra: Select tables optimized away
2 rows in set (0.00 sec)
由上述结果可以看出，位于WHERE之后的子查询的查询类型为SUBQUERY，直接查询最大id不会使用到索引。
7、索引优化
（1）对于主键索引，最好采用整型数字的格式，因为在普通索引中，索引树的叶子节点上存储的是主键索引的值，这个值如果过大，会导致普通索引会变的特别大
（2）在更新频繁的列上最好少创建索引，因为更新需要维护索引树，而这个维护过程是很耗时的
（3）创建联合索引时，应该考虑哪些组合列上的查询需求最大，从而确定联合索引的顺序，因为联合索引有左前缀规则
（4）索引并不是越多越好，索引如果过多，会导致磁盘大量浪费，而且在更新这些查询较少的列时，会产生很大的IO操作，造成服务器资源浪费
（5）索引可以在后期通过监控MySQL数据库中的慢SQL再来优化和添加。最好的办法还是开始就考虑周全，创建好索引
（6）添加索引的时候，需要注意是否有慢SQL，如果有慢SQL，会阻塞索引的添加操作，一直处于等待中
文章为作者原创，转载请务必标注出处：https://www.jinnianshizhunian.vip
手机端显示格式的问题，好多文章发布之后拍板会混乱，如果需要，可以复制上述作者网址，在PC端查看更多文章。
