mysql索引

种类

1. 主键索引：主键是一种唯一性索引，每个表只能有一个主键，在单表查询中，PRIMARY主键索引与UNIQUE唯一索引的检索效率并没有多大的区别，但在关联查询中，PRIMARY主键索引的检索速度要高于UNIQUE唯一索引。
2. 普通索引：这是最基本的索引类型，而且它没有唯一性之类的限制。
3. 唯一索引：这种索引和前面的“普通索引”基本相同，但有一个区别：索引列的所有值都只能出现一次，即必须唯一。
4. 全文索引：MySql从3.23版开始支持全文索引和全文检索。全文索引只可以在VARCHAR或者TEXT类型的列上创建。
5. 多列索引，遵循最左原则

存储引擎

MyISAM、InnoDB 是使用最广泛的两种MySQL存储引擎。MySQL5.5版本开始Innodb已经成为Mysql的默认引擎(之前是MyISAM)

InnoDB特点：

1. 支持事务处理，支持外键。
   根据索引条件检索数据使用行锁。InnoDB的行锁，只是在WHERE的主键是有效的
2. InnoDB 是聚集索引，聚簇索引的文件存放在主键索引的叶子节点上。意味着使用联合索引查询时，如果查询列和条件列都在索引中，是不用回表的， 降低磁盘io（回表的意思就是每当你索引检索到1个满足条件的就再到表里面去查找符合查询条件的，每一次回表都产生一次随机IO）
3. InnoDB 不保存表的具体行数，执行 select count(*) from table 时需要全表扫描，而MyISAM 用一个变量保存了整个表的行数，执行上述语句时只需要读出该变量即可。
4. 如果你的数据执行大量的INSERT或UPDATE，出于性能方面的考虑，应该使用InnoDB表。
5. 必须有主键的表存在
6. 不支持FULLTEXT类型的全文索引，但是innodb可以使用sphinx插件支持全文索引，并且效果更好。
7. InnoDB 需要更多的内存和存储，它会在主内存中建立其专用的缓冲池用于高速缓冲数据和索引。

MyISAM特点：

1. MyISAM 最小的锁粒度是表锁。一个更新语句会锁住整张表，导致其他查询和更新都会被阻塞，因此并发访问受限。这也是 MySQL 将默认存储引擎从 MyISAM 变成 InnoDB 的重要原因之一
2. 支持全文索引。读和读之间使用共享锁，读写之间使用独占锁。
3. MyISAM 是非聚集索引，数据文件是分离的，索引保存的是数据文件的指针。
4. 如果执行大量的SELECT，MyISAM是更好的选择
5. 允许没有任何索引和主键的表存在
6. 支持 FULLTEXT类型的全文索引
7. MyISAM支持支持三种不同的存储格式：静态表(默认，但是注意数据末尾不能有空格，会被去掉)、动态表、压缩表。当表在创建之后并导入数据之后，不会再进行修改操作，可以使用压缩表，极大的减少磁盘的空间占用。

数据结构

mysql中使用了B+树作为索引结构
B树是多叉树，所以比二叉树的树高更低，B树叶子和非叶子都存数据。而B+树的非叶子节点不存储数据只放索引。因每个存储单元（Page）的大小是有限的，B+树比B树的树高更低。
InnoDB存储引擎也有自己的最小储存单元——页（Page），叶子节点和非叶子节点最小单位都是页。B+树中叶子节点存放数据(叶子节点间指针相连，适用于局部性原理)，非叶子节点存放关键字+指针。
一个页的大小是16K

show global variables like "%innodb_page%"

InnoDB .ibd文件(索引和数据文件)的大小始终是16k的倍数。

磁盘块在mysql中是一个页，页大小是固定的，mysql innodb的默认的页大小是16k，每个索引会分配在页上的数量是由字段的大小决定。当字段值的长度越长，每一页上的数量就会越少，因此在一定数据量的情况下，索引的深度会越深，影响索引的查找效率

联合索引

联合索引又叫复合索引。对于复合索引:Mysql从左到右的使用索引中的字段，一个查询可以只使用索引中的一部份，遵循最左侧原则。复合索引也是只有一个B+树，不过是把列的值符合之后挂在叶子节点上。
例如索引是key index (a,b,c). 支持a | a,b| a,b,c 3种组合进行查找
以下通过例子分析索引的使用情况，以便于更好的理解联合索引的查询方式和使用范围。

1. 多列索引在and查询中应用

   -- 查询效率最高，索引全覆盖。
   select * from test where a=? and b=? and c=?;
   -- 索引覆盖a和b
   select * from test where a=? and b=?;
   -- 经过mysql的查询分析器的优化，索引覆盖a和b。
   select * from test where b=? and a=?;
   -- 索引覆盖a。
   select * from test where a=?;
   -- 没有a，不走索引，索引失效。
   select * from test where b=? and c=?;
   -- 没有a，不走索引，索引失效。
   select * from test where c=?
   

2. 多列索引在范围查询中应用

   -- 索引覆盖a和b，因b是范围查询，因此c不走索引。
   select * from test where a=? and b between ? and ? and c=?;
   -- a走索引，因a是范围查询，因此b不走索引。
   select * from test where a between ? and ? and b=?;
   a走索引，因a是范围查询，b是范围查询也不走索引
   select * from test where a between ? and ? and b between ? and ? and c=?;
   

3. 多列索引在排序中应用

   -- a、b、c三列全覆盖索引
   select * from test where a=? and b=? order by c;
   -- a、b使用索引查找，因b是范围查询，所以c不走索引，会出现file sort
   select * from test where a=? and b between ? and ? order by c;
   

