java六绝之MySQL

一:数据库优化

1.缓存

基于hash表，mybatis默认开启一级缓存，一级缓存基于sqlSession默认开启,在操作数据库时需要构造SqlSession对象，在对象中有一个HashMap用于存储缓存数据。不同的SqlSession之间的缓存数据区域是互相不影响的。
二级缓存需要自己去开启，配置xml中的set标签

2.主从同步、读写分离

利用主从数据库来实现读写分离，从而分担主数据库的压力。在多个服务器上部署mysql，将其中一台认为主数据库，而其他为从数据库，实现主从同步。其中主数据库负责主动写的操作，而从数据库则只负责主动读的操作（slave从数据库仍然会被动的进行写操作，为了保持数据一致性），这样就可以很大程度上的避免数据丢失的问题，同时也可减少数据库的连接，减轻主数据库的负载。
https://blog.csdn.net/qq_15092079/article/details/81672920
主服务器实现写的操作，从服务器实现读操作，主从实现原理，将主服务器对数据库的操作都会被记录到日志binlog中，从服务器会得到这个日志文件，一条条执行这些操作，当然从服务器肯定会有延迟，mysql默认异步复制，就是主服务器只负责自己的操作，至于从服务器是否完成操作它不关心。此外还有同步复制，主服务器等待所有从服务器完成操作再继续操作，半异步复制，主服务器等待第一个从服务器完成操作再继续操作

3.分表分库

水平切分：对数据行的切分
垂直切分：根据列来分表
使用中间件：如mycat

4.SQL优化

关于数据库优化主要从存储引擎和sql语句优化考虑
sql语句主要从索引失效看
1.任何对列的操作都会使得表扫描，包括函数和计算表达式，我们应该尽量把这些操作放在等号右边
2.or，in会导致索引失效，尽量进行一个拆分字句的操作，字句内使用索引

存储引擎：

关于Innodb 和myisam的取舍：

Innodb ：数据完整性，并发性处理，擅长更新，删除。行级锁 支持事务

myisam：高速查询及插入。擅长插入和查询。不支持事务

具体举例：

那么对于微博项目来看，选择哪一个存储引擎呢？
a.微博主要是插入微博和查询微博列表，较为适合MyISAM；
b.微博在更新微博和删除微博，要少的多，较为适合MyISAM；
c.对数据完整性的需求并没有那么强烈，比如用户删除微博，关联的转播和评论并不要求都做相应的行为，较为适合MyISAM；

那么对于记账财务系统，选择哪一款存储引擎呢？
a.财务系统除了读取和插入，经常要进行数据的修改和删除，较为适合InnoDB；
b.在进行财务变更的时候，如果失败需要回滚必须用到事务，较为适合InnoDB；
c.每个用户的财务数据完整性和同步性非常重要，需要外键支持，否则财务将会混乱，较为适合InnoDB。

5.执行计划(explain)

explain这个命令来查看SQL语句的执行计划，查看该SQL语句有没有使用上了索引，有没有做全表扫描，这都可以通过explain命令来查看。
使用:1.explain 表名(查询表结构)

        EXPLAIN student

结果:

查询表结构

2.explain sql语句(获取sql的执行的重要信息,便于优化)

EXPLAIN SELECT * from student

结果:

image.png

获取执行计划的字段含义:
id:select标识符,表明执行的顺序,数字越大优先级越高
select_type:查询中每个select字句的类型
table:查询中每个select字句使用的表
type:查询执行计划中表使用的连接方式

type常见的几种连接方式:
const:当表中只有一行数据匹配时出现
system:const的一种特例,表示系统表
eq_ref:对于每张来自前面的表的行组合,从该表中读取一行,可能是最好的连接方式
ref:对于每个来自于前面表的行组合,所有有匹配索引值的行将从该表获取
range:这个值表示所有符合给定范围值的索引行都被使用到
All:表示需要一次全表扫描,最差的连接方式,需要sql优化

