MySQL 索引

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

索引是数据结构

索引的分类

  • 主键索引（primary key）
  • 唯一标识，不可重复，非空
  • 一个表中只能有一个列作为主键
  • 唯一索引（unique key）
  • 避免重复的列出现
  • 一个表中唯一索引可以设置多个
  • 常规索引（index、key）
  • 默认的，index，key关键字来设置
  • 全文索引（fulltext）
  • 在特定的引擎下才有（以前只有MyISAM有）
  • 快速定位数据

索引的基础语法

-- 添加索引信息
-- 增加全文索引
ALTER TABLE school.`student` ADD FULLTEXT INDEX `StudentName` (`StudentName`);
-- 增加唯一索引
ALTER TABLE school.`student` ADD unique INDEX `IdentityCard` (`IdentityCard`);
-- 或者下面这种
-- 添加常规索引
CREATE INDEX email ON student(`E-mail`);
-- 查询索引
SHOW INDEX FROM `Student`
-- 使用`explain`分析sql执行现状
EXPLAIN SELECT * FROM `Student`
-- 返回值中可以查看历遍的行数，非全文索引返回20行。

SQL server 枚举类型 是否适合 索引_数据结构

测试索引

首先需要创建测试的表：

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=utf8;

插入100万条测试数据：

函数使用：

DELIMITER $$   --函数定义标志
CREATE FUNCTION mock_data() -- 创建函数
RETURNS INT -- 编辑返回值
BEGIN  --函数开始
   DECLARE num INT DEFAULT 1000000;  --定义变量
   DECLARE i INT DEFAULT 0;  --定义变量
   WHILE i<num DO  --循环语句
      -- 插入循环语句
      SET i = i+1;  --i自增
   END WHILE   -循环语句结束
END;  --函数结束

创建测试数据：顺便学习实践使用使用rand()和floor()创建符合要求的随机数

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),CONCAT('test',i,'@'),concat('139',floor(rand()*100000000)),FLOOR(RAND()*2),UUID(),floor(rand()*(100-1)+1));
      SET i = i+1;
   END WHILE;
   RETURN i;
END;
-- 调用函数
SELECT mock_data();
-- 删除函数
DROP FUNCTION mock_data;

结果参考：

SQL server 枚举类型 是否适合 索引_MySQL_02

执行查询命令测试

SELECT * FROM app_user WHERE `name`='用户9999' -- 执行耗时   : 1.004 sec
-- 查看执行查询历遍了多少数据
EXPLAIN SELECT * FROM app_user WHERE `name`='用户9999' --查询了 992302 行

添加索引

CREATE INDEX id_app_user_name ON app_user(`name`);

执行查询命令测试

SELECT * FROM app_user WHERE `name`='用户9999' -- 执行耗时   : 0.003 sec
EXPLAIN SELECT * FROM app_user WHERE `name`='用户9999' --查询了 1行

索引原则

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

索引的数据结构

Hash类型的索引

参考博客：http://blog.codinglabs.org/articles/theory-of-mysql-index.html

Btree：InnoDB的默认数据结构，需要额外学习数据结构知识。

以上为本次学习记录。