索引

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

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

学习以后再来回头看一下MySQL索引背后的数据结构及算法原理

1、索引分类

在一个表中，主键索引只能有一个，唯一索引可以有多个

主键索引（primary key）
- 唯一的标识，主键不可重复，只能有一个列作为主键
唯一索引（unique key）
- 避免重复的列出现，可以重复，一张表中可以标识多个唯一索引
  
  举个例子：身份证一人一个不重复，就是唯一；但是一个人可以有很多其他证件，这些证件也都唯一，这就是唯一索引的可重复
常规索引（key/index）
- 默认的 index 或者key关键字来设置
全文索引（FullText）
- 在特定的数据库引擎下才有，myisam
- 快速定位数据

2、基础语法

-- 索引的使用

-- 1.在创建表的时候给字段增加索引
-- 2.创建完毕后，增加索引

-- 显示所有的索引信息
SHOW INDEX FROM student;

-- 新增一个索引
-- 语法为 ALTER TABLE `表名` ADD 索引类型 `索引名`(`字段名`)

ALTER TABLE `student` ADD UNIQUE KEY `UK_IDENTITY_CARD` (`identity_card`);
ALTER TABLE `student` ADD KEY `K_STUDENT_NAME`(`student_name`);

ALTER TABLE `student`  ADD FULLTEXT INDEX `FI_PHONE` (`phone`);

-- explain 分析sql执行的状况

EXPLAIN SELECT * FROM student; -- 非全文索引

EXPLAIN SELECT * FROM student WHERE MATCH(`phone`) AGAINST('138'); -- 全文索引

3、索引测试练习

1.创建数据库表并插入百万数据(实际上只插入了1000条，百万条太久了！)

SQL代码

CREATE TABLE app_user (
  `id` BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT 'ID',
  `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 COMMENT '创建时间',
  `update_time` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
  PRIMARY KEY (`id`)
) ENGINE=INNODB DEFAULT CHARSET=utf8mb4 COMMENT='app用户表'


-- 插入1000条数据b (函数)

DELIMITER $$ -- 写函数之前必须要写，标志
CREATE FUNCTION mock_data()
RETURNS INT
BEGIN
  DECLARE num INT DEFAULT 1000; -- 插入1000条数据
  DECLARE i INT DEFAULT 0;
  WHILE i<num DO
    INSERT INTO app_user(`name`,`email`,`phone`,`gender`,`password`,`age`)
    VALUES(CONCAT('用户',i),'123345@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();

SELECT * FROM app_user;

-- 函数中间的插入脚本
INSERT INTO app_user(`name`,
`email`,
`phone`,
`gender`,
`password`,
`age`)
VALUES(CONCAT('用户X'),
'123345@qq.com',
CONCAT('18',FLOOR(RAND()*((999999999-100000000)+100000000))),
FLOOR(RAND()*2),
UUID(),
FLOOR(RAND()*100));

2.索引功能测试

SQL代码

-- 加索引前
SELECT * FROM app_user WHERE `name` = '用户999'; 
EXPLAIN SELECT * FROM app_user WHERE `name` = '用户999';

-- 创建索引
-- id_表名_字段名  索引名
-- CREATE INDEX 索引名 ON 表名(`字段名`);
CREATE INDEX id_app_user_name ON app_user(`name`);
 -- 加索引后
SELECT * FROM app_user WHERE `name` = '用户999'; 
EXPLAIN SELECT * FROM app_user WHERE `name` = '用户999';

加索引之前的测试效果

很明显，不加索引的话，系统会进行逐条查询，直到查询到匹配内容
加索引之后的测试效果
- 添加索引 CREATE INDEX id_app_user_name ON app_user(name);
- 测试效果
  
  很明显，加了索引以后，系统会直接根据索引查询指定内容，这样速度会快很多
测试结论

索引在小数据量的时候，用处不大，但是在数据量相当大的时候，区分十分明显