possible_keys:完成对行数据查询能够使用的索引
key:实际完成查询使用的索引
key_len:实际使用索引键的长度
ref:选择哪个列或者常数与key一起使用查询行
rows:执行计划所需要查询的行数
extra:MySQL执行查询时的额外信息

当type为ALL,extra中出现using filesort,usingtempoary表示sql需要优化

6.如何实时排查慢查询

show full processlist 查出查询性能低时间长的语句 使用kill杀死sql进程
1. SHOW PROCESSLIST 命令详解

返回字段含义
字段名 含义  说明
Id  连接ID    每个连接的唯一标识符
User    用户名 执行该查询的用户
Host    客户端主机   客户端连接的IP地址和端口
db  数据库名    当前使用的数据库
Command 命令类型    当前执行的命令类型
Time    执行时间    命令执行的时间（秒）
State   状态  当前连接的状态
Info    查询信息    正在执行的SQL语句

Command字段常见值
Query: 正在执行查询
Sleep: 连接空闲
Connect: 正在连接
Kill: 正在终止连接
Binlog Dump: 主从复制相关
Daemon: 后台进程
State字段常见值
Sending data: 正在发送数据
Copying to tmp table: 复制到临时表
Sorting result: 排序结果
Locked: 被锁定
Sleep: 空闲状态
Starting: 正在启动
Statistics: 正在收集统计信息

2. 如何识别慢SQL
方法一：通过Time字段判断

-- 查看执行时间超过10秒的查询
SELECT * FROM information_schema.PROCESSLIST 
WHERE COMMAND = 'Query' AND TIME > 10;

方法二：使用慢查询日志

-- 开启慢查询日志
SET GLOBAL slow_query_log = 'ON';
SET GLOBAL long_query_time = 2; -- 设置慢查询阈值为2秒
SET GLOBAL slow_query_log_file = '/var/log/mysql/slow.log';

方法三：实时监控长时间运行的查询

-- 监控执行时间超过30秒的查询
SELECT 
    id,
    user,
    host,
    db,
    command,
    time,
    state,
    LEFT(info, 100) as sql_preview
FROM information_schema.PROCESSLIST 
WHERE command = 'Query' 
    AND time > 30
ORDER BY time DESC;

-- LEFT(info, 100)：截取SQL前100字符平衡可读性与性能

3. SQL性能优化完整流程
第一步：分析执行计划 即执行EXPLAIN

第二步：检查索引使用情况

-- 查看表的索引信息
SHOW INDEX FROM table_name;

-- 查看索引使用统计
SELECT 
    table_name,
    index_name,
    cardinality
FROM information_schema.STATISTICS 
WHERE table_schema = 'your_database'
    AND table_name = 'your_table';

第三步：优化策略
3.1 索引优化

-- 创建复合索引
CREATE INDEX idx_age_city ON users(age, city);

-- 创建覆盖索引
CREATE INDEX idx_covering ON users(age, city, name, email);

-- 删除无用索引
DROP INDEX index_name ON table_name;

3.2 查询优化

-- 避免SELECT *
SELECT id, name, email FROM users WHERE age > 25;

-- 使用LIMIT限制结果集
SELECT * FROM users WHERE age > 25 LIMIT 100;

-- 使用EXISTS代替IN
SELECT * FROM orders o 
WHERE EXISTS (
    SELECT 1 FROM users u 
    WHERE u.id = o.user_id AND u.age > 25
);

3.3 表结构优化

-- 优化字段类型
ALTER TABLE users MODIFY COLUMN age TINYINT UNSIGNED;

-- 添加分区
ALTER TABLE orders PARTITION BY RANGE (YEAR(create_time)) (
    PARTITION p2023 VALUES LESS THAN (2024),
    PARTITION p2024 VALUES LESS THAN (2025)
);
 -- 仅扫描p2023分区:ml-citation{ref="2,3" data="citationList"}
SELECT * FROM orders PARTITION (p2023);
-- 当WHERE条件包含分区键字段时，MySQL会自动筛选相关分区，无需人工指定分区名。

** 自动化处理脚本**

