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

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 INT

BEGIN
DECLARE 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索引背后的数据结构及算法原理，东西可就多了，有一个大佬讲的挺细的，有兴趣可以翻下，传送门

–不要用肉体的勤奋，去掩盖思考的懒惰–
USB Microphone https://www.soft-voice.com/
Wooden Speakers https://www.zeshuiplatform.com/
亚马逊测评 www.yisuping.cn
深圳网站建设www.sz886.com