MYSQL的全面总结及优化

最新推荐文章于 2024-04-28 10:15:00 发布

伊佩佩

最新推荐文章于 2024-04-28 10:15:00 发布

阅读量103

点赞数

分类专栏： java 文章标签： mysql

本文链接：https://blog.csdn.net/YIYIYIPEI/article/details/119413427

版权

java 专栏收录该内容

20 篇文章 0 订阅

订阅专栏

MYSQL

一、事务
二、锁
三、sql优化原则
四、join的原理
五、执行流程计划与执行明细
六、执行流程
七、表结构对性能的影响
八、索引

一、事务

1、实例介绍

uer表数据

在这里插入图片描述

user1表数据

在这里插入图片描述

public static void main(String[] args) {
    Connection connection = null;
    PreparedStatement preparedStatement = null;
    try {
        Class.forName("com.mysql.cj.jdbc.Driver");
        String url = "jdbc:mysql://localhost:3306/test?serverTimezone=UTC";
        connection = DriverManager.getConnection(url,"root","root");
        // 禁止jdbc自动提交事务
        //connection.setAutoCommit(false);
        preparedStatement = connection.prepareStatement("update user set money = money-? where id= ?");
        preparedStatement.setInt(1,10);
        preparedStatement.setInt(2,1);
        preparedStatement.executeUpdate();
        //抛出异常
        String str = null;
        if(str.equals("")){

        }
        preparedStatement = connection.prepareStatement("update user1 set money = money+? where id = ?");
        preparedStatement.setInt(1,10);
        preparedStatement.setInt(2,1);
        preparedStatement.executeUpdate();
        // 提交事务
        //connection.commit();
    } catch (Exception e) {
        e.printStackTrace();
            // 回滚事务
            // try {
            //   connection.rollback();
            //} catch (SQLException e1) {
            //   e1.printStackTrace();
            //}

    }finally {
        try {
            preparedStatement.close();
            connection.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}

由上述代码可知：操作一执行成功后，代码出现异常，导致操作二无法进行
但转账过程是一个事务：操作应要么全部成功，要么全部失败

因此我们应该：
1、禁止jdbc自动提交事务
connection.setAutoCommit(false);
2、在两条SQl语句执行完之后提交事务
connection.commit();
3、如果有异常则回滚事务
 catch (Exception e) {
       e.printStackTrace();
       // 回滚事务
       try {
           connection.rollback();
       } catch (SQLException e1) {
           e1.printStackTrace();
  }

关于事务提交、回滚的理解：

在这里插入图片描述

2、特性(ACID)

事务的原子性

	<数据库事务不可分割的单位，要么都做，要么都不做>
 	事务是最小单元，不可再分，要么全部执行成功，要么全部失败回滚。

事务的一致性

	 <事务的操作不会改变数据库的状态，比方说唯一约束>
  一致性是指事务必须使数据库从一个一致的状态变到另外一个一致的状态，
   也就是执行事务之前和之后的状态都必须处于一致 的状态。
   不一致性包含三点：脏读，不可重复读，幻读

事务的隔离性

	<事务是相互不可见的>
	隔离性是指当多个用户并发访问数据库时，比如操作同一张表时，数据库为每一个用户开启的事务，不能被其他事务的操作所 干扰，多个并发事务之间要相互隔离

事务的持久性

	<事务一旦提交，即使宕机也是能恢复的>
	DBMS（数据库管理系统）对数据的修改是永久性的。

3、分类

https://blog.csdn.net/cpongo6/article/details/88793450

4、隔离级别

https://juejin.cn/post/6993239862354657310

mysql默认的事务隔离级别为repeatable-read

未提交读

   读未提交，即能够读取到没有被提交的数据
   所以很明显这个级别的隔离机制无法解决脏读、不可重复读、幻读中的任何一种。

已提交读

   读已提交，即能够读到那些已经提交的数据
   自然能够防止脏读，但是无法限制不可重复读和幻读

可重复读

   可重复读，读取了一条数据，这个事务不结束，
   别的事务就不可以改这条记录，这样就解决了脏读、不可重复读的问题，

串行化

   串行化，多个事务时，只有运行完一个事务之后，才能运行其他事务。

在这里插入图片描述

补充：
　　1、事务隔离级别为读提交时，写数据只会锁住相应的行
　　2、事务隔离级别为可重复读时，如果检索条件有索引（包括主键索引）的时候，默认加锁方式是next-key 锁；如果检索条件没有索引，更新数据时会锁住整张表。一个间隙被事务加了锁，其他事务是不能在这个间隙插入记录的，这样可以防止幻读。
　　3、事务隔离级别为串行化时，读写数据都会锁住整张表
　　4、隔离级别越高，越能保证数据的完整性和一致性，但是对并发性能的影响也越大。
　　5、MYSQL MVCC实现机制参考链接：https://blog.csdn.net/whoamiyang/article/details/51901888
　　6、关于next-key 锁可以参考链接：https://blog.csdn.net/bigtree_3721/article/details/73731377

5、并发问题

1、脏读：事务A读取了事务B更新的数据，然后B回滚操作，那么A读取到的数据是脏数据

2、不可重复读：事务 A 多次读取同一数据，事务 B 在事务A多次读取的过程中，对数据作了更新并提交，导致事务A多次读取同一数据时，结果不一致。

3、幻读：系统管理员A将数据库中所有学生的成绩从具体分数改为ABCDE等级，但是系统管理员B就在这个时候插入了一条具体分数的记录，当系统管理员A改结束后发现还有一条记录没有改过来，就好像发生了幻觉一样，这就叫幻读。
　　
小结：不可重复读的和幻读很容易混淆，不可重复读侧重于修改，幻读侧重于新增或删除。解决不可重复读的问题只需锁住满足条件的行，解决幻读需要锁表

二、锁

https://blog.csdn.net/lzx_victory/article/details/83040213

1、Lock

2、latch

<轻量级锁，锁的时间非常短，用来锁临界资源>

3、一致性的非锁定读

4、一致性锁定读

5、死锁

三、sql优化原则

1、尽量避免在列上进行运算，这样会导致索引失效

例如原句为： 

SELECT * FROM t WHERE YEAR(d) >= 2011；

优化为：

SELECT * FROM t WHERE d >= '2011-01-01';

2、使用JOIN时，应该用小结果集驱动大结果集。同时把复杂的JOIN查询拆分成多个Query。因为JOIN多个表时，可能导致更多的锁定和堵塞

例如： 

SELECT * FROM a JOIN b ON a.id=b.id

LEFT JOIN c ON c.time=a.date

LEFT JOIN d ON c.pid=b.aid

LEFT JOIN e ON e.cid=a.did

3、注意LIKE模糊查询的使用，避免％ %

例如原句为： 

SELECT * FROM t WHERE name LIKE '%de%'

优化为：

SELECT * FROM t WHERE name>='de' AND name<'df'

4、仅列出需要查询的字段，这对速度不会有明显影响，主要考虑节省内存

例如原句为： 

SELECT * FROM Member;

优化为：

SELECT id,name,pwd FROM Member;

5、使用批量插入语句节省交互

例如原句为： 

INSERT INTO t (id,name) VALUES (1,'a');

INSERT INTO t (id,name) VALUES (2,'b');

INSERT INTO t (id,name) VALUES (3,'c');

优化为： 

INSERT INTO t (id,name) VALUES (1,'a'),(2,'b'),(3,'c');

6、limit的基数比较大时使用between

例如原句为： 

select * from article as article order by id limit 1000000,10

优化为：

select * from article as article where id between 1000000 and 1000010 order by id

between限定比limit快，所以在海量数据访问时，建议用between或是where替换掉limit。
但是between也有缺陷，如果id中间有断行或是中间部分id不读取的情况，总读取的数量会少于预计数量！
在取比较后面的数据时，通过desc方式把数据反向查找，以减少对前段数据的扫描，让 limit的基数越小越好！

7、不要使用rand函数获取多条随机记录

例如：
select * from table order by rand() limit 20;

优化为：
SELECT * FROM 'table' AS tl JOIN (SELECT ROUND(RAND() * ((SELECT MAX(id) FROM 'table')-(SELECT MIN(id) FROM 'table'))+(SELECT MIN(id) FROM 'table')) AS id ) AS t2 WHERE tl.id>=t2.id ORDER BY tl.id LIMIT 1;

这是获取一条随机记录，这样即使执行2 0次，也比原来的语句髙效

8、避免使用NULL

9、不要使用count(id), 而应该是count( * )

1、count(*)查询的是总条数，count(id)查询的是id非null的条数
2、在100w+数据下，count(1) > count(*) > count(主键id) > count(带索引column) > count(不带索引column)，count(1)效率稍微高于count(*)，但差距微乎其微。