MySQL 索引

为什么要使用索引

我们知道数据一般是存储在磁盘中的，我们在查询数据时就需要将磁盘中的数据以块为单位查询到内存中，在进行轮询来进行查找，当然这样非常适合于数据量比较小的表。如果数据量比较大的话，那么我们的查询效率就会变得非常慢，所以我们就需要引入索引，而索引的灵感来自于字典，可以像字典一样通过偏旁部首来精确查找到我们想要字。

存储结构

InnoDB 逻辑存储结构

• table space 表在物理存储时会存在单独的表空间中。
• seqment 一个表空间中又分为 数据段 和 索引段。
• extent 一个段中又存储了多个区，一个区 1M。
• 一个区中又存储了 64 个页，每个页 16 kb。
  
• 每页中又存储了多行数据。
  假设一行数据1K，每页存储3行数据，机构如下：
  
  有一个经典的面试题，为什么 id 要连续，不建议使用 随机数，uuid 做为表的主键。
  当 id 是连续的时候，新增数据，只会在后面重新分配一页进行存储，如果 id 不连续，则会在某一页中间进行数据插入，而此页数据就超出存储范围，从而发生页分裂，导致整个数据结构发送改变，反而使得存储效率变低。

InnoDB 数据存储结构

InnoDB 中数据存储结构为 B+ 树，并且对B+树做了一定的改良，B+树只有叶子节点进行数据存储，其他节点均存储数据的主键信息以及物理地址值。并且相邻的叶子节点之间进行了链表存储，方便范围查找。

假设一张表使用 bigint 作为主键，加上指针长度一条数据为 14 个字节，那么它的一页 16k 就可以存储
1170 条数据，那么第二层可以存储 1368900 条数据，第三层存储实际数据，假设一条数据为 1K .可以存储 21902400 条数据。所以一般的表结构存储为 2 到 3层。真实通过主键找到数据只需要经过 3次 I/O 操作即可。

• B+ 树扫库扫表的能力更强，因为它叶子结点的链式存储，范围查找也很方便。
• 磁盘读写能力更强。一般3次 I/O 就可以在一张20kw的表中找到任何一条数据。
• 排序能力更强。
• 效率更加稳定。任何数据都需要经过相同次数的 I/O。

什么样的数据可以成为索引

像上面所说的，我们需要一些关键信息来最为索引进行查找，比如主键，唯一键，普通索引等等。

索引的数据结构

生成索引，建立二叉查找数进行二分查找。
生成索引，建立B-Tree结构进行查找。
生成索引，建立B±Tree结构进行查找。
生成索引，建立hash结构进行查找。

密集索引和稀疏索引的区别

概念

• 密集索引
  密集索引文件中每个搜索码值都对应一个索引值。叶子节点不仅仅保存了键值，还保存了整行的数据记录。
  

• 稀疏索引
  稀疏索引文件只为索引码的某些值建立索引项。
  

MyISAM （不管是主键，唯一键，普通索引都是稀疏索引）

对于 MyISAM 来说，主键索引和辅助索引没有任何区别，都需要查找到对应的数据行的位置，再去做检索。而且MyISAM 的索引与数据并不在同一个文件中。

InnoDB（有且仅有一个密集索引）

• 若一个主键被定义，则这个主键作为密集索引。
• 若没有定义主键，则改表第一个唯一非空索引作为密集索引。
• 若没有定义主键，也没有唯一非空索引，InnoDB内部会生成一个6字节自增列的影藏主键作为密集索引。
• 非主键索引存储相关键位和其对应的主键值，查找数据时会先通过非主键索引找到主键，再通过主键去查找数据。
  
  上图中红线表示主键索引的查找路线，直接通过主键找到数据行，而蓝线表示其他索引，则需要先通过其他辅助索引找到主键索引，再通过主键索引找到具体的数据行。
  因为InnoDB的主键索引和对应的数据是保存在同一个文件中的，通过索引加载叶子节点得同时也将数据加载到了内存中，如果根据主键索引查找数据时将就可以直接将数据加载到内存并返回。如果使用稀疏索引来检索数据，首先通过索引找到主键索引，再通过主键索引去加载叶子结点并返回数据。

索引常见的问题

如何定位并优化慢SQL

• 使用日志定位慢查询SQL.
  我们可以在mysql中使用以下命令来查询慢SQL相关的配置。

SHOW VARIABLES LIKE '%query%'

slow_query_log 是否记录慢SQL，slow_query_log_file 慢SQL的文件地址，long_query_time 执行超过这个时间就认为是慢SQL。
我们还可以使用以下命令查询慢SQL的数量。

SHOW STATUS LIKE '%show_queries%'

• 使用数据库的explan工具分析SQL
• 修改SQL或者尽量让SQL走索引

联合索引最左匹配原则的成因

当一张表拥有联合索引(A,B)时，查询语句中包含where A = ? and B = ? 时，或者 where A = ？ 时就会走这个联合索引，而 where B = ？ 时不会使用此联合索引。

• 最左匹配原则：非常重要的原则，mysql 会一直向右匹配直到遇到范围查询（>, <, between, like）就停止匹配，比如a = 1 and b = 2 and c > 3 and d = 4 如果建立（a,b,c,d）顺序的索引，d 是用不到索引的， 如果建立（a,b,d,c）的索引，则都可以使用索引，a，b，d 的顺序可以任意调整。
• =和in 可以乱序，比如 a = 1 and b = 2 and c = 3 建立（a,b,c）索引可以任意顺序，mysql的查询优化器会帮你优化成索引可以识别的形式。
  在联合索引创建时，会首先根据联合索引的第一个字段去创建一个B±Tree，再根据第二个字段去做排序，索引仅仅依靠第二个字段是无法使用索引的。

索引是建的越多越好么？

• 数据量比较小的表不需要建立索引，建立索引会额外增加开销。
• 数据变更需要去维护索引，因此更多的索引就意味着更多的维护成本。
• 更多的索引也需要更多的空间。

创建索引

• 在用于 where 判断 order 排序和 join 的 字段上创建索引。
• 索引的个数不要过多。
• 区分度低的字段不要建立索引。
• 频繁更新的值，不要作为主键或者索引。
• 组合索引要吧散列度高的值放在前面。
• 创建复合索引，而不是修改单列索引。
• 过长的字段可以采取前缀索引，前缀索引设置的长度可以通过 索引匹配到的条数除以总条数来计算，根据计算得出一个比较合理的值。

索引失效

• 索引上使用函数操作，表达式，会导致索引失效。
• 字符串不加引号，出现隐式转换，会导致索引失效。
• like 条件中签名加 % 会导致索引失效。
• 负向语句（<>, !=, not in）大表会导致索引失效。
• 未遵循最左匹配原则的会导致索引失效。