常用数据库sql和项目经验

数据库sql和项目数据库经验汇总

一、数据库sql

MySQL Kill出现Killed死锁处理

# 查看当前线程处理情况
SELECT * FROM information_schema.processlist  ORDER BY time DESC

# 使用如下语句筛选查看具体情况 >
 select * from information_schema.processlist where time>500;

# 查询执行时间超过2分钟的线程，然后拼接成 kill 语句

select concat('kill ', id, ';')
from information_schema.processlist
where command != 'Sleep'
and time > 2*60
order by time desc

拷贝sql语句执行

正在运行的MySQL连接和进程列表的查询语句

select * from information_schema.processlist; 

查看所有数据库各容量大小

select table_schema as'数据库',
sum(table_rows) as'记录数',
sum(truncate(data_length/1024/1024/1024, 3)) as'数据容量(GB)',
sum(truncate(index_length/1024/1024/1024, 3)) as'索引容量(GB)'from information_schema.tables
group by table_schema
order by sum(data_length) desc, sum(index_length) desc;

查看指定数据库各表容量大小

select table_schema AS '数据库',
table_name AS '表名',
table_rows AS '记录数',
TRUNCATE ( data_length / 1024 / 1024, 2 ) AS '数据容量(MB)',
TRUNCATE ( index_length / 1024 / 1024, 2 ) AS '索引容量(MB)'
FROM
	information_schema.TABLES 
WHERE
	table_schema = 'luckyun_eam_bjyj_bj' 
ORDER BY
	data_length DESC,
	index_length DESC;

MySQL 批量修改表名和字段名大小写

  SELECT
concat(
	'alter table ',
	TABLE_NAME,
	' change column ',
	COLUMN_NAME,
	' ',
	LOWER( COLUMN_NAME ),
	' ',
	COLUMN_TYPE,
	' comment \'',
	TRIM(
	REPLACE ( REPLACE ( REPLACE ( REPLACE ( COLUMN_COMMENT, ',', ':' ), '"', '' ), CHAR ( 10 ), '' ), CHAR ( 13 ), '' )),
	'\'',
	' ',
	IF
		(
			COLUMN_DEFAULT IS NULL,
			'',
		concat( ' default \'', TRIM( COLUMN_DEFAULT ), '\'' )),
		';' 
) AS '修改脚本sql' 
FROM
	information_schema.COLUMNS t 
WHERE
	TABLE_SCHEMA = 'test_activiti6_luckyun_t_omp_cc';

• 如果是小写改大写，只需要将LOWER 修改为 UCASE即可。

mysql通过缓存设置优化数据库性能

在 MySQL 数据库中，有几个重要的缓存设置可以用来优化数据库性能。这些设置可以在 MySQL 配置文件中进行调整。以下是一些常见的 MySQL 缓存设置和说明：

查询缓存： 查询缓存是 MySQL 中的一种缓存机制，用于缓存查询语句和其结果集。然而，自 MySQL 5.7.20 版本起，查询缓存已被弃用，并且在 MySQL 8.0 版本中已完全删除。不建议在新版本中使用查询缓存。

键缓存（Key Cache）： 键缓存用于缓存索引数据，加速索引的访问。在 InnoDB 存储引擎中，键缓存设置不适用，因为 InnoDB 有自己的缓冲池机制。

缓冲池（Buffer Pool）： 缓冲池是 InnoDB 存储引擎中的一个重要缓存机制，用于缓存表数据和索引数据。你可以通过 innodb_buffer_pool_size 参数来设置缓冲池的大小，以优化 InnoDB 存储引擎的性能。例如： 1.innodb_buffer_pool_size = 1G 2.join_buffer_size = 256K

排序缓存（Sort Buffer）： 排序缓存用于排序操作时的临时存储区。你可以通过 sort_buffer_size 参数来设置排序缓存的大小。例如：sort_buffer_size = 256K

临时表缓存（Temporary Table Cache）： 临时表缓存用于存储临时表的定义和元数据。你可以通过 tmp_table_size 参数来设置临时表缓存的大小。例如：tmp_table_size = 64M

连接缓存（Thread Cache）： 连接缓存用于缓存连接线程，以避免频繁的创建和关闭连接。你可以通过 thread_cache_size 参数来设置连接缓存的大小。例如：thread_cache_size = 60

请注意，对于不同的 MySQL 版本和存储引擎，上述设置可能会有所不同。你可以根据数据库的特点和需求进行调整。
要应用这些设置，你需要编辑 MySQL 配置文件（通常是 my.cnf 或 my.ini），将所需的设置添加或修改，并重启 MySQL 服务使其生效。
在调整这些设置时，最好进行测试，以确保所做的更改能够有效地提高数据库性能。

二、项目上mysql经验

记一次mysql慢sql优化

mysql 在做表关联的时候会有一张表作为驱动表，在优化 sql 查询效率的时候首先需要考虑就是合适的表关联，第二表如果在做表关联的时候有大表关联，要尽量避免重复关联； 驱动表被驱动表关系：

A left join B，A 为驱动表，B 为被驱动表;
A right join B，A 为被驱动表，B 为驱动表;
A inner join B，那一张表的数据量小，那一张表就是驱动表，另一张为被驱动表;

mysql 在做表关联的时候使用驱动表去开启被驱动表的查询连接的，举个例子，比如：我有 A、B 两张表，A 表有 8w 数据量，B 表有 70w 数据量，那么我在 A left join B 的时候，其实是用 A 表作为驱动表，开启了 8w 次 B 表的查询，如果是 A right join B 的时候，就是用 B 表作为驱动表开启了 70w 次 A 表的查询，那么查询的速度是有很明显的区别的。

