7.索引

# 7.索引

索引（INDEX）是帮助MySQL高效获取数据的数据结构

7.1索引的分类

主键索引（PRIMARY KEY）

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

举例子就是这里只有studentno设置为主键之后，其他什么studentname啊，loginpwd啊都不能设置为主键

Duplicate 重复的

唯一索引（UNIQUE KEY）

• 避免重复的列出现，唯一索引可以重复，多个列都可以表示为 唯一索引

举例子就是这里只有identitycard设置为唯一索引之后，其他什么studentname啊，loginpwd啊都可以设置为唯一索引

上面两个索引都不允许在同一列出现相同的值

常规索引（KEY/INDEX）

• 默认的，index/key关键字来设置

全文索引（FullText）

• 快速定位数据

基本语法：

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

-- 显示所有的索引信息
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('张')

7.2测试索引

插入100w条数据

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=utf8 COMMENT = 'app用户表'


-- 弄了半天， 终于可以了
DELIMITER $$
CREATE FUNCTION mock_data()
RETURNS INT
DETERMINISTIC
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() -- 执行此函数 生成一百万条数据

然后我们来分析一下索引的效率问题

SELECT * FROM `app_user` WHERE `name` = '用户9999' -- 现在我们查数据要用0.459 sec
EXPLAIN SELECT * FROM `app_user` WHERE `name` = '用户9999'-- 分析一下这条sql语句

要查9w多行才能找到用户9999，但是我们添加索引之后

-- 添加索引
-- 索引名格式：id_表名_字段名
-- create index 索引名 on 表名(字段名)
CREATE INDEX id_app_user_name ON app_user(`name`)
SELECT * FROM `app_user` WHERE `name` = '用户9999'-- 现在我们查数据要用0.013 sec
EXPLAIN SELECT * FROM `app_user` WHERE `name` = '用户9999'-- 分析一下这条sql语句

现在只需要查1行数据就能找到用户9999，大大的提高了效率

总结：

1.添加索引的三种方式

-- 1.直接在SQLyog里添加
-- 2.增加一个全文索引 
ALTER TABLE `student` 
ADD FULLTEXT INDEX `studentname`(`studentname`)-- 索引名(字段名)
-- 3.索引名格式：id_表名_字段名
-- create index 索引名 on 表名(字段名)
CREATE INDEX id_app_user_name ON app_user(`name`)

2.索引在小数据量的时候影响不大，但是在数据量大的时候区别十分明显

7.3索引原则

• 索引不是越多越好
• 不要对经常变动的数据加索引
• 小数据量的表不需要加索引
• 索引一般加在用来查询的字段上

索引的数据结构

Hash 类型的索引

Btree:INNODB默认的数据类型