索引
作用：
- 约束
- 加速查找
索引：
- 主键索引：加速查找 + 不能为空 + 不能重复
- 普通索引：加速查找
- 唯一索引：加速查找 + 不能重复
- 联合索引（多列）：
- 联合主键索引
- 联合唯一索引
- 联合普通索引
加速查找：
快：
select * from tb where name='asdf'
select * from tb where id=999
一.普通索引
---普通索引仅有一个功能：加速查询
创建表加索引
create table userinfo(
nid int not null auto_increment primary key,
name varchar(32) not null,
email varchar(64) not null,
extra text,
index ix_name (name)
)
1.创建索引
create index ix_name on userinfo(email)
2.删除索引
drop index ix_name on userinfo
3.显示索引
show index from userinfo;
二、唯一索引
---唯一索引有两个功能：加速查询 和 唯一约束（可含null）
create table userinfo(
nid int not null auto_increment primary key,
name varchar(32) not null,
email varchar(64) not null,
extra text,
unique ix_name (name)
)
1.创建唯一索引
create unique index 索引名 on 表名(列名)
2.删除唯一索引
drop unique index 索引名 on 表名
三、主键索引
主键有两个功能：加速查询 和 唯一约束（不可含null）
create table userinfo(
nid int not null auto_increment primary key,
name varchar(32) not null,
email varchar(64) not null,
extra text,
index ix_name (name)
)
OR
create table userinfo(
nid int not null auto_increment,
name varchar(32) not null,
email varchar(64) not null,
extra text,
primary key(ni1),
index ix_name (name)
)
1.创建主键
alter table 表名 add primary key(列名);
2.删除主键
alter table 表名 drop primary key;
alter table 表名 modify 列名 int, drop primary key;
四、组合索引
组合索引是将n个列组合成一个索引
其应用场景为：频繁的同时使用n列来进行查询，如：where n1 = 'alex' and n2 = 666。
create table userinfo(
nid int not null auto_increment primary key,
name varchar(32) not null,
email varchar(64) not null,
extra text
)
1.创建组合索引
create index ix_name_email on userinfo(name,email);
2.如上创建组合索引之后，查询：
name and email -- 使用索引
name -- 使用索引
email -- 不使用索引
五.组合索引和索引合并的区别
组合索引-->一个索引有两列(最左前缀匹配)：
- create unique index 索引名称 on 表名(列名,列名)
- drop unique index 索引名称 on 表名
- create index ix_name_email on userinfo(name,email,)
- 最左前缀匹配
select * from userinfo where name='alex';
select * from userinfo where name='alex' and email='asdf';
select * from userinfo where email='alex@qq.com'; #这样是不会走索引
组合索引效率 > 索引合并
组合索引(两种查找方式)
- (name,email,)
select * from userinfo where name='alex' and email='asdf';
select * from userinfo where name='alex';
索引合并-->各自建一个索引（2个索引）(三种查找方式)：
- name
- email
select * from userinfo where name='alex' and email='asdf';
select * from userinfo where name='alex';
select * from userinfo where email='alex';
名词：
覆盖索引：
- 在索引文件中直接获取数据
索引合并：
- 把多个单列索引合并使用
- 创建索引
- 命中索引 *****
无法命中索引的几种方式
-- like '%xx'
select * from tb1 where email like '%cn';
-- 使用函数
select * from tb1 where reverse(email) = 'wupeiqi';
-- or
select * from tb1 where nid = 1 or name = 'seven@live.com';
特别的：当or条件中有未建立索引的列才失效，以下会走索引
select * from tb1 where nid = 1 or name = 'seven@live.com' and email = 'alex'
-- 类型不一致
如果列是字符串类型，传入条件是必须用引号引起来，不然...
select * from tb1 where email = 999;
-- !=
select * from tb1 where email != 'alex'
特别的：如果是主键，则还是会走索引
select * from tb1 where nid != 123
-- >
select * from tb1 where email > 'alex'
特别的：如果是主键或索引是整数类型，则还是会走索引
select * from tb1 where nid > 123
select * from tb1 where num > 123
-- order by
select name from tb1 order by email desc;
当根据索引排序时候，选择的映射如果不是索引，则不走索引
特别的：如果对主键排序，则还是走索引：
select * from tb1 order by nid desc;
-- 组合索引最左前缀
如果组合索引为：(name,email)
name and email -- 使用索引
name -- 使用索引
email -- 不使用索引
分页 比如拿去第200万条数据
a. select * from userinfo limit 20,10;
b.- 索引表中扫：
select * from userinfo where id in(select id from userinfo limit 200000,10)
- 方案：
记录当前页最大或最小ID
1. 页面只有上一页，下一页
# max_id
# min_id
下一页：
select * from userinfo where id > 200000 limit 10;
select * from userinfo where id > max_id limit 10;
上一页：
select * from userinfo where id < 200000 order by id desc limit 10;
select * from userinfo where id < min_id order by id desc limit 10;
2. 上一页 192 193 [196] 197 198 199 下一页
select * from userinfo where id in (
select id from (select id from userinfo where id > max_id limit 30) as N order by N.id desc limit 10
)
浙公网安备 33010602011771号