1. 页大小默认是4kb
  2. mysql一次读取4页—->16kb
  3. 🌟B-树、B+树、B*树、R树
  4. mysiam默认多一次IO，因为他的索引是非聚簇索引—->所以一般选择innodb作为默认的存储引擎
  5. 一个索引一棵树
  6. 索引树一般情况下是三层，有可能会达到四层（数据量更大）
  7. 问题：MySQL查询效率到底快不快
    1. 是非常快的
    2. 为什么现在看起来慢？
      1. 卡在IO问题上
      2. 卡在并发请求上了—->并发请求多的话意味着MySQL里面会有n多个缓存，这时可能会造成内存不够，会造成频繁地替换缓存，这样会造成效率变低（虚拟内存的替换策略）—->吞吐量
      3. 缓存在8版本中剔除了，8中已经没有查询缓存了—->因为缓存要经常替换，所以效率比较低，利用率也比较低，所以一般不用
  8. 用innodb的时候建议一定要指定主键，不指定的话可能会有问题，因为自动生成的row_id只有6位
  9. Oracle中将row_id显式地展示出来了，在MySQL没有显式展示出来

image.png

索引的优点

  1. 大大减少了服务器需要扫描的数据量—->减少了IO量
  2. 帮助服务器避免排序（filesort）和临时表
  3. 将随机IO变成顺序IO—->kafka的例子
    1. 顺序IO快，随机IO慢
    2. 现在IO慢的原因就在于随机IO
  4. MySQL会默认帮我们建索引，他为主键来建索引
    1. 错误认知
    2. 数据库建索引不是给主键建的，而是给唯一键建的
    3. 主键是唯一且非空的，实际上是给唯一键建的而不是给主键建的
  5. 索引类型：主键索引、唯一索引、普通索引、全文索引、组合索引
  6. 普通索引就是给普通列创建一个索引
  7. 全文索引（FullText？？？）：一把建在CHAR、VARCHAR、TEXT这些类型上，一般用的很少
  8. 组合索引：（name，age）经常查询—->组合查询；业务中查询的时候都是name+age作为条件组合查询的，此时可以把这两个类组合起来创建一个索引
  9. 面试时的技术名词：
    1. 回表：不回表就叫做覆盖—->数据库查询中一般是不查询整行的

一般只查询几个字段，id in(……)其中in中是子查询，这时就相当于
是回表了

  1. 覆盖索引：避免了回表的过程就叫索引覆盖，能用覆盖索引要尽量用覆盖索引，在组合索引的时候用的比较多

  2. 最左匹配：多个列同时创建索引的时候，比如很多是按name和age来查询的，此时按这两个列建立索引，这时的索引中包含name和age，写sql语句的时候where name=？ and age=？这时会按照索引来查询—->即先匹配name再匹配age；如果是where age=？（将name跨过去了），此时就不能再用这个索引了—->必须现有最左边才能查右边，如果最左边没有，那么右边查不了

    1.   - 优化：
    2.      - 将组合索引中name和age的顺序交换，并且where后面name和age的顺序交不交换是无所谓的，sql的优化器会自动调整这个顺序以确保使用这个索引起作用
    3.      - 给age再单独创建一个索引，这样就多了一个索引，是他们走不同的索引。
    4.      - 显然第一种方式效率高，第二种方式效率低。
    5.   - **类似于地址中的省市区的概念，先给出一个省之后才可以决定市最后才可以去决定区**
    6.   - **只有组合索引才有最左匹配的概念，没有组合索引，最左匹配是没有概念的**
    7.   - **组合索引先匹配第一个字段，再去匹配第二个字段（在第一个字段过滤完的基础上去过滤第二个字段？？？❓🤔）**
    8.   - **假如既有**`where name=？ and age=？`**又有**`where age=？`**又有**`where name=？`**怎么办？**
    9.      - **有两种方案：1.(name，age)和age；2.(age，name)和name；**
    10.      - **该选哪一种？因为在更新数据的时候索引也要一起更新，所以需要从索引字段的大小来考虑，越小效率越高，越大意味着IO次数可能越多**
    11.      - **所以应该选方案1，因为age的长度更小**
    12.   - 假如两个索引单独建会涉及到索引合并

  3. 🌟索引合并：在低版本的MySQL中没有这个功能，默认情况下只能用一个索引；高版本会自动帮你做组合，做组合是优化器来完成的，多了一个优化的步骤，这时效率不一定会高—->**所以不一定要用单独的，不一定用单独的效率就高，这时还不如直接用组合索引自己进行索引合并呢**（如何避免？？？❓）

  4. 索引下推
    1. 谓词下推：在优化器进行编辑的时候进行的谓词下推select t1. name t2.name from t1 join t2 on t1.id = t2.id有几种执行方式？？？
    2. 这种sql语句可以分多钟情况进行讲解，如下两种……
      1. 将两表的所有字段进行连接，从连接之后的结果取出两个name
      2. 先取出两个name和两个id，再对过滤过字段的表进行连接得到结果
    3. 相当于service放到dao层
    4. 索引下推的前提一定是组合索引，不是组合索引的话就不能做索引下推了
    5. 不是组合索引，那么索引中就没有这个数据了，必然不可能进行过滤了
    6. 索引甚至可以单独拿一门课
    7. 索引这块还有一个页分裂和页合并的过程—->优化的时候会讲（如何避免？？？❓）

