java基础巩固-宇宙第一AiYWM：为了维持生计，MySQL基础Part3(索引)~整起

PART1:索引(索引就相当于我们字典中的目录，使用索引可以极大的提高我们在数据库的查询效率)、

• 当咱们接触到SSM框架或者SpringBoot框架时，会自己问，哎呀，我到底为么子要用框架呀。同样的，咱们增删改查写的好好的，为啥要有索引这个东西出现呢？
  • 根本原因，就是为了提高数据查询的效率，就像字典或者书的目录一样，对于数据库的表来说，索引其实就是数据库的表的目录，来帮助存储引擎快速获取数据的一种数据结构。索引也可以看作是一个占物理空间的文件（相当于书的目录，目录难道不占书的物理空间嘛，如我们在查字典的时候，前面都有检索的拼音和偏旁、笔画等，然后找到对应字典页码，这样然后就打开字典的页数就可以知道我们要搜索的某一个key的全部值的信息了。）【所以索引是以空间换时间的设计思想】
    • MySQL 的数据是持久化的，意味着数据（索引+记录）是保存到磁盘上的，因为这样即使设备断电了，数据也不会丢失。磁盘读写的最小单位是扇区，扇区的大小只有 512B 大小，操作系统一次会读写多个扇区，所以操作系统的最小读写单位是块（Block）。Linux 中的块大小为 4KB，也就是一次磁盘 I/O 操作会直接读写 8 个扇区。
      • 由于数据库的索引是保存到磁盘上的，因此当我们通过索引查找某行数据的时候，就需要先从磁盘读取索引到内存，再通过索引从磁盘中找到某行数据，然后读入到内存，也就是说查询过程中会发生多次磁盘 I/O，而磁盘 I/O 次数越多，所消耗的时间也就越大。所以，我们希望索引的数据结构能在尽可能少的磁盘的 I/O 操作中完成查询工作，因为磁盘 I/O 操作越少，所消耗的时间也就越小。
      • MySQL 是支持范围查找的，所以索引的数据结构不仅要能高效地查询某一个记录，而且也要能高效地执行范围查找。
        • 要设计一个适合 MySQL 索引的数据结构，至少满足以下要求：：
          • 能在尽可能少的磁盘的 I/O 操作中完成查询工作；
          • 要能高效地查询某一个记录，也要能高效地执行范围查找；
      • 一张表一般都要去建主键，所以主键索引几乎是每张表
    • 再说一点比较专业化的原因就是：
      • 创建了唯一性索引之后可以保证数据库表中每一行数据的唯一性
      • 帮助引擎层避免排序和临时表
        • MySQL 临时表的用法和特性：
          • 只对当前session可见。
          • 可以与普通表重名。
          • 增删改查用的是临时表。
          • show tables 不显示普通表。
          • 在实际应用中，临时表一般用于处理比较复杂的计算逻辑。
          • 由于临时表是每个线程自己可见的，所以不需要考虑多个线程执行同一个处理时临时表的重名问题，在线程退出的时候，临时表会自动删除。
      • 将随机IO变为顺序IO，加速表和表之间的连接
• 三种常见的索引底层数据结构，分别是：
  • 哈希表
    • 哈希表这种适用于等值查询的场景，比如 memcached 以及其它一些 NoSQL 引擎，不适合范围查询。
  • 有序数组
    • 有序数组索引只适用于静态存储引擎，等值和范围查询性能好，但更新数据成本高。
  • 搜索树
    • N 叉树由于读写上的性能优点以及适配磁盘访问模式以及广泛应用在数据库引擎中。以 InnoDB 的一个整数字段索引为例，这个 N 差不多是 1200。棵树高是 4 的时候，就可以存 1200 的 3 次方个值，这已经 17 亿了。考虑到树根的数据块总是在内存中的，一个 10 亿行的表上一个整数字段的索引，查找一个值最多只需要访问 3 次磁盘。其实，树的第二层也有很大概率在内存中，那么访问磁盘的平均次数就更少了

MySQL中索引也叫做键，是存储引擎用来快速找到记录（数据库索引，是数据库管理系统中一个排序的数据结构，以协助快速查询、更新数据库表中数据）的一种数据结构。.索引是一种特殊的文件(InnoDB数据表上的索引是表空间的一个组成部分)，它们包含着对数据表里所有记录的引用指针。

• 索引可以包括一个或者多个列的值。（多个列时列的顺序也十分重要）
  • MySQL只能高效的使用索引的最左前缀列
    
    • 什么是最左前缀原则?：最左前缀其实说的是，在 where 条件中出现的字段，「如果只有组合索引中的部分列，则这部分列的触发索引顺序」，是按照定义索引的时候的顺序从前到后触发，最左面一个列触发不了，之后的所有列索引都无法触发【最左前缀匹配原则指的是，在使用联合索引时，MySQL 会根据联合索引中的字段顺序，从左到右依次到查询条件中去匹配，如果查询条件中存在与联合索引中最左侧字段相匹配的字段，则就会使用该字段过滤一批数据，直至联合索引中全部字段匹配完成，或者在执行过程中遇到范围查询，如 >、<、between 和 以%开头的like查询 等条件，才会停止匹配。】。
      
      
      • 所以，我们在使用联合索引时，可以将区分度高的字段放在最左边，这也可以过滤更多数据。
• 索引是在存储引擎层而不是服务器层实现的
• 索引的工作原理~尽可能精准定位，锁定更少的行，尽可能少的做无用功（索引的底层实现，索引的实现通常使用B树及其变种B+树）
  
• 索引常见的SQL操作
  • 创建索引有三种方式
    • 使用ALTER TABLE命令去增加索引
      
    • 使用CREATE INDEX命令创建
      
    • 在执行CREATE TABLE时创建索引

    CREATE TABLE user_index2 (
        id INT auto_increment PRIMARY KEY,
        first_name VARCHAR (16),
        last_name VARCHAR (16),
        id_card VARCHAR (18),
        information text,
        KEY name (first_name, last_name),
        FULLTEXT KEY (information),
        UNIQUE KEY (id_card)
    );
    

    • 其他操作：
      SHOW INDEX FROM 表名：//查询表中的索引
      EXPLAIN SELECT * FROM employees.titles WHERE emp_no=‘10001’ AND title=‘Senior Engineer’ AND from_date=‘1986-06-26’;//通过explain查看MySQL语句中有没有用到索引
      
    • MySQL 如何为表字段添加索引？
      
