python-day44_MySQL数据库4-索引
1，索引
作用：约束，加速查找
分类：
主键索引：加速查找+不能为空+不能重复
普通索引：加速查找
唯一索引：加速查找+不能重复
联合索引(多列)：联合主键索引，联合唯一索引，联合普通索引
加速查找：
SELECT * FROM tb WHERE name='dksl' #从头到尾查找，慢
SELECT * FROM tb WHERE id=999 #主键查找快
无索引：从前到后依次查找
索引：
id 创建额外文件(某种格式存储)
name 创建额外文件(某种格式存储)
email 创建额外文件(某种格式存储) create index ix_name on userinfo3(email);
name email 创建额外文件(某种格式存储)
2，索引种类（某种格式存储）：
hash索引：
单值快
指定查找范围无法用于加速
btree索引：
二叉树
建立索引：
- a. 会生成额外的文件保存特殊的数据结构
- b. 查询快；但插入更新删除慢
- c. 查找时需命中索引才能加速
主键索引：
本身就能加速
普通索引：
- create index 索引名称 on 表名(列名,)
- drop index 索引名称 on 表名
唯一索引：
- create unique index 索引名称 on 表名(列名)
- drop unique index 索引名称 on 表名
组合索引(最左前缀匹配，即WHERE查找时，最左列名必须存在才会命中索引)：
- create index 索引名称 on 表名(列名1,列名2) #列名1是最左前缀
- drop index 索引名称 on 表名
例：
- create index ix_name_email on userinfo3(name,email)
- 最左前缀匹配
select * from userinfo3 where name='alex';
select * from userinfo3 where name='alex' and email='asdf';
select * from userinfo3 where email='alex@qq.com';
组合索引效率 > 索引合并
组合索引
- (name,email) #按这个建一个索引，以下2个才能命中索引
select * from userinfo3 where name='alex' and email='asdf';
select * from userinfo3 where name='alex';
索引合并：
- name 建一个索引
- email 建一个索引 #以下4个都能命中索引
select * from userinfo3 where name='alex' and email='asdf';
select * from userinfo3 where name='alex';
select * from userinfo3 where email='alex';
select * from userinfo3 where email='asdf' and name='alex';
覆盖索引： - 在索引文件中直接获取数据，不需要再查找数据库本身
索引合并： - 把多个单列索引合并使用
3，为频繁查找的列创建索引
以下操作会变慢
- 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';
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 -- 不使用索引
4，时间
执行计划：让mysql预估执行的操作类型(一般正确)
效率对比：
all < index < range < index_merge < ref_or_null < ref < eq_ref < system/const
id,email假设做了索引
慢：
select * from userinfo3 where name='alex'
explain select * from userinfo3 where name='alex' #运行执行计划
type: ALL(代表全表扫描)
select * from userinfo3 limit 1;
快：
select * from userinfo3 where email='alex'
type: ref(代表走索引)
5，DBA工作
慢日志
- 执行时间 > 10秒，记录到慢日志中
- 未命中索引
- 日志文件路径
配置慢日志：
- 基于内存
show variables like '%query%'
show variables like '%queries%'
set global 变量名 = 值 #修改当前配置
slow_query_log = OFF 是否开启慢日志记录
long_query_time = 2 时间限制，超过此时间，则记录
slow_query_log_file = /usr/slow.log 日志文件
log_queries_not_using_indexes = OFF 未使用索引的搜索是否记录
- 基于配置文件
mysqld --defaults-file='E:\wupeiqi\mysql-5.7.16-winx64\mysql-5.7.16-winx64\my-default.ini'
my.conf内容：
slow_query_log = ON
slow_query_log_file = D:/....
注意：修改配置文件之后，需要重启服务来加载它
6，分页
查找会从数据库开头开始找到开始行，再从指定行开始输出指定行数
a. select * from userinfo3 limit 20,10;
b.解决方案
- 超过的页数不让看
- 索引表中扫：
select * from userinfo3 where id in(select id from userinfo3 limit 200000,10)
- 方案：
记录当前页最大或最小ID
1. 页面只有上一页，下一页
# max_id
# min_id
下一页：
select * from userinfo3 where id > max_id limit 10;
上一页：
select * from userinfo3 where id < min_id order by id desc limit 10;
2. 上一页 192 193 [196] 197 198 199 下一页
select * from userinfo3 where id in (
select id from (select id from userinfo3 where id > max_id limit 30) as N order by N.id desc limit 10
) order by id limit 10;
c. *****为什么不用between*****：
id不连续，所以无法直接使用id范围进行查找
