MySQL--索引类型详解

索引的类型

主键索引：

PRIMARY KEY，当一张表的某个列是主键的时候，该列就是主键索引，一张表只允许有一个主键索引，主键所在的列不能为空。

创建主键索引的SQL语法：

# 给user表中的id字段创建名为id_index的主键索引,使用ALTER创建主键索引
ALTER TABLE `user` ADD PRIMARY KEY id_index(`id`);

注意主键索引不能使用create index语句创建。

唯一索引：

unique ，一个或多个列组成组成唯一，就可以做唯一索引，一个表可以有多个唯一索引，唯一索引的列不能为空。

创建唯一索引的SQL语法：

# 给user表中的user_name字段创建名为index_name 的唯一索引
CREATE UNIQUE INDEX index_name on user(user_name)

普通索引：

最基本的索引类型，没有限制，可以为空，可以有多个。

# 给user表中的age字段创建名为index_age 的唯一索引
CREATE INDEX index_age on user(age)

联合索引：

也称复合索引，就是在多个列上建立的索引。

# 给user表中的address、hobby字段创建名为index_address_hobby 的联合索引
CREATE INDEX index_address_hobby on user(address,hobby)

覆盖索引：

个人理解覆盖索引就是一种特殊的联合索引，就是查询的列在索引中就可以获取到，无需读取数据行，使一种查询手法的优化。

# 给user表中的name、age、gender字段创建名为index_name_age_gender的索引
CREATE INDEX index_name_age_gender on user(name,age,gender)

全文索引：

全文索引的类型是FullText，全文索引只支持varchar、char、text类型的列上创建，MyISAM支持全文索引，InnoDB在MySQL5.6之后支持了全文索引（项目中在MySQL上使用全文索引的场景不太多，后面会更新一篇全文索引使用详解）。

# 给user表中的name字段创建全文索引
ALTER TABLE user 名 add FULLTEXT(name);

索引结构分类（InnoDB存储引擎）：

按索引的存储结构分类，索引可以分为主键索引和二级索引（辅助索引），普通索引、联合索引、覆盖索引都可以理解为二级索引的某一种。

主键索引和二级索引（InnoDB存储引擎）：

主键索引的特点：

• 唯一，主键索引要求列的值必须唯一。
• 非空，主键索引要求列不能为空。
• 是聚集索引，非叶子节点不存储数据，叶子节点数据有序。
• 效率高，通过主键索引可以快速的定位到表中的唯一一行数据。

主键索引树的结构实例：

二级索引的特点：

• 叶子节点存储的是二级索引所在行的主键索引。
• 非叶子节点存储的是完整的索引关键字信息。
• 遵循最左前缀原则。
• 支持更为丰富的查询场景。

单列索引树的结构实例：

联合索引树的结构实例：

索引相关实用型SQL语法

ALTER TABLE和CREATE INDEX 语法的区别？

• ALTER TABLE 可以创建主键索引，而CREATE INDEX 不可以创建主键索引。
• CREATE INDEX 必须提供索引名，ALTER TABLE 如果没有指定索引名称，则会将自动创建。
• CREATE INDEX 一个语句只能创建一个索引，ALTER TABLE 可以创建多个。

 ALTER TABLE user ADD INDEX(column1),ADD INDEX(column2);

查看索引和删除索引的SQL语法：

 #查看tablename的所有索引
 SHOW INDEX  FROM tablename;
 #删除tablename中的索引index_name 
 DROP INDEX index_name ON tablename;  ;

隐藏索引：

隐藏索引功能可以作为一个SQL调优的小技巧，通过隐藏索引用来测试索引的性能，验证索引的必要性，避免了频繁删除新建索引，SQL调优完成，如果不影响性能再真正地删除索引。

 #隐藏tablename 的索引index_name 
ALTER TABLE tablename ALTER  INDEX  index_name INVISIBLE; 
 #取消tablename 的索引index_name 的隐藏
ALTER TABLE tablename ALTER  INDEX  index_name VISIBLE;   

索引的优点：

• 根据业务场景合理创建索引，可以大大提高各种查询速度，特别是在数量量大的情况下。
• 唯一索引可以避免数据重复插入。

索引的缺点：

• 索引需要占用存储空间。
• 对数据进行增删改的时候，还需要同时维护索引，有一定的开销。
• 索引设计不合理或者索引过多，可能会影响查询效率。

索引设计原则：

• 经常作为查询条件的字段创建索引。
• 经常需要分组、排序的字段创建索引。
• 在辨识度高的字段建立索引，如果是0、1这种类型的字段建议不要创建索引了。
• 尽量创建联合索引，少创建单列索引。
• 控制索引的数量，不要过多的为一个表创建索引。
• 及时删除用不到的索引。

如有不正确的地方请各位指出纠正。