MySQL之索引补充
MySQL之索引补充
1、索引的功能：
-- 加速查找
-- 约束
2、索引
-- 普通索引：加速查找
- 创建索引的语法：create index ‘创建的索引名称’ on 表名(字段);
-- 主键索引：加速查找+不能为空+不能重复
- 创建索引的语法：alter table 表名 add primary key (字段名);
-- 唯一索引：加速查找+不能重复
- 创建唯一索引的语法：create unique index ‘创建的索引名称’ on 表名(字段);
-- 联合索引(多列)：创建联合索引的语法：create index ‘创建的索引名称’ on 表名(字段,字段);
- 联合主键索引
- 联合唯一索引：create unique index ‘创建的索引名称’ on 表名(字段,字段);
- 联合普通索引
-- 组合索引：
- create index ix_name_email on userinfo(name,email);
- 最左前缀匹配：
select * from userinfo where name='george';
select * from userinfo where name='george' and email='123@123.com'
-- 覆盖索引：select id from student where id=999;
在索引文件中，直接获取数据。
-- 索引合并：select * from student where id=999 and email='123@123.com'; id 和 email是单列的
把多个单列索引合并使用。
***** 组合索引的效率 > 索引合并的效率
3、加速查找：
-- 索引，会创建一个额外的索引文件，查数据也会先查找这个索引文件。创建索引数据，会占用硬盘空间。
创建索引的语法：create index ‘创建的索引名称’ on 表名(字段);
删除索引的语法：drop index '索引名称' on 表名；
create index 'ix_name' on userinfo(name);
-- 索引种类：
- hash索引
- btree索引：二叉树
4、建立索引：
-- 需要额外的文件保存特殊的数据结构。
-- 创建索引查询快，但是插入和更新删除操作会慢。
-- 命中索引
5、频繁查找的列创建索引
-- 创建索引
-- 命中索引
- 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 -- 不使用索引
6、时间
-- 参考时间：
- 执行计划：让mysql预估执行操作(一般正确)
all < index < range < index_merge < ref_or_null < ref < eq_ref < system/const
- 慢：不走索引
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(走索引)
7、DBA工作
-- 慢日志
- 执行时间 > 10
- 未命中索引
- 日志文件路径
-- 配置：
- 内存
show variables like '%query%' #查看当前配置信息
set global 变量名 = 值 #修改当前配置
- 配置文件
mysqld --defaults-file='/etc/loacl/mysql-5.7.1/my-default.ini'
my.conf内容：
slow_query_log = ON
slow_query_log_file = D:/....
- 注意：修改配置文件之后，需要重启服务
8、分页
-- 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. ：between and
id不连续，所以无法直接使用id范围进行查找
参考博客：
http://www.cnblogs.com/wupeiqi/articles/5713323.html
http://www.cnblogs.com/wupeiqi/articles/5716963.html
----- END -----
