mysql索引

索引

mysql索引：

• 在MySQL中，索引是存储引擎实现的，所以没有统一的索引标准，不同存储引擎的索引工作方式也不一样，也不是所有的存储引擎都支持所有类型的索引
• 即使是多个存储引擎都支持同一种类型的索引，他们的底层实现也有可能不一样

mysql 主要的索引类型有：

• 普通索引，允许在定义索引的列中插入重复值和空值
• 唯一索引，允许空值，但列值必须唯一
• 主键索引，不允许空值，一般在建表的时候就建立了主键索引
• 组合索引，索引列组合的值必须唯一
• 全文索引，允许索引列有空值和重复值，效率高于like的模糊查询，但是精度有问题，一般不用

索引的优点（原因）：

• 减少了需要扫描的数据量，大大加快了数据的检索速度;
• 索引能帮助服务器避免排序和临时表
• 索引可以把随机i/o变为顺序i/o
• 创建唯一性索引，保证数据库表中每一行数据的唯一性;
• 加速表和表之间的连接;

缺点：

• 索引需要占物理空间。
• 当对表中的数据进行增加、删除和修改的时候，索引也要动态的维护，降低了数据的维护速度，
• 创建和维护索引的时间，会随着数据量的增加而增加，一般来说，一个表的索引不要超过6个
• 对于很小的表，全表扫描可能更高效

为什么要使用联合索引

• 减少开销，建一个联合索引(a,b,c)，实际相当于建了(a),(a,b),(a,b,c)三个索引，而每多一个索引，都会增加写操作的开销和磁盘空间的开销，所以使用联合索引会大大的减少开销
• 效率高，索引列越多，通过索引筛选出的数据越少，前提是索引可以生效

常见错误：为每个列创建独立的索引

• 在多个列独立的创建单列索引，大多数情况并不能提高查询性能
• mysql引入了一中索引合并的策略，如果查询能够使用两个单列索引进行扫描，并将结果合并
• 如果出现了这种情况，就说明表的索引建的很糟糕，应该建立一个由相关列的多列索引
• 对多个索引进行合并操作时，需要耗费大量的cpu和内存资源
• 并且优化器不会把这些操作计算到查询成本中，优化器只关心随机页面读取，这样会导致查询的成本被低估，甚至不如做全表扫描

组合索引的最左原则：

• 在检索数据时从联合索引的最左边开始匹配，组合索引的第一个字段必须出现在查询组句中，这个索引才会被用到

• 重复值最少的放在最前面，一次排除的数据就越多，可提高效率

前缀索引

• 当给某一个列字段添加索引时，如果该列字段的字符串值很长时，那么我们创建的索引则会很大且很慢，这个时候如果以索引列开始的部分字符串来建立索引，那么就可以节约索引空间，从而提高索引效率，此时这种类型的索引就叫“前缀索引**”（**索引值重复性越低，查询效率就越高）
• 使用前缀索引时要尽可能降低重复的索引值，否则可能会增加查询的时间

基于页的存储方式

• 随机i/o：
  • 以机械盘为例，数据最终是保存在磁盘一个个扇区上的，一个扇区写满了，就要换下一个扇区，这时就需要找到目标扇区
  • 如果下一个扇区是紧连着的，就是顺序i/o，如果不是就是随机i/o了，这需要更长的物理移动时间，所以减少随机i/o是很重要的

MySQL数据结构：

• 页
  • 数据库读写磁盘的基本单位是页（Page），数据库，无论是读一行，还是读取多行，都是将这些行所在的页进行加载。
  • 因为记录是按照行来存储的，但是数据库的读取并不以行为单位，否则一次I/O操作只能处理一行数据，效率会很低。
  • lnnoDB将数据划分为若干个页，InnoDB中页的大小默认为16KB。以页作为磁盘和内存之间交互的基本单位，也就是一次最少从磁盘中读取16KB的内容到内存中，一次最少把内存中的16KB的内容刷新到磁盘中。
  • 为了减少磁盘i/o，MySQL使用B+树索引，检索一条数据的快慢，主要受树的高度影响的，树越矮，i/o次数越少，而通过b+树检索到的不是目标行数据，而是目标行数所在的页
  • 页如果按照类型划分的话，常见的有 数据页，系统页、Undo日志页和事务数据页等。数据页是我们最常使用的页。
• 区(Extent)是比页大一级的存储结构，在InnoDB存储引擎中，一个区会分配64 个连续的页。因为InnoDB中的页大小默认是16KB，所以一个区的大小是64*16KB= 1MB
• 段(Segment)由一个或多个区组成，区在文件系统是一个连续分配的空间（在InnoDB中是连续的64个页)，不过在段中不要求区与区之间是相邻的。段是数据库中的分配单位，不同类型的数据库对象以不同的段形式存在。当我们创建数据表、索引的时候，就会相应创建对应的段，比如创建一张表时会创建一个表段，创建一个索引时会创建一个索引段。
• 表空间（Tablespace）是一个逻辑容器，表空间存储的对象是段，在一个表空间中可以有一个或多个段，但是一个段只能属于一个表空间。数据库由一个或多个表空间组成，表空间从管理上可以划分为系统表空间、用户表空间、撤销表空间、临时表空间等。

