mysql数据库sql优化及ACID和事务的隔离级别

最新推荐文章于 2024-08-15 01:26:58 发布

mist星

最新推荐文章于 2024-08-15 01:26:58 发布

阅读量901

点赞数 17

文章标签：数据库 mysql sql

本文链接：https://blog.csdn.net/wanxingahlal/article/details/140498351

版权

数据库相关知识

1.如何优化sql

1. 硬件和操作系统层面的优化

影响Mysql性能的因素有，CPU、可用内存大小、磁盘读写速度、网络带宽

从操作系层面来说，应用文件句柄数、操作系统网络的配置都会影响到Mysql性能

2. 架构设计层面的优化

搭建主从集群，保证高可用性

读写分离设计在读多写少的情况下避免读写冲突导致的性能影响

分库分表分库可以降低单个服务器的压力，分表可以降低单个表的数据量

针对热点数据进行缓存

3. mysql程序配置优化

调整 InnoDB 缓冲池大小

--  innodb_buffer_pool_size：设置为可用内存的 70%-80%，以提高数据缓存能力。
SET GLOBAL innodb_buffer_pool_size = SIZE_IN_BYTES;

调整查询缓存

-- query_cache_size 和 query_cache_type：设置查询缓存大小和类型。注意，MySQL 8.0 已经废弃了查询缓存功能。
SET GLOBAL query_cache_size = SIZE_IN_BYTES;
SET GLOBAL query_cache_type = 1;  -- 0: 禁用, 1: 启用

调整连接和线程相关参数

max_connections：增加最大连接数以支持更多并发连接。
thread_cache_size：设置线程缓存大小以减少线程创建开销。

调整日志和缓冲

innodb_log_file_size 和 innodb_log_buffer_size：调整 InnoDB 日志文件大小和日志缓冲区大小。

4. 索引失效

索引上进行函数运算

-- 索引失效
SELECT * FROM employees WHERE YEAR(hire_date) = 2022;

-- 索引有效
SELECT * FROM employees WHERE hire_date BETWEEN '2022-01-01' AND '2022-12-31';

索引使用用的类型不匹配，会隐式转换类型导致索引失效

-- hire_date 是 DATE 类型，'2022-01-01' 是字符串类型
-- 隐式类型转换导致索引失效
SELECT * FROM employees WHERE hire_date = '2022-01-01';

-- 正确使用
SELECT * FROM employees WHERE hire_date = DATE('2022-01-01');

使用!=或者not 查询此时索引效率非常低，因此mysql判断不走索引

-- 索引失效
SELECT * FROM employees WHERE salary != 5000;

-- 索引有效
SELECT * FROM employees WHERE salary > 5000 OR salary < 5000;

使用or的时候，or的前后都得使用索引，否则索引失效

-- 假设 name 列有索引，department 列无索引
-- 索引失效
SELECT * FROM employees WHERE name = 'John' OR department = 'HR';

-- 索引有效
-- 假设 name 和 department 列都有索引
SELECT * FROM employees WHERE name = 'John' OR department = 'HR';

组合索引需要匹配最左原则（查询条件需要跟索引的顺序一样）

-- 假设有组合索引 (name, age)
-- 索引失效
SELECT * FROM employees WHERE age = 30;

-- 索引有效
SELECT * FROM employees WHERE name = 'John' AND age = 30;

-- 索引部分有效，只用到 name 部分
SELECT * FROM employees WHERE name = 'John';

使用like 百分号放在左边时会失效

-- 假设 name 列有索引
-- 索引失效
SELECT * FROM employees WHERE name LIKE '%ohn';

-- 索引有效
SELECT * FROM employees WHERE name LIKE 'John%';

5. sql优化

1.慢sql定位：通过慢sql查询日志或者日志分析工具得到有问题的sql列表

2.使用explain 查看sql的执计划，重点关注type key rows filterd字段,执行慢的根本原因

3.使用show profile工具可以得到sql的资源使用 io，cpu、内存的使用情况

l SQL的查询一定要基于索引来进行数据扫描

l 避免索引列上使用函数或者运算,这样会导致索引失效

l where 字句中like %号,尽量放置在右边

l 使用索引扫描,联合索引中的列从左往右,命中越多越好.

l 尽可能使用SQL语句用到的索引完成排序,避免使用文件排序的方式

l 查询有效的列信息即可.少用 * 代替列信息

l 永远用小结果集驱动大结果集。

2.事务的隔离级别

名词解释

脏读（Dirty Read）

定义： 脏读是指一个事务读取了另一个事务尚未提交的数据。如果那个事务回滚了，那么第一个事务读取的数据就是无效的或错误的。

**示例：**
假设有两个事务 A 和 B。

- 事务 A 更新了数据但还未提交。
- 事务 B 读取了事务 A 尚未提交的数据。
- 如果事务 A 最终回滚，那么事务 B 读取的数据将是错误的。

总结
事务A读取到事务B未提交的数据 事务B回滚 导致事务A读取的数据无效 即为脏读

不可重复读（Non-repeatable Read）

定义： 不可重复读是指在一个事务中多次读取相同的数据时，读取到的数据可能会因为其他事务的提交而发生变化。

示例：

假设有两个事务 A 和 B。

- 事务 A 读取了某个数据。
- 事务 B 修改了那个数据并提交了。
- 事务 A 再次读取相同的数据时，得到的结果与第一次读取时不同。

总结
事务A读取了一行的数据，事务B修改了这行数据 导致重复读取的数据不一样 即为不可重复读

幻读（Phantom Read）

定义： 幻读是指在一个事务中执行查询时，另一事务对数据库的插入或删除操作导致前一个事务在再次执行相同查询时得到不同的结果。

示例：

假设有两个事务 A 和 B。

事务 A 执行了某个查询，获取了一些数据。
事务 B 插入了新的数据行。
事务 A 再次执行相同的查询时，得到的结果集包含了事务 B 插入的新数据行（这些数据在第一次查询时不存在）。

总结：
事务A读取了表中所有数据的条数 ，事务B添加了一条数据 倒是事务A第二次读取表中所有数据的总条数不一样 即为幻读

事务的ACID

原子性（Atomicity）：事务是一个不可分割的工作单位，要么全部执行成功，要么全部失败回滚。如果一个事务中的任何一个操作失败，整个事务都会被回滚到之前的状态，保持数据库的一致性。
一致性（Consistency）：事务的执行使数据库从一个一致性状态转变为另一个一致性状态。事务的执行不能破坏数据库的完整性约束（例如，唯一性约束、外键约束等），数据库始终保持一致性。
隔离性（Isolation）：多个事务同时执行时，每个事务都应该被隔离开来，不受其他事务的影响。隔离性确保事务之间的并发执行不会导致数据的不一致性。隔离级别用于控制事务之间的相互影响程度。
持久性（Durability）：一旦事务成功提交，其结果应该永久保存在数据库中，即使系统崩溃或断电也不会丢失。数据库系统使用日志和其他机制来确保已提交的事务在系统失败后仍然可以恢复。