MySQL整合篇

in MySQL with 0 comment

学习来源---高性能MySQL(第三版)
面经来源---牛客2020秋招面经大汇总!

读写锁

在处理并发读或者写时,可以通过实现一个由两种类型的锁系统来解决问题。这两种类型的锁通常被称为共享锁排他锁,也叫读锁写锁
锁的概念如下:

写锁比读写有更高的优先级,一个写锁的请求可能会被插入到读锁队列前面。

锁粒度

加锁需要消耗资源。锁的各种操作都会增加系统的开销。如果系统花费大量的时间来管理锁,而不是读取数据,那么系统的性能可能会因此受到影响。MySQL提供了多种选择。每种MySQL存储引擎都可以实现自己的锁策略和锁粒度。将锁粒度固定在某个级别,可以为某些特定的应用场景提供更好的性能。

表锁

表锁是MySQL中最基本的锁策略,并且也是开销最小的策略。它会锁定整张表。一个用户在对表进行写操作前,需要先获得写锁,这阻塞其他用户对该表的所有读写操作。只有没有写锁时,其他读取的用户才能获得读锁,读锁之间锁互相阻塞的。
使用如下。

  1. 单个表锁定:
    格式: LOCK TABLES tbl_name {READ | WRITE},[ tbl_name {READ | WRITE},……]
    例子: lock tables db_a.tbl_aaa read;   // 锁定了db_a库中的tbl_aaa表
    解锁: unlock tables;

  2. 全局表锁定:
    命令: FLUSH TABLES WITH READ LOCK;   // 所有库所有表都被锁定只读
    解锁: unlock tables;

  3. 查看表锁定:
    命令: show open tables in_use > 0;

两个锁表语句在执行的时候都需要注意个特点,就是隐式提交的语句,在退出mysql终端的时候都会隐式的执行unlock tables,也就是如果要让表锁定生效就必须一直保持对话。

行级锁

行级锁可以最大程度地支持并发处理,但同时也带来了最大的锁开销。行级锁只在存储引擎层实现,在MySQL服务器层没有实现。

事务

事务就是一组原子性的SQL查询,或者说是一个独立的工作单元。简单地说,事务内对语句,要么全部执行成功,要么全部执行失败。
事务主要是ACID概念。

原子性(Atomicity)

一个事务必须被视为一个不可切割对最小工作单元,整个事务中对所有操作要么全部提交成功,要么全部失败回滚。对于一个事务来说,不可能只执行其中对一部分操作。这就是事务的原子性。

一致性(Consistency)

数据库总是从一个一致性的状态切换到另外一个一致性的状态。比如银行转账,在某条语句执行时,系统崩溃,因为事务最终没有提交,所以事务中所做的修改也不会保存到数据库中。

隔离性(Isolation)

一个事务所做的修改在最终提交以前,对其他事务是不可见对。

持久性(Durability)

一旦事务提交,则其所做对修改就会永久保存到数据库中。此时即使系统崩溃,修改对数据也不会丢失。

事务就像锁粒度的升级会增加系统开销一样,这种事务处理过程中额外的安全性也会需要数据库系统做更多的额外工作。一个实现了ACID的数据库相比于没有实现ACID的数据库,通常会需要更强的CPU处理能力、更大的内存和更多的磁盘空间。

使用

可以用START TRANSACTION语句开始一个事务,然后使用COMMIT提交事务将修改的数据持久保存,或者使用ROLLBACK撤销所有的修改。

隔离级别

在SQL标准中定义了四种隔离级别,每一种级别都规定了一个事务中所做都修改,哪些事务内都事务内和事务间是可见都,哪些是不可见都。较低级别都隔离通常可以执行更高都并发,系统的开销也更低。

未提交读

在该级别中,事务中的修改,即使没有提交,对其他事务也都是可见的。事务可以读取未提交的数据,这也被成为脏读。这个级别是最低级别,性能上是最好的,但是缺乏其他级别的很多好处,在实际应用中一般很少使用。