表中数据越大，索引对性能的影响愈发重要

• mysql中 索引是存储引擎层实现的，所以不同的存储引擎对索引的实现也不一样，常见的数据格式有：

• b+树：

  • innodb和myIsam 存储引擎都支持b+树索引
  • 索引的目的就是提高查询效率，而树型结构的查找效率很高，例如二叉查找树，理想情况下每次查找都是在做二分查找
  • 因为内存的易失性，通常数据都会存储在外部设备，也就是硬盘中，但和内存相比，从硬盘中读取数据的速度会非常缓慢，所以减少对硬盘的访问次数可以显著提高效率
  • 而b-树就是一棵多叉的查找树，它的一个节点可以存储多个键值和数据，相对于平衡二叉树来说，因为每个节点都存储了更多的键值和数据，所以每个节点就能拥有更多的子节点，树的高度会降低很多，也就是减少了磁盘的读取次数，查找效率会更高，一般树的节点最多拥有几个子节点就被称为几阶b树
  • 又因为从磁盘中读取数据是按照磁盘块来读取的，并不是一条条的读取，也就是说每次从磁盘中读取到的数据大小其实是相同的，如果能把尽可能多的数据放在一个磁盘块中，一次就能读取更多数据，也就能减少读取磁盘的次数
  • b+树就是对b-树的进一步优化，b+树的非叶子节点是不存储数据的，只存储键值，所以一次能读取的数据更多，一个节点能存储的数据也会更多，树的阶数也就更大，对磁盘的读取会更少，效率也会更高

• 哈希索引

  • 哈希索引是基于散列表实现的，主要用于memory存储引擎
  • 哈希索引是使用索引列的值来计算hashcode值，然后再hashcode值对应位置存储所在行数据的物理位置，访问时根据hashcode值进行精确匹配，效率极高
  • 但是精确匹配就需要匹配索引所有列的查询才有效，只支持等值比较，不支持范围查询
  • 而且也不是按照索引值顺序排列的，所以不能用于排序，也无法区间快速查找
  • 哈希索引只包含哈希值和行指针，没有真正的数据，仍然需要读取对应的行

• 全文索引

  • 只能用于innodb和myisam，innodb也是再5.5以后才支持的全文索引，对于文本大对象或者较大的char类型数据，可以使用全文索引，但是全文索引会很浪费时间和空间
  • 全文索引查找的不是索引中的值，而是文本中的关键字，类似于搜索引擎做的事，而不是简单的where条件匹配
  • 全文索引也只适用于match against 操作
  • 在相同的列上建立不同类型的索引不会冲突

b+树和b-树的区别

• b+树因为非叶子节点没有数据，所以查询一个元素必须要从根节点访问到叶子节点，而每一个叶子节点高度是相同的，所以数据的查询效率相等，而b-树有可能在查找途中结束
• b+树的数据会在叶子节点用双向链表连接起来，而且数据是按照顺序排列的，所以b+树分组查找和去重会很高效
• myisam和innodb在b+树索引实现的不同点：myisam，b+树索引叶子节点并没有存储数据，而是存储的数据地址（非聚簇索引）

innodb自适应哈希索引：

• 当innodb发现某些索引值被非常频繁的访问时，就会在原有的b+树索引上，在内存中再构建一个哈希索引
• 这让b+树索引具备了一些哈希索引的优势，可以实现非常快速查找
• 这个过程是自动的，用户无法进行控制 ，但是可以手动关闭这个特性

innodb 聚簇索引和非聚簇索引：

• 聚簇索引将数据存储与索引放在一起，索引的每一个叶子节点保存了主键值、事务id、用于事务和mvcc的回滚指针、以及所有的剩余列，如果主键是一个前缀索引，也会包含完整的主键列
• inndob 使用的聚簇索引，聚簇索引默认是主键，如果表中没有主键，会自动选择一个主键（innodb会选择一个唯一且非空的索引代替，如果没有这样的索引，会定义一个隐式的主键来作为索引，所以聚簇索引是由唯一性的）
• 会将主键组织到一颗b+树中，行数据就存储在叶子节点
• 主键以外的列构建的 B+ 树索引，称为非聚集索引， innodb 非聚簇索引，叶子节点存储的不是行数据，而是该列对应的主键，需要根据主键进行二次查找，好处是innodb在移动行时，不需要更新二级索引中的主键指针

聚簇索引的优缺点：

• 优点：
  • 可相关联的数据保存在一起，能够减少从磁盘读取的数据页
  • 应为索引和数据保存在一起，所以访问速度更快
  • 使用索引覆盖的查询能够直接使用页节点中的主键值
