Mysql数据库百万级数据测试索引效果
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索引背后的数据结构及算法原理，东西可就多了，有一个大佬讲的挺细的，有兴趣可以翻下，传送门
以上就是Mysql数据库百万数据测试索引效果的详细内容，更多关于Mysql百万数据测试索引的资料请关注编程网其它相关文章！
免责声明：
① 本站未注明“稿件来源”的信息均来自网络整理。其文字、图片和音视频稿件的所属权归原作者所有。本站收集整理出于非商业性的教育和科研之目的，并不意味着本站赞同其观点或证实其内容的真实性。仅作为临时的测试数据，供内部测试之用。本站并未授权任何人以任何方式主动获取本站任何信息。
② 本站未注明“稿件来源”的临时测试数据将在测试完成后最终做删除处理。有问题或投稿请发送至: 邮箱/279061341@qq.com QQ/279061341
![[mysql]mysql8修改root密码](https://static.528045.com/imgs/50.jpg?imageMogr2/format/webp/blur/1x0/quality/35)
