MySQL&JDBC操作数据库&MySQL调优

快乐就去敲代码@!

于 2024-08-26 16:40:52 发布

阅读量791

点赞数 26

文章标签：数据库 mysql

本文链接：https://blog.csdn.net/weixin_74866702/article/details/141559022

版权

注：本文章只讲述部分MySQL数据库和连接问题

MySQL概念&连接

MySQL概述
- 开源免费的数据库，已经被Oracle收购，MySQL6.x版本开始就已经收费
MySQL的连接
- 首先安装MySQL软件
- 进行安装的时候设置连接密码，用户名默认是root
- 进行远程连接(Navicat连接)，本地的主机名就是localhost或者是127.0.0.1，如果是云服务器或者远程的服务器就是其主机号

SQL语言

概述

数据库是不认识 JAVA 语言的，但是我们同样要与数据库交互，这时需要使用到数据库认识的语言： SQL 语句，它是数据库的代码。

SQL语法

a.SQL 语句可以单行或多行书写,每行可以加或者不加; 但是为了严谨性,我们一般在每行 SQL 语句末尾加上;
b.SQL 语句不区分大小写,建议用大写来书写 SQL 中关键字
c.注释：
单行：1.#注释内容
2.-- 注释内容
多行的注释和Java的一样
特殊的几个数据类型
- decimal(指定小数最大长度,小数部分位数)
  例如：decimal(5,2):改小数最多 5 位,小数部分有两位 312.00 333.55
- date：为日期类型，只包含年月日
- datetime：为日期类型，精确到秒
- char和varchar区别：前者为固定字符串长度，后者是工具字符串长度来调整占用的字节长度

SQL分类

数据定义语言：简称 DDL(Data Definition Language)，用来定义数据库对象：数据库，表，列等。关键字：create，alter，drop 等
数据操作语言：简称 DML(Data Manipulation Language)，用来对数据库中表的记录进行更新。关键字：insert，delete，update 等
数据操作语言：简称 DML(Data Manipulation Language)，用来对数据库中表的记录进行更新。关键字：insert，delete，update 等
数据查询语言：简称 DQL(Data Query Language)，用来查询数据库中表的记录。关键字：select， from，where 等

DDL语句操纵数据库

-- 1.查询所有数据库
SHOW DATABASES

-- 2.创建一个数据库test
CREATE DATABASE test

-- 3.查询数据库的定义信息
-- SHOW CREATE DATABASE 数据库名;
SHOW CREATE DATABASE test;

-- 4.切换或使用某个数据库
USE test

-- 5.删除数据库
DROP DATABASE test

DDL语句操纵数据库表

-- 创建一个数据库表student
CREATE TABLE student(
	-- 列名1 类型[约束],
    -- 列名2 类型[约束]......
);

-- 查看表定义信息
-- SHOW CREATE TABLE 表名;
SHOW CREATE TABLE student;

-- 删除数据库表
DROP TABLE student

-- 向数据库表中添加数据
INSERT INTO 表名 VALUES ('值 1','值 2',......)

多表查询

内连接查询

SELECT * FROM product p
INNER JOIN category c
ON p.cid = c.id;

外连接查询

-- 左外连接
SELECT * FROM product p LEFT OUTER JOIN category c
ON p.cid=c.id;

-- 右外连接
SELECT * FROM product p RIGHT OUTER JOIN category c
ON p.cid=c.id;

JDBC操作MySQL

JDBC概述

JDBC（Java Data Base Connectivity,java 数据库连接）是一种用于执行 SQL 语句的 Java API，可以为多种关系数据库提供统一访问，它由一组用 Java 语言编写的类和接口组成。是 Java 访问数据库的标准规范
实现原理图
JDBC 是接口，驱动是接口的实现，没有驱动将无法完成数据库连接，从而不能操作数据库！
每个数据库厂商都需要提供自己的驱动，用来连接自己公司的数据库，也就是说驱动一般都由数据库厂商提供
SQL注入
- 在JDBC注册完成后，进行测试
  利用拼接的方式，会有SQL注入的漏洞
  我们就要利用PreparedStatement 预编译 SQL，利用问号占位符的方式来防止SQL注入漏洞

连接池技术

用池来管理 Connection，这样可以重复使用 Connection。有了池，所以我们就不用自己来创建 Connection，而是通过池来获取 Connection 对象。当使用完 Connection 后，调用 Connection 的 close() 方法也不会真的关闭 Connection，而是把 Connection“归还”给池。池就可以再利用这个 Connection 对象了。
Java 为数据库连接池提供了公共的接口：javax.sql.DataSource，各个厂商需要让自己的连接池实现这个接口。这样应用程序可以方便的切换不同厂商的连接池！

DBCP连接池

DBCP 是一个开源的连接池，是 Apache Common 成员之一，在企业开发中也比较常见，tomcat 内置的连接池。

DBCP基本使用

