MySQL索引基本知识

一、索引的优缺点

优点：

1. 减少了服务器扫描的数据量
2. 帮助服务器避免排序和临时表
3. 将随机io变成顺序io

缺点：

1. 虽然索引大大提高了查询速度，同时却会降低更新表的速度，如对表进行insert、update和delete。因为更新表时，不仅要保存数据，还要保存一下索引文件。
2. 建立索引会占用磁盘空间的索引文件。一般情况这个问题不太严重，但如果你在一个大表上创建了多种组合索引，索引文件的会增长很快。

索引只是提高效率的一个因素，如果有大数据量的表，就需要花时间研究建立最优秀的索引，或优化查询语句

二、索引的用处

1. 快速查找匹配WHERE子句的行
2. 从consideration中消除行，如果可以在多个索引之间进行选择，mysql通常会使用找到最少行的索引
3. 如果表具有多列索引，则优化器可以使用索引的任何最左前缀来查找行
4. 当有表连接的时候，从其他表检索行数据
5. 查找特定索引列的min或max值
6. 如果排序或分组时，在可用索引的最左前缀上完成的
7. 在某些情况下，可以优化查询以检索值而无需查询数据行

三、索引的分类

3.1 主键索引

一个表只能有一个主键，不允许有空值。

3.2 唯一索引

索引列的值必须唯一，但允许有空值。

3.3 普通索引

最基本的索引，它没有任何限制。

3.4 组合索引

指多个字段上创建的索引。列值的组合必须唯一。使用组合索引时遵循最左前缀集合。

3.5 全文索引

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

四、索引的数据结构

MyISAM和InnoDB 都是是B+树。枝干上不存数据,叶子节点存数据。
区别在于：MyISAM的叶子结点存的数据是指向数据的指针；Innodb直接存的是数据。

五、索引的技术名词

5.1 聚簇索引与非聚簇索引

这是并不是指的索引，而是两种数据存储方式。

• 聚簇索引：数据文件跟索引文件存放在一起（InnoDB）
• 非簇集索引：数据文件跟索引文件分开存放（MyISAM）

小技巧: 进行数据迁移时，可以先不创建索引，迁移完数据后再建立索引，因为索引可能会随着数据的插入而频繁的更新，降低插入的效率。

聚簇索引

• 优点：
  1. 可以把相关数据保存在一起，索引和数据保存在同一个树中，数据访问更快。
  2. 使用覆盖索引（见下文）扫描的查询可以直接使用页节点中的主键值。
• 缺点：
  1. 如果数据全部在内存，那么聚簇索引就没有什么优势。
  2. 插入速度严重依赖于插入顺序，按照主键的顺序插入是最快的方式。(基于聚簇索引的表在插入新行，或者主键被更新导致需要移动行的时候，可能面临页分裂的问题。页分裂和页合并影响性能)
  3. 更新聚簇索引列的代价很高，因为会强制将每个被更新的行移动到新的位置

  • 页分裂: 假如经过判断,需要在某个索引块中插入数据,如果这个索引块中没有空余位置了,就会页分裂,新增一个索引块,把原先的块中的数据分到两个索引块中(一人一半);页分裂会导致空间的浪费
  • 页合并: 假如删除数据后,判断两个数据页可以合并成一个,就会把他俩合并

5.2 回表

前提：索引不是主键索引（主键索引是聚簇索引，即索引的叶子节点存的是整个单条记录的所有字段值）。
当非主键索引时，叶子结点键会存储主键字段，需要其他列时，根据主键再去查主键的B+树。
回表需要查询两次B+树,IO次数比较多。

5.3 覆盖索引（索引覆盖）

如果建立的普通索引/组合索引,我们查询的时候只需要查询该索引(或者主键),不需要查询其他列，就不需要回表了，这就是索引覆盖。常见于组合索引。

5.4 最左匹配

