MySQL知识点总结（下）

YBCarry_段松啓
204 阅读12分钟

四、索引

1、索引的作用

  • 索引就相当于字典中的目录，可以极大的提高数据库的查询效率。
  • 索引是一种特殊的查询表，数据库的搜索引擎可以利用它加速对数据的检索。索引可以是唯一的，创建索引允许指定单个列或者是多个列。

2、索引类型

  • 主键索引：不允许NULL
  • 唯一索引：允许NULL
  • 普通索引：可不唯一，可为NULL
  • 全文索引：主要用来查找文本中的关键字，而不是直接与索引中的值相比较。fulltext索引跟其它索引大不相同，它更像是一个搜索引擎，而不是简单的where语句的参数匹配。fulltext索引配合match against操作使用，而不是一般的where语句加like。它可以在create table，alter table ，create index使用，不过目前只有char、varchar，text 列上可以创建全文索引。值得一提的是，在数据量较大时候，现将数据放入一个没有全局索引的表中，然后再用CREATE index创建fulltext索引，要比先为一张表建立fulltext然后再将数据写入的速度快很多。
    • 全文索引存储引擎、数据类型的支持情况：
      • (1) MySQL 5.6 以前的版本，只有 MyISAM 存储引擎支持全文索引；
      • (2) MySQL 5.6 及以后的版本，MyISAM 和 InnoDB 存储引擎均支持全文索引;
      • (3) 只有字段的数据类型为 char、varchar、text 及其系列才可以建全文索引。
      • (4) 对于中文，可以使用 MySQL 5.7.6 之后的版本，或者第三方插件。

3、聚簇索引 与 非聚簇索引

  • 聚簇索引：将数据存储和索引放一块，找到索引即找到数据
  • 非聚簇索引：数据和索引分开存储，索引结构的叶子节点指向数据对应行

4、普通索引 与 唯一索引

  • 查询：
    • 当普通索引为条件时查询到数据会一直扫描,直到扫完整张表
    • 当唯一索引为查询条件时,查到该数据会直接返回,不会继续扫表
  • 更新：
    • 普通索引会直接将操作更新到 change buffer 中,然后结束
    • 唯一索引需要判断数据是否冲突
  • 所以唯一索引更加适合查询的场景,普通索引更适合插入的场景

5、索引的优缺点

  • 索引的优点
    • 可以大大加快数据的检索速度，这也是创建索引的最主要的原因。
    • 通过使用索引，可以在查询的过程中，使用优化隐藏器，提高系统的性能。
  • 索引的缺点
    • 时间方面：创建索引和维护索引要耗费时间，具体地，当对表中的数据进行增加、删除和修改的时候，索引也要动态的维护，会降低增/改/删的执行效率。
    • 空间方面：索引需要占物理空间。

6、索引结构默认使用B+Tree

  • (1) B+树的磁盘读写代价更低：B+树的内部节点并没有指向关键字具体信息的指针，因此其内部节点相对B(B-)树更小，如果把所有同一内部节点的关键字存放在同一盘块中，那么盘块所能容纳的关键字数量也越多，一次性读入内存的需要查找的关键字也就越多，相对IO读写次数就降低了。
  • (2) 由于B+树的数据都存储在叶子结点中，分支结点均为索引，方便扫库，只需要扫一遍叶子结点即可，但是B树因为其分支结点同样存储着数据，我们要找到具体的数据，需要进行一次中序遍历按序来扫，所以B+树更加适合在区间查询的情况，所以通常B+树用于数据库索引。
  • Hash结构：
    • 虽然可以快速定位，但是没有顺序，IO复杂度高；
    • 基于Hash表实现，只有Memory存储引擎显式支持哈希索引；
    • 适合等值查询，如=、in()、<=>，不支持范围查询；
    • 因为不是按照索引值顺序存储的，就不能像B+Tree索引一样利用索引完成排序；
    • Hash索引在查询等值时非常快；
    • 因为Hash索引始终索引的所有列的全部内容，所以不支持部分索引列的匹配查找；
    • 如果有大量重复键值得情况下，哈希索引的效率会很低，因为存在哈希碰撞问题。
  • 二叉树：
    • 树的高度不均匀，不能自平衡，查找效率跟数据有关（树的高度），并且IO代价高。
  • 红黑树：
    • 树的高度随着数据量增加而增加，IO代价高。

