|
mysql创建索引优劣分析
索引的优点:
1.减少服务器需要扫描的数据量
2.避免服务器排序和临时表
3.将随机I/O变成顺序I/O
为什么添加索引后查询速度会提高?
添加索引之后,mysql会通过BTREE算法创建索引文件,当查询数据时候,会找到索引文件进行遍历.
索引添加在哪些列上比较好?
1.比较频繁作为查询条件的字段应该创建索引
2.唯一性太差字段不适合创建索引
3.更新比较频繁的字段不适合做索引
4.要考虑在where以及order by涉及列上创建索引
例子:
创建表
create table test(
id int not null,
username varchar(16) not null
)
插入100000条数据
DELIMITER #
CREATE PROCEDURE pro1()
BEGIN
DECLARE i INT DEFAULT 0;
WHILE i<100000 DO
INSERT INTO test VALUES(i, CONCAT('aa', i));
SET i=i+1;
END WHILE;
END #
CALL pro1();
查询一条记录id=54321
select * from test where id=54321;
查看执行时间Total Time : 00:00:00:063
通过explain查看执行信息
explain select * from test where id=54321;
explain关键参数分析:
type:连接访问类型,比较重要参数
一般来说,至少range级别,最后能够达到ref级别
rows: 估计值,mysql认为获取一个查询结果需要扫描的行数
添加id索引:
create index index_id on test(id);
查看索引信息
show index from test;
查询一条记录id=54321
select * from test where id=54321;
查看执行时间Total Time : 00:00:00:000
通过explain查看执行信息
explain select * from test where id=54321;
普通索引:加速查询
1.创建表时候指定索引
create table test2(
id int not null,
username varchar(20) not null,
index index_username (username(10))
)
2.使用create创建索引
create index index_username on test2(username(10));
3.使用alter添加索引
alter table test2 add index index_username(username(10));
4.删除索引
drop index index_username on test2;
唯一索引:加速查询,唯一约束(可以含有null)
1.创建表时候指定索引
create table test3(
id int not null,
username varchar(20) not null,
unique index index_username (username(10))
)
2.使用create创建索引
create unique index index_username on test3(username(10));
3.使用alter添加索引
alter table test3 add unique index index_username(username(10));
4.删除索引
drop index index_username on test3;
复合(组合,多列)索引:
应用场景:频繁的同事使用多个列进行查询,例子: where n1='aaa' and n2=888;
1.创建表指定索引
create table test4(
name varchar(32) not null,
age int not null,
index index_name_age (name(10), age)
)
2.使用create创建索引
create index index_name_age on test4(name(10), age);
3.使用alter添加索引
alter table test4 add index index_name_age(name(10), age);
主键索引:加速查询,唯一约束(不可以含有null)
1.创建表的时候指定
create table test5(
id int not null primary key auto_increment,
username varchar(20)
)
索引注意事项:
索引的长度要合适.
列子:
create table test(
n1 char(200),
index index_n1 (n1(10))
)
索引的缺点:
1.降低更新表的速度,因为更新表的时候,不仅要保存数据,还要保存索引文件
2.创建索引会占用磁盘空间,一般情况下不会太严重,但是如果在一个大表上面创建多种组合索引,索引文件会膨胀很大.
|
|
收藏
淘帖
踩
