索引
是用来快速检索出具有特定值的记录。如果没有索引,数据库就必须从第一条记录开始进行全表扫描,直到找出相关的行。数据越多,检索的代价就越高,检索时如果表的列存在索引,那么MySQL
就能快速到达指定位置去搜索数据文件,而不必查看所有数据。
索引依托于存储引擎的实现,因此,每种存储引擎的索引都不一定完全相同,并且每种存储引擎也不一定支持所有索引类型。所有存储引擎支持每个表至少16个索引,总索引长度至少为256字节。大多数存储引擎有更高的额限制。
MySQL中索引的存储类型有两种: BTREE和HASH
,具体和表的存储引擎相关;
MyISAM和InnoDB存储引擎只支持BTREE索引,MEMORY/HEAP存储引擎可以支持HASH和BTREE索引。
优点
缺点
普通索引和唯一索引
单列索引和组合索引
全文索引
FULLTEXT
,在定义索引的列上支持值的全文查找,允许在这些索引列中插入重复值和空值。全文索引可以在CHAR、VARCHAR或者TEXT类型的列上创建,MySQL中只有MyISAM存储引擎支持全文索引索引设计不合理或者缺少索引都会对数据库和应用程序的性能造成障碍,高效的索引对于获得良好的性能非常重要。
注意事项
INSERT、DELETE、UPDATE
等语句的性能,因为当表中的数据更改的同时,索引也会进行调整和更新使用 CREATE TABLE 创建表的时候,除了可以定义列的数据类型,还可以定义主键约束、外键约束或者唯一性约束,而不论创建哪种约束,在定义约束的同时相当于在指定列上创建了一个索引。
创建表时创建索引的基本语法如下:
CREATE TABLE table_name[col_name data_type]
[UNIQUE|FULLTEXT|SPATIAL]
[INDEX|KEY]
[index_name](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='客户表';
测试
-- 查看当前表的索引情况
SHOW INDEX FROM customer1;
-- 使用 EXPLAIN 分析 SQL语句 是否使用了索引
EXPLAIN SELECT * FROM customer1 WHERE customer_id = 1;
释义
EXPLAIN 语法下章会详细讲解,本章重心是索引
MySQL
执行查询时预计从当前数据表中读出的数据行数SHOW INDEX FROM 语法
当 possible_keys
与 key
都为 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 '客户姓名',
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 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`);
CREATE INDEX语句可以在已经存在的表上添加索引,MySQL中CREATE INDEX被映射到一个ALTER TABLE语句上,基本语法结构为:
CREATE [UNIQUE|FULLTEXT|SPATIAL] INDEX index_name ON table_name(col_name[length],...)[ASC|DESC]
看到和ALTER INDEX语句的语法基本一样,下面把 customer1
表删除了再创建,所有字段都没有索引,用CREATE INDEX语句创建一次索引:
CREATE INDEX idx_customer_id ON customer1(`customer_id`);
CREATE UNIQUE INDEX idx_customer_id ON customer1(`customer_id`);
CREATE INDEX idx_group_customer ON customer1(`customer_id`,`customer_name`);
最后一项工作就是删除索引了,可以使用 ALTER TABLE和DROP INDEX
删除索引。
ALTER TABLE的基本语法为:
ALTER TABLE table_name DROP EXISTS index_name;
ALTER TABLE table_name DROP INDEX IF EXISTS index_name;
建议大家使用第二条
DROP INDEX的基本语法为:
DROP INDEX index_name ON table_name
DROP INDEX IF EXISTS index_name ON table_name
建议大家使用第二条
注意一个细节,删除表中的列时,如果要删除的列为整个索引的组成部分,则该列也会从索引中删除;如果组成索引的所有列都被删除,则整个索引将被删除