• 索引使用原则、创建或者使用索引时需要注意什么（创建时需要注意什么）
  
  
  • 怎样的索引的数据结构是好的？，也就是说要设计一个适合 MySQL 索引的数据结构，至少满足以下要求：
    • 能在尽可能少的磁盘的 I/O 操作中完成查询工作；
      • 索引字段越小越好：数据库的数据存储以页为单位一页存储的数据越多一次IO操作获取的数据越大效率越高。
    • 要能高效地查询某一个记录，避免全表扫描，也要能高效地执行范围查找；
  • 1.特大型表（TB），此时用索引代价很高，所以不应用索引了而可以使用例如分区技术等，可以建立一个元数据信息表（比如哪个用户的信息存储在哪个表中，这种元数据），区分出查询需要的一组数据而不是一条记录一条记录的匹配，这样在查询时就可以直接忽略掉那些不包含指定用户信息的表
    • 要不要使用分区表
      • 分区并不是越细越好。实际上，单表或者单分区的数据一千万行，只要没有特别大的索引，对于现在的硬件能力来说都已经是小表了。
      • 分区也不要提前预留太多，在使用之前预先创建即可。比如，如果是按月分区，每年年底时再把下一年度的 12 个新分区创建上即可。对于没有数据的历史分区，要及时的 drop 掉。
  • 2.索引的查询使用或者编写利用原则
    • 2.0.小建议：
      
      • 选择性高的列，也就是重复度低的列。比如女子学校学生表中的性别列，所有数据的值都是女，这样的列就不适合建索引。比如学生表中的身份证号列，选择性就很高，就适合建索引。
      • 经常用于查询的列(出现在where 条件中的列)。不过如果不符合上一条的条件，即便是出现在where条件中也不适合建索引，甚至就不应该出现在where条件中。
      • 多表关联查询时作为关联条件的列。比如学生表中有班级ID的列用于和班级表关联查询时作为关联条件，这个列就适合建索引。
      • 值会频繁变化的列不适合建索引。因为在数据发生变化时是需要针对索引做一些处理的，所以如果不是有非常必要的原因，不要值会频繁变化的列上建索引，会影响数据更新的性能。反过来也就是说索引要建在值比较固定不变的列上。
      • 一张表上不要建太多的索引。和上一条的原因类似，如果一张 表上的索引太多，会严重影响
        数据增删改的性能。也会耗费很大的磁盘空间。
      • 直接创建完整索引，但这样可能会比较占用空间。
      • 创建前缀索引，节省空间，但会增加查询扫描次数，并且不能使用覆盖索引。
      • 倒序存储，再创建前缀索引，用于绕过字符串本身前缀的区分度不够的问题。
        • 尽量选择区分度高的列作为索引，区分度的公式是count(distinct col)/count(*)，表示字段不重复的比例，比例越大我们扫描的记录数越少，唯一键的区分度是1，而一些状态、性别字段可能在大数据面前区分度就是0，那可能有人会问，这个比例有什么经验值吗？使用场景不同，这个值也很难确定，一般需要join的字段我们都要求是0.1以上，即平均1条扫描10条记录。
        • MySQL 在真正开始执行语句之前，并不能精确地知道满足这个条件的记录有多少条。而只能根据统计信息来估算记录数。这个统计信息就是索引的“区分度。
      • 创建 hash 字段索引，查询性能稳定，有额外的存储和计算消耗，跟第三种方式一样，都不支持范围扫描。
    • 2.1.如果服务器从存储中读取一个数据块只是为了获取其中一行，此时使用索引就比较低效，应该保证咱们读取的块中能尽可能多的包含所需要的行，此时使用索引才能提高效率。（单行访问是很慢的，最好服务器从存储中读取的数据块中能包含尽可能多需要的行而不是浪费很多工作的获取到其中一行）
    • 2.2. 尽可能使用数据原生顺序从而避免额外的排序操作。按顺序访问范围数据很快，有两个原因
      • 顺序IO不需要多次磁盘寻道，所以比随机IO快很多（特别对机械硬盘）
      • 如果服务器能按照需要顺序读取数据那么就不再需要额外排序操作，并且GROUP BY 查询也无需在做排序和按行进行聚合计算了
    • 2.3.尽可能使用索引覆盖查询（覆盖索引）。如果一个索引包含了查询需要的所有列【意思就是二级索引中有我想要的东西，这就要覆盖索引。二级索引中无我想要列就得回表】，那么存储引擎就不需要再回表查找行【覆盖索引（covering index）指一个查询语句的执行只用从索引中就能够取得，不必从数据表中读取，可以减少回表的次数。换句话说，在某个查询里面，索引 k 已经“覆盖了”我们的查询需求，称为覆盖索引。】
      • 覆盖索引可以减少树的搜索次数，显著提升查询性能，所以使用覆盖索引是一个常用的性能优化手段
        
        • 优化索引常用的方法：
          • 前缀索引优化；
            
          • 覆盖索引优化；
            
          • 主键索引最好是自增的；【官方建议使用自增长主键作为索引】
            • InnoDB 创建主键索引默认为聚簇索引，数据被存放在了 B+Tree 的叶子节点上。也就是说，同一个叶子节点内的各个数据是按主键顺序存放的，因此，每当有一条新的数据插入时，数据库会根据主键将其插入到对应的叶子节点中。
              • 结合B+Tree的特点**，自增主键是连续的**，在插入过程中尽量减少页分裂，即使要进行页分裂，也只会分裂很少一部分。并且能减少数据的移动，每次插入都是插入到最后。总之就是 使用自增长主键作为索引可以减少分裂和移动的频率。
            • 如果我们使用自增主键，那么每次插入的新数据就会按顺序添加到当前索引节点的位置，不需要移动已有的数据，当页面写满，就会自动开辟一个新页面。因为每次插入一条新记录，都是追加操作，不需要重新移动数据，因此这种插入数据的方法效率非常高。
              
            • 如果我们使用非自增主键，由于每次插入主键的索引值都是随机的，因此每次插入新的数据时，就可能会插入到现有数据页中间的某个位置，这将不得不移动其它数据来满足新数据的插入，甚至需要从一个页面复制数据到另外一个页面，我们通常将这种情况称为页分裂。页分裂还有可能会造成大量的内存碎片，导致索引结构不紧凑，从而影响查询效率。
              
          • 索引最好设置为 NOT NULL
            
          • 防止索引失效；发生索引失效的情况见下面：
      • 回表：回表就是先通过数据库索引扫描出该索引树中数据所在的行，先取到主键 id，再通过主键 id 取出主键索引数中的数据，即基于非主键索引的查询需要多扫描一棵索引树.
        
        • 非聚簇索引也不是一定会进行回表查询，这涉及到查询语句所要求的字段是否全部命中了索引，如果全部命中了索引，那么就不必再进行回表查询。一个索引包含（覆盖）所有需要查询字段的值，被称之为"覆盖索引"。比如假设我们在员工表的年龄上建立了索引，那么当进行select score from student where score > 90的查询时，在索引的叶子节点上，已经包含了我们想要的score 信息，不会再次进行回表查询。
    • 2.4.索引设计规范：
      • 建议单张表索引不超过 5 个。索引并不是越多越好！索引可以提高效率同样可以降低效率。索引可以增加查询效率，但同样也会降低插入和更新的效率，甚至有些情况下会降低查询效率【因为 MySQL 优化器在选择如何优化查询时，会根据统一信息，对每一个可以用到的索引来进行评估，以生成出一个最好的执行计划，如果同时有很多个索引都可以用于查询，就会增加 MySQL 优化器生成执行计划的时间，同样会降低查询性能】。
      • 禁止给表中的每一列都建立单独的索引：5.6 版本之前，一个 sql 只能使用到一个表中的一个索引，5.6 以后，虽然有了合并索引的优化方式，但是还是远远 没有使用一个联合索引的查询方式好
      • 每个 InnoDB 表必须有个主键
        • InnoDB 是一种索引组织表：数据的存储的逻辑顺序和索引的顺序是相同的。每个表都可以有多个索引，但是表的存储顺序只能有一种。
          
      • 常见索引列建议：
        
      • 如何选择索引列的顺序：
        • 建立索引的目的是：希望通过索引进行数据查找，减少随机 IO，增加查询性能 ，索引能过滤出越少的数据，则从磁盘中读入的数据也就越少
          
      • 避免建立冗余索引和重复索引（增加了查询优化器生成执行计划的时间）
        • 重复索引示例：primary key(id)、index(id)、unique index(id)
        • 冗余索引示例：index(a,b,c)、index(a,b)、index(a)
    • 2.5 如果查询的列不是独立的（独立的列指的是索引列不能是表达式的一部分或者某个函数的参数）就不能在MySQL中使用索引
      
    • 2.6.在多个列上建立单独的索引大部分情况下不是很合适，可以使用“索引合并”策略（此时已经说明咱们的查询和表的结构不是最优的，要用索引合并来填挖的坑）
      
