mysql索引添加_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 为唯一索引。

五、创建索引

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。

九、什么情况适合建索引？

十、小测试

创建一个表，字段如下：

批量插入80万条数据，name长度为30字符左右

创建索引花了3秒多，创建索引后同一条查询语句，执行时间加快的差不多770倍，对比还是很明显的