Mysql中索引的作用、种类以及在什么情况下会失效

在使用数据库时，经常需要查找特定的数据，比如说在下图中

select * from xxx where id = 10000

Mysql数据库如果没有索引就必须从第1条记录开始遍历，直到找到id为10000的数据，这样的效率非常低。为了解决这一问题，Mysql中允许建立索引来加快数据表的查询和排序。

索引是对数据库表中一列或多列的值进行排序的一种结构，使用索引可快速访问数据库表中的特定信息，这也是索引最基本的功能。

索引的种类

1.主键索引：

create table 表名（
    字段 类型 primary key,

    -- 或者在后面
    primary key (字段名)
）

1>某一个属性组能唯一标识一条记录；2>最常见的索引类型；3>确保数据记录的唯一性； 4>确定特定数据记录在数据库中的位置

2.唯一索引：

唯一性索引是由UNIQUE定义的索引，该索引所在字段的值必须是唯一的。

create table 表名(

    字段 类型 not null UNIQUE,

-- 或者在后面
UNIQUE key(字段名)

)

*1.避免同一个表中某数据列中的值重复；*2.对比主键索引只能有一个,唯一索引可有多个

3.常规索引：

create table 表名(

    字段 类型 not null,

-- 常规索引
    INDEX / KEY (字段名)

)

1>index和key关键字都可设置常规索引；2>不宜添加太多常规索引，影响数据的插入、删除和修改操作

4.全文索引:

全文索引是由FULLTEXT定义的索引，它只能创建在CHAR、VARCHAR或TEXT类型的字段上

-- 语法
create table 表名(

    字段 varchar(10) not null,

-- 全文索引
    FULLTEXT key (字段名、字段名、字段名) with parser ngram

)

-- 用法
select 字段 from 表名 where MATCH(字段)
AGAINST(要搜索的关键词)

1>Mysql 5.6之前版本，只有myisam支持全文索引，5.6之后，Innodb和myisam均支持全文索引。 2>只有char、varchar、text类型字段能创建全文索引。 3>当大量写入数据时，建议先写入数据，后再建立全文索引，提高效率。

索引在什么情况下失效

1、like 以%开头，索引无效；当like前缀没有%，后缀有%时，索引有效。

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

3、组合索引，不满足最左原则，不使用索引

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

5、用or分割条件，若or前后只要有一个列没有索引，就都不会用索引

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

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

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

9、如果列类型是字符串，那一定要在条件中将数据使用引号引用起来，否则不使用索引