MySQL 索引
本文仅简单示例索引的创建与使用,侵删
一、索引的创建方式:
1、直接创建:
create [unique|fulltext|spatial] index|key on 表名(字段名(长度)[asc|desc]
2、创建表的时候创建索引:
create table 表名(
字符段 数据类型 【完整性约束条件】
.....
[unique|fulltext|spatial] index|key
[index_name](字段名(长度)[asc|desc])[using 索引方法]
)
3、修改表结构的时候添加索引:
alter table 表名 add [unique|fulltext|spatial] index|key
索引名 (字段名(长度)[asc|desc])[using 索引方法]
4、删除索引:
drop index 索引名 on 表名
说明:
- unique:可选,表示索引为唯一性索引
- fulltext:可选,表示索引为全文索引
- spatial:可选,表示索引为空间索引
- index和key是指定字段为索引,作用相同,二者选择一个
- 索引名(index_name):可选,给创建的索引取名字
- 字段:指定索引对应的字段的名称,该字段必须是前面定义好的字段
- 长度:可选,指定索引的长度,必须是字符串类型才可使用
- asc:可选,升序排列
- desc:可选,降序排列
- 索引方法:
hash、B-Tree。默认B-tree
两种方式的不同:
1)hash索引仅仅满足“=”,“IN”和“<=>”查询,不能使用查询范围
由于 Hash 索引比较的是进行 Hash 运算之后的 Hash 值,所以它只能用于等值的过滤,不能用于基于范围的过滤,因为经过相应的 Hash 算法处理之后的 Hash 值的大小关系,并不能保证和Hash运算前完全一样。
2)hash索引无法被用来避免数据的排序操作
由于 Hash 索引中存放的是经过 Hash 计算之后的 Hash 值,而且Hash值的大小关系并不一定和 Hash 运算前的键值完全一样,所以数据库无法利用索引的数据来避免任何排序运算;
(3)Hash 索引不能利用部分索引键查询。
对于组合索引,Hash 索引在计算 Hash 值的时候是组合索引键合并后再一起计算 Hash 值,而不是单独计算 Hash 值,所以通过组合索引的前面一个或几个索引键进行查询的时候,Hash 索引也无法被利用。
(4)Hash 索引在任何时候都不能避免表扫描。
前面已经知道,Hash 索引是将索引键通过 Hash 运算之后,将 Hash运算结果的 Hash 值和所对应的行指针信息存放于一个 Hash
表中,由于不同索引键存在相同 Hash 值,所以即使取满足某个 Hash 键值的数据的记录条数,也无法从 Hash
索引中直接完成查询,还是要通过访问表中的实际数据进行相应的比较,并得到相应的结果。
(5)Hash 索引遇到大量Hash值相等的情况后性能并不一定就会比B-Tree索引高。
对于选择性比较低的索引键,如果创建 Hash 索引,那么将会存在大量记录指针信息存于同一个 Hash 值相关联。这样要定位某一条记录时就会非常麻烦,会浪费多次表数据的访问,而造成整体性能低下。
二、索引的使用:
示例:
1、直接创建:
create index i1_age on c1_age(age);
2、创建表的时候创建索引:
create table c1(
id int,
name char,
age int,
sex enum('male','female'),
unique key uni_id(id),
index i1_name(name)
);
3、修改表结构的时候添加索引:
alter table c1 add index i1_sex(sex);
4、查看上述创建:
show create table c1;
| t1 | CREATE TABLE `t1` (
`id` int(11) DEFAULT NULL,
`name` char(1) DEFAULT NULL,
`age` int(11) DEFAULT NULL,
`sex` enum('male','female') DEFAULT NULL,
UNIQUE KEY `uni_id` (`id`),
KEY `i1_name` (`name`),
KEY `i1_age` (`age`),
KEY `i1_sex` (`sex`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
三、实例
1、准备
--准备表
create table c1(
id int,
name varchar(20),
gender char(6),
email varchar(50)
);
--创建存储过程,实现批量插入记录
delimiter $$ --声明存储过程的结束符号为$$
create procedure auto_insert1()
BEGIN
declare i int default 1;
while(i<3000000) do
insert into s1 values(i,'duoduo','male',concat('duoduo',i,'@oldboy'));
set i=i+1;
end while;
END$$ --$$结束
delimiter ; --重新声明分号为结束符号
--查看存储过程
show create procedure auto_insert1\G
--调用存储过程
call auto_insert1();
2、没有缩印的前提下测试查询速度
mysql> select * from s1 where id=333333333;
Empty set (0.33 sec)
3、表中存在大量数据的前提下,为某个字段建立索引,速度很慢
4、建立索引后,以该字段为查询条件是,查询速度提升明显
转载,原文链接