数据库(DataBase)-索引详解及应用举例

7.索引

索引的定义：

MySQL官方对索引的定义为:索引(Index)是帮助MySQL高效获取数据的数据结构。

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

通过索引，可以使我们更快地获得我们所需的数据。

Eg：原先搜索耗时：0.5s；使用索引后：0.000001s；

索引的作用：

• 提高查询速度
• 确保数据的唯一性
• 可以加速表和表之间的连接 , 实现表与表之间的参照完整性
• 使用分组和排序子句进行数据检索时 , 可以显著减少分组和排序的时间
• 全文检索字段进行搜索优化.

1.索引的分类

·主键索引：Primary Key

​ 唯一的标识，主键不可重复，且只能有一个列作为主键。
特点 :

• 最常见的索引类型
• 确保数据记录的唯一性
• 确定特定数据记录在数据库中的位置

·唯一索引：Unique Key

​ 避免重复的列出现，唯一索引可以重复，每个列都可以标识为唯一索引。
与主键索引的区别：

• 主键索引只能有一个
• 唯一索引可能有多个

·常规索引：Key/Index

​ 默认的，使用Index或者Key关键字来设置。

注意 :

• index 和 key 关键字都可以设置常规索引
• 应加在查询找条件的字段
• 不宜添加太多常规索引,影响数据的插入,删除和修改操作

·全文索引：FullTest

​ 在特定的数据库引擎下才有，MyIsam。

​ 作用 : 快速定位特定数据

​ 注意 :

​ ·只能用于MyISAM类型的数据表

​ ·只能用于CHAR , VARCHAR , TEXT数据列类型

​ ·适合大型数据集

2.百万条数据测试

索引在查询数据中的用途，在小数据中一般难以体现，我们创建一个百万数据的用户表来测试索引的效果。

(1)创建测试表格：

-- 一百万条数据检验索引的效果。
use school;-- 数据库school

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`  timestamp NULL DEFAULT NULL COMMENT '创建时间',
    `update_time` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '修改时间',
    -- Mysql 5.5版本，需要注意：`create_time`和`update_time` 插入数据时，针对创建时间字段：在sql里now()  或者在代码里new date()更改后的sql，把默认值给个空，否则报错。
	PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='app用户表';

MySQL 5.5版本，需要注意：create_time和update_time 插入数据时，针对创建时间字段：在sql里now() 或者在代码里new date()更改后的sql，把默认值给个空，否则报错。

(2)插入百万条数据：

DROP FUNCTION IF EXISTS mock_data;
-- 写函数之前必须要写，标志:$$
DELIMITER $$
CREATE FUNCTION mock_data()
RETURNS INT
-- 注意returns，否则报错。
BEGIN
DECLARE num INT DEFAULT 1000000;
-- num 作为截止数字，定义为百万，
DECLARE i INT DEFAULT 0;
WHILE i < num DO
   INSERT INTO app_user(`name`, `email`, `phone`, `gender`, `password`, `age`)
   VALUES(CONCAT('用户', i), CONCAT('100',i,'@qq.com'), CONCAT('13', FLOOR(RAND()*(999999999-100000000)+100000000)),FLOOR(RAND()*2),UUID(), FLOOR(RAND()*100));
   SET i = i + 1;
END WHILE;
RETURN i;
END;

/* 部分数据插入分析：
`name` ——> CONCAT('用户', i):
CONCAT(str1,str2,……)
CONCAT函数，将“str1,str2,……”等字段连接起来，构成一个字符串。
连接一个“用户”+i的字符串，当做用户名。

`email` ——> CONCAT('100',i,'@qq.com')：
连接“100”+i+“@qq.com”，构成整个邮箱。

`phone` ——> CONCAT('13', FLOOR(RAND()*(999999999-100000000)+100000000))：
RAND()生成一个0-1的随机数，然后乘以一个足够大的数(999999999-100000000)之后，加上100000000，
然后，使用FLOOR()强转成舍弃小数的整数。

*/

前两步执行结果：

(3)数据查询检验：

·查询数据总量：耗时14.032sec。

SELECT mock_data();

输出：

·查询特定数据的耗时：

-- 对不同用户的查询，耗时一般不同，即使是同一个用户，多次查询的耗时也可能不同。
SELECT * FROM app_user WHERE name = '用户7777'; -- 1.188sec
SELECT * FROM app_user WHERE name = '用户7777'; -- 0.766sec
SELECT * FROM app_user WHERE name = '用户77777'; -- 0.641sec
SELECT * FROM app_user WHERE name = '用户777777'; -- 0.594sec

·查询查找过程经历的数据量：

explain SELECT * FROM app_user WHERE name = '用户7777'; -- 寻找了2009736条数据后才找到用户7777
explain SELECT * FROM app_user WHERE name = '用户77777'; -- 寻找了2995119条数据后才找到用户77777

寻找了2009736条数据后，才找到“用户7777”

·修改表格，增添索引：

创建索引的格式：create index 索引名 on 表名(加持字段)（注：索引的名字惯用格式：id _ 表名 _ 字段名）

-- 索引的名字惯用格式：id_表名_字段名
-- 创建索引的格式：create index 索引名 on 表名(加持字段)
create index id_app_user_name on app_user(`name`);

-- 添加索引后再次测试
explain SELECT * FROM app_user WHERE name = '用户7777';  -- 寻找了3条数据后才找到用户7777
explain SELECT * FROM app_user WHERE name = '用户77777';  -- 寻找了3条数据后才找到用户77777

索引在小数据中的用处不大，但是在大数据中效果很明显。

3.索引原则

·索引不是越多越好

·不要对经常变动的数据加索引

·小数据量的表建议不要加索引

·索引一般应加在查找条件的字段

-- 我们可以在创建上述索引的时候，为其指定索引类型，分两类
hash类型的索引：查询单条快，范围查询慢
btree类型的索引：b+树，层数越多，数据量指数级增长（我们就用它，因为innodb默认支持它）

-- 不同的存储引擎支持的索引类型也不一样
InnoDB 支持事务，支持行级别锁定，支持 B-tree、Full-text 等索引，不支持 Hash 索引；
MyISAM 不支持事务，支持表级别锁定，支持 B-tree、Full-text 等索引，不支持 Hash 索引；
Memory 不支持事务，支持表级别锁定，支持 B-tree、Hash 等索引，不支持 Full-text 索引；
NDB 支持事务，支持行级别锁定，支持 Hash 索引，不支持 B-tree、Full-text 等索引；
Archive 不支持事务，支持表级别锁定，不支持 B-tree、Hash、Full-text 等索引；

参考文献

《【狂神说Java】MySQL最新教程通俗易懂》
视频连接：https://www.bilibili.com/video/BV1NJ411J79W

《狂神说MySQL06：事务和索引》
原文连接

《mysql5.5 timestamp类型create_time和update_time更新时间》

原文连接
2020.05.18