下面看一组查询： 优化前sql，我们可以看到该走的索引都走了，查询时间为5.87s： 优化后sql，将多关联的大表需要的字段挪到了已经关联的表中，查询时间为3.21s：

mysql大数据迁移

燕京eam系统有一张表数据量在2700w条，做数据迁移的时候直接导入导出非常慢，在不想写代码的情况下，采用了mysql的select data into outfile file.txt、load data infile file.txt into table，百万条数据大约在1分钟左右；

以下以燕京eam的kpi_result表为例

1.select * from kpi_result into outfile ‘/var/lib/mysql-flies/kpiresult.txt’; 注：

1.在不修改mysql的conf文件的情况下，‘/var/lib/mysql-flies/’路径不能修改，因为# secure-file-priv设置了默认路径，否则会报错；

2.可以使用show variables like ‘%secure%’命令查看secure-file-priv的默认路径；

3./var/lib/mysql-flies/路径是容器路径;

4.可在rancher中依次执行，cd /var/lib/mysql-flies=>ls查看文件是否保存成功

2.登陆服务器用docker命令将容器内的kpiresult.txt拷贝到opt目录下，具体命令如下： docker cp ${镜像名}:/var/lib/mysql-files/kpiresult.txt /opt

3.将拷贝出的文件压缩，命令如下: tar -cvf newfilename.tgz filename （-c 参数是建立新的存档，-v 参数详细显示处理的文件， -f 参数指定存档或设备,newfilename.tar是指压缩之后的文件名称，filename是指要压缩的文件名称）

4.下载压缩后的文件到本地，并上传到需要迁移的数据库的服务器上，放置在mysql容器的映射目录的backup下，即/data/mysql/backup/下，然后在rancher中执行命令，讲文件拷贝到mysql容器的/var/lib/mysql-flies/下，具体命令如下：

cp /var/lib/mysql/backup/kpiresult.tgz /var/lib/mysql-files/kpiresult.tgz 其中/var/lib/mysql/对应的是服务器上映射/data/mysql映射的地址**

5.解压tgz文件到当前目录，命令如下： tar zxvf kpiresult.tgz -C ./

6.在mysql中执行

load，load data infile '/var/lib/mysql-files/fans.txt' into table luckyun_eam_bjyj_bj.kpi_result (indocno, sregid, dregt, idel, skey, ilibraryid, stitle, sunit, datekpi, iplanvalue, iactualvalue, state, isortlib, isortobj, istate, isystemid, ideptid, iuserid, swbz, icompanyid, itype, inumerator, idenominator, iorgvalue);

mysql全备数据库sql恢复单库

公司现有的备份机制都是全库备份的，直接全库恢复会将mysql的系统库覆盖，某些时候我们只需要对单库进行恢复，具体操作如下：
1.将全库备份文件压缩上传到mysql服务器，放在容器持久化目录中，保证进入容器可以读到文件，解压缩;
2.docker exec -it aaeb80939914 /bin/bash进入容器;
3.mysql -uroot -pluckserver --init-command=“SET FOREIGN_KEY_CHECKS=0;” -D --force luckyun_bjyj_bj -o < all-databases-2023-08-14.sql；

–init-command="SET FOREIGN_KEY_CHECKS=0;"是导入时设置外键禁用；
–force跳过错误继续执行；

4.执行完成后mysql -uroot -pluckserver --init-command=“SET FOREIGN_KEY_CHECKS=1;” luckyun_bjyj_bj 恢复外键使用即可

三、其它

spring事务级别

Spring 框架支持与 Java EE 一样的标准事务隔离级别，这些隔离级别定义了在多个事务并发执行时数据的可见性和互操作性。在 Spring 中，你可以使用 @Transactional 注解的 isolation 属性来指定事务的隔离级别。以下是 Spring 支持的事务隔离级别：

Isolation.DEFAULT（默认值）：使用底层事务管理器的默认隔离级别。通常，这是数据库的默认隔离级别。
Isolation.READ_UNCOMMITTED：最低的隔离级别，允许脏读、不可重复读和幻读。在该隔离级别下，一个事务可以读取另一个事务未提交的数据。
Isolation.READ_COMMITTED：阻止脏读，但允许不可重复读和幻读。在该隔离级别下，一个事务只能读取已提交的数据。
Isolation.REPEATABLE_READ：阻止脏读和不可重复读，但允许幻读。在该隔离级别下，一个事务只能读取已提交的数据，并且在同一个事务内的查询将看到一致的数据。
Isolation.SERIALIZABLE：最高的隔离级别，阻止脏读、不可重复读和幻读。在该隔离级别下，事务是串行执行的，确保了最高级别的数据一致性，但可能会牺牲一些性能。

在排查燕京项目数据生成重复时，发现使用了事务，但是我在间隔很短的时间内操作了同一批数据，会导致事务重叠，意思就是在两个线程内，处理了同一条数据，那么在事务提交的时候其实是共用的同一个事务，这就导致即使我接口调用存在时间差，但是我生成的两条重复数据创建时间是一致的。

解决：将事务级别调到最高，因为我这里存在反更新操作，所以脏读换读也是需要避免的，这个时候再对同一条数据进行操作时，spring的事务管理会将异常抛出。 ps:一开始的解决思路是redis，但是如果用redis处理会需要手动处理所有异常，所以将这种方案排除了。但是通过提高事务级别会牺牲性能。