MySQL中最基本的索引类型,没有限制,允许重复,允许NULL
CREATE INDEX index_code ON tb_student(code);
唯一索引跟普通索引类似,唯一索引列的值必须唯一,允许有空值。
如果是组合索引,则列值的组合必须唯一。
创建唯一索引通常使用 UNIQUE 关键字,例如电话号码必须唯一,创建唯一索引
CREATE INDEX index_phone_number ON tb_student(phone_number);
主键索引其实也是唯一索引的一种,但是不允许值重复或者值为NULL。必须使用PRIMARY KEY,在创建表结构的时候创建,不能单独用CREATE 的方式创建,主键一般都是自增ID
全文索引主要用来查找文本中的关键字,只能在 CHAR、VARCHAR 或 TEXT 类型的列上创建。在 MySQL 中只有 MyISAM 存储引擎支持全文索引。
全文索引允许在索引列中插入重复值和NULL。
不过对于大容量的数据表,生成全文索引非常消耗时间和硬盘空间。
创建全文索引使用 FULLTEXT 关键字。
CREATE FULLTEXT INDEX index_name ON tb_student(name);
例如给日志表创建了3个联合索引,分别是
operate_type操作类型,
operate_user操作人,
operate_status操作状态
CREATE INDEX index_type_user_status ON eom_log(operate_type,operate_user,operate_status);
下面我们就来例如explain来看看是否命中索引
explain select * from eom_log where operate_type < 10
先来查一个字段,operate_type,结果如下,命中索引
再加一个字段,执行的结果也是命中索引
explain select * from eom_log where operate_type < 10 and operate_user < 10
那么如果我们查最右边的那个字段,会发生什么呢?
explain select * from eom_log where operate_status < 10
很遗憾,没有走索引,
同意的道理,只查询中间字段,也是不走索引的
explain select * from eom_log where operate_user < 10
所以在多个字段组成的联合索引中,where条件中必须要从最左边的第一个字段开始匹配,如果没有就无法命中索引
那么问题又来了,如果只用第一个和第三个 字段当条件能命中索引吗?我们来试试
explain select * from eom_log where operate_type < 10 and operate_status < 10
结果显示命中索引,所以只要where条件中包含最左边的字段,那么一定是命中
那么问题又来了,如果把这3个字段的顺序颠倒一下,能命中索引吗?
explain select * from eom_log where operate_user < 10 and operate_status < 10 and operate_type < 10
结果显示命中索引,这里可能会有小伙伴问,不是最左匹配吗,其实这是mysql的优化器的功劳,优化器会判断纠正这条sql语句该以什么样的顺序执行效率最高,他会排列where的查询顺序,使他能够命中索引,最后才生成真正的执行计划。所以mysql查询优化器会最终以这种顺序进行查询执行。
都属于B + tree的所有结构,那么他们的区别是什么呢?最大的区别就是存储方式的不同
优点
缺点
覆盖索引就是SQL只需要通过索引就可以返回查询所需要的数据,而不必通过二级索引查到主键之后再去查询数据。说白了,就是你的查询走到了索引,并且需要返回的数据刚好是索引的组成字段,不需要回表就能得到想要的结果;
简单粗暴理解:如果一个索引包含了(或覆盖了)满足查询语句中字段与条件的数据就叫做覆盖索引
看看例子,有个学生表student,里面有个联合索引学号和电话号码index_code_phone
SELECT code,phone from tb_user where code = 'a' and phone = 'b'
那么这个要查询的code 和phone 正好索引,那么查询的时候就直接能够返回数据,不需要走二次查询,这就是覆盖索引