索引匹配方式

全值匹配

  • 和索引中的所有列进行匹配
  • mysql官网上默认的几张表！！！—->官网上的所有sql语句都是基于这几张表的

当一个列是字符型且比较长的时候并且要作为查找条件建立索引的时候，可以通过CRC（循环冗余哈希）映射为一个整型值，给这个整型值建立索引—->一种哈希的思想

  • 这样可以降低索引树的层数
  • 降低IO的次数

当存储索引需要占据很大的存储空间的时候可以用哈希索引或者哈希算法（CRC32、自己写的哈希算法）

  • memory几乎不用，很少用，公司中没用
  • memory速度比较快，但是唯一的缺点就是不能持久化，重启就没了
  • 分区的时候用hash和list有什么区别？

memory存储引擎什么时候用？

  • 可以做缓存？
  • 使用场景：定义了一些非常大的常量池、常量表（这个数量就相对大一些100w条记录，就将其当作缓存来使用），定义好之后就不会去改他，固定好了；但是日常查询的时候经常要从常量池中取这些值，此时就可以将这个常量池放到memory里面
  • 一般上面的情况很少，因为做常量池的时候不会特别大，有这样的需求可以放在memory中，将memory当作缓存来用；每次从内存中加载而不从硬盘中加载了，这样能提高效率，在使用的过程中不建议对其中的值进行频繁地修改
  • 各地行政区编码可以用这个
  • 占内存但是速度快

组合索引

image.png
第一个条件只能使用a和b，因为b是范围查询，b是范围查找的话后面所有的索引项都会被忽略，不会进行匹配（不管后面是不是索引项）—->因为建立索引的时候只能根据一个精确值进行匹配，而b这里是一个范围，一个b可能对应n多个c，这个没法判断了，就算后面是索引列也会被忽略掉，只使用a和b

  • 组合索引中使用了范围查找时，后面的失效！！！
  • 组合索引要考虑的问题：
    • 组合索引使用时的效率问题
    • 组合索引占用空间的问题—->尽量选择长度比较小的列，创建好之后占用的存储空间比较少


mysql的索引B+树中的每一次拆分和合并都要涉及到IO操作，拆分和合并操作多的话会造成效率的降低

  • 资源和性能会浪费在这上面
  • 会对查询的效率也造成影响
  • 这就是所谓的页分裂和页合并
  • 有点类似于大数据中的region分割—->等分过程，对数据做了一个纵向的切分（b+树节点的切分）异曲同工之妙—->region server hbase
  • 大数据和架构底层用的知识点都是差不多的，要将知识点关联起来


聚簇索引可能导致整个表扫描变满，尤其是行比较稀疏的时候，或者由于页分裂导致数据存储不连续的时候

场景：有一批大量的数据，要将这批数据整体的往mysql上进行迁移（移动）

  • 在mysql中默认会帮我们给主键和唯一键创建索引
  • 在进行数据移动之前，可以把默认创建索引这个属性给关掉off
  • 将数据文件先拷过来
  • 拷过来之后，再将索引打开，此时也会帮我们建索引
  • 好处：一边导数据，一边建索引，索引可能会频繁地进行更新，这个效率是极低的—->一次专注于一件事情比较好，可以减少很多的上下文切换时间，否则一会移动数据，一会建索引，这样效率会比较低
  • 可以先将索引先关掉，将数据移动完之后，再将索引打开
  • DBA做的？？？别甩锅

覆盖索引

