mysql索引、锁、事务

索引

索引类型

• 从物理角度

  • 1、聚集（聚簇）索引

    • 真实存储数据的位置，在B+Tree 中仅仅在叶子节点存储数据。

  • 2、非聚集索引

    • 索引通过逻辑地址进行连接，实际位置并没有真实的数据，只是记录数据在聚簇索引中的位置（简单理解，节点上存储的是主键。查询索引的时候，通过索引树，找到聚集索引，最后找到数据的过程叫回表）

• 从逻辑角度

  • 主键索引

  • 唯一索引

    • 唯一值，不可重复

  • 普通索引

    • 一般查询索引

  • 组合索引

    • 最左匹配原则：如果创建了 key1 、key2 、key3 ,按照最左匹配原则，相当于创建了 key1 ,和 key1、key2 和 key1、key2、key3 三个索引

  • 全文索引

    • 对文本内容进行分词搜素：select * from fulltext_test where match(content,tag) against(‘xxx xxx’);

  • 索引合并、覆盖索引

    • 覆盖：不需要回表，一次查询，查询的列再索引中可以都找到

存储引擎

• InnoDB

  • 默认的存储引擎，使用量最多
  • 支持行锁、外键
  • 支持事务（牺牲部分效率）
  • 存着缓冲管理，通过缓冲池，将索引和数据全部缓存起来，加快查询的速度

• MyIsam

  • 拥有较高的插入，查询速度，但不支持事务，锁粒度为表锁

• Memory

  • 所有数据基于内存，极高的插入，查询效率，但数据不进行持久化存储，随着mysql 的重启，数据会丢失

• 其他类型：show engines; 使用命令可以查看

索引数据结构类型

• b+Tree

  • 结构

    • 树结构+链表结构，随便搜索下都是画好的图

  • 优点

    • 1 、数据存储在叶子节点上，每一层相比B树来说，存储的节点数量更多，减少了磁盘的IO 次数
    • 2、有重复节点，更加的提高了查询效率，更加的适合范围查找

• hash索引

  • 缺点

    • 1 数据无序，无法排序
    • 2 由于存在hash碰撞，查询效率不稳定

索引失效常见场景

• 所有的总结都需要经过自己验证，否则今天是理论，明天啥也不是
• 1. where语句中包含or时，可能会导致索引失效
• 2. where语句中索引列使用了负向查找（非、不，此类型的查找），可能会导致索引失效
     负向查询包括：NOT、!=、<>、!<、!>、NOT IN、NOT LIKE等
• 3 对null 的判断（建议所有的null 和空 都进行默认值填充，）
• 4 索引上加处理函数，其中包括隐式类型转换，隐式编码转换（当两个表的表的关联字段不一致的时候可能发生）
• 5. 对索引列进行运算
• 6 like 以%开头的 会导致失效（和索引结构有关，想想是理所当然的）
• 7. 联合索引中，where中索引列违背最左匹配原则。例如 ： 创建了 A 、B、C ,
     下列生效的 有 A ，AB ,ABC 不生效的 ，B ,C ,BC 部分生效的 AC 其中只有A 生效
• 8. MySQL优化器的最终选择，不走索引

锁

事务

• 特性

  • 原子性：在做一件事的时候，要么全做，要么不做
  • 一致性：对数据库的影响，前后一致，事务执行的前后都是合法的数据状态
  • 隔离性：各个并发事务之间相互独立互相不干扰
  • 持久性：对数据库的影响是永久性的

• 事务的隔离级别

  • 脏读：一个事务读取到了另外一个事务修改之后，还未提交的数据（侧重点：修改，未提交）
  • 幻读：A 修改完数据之后，在重新读取数据的时候，B 插入 或者删除了一条记录，导致A 读取到的数据和预期不符合，像是产生幻觉。（侧重点“新增 或者减少）
  • 不可重复读：事务A 多次读取同一数据的过程中，B 事务进行了修改 并且提交。导致 A 事务读取的结果不一致，这个过程称作不可重复读

乐观锁和悲观锁

• 乐观锁：对事务存在乐观的想法，默认事务不会造成冲突，当在真正提交的时候，才会判断是否产生冲突（通过版本号，或者时间戳判断）一般数据竞争不激烈的情况下会使用
• 悲观锁：对事务存在悲观的想法：直接进行加锁，其他事务无法进行操作，减少因为数据冲突导致的回滚操作

