索引的简介及应用

索引

索引用于快速找出在某个列中有一特定值的行。

索引的分类

• 主键索引（primary key）

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

• 唯一索引（unique key）

避免重复的列出现（字段），唯一索引可以重复（可以设置多个列为unique）。

• 常规索引（key/index）

默认设置，index，key关键字

• 全文索引（FullText）

特定数据库引擎下才有

快速定位

索引的使用

创建的两种方式：

1. 在创建表的时候给字段增加索引

2. 创建完毕后增加索引

-- 显示所有的索引信息
SHOW INDEX FROM 表名;
-- alter可以增加任意一个索引
-- 全文索引
ALTER TABLE 表名 ADD FULLTEXT INDEX 索引名（列名）;
-- 主键索引
ALTER TABLE 表名 ADD primary key(列名);
-- 普通索引
ALTER TABLE 表名 ADD INDEX 索引名（列名）;
-- 唯一索引
ALTER TABLE 表名 ADD unique(列名);

-- create只能增加普通索引和unique索引
-- 普通索引
CREATE INDEX 索引名 ON 表名(`列名`);
-- 唯一索引
CREATE unique INDEX 索引名 ON 表名(`列名`);



-- EXPLAIN 分析sql执行的效果
EXPLAIN SELECT * FROM sc;

上面会出现以下的结果：

EXPLAIN 下各个字段的含义，请参考：

EXPLAIN字段解释

测试索引

-- 插入百万数据
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 COMMENT='app用户表';

-- 了解即可
-- 定义一个函数
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), '24736743@qq.com', CONCAT('18', FLOOR(RAND()*(999999999-100000000)+100000000)),ROUND(RAND()*2),UUID(),FLOOR(RAND()*100));
     SET i = i + 1;
  END WHILE;
  RETURN i;
END;


-- 调用函数
SELECT  mock_data();

-- 插入成功
-- 进行查询
SELECT * FROM app_user WHERE `name`='用户15256';
-- 执行耗时   : 2.093 sec
EXPLAIN SELECT * FROM app_user WHERE `name`='用户15286';
-- 加入索引
CREATE INDEX id_app_user ON app_user(`name`);
SELECT * FROM app_user WHERE `name`='用户15256';
-- 执行耗时   : 0 sec
EXPLAIN SELECT * FROM app_user WHERE `name`='用户15286';

明显就可以发现加入索引的好处。

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

索引原则

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