mysql创建索引
MySQL 索引
内容主要摘抄⾃《MySQL5.7从⼊门到精通》
索引是对数据库表中的⼀列或多列进⾏排序的⼀种数据结构，使⽤索引可以提⾼数据库中特定数据的查询速度。
⼀、索引含义和特点
索引是⼀个单独的、存储在磁盘上的数据库结构，它们包含着对数据表⾥所有记录的引⽤指针。
mysql 中索引存储类型有两种：BTREE 和 HASH，具体和表的存储引擎相关；MyISAM 和 InnoDB 存储引擎只⽀持 BTREE 索引；MEMORY/HEAP 存储引擎可以⽀持 HASH 和 BTREE 索引。
⼆、索引分类
1. 普通索引和唯⼀索引
普通索引：定义的索引列运⾏有空值和重复值。
唯⼀索引：索引列的值必须唯⼀，允许有空值。
2. 单列索引和组合索引
单列索引：⼀个索引只包含单列。
组合索引：在表的多个字段组合上创建的索引，使⽤组合索引时遵循最左前缀集合，只有查询条件中使⽤了这些字段的左边字段，索引才会被使⽤。
3. 全⽂索引
全⽂索引类型为 FULLTEXT，在定义索引的列上⽀持值的全⽂查，允许索引列插⼊重复值和空值。全⽂索引可以在字符或⽂本类型的列上创建，mysql 中只有 MyISAM 存储引擎⽀持全⽂索引。
4. 空间索引
mysql 空间数据类型有四种：GEOMETRY、POINT、LINESTRING 和 POLYGON。空间索引是对空间数据类型的字段建⽴的索引，创建空间索引的列必须声明为 NOT NULL。
三、索引设计原则
1. 索引并⾮越多越好
⼀个表中若有⼤量索引，会占⽤磁盘空间，也会影响 insert、delete、update 等语句的性能，因为表中数据更改时，索引也会进⾏调整和更新。
2. 索引中的列尽可能的少
对经常查询的字段应该创建索引，但要避免添加不必要的字段。
3. 对数据量⼩的表不要使⽤索引
由于数据较少，查询的花费的时间可能⽐遍历索引的时间还要短，可能不会产⽣优化效果。
4. 在不同值较多的列上建⽴索引
例如性别字段上只有“男”，“⼥”，“其他”，三个不同值，若在性别列上建⽴索引，可能不但不会提⾼查询效率，反⽽会严重降低数据更新速度。
四、查询表中索引
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。
查看mysql索引九、什么情况适合建索引？
⼗、⼩测试
创建⼀个表，字段如下：
批量插⼊80万条数据，name长度为30字符左右
创建索引花了3秒多，创建索引后同⼀条查询语句，执⾏时间加快的差不多770倍，对⽐还是很明显的

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。