【学习笔记】索引
mysql官方对索引的定义为：索引（index）是帮助Mysql高效获取数据的数据结构
，提取句子主干，就可以得到索引的本质：索引是数据结构。
索引的分类
-
主键索引 （PRIMARY KEY）
-
唯一的标识，主键不可重复，只能有一个列作为主键
-
-
唯一索引 （UNIQUE KEY）
-
避免重复的列出现，唯一索引可以重复，多个列都可以标识为唯一索引
-
-
常规索引 （KEY/INDEX）
-
默认的，用index或key关键字来设置
-
-
全文索引
-
在特定的数据库引擎下才有，之前是MYISAM，现在INNODB也有了
-
快速定位数据
-
索引的使用：
-
在创建表的时候给字段增加索引
-
创建完毕后，增加索引
-- 显示所有的索引信息
SHOW INDEX FROM student
主键索引和唯一索引的Non_unique都是0，说明它们是唯一的，常规索引是1说明是不唯一的
-- 添加一个索引 索引名（列名）
ALTER TABLE `student` ADD FULLTEXT INDEX `studentname`(`studentname`)这是给student表添加了一个全文索引，索引名为studentname
它的数据类型为FULLTEXT
目前这个全文索引是没有用的，因为数据量太少
-- EXPLAIN 分析sql执行的状况
EXPLAIN SELECT * FROM student -- 非全文索引
SELECT * FROM student WHERE MATCH(studentname) against('张')
什么都没有查到，原因是全文索引在数据量比较少的情况下是没有用的
参考博客：
测试索引
我们创建一个表，用循环插入100万条数据，然后查询
首先创建一个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万数据.
DELIMITER $$
-- 写函数之前必须要写，标志
CREATE FUNCTION mock_data ()
RETURNS INT
BEGIN
DECLARE num INT DEFAULT 1000000;
DECLARE i INT DEFAULT 0;
WHILE i<num DO
INSERT INTO `app_user`(`name`,`email`,`phone`,`gender`,`password`)VALUES(CONCAT('用户',i),'19224305@qq.com','123456789',FLOOR(RAND()*2),'123456');
SET i=i+1;
END WHILE;
RETURN i;
END;
SELECT mock_data ()插入100万条数据花费142s
不添加索引，去查询 ‘用户9999’
SELECT * FROM app_user WHERE `name` = '用户9999'
耗时2.281s
EXPLAIN 查看sql执行过程
EXPLAIN SELECT * FROM app_user WHERE `name` = '用户9999'
可以发现查找了994559 行
现在我们给这个字段添加一个索引
-- CREATE INDEX 索引名 ON 表名(字段名)
CREATE INDEX id_app_user_name ON app_user(`name`)
创建索引耗时8.6404s
再去查询 ‘用户9999’
SELECT * FROM app_user WHERE `name` = '用户9999'
耗时0.003s
EXPLAIN 查看sql执行过程
EXPLAIN SELECT * FROM app_user WHERE `name` = '用户9999'
只查找了一行
对比：
| 没有索引 | 有索引 | |
|---|---|---|
| 查询语句时长 | 2.281s | 0.003s |
| 查询语句的行数 | 994559 | 1 |
通过对比，我们发现索引可以大大提升查询的效率
索引在小数据量的时候，用处不大，但是在大数据的时候，区别明显
索引原则
-
索引不是越多越好
-
不要对进程变动数据加索引
-
小数据量的表不需要加索引
-
索引一般加在常用来查询的字段上
索引的数据结构
Btree 是Innodb默认的数据结构
参考博客：
