数据库——索引
MySQL官方对索引的定义为:索引(Index)是帮助MySQL高效获取数据的数据结构。
获取数据需要0.5s和获取数据需要0.001s,是有很大区别的!
因此,学习索引是很有必要的!
索引的本质:索引是数据结构。
一、索引的原则
- 索引不是越多越好;
- 不要对进程变动数据加索引;
- 小数据量的表不需要加索引;
- 索引一般加在常用来查询的字段上。
二、索引的分类
在一个表中,主键索引只能有一个,唯一索引可以有多个;
- 主键索引 (PRIMARY KEY )
- 唯一的标识,主键不可重复,只能有一个列作为主键;
- 唯一索引 (UNIQUE KEY)
- 避免重复的列出现,唯一索引可以重复,多个列都可以标识 唯一索引;
- 常规索引 (KEY/INDEX)
- 默认的 index 。通过 key 关键字来设置;
- 全文索引 (FullText)
- 在特定的数据库引擎下才有,MyISAM;
- 快速定位数据;
基础语法
-- 索引的使用
-- 1、在创建表的时候给字段增加索引
-- 2、创建完毕后,增加索引
-- 显示所有的索引信息
SHOW INDEX FROM student
-- 增加一个全文索引 (索引名) 列名
ALTER TABLE school.student ADD FULLTEXT INDEX `studentName`(`studentName`);
-- EXPLAIN 分析sql执行的状况
EXPLAIN SELECT * FROM student; -- 非全文索引
EXPLAIN SELECT * FROM student WHERE MATCH(studentName) AGAINST('刘');
三、测试索引
先创建一个用户表
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 NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE
CURRENT_TIMESTAMP,
PRIMARY KEY (`id`)
) ENGINE=INNODB DEFAULT CHARSET=utf8mb4 COMMENT='app用户表'
插入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`,`age`)VALUES(CONCAT('用
户',i),'24736743@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(); -- 我用时 1min 3sec
SELECT * FROM app_user WHERE `name` = '用户9999'; -- 我用时1.220sec
创建索引
-- id _ 表名 _ 字段名
-- CREATE INDEX 索引名 on 表(字段)
CREATE INDEX id_app_user_name ON app_user(`name`);
SELECT * FROM app_user WHERE `name` = '用户9999'; -- 用时0.001sec
EXPLAIN SELECT * FROM app_user WHERE `name` = '用户9999'; -- 唯一定位了,用时0.001sec
索引缺点:
- 索引在小数据量的时候,用处不大,优点显现不明显,还可能降低查询速度;
- 但是在大数据量的时候,优点十分明显,查询速度大大提高。