mysql索引

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

加速查找：
快：
select * from tb where name='asdf' （name做了普通索引的情况下）
select * from tb where id=999
假设：
id name email
...
...
..

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

索引种类（某种格式存储）：
hash索引：
单值快 （适用于频繁的查询单个数据）
范围
btree索引： btree索引 (innodb引擎使用)
二叉树

========》 结果：快 《========
建立索引：
- 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'; （慢，因为email不是最左，虽然做了组合索引）

组合索引效率 > 索引合并
组合索引 （遵循最左前缀匹配时，检索速度快）
- (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@qq.com';
select * from userinfo3 where name='alex';
select * from userinfo3 where email='alex@qq.com';

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

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


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条件中有未建立索引的列才失效，以下会走索引 （当name跟emai建立了普通索引时，如有一个未建立将失效）
select * from tb1 where nid = 1 or name = 'seven';
select * from tb1 where nid = 1 or name = 'seven' and email = 'seven@live.com'


- 类型不一致
如果列是字符串类型，传入条件是必须用引号引起来，不然...
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预估执行操作(一般正确) （在做查询时，先用explain命令查看type类型）
执行效率排列： 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; （虽然是all类型，但由于数据较小，查询速度还是快）
快：
select * from userinfo3 where email='alex'
type: const(走索引)

4. DBA工作

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

配置：
- 内存
show variables like '%query%'
set global 变量名 = 值
- 配置文件
mysqld --defaults-file='E:\xxx\my-default.ini' （自定义路径，启动服务端时运行命令将会加载自定义的配置文件）

my.conf内容：
slow_query_log = ON 长索引监测日志开关
slow_query_log_file = D:/....

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

5. ******分页*******（分页查看sql的方法，网页显示）

a. select * from userinfo3 limit 20,10;（直接按ID来分页）
b.
- 不让看 （当limit后面的值过大，会影响查询速度，所以直接限定最大能看的页数，减少查询压力）
- 索引表中扫：
select * from userinfo3 where id in(select id from userinfo3 limit 200000,10)（通过id索引来查询，效率有些许提高，但效果并不明显差别不大）
- 方案：
记录当前页最大或最小ID
1. 页面只有上一页，下一页
# max_id （获取当前页的最大ID）
# min_id （获取当前页的最小ID）
下一页：
select * from userinfo3 where id > max_id limit 10; （定位到当前页最大ID，大于它即是下一页，取10凑足一页）
上一页：
select * from userinfo3 where id < min_id order by id desc limit 10; （定位到当前页最小ID，小于它即是上一页，取10凑足一页，
需要注意的是，小于最小ID的第一个数，直接Limit往后取还是本页，所以需要desc倒着取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
)
（同上，翻多少页就乘以几个10，（n-当前页码）*10来取到初始位置再order by desc limit 往前取10）

c. between:

假设id连续的情况下，页码就可以用 where id>(n-1)*10 and id<=n*10 如第10页 就是91-100对应的id数据。

大多数情况下，id不连续，所以无法直接使用id范围进行查找。