4. 注意
   4.1 联合联合索引的使用在写where条件的顺序无关，mysql查询分析会进行优化而使用索引。但是减轻查询分析器的压力，最好和索引的从左到右的顺序一致。
   4.2 使用等值查询，多列同时查询，索引会一直传递并生效。因此等值查询效率最好。
   4.3 索引查找遵循最左侧原则。但是遇到范围查询列之后的列索引失效。
   4.4 排序也能使用索引，合理使用索引排序，避免出现file sort。

执行计划

explain这个命令来查看一个这些SQL语句的执行计划，查看该SQL语句有没有使用上了索引，有没有做全表扫描，这都可以通过explain命令来查看

select_type

1. SIMPLE(简单SELECT，不使用UNION或子查询等)
2. PRIMARY(子查询中最外层查询，查询中若包含任何复杂的子部分，最外层的select被标记为PRIMARY)
3. UNION(UNION中的第二个或后面的SELECT语句)
4. DEPENDENT UNION(UNION中的第二个或后面的SELECT语句，取决于外面的查询)
5. UNION RESULT(UNION的结果，union语句中第二个select开始后面所有select)
6. SUBQUERY(子查询中的第一个SELECT，结果不依赖于外部查询)
7. DEPENDENT SUBQUERY(子查询中的第一个SELECT，依赖于外部查询)
8. DERIVED(派生表的SELECT, FROM子句的子查询)
9. UNCACHEABLE SUBQUERY(一个子查询的结果不能被缓存，必须重新评估外链接的第一行)

type

ALL、index、range、 ref、eq_ref、const、system、NULL（从左到右，性能从差到好）

1. ALL：Full Table Scan， MySQL将遍历全表以找到匹配的行
2. index: Full Index Scan，index与ALL区别为index类型只遍历索引树
3. range:只检索给定范围的行，使用一个索引来选择行
4. ref: 表示上述表的连接匹配条件，即哪些列或常量被用于查找索引列上的值
5. eq_ref: 类似ref，区别就在使用的索引是唯一索引，对于每个索引键值，表中只有一条记录匹配，简单来说，就是多表连接中使用primary key或者 unique key作为关联条件
6. const、system: 当MySQL对查询某部分进行优化，并转换为一个常量时，使用这些类型访问。如将主键置于where列表中，MySQL就能将该查询转换为一个常量，system是const类型的特例，当查询的表只有一行的情况下，使用system
7. NULL: MySQL在优化过程中分解语句，执行时甚至不用访问表或索引，例如从一个索引列里选取最小值可以通过单独索引查找完成。

possible_keys

指出MySQL能使用哪个索引在表中找到记录，查询涉及到的字段上若存在索引，则该索引将被列出，但不一定被查询使用（该查询可以利用的索引，如果没有任何索引显示 null）

该列完全独立于EXPLAIN输出所示的表的次序。这意味着在possible_keys中的某些键实际上不能按生成的表次序使用。
如果该列是NULL，则没有相关的索引。在这种情况下，可以通过检查WHERE子句看是否它引用某些列或适合索引的列来提高你的查询性能。如果是这样，创造一个适当的索引并且再次用EXPLAIN检查查询

Key

key列显示MySQL实际决定使用的键（索引），必然包含在possible_keys中

如果没有选择索引，键是NULL。要想强制MySQL使用或忽视possible_keys列中的索引，在查询中使用FORCE INDEX、USE INDEX或者IGNORE INDEX。

key_len

表示索引中使用的字节数，可通过该列计算查询中使用的索引的长度（key_len显示的值为索引字段的最大可能长度，并非实际使用长度，即key_len是根据表定义计算而得，不是通过表内检索出的）

不损失精确性的情况下，长度越短越好

ref

列与索引的比较，表示上述表的连接匹配条件，即哪些列或常量被用于查找索引列上的值

rows

估算出结果集行数，表示MySQL根据表统计信息及索引选用情况，估算的找到所需的记录所需要读取的行数

Extra

该列包含MySQL解决查询的详细信息,有以下几种情况：

1. Using where:不用读取表中所有信息，仅通过索引就可以获取所需数据，这发生在对表的全部的请求列都是同一个索引的部分的时候，表示mysql服务器将在存储引擎检索行后再进行过滤
2. Using temporary：MySQL需要使用临时表来存储结果集，常见于排序和分组查询，常见 group by ; order by
3. Using filesort：当Query中包含 order by 操作，而且无法利用索引完成的排序操作称为“文件排序”
4. Using join buffer：获取连接条件时没有使用索引，并且需要连接缓冲区来存储中间结果。如果出现了这个值，那应该注意，根据查询的具体情况可能需要添加索引来改进能。
5. Impossible where：where语句会导致没有符合条件的行（通过收集统计信息不可能存在结果）。
6. Select tables optimized away：仅通过使用索引，优化器可能仅从聚合函数结果中返回一行
7. No tables used：Query语句中使用from dual 或不含任何from子句

在创建索引时，我们尽量做到

1. 为经常需要排序、分组和联合操作的字段建立索引
   3．为常作为查询条件的字段建立索引
   4．在不同值较少的字段上不必要建立索引，如性别字段；
   5．用于联接的列（主健/外健）上建立索引；
   6．复合索引的建立顺序要按照使用的频度来确定，因为联合索引使用最左前缀来索引
   7．很少使用的字段不要建立索引
   9、不推荐在同一列建多个索引
2. 经常更新修改的字段不要建立索引（针对mysql说，因为字段更改同时索引就要重新建立，排序，而Orcale好像是有这样的机制字段值更改了，它不立刻建立索引，排序索引，而是根据更改个数，时间段去做平衡索引这件事的）

一个表支持多少索引

mysql 可支持16个索引，最大索引长度256字节。

关于in

select * from table where id in