centos 数据库管理2
1,缓存管理 2,索引管理 3,加锁解锁 4,事务管理
一,缓存管理
1. 查询缓存原理
缓存SELECT操作或预处理查询的结果集和SQL语句,当有新的SELECT语句或预处理查询语句请求, 先去查询缓存,判断是否存在可用的记录集,判断标准:与缓存的SQL语句,是否完全一样,区分大小 写
2. 优缺点
不需要对SQL语句做任何解析和执行,当然语法解析必须通过在先,直接从Query Cache中获得查询 结果,提高查询性能
查询缓存的判断规则,不够智能,也即提高了查询缓存的使用门槛,降低效率
查询缓存的使用,会增加检查和清理Query Cache中记录集的开销
3. 哪些查询可能不会被缓存
a. 查询语句中加了SQL_NO_CACHE参数
b. 查询语句中含有获得值的函数,包含自定义函数,如:NOW() CURDATE()、GET_LOCK()、RAND()、CONVERT_TZ()等
c. 对系统数据库的查询:mysql、information_schema 查询语句中使用SESSION级别变量或存储过 程中的局部变量
d. 查询语句中使用了LOCK IN SHARE MODE、FOR UPDATE的语句,查询语句中类似SELECT … INTO 导出数据的语句
e. 对临时表的查询操作;存在警告信息的查询语句;不涉及任何表或视图的查询语句;某用户只有列 级别权限的查询语句
f. 事务隔离级别为Serializable时,所有查询语句都不能缓存
4. SELECT语句的缓存控制
a. SQL_CACHE:显式指定存储查询结果于缓存之中
b. SQL_NO_CACHE:显式查询结果不予缓存 query_cache_type参数变量
5. 查询缓存相关的状态变量:
query_cache_min_res_unit:查询缓存中内存块的最小分配单位,默认4k,较小值会减少浪费, 但会导致更频繁的内存分配操作,较大值会带来浪费,会导致碎片过多,内存不足
query_cache_limit:单个查询结果能缓存的最大值,默认为1M,对于查询结果过大而无法缓存的 语句,建议使用SQL_NO_CACHE
query_cache_size:查询缓存总共可用的内存空间;单位字节,必须是1024的整数倍,最小值 40KB,低于此值有警报
query_cache_wlock_invalidate:如果某表被其它的会话锁定,是否仍然可以从查询缓存中返回结 果,默认值为OFF,表示可以在表被其它会话锁定的场景中继续从缓存返回数据;ON则表示不允 许
query_cache_type:是否开启缓存功能,取值为ON, OFF, DEMAND
MariaDB [(none)]> show variables like 'query%'
-> ;
+------------------------------+----------+
| Variable_name | Value |
+------------------------------+----------+
| query_alloc_block_size | 16384 |
| query_cache_limit | 1048576 |
| query_cache_min_res_unit | 4096 |
| query_cache_size | 33554432 |
| query_cache_strip_comments | OFF |
| query_cache_type | ON |
| query_cache_wlock_invalidate | OFF |
| query_prealloc_size | 24576 |
+------------------------------+----------+
8 rows in set (0.01 sec)
6. 查询缓存的内容变量
SHOW GLOBAL STATUS LIKE ‘Qcache%';
Qcache_free_blocks:处于空闲状态
Query Cache中内存 Block 数
Qcache_total_blocks:Query Cache 中总Block ,当Qcache_free_blocks相对此值较大时,可能 用内存碎片,执行FLUSH QUERY CACHE清理碎片
Qcache_free_memory:处于空闲状态的
Query Cache 内存总量
Qcache_hits:Query Cache 命中次数
Qcache_inserts:向 Query Cache 中插入新的 Query Cache 的次数,即没有命中的次数 Qcache_lowmem_prunes:记录因为内存不足而被移除出查询缓存的查询数
Qcache_not_cached:没有被 Cache 的 SQL 数,包括无法被 Cache 的 SQL 以及由于
query_cache_type 设置的不会被 Cache 的 SQL语句
Qcache_queries_in_cache:在 Query Cache 中的 SQL 数量
MariaDB [(none)]> show global status like 'qcache%';
+-------------------------+----------+
| Variable_name | Value |
+-------------------------+----------+
| Qcache_free_blocks | 2 |
| Qcache_free_memory | 33527544 |
| Qcache_hits | 4 |
| Qcache_inserts | 21 |
| Qcache_lowmem_prunes | 0 |
| Qcache_not_cached | 18 |
| Qcache_queries_in_cache | 7 |
| Qcache_total_blocks | 19 |
+-------------------------+----------+
8 rows in set (0.00 sec)
7. 命中率和内存使用率估算
查询缓存中内存块的最小分配单位query_cache_min_res_unit :
(query_cache_size - Qcache_free_memory) / Qcache_queries_in_cache
查询缓存命中率 :
Qcache_hits / ( Qcache_hits + Qcache_inserts ) * 100%
查询缓存内存使用率:
(query_cache_size – qcache_free_memory) / query_cache_size * 100%
二,索引管理
复合索引,不能跳过第一个索引,直接查第二个索引, 必须 第一索引当条件
1. 创建索引
CREATE [UNIQUE] INDEX index_name ON tbl_name (index_col_name[(length)],...);
ALTER TABLE tbl_name ADD INDEX index_name(index_col_name); help CREATE INDEX;
2. 删除索引
DROP INDEX index_name ON tbl_name;
ALTER TABLE tbl_name DROP INDEX index_name(index_col_name);
3. 查看索引
SHOW INDEXES FROM [db_name.]tbl_name;
4. 优化表空间
OPTIMIZE TABLE tb_name
5. 查看索引的使用
SET GLOBAL userstat=1;
SHOW INDEX_STATISTICS;
6. EXPLAIN 工具(重点,主要是用它来查询是否使用索引,以及索引工作的效果)
EXPLAIN SELECT clause
MariaDB [hellodb]> explain select * from students where stuid=22;
+------+-------------+----------+-------+---------------+---------+---------+-------+------+-------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+------+-------------+----------+-------+---------------+---------+---------+-------+------+-------+
| 1 | SIMPLE | students | const | PRIMARY | PRIMARY | 4 | const | 1 | |
+------+-------------+----------+-------+---------------+---------+---------+-------+------+-------+
1 row in set (0.00 sec)
MariaDB [hellodb]>
说明: type显示的是访问类型,是较为重要的一个指标,结果值从好到坏依次是:system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL ,一般来说,得保证查询至少达到range级别,最好能达到ref
三,加锁或解锁
1. 锁机制
a. 锁粒度: 表级锁 行级锁
b. 读锁:共享锁,只读不可写(包括当前事务) ,多个读互不阻塞
c. 写锁:独占锁,排它锁,写锁会阻塞其它事务(不包括当前事务)的读和它锁 实现
d. 存储引擎:自行实现其锁策略和锁粒度 服务器级:实现了锁,表级锁,用户可显式请求
e. 隐式锁:由存储引擎自动施加锁
f. 显式锁:用户手动请求
g. 锁策略:在锁粒度及数据安全性寻求的平衡机制
2. 显式使用锁-加锁
LOCK TABLES tbl_name [[AS] alias] lock_type [, tbl_name [[AS] alias] lock_type] ... lock_type: READ , WRITE
MariaDB [hellodb]> lock tables students write;
3. 显式使用锁-解锁
UNLOCK TABLES
MariaDB [hellodb]> unlock tables;
4. 关闭正在打开的表(清除查询缓存),通常在备份前加全局读锁
FLUSH TABLES [tb_name[,...]] [WITH READ LOCK]
5. 查询时加写或读锁
SELECT clause [FOR UPDATE | LOCK IN SHARE MODE]
四,事务管理
只对DML语言生效,默认情况下,一条sql语句,就是一条事务。
事务Transactions:一组原子性的SQL语句,或一个独立工作单元
事务日志:记录事务信息,实现undo,redo等故障恢复功能
ACID特性:
A:atomicity原子性;整个事务中的所有操作要么全部成功执行,要么全部失败后回滚
C:consistency一致性;数据库总是从一个一致性状态转换为另一个一致性状态
I:Isolation隔离性;一个事务所做出的操作在提交之前,是不能为其它事务所见;隔离有多种隔离级别,实现并发
D:durability持久性;一旦事务提交,其所做的修改会永久保存于数据库中
1. 启动事务:
BEGIN
BEGIN WORK
START TRANSACTION
2. 结束事务:
#提交 COMMIT
#回滚 ROLLBACK
3. 自动提交:
set autocommit={1|0}
默认为1,为0时设为非自动提交 建议:显式请求和提交事务,而不要使用“自动提交”功能
4. 事务支持保存点:
SAVEPOINT identifier
ROLLBACK [WORK] TO [SAVEPOINT] identifier
RELEASE SAVEPOINT identifier
5. 事务日志隔离级别
READ UNCOMMITTED 可读取到未提交数据,产生脏读
READ COMMITTED 可读取到提交数据,但未提交数据不可读,产生不可重复读,即可读取到多个 提交数据,导致每次读取数据不一致
REPEATABLE READ 可重复读,多次读取数据都一致,产生幻读,即读取过程中,即使有其它提交 的事务修改数据,仍只能读取到未修改前的旧数据。此为MySQL默认设置
SERIALIZABLE 可串行化,未提交的读事务阻塞修改事务,或者未提交的修改事务阻塞读事务。会 导致并发性能差
6. 指定事务隔离级别:
a. 服务器变量tx_isolation指定,默认为REPEATABLE-READ,可在GLOBAL和SESSION级进行设置
SET tx_isolation='
READ-UNCOMMITTED
READ-COMMITTED
REPEATABLE-READ
SERIALIZABLE
b. 服务器选项中指定 vim /etc/my.cnf [mysqld] transaction-isolation=SERIALIZABLE
centos 数据库管理2
最新推荐文章于 2023-05-19 21:37:26 发布