覆盖索引要存储索引列的值
索引树的叶子节点中出现select的字段就是覆盖索引

myisam也有覆盖索引

  • 他的覆盖索引也是要去文件中查找
  • 但是那个地址值是不一样的，找到地址值再向下查找（myisam中还将主键进行了单独的存储？？？？）
  • myisam中对数据总数是进行了单独存储的count
  • 数据由操作系统缓存，所以在使用的时候要进行一次系统调用，这样会降低效率
  • 公司中用得不是很多

    memory没有覆盖索引

索引优化的小细节

前缀索引（不同的位数对索引扫描效率的影响是非常高的）—->选择性

  • 使用某个列开始的部分字符串建立索引可以大大节约索引空间，从而提高索引效率，但是这会降低索引的选择性
  • 选择性：在使用前缀索引的时候会出现原来不重复但是前缀重复的字符串，重复了就不符合索引的本意了，这样效率就变低了
  • 要达到取前几个的效果看起来和取全部的效果一模一样
  • 选择性是指不重复的索引值和数据表数据总数的比值（要原来是唯一键才有意义？？？），范围在0 - 1之间，选择性越高，索引的效率越高
    • 100w行数据的前3个字符都是一样的，此时前缀索引建立在前三个字母上的效率就低多了，因为所有的值都一模一样了，所有的数据都聚集到一起了，这时的索引就没有意义了—->这时就和完全扫描查找数据一样了
  • 一般情况 下某个列前缀的选择性也是足够高的，足以满足查询的性能,但是对应BLOB,TEXT.VARCHAR类型的列，必须要使用前缀索引，因为mysq|不允许索引这些列的完整长度,使用该方法的诀窍在于要选择足够长的前缀以保证较高的选择性，通过又不能太长。

image.png

  • 重复值过多的话，会造成在索引块中频繁地顺序查找，这样会降低效率！

show index中的字段—->Cardinality基数

  • 基数在OLAP（联机分析处理系统）中很重要，是必问的
  • 进行复杂情况查询时，有n多表、n多列，这时统计时，很难进行统计；所以一般情况会（列中唯一值很多的时候，分析很麻烦，因为要做很多次关联—->值的种类越多的列join起来效率越低，种类越少效率越高）
  • 做OLAP的时候，基数必问
  • OLAP：是对历史数据进行整体分析的
  • 数据库叫OLTP（联机事务处理），要求比较高的即时性，嵌在业务系统中
  • 电商网站10年积累的数据，对这些数据进行分析应对明年的广告投放和一些决策，做这种事情的系统叫做OLAP系统
  • 要做这个分析的话，需要拿a表和b表（不是大数据，和大数据没关系，在没有大数据的时候OLAP也有，是一个独立的概念）做一个join操作时，要做一个选择
    • b表中有一个c列，1000w行中重复的值只有两个
    • b表中有一个d列，1000w行中重复的值有1000个
    • 此时要用a表joinb表，join条件要么是c，要么是d（假设两种都可以）
    • 选择join哪一列（c or d）—->一定是c，就算用最老的方式nested join（循环嵌套的方式）那么就要匹配1000次；假如用b列那么就只要匹配两次（两次效率一定会高很多）
    • 所以意味着要对这两列的值进行一个基数统计，基数统计就是统计某一列中唯一的值大概有多少个（有多少个唯一值—->去重之后的数量）这就是基数的概念count(distinct ×××)
  • 由于上面的需求所以需要我们对某一列不重复的值做一个计数统计，统计一下单列中去重之后的唯一的值（基数）大概有多少个？

image.png

  • 没有执行sql语句是没办法预估他的执行时间的，但是可以计算基数的大概值或者近似值—->10数量级的值和1000数量级的值，一对比就知道10数量级的值更快
  • 所以不需要准确值，所以只需要一个大概的近似值，通过对应的近似值做一个最基本的判断

image.png

  • 做这个近似值估算的时候可以用HyperLogLog算法
  • HyperLogLog算法
    • HyperLogLog算法经常在数据库中被用来统计某一字段的Distinct Value (下文简称DV)，比如Redis的HyperLogLog结构
    • redis、mysql以及大数据中的那个麒麟Kylin也用到了

image.png

  • 他们这些底层用的思想都是一样的东西，核心点没有任何的变化
  • 把HyperLogLog算法看一下

