MySQL数据索引，减少查询时间...

MySQL索引

前言

这一章学习的是MySQL关于索引方面的知识，索引定义如下：

MySQL官方对索引的定义为：索引（index）是帮助MySQL高效获取数据的数据结构。

提取句子主干，就可以得到索引的本质：索引是数据结构

1、索引的分类

主键索引（primary key）
- 唯一的标识，主键不可重复，只能有一个列作为主键
唯一索引（unique key）
- 避免重复的列出现，唯一索引可以重复，多个列可以标识
常规索引（key/index）
- 默认的，index/key 关键字来设置
全文索引（fulltext）
- 在特定的数据库引擎下才有，myisam
- 快速定位数据

2、索引的使用

-- 索引的使用
-- 1、在建表的时候给字段增加索引
-- 2、在创建完毕后,增加索引

-- 显示所有的索引信息
show index from 表名

-- 增加一个索引
alter table school.student add fulltext index `studentname`(`studentname`)

-- explain 分析SQL执行的状况
explain select * from student	-- 非全文索引
explain select * from student where match(studentname) against('刘')

3、索引的测试

创建一个 app_user表

CREATE TABLE `app_user` (
`id` BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT,
`name` VARCHAR(50) DEFAULT'' COMMENT'用户昵称',
`email` VARCHAR(50) NOT NULL COMMENT'用户邮箱',
`phone` VARCHAR(20) DEFAULT'' COMMENT'手机号',
`gender` TINYINT(4) UNSIGNED DEFAULT '0'COMMENT '性别（0：男;1:女）',
`password` VARCHAR(100) NOT NULL COMMENT '密码',
`age` TINYINT(4) DEFAULT'0'  COMMENT '年龄',
`create_time` DATETIME DEFAULT CURRENT_TIMESTAMP,
`update_time` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (`id`)
) ENGINE=INNODB DEFAULT CHARSET=utf8 COMMENT = 'app用户表'

插入100万条数据

-- 插入100万数，耗时15.087 sec
DELIMITER $$
CREATE FUNCTION mock_data()
RETURNS INT
DETERMINISTIC
BEGIN
 DECLARE num INT DEFAULT 1000000;
 DECLARE i INT DEFAULT 0;
 WHILE i < num DO
  INSERT INTO app_user(`name`,`email`,`phone`,`gender`,`password`,`age`)VALUES(CONCAT('用户',i),'12345678@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;


SELECT mock_data();`app_user` -- 创建完mock_data函数后使用

未创建索引测试查询时间

SELECT * FROM app_user WHERE `name` = '用户9999' -- 总耗时: 0.481 sec
explain SELECT * FROM app_user WHERE `name` = '用户9999'	-- 用explain检测执行sql情况

创建索引后测试查询时间

-- id 	表名	字段名
-- create index 索引名 on 表(字段)
CREATE INDEX id_app_user_name ON app_user(`name`) -- 总耗时:2.418 sec

SELECT * FROM app_user WHERE `name` = '用户9999' -- 总耗时: 0.010 sec
explain SELECT * FROM app_user WHERE `name` = '用户9999'	-- 用explain检测执行sql情况

未使用索引之前是使用遍历查询数据，创建索引之后是使用索引查询数据。

可以看到，遍历查询的rows是992391，而索引查询的rows是1，大大节约时间

总结：

索引在数据量小的时候，用处不大，在数据量庞大的时候，用处很大

4、索引原则

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

索引的数据结构

hash类型的索引

btree：innodb的默认数据结构

4、索引原则

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

索引的数据结构

hash类型的索引

btree：innodb的默认数据结构

深入学习可以参考博客：MySQL索引背后的数据结构及算法原理

后话

到这里MySQL关于索引的浅层学习就结束了，如果有任何的问题和错误欢迎在评论区指出，我会一一回复！

【MySQL】数据库的索引使用

MySQL索引

前言

1、索引的分类

2、索引的使用

3、索引的测试

4、索引原则

4、索引原则

后话