/**
* 使用 DBCP 连接池
*/
public class DBCPDemo {
 @Test
 public void test() throws Exception {
 //1.使用 BasicDataSource 基础数据源
 BasicDataSource bs = new BasicDataSource();
 //2.设置数据库连接参数
 bs.setDriverClassName("com.mysql.jdbc.Driver");

bs.setUrl("jdbc:mysql://192.168.1.231:3306/test02?useSSL=false&characterEncodi
ng=UTF-8");
 bs.setUsername("root");
 bs.setPassword("123456");
 //3.从数据源中获取一个连接
 Connection con = bs.getConnection();
 System.out.println(con);
 }
}

事务管理

事务概述

事务是指逻辑上的一组操作(对于数据库来说,就是多条 SQL语句),这一组操作中包含单个操作要么全部成功,要么全部失败

MySQL管理事务

-- MySQL 中事务默认是自动提交(数据永久改变),每执行一条 SQL,就会提交一个事务
 UPDATE account SET money=money-1000 WHERE name='zhangsan';
 SELECT * FROM account;
#a.通过 SQL 语句开启事务
 -- 开启一个事务
 START TRANSACTION;
 UPDATE account SET money=money-1000 WHERE name='zhangsan';
 SELECT * FROM account;
 -- 假设转账失败,此时我们可以还原 zhangsan 的余额
 ROLLBACK;
 SELECT * FROM account;
#b.关闭自动提交
 -- 查看 MySQL 自动提交变量
 SHOW VARIABLES LIKE '%commit%';
 -- 关闭自动提交,一旦关闭自动提交,相当于在执行每条 SQL 语句前,执行一次 START TRANSACTION;
 SET AUTOCOMMIT=OFF;
 
 UPDATE account SET money=money-1000 WHERE name='zhangsan';
 SELECT * FROM account;
 -- 假设转账失败,此时我们可以还原 zhangsan 的余额
 ROLLBACK;
 SELECT * FROM account;

JDBC事务管理利用rollback来进行数据的回滚来实现
DBUtils事务管理利用DbUtils.rollbackAndCloseQuietly(conn)来实现

事务管理四大特性(ACID)

1.原子性:事务中的操作是不可分割的,要么都成功,要么都失败

2.一致性:保证数据完整性,例如:删除种类表 id=3 的种类,但是我们没有删除商品表中 cid=3 对应的商品信息
此时商品表中依赖 cid=3 种类信息丢失,破坏数据一致性
我们需要先删除商品表中 cid=3 的所有商品信息,然后在删除种类表中 id=3 的种类信息

3.隔离性:多个事务操作一条数据记录,事务之间应该相互隔离,不受影响

4.持久性:事务一旦提交(commit),数据将将永久改变,不能恢复

可能产生的安全性问题

多个事务操作数据库同一份数据,如果不考虑隔离性会引发一些安全性问题:
1.脏读: 一个事务读到了另一个事务未提交的数据
2.不可重复读: 一个事务读到了另一个事务已经提交的 update 语句的数据,导致一个事务中出现多次查询,查询结果结果不一致
3.虚读: 一个事务读到了另一个事务已经提交的 insert 语句的数据,导致一个事务中出现多次查询,查询结果结果不一致

需要使用数据库隔离级别来解决上面三种问题:
1.read uncommitted : 未提交读脏读,不可重复读,虚读都有可能发生
2.read committed : 已提交读避免脏读, 不可重复读和虚读都有可能发生
3.repeatable read: 可重复读避免脏读和不可重复读,虚读有可能发生
4.serializable: 串行化避免脏读,不可重复读,虚读会导致一个事务未执行完,其它事务无法执行

从上到下,安全性依次递增,效率依次递减 MySQL 数据库默认隔离级别:repeatable read

SQL索引

语法结构

CREATE [UNIQUE|FULLTEXT|SPATIAL] INDEX index_name
ON table_name (column_name1 [ASC|DESC], column_name2 [ASC|DESC],...);

--  UNIQUE ：创建唯一索引，确保索引列中的值是唯一的。
--  FULLTEXT ：创建全文索引，用于对文本类型的列进行全文搜索。
--  SPATIAL ：创建空间索引，用于对空间数据类型的列进行空间操作。
--  index_name ：索引的名称。
--  table_name ：要创建索引的表名。
--  column_name1 、 column_name2 等：要创建索引的列名，可以是单个列或多个列。
--  ASC 或 DESC ：指定索引列的排序顺序，默认为升序（ASC）。

示例

创建单个列的普通索引：

CREATE INDEX idx_customername
ON customers (customer_name);

这将在 customers 表的 customer_name 列上创建一个名为 idx_customername 的普通索引。

2.创建多个列的复合索引：

CREATE INDEX idx_orderdetails
ON order_details (product_id, quantity);

在 order_details 表的 product_id 和 quantity 列上创建一个名为 idx_orderdetails 的复合索引。

创建唯一索引：

CREATE UNIQUE INDEX idx_useremail
ON users (user_email);

在 users 表的 user_email 列上创建一个唯一索引，确保该列中的值是唯一的。

注：不同的数据库系统提供了不同的方式来重建索引，例如在 MySQL 中可以使用 ALTER TABLE… ENGINE=InnoDB 语句来重建表和索引。

SQL优化的方法

一、查询语句(SELECT)优化

避免使用 SELECT *，只选择需要的列，减少数据传输量和查询开销。

例如： SELECT column1, column2 FROM table_name 比 SELECT * FROM table_name 更高效。

添加合适的索引：索引可以大大提高查询速度。

根据查询条件和表的结构，选择合适的列创建索引。
例如，如果经常根据某个字段进行查询，可以在该字段上创建索引。

避免在索引列上进行函数操作：这会导致索引无法使用。

错误示例：