• 索引优点以及索引缺点
  
  
  • 什么时候需要 / 不需要创建索引？索引不是万能钥匙，它也是根据场景来使用的
    
• 索引的类型或者说分类（MySQL默认的存储引擎是InnoDB,InnoDB支持B+Tree索引、全文索引、哈希索引等）
  
  • 根据叶子节点的内容或者说物理存储来分类，索引类型分为主键索引【属于聚簇索引】和非主键索引【也叫辅助索引或者二级索引】
    • 在 InnoDB 里，索引B+ Tree的叶子节点存储了整行数据的是主键索引，也被称之为聚簇索引，即将数据存储与索引放到了一块，找到索引也就找到了数据。
      • 聚集（表示数据行和相邻的键值紧凑的存储在一起）索引：并不是一种单独的索引类型而是一种数据存储方式。具体细节取决于不同的实现，InnoDB的聚簇索引其实就是在同一个结构中保存了B-Tree索引(技术上来说是B+Tree)和数据行
      • 在 MySQL 中，InnoDB 引擎的表的 .ibd文件就包含了该表的索引和数据，对于 InnoDB 引擎表来说，该表的索引(B+树)的每个非叶子节点存储索引，叶子节点存储索引和索引对应的数据
      • 当表有聚簇索引时他的数据行实际上存放在索引的叶子页中
        • 在 InnoDB 里，索引B+ Tree的叶子节点存储了整行数据的是主键索引，也被称之为聚簇索引，即将数据存储与索引放到了一块，找到索引也就找到了数据。
        • 叶子页包含了行的全部数据，但是节点页只包含了索引列
      • 一个表只能有一个聚簇索引，因为无法同时把数据行存放在两个不同的地方
      • 聚集索引或者叫主键索引的优缺点：
        
        
    • 非主键索引叶子节点存的主键的值，在InnoDB里也被称为二级索引
      
      • 非聚集索引（除了聚簇索引（聚集索引），而索引B+ Tree的叶子节点存储了主键的值的而不是主键索引，非聚集索引也被称之为非聚簇索引、二级索引。）
        
      • 在查询时使用了二级索引，如果查询的数据能在二级索引里查询的到，那么就不需要回表，这个过程就是覆盖索引。如果查询的数据不在二级索引里，就会先检索二级索引，找到对应的叶子节点，获取到主键值后，然后再检索主键索引，就能查询到数据了，这个过程就是回表。
        
  • 按字段特性分类：唯一索引，普通索引，前缀索引等索引属于二级索引【或者叫辅助索引】。
    
    • 主键索引
      
    • 唯一索引： 建立唯一索引的目的大部分时候都是为了该属性列的数据的唯一性，而不是为了查询效率
      
    • 普通索引：普通索引的唯一作用就是为了快速查询数据，一张表允许创建多个普通索引，并允许数据重复和 NULL
      
    • 前缀索引：前缀索引只适用于字符串类型的数据。前缀索引是对文本的前几个字符创建索引，相比普通索引建立的数据更小， 因为只取前几个字符【因为可能我们索引的字段非常长，这既占内存空间，也不利于维护。所以我们就想，如果只把很长字段的前面的公共部分作为一个索引，就会产生超级加倍的效果】
      
      • order by不支持前缀索引
      • 创建前缀索引的流程是：
        • 先计算完整列的选择性 : select count(distinct col_1)/count(1) from table_1
        • 再计算不同前缀长度的选择性 :select count(distinct left(col_1,4))/count(1) from table_1
        • 找到最优长度之后，创建前缀索引 : create index idx_front on table_1 (col_1(4))
  • 按照字段个数：
    • 单列索引，建立在单列上的索引称为单列索引，比如主键索引；
    • 联合索引，也叫复合索引：通过将多个字段组合成一个索引，也就是建立在多列上的索引称为联合索引
      
      • 在联合索引中，如果想要命中索引，需要按照建立索引时的字段顺序挨个使用，否则无法命中索引。具体原因是：
        • MySQL使用索引时需要索引有序，假设现在建立了"name，age，school"的联合索引，那么索引的排序为: 先按照name排序，如果name相同，则按照age排序，如果age的值也相等，则按照school进行排序。当进行查询时，此时索引仅仅按照name严格有序，因此必须首先使用name字段进行等值查询，之后对于匹配到的列而言，其按照age字段严格有序，此时可以使用age字段用做索引查找，以此类推。因此在建立联合索引的时候应该注意索引列的顺序，一般情况下，将查询需求频繁或者字段选择性高的列放在前面。此外可以根据特例的查询或者表结构进行单独的调整。
      • 联合索引的最左匹配原则：在遇到范围查询（>、<、between、like 包括like '林%'这种）的时候，就会停止匹配，也就是范围列可以用到联合索引，但是范围列后面的列无法用到联合索引，因为范围列后面的列无法保证key有序。
        
        性别的区分度就很小，不适合建立索引或不适合排在联合索引列的靠前的位置，而 UUID 这类字段就比较适合做索引或排在联合索引列的靠前的位置。
        
  • 从存储结构方面（索引存储时保存的形式）来分：B+tree索引、Hash索引、Full-text索引。
    
    • 全文索引:FullText,用来查找文本中的关键词而不是直接比较索引中的值
      
      • 常用于大数据情况下，在MyISAM数据库引擎下才有，用来快速定位数据，类似于搜索引擎做的事情而不是简单的WHERE条件匹配
    • 空间数据索引（R-Tree）
      • 这类索引可以从任意维度来组合查询，开源关系数据库系统中对GIS的解决方案做的比较好的是PostgresSQL的PostGIS
    • BTree索引（B-Tree或B+Tree索引），Memory引擎同时也支持B-Tree索引。B-Tree索引：（**谈论索引（在不指明类型的情况下）大部分说的是B-Tree索引，咱们多数情况下也会使用B-Tree索引，其他类型的索引大多适用于特殊的目的），【InnoDB 是在 MySQL 5.5 之后成为默认的 MySQL 存储引擎，B+Tree 索引类型也是 MySQL 存储引擎采用最多的索引类型**。】
      • MyISAM 和 InnoDB 实现 B 树索引方式的区别：
        
        • InnoDB 存储引擎：B+树索引的叶子节点保存数据本身，其数据文件本身就是索引文件
        • MyISAM 存储引擎：B+ 树索引的叶子节点保存数据的物理地址，叶节点的 data 域存放的是数据记录的地址，索引文件和数据文件是分离的。
          
      • B-Tree由于使得存储引擎 不再需要进行全表扫描而是从索引的根节点开始搜索来获取需要的数据（数据分布在各个节点之中），加快了访问的速度。根节点的槽中存放了指向子节点的指针，存储引擎根据这些指针向下层查找。通过比较节点页的值和要查找的值可以找到合适的指针进入下层子节点，这些指针实际上定义了子节点页中值的上限和下限。最终存储引擎要么是找到对应的值要么该记录不存在。
        
      • B-Tree通常所有的值都是按顺序存储的（意味着索引首先按照最左列进行排序，其次是第二列），也就是说B-Tree对索引列是按顺序组织存储的（索引对作为索引的多个值进行排序的依据是CREATE TABLE语句中定义索引时的列的顺序），并且每一个叶子页到根的距离相同。-----所以很适合查找范围数据（比如找到所有以I到K开头的名字）
        • 选择索引的列顺序的一个经验法则：将选择性最高的列放到索引最前列（不需要考虑排序和分组时），避免随机IO和排序
          
          
          
      • 很多存储引擎其实用的是B+Tree,B+Tree中每一个叶子节点都包含指向下一个叶子结点的指针，从而方便叶子结点的一定范围内的遍历。
        
      • B-Tree索引的限制或者说缺点就跟链表一样,【得从最开始找起】
        
      • B-Tree有缺点，所以诞生了B+Tree。B+Tree索引是B-Tree的改进版，MySQL使用B+Tree。（B+Tree是根据 键值（KeyValue） 快速找到数据，B+Tree树索引的构造类似于二叉树，
        • 举个例子：
          
          
          
        • B+Tree 存储千万级的数据只需要 3-4 层高度就可以满足，这意味着从千万级的表查询目标数据最多需要 3-4 次磁盘 I/O，所以B+Tree 相比于 B 树和二叉树来说，最大的优势在于查询效率很高，因为即使在数据量很大的情况，查询一个数据的磁盘 I/O 依然维持在 3-4次。
        • B+树定义: B+ 树是为磁盘或其他直接存取辅助设备设计的一种平衡查找树。在B+树中，所有记录节点都是按键值的大小顺序存放在同一层的叶子节点上，由各叶子节点指针进行连接。具体例子如下
          
        • B+树索引并不能按照给定的键值对找到一个具体行或者叫记录，B+树索引找到的只是被查找的数据行所在的页，然后数据库通过把页读入到内存中再从内存中查找待查的数据）
          • 每页Page Directory中的槽是按照主键的顺序存放的，对于某一条具体记录的查询是通过对Page Directory进行二分查找得到的。
        • B+Tree中数据都在叶子结点上并且增加了顺序访问指针，每个叶子节点都指向相邻的叶子节点的地址，相比于B-Tree来说，进行范围查找时只需要查找两个节点进行遍历即可（B-Tree需要获取所有的节点）
        • B+根据键值快速找到数据，B+树索引就是咱们经常说起的传统意义上的索引
          • B+树中的B不是代表二叉(binary)， 而是代表平衡(balance), 因为B+树是从最早的平衡二叉树演化而来，但是B+树不是一个二叉树。
            • B+树是通过二叉查找树（左子树的键值总是小于根的键值，右子树的键值总是大于根的键值。因此可以通过中序遍历得到键值的排序输出），再由平衡二叉树，B树演化而来
            • 平衡二叉树的查找性能是比较高的，但不是最高的，只是接近最高性能。最好的性能需要建立一棵最优二叉树，但是最优二叉树的建立和维护需要大量的操作，因此，用户一般只需建立一棵平衡二叉树即可。
        • 性质
          
        • 数据库的B+索引分为两类（聚集索引与辅助索引两类的区别就是叶子结点存放的是否是一整行的信息，但是不管是聚集还是辅助的索引，其内部都是B+树的，即高度平衡的，叶子节点存放着所有的数据。）
          
          
          
          • 聚集（聚簇）索引Clustered Index:（因为数据页只能按照一颗B+树进行排序，你一张表不可能有多颗B+树吧，所以也就是说一张表只能有一个聚簇索引）
            • InnoDB存储引擎表是索引组织表，也就是表中数据按照主键顺序存放
              
              
          • 辅助索引：Secondary Index，也叫二级索引或者非主键索引
            
            也不是每次都要查两棵树才能获取到数据，有时候，在二级索引的 B+Tree 就能查询到结果的过程就叫作「覆盖索引」，也就是只需要查一个 B+Tree 就能找到数据。
            
            
            
        • B+Tree底层工作原理，在B+Tree中所有记录节点都是按键值大小顺序存放在同一层的叶子节点中，由叶子结点指针进行进行连接
          
          
          • MySQL的默认存储引擎存储数据的过程：咱们在一个数据页中的记录是有限的，且主键值是有序的，所以通过对所有记录进行分组，然后将组号（槽号）存储到页目录，使其起到索引作用，通过二分查找的方法快速检索到记录在哪个分组，来降低检索的时间复杂度。
          • B+ 树是如何进行查询的：但是当我们需要存储大量的记录时，就需要多个数据页，这时我们就需要考虑如何建立合适的索引，才能方便定位记录所在的页。为了解决这个问题，InnoDB 采用了 B+ 树作为索引。磁盘的 I/O 操作次数对索引的使用效率至关重要，因此在构造索引的时候，我们更倾向于采用“矮胖”的 B+ 树数据结构，这样所需要进行的磁盘 I/O 次数更少，而且 B+ 树 更适合进行关键字的范围查询。【InnoDB 里的 B+ 树中的每个节点都是一个数据页】
            
            
        • B+树的常见操作（B+Tree是通过二叉查找树（二叉搜索树，下面有）----->再到平衡二叉树，才演化到了----->B树（也就是说B树肯定是一颗平衡二叉树和二叉搜索树，平衡二叉树肯定是一颗二叉搜索树，有包含关系呢））
          • 平衡二叉树除了插人操作，还有更新和删除操作，不过这和插人没有本质的区别，都是通过左旋或者右旋来完成的。因此对一棵平衡树的维护是有一定开销的， 不过平衡二叉树多用于内存结构对象中，因此维护的开销相对较小。
            
          • 插入操作：插入后必须保证叶子结点中的记录依然是有序的
            
            以这个B+树为例
            
            
            
            
            InnoDB存储引擎的PageHeader中有以下几个部分用来保存插入的顺序信息。通过这些信息InnoDB存储引擎可以决定向左还是向右进行页的分裂，同时决定将分裂点记录为哪一个
            
            以这个B+树为例
            
            
          • 删除操作：B+树使用最小值可设为50%的填充因子fill factor来控制树的删除变化。同样和插入操作一样的是删除后要保证叶子结点中的记录依然排序
            
          • B+树为了保持平衡（因为B+树首先是一颗二叉搜索树或者平衡二叉树）可能要进行大量的拆分页操作这就导致了很多磁盘的操作（由于B+树主要用于磁盘），这肯定不好呀，所以得尽量减少页的拆分操作，用旋转等操作
            • B+树的旋转操作：当叶子节点满了而这个叶子结点的兄弟节点没有满时，这时先别着急分页而是将记录移到所在页的兄弟节点上面，通常左兄弟会首先被检查去用作旋转操作
      • 总结：
        
        • 二分查找树（二叉查找树）：一个非线形且天然适合二分查找的数据结构【二叉查找树的特点是一个节点的左子树的所有节点都小于这个节点，右子树的所有节点都大于这个节点，这样我们在查询数据时，不需要计算中间节点的位置了，只需将查找的数据与节点的数据进行比较。】
          
          • 构造一颗二分查找树（二叉查找树）过程就是：找到所有二分查找中用到的所有中间节点，把他们用指针连起来，并将最中间的节点作为根节点。就够造出来了一颗二分查找树。
            
          • 二叉查找树解决了插入新节点的问题，因为二叉查找树是一个跳跃结构，不必连续排列。这样在插入的时候，新节点可以放在任何位置【只要保证左<根<右就行】，不会像线性结构那样插入一个元素，所有元素都需要向后排列。【二叉查找树解决了连续结构插入新元素开销很大的问题，同时又保持着天然的二分结构。】
          • 但是，二叉查找树有一个很大的问题：当每次插入的元素都是二叉查找树中最大的元素，二叉查找树就会退化成了一条链表，查找数据的时间复杂度变成了 O(n)，相当于就变成了一个瘸子。由于树是存储在磁盘中的，访问每个节点，都对应一次磁盘 I/O 操作（假设一个节点的大小「小于」操作系统的最小读写单位块的大小），也就是说树的高度就等于每次查询数据时磁盘 IO 操作的次数，所以树的高度越高，就会影响查询性能。二叉查找树由于存在退化成链表的可能性，会使得查询操作的时间复杂度从 O(logn) 升为 O(n)。而且会随着插入的元素越多，树的高度也变高，意味着需要磁盘 IO 操作的次数就越多，这样导致查询性能严重下降，再加上不能范围查询，所以不适合作为数据库的索引结构。
        • 自平衡二叉树：
          • 主要是在二叉查找树的基础上增加了一些条件约束：每个节点的左子树和右子树的高度差不能超过 1。也就是说节点的左子树和右子树仍然为平衡二叉树，这样查询操作的时间复杂度就会一直维持在 O(logn) 。
          • 哪怕每次插入的元素都是平衡二叉查找树中最大的元素，可以看到，它会维持自平衡
            
        • 红黑树：通过一些约束条件来达到自平衡，比如通过左旋右旋的操作来实现自平衡
          • 不管平衡二叉查找树还是红黑树，都会随着插入的元素增多，而导致树的高度变高，这就意味着磁盘 I/O 操作次数多，会影响整体数据查询的效率。根本原因是因为它们都是二叉树，也就是每个节点只能保存 2 个子节点 ，如果我们把二叉树改成 M 叉树不就可以提升性能了嘛，让树变矮了呀
        • B树：【B 树也称 B-树,全称为 多路平衡查找树 ，B+ 树是 B 树的一种变体。B 树和 B+树中的 B 是 Balanced （平衡）的意思。】。自平衡二叉树虽然能保持查询操作的时间复杂度在O(logn)，但是因为它本质上是一个二叉树，每个节点只能有 2 个子节点，那么当节点个数越多的时候，树的高度也会相应变高，这样就会增加磁盘的 I/O 次数，从而影响数据查询的效率。为了解决降低树的高度的问题，后面就出来了 B 树， B 树不再限制一个节点就只能有 2 个子节点，而是允许 M 个子节点 (M>2)，从而降低树的高度。
          
          • B 树的每一个节点最多可以包括 M 个子节点，M 称为 B 树的阶，所以 B 树就是一个多叉树。
          • B 树每个节点最多有 （M-1个）数据和最多有 （M个）子节点，超过这些要求的话，就会分裂节点
          • 但是 B 树的每个节点都包含数据（索引+记录），而用户的记录数据的大小很有可能远远超过了索引数据，这就需要花费更多的磁盘 I/O 操作次数来读到「有用的索引数据」；而且，在我们查询位于底层的某个节点（比如 A 记录）过程中，「非 A 记录节点」里的记录数据会从磁盘加载到内存，但是这些记录数据是没用的，我们只是想读取这些节点的索引数据来做比较查询，而「非 A 记录节点」里的记录数据对我们是没用的，加载进来没有用的记录数据不仅增多磁盘 I/O 操作次数，也占用内存资源；另外，如果使用 B 树来做范围查询的话，需要使用中序遍历，这会涉及多个节点的磁盘 I/O 问题，从而导致整体速度下降。所以，B+树应运而生
          • B 树& B+树两者有何异同呢？
            
        • B+树
    • 哈希索引（MySQL中的Memory引擎表的默认索引类型）：基于哈希表实现，只有精确匹配索引所有列的查询才能使索引生效。
      • 底层工作原理：对于每一行数据存储引擎都会对对所有的索引列计算一个哈希码（hash code)(哈希索引将所有的哈希码存储在索引中，同时在哈希表中保存指向每个数据行的指针)。通过键(key)即可快速取出对应的值(value)，因此哈希表可以快速检索数据（接近 O（1））
        
        • InnoDB引擎有一个特殊的功能叫做“自适应哈希索引”。当InnoDB注意到某些索引值被使用的非常频繁时，他会在内存中基于B-Tree索引之上再创建一个哈希索引，这样就让B-Tree索引也具有哈希索引的一些优点，比如快速的哈希查找。
      • Memory引擎是支持非唯一哈希索引的，意思就是如果多个列的哈希值相同，索引会以链表的方式存放多个记录指针到同一个哈希条目中
      • InnoDB 存储引擎支持的哈希索引是自适应的，InnoDB 存储引擎会根据表的使用情况自动为表生成哈希索引，不能人为干预是否在一张表中生成哈希索引。
      • 哈希索引优缺点：
        
      • 创建自定义哈希索引（有时候需要索引很长的字符列，这会让索引变得大且慢，所以可以用很多策略解决大而慢这个问题，其中的一个策略就是模拟哈希索引）：在B-Tree基础上创建一个伪哈希索引，虽然说还是使用B-Tree进行查找但是它使用哈希值而不是键本身进行索引查找，咱们需要做的就是在查询的WHERE子句中手动指定使用哈希函数，举个例子：

        CREATE TABLE hashdemo(
        	first_name VARCHAR(50) NOT NULL,
        	last_name VARCHAR(50) NOT NULL,
        	KEY USING HASH(first_name)
        )ENGINE=MEMORY;
        

        
        

