MySQL索引机制
永远年轻,永远热泪盈眶
一.索引的类型与常见的操作
-
前缀索引 MySQL 前缀索引能有效减小索引文件的大小,提高索引的速度。但是前缀索引也有它的坏处:MySQL 不能在 ORDER BY 或 GROUP BY 中使用前缀索引,也不能把它们用作覆盖索引(Covering Index)。
-
复合索引 集一个索引包含多个列(最左前缀匹配原则)
-
唯一索引 索引列的值必须唯一,但允许有空值
-
全文索引 在MySQL 5.6版本以前,只有MyISAM存储引擎支持全文引擎.在5.6版本中,InnoDB加入了对全文索引的支持,但是不支持中文全文索引.在5.7.6版本,MySQL内置了ngram全文解析器,用来支持亚洲语种的分词. 全文索引为FUllText,在定义索引的列上支持值的全文查找,允许在这些索引列中插入重复值和空值,全文索引可以在CHAR,VARCHAR,TEXT类型列上创建
-
主键索引 设定主键后数据会自动建立索引,InnoDB为聚簇索引
-
单列索引 即一个索引只包含单个列,一个表可以有多个单列索引
-
覆盖索引 覆盖索引是指一个查询语句的执行只用从所有就能够得到,不必从数据表中读取,覆盖索引不是索引树,是一个结果,当一条查询语句符合覆盖索引条件时候,MySQL只需要通过索引就可以返回查询所需要的数据,这样避免了查到索引后的回表操作,减少了I/O效率
-- 目前有一个key(name)索引,聚簇索引是key(id) |
|
-- 使用了覆盖索引 |
|
select id from stu where key = '天天'; |
|
-- 不使用覆盖索引,因为查询的结果无法从普通索引树中得到 |
|
select * from stu where key = '天天' |
查看索引
show index from table_name; |
编辑切换为居中
添加图片注释,不超过 140 字(可选)
列名解析:
列名title |
解释desc |
取值value |
---|
删除索引
drop index index_name on table name; |
|
-- 错误删除primary索引 |
|
drop index `PriMary` on temp; |
|
-- >:Incorrect table definition; there can be only one auto column and it must be defined as a key |
二.常见的索引详解与创建
-
主键索引
-- mysql中InnoDB使用主键索引作为聚簇索引,主键索引无法使用 |
|
-- 创建时候,主键自动定义 |
|
create table temppp(id int auto_increment,primary key(id),name varchar(20) not null unique); |
|
-- 无法删除primary key索引,需要改变的时候,首先需要删除主键列,删除后自动选择一行unique的列作为主键索引 |
|
alter table temppp drop COLUMN id; |
查看:
编辑切换为居中
添加图片注释,不超过 140 字(可选)
删除前:
编辑切换为居中
添加图片注释,不超过 140 字(可选)
删除后: