MySQL实战学习(三)

  • 有序数组

  • 搜索树

  • 哈希表

    哈希表是一种以键 - 值(key-value)存储数据的结构,我们只要输入待查找的值即 key,就可以找到其对应的值即 Value。

    优点:做数据新增时,索引的建立会比较快。适用于等值查询的场景。

    缺点:在进行数据区间查找时,会比较慢。

    有序数组

    由数组的特点可以知道,有序数组在等值查询和范围查询的场景的性能非常优秀。在有序数组中利用二分搜索可以快速的找到对应的值。但是当数据大量变动时,采用有序数组会导致性能降低。因此,有序数组索引只适用于静态存储引擎。

    优点:有序数组在等值查询和范围查询场景中的性能就都非常优秀。

    缺点:在需要更新数据的时候就麻烦了,你往中间插入一个记录就必须得挪动后面所有的记录,成本太高。

    搜索树

    二叉搜索树的特点是:每个节点的左儿子小于父节点,父节点又小于右儿子。这样如果你要查 ID_card_n2 的话,按照图中的搜索顺序就是按照 UserA -> UserC -> UserF -> User2 这个路径得到。这个时间复杂度是 O(log(N))。

    优点:查询时间复杂度O(log(N)),更新时间复杂度O(log(N))

    缺点:数据库存储大多不适用二叉树,因为树高过高,会导致多次的硬盘数据读取,会适用N叉树

    InnoDB的索引模型

    InnoDB 使用了 B+ 树索引模型,所以数据都是存储在 B+ 树中的。

    索引类型分为

    • 主键索引

      主键索引的叶子节点存的是整行数据。在 InnoDB 里,主键索引也被称为聚簇索引(clustered index)

    • 非主键索引

      非主键索引的叶子节点内容是主键的值。在 InnoDB 里,非主键索引也被称为二级索引(secondary index)。

    基于主键索引和普通索引的查询有什么区别?

    • 如果语句是 select * from T where ID=500,即主键查询方式,则只需要搜索 ID 这棵 B+ 树;

    • 如果语句是 select * from T where k=5,即普通索引查询方式,则需要先搜索 k 索引树,得到 ID 的值为 500,再到 ID 索引树搜索一次。这个过程称为回表。

    覆盖索引

    「覆盖索引是指在普通索引树中可以得到查询的结果,不需要在回到主键索引树中再次搜索」。

    建立如下这张表来演示覆盖索引:

    create table T (ID int primary key,age int NOT NULL DEFAULT 0,name varchar(16) NOT NULL DEFAULT "",index age(age))engine=InnoDB;
    

    我们执行select * from T where age between 13 and 25 语句,这条语句的执行流程大概为:

    1、在 age 索引树中查找到 age = 13 的记录,取得 ID 的值

    2、根据 id 的值在主键索引上查找所需要的所有信息

    3、在 age 索引树上往下取,重复 1、2 两步操作,直到 age 不符合条件为止。

    如果我们将语句换为 select ID from T where age between 13 and 25,执行这条语句时,在 age 索引树上就可以查询到 ID 的值,省去了上面的回表操作,这样就减少了搜索次数,提升了查询效率。

    这时候的 age 索引树已经可以满足我们的查询需求,age 索引就称为覆盖索引。

    覆盖索引是常用的数据查询优化技术,可以极大的提升数据库性能,有以下几个原因:

    • 「减少树的搜索次数,显著提升查询性能」

    • 「索引是按照值的顺序存储,所以对于 I/O 密集型的范围查询比随机从磁盘中读取每一行的 I/O 要少很多」。

    • 「索引的条目远小于数据的条目,在索引树上读取会极大的减小数据库的访问量」。

    最左前缀原则

    「最左前缀原则是建立在联合索引之上的,如果我们建立了联合索引,我们不需要使用索引的全部定义,只要用到了索引中的最左边的那个字段就可以使用这个索引,这就是 B-tree 索引支持最左前缀原则。」

    建立如下这张表来解释最左前缀原则:

    create table T (ID int primary key,age int NOT NULL DEFAULT 0,name varchar(16) NOT NULL DEFAULT "",ismale tinyint(1) DEFAULT NULL,email varchar(64),address varchar(255),KEY `name_age` (`name`,`age`))engine=InnoDB;
    

    我们建立了联合索引 name_age,现在,假设我们有以下三种查询情景:

    • 查出用户名的第一个字是“张”开头的人的年龄。即查询条件子句为"where name like "张%""

    • 查处用户名中含有“张”字的人的年龄。即查询条件子句为"where name like "%张%""

    • 查出用户名以“张”字结尾的人的年龄。即查询条件子句为"where name like "%张""

    在这三种情况中,第一种情况可以利用到 name_age 这个联合索引,加速查询,可以看出,我们并没有 使用索引的全部定义,「只要满足最左前缀,就可以利用索引来加速检索。这个最左前缀可以是联合索引的最左 N 个字段,也可以是字符串索引的最左 M 个字符。」

    如果我们将索引的顺序调整为KEYname_age(age,name) ,那么上面三种情况都使用不到这个联合索引。

    「维护索引需要代价,所以有时候我们可以利用“最左前缀”原则减少索引数量」。

    索引下推

    「索引下推优化是 MySQL 5.6 引入的, 可以在索引遍历过程中,对索引中包含的字段先做判断,直接过滤掉不满足条件的记录,减少回表次数。」

    建立如下这张表来解释索引下推:

    create table T (ID int primary key,age int NOT NULL DEFAULT 0,name varchar(16) NOT NULL DEFAULT "",ismale tinyint(1) DEFAULT NULL,email varchar(64),address varchar(255),KEY `name_age` (`name`,`age`))engine=InnoDB;
    

    在表中建立了 name、age 的联合索引,我们执行 select * from T where name like "张%" and age=10 and ismale=1;语句,「我们已经知道了B-tree 索引的最左前缀原则,所以将会用到 name_age 索引,因为索引下推优化,会在 name_age 索引树上判断 name 和 age 是否满足」。

    根据我们上面的执行语句,会在 name_age 索引树上查找 name 以 "张" 开头的并且 age = 10 的数据,然后在回到主键索引树中查询所需要的信息,并不是所有 name_age 索引树上查找 name 以 "张" 开头的数据都回主键索引树中查询数据,这样就减少了一些不必要的查询。

    假设我们的数据如下图所示: 在 name_age 索引树中有四条符合 name 以 "张"开头的数据,如果没有索引下推,则需要回到主键索引树上判断 age 是否等于 10 ,这样就需要回表四次,而有了索引下推之后,在 name_age 索引树上就判断 age 是否等于 10 ,只需要回表两次,这样就减少了回表次数,提升了查询性能。

    全局锁

    顾名思义,全局锁就是对整个数据库实例加锁。

    MySQL 提供了一个加全局读锁的方法,命令是 Flush tables with read lock (FTWRL)。当你需要让整个库处于只读状态的时候,可以使用这个命令,之后其他线程的以下语句会被阻塞:数据更新语句(数据的增删改)、数据定义语句(包括建表、修改表结构等)和更新类事务的提交语句。

    全局锁的典型使用场景是,做全库逻辑备份

    官方自带的逻辑备份工具是 mysqldump。当 mysqldump 使用参数–single-transaction 的时候,导数据之前就会启动一个事务,来确保拿到一致性视图。而由于 MVCC 的支持,这个过程中数据是可以正常更新的。对于 MyISAM 这种不支持事务的引擎,如果备份过程中有更新,总是只能取到最新的数据,那么就破坏了备份的一致性。这时,我们就需要使用 FTWRL 命令了。

    表级锁

    MySQL 里面表级别的锁有两种:一种是表锁,一种是元数据锁(meta data lock,MDL)。

    • 表锁的语法是 lock tables … read/write。与 FTWRL 类似,可以用 unlock tables 主动释放锁,也可以在客户端断开的时候自动释放。需要注意,lock tables 语法除了会限制别的线程的读写外,也限定了本线程接下来的操作对象。

    • 另一类表级的锁是 MDL(metadata lock)。MDL 不需要显式使用,在访问一个表的时候会被自动加上。MDL 的作用是,保证读写的正确性。

    在 MySQL 5.5 版本中引入了 MDL,当对一个表做增删改查操作的时候,加 MDL 读锁;当要对表做结构变更操作的时候,加 MDL 写锁。

    • 读锁之间不互斥,因此你可以有多个线程同时对一张表增删改查。

    • 读写锁之间、写锁之间是互斥的,用来保证变更表结构操作的安全性。因此,如果有两个线程要同时给一个表加字段,其中一个要等另一个执行完才能开始执行。

    行锁

    MySQL 的行锁是在引擎层由各个引擎自己实现的。但并不是所有的引擎都支持行锁,比如 MyISAM 引擎就不支持行锁。不支持行锁意味着并发控制只能使用表锁,对于这种引擎的表,同一张表上任何时刻只能有一个更新在执行,这就会影响到业务并发度。InnoDB 是支持行锁的,这也是 MyISAM 被 InnoDB 替代的重要原因之一。

    在 InnoDB 事务中,行锁是在需要的时候才加上的,但并不是不需要了就立刻释放,而是要等到事务结束时才释放。这个就是两阶段锁协议。 ​ 建议:如果你的事务中需要锁多个行,要把最可能造成锁冲突、最可能影响并发度的锁尽量往后放。 死锁:当并发系统中不同线程出现循环资源依赖,涉及的线程都在等待别的线程释放资源时,就会导致这几个线程都进入无限等待的状态。 死锁解决方案

    • 1、通过参数 innodb_lock_wait_timeout 根据实际业务场景来设置超时时间,InnoDB引擎默认值是50s。

    • 2、发起死锁检测,发现死锁后,主动回滚死锁链条中的某一个事务,让其他事务得以继续执行。将参数 innodb_deadlock_detect 设置为 on,表示开启这个逻辑(默认是开启状态)。

    如何解决热点行更新导致的性能问题?

    • 如果你能确保这个业务一定不会出现死锁,可以临时把死锁检测关闭掉。一般不建议采用

    • 控制并发度,对应相同行的更新,在进入引擎之前排队。这样在InnoDB内部就不会有大量的死锁检测工作了。

    • 将热更新的行数据拆分成逻辑上的多行来减少锁冲突,但是业务复杂度可能会大大提高。