提交读

已提交读是指一个事务开始时,只能看见已经提交的事务所做的修改。换句话说,一个事务从开始直到提交之前,所做的任何修改对其他事务都是不可见的。这个级别有时候也叫不可重复读,因为两次执行同样的查询,可能会得到不一样的结果。

可重复读

该级别是MySQL的默认事务级别。他解决了脏读的问题。该级别保证了在同一个事务中多次读取同样记录的结果是一致的。但是可重复读还是无法解决幻读问题。

串行化

串行化是最高隔离级别。它通过强制事务串行执行,避免了幻读问题。简单的说,串行化会在读取的每一行数据上都加锁。故而会导致大量的超时和锁争用问题,增大系统开销。

总结

事务的四大隔离级别。简单点说就是在性能和功能上的取舍。性能强的隔离级别会导致许多问题。性能低的隔离级别会减少许多问题的发生。
主要问题如下

使用

修改事务隔离级别语句:

SET [SESSION | GLOBAL] TRANSACTION ISOLATION LEVEL {READ UNCOMMITTED | READ COMMITTED | REPEATABLE READ | SERIALIZABLE}

多版本并发控制(MVCC)

MVCC是行级锁的一个变种。它在很多情况下避免了加锁操作,所以开销更低。同样的,实现了非阻塞的读操作,但写操作也只锁定必的行。

MVCC的实现是通过保存数据在某个时间点的快照来实现的。不管需要执行多长时间,每个事务看到的数据都是一致的。根据事务开始的时间不同,每个事务对同一张表,同一时刻看到的数据可能是不一样的。

InnoDB的MVCC是通过在每行记录的后面保存两个隐藏列来实现。这两个列,一个是保存行的创建时间,一个是保存行的过期时间。存储都是系统版本号。每开始一个新的事务,系统版本号都会自动递增。事务开始时刻的版本号会作为事务的版本号,用来和查询到的每行记录的版本号进行比较。

MVCC可以解决在可重复读隔离级别下的幻读问题。实现方式就是通过两个隐藏列实现的。通过版本号,使幻读问题不在发生。

但MVCC只在可重复读和提交读两个级别下工作。因为未提交读总是读取最新的数据行,而不是符合当前事务版本的数据行。

MySQL存储引擎

主要使用的就是两个存储引擎,分别是InnoDB和MyISAM

InnoDB

InnoDB是MySQL的默认存储引擎。
InnoDB采用MVCC来支持高并发,并且实现了四个标准的隔离级别。默认级别是可重复读。通过间隙锁策略防止幻读的出现。
InnoDB表是基于聚簇索引建立的。聚簇索引对主键查询有很高的性能。
InnoDB内部做了很多优化。包括从磁盘读取数据时采用可预测性预读,能够自动在内存中创建Hash索引以加速读操作的自适应哈希索引,以及能够加速插入操作的插入缓冲区等。

MyISAM

MyISAM提供了大量的特性,包括全文索引、压缩、空间函数等,但MyISAM不支持事务和行级锁。
在MyISAM表,可以基于其前500个字符创建索引。MyISAM也支持全文索引,这是一种基于分词创建但索引,支持复杂但查询。
MyISAM引擎设计简单,数据以紧密格式存储,在某些场景下,性能很好。

InnoDB和MyISAM区别

  1. 是否支持事务
  1. 支持锁的级别不同
  1. 是否支持外键

对一个包含外键的InnoDB表转为MYISAM会失败;
4. 存放索引的方式

  1. 查询具体行数的差异
  1. 是否支持全文索引

索引

索引用个比喻就是一本书的目录。专业解释就是一种单独的、物理的对数据库表中一列或多列的值进行排序的一种存储结构(百度百科)。索引可以使对应于表的SQL语句执行得更快,可快速访问数据库表中的特定信息。

