MySQL的查询缓存默认是关闭的,需要手动配置参数 query_cache_type
, 来开启查询缓存。query_cache_type
该参数的可取值有三个 :
| 值 | 含义 |
| — | — |
| OFF 或 0 | 查询缓存功能关闭 |
| ON 或 1 | 查询缓存功能打开,SELECT的结果符合缓存条件即会缓存,否则,不予缓存,显式指定 SQL_NO_CACHE,不予缓存 |
| DEMAND 或 2 | 查询缓存功能按需进行,显式指定 SQL_CACHE 的SELECT语句才会缓存;其它均不予缓存 |
7-5 查询缓存SELECT选项
可以在SELECT语句中指定两个与查询缓存相关的选项 :
-
SQL_CACHE
: 如果查询结果是可缓存的,并且 query_cache_type 系统变量的值为ON或 DEMAND ,则缓存查询结果 。 -
SQL_NO_CACHE
: 服务器不使用查询缓存。它既不检查查询缓存,也不检查结果是否已缓存,也不缓存查询结果。
例子:
SELECT SQL_CACHE id, name FROM customer;
SELECT SQL_NO_CACHE id, name FROM customer;
7-6 查询缓存失效的情况
- SQL 语句不一致的情况, 要想命中查询缓存,查询的SQL语句必须一致。
SQL1 : select count(*) from tb_item;
SQL2 : Select count(*) from tb_item;
#s大小写不一样也不行
- 当查询语句中有一些不确定的时,则不会缓存。如 : now() , current_date() , curdate() , curtime() , rand() , uuid() , user() , database() 。
SQL1 : select * from tb_item where updatetime < now() limit 1;
SQL2 : select user();
SQL3 : select database();
- 不使用任何表查询语句。
select ‘A’;
- 查询 mysql, information_schema或 performance_schema 数据库中的表时,不会走查询缓存。
select * from information_schema.engines;
-
在存储的函数,触发器或事件的主体内执行的查询。
-
如果表更改,则使用该表的所有高速缓存查询都将变为无效并从高速缓存中删除。这包括使用
MERGE
映射到已更改表的表的查询。一个表可以被许多类型的语句,如被改变 INSERT, UPDATE, DELETE, TRUNCATE TABLE, ALTER TABLE, DROP TABLE,或 DROP DATABASE 。
8-1 内存优化原则
-
将尽量多的内存分配给MySQL做缓存,但要给操作系统和其他程序预留足够内存。
-
MyISAM 存储引擎的数据文件读取依赖于操作系统自身的IO缓存,因此,如果有MyISAM表,就要预留更多的内存给操作系统做IO缓存。
-
排序区、连接区等缓存是分配给每个数据库会话(session)专用的,其默认值的设置要根据最大连接数合理分配,如果设置太大,不但浪费资源,而且在并发连接较高时会导致物理内存耗尽。
8-2 MyISAM 内存优化
myisam存储引擎使用 key_buffer 缓存索引块,加速myisam索引的读写速度。对于myisam表的数据块,mysql没有特别的缓存机制,完全依赖于操作系统的IO缓存。
key_buffer_size:
key_buffer_size决定MyISAM索引块缓存区的大小,直接影响到MyISAM表的存取效率。可以在MySQL参数文件中设置key_buffer_size的值,对于一般MyISAM数据库,建议至少将1/4可用内存分配给key_buffer_size。
在/usr/my.cnf 中做如下配置:
key_buffer_size=512M
read_buffer_size:
如果需要经常顺序扫描myisam表,可以通过增大read_buffer_size的值来改善性能。但需要注意的是read_buffer_size是每个session独占的,如果默认值设置太大,就会造成内存浪费。
read_rnd_buffer_size:
对于需要做排序的myisam表的查询,如带有order by子句的sql,适当增加 read_rnd_buffer_size 的值,可以改善此类的sql性能。但需要注意的是 read_rnd_buffer_size 是每个session独占的,如果默认值设置太大,就会造成内存浪费。
8-3 InnoDB 内存优化
nnodb用一块内存区做IO缓存池,该缓存池不仅用来缓存innodb的索引块,而且也用来缓存innodb的数据块。
innodb_buffer_pool_size:
该变量决定了 innodb 存储引擎表数据和索引数据的最大缓存区大小。在保证操作系统及其他程序有足够内存可用的情况下,innodb_buffer_pool_size 的值越大,缓存命中率越高,访问InnoDB表需要的磁盘I/O 就越少,性能也就越高。
innodb_log_buffer_size:
决定了innodb重做日志缓存的大小,对于可能产生大量更新记录的大事务,增加innodb_log_buffer_size的大小,可以避免innodb在事务提交前就执行不必要的日志写入磁盘操作。
从实现上来说,MySQL Server 是多线程结构,包括后台线程和客户服务线程。多线程可以有效利用服务器资源,提高数据库的并发性能。在Mysql中,控制并发连接和线程的主要参数包括 max_connections
、back_log
、thread_cache_size
、table_open_cahce
。
9-1 max_connections
采用max_connections
控制允许连接到MySQL数据库的最大数量,默认值是 151。如果状态变量 connection_errors_max_connections
不为零,并且一直增长,则说明不断有连接请求因数据库连接数已达到允许最大值而失败,这是可以考虑增大max_connections
的值。
Mysql 最大可支持的连接数,取决于很多因素,包括给定操作系统平台的线程库的质量、内存大小、每个连接的负荷、CPU的处理速度,期望的响应时间等。在Linux 平台下,性能好的服务器,支持 500-1000 个连接不是难事,需要根据服务器性能进行评估设定。
9-2 back_log
back_log
参数控制MySQL监听TCP端口时设置的积压请求栈大小。如果MySql的连接数达到max_connections
时,新来的请求将会被存在堆栈中,以等待某一连接释放资源,该堆栈的数量即back_log
,如果等待连接的数量超过back_log
,将不被授予连接资源,将会报错。5.6.6 版本之前默认值为 50 , 之后的版本默认为 50 + (max_connections / 5), 但最大不超过900。
如果需要数据库在较短的时间内处理大量连接请求, 可以考虑适当增大back_log
的值。
9-3 table_open_cache
该参数用来控制所有SQL语句执行线程可打开表缓存的数量, 而在执行SQL语句时,每一个SQL执行线程至少要打开 1 个表缓存。该参数的值应该根据设置的最大连接数 max_connections
以及每个连接执行关联查询中涉及的表的最大数量来设定 :max_connections x N
;
9-4 thread_cache_size
为了加快连接数据库的速度,MySQL 会缓存一定数量的客户服务线程以备重用,通过参数 thread_cache_size
可控制 MySQL 缓存客户服务线程的数量。
9-5 innodb_lock_wait_timeout
该参数是用来设置InnoDB 事务等待行锁的时间,默认值是50ms , 可以根据需要进行动态设置。对于需要快速反馈的业务系统来说,可以将行锁的等待时间调小,以避免事务长时间挂起; 对于后台运行的批量处理程序来说, 可以将行锁的等待时间调大, 以避免发生大的回滚操作。
10-1 锁概述
锁是计算机协调多个进程或线程并发访问某一资源的机制(避免争抢)。
在数据库中,除传统的计算资源(如 CPU、RAM、I/O 等)的争用以外,数据也是一种供许多用户共享的资源。如何保证数据并发访问的一致性、有效性是所有数据库必须解决的一个问题,锁冲突也是影响数据库并发访问性能的一个重要因素。
10-2 锁分类
从对数据操作的粒度分 :
-
表锁:操作时,会锁定整个表。
-
行锁:操作时,会锁定当前操作行。
从对数据操作的类型分:
-
读锁(共享锁):针对同一份数据,多个读操作可以同时进行而不会互相影响。
-
写锁(排它锁):当前操作没有完成之前,它会阻断其他写锁和读锁。
10-3 Mysql锁
相对其他数据库而言,MySQL的锁机制比较简单,其最显著的特点是不同的存储引擎支持不同的锁机制。下表中罗列出了各存储引擎对锁的支持情况:
| 储引擎 | 表级锁 | 行级锁 |
| — | — | — |
| MyISAM | 支持 | 不支持 |
| InnoDB | 支持 | 支持 |
MySQL这2种锁的特性可大致归纳如下 :
| 锁类型 | 特点 |
| — | — |
| 表级锁 | 偏向MyISAM 存储引擎,开销小,加锁快;不会出现死锁;锁定粒度大,发生锁冲突的概率最高,并发度最低 |
| 行级锁 | 偏向InnoDB 存储引擎,开销大,加锁慢;会出现死锁;锁定粒度最小,发生锁冲突的概率最低,并发度也最高 |
从上述特点可见,很难笼统地说哪种锁更好,只能就具体应用的特点来说哪种锁更合适!仅从锁的角度来说:表级锁更适合于以查询为主,只有少量按索引条件更新数据的应用,如Web 应用;而行级锁则更适合于有大量按索引条件并发更新少量不同数据,同时又有并查询的应用,如一些在线事务处理(OLTP)系统。
10-4 MyISAM 表锁
MyISAM 存储引擎只支持表锁,这也是MySQL开始几个版本中唯一支持的锁类型。
如何加表锁:
MyISAM 在执行查询语句(SELECT)前,会自动给涉及的所有表加读锁,在执行更新操作(UPDATE、DELETE、INSERT 等)前,会自动给涉及的表加写锁,这个过程并不需要用户干预,因此,用户一般不需要直接用 LOCK TABLE 命令给 MyISAM 表显式加锁。
显示加表锁语法:
加读锁 : lock table table_name read;
加写锁 : lock table table_name write;
结论:
由上表可见:
-
对MyISAM 表的读操作,不会阻塞其他用户对同一表的读请求,但会阻塞对同一表的写请求;
-
对MyISAM 表的写操作,则会阻塞其他用户对同一表的读和写操作;
简而言之,就是读锁会阻塞写,但是不会阻塞读。而写锁,则既会阻塞读,又会阻塞写。
此外,MyISAM 的读写锁调度是写优先,这也是MyISAM不适合做写为主的表的存储引擎的原因。因为写锁后,其他线程不能做任何操作,大量的更新会使查询很难得到锁,从而造成永远阻塞。
查看锁的争用情况:
show open tables;
数为零,则表是打开的,但是当前没有被使用。
Name_locked
:表名称是否被锁定。名称锁定用于取消表或对表进行重命名等操作。
show status like ‘Table_locks%’;
Table_locks_immediate
: 指的是能够立即获得表级锁的次数,每立即获取锁,值加1。
Table_locks_waited
: 指的是不能立即获取表级锁而需要等待的次数,每等待一次,该值加1,此值高说明存在着较为严重的表级锁争用情况。
10-5 InnoDB行锁
10-5-1 行锁介绍
-
行锁特点 :偏向InnoDB 存储引擎,开销大,加锁慢;会出现死锁;锁定粒度最小,发生锁冲突的概率最低,并发度也最高。
-
InnoDB 与 MyISAM 的最大不同有两点:一是支持事务;二是采用了行级锁
10-5-2 事务相关知识
事务及其ACID属性:
事务是由一组SQL语句组成的逻辑处理单元。
事务具有以下4个特性,简称为事务ACID属性:
| ACID属性 | 含义 |
| — | — |
| 原子性(Atomicity) | 事务是一个原子操作单元,其对数据的修改,要么全部成功,要么全部失败。 |
| 一致性(Consistent) | 在事务开始和完成时,数据都必须保持一致状态。 |
| 隔离性(Isolation) | 数据库系统提供一定的隔离机制,保证事务在不受外部并发操作影响的 “独立” 环境下运行。 |
| 持久性(Durable) | 事务完成之后,对于数据的修改是永久的。 |
并发事务处理带来的问题:
| 问题 | 含义 |
| — | — |
| 丢失更新(Lost Update) | 当两个或多个事务选择同一行,最初的事务修改的值,会被后面的事务修改的值覆盖。 |
| 脏读(Dirty Reads) | 当一个事务正在访问数据,并且对数据进行了修改,而这种修改还没有提交到数据库中,这时,另外一个事务也访问这个数据,然后使用了这个数据。 |
| 不可重复读(Non-Repeatable Reads) | 一个事务在读取某些数据后的某个时间,再次读取以前读过的数据,却发现和以前读出的数据不一致。 |
| 幻读(Phantom Reads) | 一个事务按照相同的查询条件重新读取以前查询过的数据,却发现其他事务插入了满足其查询条件的新数据。 |
事务隔离级别:
为了解决上述提到的事务并发问题,数据库提供一定的事务隔离机制来解决这个问题。数据库的事务隔离越严格,并发副作用越小,但付出的代价也就越大,因为事务隔离实质上就是使用事务在一定程度上“串行化” 进行,这显然与“并发” 是矛盾的。
数据库的隔离级别有4个,由低到高依次为Read uncommitted
、Read committed
、Repeatable read
、Serializable
,这四个级别可以逐个解决脏写、脏读、不可重复读、幻读这几类问题。
| 隔离级别 | 丢失更新 | 脏读 | 不可重复读 | 幻读 |
| — | — | — | — | — |
| Read uncommitted | × | √ | √ | √ |
| Read committed | × | × | √ | √ |
| Repeatable read(默认) | × | × | × | √ |
| Serializable | × | × | × | × |
备注 : √ 代表可能出现 , × 代表不会出现 。
Mysql 的数据库的默认隔离级别为 Repeatable read , 查看方式:
show variables like ‘tx_isolation’;
10-5-3 InnoDB 的行锁模式
InnoDB 实现了以下两种类型的行锁:
-
共享锁(S):又称为读锁,简称S锁,共享锁就是多个事务对于同一数据可以共享一把锁,都能访问到数据,但是只能读不能修改。
-
排他锁(X):又称为写锁,简称X锁,排他锁就是不能与其他锁并存,如一个事务获取了一个数据行的排他锁,其他事务就不能再获取该行的其他锁,包括共享锁和排他锁,但是获取排他锁的事务是可以对数据就行读取和修改。
注:对于UPDATE、DELETE和INSERT语句,InnoDB会自动给涉及数据集加排他锁(X);对于普通SELECT语句,InnoDB不会加任何锁;
可以通过以下语句显示给记录集加共享锁或排他锁 :
共享锁(S):SELECT * FROM table_name WHERE … LOCK IN SHARE MODE
排他锁(X) :SELECT * FROM table_name WHERE … FOR UPDATE
10-5-4 行锁演示
准备表和数据:
create table test_innodb_lock(
id int(11),
name varchar(16),
sex varchar(1)
)engine = innodb default charset=utf8;
insert into test_innodb_lock values(1,‘100’,‘1’);
insert into test_innodb_lock values(3,‘3’,‘1’);
insert into test_innodb_lock values(4,‘400’,‘0’);
insert into test_innodb_lock values(5,‘500’,‘1’);
insert into test_innodb_lock values(6,‘600’,‘0’);
insert into test_innodb_lock values(7,‘700’,‘0’);
insert into test_innodb_lock values(8,‘800’,‘1’);
insert into test_innodb_lock values(9,‘900’,‘1’);
insert into test_innodb_lock values(1,‘200’,‘0’);
create index idx_test_innodb_lock_id on test_innodb_lock(id);
create index idx_test_innodb_lock_name on test_innodb_lock(name);
10-5-5 无索引行锁升级为表锁
如果不通过索引条件检索数据,那么InnoDB将对表中的所有记录加锁,实际效果跟表锁一样
关闭Session-1和 Session-2的事务自动提交:
SET autocommit=0;
Session-1执行更新语句:
UPDATE test_innodb_lock SET sex=‘2’ WHERE NAME=400;//这里应该’400’
Session-1执行更新语句,但处于阻塞状态:
UPDATE test_innodb_lock SET sex=‘2’ WHERE id=9;
当Session-1提交事务,此时Session-2就会解除阻塞,然后在提交事务:
COMMIT;
由于Session-1 执行更新时 , name字段本来为varchar类型, 我们是作为数组类型使用,存在类型转换,索引失效,最终行锁变为表锁 ;
10-5-6 间隙锁危害
自我介绍一下,小编13年上海交大毕业,曾经在小公司待过,也去过华为、OPPO等大厂,18年进入阿里一直到现在。
深知大多数Java工程师,想要提升技能,往往是自己摸索成长或者是报班学习,但对于培训机构动则几千的学费,着实压力不小。自己不成体系的自学效果低效又漫长,而且极易碰到天花板技术停滞不前!
因此收集整理了一份《2024年Java开发全套学习资料》,初衷也很简单,就是希望能够帮助到想自学提升又不知道该从何学起的朋友,同时减轻大家的负担。
既有适合小白学习的零基础资料,也有适合3年以上经验的小伙伴深入学习提升的进阶课程,基本涵盖了95%以上Java开发知识点,真正体系化!
由于文件比较大,这里只是将部分目录大纲截图出来,每个节点里面都包含大厂面经、学习笔记、源码讲义、实战项目、讲解视频,并且后续会持续更新
如果你觉得这些内容对你有帮助,可以添加V:vip1024b 备注Java获取(资料价值较高,非无偿)
最后
由于篇幅有限,这里就不一一罗列了,20道常见面试题(含答案)+21条MySQL性能调优经验小编已整理成Word文档或PDF文档
还有更多面试复习笔记分享如下
Java开发全套学习资料》,初衷也很简单,就是希望能够帮助到想自学提升又不知道该从何学起的朋友,同时减轻大家的负担。**
[外链图片转存中…(img-qyJEOLgo-1711594626984)]
[外链图片转存中…(img-2jSCo6Z0-1711594626985)]
[外链图片转存中…(img-viPKE2uP-1711594626986)]
[外链图片转存中…(img-WUleXiTl-1711594626986)]
既有适合小白学习的零基础资料,也有适合3年以上经验的小伙伴深入学习提升的进阶课程,基本涵盖了95%以上Java开发知识点,真正体系化!
[外链图片转存中…(img-Syllhmod-1711594626987)]
[外链图片转存中…(img-xbm5bs64-1711594626988)]
由于文件比较大,这里只是将部分目录大纲截图出来,每个节点里面都包含大厂面经、学习笔记、源码讲义、实战项目、讲解视频,并且后续会持续更新
如果你觉得这些内容对你有帮助,可以添加V:vip1024b 备注Java获取(资料价值较高,非无偿)
[外链图片转存中…(img-ZOvRTgGj-1711594626988)]
最后
由于篇幅有限,这里就不一一罗列了,20道常见面试题(含答案)+21条MySQL性能调优经验小编已整理成Word文档或PDF文档
[外链图片转存中…(img-uebThxNH-1711594626989)]
还有更多面试复习笔记分享如下
[外链图片转存中…(img-wai9nOE3-1711594626989)]