原理:
通过不断缩小想要获取数据的范围来筛选出最终想要的结果,同时把随机的事件变成顺序事件。
常用索引
唯一索引:
主键索引 primary key 加速查找 + 约束(不为空,不能重复)
唯一索引 unique 加速查找 + 约束(不能重复)
联合索引:
primary key (id, name) 联合主键索引
unique (id, name) 联合唯一索引
index (id, name)联合普通索引
操作索引:
创建索引的语法:
一:创建表时:
create table 表名(
字段1 数据类型 [完整性约束条件],
[unique| fulltext |spatial | index | key
[索引名] (字段名[(长度)] [ASC | DESC])
二:在已经存在的表上创建:
create [unique| fulltext |spatial | index 索引名
on 表名 (字段名[(长度)] [ASC | DESC])
三: alter 在已经存在的表上创建
alter table 表名 add [unique| fulltext |spatial | index
索引名 (字段名[(长度)] [ASC | DESC])
四:删除索引:drop index 索引名 on 表名
添加主键索引
创建时添加: 添加索引时应尽量给数据比较小,区分度高的字段添加索引
聚集索引的添加方式:
创建时添加:
create table t1(id int primary key,)
create table t1(id int, primary key(id))
创建后添加:
alter table t1 add primary key(id)
删除索引
alter table t1 drop primary key
唯一索引
创建时添加:
create table t1(id int unique,)
create table t1(id int unique key uni_name (id))
表创建好后添加:
alter table t1 add unique key u_name(id);
删除唯一索引:
alter table t1 drop inde u_name;
普通索引:
创建时添加:
create table t1(id int, Index index_name(id))
创建后添加
alter table t1 add index index_name(id);
create index index_name on t1(id);
删除:
alter table t1 drop index index_name;
drop index index_name on t1;
准备数据
create table t1(
id int,
name varchar(20),
gender char(6),
email varchar(30)
);
delimiter $$
create procedure auto_insert1()
BEGIN
declare i int default 1;
while(i<3000000)do;
insert into t1 values(i,'Jack','male',concat('jack',i,'@edu.com'));
set i=i+1;
end while;
END$$
delimiter;
call auto_insert1();
在没有索引的前提下查询数据:
select * from t1 where id=1000000;
Empty set (0.15 sec)
mysql> select * from t1 where id=3000000;
Empty set (0.14 sec)
在数据存在的前提下为某个字段建立索引,速度很慢
mysql> alter table t1 add primary key(id);
Query OK, 0 rows affected (7.58 sec)
Records: 0 Duplicates: 0 Warnings: 0
或者:create index a on t1(id);
索引建立完后,以该字段为查询条件
mysql> select * from t1 where id=3000000;
Empty set (0.00 sec)
总结:
为搜索条件的字段创建索引 也就是where后的condition
在表中已经存在大量数据的前提下,创建索引会很慢,且占用硬盘空间
innodb 表的索引会存放在t1.ibd文件中,myisam表的索引则会有单独的索引文件table1.myi
正确使用索引:
索引未命中
范围问题,或者条件不明确,条件中出现这些符号或者关键字:> >= < <= != between and like
尽量选择区分度高的列作为索引,区分度的公式:count(distinct col)/count(*)
= 和in 可以乱序,比如a = 1 and b =2 and c =3 建立(a,b,c)索引可以任意顺序,mysql的查询优化器会帮你优化成 索引可以识别的形式
索引不能参与计算