然后呢，老规矩，买一赠一：

另外就是几个小问题：

• 前缀索引（决定前缀的合适长度）
  
  
  
• 索引下推（MySQL 5.6 引入的索引下推优化（index condition pushdown)， 可以在索引遍历过程中，对索引中包含的字段先做判断，直接过滤掉不满足条件的记录，说白了索引下推减少回表次数。）：如果存在某些对索引的列的判断条件时，MySQL 将这一部分判断被索引的列的条件传递给存储引擎，然后由存储引擎通过判断索引是否符合 MySQL 服务器传递的条件，只有当索引符合条件时才会将数据检索出来返回给 MySQL 服务器
  
• 什么情况下不走索引？，也就是索引失效（补充框图），
  • 除了下面常见的索引失效场景，实际过程中，可能会出现其他的索引失效场景，这时我们就需要查看执行计划，通过执行计划显示的数据判断查询语句是否使用了索引。
    • 执行计划：
      
  • 常见的索引失效场景有以下几种情况：
    • 最左前缀法则（带头索引不能死，中间索引不能断）
      
      
      
      • 对主键字段建立的索引叫做聚簇索引，对普通字段建立的索引叫做二级索引。那么多个普通字段组合在一起创建的索引就叫做联合索引，也叫组合索引。创建联合索引时，我们需要注意创建时的顺序问题，因为联合索引 (a, b, c) 和 (c, b, a) 在使用的时候会存在差别。联合索引要能正确使用需要遵循最左匹配原则，也就是按照最左优先的方式进行索引的匹配。
    • 不能继续使用索引中范围条件（bettween、<、>、in等）右边的列
    • 导致索引失效而转向全表扫描：【执行计划中的 type=ALL 就代表了全表扫描，而没有走索引。】
      
      • 索引字段上使用（！= 或者 < >）判断时，会导致索引失效而转向全表扫描
      • 索引字段上使用 is null / is not null 判断时，会导致索引失效而转向全表扫描
      • 索引字段使用like以通配符开头（‘%字符串’）时，会导致索引失效而转向全表扫描，也是最左前缀原则
        • 对索引使用左或者左右模糊匹配，也就是 like %xx 或者 like %xx% 这两种方式都会造成索引失效。原因在于索引 B+ 树是按照「索引值」有序排列存储的，只能根据前缀进行比较。查询的结果可能是多个，不知道从哪个索引值开始比较，于是就只能通过全表扫描的方式来查询。
          
          • 把%放前面，并不走索引
            
          • 把% 放关键字后面，还是会走索引的
            
      • 索引字段是字符串，但查询时不加单引号，会导致索引失效而转向全表扫描
      • 索引字段使用 or 时，会导致索引失效而转向全表扫描
        • WHERE 子句中的 OR语句，只要有条件列不是索引列，就会导致索引失效而转向进行全表扫描。【在 WHERE 子句中，如果在 OR 前的条件列是索引列，而在 OR 后的条件列不是索引列，那么索引会失效。】
          
          • 当 where 查询操作符左边为字符类型时发生了隐式转换，那么会导致索引失效，造成全表扫描效率极低。
      • 不要在索引上做任何操作（计算、函数、自动/手动类型转换），不然会导致索引失效而转向全表扫描
        • 对索引进行函数/表达式计算，因为索引保持的是索引字段的原始值，而不是经过函数计算的值，自然就没办法走索引
          • 不过，从 MySQL 8.0 开始，索引特性增加了函数索引，即可以针对函数计算后的值建立一个索引，也就是说该索引的值是函数计算后的值，所以就可以通过扫描索引来查询数据。
          • 在查询条件中对索引进行表达式计算，也是无法走索引的。
            
        • 对索引进行隐式转换相当于使用了新函数：【MySQL 在遇到字符串和数字比较的时候，会自动把字符串转为数字，然后再进行比较。】
          • 如果索引字段是字符串类型，在条件查询中，输入的参数是整型的话，你会在执行计划的结果发现这条语句会走全表扫描。
            • 在写 SQL 时一定要养成良好的习惯，查询的字段是什么类型，等号右边的条件就写成对应的类型。特别当查询的字段是字符串时，等号右边的条件一定要用引号引起来标明这是一个字符串，否则会造成索引失效触发全表扫描。
          • 当操作符左右两边的数据类型不一致时，会发生隐式转换。
          • 但是如果索引字段是整型类型，查询条件中的输入参数即使字符串，是不会导致索引失效，还是可以走索引扫描。

