专栏目录请点击
简介
- 索引就像一本书的目录,添加了索引数据库能根据索引很快查找到需要的内容
- 数据库会自动添加id字段为索引,在mysql中,如果一个字段上有unique约束的话,也会自动创建索引
- 在任何数据库中,任何一张表的任意一条记录,在实际硬盘上存储都有一个硬盘的物理存储编号
- 不管存储在哪里,索引在mysql中都是以树的形式而存在(平衡二叉树:B-Tree)
查询过程
增加了索引也就是缩小了扫描的范围
假设下面下面这张图示id的索引
我们执行下面的sql语句
SELECT * from tabal WHERE id = 101
- mysql发现有id索引,那么就会找到这个二叉树,定位到101所在的节点,找到节点坐在的地址0x6666
- 这个时候所谓的查询就是直接查询地址,通过这个地址能找到相应的行
什么时候添加索引
- 一个表的数据量很庞大,发现查询的速度确实很慢,这个时候我们需要添加索引
- 一些字段经常用来查询
- 该字段又很少的DML(增删)操作,因为增删之后,二叉树需要重新排序
索引的分类
单列索引
- 主键索引
- 数据库表的主键会自动创建索引
- 唯一索引
- 当某个列添加了Unique约会,也会创建索引,要求值必须是唯一的
- 普通索引
- 给普通字段添加的索引就是普通索引
组合索引
- 多个字段合起来创建一个索引,只有当查询添加中使用了组合索引的第一个字段,索引才会被使用
使用
创建索引
create index 索引名 on 表名(要创建索引的字段);
删除索引
drop index 索引名 on 要删除的索引所在的表名;
查看索引
SHOW INDEX FROM 表名;
例子
例子
SHOW INDEX FROM action_type;
参数说明
参数 | 说明 |
---|---|
Table | 表示创建索引的数据表名, |
Non_unique | 表示该索引是否是唯一索引。若不是唯一索引,则该列的值为 1;若是唯一索引,则该列的值为 0。 |
Key_name | 表示索引的名称。 |
Seq_in_index | 表示该列在索引中的位置,如果索引是单列的,则该列的值为 1;如果索引是组合索引,则该列的值为每列在索引定义中的顺序。 |
Column_name | 表示定义索引的列字段。 |
Collation | 表示列以何种顺序存储在索引中。在 MySQL 中,升序显示值“A”(升序),若显示为 NULL,则表示无分类。 |
Cardinality | 索引中唯一值数目的估计值。基数根据被存储为整数的统计数据计数,所以即使对于小型表,该值也没有必要是精确的。基数越大,当进行联合时,MySQL 使用该索引的机会就越大。 |
Sub_part | 表示列中被编入索引的字符的数量。若列只是部分被编入索引,则该列的值为被编入索引的字符的数目;若整列被编入索引,则该列的值为 NULL。 |
Packed | 指示关键字如何被压缩。若没有被压缩,值为 NULL。 |
Null | 用于显示索引列中是否包含 NULL。若列含有 NULL,该列的值为 YES。若没有,则该列的值为 NO。 |
Index_type | 显示索引使用的类型和方法(BTREE、FULLTEXT、HASH、RTREE)。 |
Comment | 显示评注。 |
索引失效
使用like
SELECT * FROM employee WHERE `name` LIKE "%孙%";
- 即使我们在name上建立了索引,那么他也是不走索引的
- 模糊匹配中使用了%,mysql还是要全表扫描的
- 对于较大的表,我们尽量避免模糊查询使用%
使用or
- 使用or的时候可能会失效,如果or两边的字段都有索引,那么才会走索引
- 如果or两边只有一个字段有索引,那么久不会走索引
- 所以建议使用
union
来代替使用or
SELECT * FROM department WHERE location = "杭州" OR deptname = "科技部";
当前sql语句中location
有索引,但是deptname
没有索引,那么整个语句都不会走索引,我们可以改成如下的语句
SELECT * FROM department WHERE location = "杭州"
UNION
SELECT * FROM department WHERE deptname = "科技部";
这样location
字段的索引还是有用的
复合索引
当我们查询的时候,如果有如何索引,且用到了符合索引中的字段,这个时候我们就要分情况讨论了
- 使用到了索引最左侧的字段,那么索引生效
- 没有用到索引最左侧的字段,但用到了其他字段,那么索引是不会生效的
创建索引
CREATE INDEX employee_job_sal_index ON employee(job,sal);
查询数据
# 无法使用索引
SELECT * FROM employee WHERE sal = 3000;
# 可以使用索引
SELECT * FROM employee WHERE job = "科员";
索引列参与运算
当索引参与运算的时候,那么索引就会失效
创建索引
CREATE INDEX employee_sal_index ON employee(sal);
查询
# 使用索引
SELECT * FROM employee WHERE sal = 3000;
# 无法使用索引
SELECT * FROM employee WHERE sal + 1 =3000;
索引列使用函数
当对于索引列使用函数的时候,那么查询也是不做索引
SELECT * FROM employee WHERE LOWER(`name`) = "小花";
- 当前name是索引列,但是由于对于name使用了函数,那么他的查询也是不走索引的