文章目录
(一) mysql笔记–基本概念
(二) mysql笔记–基本操作
(三) mysql笔记–事务
(四) mysql笔记–索引
(五) mysql笔记–其他操作
(六) mysql笔记–锁
4. 索引
索引的作用就是加速查找,查询数据库时不直接读取硬盘(前提要命中索引),每条索引的建立需要创建索引文件,所以索引影响插入删除速度
4.1 索引类型及存储
无索引:从前到后依次查找
索引类型:
- 主键索引:加速查找 + 不能为空 + 不能重复
- 普通索引:加速查找
- 唯一索引:加速查找 + 不能重复
- 联合索引(多列):
- 联合主键索引
- 联合唯一索引
- 联合普通索引
索引存储格式:
- hash索引:
单值查询速度快
范围查询慢 - btree索引:
btree索引(常用),由于硬盘的读取慢,使用块读取
二叉树,数的深度太高影响性能
4.2 索引的建立
- 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';
索引合并:
-- name索引
-- email索引
select * from userinfo3 where name='alex' and email='asdf';
4.3 索引命中
覆盖索引:
- 在索引文件中直接获取数据,即直接select索引
索引合并:
- 把多个单列索引合并使用,使用条件and俩索引
以下情况不命中索引:
设nid,email建立了索引
- 使用通配符 like ‘%xx’
select * from tb1 where email like '%cn';
- 使用函数
select * from tb1 where reverse(email) = 'asdf';
- 使用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 -- 不使用索引