创建方式
CREATE
CREATE INDEX indexName ON tableName (columnName(length) [ASC|DESC]);
参数含义
indexName
当前创建的索引,创建成功后叫啥名字
tableName
要在哪张表上创建一个索引,这里指定表名
columnName
要为表中的哪个字段创建索引,这里指定字段名
length
如果字段存储的值过长,选用值的前多少个字符创建索引
同比前缀索引
ASC|DESC
指定索引的排序方式,ASC是升序,DESC是降序,默认ASC
ALTER
ALTER TABLE tableName ADD INDEX indexName(columnName(length) [ASC|DESC]);
DDL
CREATE TABLE tableName(
columnName1 INT(8) NOT NULL,
columnName2 ....,
.....,
INDEX [indexName] (columnName(length))
);
创建表就确定索引
查询/删除/指定
查看索引
SHOW INDEX FROM tableName;
这条命令在后续排除问题、性能调优时,会有不小的作用,比如可以通过分析其中的Cardinality字段值,如果该值少于数据的实际行数,那目前索引有可能 失效
参数含义
Table
当前索引属于那张表
Non_unique
目前索引是否属于唯一索引,0代表是的,1代表不是
Key_name
当前索引的名字
Seq_in_index
如果当前是联合索引,目前字段在联合索引中排第几个
Column_name
当前索引是位于哪个字段上建立的
Collation
字段值以什么方式存储在索引中,A表示有序存储,NULL表无序
Cardinality
当前索引的散列程度,也就是索引中存储了多少个不同的值
Sub_part
当前索引使用了字段值的多少个字符建立,NULL表示全部
Packed
表示索引在存储字段值时,以什么方式压缩,NULL表示未压缩
Null
当前作为索引字段的值中,是否存在NULL值,YES表示存在
Index_type
当前索引的结构(BTREE, FULLTEXT, HASH, RTREE)
Comment
创建索引时,是否对索引有备注信息
删除索引
在MySQL中并未提供修改索引的命令,也就说当你建错了索引,只能先删再重新建立一次,删除索引的语句如下
DROP INDEX indexName ON tableName
//指定索引
SELECT * FROM table_name FORCE INDEX(index_name) WHERE .....;
FORCE INDEX关键字可以为一条查询语句强制指定走哪个索引查询,但要牢记的是:如果当前的查询SQL压根不会走指定的索引字段,哪这种方式是行不通的,这个关键字的用法是:一条查询语句在有多个索引可以检索数据时,显式指定一个索引,减少优化器选择索引的耗时。
索引分类
数据结构
-
B+Tree类型
MySQL中最常用的索引结构,大部分引擎支持,有序
-
Hash类型
大部分存储引擎都支持,字段值不重复的情况下查询最快,无序
-
R-Tree类型
MyISAM引擎支持,也就是空间索引的默认结构类型
-
T-Tree类型
NDB-Cluster引擎支持,主要用于MySQL-Cluster服务中
单列索引
-
唯一索引
指索引中的索引节点值不允许重复,一般配合唯一约束使用
-
主键索引
主键索引是一种特殊的唯一索引,和普通唯一索引的区别在于不允许有空值
-
普通索引
通过KEY、INDEX关键字创建的索引就是这个类型,没啥限制,单纯的可以让查询快一点 .....还有很多很多,只要是基于单个字段建立的索引都可以被称为单列索引。
多列索引
组合索引、联合索引、复合索引、多值索引.... 由多个字段组合建立的索引 不过在使用多列索引时要注意:当建立多列索引后,一条SELECT语句,只有当查询条件中了包含了多列索引的第一个字段时,才能使用多列索引
功能逻辑划分
普通索引
唯一索引
UNIQUE
CREATE UNIQUE INDEX indexName ON tableName (columnName(length));
ALTER TABLE tableName ADD UNIQUE INDEX indexName(columnName);
CREATE TABLE tableName(
columnName1 INT(8) NOT NULL,
columnName2 ....,
.....,
UNIQUE INDEX [indexName] (columnName(length))
);
- 主键索引
PRIMARY
ALTER TABLE tableName ADD PRIMARY KEY indexName(columnName);
- 全文索引
FULLTEXT
ALTER TABLE tableName ADD FULLTEXT INDEX indexName(columnName);
CREATE FULLTEXT INDEX indexName ON tableName(columnName);
-
注意点
5.6版本的MySQL中,存储引擎必须为MyISAM才能创建 创建全文索引的字段,其类型必须要为CHAR、VARCHAR、TEXT等文本类型 如果想要创建出的全文索引支持中文,需要在最后指定解析器:with parser ngram
空间索引
存储方式
-
聚簇索引
也被称为聚集索引、簇类索引 子主题 2
-
非聚簇索引
也叫非聚集索引、非簇类索引、二级索引、辅助索引、次级索引 子主题 2
索引本质
索引也会在磁盘生成本地文件
因此不同存储引擎下的索引文件,其保存在本地的格式也并不相同