7、回表

  • 回表就是先通过数据库索引扫描出该索引树中数据所在的行，取到主键id，再通过主键id取出主键索引数中的数据，即基于非主键索引的查询需要多扫描一棵索引树。 image.png

8、索引下推

  • 如果存在某些被索引的列的判断条件时，MySQL将这一部分判断条件传递给存储引擎，然后由存储引擎通过判断索引是否符合MySQL服务器传递的条件，只有当索引符合条件时才会将数据检索出来返回给MySQL服务器。

9、覆盖索引

  • 覆盖索引（covering index）指一个查询语句的执行只用从索引中就能够取得，不必从数据表中读取，可以减少回表的次数。比如:
select id from t where age = 1;

id 为主键索引，age 为普通索引，age 这个索引树存储的就是逐渐信息，可以直接返回。

10、最左前缀原则

  • 最左前缀其实说的是，在 where 条件中出现的字段，「如果只有组合索引中的部分列，则这部分列的触发索引顺序」，是按照定义索引的时候的顺序从前到后触发，最左面一个列触发不了，之后的所有列索引都无法触发。
  • 比如有一个 (a,b,c) 的组合索引
where a = 1 and b = 1

此时 a,b 会命中该组合索引

where a = 1 and c = 1

此时 a 会命中该组合索引, c 不会

where b = 1 and c = 1

此时不会命中该组合索引

11、创建索引注意事项

  • 非空字段： 应该指定列为NOT NULL，除非你想存储NULL。在mysql中，含有空值的列很难进行查询优化，因为它们使得索引、索引的统计信息以及比较运算更加复杂。你应该用0、一个特殊的值或者一个空串代替空值；
  • 取值离散大的字段：（变量各个取值之间的差异程度）的列放到联合索引的前面，可以通过count()函数查看字段的差异值，返回值越大说明字段的唯一值越多字段的离散程度高；
  • 索引字段越小越好： 数据库的数据存储以页为单位一页存储的数据越多一次IO操作获取的数据越大效率越高。

12、索引创建的原则

  • (1) 最左前缀匹配原则，非常重要的原则，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的顺序可以任意调整。
  • (2) =和in可以乱序，比如a = 1 and b = 2 and c = 3 建立(a,b,c)索引可以任意顺序，mysql的查询优化器会帮你优化成索引可以识别的形式。
  • (3) 尽量选择区分度高的列作为索引，区分度的公式是count(distinct col)/count(*)，表示字段不重复的比例，比例越大我们扫描的记录数越少，唯一键的区分度是1，而一些状态、性别字段可能在大数据面前区分度就是0，那可能有人会问，这个比例有什么经验值吗？使用场景不同，这个值也很难确定，一般需要join的字段我们都要求是0.1以上，即平均1条扫描10条记录。
  • (4) 索引列不能参与计算，保持列“干净”，比如from_unixtime(create_time) = ’2014-05-29’就不能使用到索引，原因很简单，b+树中存的都是数据表中的字段值，但进行检索时，需要把所有元素都应用函数才能比较，显然成本太大。所以语句应该写成create_time = unix_timestamp(’2014-05-29’)。
  • (5) 尽量的扩展索引，不要新建索引。比如表中已经有a的索引，现在要加(a,b)的索引，那么只需要修改原来的索引即可。

13、索引失效场景

  • (1) 使用!=或者<>导致索引失效
  • (2) 查询语句参数的类型和数据库的类型不匹配
  • (3) 函数导致的索引失效（计算、函数、自动/手动类型转换）
  • (4) 运算符导致的索引失效（+，-，*，/，!）
  • (5) 索引字段使用or时
    • or导致索引是在特定情况下的，并不是所有的OR都是使索引失效，如果or连接的是同一个字段，那么索引不会失效，反之索引失效。
  • (6) 模糊搜索导致的索引失效(使用like以通配符开头)
  • (7) not in、not exists、is null、is not null导致索引失效

14、推荐使用自增 id 作为主键

  • (1) 普通索引的 B+ 树上存放的是主键索引的值，如果该值较大，会导致普通索引的存储空间较大；
  • (2) 使用自增 id 做主键索引新插入数据只要放在该页的最尾端就可以，直接按照顺序插入，不用刻意维护；
  • (3) 页分裂容易维护，当插入数据的当前页快满时，会发生页分裂的现象，如果主键索引不为自增 id，那么数据就可能从页的中间插入，页的数据会频繁的变动，导致页分裂维护成本较高。