简单点说,索引就是一种排序后的存储结构,它会使SQL语句执行更快。

索引的类型

MySQL中索引主要有B-Tree索引,Hash索引,R-Tree索引,全文索引。

B-Tree索引

如果没有特别指明类型,那大多数谈索引都是B—Tree索引。它使用B-Tree数据结构来存储索引。其中,主要用的还是InnoDB中,使用的是B+Tree。

先谈二叉树,它是一种树形数据结构,正如它的名字一样,每个分支都是两个。
其中有个实现,平衡二叉树,它的特点就是左子树的值永远比根节点小,右子树永远比根节点大。
于是它又有了一个变种,B树,它和平衡二叉树的特点一样,但不同但是B树是一颗多叉树又名平衡多路查找树。它的分支不止是两个,使得树的高度变得更低。但它的存储的值全在节点上,导致查找效率不是那么高。所以,它的升级版,B+树来了。
B+树是B树的一个升级版,相对于B树来说B+树更充分的利用了节点的空间,让查询速度更加稳定,其速度完全接近于二分法查找。它存储值的位置和B树不同的是,它的存储的位置全在叶子节点,所以使得B+树的查找效率更高,层级也更矮。
另外再来篇文章
InnoDB一棵B+树可以存放多少行数据?

哈希索引

哈希索引是基于哈希表实现的。只有精确匹配索引所有列的查询才有效。对于每一行数据,存储引擎都会对所有的索引列计算一个哈希玛。哈希玛是一个较小的值,并且不同键值的行计算出来的哈希玛也不一样。哈希索引将所有的哈希玛存储在索引中,同时在哈希表中保存指向每个数据行的指针。
比如在存储地址URL的时候,这个时候用哈希索引就比用B-Tree索引效果好,因为URL地址的长度一般都很长,如果使用B—Tree索引存储,会使节点中的值变长,使索引的内存空间更大。但使用哈希索引,将url字符串计算出一个哈希玛,会让索引占用内存空间小。

R-Tree索引

即空间索引,在MySQL中很少使用,可以用作地理数据存储。

全文索引

它查找的是文本中的关键词,而不是直接比较索引中的值。
在相同的列上同时创建全文索引和基于值的B-Tree索引不会有冲突。

索引匹配原则

即最左匹配原则

  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的查询优化器会帮你优化成索引可以识别的形式

explain

explain是查看SQL执行计划。
使用explain很简单,就是在sql语句前面加上explain就行。
使用EXPLAIN解析SQL执行计划时,如果有下面几种情况,就需要特别关注下了:

  1. 首先看下 type 这列的结果,如果有类型是 ALL 时,表示预计会进行全表扫描(full table scan)。通常全表扫描的代价是比较大的,建议创建适当的索引,通过索引检索避免全表扫描。此外,全索引扫描(full index scan)的代价有时候是比全表扫描还要高的,除非是基于InnoDB表的主键索引扫描。

  2. 再来看下 Extra 列的结果,如果有出现 Using temporary 或者 Using filesort 则要多加关注:

    • Using temporary,表示需要创建临时表以满足需求,通常是因为GROUP BY的列没有索引,或者GROUP BY和ORDER BY的列不一样,也需要创建临时表,建议添加适当的索引。
    • Using filesort,表示无法利用索引完成排序,也有可能是因为多表连接时,排序字段不是驱动表中的字段,因此也没办法利用索引完成排序,建议添加适当的索引。
    • Using where,通常是因为全表扫描或全索引扫描时(type 列显示为ALL 或 index),又加上了WHERE条件,建议添加适当的索引。

其他状态例如:Using index、Using index condition、Using index for group-by 则都还好,不用紧张。

向数据库插入500W条数据

