索引

索引能提高查询效率,但是查询效率和修改效率一直是一个矛盾。

普通索引

-- 单独创建,colname为列名
CREATE INDEX indexname ON tablename(colname(length));
-- 也可以在修改表的时候创建
ALTER TABLE table_name ADD INDEX [index_name] (column_list)
-- 创建表的时候指定
CREATE TABLE mytable(
trueID INT NOT NULL,
username VARCHAR(16) NOT NULL,
INDEX [indexname] (username(length)
);

唯一索引

索引列必须唯一,但可以为 NULL,不能有多个 NULL。

-- 单独创建
CREATE UNIQUE INDEX indexname ON tablename(colname(length));
-- 也可以在修改表的时候创建
ALTER TABLE table_name ADD UNIQUE [index_name] (column_list)
-- 创建表的时候指定
CREATE TABLE mytable(
trueID INT NOT NULL,
username VARCHAR(16) NOT NULL,
UNIQUE INDEX [indexname] (username(length)
);

主键索引

就是之前所说过的主键。不能使用CREATE语句单独创建。

-- 可以在修改表的时候创建
ALTER TABLE tablename ADD PRIMARY KEY (column_list);
-- 在表级定义主键
CREATE TABLE mytable(
trueID INT NOT NULL,
username VARCHAR(16) NOT NULL,
PRIMARY KEY(ID)
);
-- 在列级定义主键
CREATE TABLE mytable(
trueID INT NOT NULL PRIMARY KEY,
username VARCHAR(16) NOT NULL
);

由上可知,有两种主键定义方法。但是两者不尽相同。在列级定义主键这样是无法指定多个字段做主键的,这中情况下只能在表级定义主键

索引组织表

InnoDB 引擎中,表都是根据主键顺序组织存放的。称之为索引组织表
在没有显示地声明主键的情况下,InnoDB 按照如下方式选择或创建主键:

  • 如果存在非空唯一约束,则将该列视为主键
  • 如果不存在,则自动创建一个 6 字节大小的指针

如果表中有多个非空的唯一索引,那么 InnoDB 选择第一个定义的非空索引。这里依据的顺序是索引定义的顺序,而不是建表时列的顺序。

_rowid

在主键为单个列的时候,我们可以使用_rowid 来查看主键的值。

-- 比如:
SELECT *,_rowid FROM test;

全文索引

只有 InnodB 引擎支持