 SELECT * FROM table_name WHERE UPPER(column_name) = 'SOME_VALUE'

正确做法是在应用程序中处理函数操作，或者在数据库设计时考虑使用合适的数据类型避免函数操作。
4. 限制返回行数：使用 LIMIT 语句限制返回的行数，特别是在处理大量数据时。

例如： SELECT * FROM table_name LIMIT 100

二、连接查询优化

确保连接条件正确且高效：连接条件应该基于有索引的列，并且连接类型（内连接、左连接、右连接等）应该根据实际需求选择。

例如，在两个大表连接时，确保连接条件的列上有索引可以提高连接效率。

避免笛卡尔积：在多表连接时，确保有正确的连接条件，避免产生笛卡尔积，这会导致查询性能急剧下降。

检查连接语句中的 ON 子句，确保连接条件准确无误。

三、存储过程和函数优化

减少存储过程和函数的复杂性：过于复杂的存储过程和函数可能会导致性能问题。尽量将复杂的逻辑拆分成多个简单的步骤。
- 例如，如果一个存储过程中有大量的嵌套循环和复杂的计算，可以考虑将一些计算逻辑提取到单独的函数中，以便更好地维护和优化。
避免在存储过程和函数中进行大量的数据操作：如果可能，尽量在存储过程和函数之外进行数据的批量处理，然后将处理后的结果传递给存储过程或函数进行进一步的操作。
- 例如，不要在存储过程中进行大量的插入、更新或删除操作，而是在外部程序中准备好数据，然后通过参数传递给存储过程进行少量的操作。

四、数据库设计优化

规范化数据库设计：合理的数据库设计可以减少数据冗余，提高数据的一致性和查询性能。
- 遵循数据库规范化原则，将数据拆分成多个表，并通过外键关联起来。
- 例如，将客户信息和订单信息分别存储在两个表中，通过客户 ID 进行关联。
避免过度规范化：过度规范化可能会导致过多的连接操作，影响查询性能。在某些情况下，可以适当的反规范化，以提高查询速度。
- 例如，将一些经常一起查询的字段冗余存储在一个表中，避免频繁的连接操作。

五、其他优化方法

监控数据库性能：使用数据库监控工具，实时监测数据库的性能指标，如查询响应时间、CPU 使用率、内存使用率等。

根据监控结果，及时发现性能问题并进行优化。

MySQL调优

一、监控报警

监控工具(例如有：Prometheus+Grafana)监控MySQL，如果发现查询性能变慢，报价提醒运维人员来进行排查

二、排查慢SQL

查看慢查询次数：

show status like 'slow_queries';

开启慢查询日志，修改慢查询阈值：

set slow_query_log='ON';  -- 开启慢查询日志
set long_query_time = 1;  -- 设置慢查询阈值

找出最慢的几条SQL
- 慢查询日志分析工具mysqldumpslow找到最慢的几条语句
- 示例，按照查询时间排序，查看前五条慢查询SQL语句

mysqldumpslow 命令的具体参数如下：

-a: 不将数字抽象成N，字符串抽象成S
-s: 是表示按照何种方式排序
c: 访问次数
l: 锁定时间
r: 返回记录
t: 查询时间
al:平均锁定时间
ar:平均返回记录数
at:平均查询时间（默认方式）
ac:平均查询次数
-t: 即为返回前面多少条的数据；
-g: 后边搭配一个正则匹配模式，大小写不敏感的；

mysqldumpslow -s t -t 5 /var/lib/mysql/xxx-slow.log

分析查询计划
explan分析sql执行计划

三、MySQL调优

1.基础优化

缓存优化
mysql调整缓冲池大小等参数，引入redis
硬件优化
服务器加内存条、升级SSD固态硬盘、把磁盘I/O分散在多个设备、配置多处理器
参数优化
使用合适的存储引擎
MyISAM：适合读取频繁，写入较少的场景（因为表级锁、B+树叶存储地址）
InnoDB：适合并发写入场景(因为行级锁、B+树叶存记录)
读写分离
分库分表
垂直拆分(分库)、水平拆分(分表)、垂直+水平拆分(分库分表)

2.表设计优化

混合业务分表、冷热数据分表
联合查询改为中间关系表
遵循三个范式
每个属性不可再分、表必须有且只有一个主键、非主键列必须直接依赖主键
字段建议非空约束
null值会有很多问题
数据类型优化
能整数就不要文本类型
避免使用TEXT、BLOB数据类
避免使用枚举类型，因为排序很慢
使用TIMESTAMP存储时间，因为使用的是4字节，DATETIME使用8字节，缺点是只能存到2038年，MySQL5.6.4版本可以参数配置，自动修改为BIGINT类型
DECIMAL存浮点数