MySQL--锁,日志,mysql的JDBC操作

1.锁

锁是计算机协调多个进程或线程并发访问某一资源的机制。在数据库中，除传统的计算资源、（CPU、RAM、I/O)的争用以外，数据也是一种供许多用户共享的资源。如何保证数据并发访问的一致性、有效性是所有数据库必须解决的一个问题，锁冲突也是影响数据库并发访问性能的一个重要因素。从这个角度来说，锁对数据库而言显得尤其重要，也更加复杂。
分类.
MySQL中的锁，按照锁的粒度分，分为以下三类:

1. 全局锁:锁定数据库中的所有表。
2. 表级锁:每次操作锁住整张表。
3. 行级锁:每次操作锁住对应的行数据。

(1).全局锁

全局锁就是对整个数据库实例加锁，加锁后整个实例就处于只读状态，后续的DML的写语句,DDL语句，已经更新操作的事务提交语句都将被阻塞。其典型的使用场景是做全库的逻辑备份，对所有的表进行锁定，从而获取一致性视图，保证数据的完整性。
一致性数据备份：
① 加全局锁

flush tables with read lock ;

② 数据备份

mysqldump -uroot –p1234 itcast > itcast.sql
-- -u指代用户名，–p指代密码
-- 其中itcast是要备份的数据库的名称，itcast.sql是备份后的存储路径文件名
-- 注意，此命令不是mysql的命令，不能在mysql中执行，需要在windows或linux命令行中执行

③ 释放锁

unlock tables ;

数据库中加全局锁，是一个比较重的操作，存在以下问题：

• 如果在主库上备份，那么在备份期间都不能执行更新，业务基本上就得停摆。
• 如果在从库上备份，那么在备份期间从库不能执行主库同步过来的二进制日志（binlog），会导致主从延迟。

注意：在InnoDB引擎中，我们可以在备份时加上参数 --single-transaction 参数来完成不加锁的一致 性数据备份：mysqldump --single-transaction -uroot –p123456 itcast > itcast.sql

(2).表级锁

表级锁，每次操作锁住整张表。锁定粒度大，发生锁冲突的概率最高，并发度最低。应用在MyISAM、InnoDB、BDB等存储引擎中。
对于表级锁，主要分为以下三类:

1. 表锁
2. 元数据锁（ meta data lock,MDL）
3. 意向锁

[1].表锁

表级锁，每次操作锁住整张表。锁定粒度大，发生锁冲突的概率最高，并发度最低。应用在MyIlSAM、InnoDB、BDB等存储引擎中。
对于表锁，分为两类:

1. 表共享读锁(read lock )：自己和其他客户端都可以读，但都不能写
2. 独占写锁( writelock )：自己能读能写，其他客户端不能读也不能写

语法:

1.加锁:lock tables表名… read/write。
2.释放锁:unlock tables/客户端断开连接。

[2].元数据锁

MDL加锁过程是系统自动控制，无需显式使用，在访问一张表的时候会自动加上。MDL锁主要作用是维护表元数据的数据一致性，在表上有活动事务的时候，不可以对元数据进行写入操作。为了避免DML与DDL冲突，保证读写的正确性。 在MySQL5.5中引入了MDL，当对一张表进行增删改查的时候，加MDL读锁(共享);当对表结构进行变更操作的时候，加MDL写锁(排他)。

查看元数据锁：

select object_type,object_schema,object_name,lock_type,lock_duration from performance_schema.metadata_locks;

[3].意向锁

为了避免DML在执行时，加的行锁与表锁的冲突，在InnoDB中引入了意向锁，使得表锁不用检查每行数据是否加锁，使用意向锁来减少表锁的检查。

