「Mysql」数据库索引，百万数据测试索引效果
Mysql官方对索引的定义是：索引（index）是帮助Mysql高效获取数据的数据结构。进而，我们可以知道索引的本质是数据结构。
一、索引的分类
主键索引：也就是我们常见的 PRIMARY KEY，只有一个列作为主键，唯一标识，不可重复。
唯一索引：UNIQUE KEY，避免重复的列出现，唯一索引是可以有多个，同一张表里的多个列都可以设置唯一索引。
常规索引：KEY/INDEX，默认的类型，通过关键字INDEX或者KEY来设置。
全文索引：FullText，在特定的数据库引擎下才支持，作用是快速定位数据。
二、使用索引
在创建表或者创建之后，都可以给字段增加索引。
比如现在创建一个测试用的表，我先在创建的时候加了前三种类型的索引：
-- 创建表CREATETABLE`student`(`StudentNo`INT(4)NOTNULLCOMMENT"学号",`LoginPwd`VARCHAR(20)DEFAULTNULL,`StudentName`VARCHAR(20)DEFAULTNULLCOMMENT"学生姓名",`Sex`TINYINT(1)DEFAULTNULLCOMMENT"性别：0-1",`GradeId`INT(11)DEFAULTNULLCOMMENT"年纪编号",`Phone`VARCHAR(50)NOTNULLCOMMENT"联系电话",`Address`VARCHAR(255)NOTNULLCOMMENT"地址",`BornDate`DATETIMEDEFAULTNULLCOMMENT"出生日期",`Email`VARCHAR(50)NOTNULLCOMMENT"邮箱",`IdentityCard`VARCHAR(18)DEFAULTNULLCOMMENT"身份证号", PRIMARYKEY(`StudentNo`),-- 主键索引UNIQUEKEY`IdentityCard`(`IdentityCard`),-- 唯一索引，前面是索引名称，括号里是字段名KEY`Email`(`Email`)-- 常规索引)ENGINE=INNODBDEFAULTCHARSET=utf8;
执行sql，创建表成功。这时候我继续增加一个全文索引。
-- 增加一个全文索引类型，前面是索引名称，括号里是字段名ALTERTABLE`school`.`student`ADDFULLTEXTINDEX`StudentName`(`StudentName`);
可以查看student表的所有索引SHOW INDEX FROM student;
还有第三种，CREATE INDEX 索引名 on 表(字段)，到后面演示。
三、百万数据测试索引效果
1. 再来创建个测试表
-- 创建表CREATETABLE`app_user`(`id`BIGINT(20)UNSIGNEDNOTNULLAUTO_INCREMENT,`name`VARCHAR(50)DEFAULT''COMMENT'昵称',`email`VARCHAR(50)DEFAULTNULLCOMMENT"邮箱",`phone`VARCHAR(20)DEFAULTNULLCOMMENT"手机号",`gender`TINYINT(4)DEFAULTNULLCOMMENT"性别 0-男, 1-女",`password`VARCHAR(100)NOTNULLCOMMENT"密码",`age`TINYINT(4)NOTNULLCOMMENT"年龄",`create_time`DATETIMEDEFAULTCURRENT_TIMESTAMP,`update_time`TIMESTAMPNULLDEFAULTCURRENT_TIMESTAMPONUPDATECURRENT_TIMESTAMP, PRIMARYKEY(`id`))ENGINE=INNODBDEFAULTCHARSET=utf8COMMENT='app用户表';
执行创建成功，现在插入数据。
2. 插入数据
-- 插入百万数据DELIMITER $$CREATE FUNCTION mock_data()RETURNS INTBEGINDECLARE num INT DEFAULT1000000; DECLARE i INT DEFAULT0; 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;ENDWHILE; 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 ，然后再试下。
执行成功后，再执行：
SELECTmock_data();
大概持续1分钟左右，别急。
3. 测试查询
未加索引
查询一条数据。
SELECT*FROM`app_user`WHERE`name`='用户9999'
多次执行查询，发现花费时间，稳定在0.63s左右，这个从点击执行到看到结果，已经从肉眼可以感知有点慢了。
我们可以增加关键词EXPLAIN分析sql执行的情况。
EXPLAINSELECT*FROM`app_user`WHERE`name`='用户9999'
可以看到这个语句查了99W+条，这都是时间消耗。
添加索引 用上面说的第三种方式，增加一个常规索引。
CREATEINDEXid_app_user_nameONapp_user(`name`);
再重新执行下查询：
SELECT*FROM`app_user`WHERE`name`='用户9999'
查询时间大幅缩短，只需要要0.1s+。
再分析下加了索引后的查询。
EXPLAINSELECT*FROM`app_user`WHERE`name`='用户9999'
只查了一条，精准查询。
三、索引使用原则
索引虽然好用，但是不可以滥用，这里有几个原则可以记一下：
索引不是越多越好。
不要对经常变动的数据加索引。
小数据量的表不需要加索引。
索引一般加在常用来查询的字段上。
以上就是对索引的简单介绍，但是MySQL索引背后的数据结构及算法原理，东西可就多了