n多种技术只要底层用到相似的点（知识点）了，那么采用的方式大部分都一模一样，我们需要做的就是找这些技术点的通性—->最重要的是看到技术名词的条件反射（什么东西和什么东西一样）

索引排序

  • 索引本身是有序的，直接拿索引就行
  • 利用索引扫描来进行排序
  • Extra中有using filesort就表示用的是文件排序，而不是索引排序（用文件来进行排序）
  • 利用索引进行排序的时候也要避免回表，因为回表会产生大量的随机IO，这样会大幅度地降低效率
  • mysq有两种方式可以生成有序的结果:通过排序操作或者按索引顺序扫描,如果explain出来的type列的值为index,则说明mysq|使用了索弓|扫描来做排序
  • 扫描索引本身是很快的，因为只需要从一条索弓|记录移动到紧接着的下一条记录。但如果索弓|不能覆盖查询所需的全部列，那么就不得不每扫描一条索引记录就得回表查询一次对应的行，这基本都是随机I0,因此按索引顺序读取数据的速度通常要比顺序地全表扫描慢
  • mysq可以使用同一个索引即满足排序，又用于查找行，如果可能的话，设计索引时应该尽可能地同时满足这两种任务。
  • 只有当索引的列顺序和order by子句的顺序完全一致, 并且所有列的排序方式都一样时, mysql才 能够使用索引来对结果进行排序，如果查询需要关联多张表，则只有当orderby子句**引用的字段全部为第一张表时**， 才能使用索引做排序。order by子句和查找型查询的限制是一样的， 需要满足索引的最左前缀的要求，否则，mysq|都需要执行顺序操作（**效率低**），而无法利用索引排序
  • 使用索引排序的两种条件：
    • where条件和order by里面的列能够组成一个最左前缀匹配的话，就会使用索引排；当第一列是一个范围的时候就没办法使用索引排序了
    • 如果order by的顺序和索引中对应的升序、降序不一样的话，也没办法使用索引排序
    • 排序的列不是索引列的时候会产生using filesort
  • 排序的注意点：
    • 如果排序列中是索引列的话，一定要用，效率会高一些
    • 如果不是索引的话，会使用文件或者临时表空间来进行排序，IO量很大，性能很低
  • 以后使用索引扫描的时候，列最好要指定主键列
  • 组合索引时的注意点：
    • 按组合索引条件方式使用where条件
    • where条件中的where条件查找后面的索引会失效
      • 可以使用范围查找，但是使用范围查找之后，范围条件后面的所有组合索引列都会失效（失效了—->后面就算有索引列也不会按照索引列进行检索）

image.png

  • 创建索引的时候，默认是升序的排序
    • 后面的排序方式要和索引一模一样
    • 和索引不一样就没办法使用这个条件了
  • 以下两种情况的区别：
    • 使用索引条件查询：

image.png
image.png
image.png
image.png

  • 使用where条件查询

image.png
image.png
image.png

union all、in、or的索引优化

  • 推荐用in，in的效率更高一些（数据量大的时候in的效率是要远远高于or的效率的）
  • 本质上这三种方式都是一样的，可以达到相同的效果
  • or会不会转换成in，会不会被优化为in？？？
    • 关键看sql语句中有没有in
  • 推荐用in，为什么推荐用in？？？
    • 不是因为只有一条sql语句（短，其他的也是一条sql语句）
    • in有个数限制吗？？？
      • Oracle中的in限制1000个值
      • 一般情况下，in中包含的是一个子查询
      • 先子查询和直接写值是不一样的，因为先子查询还会包含另外的一个过程
    • 第二种方式和第三种方式一样吗？？？
    • 首先，union all分了两阶段查询，这肯定是不太好的
    • 在公司中必须使用全集方式的时候，有union和union all，建议使用union all而不要使用union，因为union本身包含了distinct过程，而去重的过程是一个比较麻烦的过程，会降低效率；虽然有重复数据但是对查询没有什么影响；用union all不会有过滤数据那样的东西
    • union all不考虑
    • 虽然后两种的执行计划一模一样，但是可以看性能监控找区别

image.png
image.png

  • 这里没有缓存，要想用缓存得配置一个查询缓存，这里没有配置，这里的mysql安装好之后什么都没有开启
  • 为什么in的效率高？
    • 因为用or匹配的时候需要每次先匹配第一个判断条件，匹配完之后再匹配第二个判断条件—->要把两个条件进行判断
    • 而用in的时候，只需要在当前in的那个集合中做一些筛选就可以了
    • 而在Oracle中因为in有1000❓的限制，所以超过1000❓的时候就不推荐用in了（自己测一些）
    • 其实效率相差不太多？？？❓❓❓