1. 意向共享锁（IS):由语句select … lock in share mode添加。与表锁共享锁( read）兼容，与表锁排它锁( write）互斥。

2. 意向排他锁（IX):由insert、update、delete、select … for update添加。与表锁共享锁(read）及排它锁(write）都互斥。意向锁之间不会互斥。

可以通过以下SQL，查看意向锁及行锁的加锁情况:

select object_schema,object_name,index_name,lock_type,lock_mode,lock_data from performance_schema.data_locks;

(3).行级锁

行级锁，每次操作锁住对应的行数据。锁定粒度最小，发生锁冲突的概率最低，并发度最高。应用在lnnoDB存储引擎中。InnoDB的数据是基于索引组织的，行锁是通过对索引上的索引项加锁来实现的，而不是对记录加的锁。对于行级锁，主要分为以下三类:

1. 行锁(Record Lock):锁定单个行记录的锁，防止其他事务对此行进行update和delete。在RC、RR隔离级别下都支持。
2. 间隙锁(Gap Lock):锁定索引记录间隙(不含该记录），确保索引记录间隙不变，防止其他事务在这个间隙进行insert，产生幻读。在RR隔离级别下都支持。
3. 临键锁（Next-Key Lock):行锁和间隙锁组合，同时锁住数据，并锁住数据前面的间隙Gap。在RR隔离级别下支持。

[1].行锁

InnoDB实现了以下两种类型的行锁:

1. 共享锁(S)∶允许一个事务去读一行，阻止其他事务获得相同数据集的排它锁。
2. 排他锁（X)∶允许获取排他锁的事务更新数据，阻止其他事务获得相同数据集的共享锁和排他锁。

默认情况下，InnoDB在REPEATABLE READ事务隔离级别运行，InnoDB使用next-key锁进行搜索和索引扫描，以防止幻读。

1. 针对唯一索引进行检索时，对已存在的记录进行等值匹配时，将会自动优化为行锁。
2. InnoDB的行锁是针对于索引加的锁，不通过索引条件检索数据，那么InnoDB将对表中的所有记录加锁，此时就会升级为表锁。

[2].间隙锁/临键锁

默认情况下，InnoDB在REPEATABLE READ事务隔离级别运行，InnoDB使用next-key锁进行搜索和索引扫描，以防止幻读。

1. 索引上的等值查询(唯一索引)，给不存在的记录加锁时,优化为间隙锁。
2. 索引上的等值查询(普通索引)，向右遍历时最后一个值不满足查询需求时，next-key lock退化为间隙锁。
3. 索引上的范围查询(唯一索引)–会访问到不满足条件的第一个值为止。

注意:间隙锁唯一目的是防止其他事务插入间隙。间隙锁可以共存，一个事务采用的间隙锁不会阻止另一个事务在同一间隙上采用间隙锁。

2.日志

在任何一种数据库中，都会有各种各样的日志，记录着数据库工作的方方面面，以帮助数据库管理员追踪数据库曾经发生过的各种事件。MySQL也不例外。
日志分类：

1. 错误日志
2. 二进制日志
3. 查询日志
4. 慢查询日志

(1).错误日志

错误日志是MySQL中最重要的日志之一，它记录了当mysql启动和停止时，以及服务器在运行过程中发生任何严重错误时的相关信息。当数据库出现任何故障导致无法正常使用时，可以首先查看此日志。
该日志是默认开启的，默认存放目录为mysql的数据目录,默认的日志文件名为hostname.err (hostname是主机名)。

查看日志位置的指令：

show variables like 'log_error%';

(2).二进制日志-binlog

二进制日志（BINLOG)记录了所有的DDL(数据定义语言）语句和DML（数据操纵语言）语句，但是不包括数据查询语句。此日志对于灾难时的数据恢复起着极其重要的作用，MySQL的主从复制，就是通过该binlog实现的。
二进制日志，MySQ/8.0默认已经开启，低版本的MySQL的需要通过配置文件开启，并配置MySQL日志的格式。Windows系统:my.ini Linux系统:my.cnf

---

低版本MySQL需要配置:

最后对MySQL服务器重启

---

日志格式.

. STATEMENT
该日志格式在日志文件中记录的都是saL语句(statement)，每一条对数据进行修改的SQL都会记录在日志文件中，通过Mysql提供的mysqlbinlog工具，可以清晰的查看到每条语句的文本。主从复制的时候，从库（slave)会将日志解析为原文本，并在从库重新执行一次。
. ROW
该日志格式在日志文件中记录的是每一行的数据变更，而不是记录sQL语句。比如，执行sQL语句:updatetb_book set status=‘1’,如果是STATEMENT日志格式，在日志中会记录一行saL文件;如果是ROw，由于是对全表进行更新，也就是每一行记录都会发生变更，ROW格式的日志中会记录每一行的数据变更。
.MIXED
混合了STATEMENT和ROW两种格式。

-- 查看MySQL是否开启了bin_log日志
show variables like 'log_bin';
-- 查看bin_log日志的格式
show variables like 'log_format';
-- 查看所有日志
show binlog events ;
-- 查看最新的日志
show master status ;
-- 查询指定的binlog日志
show binlog events in 'DESKTOP-O33VG2S-bin.000001';
-- 从指定的位置开始,查看指定的Binlog日志,限制查询的条数
show binlog events in 'DESKTOP-O33VG2S-bin.000001' from 4 limit 2;
-- 从指定的位置开始，带有偏移，查看指定的Binlog日志,限制查询的条数
show binlog events in 'DESKTOP-O33VG2S-bin.000001' from 4 limit 1,2;

reset master
-- 清空所有的binlog日志文件

(3).查询日志

查询日志中记录了客户端的所有操作语句，而二进制日志不包含查询数据的sQL语句。默认情况下，查询日志是未开启的。如果需要开启查询日志，可以设置以下配置：

(4).慢查询日志

慢查询日志记录了所有执行时间超过参数long_query_time设置值并且扫描记录数不小于min_examined_row_limit 的所有的sQL语句的日志。long_query_time默认为10秒，最小为0，精度可以到微秒。

3.mysql的JDBC操作

JDBC(Java DataBase Connectivity,java数据库连接〉是一种用于执行SQL语句的Java API。JDBC是Java访问数据库的标准规范，可以为不同的关系型数据库提供统一访问，它由一组用Java语言编写的接口和类组成。

JDBC需要连接驱动，驱动是两个设备要进行通信，满足一定通信数据格式，数据格式由设备提供商规定，设备提供商为设备提供驱动软件，通过软件可以与该设备进行通信。

JDBC核心类和接口
DriverManager:用于注册驱动
Connection:表示与数据库创建的连接
Statement/PrepareStatement:操作数据库sql语句的对象
ResultSet:结果集或一张虚拟表

执行流程

例.

import java.sql.*;
public class JdbcTest01 {
    public static void main(String[] args) throws SQLException {
    //1.注册驱动,可以不写
        DriverManager.registerDriver(new com.mysql.jdbc.Driver());
    //2.获取连接
        Connection connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/itcast","root","123456");
    //3.执行sql
        Statement statement = connection.createStatement();
        ResultSet resultSet = statement.executeQuery("select * from product");
    //4.获取结果集
        while (resultSet.next()){
            int pid = resultSet.getInt("pid");
            String pname = resultSet.getString("pname");
            int price = resultSet.getInt("price");
            String categoryId = resultSet.getString("category_id");
            System.out.println(pid+"\t"+pname+"\t"+price+"\t"+categoryId);
        }
    //5.关闭连接
        resultSet.close();
        statement.close();
        connection.close();
    }
}

---

-- 增添操作，例
  statement.executeLargeUpdate("insert into product values (18,'香精煎鱼',100,'c004');");

结果集优化.

    //3.执行sql
        Statement statement = connection.createStatement();
        ResultSet resultSet = statement.executeQuery("select * from product");
        //获取表的列数
        ResultSetMetaData metaData = resultSet.getMetaData();
        int columnCount = metaData.getColumnCount();
        //4.获取结果集
        while (resultSet.next()){
            for (int i=1;i<= columnCount;i++){
                System.out.print( resultSet.getObject(i)+"\t");
            }
            System.out.println();
        }

sql注入

SQL注入（SQL lnjection）是发生在Web程序中数据库层的安全漏洞，是比较常用的网络攻击方式之一，他不是利用操作系统的BUG来实现攻击，而是针对程序员编写时的疏忽，通过SQL语句，实现无账号登录，甚至修改数据库。也就是说，SQL注入就是在用户输入的字符串中添加SQL语句，如果在设计不良的程序中忽略了检查，那么这些注入进去的SQL语句就会被数据库服务器误认为是正常的SQL语句而运行，攻击者就可以执行计划外的命令或者访问未授权的数据。