索引

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

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

索引的分类：

1.主键索引：primary key

　　唯一标识：主键不可重复，只能有一列作为主键

2.唯一索引：unique key

　　避免重复列出现，可以重复，多个列都可标识为唯一索引

3.常规索引：key

　　默认的，也可用index关键字设置

4.全文索引：FullText

　　在特定的数据库引擎下才有，快速定位数据

-- ======索引==============
-- 显示所有索引信息
SHOW INDEX FROM student
-- 增加一个全文索引 
ALTER TABLE `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),'23124@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;

-- 执行function生成100万条数据
SELECT mock_data();  

然后进行测试查询name=用户9999的信息

-- 测试索引
SELECT * FROM app_user WHERE `name`='用户9999' -- 0.354s
SELECT * FROM app_user WHERE `name`='用户9999' -- 0.357s
EXPLAIN SELECT * FROM app_user WHERE `name`='用户9999'

创建一个常规索引：

-- 创建一个常规索引
CREATE INDEX id_app_user_name ON app_user(`name`)
SELECT * FROM app_user WHERE `name`='用户9999' -- 0.002s
SELECT * FROM app_user WHERE `name`='用户9999' -- 0.000s
EXPLAIN SELECT * FROM app_user WHERE `name`='用户9999'

很明显的查询提速了，其实也不是很明显，但是能看得出来

 

索引原则

··索引不是越多越好

··不要对经常变动的数据加索引

··小数据量的表不需要加索引

··索引一般加在常用来查询的字段上

 

索引的数据结构

InnoDB默认数据结构：Btree

相关文章：http://blog.codinglabs.org/articles/theory-of-mysql-index.html