详解MySql索引

目录

一 、概念

二、使用场景 

三、索引使用 

四、索引存在问题

五、命中索引问题

六、索引执行原理 

---

一 、概念

索引是一种特殊的文件，包含着对数据表里所有记录的引用指针。暂时可以理解成C语言的指针,文章后面详解

二、使用场景 

• 数据量较大，且经常对这些列进行条件查询。
• 该数据库表的插入操作，及对这些列的修改操作频率较低。
• 索引会占用额外的磁盘空间。

三、索引使用 

创建主键约束（ PRIMARY KEY ）、唯一约束（ UNIQUE ）、外键约束（ FOREIGN KEY ）时，会自动创建 对应列的索引。

• 查看索引

show index from 表名;

• 创建索引

create index 索引名 on 表名(字段名);

• 删除索引

drop index 索引名 on 表名;

四、索引存在问题

• 索引也会占用一些内存，在表数据量越大越明显
• 索引是可以提高查询速度（前提是要命中索引，后面有解释命中索引），但是可能会拖慢增删改速度。
• 后续如果对数据进行了增删改都要同步索引。

五、命中索引问题

索引命中规则详解：t这张表 a,b,c 三个字段组成组合索引select * from t where a=? and b=? and c=?  全命中select * from t where c=? and b=? and a=?  全命中 解析MySQL的查询优化器会自动调整where子句的条件顺序以使用适合的索引select * from t where a=?  命中a  解析:最左前缀匹配select * from t where a=? and b=?  命中a和b  解析:最左前缀匹配select * from t where a=? or b=?  一个没命中 解析or无法命中select * from t where a=? and c=?  命中a 解析:最左前缀匹配，中间没有则无法使用索引select * from t where a=? and b in ( x, y, z) and c=?  全部命中 in精确匹配可以使用索引select * from t where b=?  一个没命中  解析:最左前缀匹配原则select * from t where b=? and c=?  一个没命中  解析:最左前缀匹配原则select * from t where a=? and b like 'xxx%'   命中a和bselect * from t where a=? and b like '%xxx'  命中aselect * from t where a<? and b=?   命中a 解析这个是范围查找select * from t where a between ? and ?  and b=?  命中a和b 解析BETWEEN相当于in操作是精确匹配select * from t where a between ? and ?  and b=? and c  and between ? and ?    全部命解析中同上select * from where a-1=?   函数和表达式无法命中索引

 

六、索引执行原理 

准备测试表

-- 创建用户表
DROP TABLE IF EXISTS test_user;
CREATE TABLE test_user (id_number INT,name VARCHAR(20) comment '姓名'
,age INT comment '年龄'
,create_time timestamp comment '创建日期'
);

不加索引情况，要是查询大量数据可能死机 ：

select * from test_user where id_number=556677;

为提供查询速度，创建 id_number 字段的索引：

create index idx_test_user_id_number on test_user(id_number);

换一个身份证号查询，并比较执行时间：

select * from test_user where id_number=776655;

注意：我们可以看到我们如果查询的是主键，那么索引的值就是我们想要找到的值，如果我们想要获取非主键的值，我们必须根据找到的主键id去原来表中找到非主键，这种操作叫做回表