MySQL----索引

索引概念

是帮助Mysql高效获取数据的排好序的数据结构，索引存储在文件里，它包含着对数据表里所有记录的引用指针，通过索引可以快速定位数据，就好比书的目录，可以加快数据库查询速度（一般数据库默认会以主键生成索引）

索引类型

mysql的索引分为单列索引(主键索引,唯一索引,普通索引)和组合索引.

• 主键索引（可以将其归为唯一索引，但是主键索引的列不能为NULL）。主键索引会自动创建。
• 唯一索引：索引值要唯一

        CREATE UNIQUE INDEX IndexName ON `TableName`(`字段名`(length));

• 普通索引：没有限制

       CREATE  INDEX IndexName ON `TableName`(`字段名`(length));

• 组合索引：多个限制条件，相当于目录的多级查询，先找某一个再去找某一个

       CREATE INDEX IndexName On `TableName`(`字段名`(length),`字段名`(length),...);

       如果你建立了组合索引（name，age，sex），那么它实际包含的是3个索引 (name) (name,age)(name，age，sex)

        对于select * from tablename where age=15 不会用索引，因为没有age的索引，遵循最左前缀原则

ps：多个单列索引与单个组合索引的查询效果不同，执行查询时，只能用一个索引，会从多个索引中选择一个限制最严格的索引。

按照索引结构分类

• Btree索引：Btree索引是最常使用的索引，默认的基本都是Btree索引
• hash索引：MyISAM不支持hash索引，Innodb是自适应的，会根据表的使用情况自动为表生成哈希索引，不能人为干预是否在一张表中生成哈希索引
• 全文索引：对于全文的一个索引，会分词等；生成全文索引非常耗时以及耗费硬盘，InnoDB并不支持全文索引

        create  fulltext  index  index-content  on article（content）；

索引的优缺点

优点

• 大大加快了数据的查询速度（因为其独特的数据结构B+树，减少了磁盘I/O，因此速度变快）
• 加速了表和表之间的的连接
• 降低了CPU的使用率（索引文件本身就时排好序的（B+树），那么查询排序时，可以直接使用，不再进行排序，也就降低了CPU的使用率）

缺点

• 索引虽然加速了查询速度，但是降低了增删改的效率，因为还需要去修改索引结构
• 建立索引有索引文件存在，占用磁盘空间

那些情况不适合用索引？

并不是所有场景使用索引都适合的，下列情况就是不适合使用索引

• 数据量少的，因为创建和维护索引是很耗费时间的，对于数据量少的本身查询速度也不会很慢，没有必要创建索引，另外索引还需要占磁盘空间。
• 频繁更新的字段，创建索引还需要更新索引
• 很少使用的字段，没有必要为不用的字段去维护索引结构

MySQL索引的优化

• 使用短索引

对串列进行索引，如果可能应该指定一个前缀长度。例如，如果有一个CHAR(255)的列，如果在前10个或20个字符内，多数值是唯一的，那么就不要对整个列进行索引。短索引不仅可以提高查询速度而且可以节省磁盘空间和I/O操作

• 索引不会包含有NULL值的列

只要列中包含有NULL值都将不会被包含在索引中，复合索引中只要有一列含有NULL值，那么这一列对于此复合索引就是无效的。所以我们在数据库设计时不要让字段的默认值为NULL。

• like语句操作

like “%aaa%” 不会使用索引而like “aaa%”可以使用索引，既以%号或者_开头的情况不会使用索引；

MySQL只对以下操作符才使用索引：<,<=,=,>,>=,between,in,以及不以通配符%或_开头的时候的like()。理论上每张表里面最多可创建16个索引，不过除非是数据量真的很多，否则不建议使用过多的索引。

• 尽量的扩展索引，不要新建索引

比如表中已经有a的索引，现在要加(a,b)的索引，那么只需要修改原来的索引即可

• 索引列排序

MySQL查询只使用一个索引，因此如果where子句中已经使用了索引的话，那么order by中的列是不会使用索引的。因此数据库默认排序可以符合要求的情况下不要使用排序操作；尽量不要包含多个列的排序，如果需要最好给这些列创建复合索引。

• 注意类型匹配

如果有类型转换，可能不会使用索引，所以查询的时候注意类型匹配

• 尽量选择区分度高的列作为索引

区分度表示字段不重复的比例，比例越大我们扫描的记录数越少。主键索引和唯一索引的区分度最大，比如我们会选择学号做索引，而不会选择性别来做索引

聚集索引与非聚集索引

对于MyISAM并不支持聚集索引，只支持非聚集索引；但是InnoDB支持聚集索引

每个MyISAM表存储三个文件在磁盘上，一个.FRM文件存储表格式，.MYD存放数据文件，.MYI存放索引文件；但是每个InnoDB表只有两个文件存储在磁盘上，一个.FRM文件存储表格式，另一个是.IBD文件是索引和数据存放在一起的。(因为聚集索引)