• 缺点：
  • 插入速度严重依赖于插入顺序，按照顺序插入是最快的方式，如果不是最好用optimize table 命令重新组织以下表，当然最好避免随机写入
  • 更新聚簇索引的代价会很高，会把被更新的行移动到新的位置
  • 插入新行时，如果页已满，就会造成页分裂，来容纳改行，导致表占用更多的空间
  • 聚簇索引会使全表扫描变慢，尤其是行比较稀疏，或者由于页分裂导致数据存储不连续的时候
  • 二级索引可能会很大，并且二级索引需要查找两次

mylsam 非聚簇索引：

• 将行数据与索引分开存储，索引结构的叶子节点指向了数据对应的地址
• mylsam 使用的就是非聚簇索引，无论是主键索引的b+树，还是非主键索引的b+树，叶子节点都指向真正的数据，索引树也是独立的

innodb主键：

• Innodb主键是聚簇索引，其他的二级索引中必须包含主键列，如果主键很大的话，其他的所有索引都会很大，所以如果表上的索引较多，主键应该尽可能的小
• 主键如果是自增列，可以保证是按照顺序写入，最好避免随机写入，会使得聚簇索引很糟糕，
  • 例如使用uuid作为主键是非常差的选择（不仅插入时间会变长，索引也会变得更大，而且会导致更多的也分裂和碎片）
  • 不按顺序写，新的主键值不一定比之前的大，所以不能直接的插到索引的最后，而是需要找到合适的位置，并且分配空间，会增加很多额外工作
• 但是对于高并发的表，顺序的主键上界会成为热点，因为所有的插入都发生在这里，会导致资源竞争
• 如果没有定义主键，innodb会选择一个唯一的非空索引替代，如果也没有这样的索引会隐式定义一个主键做聚簇索引，这样的缺点是依赖一个单点的自增值，可能会导致非常高的锁竞争

索引失效场景：

• 使用索引是没有匹配“最左匹配原则”
• 在使用索引前使用了范围查找
• 使用or关键字用不了索引
• 索引列有运算，或者函数运算
• like以%开头
• 产生类型转换
• 如果MySQL预计使用全表扫描要比使用索引快，则不使用索引

索引覆盖：

• 也就是通过索引直接获取列数据，不需要再读取行，只扫描索引，不需要回表好处有：
  • 索引条目通常小于数据行大小，只读取索引可以减小数据访问量
  • 索引按照列值顺序存储，返回查询比随机从磁盘读取要快的多
  • 覆盖索引对innodb特别有用，如果二级索引可以覆盖查询，就不用再对主键索引二次查询
• 不是所有的索引都能称为覆盖索引，覆盖索引必须存储列的值，所以只有B+ 树索引可以
• 想要覆盖索引，就不要使用like，不要使用索引以外的列

innodb只有再访问行的时候才会加锁，索引可以减少访问的行数，从而减少锁的数量，但是如果索引不能有效过滤掉无效的行，再数据返回给服务器层后，mysql才能使用where字句，这时就无法避免的会锁定行

重复索引

• 重复索引是指在相同列上按照相同顺序建立的相同类型的索引，应该避免创建重复索引，发现之后应该立即移除

• mysql允许在相同的列上创建多个相同索引，虽然会抛出警告，但并不会阻止，

• MySQL需要单独维护重复的索引，优化器在优化查询的时候，也需要逐个评估，会影响性能和浪费磁盘

冗余索引

• 例如创建了（A,B）索引，在去创建（A）索引就是冗余索引，
• 还有就是创建了（A）索引，再去创建（A，id）索引，也是冗余索引
• 大多数情况下，都不需要冗余索引，应该扩展已有的索引，而不是创建新的索引，但是有的时候扩展索引会导致索引变得很大，从而影响其他使用该索引的性能

至于未使用的索引，完全是累赘，建议删除

索引使用的注意事项

• 在有更多不同值的列上创建索引会更好
• 尽可能的去重用索引，而不是建立大量的索引
• 尽可能把范围查询放到索引列的后面，对于范围查询，没办法使用到范围列后面的索引

limit数据量过大:

• 尽可能的使用索引覆盖扫描，而不是查询所有列，对于偏移量很大的时候，效率提升很明显

• 随着偏移量的增加，需要花费大量的时间来扫描需要丢弃的数据，缓存和提前计算是仅有的策略

• 更好的办法当然是减少用户能够翻到的页数（大量的翻页，没有意义）

维护表：

• 目的：找到并修复损坏的表，维护准确的索引统计信息，减少碎片
• 有可能因为硬件或者其他问题，导致表或索引损坏，会导致很多莫名其妙的问题，如果遇到了不应该出现的问题，可以试试 CHECK TABLE 来检查表是否发生了损坏，并可以使用REPAIR TABLE来修复损坏的表
• 如果存储引擎向优化器提供的扫描行数信息不准确，或者执行计划太复杂导致无法准确的获取各个阶段的匹配行数，优化器就没办法通过索引的形象来估算扫描行数，可以通过 analyze table 来重新生成统计信息
• b+树索引会产生碎片化，可以通过optimize table 命令重新组织以下表