索引的分类及创建索引的语法_建索引语句

索引的分类：

索引的类型和添加语法：

• 唯一索引（unique key）：为了避免数据重复出现

  # 下面在 tb_student 表中的 id 字段上建立名为 index_id 的索引，SQL语句如下：
  # 使用CREAT创建索引：
  CREATE UNIQUE INDEX index_id ON tb_student(id);
  # 使用ALTER创建索引:
  ALTER TABLE `tb_student` ADD UNIQUE [index_id](`id`);
  1
  2
  3
  4
  5

• 主键索引（primary key）：唯一且非空，只能有一个列作为主键

  通常使用primary key关键字，不能使用create index语句创建主键索引

  # 在tb_student表中的id字段创建名为index_id的主键索引,sql如下：
  # 使用ALTER创建索引：
  ALTER TABLE `tb_student` ADD PRIMARY KEY [index_id](`id`);
  1
  2
  3

• 普通索引（key /index）：加快了对数据的访问速度

  # 下面在 tb_student 表中的 id 字段上建立名为index_id 的索引，SQL语句如下
  # 使用CREAT创建索引：
  CREATE INDEX index_id ON tb_student(id);
  # 使用ALTER创建索引：
  ALTER TABLE `tb_student` ADD INDEX [index_id](`id`);
  1
  2
  3
  4
  5

• 组合索引：两个或者多个列上的索引被称作组合索引，又称做联合索引

  # 在 tb_student 表中的column1,column2,...等列上面创建联合索引：sql如下
  CREATE INDEX index_name ON tb_student(column1,column2,...)
  # 还可以添加成为组合要求的唯一索引
  CREATE UNIQUE INDEX index_name ON tb_studentb(column1,column2,...)
  1
  2
  3
  4

• 前缀索引：

  # 在 table_name 表中的column_name列上面添加前缀长度为length,引索名为title_pre的前缀索引：sql如下
  # 使用CREATE创建索引
  CREATE INDEX index_name ON tb_student(column1(length));
  # 使用ALERT创建索引
  ALTER TABLE table_name ADD INDEX title_pre(column_name(length));
  1
  2
  3
  4
  5

• 全文索引：对文本的内容进行搜索（主要用来查找文本中的关键字，只能在char，varchar或者text类型的列上创建）全文索引允许在列中插入重复值和空值

  # 在tb_student表中的info字段上建立名为index_info的全文索引，sql如下：
  # 使用CREATE创建索引：
  CREATE FULLTEXT INDEX index_info ON tb_student(info);
  # 使用ALTER创建全文索引：
  ALTER TABLE `tb_student` ADD FULLTEXT [index_info](`info`);
  1
  2
  3
  4
  5

• 空间索引：对空间数据类型的字段建立的索引，MYSQL中的空间数据类型有四种：GEOMETRY（几何），POINT（点），LINESTRING（线），POLYGON（面）

删除索引的方式：

# 使用ALERT删除：（删除表table_name中的名为index_name的索引）
ALTER TABLE table_name DROP INDEX index_name;
# 使用DROP删除： （删除表table_name中的名为index_name的索引）
DROP INDEX index_name ON table_name;
1
2
3
4

创建索引的方式：

• create index语句：

  # 添加普通索引
  CREATE INDEX index_name ON table_name(id);
  1
  2

• create table语句：创建表的时候直接创建索引

  # 在建表的时候添加索引
  CREATE TABLE table_name(
      属性名 数据类型[完整约束条件]
      属性名 数据类型[完整约束条件],
  	...
      [ UNIQUE | FULLTEXT | SPATIAL ] INDEX | KEY [indexname](属性名(length))
  )
  1
  2
  3
  4
  5
  6
  7

• alter table语句

  # 添加普通索引
  ALTER TABLE table_name ADD INDEX index_name(column)
  1
  2

ALTER和CREATE创建索引的区别：

• CREATE INDEX 必须提供索引名，对于ALTER TABLE，如果你不提供索引名，将自动创建。

• CREATE INDEX一个语句只能创建一个索引，ALTER TABLE可以创建多个

  • ALTER TABLE table_name ADD INDEX(column),ADD UNIQUE(column);
    1

• 创建主键只能使用ALTER TABLE

索引方法：

b-tree

hash

r-tree

常用索引优化策略：

• 覆盖索引：查询的列要被所建的索引覆盖
• 最左前缀匹配：联合索引中必须遵循”最左前缀匹配“，sql查询where条件字段必须从索引的最左前列开始匹配，不能跳过索引中的列。否者的话没法使用到索引
• 范围查询字段放在最后：联合索引，范围查询后，后面的字段不走索引
• 不对索引字段进行逻辑操作：在索引字段上进行计算、函数、类型转换（自动\手动）都会导致索引失效。
• 尽量全值匹配：使用like会使查询效率降低
• like查询，左侧尽量不要加%：like以%开头，当前列索引无效（当为联合索引时，当前列和后续列索引不生效，导致索引使用不充分）；当like前缀没有%，后缀有%时，索引有效。
• 尽量避免null：字段定义默认为null时，null索引生效，not null索引列索引均失效；字段定义为not null ,不允许为空时，null/not null索引列均失效。
• 尽量减少使用不等于：不等于操作符是不会使用引索的，不等于操作符包括：not，<>，!=。优化方法：数值型key<>改为key>0 or key <0.
• 字符类型务必加上单引号：varchar类型字段值不加单引号可能会发生数据隐式转化，自动转化为int型，使索引无效
• or关键字前后尽量都为索引列：当or左右查询字段只有一个是索引时，会使索引失效。

参考：
MySQL 索引优化的 10 个策略