MySQL创建表时没有显示的定义主键时，他首先会判断表中是否有非空的整形唯一索引,如果有,则该列为主键。如果没有符合条件的则会自动创建一个6字节的主键(该主键是查不到的).

 

聚集索引：往往是以主键作为索引列的，但也不全是。聚集索引是将索引列与表的数据排列在一起，也即整个表变成了一个索引，一个表只能有一个聚集索引。

事实上，一个没加主键的表，它的数据无序的放置在磁盘存储器上，一行一行的排列的很整齐， 跟认知中的表很接近。但是如果给表上了主键，那么表在磁盘上的存储结构就由整齐排列的结构转变成了树状结构（B+），就是整个表就变成了一个索引。这就是所谓的聚集索引。这就是为什么一个表只能有一个主键， 一个表只能有一个聚集索引，因为主键的作用就是把表的数据格式转换成索引（B+）的格式放置。

非聚集索引：非聚集索引和聚集索引一样， 同样是采用B+树作为索引的数据结构。索引树结构中各节点的值来自于表中的索引字段(不包含表数据)， 假如给user表的name字段加上索引 ， 那么索引就是由name字段中的值构成，在数据改变时， DBMS需要一直维护索引结构的正确性。如果给表中多个字段加上索引 ， 那么就会出现多个独立的索引结构，每个索引（非聚集索引）互相之间不存在关联。（一个表可以有多个非聚集索引）每次给字段建一个新索引， 字段中的数据就会被复制一份出来， 用于生成索引。 因此， 给表添加索引，会增加表的体积， 占用磁盘存储空间。

对于MyISAM不支持聚集索引，所以它的索引文件和数据文件是分离的，不论索引列是否是主键，都是非聚集索引，叶子节点存放的都是磁盘的文件指针，通过这个指针定位到数据文件中找到数据

但是对于InnoDB是支持聚集索引的，所以它的数据文件和索引文件在一起，对于它的非聚集索引其叶子存放的就不是文件指针，而是聚集索引的索引值（主键），那么就可以通过自身索引查找到聚集索引的索引值进而查找到数据。

非聚集索引和聚集索引的区别

 通过聚集索引可以查到需要查找的数据， 而通过非聚集索引可以查到记录对应的主键值（或者文件指针） ， 再使用主键的值（或文件指针）定位到数据。官方区别：聚集索引的逻辑顺序与表中的相应行的物理顺序一致（相当于字典，所以也就只有一个聚集索引），而非聚集索引索引的逻辑顺序与磁盘上行的物理存储顺序不同。

对于MyISAM不论以何种方式查询表，都是用的非聚集索引找到文件指针去定位数据

对于InnoDB不管以任何方式查询表， 最终都会利用主键通过聚集索引来定位到数据， 聚集索引（主键）是通往真实数据所在的唯一路径。

覆盖索引

对于InnoDB有一种例外可以不使用聚集索引就能查询出所需要的数据， 这种非主流的方法 称之为覆盖索引查询， 也就是平时所说的复合索引或者组合索引查询。 文章上面的内容已经指出， 当为字段建立索引以后， 字段中的内容会被同步到索引之中， 如果为一个索引指定两个字段， 那么这个两个字段的内容都会被同步至索引之中。

通过非聚集索引index_birthday_and_name查找birthday等于1993的叶节点的内容，然而， 叶节点中除了有表主键ID的值以外， name字段的值也在里面， 因此不需要通过主键ID值的查找数据行的真实所在， 直接取得叶节点中name的值返回即可。 通过这种覆盖索引直接查找的方式， 可以省略不使用覆盖索引查找的后面两个步骤， 大大的提高了查询性能，即覆盖查询。

ps1：单列索引可以看作索引列为1的联合索引，实际上联合索引就是每个树节点有多个索引值，若在查找的时候匹配到某一个索引值不匹配，后续的则不需要再进行匹配。

ps2:除了覆盖查询，非聚集索引都是要靠聚集索引的，所以聚集索引速度比较快，可以直接定位数据

何时使用聚集索引或非聚集索引？

1：我们可以通过前面聚集索引和非聚集索引的定义的例子来理解上表。如：返回某范围内的数据一项。比如您的某个表有一个时间列，恰好您把聚合索引建立在了该列，这时您查询2004年1月1日至2004年10月1日之间的全部数据时，这个速度就将是很快的，因为您的这本字典正文是按日期进行排序的，聚类索引只需要找到要检索的所有数据中的开头和结尾数据即可（叶节点是链式的指针串起来的）；而不像非聚集索引，必须先查到目录中查到每一项数据对应的页码，然后再根据页码查到具体内容。

2：另外一点，对于频繁更新或者修改的索引列，不适合用聚集索引，因为聚集索引需要按照顺序排列，一旦修改我们要去维护这个顺序，所以不适合用聚集索引，但是可以使用非聚集索引。