MySQL索引

---

什么是索引

我们需要知道索引其实是一种数据结构，其功能是帮助我们快速匹配查找到需要的数据行，是数据库性能优化最常用的工具之一。其作用相当于超市里的导购员、书本里的目录。

---

索引类型

主键索引:
主键是一种唯一性索引，但它必须指定为PRIMARY KEY，每个表只能有一个主键。

alert table tablename add primary key (字段名)

唯一索引:
索引列的所有值都只能出现一次，即必须唯一，值可以为空。

alter table table_name add primary key (字段名);

普通索引 :
基本的索引类型，值可以为空，没有唯一性的限制。

alter table table_name add index (字段名);

全文索引:
全文索引的索引类型为FULLTEXT。全文索引可以在varchar、char、text类型的列上创建。可以通过ALTER TABLE或CREATE INDEX命令创建。对于大规模的数据集，通过ALTER TABLE（或者CREATE INDEX）命令创建全文索引要比把记录插入带有全文索引的空表更快。MyISAM支持全文索引，InnoDB在mysql5.6之后支持了全文索引。

alter table 表名 add FULLTEXT(字段名);

---

查看表的所有索引和删除

 #查看:
show indexes from `表名`;
或
show keys from `表名`;
#删除
alter table `表名` drop index 索引名;

---

索引的机制

1.为什么我们添加完索引后查询速度为变快？

• 传统的查询方法，是按照表的顺序遍历的，不论查询几条数据，mysql需要将表的数据从头到尾遍历一遍
• 在我们添加完索引之后，mysql一般通过BTREE算法生成一个索引文件，在查询数据库时，找到索引文件进行遍历(折半查找大幅查询效率)，找到相应的键从而获取数据

2.索引的代价

• 创建索引是为产生索引文件的，占用磁盘空间
• 索引文件是一个二叉树类型的文件，可想而知我们的dml操作同样也会对索引文件进行修改，所以性能会下降

3.在哪些column上使用索引？

• 较频繁的作为查询条件字段应该创建索引
• 唯一性太差的字段不适合创建索引，尽管频繁作为查询条件，例如gender性别字段
• 新非常频繁的字段不适合作为索引
• 不会出现在where子句中的字段不该创建索引

---

优缺点

MySQL中索引的存储类型有两种：BTREE和HASH，具体和表的存储引擎相关；
MyISAM和InnoDB存储引擎只支持BTREE索引，MEMORY/HEAP存储引擎可以支持HASH和BTREE索引。

优点

• 加快数据的查询速度
• 唯一索引，可以保证数据库表中每一行数据的唯一性
• 在实现数据的参考完整性方面，可以加速表和表之间的连接
• 在使用分组和排序子句进行数据查询时，也可以显著减少查询中分组和排序的时间

缺点

• 占用磁盘空间，除了数据表占数据空间之外，每一个索引还要占一定的物理空间，如果有大量的索引，索引文件可能比数据文件更快达到最大文件尺寸（合理运用，问题不大）
• 损耗性能（添加、修改、删除） 索引需要动态地维护

注意事项

• 索引并非越多越好，一个表中如有大量的索引，不仅占用磁盘空间，而且会影响INSERT、DELETE、UPDATE等语句的性能，因为当表中的数据更改的同时，索引也会进行调整和更新
• 避免对经常更新的表设计过多的索引，并且索引中的列尽可能要少，而对经常用于查询的字 段应该创建索引，但要避免添加不必要的字段
• 数据量小的表最好不要使用索引，由于数据较少，查询花费的时间可能比遍历索引时间还要短，索引可能不会产生优化效果
• 在条件表达式中经常用到的不同值较多的列上建立索引，在不同值较少的列上不要建立索引，比如性别字段只有男和女，就没必要建立索引。如果建立索引不但不会提高查询效率，反而会严重降低更新速度
• 当唯一性是某种数据本身的特征时，指定唯一索引。使用唯一索引需能确保定义的列的数据完整性，以提高查询速度
• 在频繁排序或分组（即group by或order by操作）的列上建立索引，如果待排序的列有多个，可以在这些列上建立组合索引

---

实例

使用 CREATE TABLE 创建表的时候，除了可以定义列的数据类型，还可以定义主键约束、外键约束或者唯一性约束，而不论创建哪种约束，在定义约束的同时相当于在指定列上创建了一个索引。
创建表时创建索引的基本语法如下：

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为需要创建索引的字段列，该列必须从数据表中该定义的多个列中选择
• index_name为指定索引的名称，为可选参数，如果不指定则MySQL默认col_name为索引值
• length为可选参数，表示索引的长度，只有字符串类型的字段才能指定索引长度
• ASC或DESC指定升序或者降序的索引值存储

