MySQL索引总结

一、索引概念

        官方对索引的定义为：索引（Index）是帮助MySQL高效获取数据的数据结构，索引对于良好的性能非常关键，尤其是当表中的数据量越来越大时，索引对于性能的影响愈发重要。索引优化应该是对查询性能优化最有效的手段了。索引能够轻易将查询性能提高好几个数量级。通俗来讲，索引类似文章的目录，用来提高查询的效率。

二、索引作用

• 设置合适的索引之后，数据库利用各种快速定位技术，能够大大加快查询速度，这是创建索引的最主要的原因。
• 当表很大或查询涉及到多个表时，使用索引可以成千上万倍地提高查询速度。
• 可以降低数据库的IO成本，并且索引还可以降低数据库的排序成本。
• 通过创建唯一性索引，可以保证数据表中每一行数据的唯一性。
• 可以加快表与表之间的连接。
• 在使用分组和排序时，可大大减少分组和排序的时间。

        建立索引在搜索和恢复数据库中的数据时能显著提高性能。但是索引需要占用额外的磁盘空间。对于 MyISAM 引擎而言，索引文件和数据文件是分离的，索引文件用于保存数据记录的地址。而 InnoDB 引擎的表数据文件本身就是索引文件。更新一个包含索引的表需要比更新一个没有索引的表花费更多的时间，这是由于索引本身也需要更新，导致性能会相应的有所下降。

三、索引分类

• 从索引存储结构划分：B Tree索引、Hash索引、FULLTEXT全文索引、。
• 从应用层次划分：普通索引、唯一索引、主键索引、复合索引。
• 从索引键值类型划分：主键索引、辅助索引（二级索引）。
• 从数据存储和索引键值逻辑关系划分：聚簇索引、非聚簇索引。

四、MySQL中索引操作

当一张表，把某个列设为主键的时候，则该列就是主键索引。

create table table_demo (  

  `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '主键',

  `name` varchar(20) not null default '' COMMENT '姓名',

  `age` tinyint(4) NOT NULL default 0 COMMENT '年龄',

  `card_no` varchar(30) NOT NULL COMMENT '身份证号',

  `base_info`text default NULL COMMENT '基础信息',

  PRIMARY KEY (`id`)

) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 COMMENT='测试demo';

①主键索引

这里id就是表的主键，如果当创建表时没有指定主键索引，也可以在创建表之后添加：

alter table table_demo add primary key (card_no);

②普通索引

以name为例创建普通索引

CREATE INDEX table_demo_name_IDX USING BTREE ON table_demo (name);

或者

alter table table_demo add index table_demo_name_IDX (name);

③全文索引

全文索引主要针对文本文件，比如文章，标题。在MySQL5.6之前，只有MyISAM存储引擎支持全文索引，MySQL5.6之后InnoDB存储引擎也支持全文索引，以base_info为例创建普通索引。

CREATE FULLTEXT INDEX table_demo_base_info_IDX ON table_demo (base_info);

或者

alter table table_demo add FULLTEXT  index table_demo_base_info_IDX (base_info);

④唯一索引

索引列中的值必须是唯一的，但是允许为空值，相比主键索引，主键字段不能为null，也不能重复， 以card_no为例创建普通索引 。

CREATE UNIQUE INDEX table_demo_card_no_IDX USING BTREE ON table_demo (card_no);

或者

alter table table_demo add UNIQUE index table_demo_card_no_IDX (card_no);

⑤组合索引

用多个列组合构建的索引，以name,age为例创建普通索引。

CREATE INDEX table_demo_name_age_IDX USING BTREE ON table_demo (name,age);

或者

alter table table_demo add index table_demo_name_age_IDX (name,age);

⑥删除索引

ALTER TABLE table_demo DROP INDEX 索引名称;

⑦修改索引

        没有直接修改命令，只能删除重建。

五、索引内部结构

        索引实际上是数据库中满足特定查找算法的数据结构，这些数据结构以某种方式引用（指向）数据，这样就可以在这些数据结构上实现高级查找算法 。

①索引的特性

• MySQL数据库索引采用的是B+Tree结构，在B-Tree结构上做了优化改造。
• 索引值和data数据分布在整棵树结构中。
• 树节点中的多个索引值从左到右升序排列。
• 从根节点开始，对节点内的索引值序列采用二分法查找，如果命中就结束查找。没有命中会进入子节点重复查找过程，直到所对应的的节点指针为空，或已经是叶子节点了才结束。
• 非叶子节点不存储data数据，只存储索引值，这样便于存储更多的索引值。
• 叶子节点包含了所有的索引值和data数据。
• 叶子节点用指针连接，提高区间的访问性能。
• 相比B树，B+树进行范围查找时，只需要查找定位两个节点的索引值，然后利用叶子节点的指针进行遍历即可。而B树需要遍历范围内所有的节点和数据，显然B+Tree效率高。

②名词介绍

•  回表查询 

        InnoDB索引有聚簇索引和辅助索引。聚簇索引的叶子节点存储行记录，InnoDB必须要有，且只有一个。辅助索引的叶子节点存储的是主键值和索引字段值，通过辅助索引无法直接定位行记录，通常情况下，需要扫码两遍索引树。先通过辅助索引定位主键值，然后再通过聚簇索引定位行记录，这就叫做回表查询。

•  覆盖索引 

      在一棵索引树上就能获取SQL所需的所有列数据，无需回表，速度更快，这就叫做索引覆盖。实现索引覆盖最常见的方法就是：将被查询的字段，建立到组合索引。

•  左前缀原则 

复合索引使用时遵循最左前缀原则，最左前缀顾名思义，就是最左优先，即查询中使用到最左边的列，那么查询就会使用到索引，如果从索引的第二列开始查找，索引将失效。

•  索引下推