centos 数据库管理2

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