一、索引的介绍:
1.索引的分类
索引 | 介绍 |
1.主键索引 (primary key) | 一般建表的时候添加,非空 ,比如 id,不能取名. |
2.唯一索引(unique key) | 表中的值必须唯一不能重复,比如账户名称, |
3.默认索引(key ) | 没有限制,可空,可重复 |
4.全文索引(fulltext) | 配合match against 操作而不是 where,先放数据在全文索引速度快些 |
5.组合索引 | 就是在多个字段上创建索引,但是只有查询第一个字段(最左)的时候索引才会生效 |
2. 设置索引的几种方法
1.在创建表的时候设置比如
create table if not exists `student`(
`name` varchar(100) not null comment "学生名称",
`id` int not null auto_increment comment"学生学号自增auto_increment",
`sex` char not null default 'M' comment "性别默认男",
primary key (`id`)
)engine = innodb default charset utf8;
效果:
2.修改表的时候添加索引。(transactionbank是数据库名称(添加索引的表之前要加数据库),特殊索引name是给索引取的名字,)
ALTER TABLE `transactionbank`.`student` ADD UNIQUE INDEX `特殊索引name` (`name`);
效果:
3.直接添加索引。默认索引(index)
CREATE INDEX `默认索引name` ON student(`sex`)
二、索引的使用(在100万条数据下的查询效果)
1. 使用函数给SQL插入100万条数据.
1.建表
CREATE TABLE `student` (
`id` int(10) unsigned zerofill NOT NULL AUTO_INCREMENT COMMENT '主键',
`name` varchar(20) NOT NULL COMMENT '学员名称(学员01)',
`phone` varchar(11) DEFAULT NULL COMMENT '学员的电话',
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=7759910 DEFAULT CHARSET=utf8
2.创建一个插入数据的函数
(1)如果有函数先删除
-- 如果有函数先删除,注意这里没有 () 括号
DROP FUNCTION IF EXISTS createDate
(2)创建函数(注意要先设置结束符号)
DELIMITER $$ -- 因为函数里面存在;结束符号,更改结束符号为 $$
CREATE FUNCTION createDate()
RETURNS INT -- returns 不是 return
BEGIN
DECLARE numberOfDate INT DEFAULT 1000000; -- 数据量 100万
DECLARE i INT DEFAULT 0; -- 遍历 i 为 0
WHILE i < numberOfDate DO -- 如果条件成立就执行
INSERT INTO `student`(`name`,`phone`)
VALUES
(CONCAT("学员",i), -- 学员名称
CONCAT("18",RAND()*(999999999-100000000)+100000000) -- 学员电话
);
SET i = i + 1;
END WHILE ; -- 结束循环 end while 不是 while end
RETURN i; -- 返回函数值
END;
$$ -- 结束函数的创建.
3.执行函数插入数据,(可能需要几十秒)
SELECT createDate();
2. 不使用索引的 查询
1.查询手机 为 18371641007 的学生全部信息(你可能无这个号码,你需要选一个自己有的号码)
SELECT * FROM student
WHERE phone = "18371641007";
2.查询时间: 0.120 sec
3. 使用索引进行查询
1.给手机号添加索引 默认索引(inser1000wdate是数据库名,student 是表名, 新默认索引01是自定义hi的索引名)
ALTER TABLE `inser1000wdate`.`student` ADD INDEX `新默认索引01` (`phone`);
由于数据比较多添加索引时间可能有点长
2.添加完成后查询手机 为 18371641007 的学生全部信息(你可能无这个号码,你需要选一个自己有的号码)
SELECT * FROM student
WHERE phone = "18371641007";
3。如果使用like查询呢?(依旧长时间)
(1)使用like查询 尾号为 007 的用户
SELECT * FROM student
WHERE `phone`LIKE "%007"
三、索引是一种数据结构 BTree 俗称 “B树”。
了解更多
四、索引的使用原则
1.索引可以增加查询的速度
2.索引会减慢增删,改的速度。
- 1.不要对经常变动的数据进行索引
- 2.小表不需要索引
- 3.一般索引只需要在大表的经常查询的字段上。