image.png

  • exits方式（查询的时候不是建表的时候）
    • exits的规则：exits在使用的时候相当于是一个双层的for循环，外面那张表的时候会对其中的每一条记录进行查询，当查询完之后，只要exists后面的子查询有结果，就意味着当前sql语句已经满足了，那么就会输出结果—->下面的例子中每次exits都会有值，所以会把主查询中的每一条结果都查出来（不管子查询是什么，只要有值就满足了where条件）—->可以通过添加限制条件解决这个问题（给表加别名），但是因为and的优先级高于or，所以此时查出来还是整张表（见下图）—->调整优先级之后（加括号），可以完成查询
    • 这就是exits的用法：通过外层循环的某些值来限制内层循环的执行
    • exits里面写的必须是一个子查询
    • exits在使用的时候经常是跟in来替换的
    • exits写起来费劲？？？容易拼错🤣，写起来比较麻烦，但是在某些应用场景中exists是非常好用的
    • 什么时候用join（多表连接查询的时候），什么时候用exists（在筛选值的时候要写一个丰富的查询语句，但是这个数据是来自于另一张表，而另一张表中的数据不会凸显到查询字段中）
    • exists确实比较快，但是想查询子查询表中的某些数据的话，就不能用exists，因为exits并不能查询子查询中的一些字段
    • in和exists要做选择的话，exists推荐使用！仅此而已，一般写sql语句的时候不会写exists，因为觉得exists太麻烦了，但实际上exists的效率还是很高的！！！

image.png
image.png
image.png
image.png
image.png

范围列可以用到索引，但是第一个范围之后的列就用不到索引了

  • (age,name)和(name.age)这两种索引存储空间的大小比较❓❓❓

轻质类型转换会全表扫描

  • mysql中支持int和varchar的自动类型转换，Oracle中支持的没有这么好
  • 要保证类型的一致性，这样才能使用到索引，否则是全表扫描

数据区分度不高字段不宜建立索引

  • 基数低的列建索引反而会降低效率，因为修改数据的同时还要同步更新索引，而在使用索引扫描的时候反而不会有太大的提升，甚至会退化为全表扫描
  • 数据区分度=基数/列总数

列索引尽量不为null

  • 在MySQL中null不等于null，并且null在存储的时候要特殊处理，效率低；并且使用了null在进行匹配和索引的时候可能得到不符合预期的结果
  • 能不用null就尽量不用null，之前为null的直接修改为not null的话效率提升不高（一般没必要）

进行表连接的时候最好不要超过三张表，因为需要join的字段数据类型必须一致

  • 大表join大表没有太好的匹配方式—->分区运算，分区表，拆分成n多条语句执行
  • 分而治之的思想，不直接join；直接join对mysql性能影响很大；把无效的数据列用where过滤一遍之后效果会好一点
  • mapjoin：用内存中的数据去join硬盘上的数据
  • 大表join排序，排序方式order by、sort by、distribute by、classification by（hive中的排序方式，mysql只支持order by，分区表可以用其他的排序算法？？？），先分区保证小表有序再进行合并，这样计算复杂度会增加，IO会增加；要做一个衡量和选择！！！
  • block nested join只能一下子将驱动表的全部放入内存，因为mysql中没有进行offset偏移量的保存，所以不能分多次进入，但是hdfs可以分多次

image.png

  • 上面这两种inner join方式的结果一样，并且效率没有上面差别，基本一样（and的情况稍微快一点）
  • 用外连接的时候这两种结果就不一样了
  • 判断and和join，谁先执行，谁后执行；看and后面的条件有没有参与join的过程

image.png

date类型的修改后自动更新的两种策略！！！

表中字段名不是用单引号引起来的，而是用反引号引起来的！！！（不要搞错混淆！—->可以手工配置？？？❓）

一张表总共4个字段，建的索引是全量数据的时候肯定会走索引（type为index）—->表示你要查询的字段一定是在索引中的，所以不需要扫描全表，只需要扫描索引（文件）即可！！！（扫描索引并不是说要可以用到索引才会扫描索引，而是说只要字段位于索引中即可，他是不管顺序的！！！）

sqlite使用c语言写的（始祖数据库？），derby是jdk自带的数据库