MySQL 索引

https://www.cnblogs.com/luyucheng/p/6289714.html 原文链接

MySQL 索引类型主要有以下几种类型

• 普通索引
• 主键索引
• 唯一索引
• 组合索引
• 全文索引

普通索引
它是最基本的索引，没有什么其他的限制，它的创建方式有以下几种

• 直接创建索引

create index index_name on table(col_name [length])

length 是可选参数 , 只有字符串类型的字段才能设置长度, 选择合适的 length 不仅可以提高 索引的查询效率，还能提高 insert update delete 的速度。

• 通过修改表的方式来创建

alter table table_name add index index_name on(col_name)

• 创建表的时候同时创建索引

create table ‘table_name’(
	...
	index index_name(col_name[length])
)

删除索引

drop index index_name on table 

唯一索引
唯一索引和前面的普通索引类似，不同的是索引列的值必须唯一，允许有空值，如果是组合索引，则列值得组合必须唯一，唯一索引的创建方式有：

• 直接创建

create unique index index_name on table （col_name[length]）

• 修改表结构的时候创建

alter table table_name add unique index_name on ( col_name [length] )

• 创建表的时候直接指定

create table table_name(
	...
	unique index_name(col_name[length])
)

主键索引
是一中特殊的唯一索引，一个表只能有一个主键，不允许有空值，一般是在建表的时候同时创建主键索引，这个都很熟悉了，不做过多的解释。

组合索引
在多个字段上创建的索引，只有在查询的时候使用了创建索引时的第一个字段，索引才会被使用，使用组合索引遵循最左前缀集合

alter table table_name add index colA_colB_colC(colA,colB,colC);

对于上面的组合索引，安装最左前缀原则，只有下面的查询 语句才会走这个组合索引

select * from table_name where colA= ? and colB=?
select * from table_name where colA= ? and colC=?
select * from table_name where colA= ? 

全文索引
主要用来查找文本中的关键字，而不是直接与索引总的值相比较， fulltext 索引根其他索引大不相同，它更像是一个搜索引擎，而不是简单的 where语句的参数匹配， fulltext 索引配合 match against 操作使用，而不是一般的 where 语句 加 like ，它可以在 create table ， alter table，create index 时使用，不过目前只有 char 、varchar、text 列上可以创建 全文索引。值得说明的是，在数据量较大的时候，现将数据放入一个没有全局索引的表中，然后再用 create index 创建 fulltext 索引，要比先为表创建 fulltext 然后再讲数据写入的速度快的多。

• 创建表的时候添加 全文索引

create table table_name ('
	...
	fulltext (col_name)
)

• 修改表的时候添加

alter table table_name add fulltext index_name(col_name)

• 直接创建全文索引

create fulltext index index_name on table_name(col_name)

使用索引的缺点是什么？

• 可以提高查询的效率，但是也会影响 insert update 等速度，因为毕竟要保存数据，还要保存或者修改 索引文件

使用索引的注意事项

• 索引不会包含含有 null 值得列
  只要列中包含 null 值都将不会被索引包含，组合索引中如果有一列为 null ，那么这一列对于此符合索引就是无效的。

• 使用短索引
  对字符串进行索引时，如果可能就应该设置一个前缀的长度 col_name(length), 例如 一个 char（255） 的列，如果在前10 或者 20 个字符内，多数值是唯一的，就不要对整个列进行索引。短索引不仅可以提高查询的速度而且可以节省磁盘空间和IO 操作

• 索引列排序
  查询只使用一个索引，如果where 字句中已经使用了索引的话，那么 order by 中的列是不会使用索引的。尽量不要包含多个列的排序，如果需要最后创建组合索引

• like 语句
  like “%xxx%” 索引无效 like “xxx%” 索引有效

• 不要在列上进行计算
  这将会使得索引失效而进行全表扫描

• 不要使用 not in 和 <> 操作

• or 操作对 索引无效