mysql系列——索引基础（九）

什么是索引？

索引是对数据库中一列或多列的值进行排序的一种数据结构，可以快速的查询数据库中的特定信息（加速检索数据库中表的数据）。

索引的分类及详解：

1.普通索引  2.唯一索引  3.主键索引 （前三个为单列索引）4.组合索引  5.全文索引

普通索引(index)：  mysql的基本索引类型，允许在定义索引的列中插入重复值和空值

create index [索引名] on [表名]([列名] [(length（可以不指定）)])
或
alter table [表名] add index [索引名]([列名])
 
可以在建表时指定索引
create table [表名](
    id int not null,
    name varchar(10) not null,
    index [索引名] ([列名](length))
)

如果是char、varchar类型，length长度可以小于字段的实际长度，也可以不指定；如果是BLOB、TEXT类型，必须指定length　

唯一索引(unique index)：  索引列的值必须唯一的，但允许存在空值。

创建唯一索引与普通索引基本相同，普通索引是index， 唯一索引为unique index

create unique index [索引名] on [表名]([列名] [(length（可以不指定）)])
或
alter table [表名] add unique index [索引名]([列名])
 
可以在建表时指定索引
create table [表名](
    id int not null,
    name varchar(10) not null,
    unique [索引名] ([列名](length))
)

主键索引(primary key)： 特殊的唯一索引，不允许有空值。主键索引可以不指定索引名，因为一个表中只能有一个主键索引，但可以是多个列组成一个关联主键。

 alter table [表名] add primary key ([列名])

组合索引(index)：在表中多个字段组合上创建索引，只有在查询条件中使用了这些字段的左边字段时，索引才会被使用。创建一个组合索引实际上是创建了多个普通索引。　　

create index [indexName] on [tableName]([column1, column2, column3])
或
alter table [tableName] add index [indexName]([column1, column2, column3])
 
可以在建表时指定索引
create table [表名](
    id int not null,
    name varchar(10) not null,
    index [索引名] ([column1, column2, column3])
)

如上面创建的组合索引，实际上是创建了三个索引：column1，column2、column3 。组合索引遵从最左前缀，利用索引中最左边的列集来进行匹配，这样的列集称为最左前缀。如在上面建立的组合索引中，如果在查询中使用了column1、（column1,column2）、（column1,,column2,column3）中的一个 作为查询条件一般就会使用索引。

使用组合索引需要注意：选择合适的索引列顺序很重要，索引列顺序意味着索引首先按照最左列进行排序，其次是第二列，等等。对于索引列顺序有一个经验法则----- 将选择性最高的列放到索引最前列。但这并不适用于所有场景，还要根据具体场景进行分析和创建索引。

全文索引（fulltext index）：MyISAM引擎上才能使用，但在mysql5.6及之后版本，InnoDB引擎也支持全文索引。全文索引只能在char、varchar、text字段上使用。

全文索引是指在一堆的文字中，通过使用某个关键字进行搜索字段所属的记录行，这里有点类似于es中的分词、模糊查询。但是mysql的全文索引对文本字段的搜索，只能搜索一些生僻的关键词进行搜索，一些常用的关键词可能不会搜索到结果。

使用全文索引需要借助MATCH函数

select * from work_order where MATCH(info) AGAINS('workOrder') ;

什么情况适合使用索引？

　　（1）主键自动建立唯一索引

　　（2）频繁作为查询条件的字段应该创建索引

　　（3）查询中排序的字段，排序字段若通过索引去访问将大大提高排序速度

　　（4）查询中统计或者分组字段。

　　（5）数据区分度不大的字段不宜使用索引 比如性别字段sex。

索引失效的情况？

1.如果条件中有or，即使其中有条件带索引也不会使用，注意：要想使用or，又想让索引生效，只能将or条件中的每个列都加上索引

 2.对于多列索引，不遵循最左前缀原则，则不会使用索引

3.like查询是以%开头

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

5.在属性上计算不能命中索引

select id from t_user where year(data)<='2017';即使在data上创建索引也会全表扫描，可以优化为 select id from t_user where data<=curdate();

6.负向查询不能使用索引

select * from t_user where age!=10 可以优化为in查询 select *from t_user where age in(11,12);

7.如果mysql估计使用全表扫描要比使用索引快,则不使用索引

补充：

查看索引的使用情况：show status like 'Handler_read%';

handler_read_key:这个值越高越好，表示使用索引查询到的次数

handler_read_rnd_next:这个值越高，说明查询低效