表锁和行锁

• 表锁：开销小，加锁快；不会出现死锁；锁定粒度大，发生锁冲突的概率最高,并发度最低，
• 行锁：开销大，加锁慢；会出现死锁；锁定粒度最小，发生锁冲突的概率最低,并发度也最高

读锁和写锁

• 读锁（共享锁）：对同一个数据，多个读操作可以同时进行，互不干扰。加锁的会话只能对此表进行读操作,其他会话也只能进行读操作。MyISAM的读默认是加读锁
• 写锁（排他锁）： 如果当前写操作没有完毕，则无法进行其他的读操作、写操作。当前会话只能对此表进行读,写操作,其他会话无法进行任何操作。

sql优化

没有从SQL语法层面一定能提高效率的优化,优化还是多的要靠执行计划来看，怎么写好呢。《从根儿上理解mysql 》 这个写的很棒，之后有机会补上执行计划这一块，这个确实十一分重要

问题思考

1、B 树为什么不行，为什么要B+Tree?

• B 树虽然有序，但是所有节点上都存储数据，当数据量很大的时候，树的层数就会变得很深（每一页能存储的数据大小有限，节点上存储的数据大了之后，节点个数就会变少，导致层数变深），减少了IO的次数
• 使用B+Tree 好处：1 、查询效率稳定 2 更加的适合范围查找 3 、减少了磁盘读写的IO

2、索引上面存储什么东西？

• 聚簇索引的叶子节点含有真实数据，其他类型的索引以及所有节点上，含有的只是聚簇索引的位置。 节点上含有：当前节点类型、节点指针下个位置，当前节点数据，等其他信息，并且索引父节点上存的数据，为范围数据 如 节点上存储了 10，20，30，40 ，其子节页分为5 段 ，有序存储

3、索引越多越好吗？有什么弊端，什么情况下需要建立索引，什么情况下建立索引会更慢？

• 需要建立索引才建立：1 索引会占用空间 2 索引理论上可以提高查的效率，但是增删改操作，会一定的效率影响，这些操作时 需要维护索引
• 从B+Tree 的结构来看，存在重复节点：当一个列的重复度很高的时候，使用索引可能会降低效率：count(distinct(columeName)) : count(*) 越高 使用索引效率越好

4、存储过程、函数，有什么用，怎么用，有什么好处？

• 优点

  • 1.具有更好的性能
    存储过程是预编译的，只在创建时进行编译，以后每次执行存储过程都不需再重新编译
  • 2.功能实现更加灵活
    可以应用条件判断和游标等语句，以及数据库的一些内置函数，完成复杂的判断和较复杂的运算。
  • 3.减少网络传输
  • 4.具有更好的安全性
    在通过网络调用过程时，只有对执行过程的调用是可见的。无法看到表和数据库对象名称，不能嵌入SQL 语句，有助于避免 SQL 注入攻击。

• 缺点

  • 1.架构不清晰，不够面向对象
    存储过程不太适合面向对象的设计，无法采用面向对象的方式将业务逻辑进行封装，业务逻辑在存储层实现，增加了业务和存储的耦合，代码的可读性也会降低，
  • 2.开发和维护要求比较高
    存储过程的编写直接依赖于开发人员，如果业务逻辑改动较多，需要频繁直接操作数据库，导致数据库的维护混乱。
  • 3.可移植性差
    过多的使用存储过程会降低系统的移植性。在对存储进行相关扩展时，可能会增加一些额外的工作

5、回表

• 先通过非聚簇索引，定位聚簇索引的位置，再通过主键值，找到数据记录位置。不需要回表的场景：1、通过聚簇索引去查询 2、查询的所有字段，在组合索引中都能覆盖

6、查询优化器是什么？

• 优化SQL 语句的查询逻辑选择，较优的执行计划进行执行，所以存在可能，数据字段已经加了索引也不走索引
• 具体怎么优化呢？再查查吧。

7、索引下推

• 在查询时，首先只读取索引元组（不是整行数据），通过索引元组来判断数据是否符合WHERE语句中的条件，然后只对其中符合条件的行，再读取整行数据来进行其他WHERE条件的判断，可以减少回表次数