mysql 创建索引
MySQL 索引
内容主要摘抄自《MySQL5.7从入门到精通》
索引是对数据库表中的一列或多列进行排序的一种数据结构，使用索引可以提高数据库中特定数据的查询速度。
一、索引含义和特点
索引是一个单独的、存储在磁盘上的数据库结构，它们包含着对数据表里所有记录的引用指针。
mysql 中索引存储类型有两种：BTREE 和 HASH，具体和表的存储引擎相关；MyISAM 和 InnoDB 存储引擎只支持 BTREE 索引；MEMORY/HEAP 存储引擎可以支持 HASH 和 BTREE 索引。
二、索引分类
-
普通索引和唯一索引
普通索引：定义的索引列运行有空值和重复值。
唯一索引：索引列的值必须唯一，允许有空值。 -
单列索引和组合索引
单列索引：一个索引只包含单列。
组合索引：在表的多个字段组合上创建的索引，使用组合索引时遵循最左前缀集合，只有查询条件中使用了这些字段的左边字段，索引才会被使用。 -
全文索引
全文索引类型为 FULLTEXT，在定义索引的列上支持值的全文查找，允许索引列插入重复值和空值。全文索引可以在字符或文本类型的列上创建，mysql 中只有 MyISAM 存储引擎支持全文索引。
-
空间索引
mysql 空间数据类型有四种：GEOMETRY、POINT、LINESTRING 和 POLYGON。空间索引是对空间数据类型的字段建立的索引，创建空间索引的列必须声明为 NOT NULL。
三、索引设计原则
-
索引并非越多越好
一个表中若有大量索引，会占用磁盘空间，也会影响 insert、delete、update 等语句的性能，因为表中数据更改时，索引也会进行调整和更新。
-
索引中的列尽可能的少
对经常查询的字段应该创建索引，但要避免添加不必要的字段。
-
对数据量小的表不要使用索引
由于数据较少，查询的花费的时间可能比遍历索引的时间还要短，可能不会产生优化效果。
-
在不同值较多的列上建立索引
例如性别字段上只有“男”，“女”，“其他”，三个不同值，若在性别列上建立索引，可能不但不会提高查询效率，反而会严重降低数据更新速度。
四、查询表中索引
SHOW INDEX FROM test;
一般查询结果显示的是表格形式，在查询语句后加上 \G 可以将查询结果纵向显示。
上图显示 id 为唯一索引。
五、创建索引
官网参考手册（5.7）-- CREATE INDEX Syntax
1. 创建表时创建索引
创建名为 idx_name 的普通索引，对唯一性没有限制
CREATE TABLE test2 (
id int(11) NOT NULL AUTO_INCREMENT,
name varchar(255) NOT NULL,
num int(11) NOT NULL DEFAULT 0,
created_at timestamp NULL DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (`id`),
INDEX idx_name(name(5))
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
INDEX idx_name(name(5))
上面语句指的是截取字段 name 前5个字符作为索引，也就是说每条记录的name字段的值的前5个字符不能出现重复，否则在使用 INSERT 时会出现 “Duplicate” 错误。
2. 使用 ALTER TABLE 语句
ALTER TABLE test2 ADD INDEX idx_name(name(5));
3. 使用 CREATE INDEX 语句
CREATE INDEX idx_name ON test2(name(5));
六、删除索引
1. 使用 ALTER TABLE 删除
ALTER TABLE test2 DROP INDEX idx_name;
2. 使用 DROP INDEX 删除
DROP INDEX idx_name ON test2;
七、索引名称规范
-
普通索引
"idx_" 前缀加上字段名称，eg: idx_name
-
唯一索引
"uniq_"前缀加上字段名称，eg: uniq_name
八、索引长度限制
长度不超过767bytes,联合索引长度不超过3072bytes。
于InnoDB索引长度限制的tips
mysql innodb存储与索引的总结
九、什么情况适合建索引？
十、小测试
创建一个表，字段如下：
批量插入80万条数据，name长度为30字符左右
创建索引花了3秒多，创建索引后同一条查询语句，执行时间加快的差不多770倍，对比还是很明显的