-- 生成KILL语句批量终止慢查询
SELECT CONCAT('KILL ', id, ';') AS kill_command
FROM information_schema.PROCESSLIST 
WHERE command = 'Query' AND time > 30
ORDER BY time DESC;

连接泄漏检测

SELECT user, host, db, time
FROM information_schema.PROCESSLIST
WHERE command = 'Sleep' AND time > 3600

7.索引

索引是什么

索引是一种存储在硬盘上的，对数据库表中一列或多个列进行排序的数据结构。
sql索引有两种，聚集索引和非聚集索引，它们主要提升sql server系统的性能，加快数据的查询速度和减少系统的响应时间
聚集索引它在物理内存上是连续的，比如主键索引，类似字典中根据拼音查询
非聚集索引它在逻辑上是连续，但在内存上是不连续的，类似字典中根据部首查询，非聚集索引可以看做一个只包含非聚集索引列的表和一个指向实际物理表的指针
优化：
1.任何对列的操作都会使得表扫描，包括函数和计算表达式，我们应该尽量把这些操作放在等号右边
2.or，in会导致索引失效，尽量进行一个拆分字句的操作，字句内使用索引

建立索引的原则：

  1. 定义主键的数据列一定要建立索引。

  2. 定义有外键的数据列一定要建立索引。

  3. 对于经常查询的数据列最好建立索引。

  4. 对于需要在指定范围内的快速或频繁查询的数据列;

  5. 经常用在WHERE子句中的数据列。

  6. 经常出现在关键字order by、group by、distinct后面的字段，建立索引。如果建立的是复合索引，索引的字段顺序要和这些关键字后面的字段顺序一致，否则索引不会被使用。

  7. 对于那些查询中很少涉及的列，重复值比较多的列不要建立索引。

  8. 对于定义为text、image和bit的数据类型的列不要建立索引。

  9. 对于经常存取的列避免建立索引

  10. 限制表上的索引数目。对一个存在大量更新操作的表，所建索引的数目一般不要超过3个，最多不要超过5个。索引虽说提高了访问速度，但太多索引会影响数据的更新操作。

  11. 对复合索引，按照字段在查询条件中出现的频度建立索引。在复合索引中，记录首先按照第一个字段排序。对于在第一个字段上取值相同的记录，系统再按照第二个字段的取值排序，以此类推。因此只有复合索引的第一个字段出现在查询条件中，该索引才可能被使用,因此将应用频度高的字段，放置在复合索引的前面，会使系统最大可能地使用此索引，发挥索引的作用。

五种索引：

普通索引 ： 列值可以取空值或重复值。创建使用关键字INDEX或KEY；
唯一索引：列值不能重复；即索引列值必须是唯一的，但可以是空值；创建使用关键字UNIQUE；
主键索引：主键索引是系统自动创建的主键索引，并且是唯一的。与唯一索引区别是；列值不能为空；
组合索引：就是数据存储的物理存储顺序，非聚簇索引就是索引顺序与数据的物理顺序无关。一个表只能有一个聚簇索引。目前只有InoDB和solidDB支持。
全文索引：只能创建在varchar或text的列上；建立全文索引能够在全文索引的列上进行查找。
覆盖索引：在表列上创建覆盖索引时，需要指定哪些额外的列值需要和聚集索引键值(主键)一起存储在索引页中。

如何设计表，要怎么考虑

四范式和业务需求：
1.第一范式：
不可再分，将表字段设计到不可再分，如地址设置到省市县区
2.第二范式：
属性完全直接依赖主键，简而言之我们必须建立主键，主键是唯一标识
3.第三范式：
属性不依赖于其他非主属性
非主属性，即除主码和候选码中外的属性，当有属性依赖非主属性，应该再继续拆分，比如一张表内有主键id，name，分数，分数等级，分数等级依赖于分数，应该再分一张分数等级表
4.第四范式：
禁止非主键列与其他非主键列存在一对多关系，如果存在，应该再分表


加油
索引的使用与创建
drop TABLE if exists my_test;
create table my_test(
    id int(11) not null unique auto_increment,
    name varchar(10) not null default 0,
  age int(11) not null default 0
) engine=innodb charset=utf8;

