1 索引：
1.1 索引类型
- 普通索引 特性：加速查找
- 主键索引 特性：加速查找 + 不能为空 + 不能重复
- 唯一索引 特性：加速查找 + 不能重复
- 联合索引 特性：加速查找
- 联合普通索引
- 联合主键索引
- 联合唯一索引
- 全文索引：会借助第三方工具做
索引存储种类（某种格式存储）：
- hash索引：存储的顺序和数据表不一致，找单值速度快
- btree索引（mysql常用索引）：一般用于数据库的索引综合效率较高
1.2 索引操作
创建普通索引：
create index index_email on user(email);
删除普通索引：
drop index index_email on user;
创建唯一索引：
create unique index index_name on user(name);
删除唯一索引：
drop index index_name on user;
创建联合索引：
create index index_email_name on user(email,name);
注：联合索引遵循最左前缀匹配原则
select * from user where email='abc123431@qq.com'; -- 走索引
select * from user where email='abc123431@qq.com' and name='alex3423'; -- 走索引
select * from user where name='alex3423' and email='abc123431@qq.com'; -- 不走索引
select * from user where name='alex3423'; -- 不走索引
注意：联合索引效率 > 索引合并
删除联合索引：
drop index index_email_name on user;
有索引和无索引的区别：
无索引：从前到后依次查找
有索引：创建额外文件（某种格式存储），会占用磁盘空间
1.3 索引补充
建立索引：
- 额外的文件保存特殊的数据结构
- 查询快，插入更新删除慢
- 命中索引
索引名词补充
- 覆盖索引 在索引文件中直接获取数据
select * from user where email='alex3423@qq.com'; - 索引合并 把多个单列索引合并使用
select * from user where email='abc123431@qq.com' and id =123432;
1.4 命中索引
只有满足下面两点索引才能加速查询(假设nid和email是索引列)
- 创建索引
- 命中索引
以下几种情况不能命中索引：
-
like '%xx'
select * from tb1 where name like '%cn'; -
使用函数
select * from tb1 where reverse(name) = 'wupeiqi'; -
or
select * from tb1 where nid = 1 or name = 'seven@live.com';
特别的：当or条件中有未建立索引的列才失效，以下会走索引
select * from tb1 where nid = 1 or email = 'seven';
select * from tb1 where nid = 1 or email = 'seven@live.com' and name = 'alex' -
类型不一致
如果列是字符串类型，传入条件是必须用引号引起来，不然...
select * from tb1 where email = 999; -
!=
select * from tb1 where email != 'abc123431@qq.com'
特别的：如果是主键，则还是会走索引
select * from tb1 where nid != 123 -
select * from tb1 where name > 'alex'
特别的：如果是主键或索引是整数类型，则还是会走索引
select * from tb1 where nid > 123
select * from tb1 where num > 123 -
order by
注：部分版本会存在此问题
select name from tb1 order by email desc; -- 根据哪一列order by就取select哪一列的值
当根据索引排序时候，选择的映射如果不是索引，则不走索引
特别的：如果对主键排序，则还是走索引：
select * from tb1 order by nid desc; -
组合索引最左前缀
如果组合索引为：(name,email)
name and email -- 使用索引
name -- 使用索引
email -- 不使用索引
其他注意事项：
- 避免使用select *
- count(1)或count(列) 代替 count(*)
- 创建表时尽量时 char 代替 varchar
- 表的字段顺序固定长度的字段优先
- 组合索引代替多个单列索引（经常使用多个条件查询时）
- 尽量使用短索引，比如如果字段类型是text类型，则不能再该列建立索引
- 使用连接（JOIN）来代替子查询(Sub-Queries)
- 连表时注意条件类型需一致
- 索引散列值（重复少）不适合建索引，例：性别不适合
1.5 执行计划
查询时的访问方式，性能：all < index < range < index_merge < ref_or_null < ref < eq_ref < system/const （一般情况下是准确的）
ALL 全表扫描，对于数据表从头到尾找一遍
select * from tb1;
特别的：如果有limit限制，则找到之后就不在继续向下扫描
select * from tb1 where email = 'seven@live.com'
select * from tb1 where email = 'seven@live.com' limit 1;
虽然上述两个语句都会进行全表扫描，第二句使用了limit，则找到一个后就不再继续扫描。
INDEX 全索引扫描，对索引从头到尾找一遍
select nid from tb1;
RANGE 对索引列进行范围查找
select * from tb1 where name < 'alex';
PS:
between and
in
> >= < <= 操作
注意：!= 和 > 符号
INDEX_MERGE 合并索引，使用多个单列索引搜索
select * from tb1 where name = 'alex' or nid in (11,22,33);
REF 根据索引查找一个或多个值
select * from tb1 where name = 'seven';
EQ_REF 连接时使用primary key 或 unique类型
select tb2.nid,tb1.name from tb2 left join tb1 on tb2.nid = tb1.nid;
CONST 常量
表最多有一个匹配行,因为仅有一行,在这行的列值可被优化器剩余部分认为是常数,const表很快,因为它们只读取一次。
select nid from tb1 where nid = 2 ;
SYSTEM 系统
表仅有一行(=系统表)。这是const联接类型的一个特例。
select * from (select nid from tb1 where nid = 1) as A;
possible_keys 可能使用的索引
1.6 慢查询日志配置
- 执行时间大于1s（自定义）的
- 执行语句未走索引的
global变量在线配置，立即生效
修改配置文件重启MySQL生效
1.7 分页实战
select * from user limit 200000,10;
当起始位置越大时，查询效率越低
实战：提高博客中的页码翻页查询效率
解决方案：
- 不让看
- 索引表中扫(效率提升不是很大)
select * from user where id in (select id from user limit 200000,10);
- 记录当前页的起始id和结束id
max_id 当前页最大id
min_id 当前页最小id
每页显示10条数据
情况1：页面只有上一页下一页
select * from user where id > max_id limit 10;
select * from user where id < max_id order by id desc limit 10;
情况2：用户进行翻页时可跳转几页
例如，用户跳转了3页向后翻页
select * from user where id
in (select id from user where id > max_id limit 30) as N order by N.id desc limit 10 order by N.id asc;
例如，用户跳转了3页向前翻页
select * from user where id
in (select id from user where id < min_id order by id desc limit 30) as N order by N.id desc limit 10 order by N.id asc;
ps：id不连续，所以无法直接使用id范围进行查找