MySQL索引

飞___
95 阅读3分钟

建立索引

在建表时创建索引

CREATE TABLE table_test
(
    id    int PRIMARY KEY,
    age   int,
    class varchar(255),
    name  varchar(255),
    INDEX (age),
    UNIQUE (class, name)
);

可以在字段后面跟上索引类型，也可以写在最后。

  • 主键索引：简称主键，PRIMARY KEY，该列数据唯一，非空，一个表只能有一个主键
  • 唯一索引：UNIQUE [ INDEX | KEY] ，数据唯一，可以为空
  • 普通索引：INDEX，数据可以不唯一，也可以空
  • 外键索引：FOREIGN KEY

表建好后，单独创建索引

alter table table_test add index(age);
alter table table_test add unique(class,name);
alter table table_test add primary key(id);

索引结构

在InnoDB存储引擎下，索引为B+树结构，每一个索引都有一个B+树。
对于主键索引，采用聚集索引结构，非叶子节点只包含索引列的数据，叶子节点包含对应行的完整记录。

唯一索引、普通索引又叫做辅助索引，其叶子节点只包含当前记录的索引列数据和主键数据，当使用辅助索引时，先找到对应记录的主键数据，再到主键索引树上找到完整的记录行，这个过程叫做回表。

索引 (1).png

联合索引

由多个列属性共同构建的索引，由创建索引时指定的列顺序进行多级排序来构建索引

alter table table_test add unique(class,name);

如上便创建了由class,name构建的联合索引，且先按class排序，如果class相同再按name排序

联合索引.png

最左前缀原则

最左前缀原则是针对联合索引，因为多级排序是从最左边的列开始排的，所以在使用索引树时也需要按从最左边的列属性开始检索，不能从中间的列开始，那样会不走索引，而是全表遍历。

查看sql的性能可以用EXPLAIN加对应sql语句，其中结果字段type表明使用索引的情况，从最优到最差分别为：system > const > eq_ref > ref > range > index > all

  • all是完全没有用到索引，直接全表扫描
  • index按索引顺序扫描全表，即在叶子节点的链表上遍历
  • range对索引列范围搜索，< > between
  • ref对非唯一索引，或唯一索引的部分索引（联合索引的情况），查询结果可能不唯一

假设有表t2

image.png

创建了如下联合索引

alter table t2 add unique(a,b,c);

如下sql可以利用该索引

EXPLAIN select * from t2 where a=12;
EXPLAIN select * from t2 where a<50;
EXPLAIN select * from t2 where a=12 and b=45;
EXPLAIN select * from t2 where a<50 and b=25;
EXPLAIN select * from t2 where a=12 and b=45 and c=61;
EXPLAIN select * from t2 where a<50 and b=45 and c=61;

image.png

MySQL会对where条件部分顺序优化，sql里的顺序可以随意，只要最左列出现即可，且中间无断档，如下的也可以用索引

select * from t2 where c=61 and b=45 and a=12;

如下sql没有用到索引

EXPLAIN select * from t2 where b=32;
EXPLAIN select * from t2 where b=2 and c=57;
EXPLAIN select * from t2 where c=34;

type都是index的

image.png

还有一些情况是不走索引

  • 对索引列使用函数，表达式计算，隐式类型转换。这些都是在索引上多了一步操作，操作后的数据就不是索引了，导致无法使用索引。
select * from t3 where left(name, 3)='张三';
select * from t3 where id-1=5;
select * from t3 where name=7;
- name是字符串型，匹配时会先转换成整型
  • 前缀模糊搜索
select * from t3 where name like '%三'；
  • or连接两个条件
select * from t3 where name = '张三' or name = '李四'；
  • is null，is not null
select * from t3 where name is null
avatar
文章
阅读
粉丝