500W条数据肯定不会一次性的将500W条一次性插入到数据库,我选择的是分500次,一次插入10000条数据。具体代码如下:

	long time = System.currentTimeMillis();
        //读取conf.xml
        Reader reader = Resources.getResourceAsReader("conf.xml");
        //创建会话工厂
        SqlSessionFactory sessionFactory = new SqlSessionFactoryBuilder().build(reader);
        SqlSession sqlSession = sessionFactory.openSession();
        try {
            PersonMapper personMapper = sqlSession.getMapper(PersonMapper.class);
            List<Person> persons = new ArrayList<>();
            for (int i = 0; i < 500 ; i++) {
                for (int j = 0; j <10000 ; j++) {
                    Person person = new Person(Age(),Name(),Sex());
                    persons.add(person);
                    person=null;
                }
                personMapper.insert(persons);
                sqlSession.commit();
                System.out.println("第"+i+"次,10000条数据插入成功");
                persons.clear();
            }

        }catch (Exception e){
            e.printStackTrace();
            sqlSession.rollback();
        }finally {
            sqlSession.close();
            System.out.println(System.currentTimeMillis()-time);
        }

实体类如下

public class Person {
    private int id;
    private int age;
    private String name;
    private String sex;

    public Person(int age, String name, String sex) {
        this.age = age;
        this.name = name;
        this.sex = sex;
    }
}

为了方便测试,实体类数据全部随机生成

	static String Name(){
        String str="abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789";
        Random random=new Random();
        StringBuffer sb=new StringBuffer();
        for(int i=0;i<2;i++){
            int number=random.nextInt(62);
            sb.append(str.charAt(number));
        }
        return sb.toString();
    }

    static int Age(){
        return (int)(Math.random()*100+1);
    }

    static String Sex(){
        Random random = new Random();
       int i =  random.nextInt(2)+1;
       return i==1?"男":"女";
    }

代码开跑!
image.png
64秒,四舍五入,一分钟,500W条数据插入成功
image.png

实操索引

先随便查找下年龄在30岁的信息
结果如下
image.png
需要0.98s
接着,创建age的索引,再次查找

ALTER TABLE `Person` ADD INDEX index_age ( `age` ) 

因为索引是在物理内存地址会生成树,所以在创建索引会有点慢。
image.png
继续执行刚才的SQL语句查找age在30岁的信息
image.png
欧吼,从近1S的查找数据,变成了0.175s,快了近五倍

其实,还可以更快的,因为我是查找的Select *,通过索引查找,会有一个回表的过程,现在继续看看Explain,执行计划

image.png

现在看看联合索引,继续创建name和age的联合索引
image.png
一样的,创建索引需要时间
再查找
image.png
执行结果0.02s,500W条数据,以及很快了,现在,我们把联合索引删了,再执行一次,看看时间
image.png
image.png
只不过删了联合索引,为什么执行时间又起来了呢。其实中间有个回表的过程。

在谈B+树索引

其实索引分为一级索引和二级索引,除主键是以及索引以外,均为二级索引。我设置的age是二级索引,其中存储的信息只有age和主键,所以,在我select*或者Select name和sex其中一个的时候,都会进行一个叫做回表的操作,这个回表就是通过主键值,再去表中查找我需要的name和sex值,所以,这个回表就导致了执行时间从0.02s到了0.17s。
而我创建了联合索引,使用联合索引以age查找name的值,因为我age和name设置的是联合索引,所以在B+树的叶子节点,存了3个值,分别是主键,age,name,那么在我通过age找name,B+树叶子结点已经有了我的name值,就不会再进行回表操作,反之,我再加上一个sex,又回进行回表,结果如下
image.png
继续,回到联合索引,先把age的单独索引删除,查看索引的执行计划,这是只查找的name执行计划
image.png
下面是查找name+sex的执行计划
image.png

可以看见Extra,一个是Using index ,一个没有,Extra为Using index说明,SQL所需要返回的所有列数据均在一棵索引树上,而无需访问实际的行记录。即是否回表。

暂时数据库方面先到这里把...