十四、MySQL索引

MySQL索引的建立对于MySQL的高效运行是很重要的，索引可以大大提高MySQL的检索速度。

拿汉语字典的目录页（索引）打比方，我们可以按拼音、笔画、偏旁部首等排序的目录（索引）快速查找到需要的字。

 

索引分单列索引和组合索引。

• 单列索引，即一个索引只包含单个列，一个表可以有多个单列索引，但这不是组合索引。
• 组合索引，即一个索引包含多个列。

 

创建索引时，你需要确保该索引是应用在 SQL 查询语句的条件(一般作为 WHERE 子句的条件)。

实际上，索引也是一张表，该表保存了主键与索引字段，并指向实体表的记录。

 

索引也会有它的缺点：

• 虽然索引大大提高了查询速度，同时却会降低更新表的速度，如对表进行INSERT、UPDATE和DELETE。因为更新表时，MySQL不仅要保存数据，还要保存一下索引文件。
• 建立索引会占用磁盘空间的索引文件。

 

索引分类：

• 主键索引（PRIMARY KEY）

ALTER TABLE tbl_name ADD PRIMARY KEY (column_list);--该语句添加一个主键，这意味着索引值必须是唯一的，且不能为NULL。

• 唯一索引（UNIQUE ）

ALTER TABLE tbl_name ADD UNIQUE index_name (column_list);--这条语句创建索引的值必须是唯一的（除了NULL外，NULL可能会出现多次）。

• 普通索引（INDEX/KEY）

ALTER TABLE tbl_name ADD INDEX index_name (column_list);-- 添加普通索引，索引值可出现多次。

• 全文索引（部分版本支持）（FULLTEXT）

ALTER TABLE tbl_name ADD FULLTEXT index_name (column_list);--该语句指定了索引为 FULLTEXT ，用于全文索引。

 

Demo：100w条数据的有无索引测试

1. 新建一个vip表

CREATE TABLE `vip` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) DEFAULT NULL,
  `age` int(11) DEFAULT NULL,
  `create_time` datetime DEFAULT CURRENT_TIMESTAMP,
  `update_time` datetime DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4

2. 编写一个插入100w条数据的函数，并执行

--编写函数
CREATE FUNCTION mock_data()
RETURNS INT
BEGIN
	DECLARE num INT DEFAULT 1000000;
	DECLARE i INT DEFAULT 1;
	WHILE i<=num DO
		INSERT INTO vip (`name`,age) VALUES (CONCAT('zs',i),RAND()*100);
		SET i=i+1;
	END WHILE;
	RETURN i-1;
END;

 --执行函数
SELECT mock_data();--大概需要一分钟左右

3. 性能对比

-- 未添加索引
SELECT * FROM vip WHERE `name` = 'zs10000'; -- 0.363s

ALTER TABLE vip ADD INDEX IX_USER_NAME(name); --添加普通索引

-- 添加索引后
SELECT * FROM vip WHERE `name` = 'zs10000'; -- 0.008s

可以看出，添加索引后的查询速度变得非常快，在并发访问的时候体现的更加明显。