索引 作用： - 约束 - 加速查找 索引： - 主键索引：加速查找 + 不能为空 + 不能重复 - 普通索引：加速查找 - 唯一索引：加速查找 + 不能重复 - 联合索引（多列）： - 联合主键索引 - 联合唯一索引 - 联合普通索引 加速查找： 快： 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 )