MySQL数据库基础--索引详解

索引

索引的本质

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

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

索引的分类

• 主键索引 （PRIMARY KEY）
  • 唯一的标识，主键不可重复，只能有一个列作为主键
• 唯一索引 （UNIQUE KEY）
  • 避免一列中出现重复的行数据，唯一索引可以重复，多个列都可以标识为唯一索引
• 常规索引 （KEY / INDEX）
  • 默认的。index或key关键字设置
• 全文索引 （FullText）
  • 在特定的数据库引擎下才有，MyISM
  • 快速定位数据

索引的使用

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

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

-- 增加一个全文索引  索引名  （列名）
ALTER TABLE `student` ADD FULLTEXT INDEX `StudentName`(`StudentName`) 

-- id _ 表名 _ 字段名
-- CREATE INDEX 索引名 on 表（字段）
CREATE INDEX id_app_user_name ON app_user(`name`)

-- 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 NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=INNODB DEFAULT CHARSET=utf8mb4 COMMENT = 'app用户表'

-- 插入100万数据  33.496 sec
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),'123456@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` where `name` = '用户9999'; -- 0.492 sec
SELECT * FROM `app_user` WHERE `name` = '用户9999'; -- 0.398 sec
c -- 0.447 sec

explain SELECT * FROM `app_user` WHERE `name` = '用户9999';

-- id _ 表名 _ 字段名
-- CREATE INDEX 索引名 on 表（字段）
create index id_app_user_name on app_user(`name`)

SELECT * FROM `app_user` WHERE `name` = '用户9999'; -- 0.002 sec

EXPLAIN SELECT * FROM `app_user` WHERE `name` = '用户9999';  -- rows = 1

索引在小数据量时区别不大，在大数据量的时候，区别明显

索引原则

1. 索引不是越多越好
2. 不要对经常变动的数据加索引
3. 小数据量的表不需要加索引
4. 索引一般加在经常用于查询的字段上！

索引的数据结构：[CodingLabs - MySQL索引背后的数据结构及算法原理](