索引、分页

1. 索引
作用：
- 约束
- 加速查找
索引：
- 主键索引：加速查找 + 不能为空 + 不能重复
- 普通索引：加速查找
- 唯一索引：加速查找 + 不能重复
- 联合索引（多列）：
- 联合主键索引
- 联合唯一索引
- 联合普通索引

加速查找：
快：
select * from tb where name='asdf'
select * from tb where id=999
假设：
id name email
...
...
..

无索引：从前到后依次查找
索引：
id 创建额外文件(某种格式存储)
name 创建额外文件(某种格式存储)
email 创建额外文件(某种格式存储) create index ix_name on userinfo3(email);
name email 创建额外文件(某种格式存储)

索引种类（某种格式存储）：
hash索引：
单值快
范围
btree索引： btree索引
二叉树

========》 结果：快 《========
建立索引：
- a. 额外的文件保存特殊的数据结构、
- b. 查询快；插入更新删除慢
- c. 命中索引

select * from userinfo3 where email='asdf';

select * from userinfo3 where email like 'asdf'; 慢
...
主键索引：

普通索引：
- create index 索引名称 on 表名(列名,)
- drop index 索引名称 on 表名
唯一索引：
- create unique index 索引名称 on 表名(列名)
- drop unique index 索引名称 on 表名

组合索引(最左前缀匹配)：
- create unique index 索引名称 on 表名(列名,列名)
- drop unique 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,)
select * from userinfo3 where name='alex' and email='asdf';
select * from userinfo3 where name='alex';
索引合并：
- name
- email
select * from userinfo3 where name='alex' and email='asdf';
select * from userinfo3 where name='alex';
select * from userinfo3 where email='alex';

名词：
覆盖索引：
- 在索引文件中直接获取数据

索引合并：
- 把多个单列索引合并使用


2. 频繁查找的列创建索引
- 创建索引
- 命中索引 *****

- 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 -- 不使用索引


3. 时间

执行计划：让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: const(走索引)

4. DBA工作

慢日志
- 执行时间 > 10
- 未命中索引
- 日志文件路径

配置：
- 内存
show variables like '%query%'
set global 变量名 = 值
- 配置文件
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:/....

注意：修改配置文件之后，需要重启服务

5. ******分页*******

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
)


c. 使用betwen：
id不连续，所以无法直接使用id范围进行查找

转载于:https://www.cnblogs.com/Mr-Feng/p/10935738.html