MySQL5.7索引知识点总结，方便大家理解什么是索引，索引设计和创建，删除等命令的掌握

索引是对数据表中列的值进行排序的一种结构，使用索引可以提高查询速度。

索引是数据库结构

索引是在存储引擎中实现的

所有存储引擎，至少支持16个索引

索引的存储类型：HASH,BTREE

MyISAM 和InnoDB只支持BTREE索引
MEMORY/YEAP支持HASH和BTREE索引

索引的优点（为什么使用索引）

1、通过创建唯一索引，可以保证数据库表中每一行数据的唯一性

2、可以加快速度的查询速度
3、在实现数据的参考完整性方面，可以加速表与表之间的连接
4、在使用分组和排序子句进行数据查询时候，也可以显著减少查询中分组和排序的时间

索引的不足

1、创建索引和维护索引要耗费时间，数据量越多，耗费的时间也增加
2、索引需要占磁盘空间
3、对数据表进行增删改，索引需要动态维护，降低数据的维护速度

索引的分类

1、普通索引：允许空值和重复值 和 唯一索引：值必须唯一，允许有空值。主键索引是特殊的唯一索引，不允许有空值

2、单列索引：一个索引只包含单个列，一个表可以有多个单列索引 和 组合索引：在表的多个字段组合上创建索引，遵循最左前缀集合

3、全文索引（MyISAM存储引擎支持）类型为：FULLTEXT 在CHAR/VARCHAR/TEXT上创建

4、空间索引：是对空间数据类型的字段创建的索引。空间数据类型：GEOMETRY,POINT,LINESTRING,POLYGON。使用SPATIAL关键字进行扩展。只能在MyISAM存储引擎上扩展

索引的设计原则：
1、索引不是越多越好。大量的索引会影响增删改。
2、避免对经常更新的表进行过多的索引
3、数据量小的表建议不要使用索引
4、在频繁进行排序或分组的列上建立索引，或组合索引

创建索引的方法：
1、创建表的时候创建索引。语法如下
CREATE TABLE table_name [col_name data_type]
[UNIQUE|FULLTEXT|SPATIAL] [INDEX|KEY] [index_name] (col_name [length]) [ASC|DESC]

[]代表可选参数
UNIQUE、FULLTEXT、SPATIAL分别表示：唯一索引，全文索引，空间索引
INDEX、KEY为同义词，用来指定创建索引
col_name为需要创建索引的字段列，如果不指定，MySQL默认为col_name为索引值，
length为索引长度，字符串类型的字段可以指定索引长度
ASC、DESC指定升序或者降序的索引值存储

例1：创建普通索引(单列索引)
CREATE TABLE t1 (
    id INT NOT NULL,
    name CHAR(50 ) NOT NULL,
    INDEX SingleIdx(name(20))
);

创建了一个名为SingleIdex的单列索引，长度为20

例2：创建唯一索引：
CREATE TABLE t2 (
   id INT NOT NULL,
   name CHAR(50) NOT NULL,
   id_card CHAR(19) NOT NULL,
   UNIQUE INDEX UniqIdx(id),
);

创建了唯一索引，名为UniqIdx的唯一索引

例3：创建组合索引
CREATE TABLE t3 (
    id INT NOT NULL,
    name CHAR(50) NOT NULL,
   age TINYINT(2) NOT NULL,
   info VARCHAR(255) NOT NULL,
   INDEX MultiIdx(id,name,age(100))
);

需要遵循最左前缀 如：(id,name,age),(id,name),id的字段组合可以。(id,age),(name,age)不能使用索引查询

例4：创建全文索引
CREATE TABLE t4(
    id INT NOT NULL,
    name CHAR(50) NOT NULL,
    info VARCHAR(255) NOT NULL,
    FULLTEXT INDEX FullTextIdx(info)
)ENGINE=MyISAM DEFAULT CHARSET=utf8;

MySQL5.7以上默认InnoDB，所以得指定存储引擎为MyISAM

例5：创建空间索引（在MyISAM 存储引擎上创建，字段必须为非空）
CREATE TABLE t5 (
    g GEOMETRY NOT NULL, 
    SPATIAL INDEX spatIdx(g) ENGINE = MyISAM;
);

2、在已经存在的表上创建索引

(1)ALTER TABLE table_name ADD [UNIQUE|FULLTEXT|SPATIAL] [INDEX|KEY] [index_name] (col_name[length]) [ASC|DESC]

(2)GREATE [UNIQUE|FULLTEXT|SPATIAL] INDEX index_name ON table_name (col_name[length]) [ASC|DESC]

例如：(1)ALTER TABLE t1 ADD INDEX nameIdx(name (50));
(2)CREATE UNIQUE nameIdx ON t1 (name(50));

查看索引的命令SHOW INDEX FROM t1 \G;

删除索引：

(1)ALTER TABLE table_name DROP INDEX index_name;
(2)DROP INDEX index_name ON table_name;