灵魂拷问：

• 使用索引查询一定能提高查询的性能吗？
  • 通常通过索引查询数据比全表扫描要快。但是我们也必须注意到它的代价。
  • 使用索引查询不一定能提高查询性能，索引范围查询(INDEX RANGE SCAN)适用于两种情况:
    • 基于一个范围的检索，一般查询返回结果集小于表中记录数的30%
    • 基于非唯一性索引的检索。
  • 索引需要空间来存储，也需要定期维护， 每当有记录在表中增减或索引列被修改时，索引本身也会被修改。 这意味着由于索引的存在，每条记录的INSERT，DELETE，UPDATE将为此多付出4，5 次的磁盘I/O。 因为索引需要额外的存储空间和处理，那些不必要的索引反而会使查询反应时间变慢
    • 换句话说，数据量大时增删改速度变慢，因为要保持数据库底层的形状性质等，查变快
• update 没加索引会怎么样【`基于 InnoDB 存储引擎，且事务的隔离级别是可重复读``】：执行一条 update 语句修改数据库数据的时候，where 条件没有带上索引。【在 update 语句的 where 条件没有使用索引，就会全表扫描，于是就会对所有记录加上 next-key 锁（记录锁 + 间隙锁），相当于把整个表锁住了。】
  
  
  • InnoDB 存储引擎的默认事务隔离级别是「可重复读」，但是在这个隔离级别下，`在多个事务并发的时候，会出现幻读的问题，------>
    • 因此 InnoDB 存储引擎自己实现了行锁，通过 next-key 锁（记录锁和间隙锁的组合）来锁住记录本身和记录之间的“间隙”，防止其他事务在这个记录之间插入新的记录，从而避免了幻读现象。
  • 避免业务停止的方法：
    
