MySql中什么是索引？常用的索引有哪些种类？索引在什么情况下会失效？

一、索引的概念：

1、概念：

索引(Index)是对数据库表中一列或者多列的值进行排序的一种结构，使用索引可快速访问数据库表中的特定信息。

2、特点：

①高效性：可以提高数据库的查询效率；

②唯一性：可以确保所查的数据的唯一性；

③完整性：用户可以加速表和表之间的连接，实现表与表之间的参照完整性；

④特殊能力：可以在查询过程中，使用优化隐藏器，提高系统性能。

3、缺点：

①虽然索引大大提高了查询速度，同时却会降低更新表的速度，如对表进行INSERT、 UPDATE和DELETE；

②因为更新表时，MySQL不仅要保存数据，还要保存一下索引文件。建立索引会占用磁盘空间的索引文件；

③如果在一个大表上创建了多种组合索引，索引文件的会膨胀很快。

PS：索引只是提高效率的一个因素，如果你的MySQL有大数据量的表，就需要花时间研究建立

最优秀的索引，或优化查询语句。

二、索引的种类：

1、主键索引：在数据库关系图中为表定义一个主键将自动创建主键索引。

语法：
create table 表名(
   字段1 int(11) auto_increment primary key,
   #或 primary key(字段1)
)

PS：

• 某一个属性组能唯一标识一条记录；

• 最常见的索引类型；

• 确保数据记录的唯一性；

• 确定特定数据记录在数据库中的位置。

 

2、唯一索引：不允许具有索引值相同的行，从而禁止重复的索引或键值。

语法：
create table 表名(
  字段1 int(11) not null unique, 
  #或 UNIQUE KEY(字段1)
)

PS：

• 快速定位特定数据；

• index和key关键字都可设置常规索引；

• 应加在查找条件的字段；

• 不宜添加太多常规索引，影响数据的插入、删除和修改操作。

3、常规索引：最基本的索引类型，唯一索引可有多个。

语法：
create table 表名(
  字段1 int(11) not null,
  index/key(字段1)
)

PS：

• 避免同一个表中某数据列中的值重复；

• 对比主键索引只能有一个,唯一索引可有多个。

4、全文索引：搜索引擎的关键技术，用于检索文本信息，可以是词语或者段落。

语法：
create table 表名(
  字段1 varchar(32) not null,
  fulltext key (字段名，字段名，字段名) with parser ngram
)

用法：
select 字段名 from 表名 
where match(字段名，字段名，字段名) AGAINST (‘要搜索的关键词’)

PS：

• Mysql 5.6 之前版本，只有 myisam 支持全文索引， 5.6 之后， Innodb 和 myisam 均支持全文索引 ；

• 只有 char 、 varchar 、 text 类型字段能创建全文索引 ；

• 当大量写入数据时，建议先写入数据，后再建立全文索引，提高效率 ；

• Mysql 内置 ngram 解析器，可以解析中日韩三国文字。有汉字的一定要启用它 ;

• 英文分词用空格，逗号；中文分词用 ngram_token_size 设定 ；

• 注意需要在配置文件中加入：[mysqld]     ngram_token_size=2

三、索引失效的场景：

1、以%开头的like模糊查询：

2、数据类型出现隐式转化，不会使用索引；

3、不符合最佳左前缀原则，过滤条件要使用索引必须按照索引建立时的顺序，依次满足，一旦跳过某个字段，索引后面的字段都无法被使用；

4、估计使用索引比全表扫描还慢，则不要使用索引；

5、 使用 OR 关键字会导致索引失效，不过如果要想使用OR 又不想让索引失效，那就得需要为or条件中的每个列都建立索引。和上面的不要建立太多的索引相违背；

6 、使用！ = 或 <> 操作符时 ： 尽量避免使用！ = 或 <> 操作符，否则数据库引擎会放弃使用索引而进行全表扫描。使用> 或 < 会比较高效；

7 、对字段进行 null 值判断： 应尽量避免在 where 子句中对字段进行 null 值判断，否则将导致引擎放弃使用索引而进行全表扫描；

8 、避免 select * ： 在解析的过程中，会将 '*' 依次转换成所有的列名，这个工作是通过查询数据字典完成的，这意味着将耗费更多的时间。