#查看表my_test的全部索引
show index from my_test;

#创建索引
create index index_n on my_test(name); #为表my_text中的列name创建普通索引名称index_n
create index index_age on my_test(age DESC); #为age做降序索引
create index index_age_name on my_test(age,name DESC);#建立表列age和name的联合索引；按照age升序后再按照name降序排序

#删除索引
drop index index_n on my_test;

#修改索引
alter table my_test drop index index_age;#删除索引
alter table my_test add index index_age(age);
索引失效的情况

1.%查询字段%，模糊查询前有占位符会使得索引失效
2.当索引需要进行加减乘除等函数操作时会失效
3.当组合索引的前列索引未使用到，会失效

8.存储引擎

innodb存储引擎：mysql的默认存储引擎，主要特点：
1.灾难恢复性比较好
2.支持事务。默认是支持可重复读，根据mvcc(并发版本控制)实现的
3.使用的锁级是行级锁，支持的并发更高
4.支持外键
5.可以通过一些工具实现热备份
6.对于Innodb存储引擎，它内部存在缓冲管理，可以把索引与数据存在缓存里，加快查询速度
7.对于Innodb类型表，物理结构是聚簇表，索引与数据存在一起，都位于b+树的叶子节点上

9.为什么底层使用B+树

哈希虽然能够提供 O(1) 的单数据行操作性能，但是对于范围查询和排序却无法很好地支持，最终导致全表扫描；

B 树能够在非叶节点中存储数据，但是这也导致在查询连续数据时可能会带来更多的随机 I/O，而 B+ 树的所有叶节点可以通过指针相互连接，能够减少顺序遍历时产生的额外随机 I/O；

因为磁盘IO问题。之前说过，索引是存储在磁盘上的，对数据库表中一列或多列进行排序的数据结构。数据库中的数据可能很大，在大量的数据存储在磁盘上时。计算机无法一次性将数据全部加载进内存。而是通过逐一加载每一磁盘页。而加载磁盘页对应着索引树的节点。那么一次查找所经历的索引树的深度对应着磁盘IO交互的次数。如果采取二叉树结构，那么显而易见，可能导致遍历的深度太大导致磁盘IO交互的次数太多。而相较于内存查询，磁盘IO才是影响查询以及更新表中数据的关键。那么由二叉树这样的瘦长结构自然容易联想到如何将它变的矮胖。这样做虽然没有降低比较次数，由于深度的降低，可以极大的减少磁盘IO次数。从而可以提升查询以及更新数据的性能。

二:ACID

1.隔离级别

读未提交
读已提交：解决了脏读问题
脏读问题描述：
脏读是指在一个事务处理过程里读取了另一个未提交的事务中的数据。当一个事务正在多次修改某个数据，而在这个事务中这多次的修改都还未提交，这时一个并发的事务来访问该数据，就会造成两个事务得到的数据不一致。
可重复读：解决脏读和不可重复读
不可重复读问题描述：
不可重复读是指在对于数据库中的某条数据，一个事务范围内多次查询返回不同的数据值(这里不同是指某一条或多条数据的内容前后不一致，但数据条数相同)，这是由于在查询间隔，该事物需要用到的数据被另一个事务修改并提交了。
串行化：解决幻读
幻读问题描述：
当一个事务开启，对其中一个条件为id=1进行查询为null，此时另外一个事务添加一个id=1，然后第一个事务中再查询一次id=1依旧是为null，此时该事务也添加一个id=1的值，执行不成功，事务提交，失败。

事务四大特性：

原子性：将事务看做一个整体，同成功同失败
一致性：事务必须从一个一致性状态变为另一个一致性状态
隔离性：一个事务的执行不能被其他事务干扰
持久性：一个事务一旦成功提交，数据将持久化为永久状态，接下来的其他操作或者故障不应该对它产生影响

事务传播行为