• InnoDB 为什么设计 B+ 树索引，也可以说为啥要有B+树索引：有两个考虑因素：
  • InnoDB 需要执行的场景和功能需要在特定查询上拥有较强的性能。
    • 哈希索引虽然能提供O（1）复杂度查询，但对范围查询和排序却无法很好的支持，最终会导致全表扫描。
    • B 树能够在非叶子节点存储数据，但会导致在查询连续数据可能带来更多的随机 IO
      • B+ 树的所有叶节点可以通过指针来相互连接，减少顺序遍历带来的随机 IO
  • CPU 将磁盘上的数据加载到内存中需要花费大量时间。
• MySQL InnoDB 选择 B+tree 作为索引的数据结构，,而不是 B-树、二叉树Hash索引结构这些呢？【从数据结构的角度出发以及还要考虑磁盘 I/O 操作次数，因为 MySQL 的数据是存储在磁盘中的嘛。】
  
  • 1.B+ 树只在叶子结点储存数据，非叶子结点不存具体数据，只存 key，查询更稳定，增大了广度，而一个节点就是磁盘一个内存页，内存页大小固定。那么相比 B 树【B 树 的非叶子节点也要存储数据】，B- 树这些，B+可以存更多的索引结点，宽度更大，树更矮，节点小，拉取一次数据的磁盘 IO 次数少【 B+Tree 的单个节点的数据量更小，在相同的磁盘 I/O 次数下，就能查询更多的节点。】，并且 `B+ 树只需要去遍历叶子节点就可以实现整棵树的遍历。
    • 对于有 N 个叶子节点的 B+Tree，其搜索复杂度为O(logdN)，其中 d 表示节点允许的最大子节点个数为 d 个。在实际的应用当中， d 值是大于100的，这样就保证了，即使数据达到千万级别时，B+Tree 的高度依然维持在 3-4 层左右，也就是说一次数据查询操作只需要做 3~4 次的磁盘 I/O 操作就能查询到目标数据；而二叉树的每个父节点的儿子节点个数只能是 2 个，意味着其搜索复杂度为 O(logN)，这已经比 B+Tree 高出不少，因此二叉树检索到目标数据所经历的磁盘 I/O 次数要更多。
      • B+ 树与 B 树差异：
        • B+ 树叶子节点（最底部的节点）才会存放实际数据（索引+记录），非叶子节点只会存放索引；B 树的查询波动会比较大，因为每个节点即存索引又存记录
          
        • 插入和删除效率：
          • B+ 树有大量的冗余节点，这样使得删除一个节点的时候，可以直接从叶子节点中删除，甚至可以不动非叶子节点，这样删除非常快，B+ 树的插入也是一样，有冗余节点，插入可能存在节点的分裂（如果节点饱和），但是最多只涉及树的一条路径。而且 B+ 树会自动平衡，不需要像更多复杂的算法，类似红黑树的旋转操作等。因此，B+ 树的插入和删除效率更高。
          • B 树则不同，B 树没有冗余节点，删除节点的时候非常复杂，比如删除根节点中的数据，可能涉及复杂的树的变形
        • 范围查询：
          • B 树和 B+ 树等值查询原理基本一致，先从根节点查找，然后对比目标数据的范围，最后递归的进入子节点查找。
            
        • B+ 树中所有索引都会在叶子节点出现，叶子节点之间构成一个有序链表；
        • 非叶子节点的索引也会同时存在在子节点中，并且是在子节点中所有索引的最大（或最小）。
        • B+ 树中非叶子节点中有多少个子节点，就有多少个索引；
  • 2.在数据库中基于范围的查询是非常频繁的， B+Tree 叶子节点采用的是双链表连接，适合 MySQL 中常见的基于范围的顺序查找，效率更高，而 B 树无法做到这一点。
  • 3.Hash 在做等值查询的时候效率贼快，搜索复杂度为 O(1)。但是 Hash 表不适合做范围查询，它更适合做等值的查询，这也是 B+Tree 索引要比 Hash 表索引有着更广泛的适用场景的原因。
    • 或者说为什么MySQL 没有使用其作为索引的数据结构的原因是：
      
      • Hash 冲突问题 ：，不过对于数据库来说这还不算最大的缺点
      • Hash 索引不支持顺序和范围查询(Hash 索引不支持顺序和范围查询是它最大的缺点： 假如我们要对表中的数据进行排序或者进行范围查询，那 Hash 索引可就不行了。
• 普通索引和唯一索引该怎么选择：唯一索引更加适合查询的场景,普通索引更适合插入的场景（由于唯一索引用不上 change buffer 的优化机制，因此如果业务可以接受，从性能角度出发建议优先考虑非唯一索引。）
  • 查询
    • 当普通索引为条件时查询到数据会一直扫描,直到扫完整张表，这不把人等着急死了
    • 当唯一索引为查询条件时,查到该数据会直接返回,不会继续扫表
  • 更新
    • 普通索引会直接将操作更新到 change buffer 中,然后结束
      • MySQL 的 change buffer：唯一索引的更新就不能使用 change buffer，实际上也只有普通索引可以使用MySQL 的 change buffer。
        • 当需要更新一个数据页时，如果数据页在内存中就直接更新
        • 如果这个数据页还没有在内存中的话，在不影响数据一致性的前提下，InnoDB 会将这些更新操作缓存在 change buffer 中。这样就不需要从磁盘中读入这个数据页了，在下次查询需要访问这个数据页的时候，将数据页读入内存，然后执行 change buffer 中与这个页有关的操作。通过这种方式就能保证这个数据逻辑的正确性。
        • MySQL 的 change buffer适用场景：
          • 对于写多读少的业务来说，页面在写完以后马上被访问到的概率比较小，此时 change buffer 的使用效果最好。这种业务模型常见的就是账单类、日志类的系统。
          • 假设一个业务的更新模式是写入之后马上会做查询，那么即使满足了条件，将更新先记录在 change buffer，但之后由于马上要访问这个数据页，会立即触发 merge 过程。这样随机访问 IO 的次数不会减少，反而增加了 change buffer 的维护代价。
    • 唯一索引需要判断数据是否冲突

巨人的肩膀：
高性能MySQL
Mysql技术内幕
Mysql官方文档
小林coding
捡田螺的小男孩老师的索引常考题目：为什么选择B+树作索引结构、一次索引搜索过程…