MySQL索引类型

一、索引是什么

MySQL官方对索引的定义为：索引(Index)是帮助MySQL高效获取数据的数据结构。可以得出，索引是数据结构，索引的目的是提高MySQL的查询效率。

二、为什么要索引

索引在MySQL中也叫做“键”，是存储引擎用于快速找到记录的一种数据结构。索引对于良好的性能
非常关键，尤其是当表中的数据量越来越大时，索引对于性能的影响愈发重要。
索引优化应该是对查询性能优化最有效的手段了。索引能够轻易将查询性能提高好几个数量级。
索引相当于字典的音序表，如果要查某个字，如果不使用音序表，则需要从几百页中逐页去查。

三、索引的优缺点

1、优点：

• 提高数据检索效率，降低数据库IO成本。

• 通过索引列对数据进行排序，降低数据排序成本，降低了CPU消耗。

2、缺点：

• 一个索引都为对应一棵B+树，树中每一个节点都是一个数据页，一个页默认会占用16KB的存储空间，所以一个索引也是会占用磁盘空间的。

• 大大提高了查询速度，同时却会降低更新表的速度，如对表进行INSERT、UPDATE和DELETE。因为更新表时，MySQL不仅要保存数据，还要保存索引文件。

四、索引的分类及操作

1、普通索引 

又称常规索引，仅加速查询，一张数据表中可以有多个常规索引

--创建普通索引
CREATE INDEX index_name ON table_name(col_name);

--修改表结构的方式添加索引
ALTER TABLE table_name ADD INDEX index_name ON (column(length));

--创建表的时候同时创建索引
CREATE TABLE `table` (
    `id` int(11) NOT NULL AUTO_INCREMENT ,
    `title` char(255) CHARACTER NOT NULL ,
    `content` text CHARACTER NULL ,
    `time` int(10) NULL DEFAULT NULL ,
    PRIMARY KEY (`id`),
    INDEX index_name (title(length))
);

--删除索引
DROP INDEX index_name ON table;

2、唯一索引 （Unique Key）

可以提高查询效率，并提供唯一性约束。一张表中可以有多个唯一索引。

--直接创建索引
CREATE UNIQUE INDEX indexName ON table(column(length));

--修改表结构的方式添加索引
ALTER TABLE table_name ADD UNIQUE indexName ON (column(length));

--创建表的时候同时创建索引
CREATE TABLE `table` (
    `id` int(11) NOT NULL AUTO_INCREMENT ,
    `title` char(255) CHARACTER NOT NULL ,
    `content` text CHARACTER NULL ,
    `time` int(10) NULL DEFAULT NULL ,
    UNIQUE indexName (title(length))
);

3、主键索引（Primary Key）

也简称主键。它可以提高查询效率，并提供唯一性约束。一张表中只能有一个主键。被标志为自动增长的字段一定是主键，但主键不一定是自动增长。一般把主键定义在无意义的字段上（如：编号），主键的数据类型最好是数值。

--随表一起建索引：
CREATE TABLE customer (id INT(10) UNSIGNED  AUTO_INCREMENT ,customer_no VARCHAR(200),customer_name VARCHAR(200),
  PRIMARY KEY(id) 
);

--使用AUTO_INCREMENT关键字的列必须有索引(只要有索引就行)。
CREATE TABLE customer2 (id INT(10) UNSIGNED,customer_no VARCHAR(200),customer_name VARCHAR(200),
  PRIMARY KEY(id) 
);

--单独建主键索引：
ALTER TABLE customer add PRIMARY KEY customer(customer_no);  

--删除主键索引：
ALTER TABLE customer drop PRIMARY KEY ;

4、组合索引

多列值组成一个索引，专门用于组合搜索，其效率大于索引合并。

--创建普通组合索引
CREATE INDEX index_name ON table_name(col_name_1,col_name_2);

--创建唯一组合索引
CREATE UNIQUE INDEX index_name ON table_name(col_name_1,col_name_2);

5、全文索引

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

--创建表的适合添加全文索引
CREATE TABLE `table` (
    `id` int(11) NOT NULL AUTO_INCREMENT ,
    `title` char(255) CHARACTER NOT NULL ,
    `content` text CHARACTER NULL ,
    `time` int(10) NULL DEFAULT NULL ,
    PRIMARY KEY (`id`),
    FULLTEXT (content)
);

--修改表结构添加全文索引
ALTER TABLE article ADD FULLTEXT index_content(content);

--直接创建索引
CREATE FULLTEXT INDEX index_content ON article(content);

五、索引失效的场景

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

2、or语句前后没有同时使用索引。当or左右查询字段只有一个是索引，该索引失效，只有当or左右查询字段均为索引时，才会生效

3、组合索引，不是使用第一列索引，索引失效。

4、在索引字段上使用not，<>，!=。不等于操作符是永远不会用到索引的，因此对它的处理只会产生全表扫描。 优化方法： key<>0 改为 key>0 or key<0。

5、对索引字段进行计算操作、字段上使用函数。

6、当全表扫描速度比索引速度快时，mysql会使用全表扫描，此时索引失效。

---