https://segmentfault.com/a/1190000013341344
一共有七种传播行为：
1.PROPAGATION_REQUIRED（必需） 如果当前方法存在一个事务，则将该方法置于同一个事物中，如果之前不存在事务，则另新开启一个事物（delete ，insert update）
2.PROPAGATION_SUPPORT（支持）S 如果当前方法存在一个事务，则将该方法置于同一个事物中，如果之前不存在事务，则进行非事务执行（select）
3.PROPAGATION_MANDATORY（强制） 如果已经存在一个事务，支持当前事务。如果没有一个活动的事务，则抛出异常。
4.PROPAGATION_REQUIRES_NEW（必须新建） 使用PROPAGATION_REQUIRES_NEW,需要使用 JtaTransactionManager作为事务管理器。
它会开启一个新的事务。如果一个事务已经存在，则先将这个存在的事务挂起。
5.PROPAGATION_NOT_SUPPORTED（不支持） PROPAGATION_NOT_SUPPORTED 总是非事务地执行，并挂起任何存在的事务。使用PROPAGATION_NOT_SUPPORTED,也需要使用JtaTransactionManager作为事务管理器。
6.PROPAGATION_NEVER（从不） 总是非事务地执行，如果存在一个活动事务，则抛出异常。
7.PROPAGATION_NESTED （嵌套）如果一个活动的事务存在，则运行在一个嵌套的事务中。 如果没有活动事务, 则按TransactionDefinition.PROPAGATION_REQUIRED 属性执行

2.MVCC(乐观锁除此外还有CAS实现)

多版本并发控制(Multi-Version Concurrency Control, MVCC)是MySQL中基于乐观锁理论实现隔离级别的方式，用于实现读已提交和可重复读取隔离级别的实现。
实现(隔离级别为可重复读)
在说到如何实现前先引入两个概念：

系统版本号：一个递增的数字，每开始一个新的事务，系统版本号就会自动递增。
事务版本号：事务开始时的系统版本号。

在MySQL中，会在表中每一条数据后面添加两个字段：

创建版本号：创建一行数据时，将当前系统版本号作为创建版本号赋值
删除版本号：删除一行数据时，将当前系统版本号作为删除版本号赋值

进行Select时，当前事务版本号应该大于等于创建版本号，删除版本号为空或者小于事务版本号
创建版本号<=当前事务版本号保证取出的数据不会有后启动的事物中创建的数据。这也是为什么在开始的示例中我们不会查出后来添加的数据的原因

删除版本号为空或>当前事务版本号保证了至少在该事物开启之前数据没有被删除，是应该被查出来的数据。

INSERT

insert时将当前的系统版本号赋值给创建版本号字段。

UPDATE
插入一条新纪录，保存当前事务版本号为行创建版本号，同时保存当前事务版本号到原来删除的行，实际上这里的更新是通过delete和insert实现的。

DELETE

删除时将当前的系统版本号赋值给删除版本号字段，标识该行数据在那一个事物中会被删除，即使实际上在位commit时该数据没有被删除。根据select的规则后开启懂数据也不会查询到该数据。
MVCC学习来自于如下链接：
https://www.jianshu.com/p/1975e453b2cf

3.MySQL锁

排它锁（Exclusive），又称为X 锁，写锁。
共享锁（Shared），又称为S 锁，读锁。
这两种锁即为悲观锁思想体现
读写锁之间有以下的关系：

一个事务对数据对象O加了 S 锁，可以对 O进行读取操作，但是不能进行更新操作。加锁期间其它事务能对O 加 S 锁，但是不能加 X 锁。
一个事务对数据对象 O 加了 X 锁，就可以对 O 进行读取和更新。加锁期间其它事务不能对 O 加任何锁。
即读写锁之间的关系可以概括为：多读单写

最后编辑于 ：
©著作权归作者所有,转载或内容合作请联系作者
【社区内容提示】社区部分内容疑似由AI辅助生成，浏览时请结合常识与多方信息审慎甄别。
平台声明：文章内容（如有图片或视频亦包括在内）由作者上传并发布，文章内容仅代表作者本人观点，简书系信息发布平台，仅提供信息存储服务。

友情链接更多精彩内容