普通索引

-- 这句作用是,如果 customer1 存在就删除
DROP TABLE IF EXISTS customer1;
CREATE TABLE `customer1` (
  `customer_id` bigint(20) NOT NULL COMMENT '客户ID',
  `customer_name` varchar(30) DEFAULT NULL COMMENT '客户姓名',
  INDEX `idx_customer_id` (`customer_id`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='客户表';

唯一索引
单列索引是在数据表中的某一个字段上创建的索引，一个表中可以创建多个单列索引，前面两个例子中创建的索引都是单列索引，比如：

DROP TABLE
IF EXISTS customer1;

CREATE TABLE `customer1` (
    `customer_id` BIGINT (20) NOT NULL COMMENT '客户ID',
    `customer_name` VARCHAR (30) DEFAULT NULL COMMENT '客户姓名',
    UNIQUE INDEX `idx_customer_id` (`customer_id`) USING BTREE
) ENGINE = INNODB DEFAULT CHARSET = utf8mb4 COMMENT = '客户表';

这样就代表在表的customer_id字段上创建了一个名为idx_customer_id的唯一索引

组合索引
组合索引是在多个字段上创建一个索引，比如：

DROP TABLE
IF EXISTS customer1;

CREATE TABLE `customer1` (
    `customer_id` BIGINT (20) NOT NULL COMMENT '客户ID',
    `customer_name` VARCHAR (30) DEFAULT NULL COMMENT '客户姓名',
     INDEX `idx_group_customer` (`customer_id`,`customer_name`) USING BTREE
) ENGINE = INNODB DEFAULT CHARSET = utf8mb4 COMMENT = '客户表';

SHOW INDEX FROM customer1;

这就为customer_id、customer_name两个字段成功创建了一个名为idx_group_customer的组合索引，通过SHOW INDEX FROM customer1; 将会看到两条记录

全文索引
全文索引可以对全文进行搜索，只有MyISAM存储引擎支持全文索引，并且只为CHAR、VARCHAR和TEXT列，索引总是对整个列进行，不支持局部索引，比如：

DROP TABLE
IF EXISTS customer1;

CREATE TABLE `customer1` (
    `customer_id` BIGINT (20) NOT NULL COMMENT '客户ID',
    `customer_name` VARCHAR (255) DEFAULT NULL COMMENT '客户姓名',
     FULLTEXT INDEX `idx_fulltext_customer_name` (`customer_name`)
) ENGINE = MyISAM DEFAULT CHARSET = utf8mb4 COMMENT = '客户表';

SHOW INDEX FROM customer1;

因为默认的存储引擎为InnoDB，而全文索引只支持MyISAM，所以这里创建表的时候要手动指定一下引擎。

看到这么创建，就在info字段上成功建立了一个名为idx_fulltext_customer_name的FULLTEXT全文索引，全文索引非常适合大型数据库，而对于小的数据集，它的用处可能比较小

在已经存在的表上创建索引
在已经存在的表上创建索引，可以使用ALTER TABLE语句或者CREATE INDEX语句，所以，分别讲解一下如何使用ALTER TABLE和CREATE INDEX语句在已知的表字段上创建索引。

ALTER TABLE 语法
ALTER TABLE创建索引的基本语法为：

ALTER TABLE table_name ADD [UNIQUE|FUUTEXT|SPATIAL]
[INDEX|KEY] [index_name] (col_name[length],...) [ASC|DESC]

普通索引

ALTER TABLE customer1 ADD INDEX idx_customer_id(`customer_id`);

ALTER TABLE customer1 ADD INDEX idx_customer_id(customer_name(50));

意思是查询的时候，只需要检索前面50个字符。这里专门提一下，对字符串类型的字段进行索引，如果可以尽可能的指定一个前缀长度，例如，一个CHAR(255)的列，如果在前10个或者前30个字符内，多数值是唯一的，则不需要对整个列进行索引，短索引不仅可以提高查询速度而且可以节省磁盘空间、减少I/O操作。

唯一索引

ALTER TABLE customer1 ADD UNIQUE INDEX `idx_customer_id` (`customer_id`);

组合索引

ALTER TABLE customer1 ADD INDEX `idx_group_customer` (`customer_id`,`customer_name`);

删除索引
最后一项工作就是删除索引了，可以使用ALTER TABLE和DROP INDEX删除索引。

ALTER TABLE 语法
ALTER TABLE的基本语法为：

ALTER TABLE table_name DROP EXISTS index_name;

ALTER TABLE table_name DROP INDEX IF EXISTS index_name;

DROP INDEX 语法
DROP INDEX的基本语法为：

DROP INDEX index_name ON table_name

DROP INDEX IF EXISTS  index_name ON table_name