五、执行过程

1、查询语句执行过程

  • (1) 通过连接器跟客户端建立连接
  • (2) 通过查询缓存查询之前是否有查询过该sql
    • 有则直接返回结果
    • 没有则执行第三步
  • (3) 通过分析器分析该sql的语义是否正确，包括格式、表等等
  • (4) 通过优化器优化该语句，比如选择索引，join表的连接顺序
  • (5) 验证权限，验证是否有该表的查询权限
    • 没有则返回无权限的错误
    • 有则执行第六步
  • (6) 通过执行器调用存储引擎执行该sql，然后返回执行结果

2、在 Innodb 中，更新语句执行过程

  • 用以下语句来举例，c 字段无索引，id 为主键索引
update T set c=c+1 where id=2;
  • (1) 执行器先找引擎取 id=2 这一行。id 是主键，引擎直接用树搜索找到这一行
    • 如果 id=2 这一行所在的数据页本来就在内存中，就直接返回给执行器
    • 如果不在内存中，需要先从磁盘读入内存，然后再返回
  • (2) 执行器拿到引擎给的行数据，把这个值加上 1，比如原来是 N，现在就是 N+1，得到新的一行数据，再调用引擎接口写入这行新数据
  • (3) 引擎将这行新数据更新到内存中，同时将这个更新操作记录到redolog里面，此时redolog处于 prepare 状态，之后告知执行器执行完成了，随时可以提交事务
  • (4) 执行器生成这个操作的binlog，并把binlog写入磁盘
  • (5) 执行器调用引擎的提交事务接口，引擎把刚刚写入的redolog改成提交（commit）状态，更新完成

3、查询语句一直慢的原因

  • (1) 没有用到索引
    • 比如函数导致的索引失效，或者本身就没有加索引
  • (2) 表数据量太大
    • 考虑分库分表吧
  • (3) 优化器选错了索引
    • 考虑使用 force index 强制走索引

4、查询语句偶尔慢的原因

  • (1) 数据库在刷新脏页
    • 比如 「redolog 写满了」，**「内存不够用了」**释放内存如果是脏页也需要刷，mysql 「正常空闲状态刷脏页」
  • (2) 没有拿到锁

5、调优思路

  • (1) 「表结构优化」
    • 拆分字段
    • 字段类型的选择
    • 字段类型大小的限制
    • 合理的增加冗余字段
    • 新建字段一定要有默认值
  • (2) 「索引方面」
    • 索引字段的选择
    • 利用好mysql支持的索引下推，覆盖索引等功能
    • 唯一索引和普通索引的选择
  • (3) 「查询语句方面」
    • 避免索引失效
    • 合理的书写where条件字段顺序
    • 小表驱动大表
    • 可以使用force index()防止优化器选错索引
  • (4) 「分库分表」

六、主从相关

1、Mysql 主从之间是怎么同步数据的

  • (1) master 主库将此次更新的事件类型写入到主库的 binlog 文件中；
  • (2) master 创建 log dump 线程通知 slave 需要更新数据；
  • (3) slave 向 master 节点发送请求，将该 binlog 文件内容存到本地的 relaylog中；
  • (4) slave 开启 sql 线程读取 relaylog 中的内容，将其中的内容在本地重新执行一遍，完成主从数据同步。 image.png
  • 同步策略：
    • (1) 全同步复制：主库强制同步日志到从库，等全部从库执行完才返回客户端，性能差；
    • (2) 半同步复制：主库收到至少一个从库确认就认为操作成功，从库写入日志成功返回ack确认。

2、主从延迟如何解决

  • (1) MySQL 5.6 版本以后，提供了一种并行复制的方式，通过将 SQL 线程转换为多个 work 线程来进行重放；
  • (2) 提高机器配置；
  • (3) 在业务初期就选择合适的分库、分表策略，避免单表单库过大带来额外的复制压力；
  • (4) 避免长事务；
  • (5) 避免让数据库进行各种大量运算；
  • (6) 对于一些对延迟很敏感的业务直接使用主库读。
avatar
文章
阅读
粉丝