建立组合索引后,比如对三个列name,age和sex 建立了组合索引 index_name_age_sex。
那么where name=? and age=? and sex=?时肯定可以用到索引；where name=? 或者 where where name=? and age=?时也可以用到索引 (使用多个where条件时，顺序无所谓，MySQL会帮我们优化调整成最左匹配的顺序）。如果达不到最左匹配,比如where age=?,则不会使用该组合索引。

解决方法：

1. 把组合索引调整一下顺序,改成 index_age_name_sex
2. age单独建立一个索引

如果需要在为name和age单独创建索引中 二选一，那么我会选择age；因为age占用磁盘空间更少，每页存储的数据个数就会多，减少磁盘IO。

5.5 索引（条件）下推 Index Condition Pushdown (ICP)

MySQL 5.6添加的，用于优化数据查询：减少存储引擎查询基础表的次数，也可以减少MySQL服务器从存储引擎接收数据的次数。
“下推”：条件判断由server下推到存储引擎。

• 不使用索引条件下推优化时：存储引擎通过索引检索到数据，然后返回给MySQL服务器，然后服务器判断数据是否符合条件。
• 使用索引条件下推优化时：如果存在某些被索引的列的判断条件时，MySQL服务器将这一部分判断条件传递给存储引擎，然后由存储引擎通过判断索引是否符合MySQL服务器传递的条件，只有当索引符合条件时才会将数据检索出来返回给MySQL服务器。

比如：查询sql的过滤条件where name=? and age=?
老版本的MySQL，server第一步会先根据name条件从存储引擎中取出数据，第二步在server层根据age筛选；在组合索引中，高版本MySQL会优化一下，会把第二步合并到第一步，在查询数据时就根据name和age筛选，减少了IO量。

• 不使用ICP技术（过程使用数字符号标示，如①②③等）
  

• 使用ICP技术（过程使用数字符号标示，如①②③等）
  

配置
索引下推优化是默认开启的。可以通过下面的脚本控制开关

SET optimizer_switch = 'index_condition_pushdown=off'; 
SET optimizer_switch = 'index_condition_pushdown=on';

六、索引的匹配方式全值匹配

create table staffs(
    id int primary key auto_increment,
    name varchar(24) not null default '' comment '姓名',
    age int not null default 0 comment '年龄',
    pos varchar(20) not null default '' comment '职位',
    add_time timestamp not null default current_timestamp comment '入职时间'
  ) charset utf8 comment '员工记录表';
-----------
alter table staffs add index idx_nap(name, age, pos);

6.1 全值匹配

指的是和索引中的所有列进行匹配
explain select * from staffs where name = ‘July’ and age = ‘23’ and pos = ‘dev’;

6.2 匹配最左前缀

只匹配前面的几列
explain select * from staffs where name = ‘July’ and age = ‘23’;
explain select * from staffs where name = ‘July’;

6.3 匹配列前缀

可以匹配某一列的值的开头部分
explain select * from staffs where name like ‘J%’;
explain select * from staffs where name like ‘%y’;

6.4 匹配范围值

可以查找某一个范围的数据
explain select * from staffs where name > ‘Mary’;

精确匹配某一列并范围匹配另外一列
可以查询第一列的全部和第二列的部分
explain select * from staffs where name = ‘July’ and age > 25;

6.5 只访问索引的查询

查询的时候只需要访问索引，不需要访问数据行，本质上就是覆盖索引
explain select name,age,pos from staffs where name = ‘July’ and age = 25 and pos = ‘dev’;

七、索引的使用语法

语法

CREATE TABLE table_name

[col_name data type]

[unique|fulltext][index|key][index_name](col_name[length])[asc|desc]

1.unique|fulltext为可选参数，分别表示唯一索引、全文索引
2.index和key为同义词，两者作用相同，用来指定创建索引
3.col_name为需要创建索引的字段列，该列必须从数据表中该定义的多个列中选择
4.index_name指定索引的名称，为可选参数，如果不指定，默认col_name为索引值
5.length为可选参数，表示索引的长度，只有字符串类型的字段才能指定索引长度
6.asc或desc指定升序或降序的索引值存储

普通索引：

（1）直接创建索引

CREATE INDEX index_name ON table(column(length))

（2）修改表结构的方式添加索引

ALTER TABLE table_name ADD INDEX index_name(column(length))

（3）创建表的时候同时创建索引

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))
)

（4）删除索引

DROP INDEX index_name ON table

唯一索引

（1）创建唯一索引

CREATE UNIQUE INDEX indexName ON table(column(length))

（2）修改表结构

ALTER TABLE table_name ADD UNIQUE indexName(column(length))

（3）创建表的时候直接指定

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))
);

主键索引

创建主键索引：

CREATE TABLE `table` (
    `id` int(11) NOT NULL AUTO_INCREMENT ,
    `title` char(255) NOT NULL ,
    PRIMARY KEY (`id`)
);

组合索引

ALTER TABLE `table` ADD INDEX name_city_age (name,city,age); 

全文索引

（1）创建表的适合添加全文索引

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)
);

（2）修改表结构添加全文索引

ALTER TABLE article ADD FULLTEXT index_content(content)

（3）直接创建索引

CREATE FULLTEXT INDEX index_content ON article(content)