索引

索引

MySQL官方对索引的定义为：索引（Index）是帮助MySQL高效获取数据的数据结构。提取句子主干，就可以得到索引的本质：索引是数据结构。

索引的分类

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

• 主键索引 (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`  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用户表';


-- 插入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),'[email protected]',CONCAT('18',RAND()*((999999999-10000000)+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.538 sec
SELECT * FROM app_user WHERE `name`='用户9999'; -- 0.592 sec
SELECT * FROM app_user WHERE `name`='用户9999'; -- 0.590 sec

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

SELECT * FROM student
 
-- id_表名_字段名
-- create index 索引名 on 表(字段)
CREATE INDEX id_app_user_name ON app_user(`name`);
SELECT * FROM app_user WHERE `name`='用户9999';-- 0.005 sec
EXPLAIN SELECT * FROM app_user WHERE `name`='用户9999';

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