【Mysql】数据库索引，百万数据测试索引效果

• 这套Github上40K+star学习笔记，可以帮你搞定95%以上的Java面试

• 毫不夸张的说，这份SpringBoot学习指南能解决你遇到的98%的问题

• 给跪了！这套万人期待的 SQL 成神之路PDF，终于开源了

Mysql官方对索引的定义是：索引（index）是帮助Mysql高效获取数据的数据结构。进而，我们可以知道索引的本质是数据结构。

一、索引的分类

• 主键索引：也就是我们常见的 PRIMARY KEY，只有一个列作为主键，唯一标识，不可重复。
• 唯一索引：UNIQUE KEY，避免重复的列出现，唯一索引是可以有多个，同一张表里的多个列都可以设置唯一索引。
• 常规索引：KEY/INDEX，默认的类型，通过关键字INDEX或者KEY来设置。
• 全文索引：FullText，在特定的数据库引擎下才支持，作用是快速定位数据。

在创建表或者创建之后，都可以给字段增加索引。

比如现在创建一个测试用的表，我先在创建的时候加了前三种类型的索引：

-- 创建表CREATE TABLE `student`(`StudentNo` INT(4) NOT NULL COMMENT "学号",`LoginPwd` VARCHAR(20) DEFAULT NULL,`StudentName` VARCHAR(20) DEFAULT NULL COMMENT "学生姓名",`Sex` TINYINT(1) DEFAULT NULL COMMENT "性别：0-1",`GradeId` INT(11) DEFAULT NULL COMMENT "年纪编号",`Phone` VARCHAR(50) NOT NULL COMMENT "联系电话",`Address` VARCHAR(255) NOT NULL COMMENT "地址",`BornDate` DATETIME DEFAULT NULL COMMENT "出生日期",`Email` VARCHAR(50) NOT NULL COMMENT "邮箱",`IdentityCard` VARCHAR(18) DEFAULT NULL COMMENT "身份证号",PRIMARY KEY (`StudentNo`), -- 主键索引UNIQUE KEY `IdentityCard` (`IdentityCard`), -- 唯一索引，前面是索引名称，括号里是字段名KEY `Email` (`Email`) -- 常规索引)ENGINE=INNODB DEFAULT CHARSET=utf8;

执行sql，创建表成功。这时候我继续增加一个全文索引。

-- 增加一个全文索引类型，前面是索引名称，括号里是字段名ALTER TABLE `school`.`student` ADD FULLTEXT INDEX `StudentName` (`StudentName`);

可以查看student表的所有索引SHOW INDEX FROM student;

还有第三种，CREATE INDEX 索引名 on 表(字段)，到后面演示。

三、百万数据测试索引效果1. 再来创建个测试表-- 创建表CREATE TABLE `app_user`(`id` BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT,`name` VARCHAR(50) DEFAULT '' COMMENT '昵称',`email` VARCHAR(50) DEFAULT NULL COMMENT "邮箱",`phone` VARCHAR(20) DEFAULT NULL COMMENT "手机号",`gender` TINYINT(4) DEFAULT NULL COMMENT "性别 0-男, 1-女",`password` VARCHAR(100) NOT NULL COMMENT "密码",`age` TINYINT(4) NOT NULL COMMENT "年龄",`create_time` DATETIME DEFAULT CURRENT_TIMESTAMP,`update_time` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,PRIMARY KEY (`id`))ENGINE=INNODB DEFAULT CHARSET=utf8 COMMENT='app用户表';

执行创建成功，现在插入数据。

2. 插入数据-- 插入百万数据DELIMITER $$CREATE FUNCTION mock_data()RETURNS INTBEGINDECLARE num INT DEFAULT 1000000;DECLARE i INT DEFAULT 0;WHILE i < num DO-- 插入语句INSERT INTO `school`.`app_user`(`name`,`email`,`phone`,`gender`,`password`,`age`)VALUES(CONCAT('用户',i), '123456@qq.com', CONCAT('18', FLOOR(RAND()*((999999999-100000000)+100000000))),FLOOR(RAND()*2), UUID(), FLOOR(RAND()*100));SET i = i+1;END WHILE;RETURN i;END;

执行可能会出现This function has none of DETERMINISTIC, NO SQL, or READS SQL DATA in its declaration and binary报错。

如果出现，可以先执行set global log_bin_trust_function_creators=TRUE，然后再试下。

执行成功后，再执行：

SELECT mock_data();

大概持续1分钟左右，别急。

3. 测试查询

未加索引

查询一条数据。

SELECT * FROM `app_user` WHERE `name`='用户9999'

多次执行查询，发现花费时间，稳定在0.63s左右，这个从点击执行到看到结果，已经从肉眼可以感知有点慢了。

我们可以增加关键词EXPLAIN分析sql执行的情况。

EXPLAIN SELECT * FROM `app_user` WHERE `name`='用户9999'

可以看到这个语句查了99W+条，这都是时间消耗。

添加索引
用上面说的第三种方式，增加一个常规索引。

CREATE INDEX id_app_user_name ON app_user(`name`);

再重新执行下查询：

SELECT * FROM `app_user` WHERE `name`='用户9999'

查询时间大幅缩短，只需要要0.1s+。

再分析下加了索引后的查询。

EXPLAIN SELECT * FROM `app_user` WHERE `name`='用户9999'

只查了一条，精准查询。

三、索引使用原则

索引虽然好用，但是不可以滥用，这里有几个原则可以记一下：

• 索引不是越多越好。
• 不要对经常变动的数据加索引。
• 小数据量的表不需要加索引。
• 索引一般加在常用来查询的字段上。

以上就是对索引的简单介绍，但是MySQL索引背后的数据结构及算法原理，东西可就多了

推荐阅读更多精彩内容

• 源码时代软件测试干货|史上最全的mysql数据库优化方法，软件测试人手必备！ 史上最全的mys...

  课程介绍Ø 1.介绍什么是mysql优化Ø 2.mysql优化方法Ø 3.Mysql索引的使用Ø 4.分表技术 m...

  源码时代官方阅读321评论0赞0

• 重新学习Mysql数据库4：Mysql索引实现原理和相关数据结构算法

  本文转自互联网 本系列文章将整理到我在GitHub上的《Java面试指南》仓库，更多精彩内容请到我的仓库里查看 h...

  Java技术江湖阅读117评论0赞2

• 数据库相关（1）-- Mysql索引

  M ysql索引用来快速地寻找那些具有特定值的记录，所有MySQL索引都以B-树的形式保存。如果没有索引，执行查询...

  脆皮鸡大虾阅读53评论0赞0

• MySQL学习1：约束、存储引擎、事务、索引、视图、DBA命令、数据库设计三范式

  约束（Constraint） 什么是约束？在创建表的时候，可以给表的字段添加相应的约束，添加约束的目的是为了保证表...

  苦难_69e0阅读66评论0赞1

• MySQL数据库笔记 --- 索引机制

  说说你对 MySQL 索引的理解？ 索引是一种用于快速查询和检索数据的数据结构。常见的索引结构有: B 树， B+...

  _code_x阅读112评论0赞4