Mysql知识总结（三）

micromax

　　MySQL锁
　　在mysql并发访问时，如果多个用户在访问同一张表时，例如用户A在执行修改表的操作，而同时用户B发起对该表查询操作，此时需要让用户B知道用户A正在施加写操作，这就意味着用户B此刻需要等待，如何等待，那就需要用到MVCC(并发访问控制)机制，而该机制是基于锁来完成；对于多个用户同时读来讲是没有问题的，但是同时写，势必会导致系统崩溃;所以锁并非是可有可无的，而是必须具备的，尤其是在并发访问的模型下.
　　执行操作时施加的锁模式
　　读锁：共享锁、非阻塞阻塞、防止我们在读取、查询所的时候,别人去执行写操作;
　　写锁：独占锁,排它锁;彼此间是阻塞
　　锁粒度：
　　表锁：TABLE  LOCK   锁定了整张表
　　行锁：ROW  LOCK      锁定了需要的行
　　粒度越小,开销越大,但并发性越好;
　　粒度越大,开销越小,但并发性越差;
　　锁的实现位置：
　　mysql锁:可以手动请求显示锁;
　　存储引擎锁:自动进行的(隐式锁);
　　显示锁(表级)
　　LOCK TABLES
　　UNLOCK TABLES
　　语法：
　　LOCK TABLES
tbl_name [[AS] alias] lock_type　　[, tbl_name [[AS] alias] lock_type] ...
lock_type:锁类型：　　READ [LOCAL]
　　| [LOW_PRIORITY] WRITE
例子1(施加读锁)
　　mysql> LOCK TABLES>　　Query OK, 0 rows affected (0.00 sec)

　　mysql> select * from>　　+---------+----------------+----------+

　　|>　　+---------+----------------+----------+
　　|       1 | Shaolin Pai    |       10 |
　　|       2 | Emei Pai       |        7 |
　　|       3 | QingCheng Pai  |       11 |
　　|       4 | Wudang Pai     |       12 |
　　|       5 | Riyue Shenjiao |       31 |
　　|       6 | Lianshan Pai   |       27 |
　　|       7 | Ming Jiao      |       27 |
　　|       8 | Xiaoyao Pai    |       15 |
　　+---------+----------------+----------+
　　8 rows in set (0.00 sec)

　　mysql> INSERT INTO>　　mysql> UNLOCK TABLES;  //解锁
　　Query OK, 0 rows affected (0.00 sec)

　　mysql> INSERT INTO>　　Query OK, 1 row affected (1 min 21.91 sec)
例子2(施加写锁)
　　mysql> LOCK TABLES>　　Query OK, 0 rows affected (0.00 sec)

　　mysql> SELECT * FROM>　　mysql> UNLOCK TABLES; //解锁
　　Query OK, 0 rows affected (0.00 sec)

　　mysql> INSERT INTO>　　Query OK, 1 row affected (1 min 21.91 sec)

　　mysql> SELECT * FROM>　　+---------+----------------+----------+
　　|>　　+---------+----------------+----------+
　　|       1 | Shaolin Pai    |       10 |
　　|       2 | Emei Pai       |        7 |
　　|       3 | QingCheng Pai  |       11 |
　　|       4 | Wudang Pai     |       12 |
　　|       5 | Riyue Shenjiao |       31 |
　　|       6 | Lianshan Pai   |       27 |
　　|       7 | Ming Jiao      |       27 |
　　|       8 | Xiaoyao Pai    |       15 |
　　|       9 | taoyuanji      |       34 |
　　+---------+----------------+----------+
　　9 rows in set (3 min 20.79 sec)
　　InnoDB存储引擎也支持另外一种显示锁(锁定挑选出的部分行，行级锁);
　　SELECT ......LOCK IN SHARE MODE;
　　SELECT ......FOR UPDATE;
　　事务:Transaction
　　事务就是一组原子性的查询语句,将多个查询当作一个独立单元.
　　ACID测试：能满足ACID测试就表示其支持事务,或兼容事务.
　　A:Atomicity       原子性
　　C:Consistency     一致性
　　I:Isolation       隔离性,一个事务的所有修改操作在提交之前对其他事务是不可见的
　　D:Durability      持久性,一旦事务得到提交,其所做的修改会永久有效
　　隔离级别：
　　READ UNCOMMITTED(读未提交)  脏读,不可重读;能够看到别人尚未提交的修改
　　READ COMMITTED(读提交)   不可重读,幻读
　　REPEATABLE READ(可重读)   幻读
　　SERIALIZABLE(可串行化)   强制事务的串行执行避免了幻读
　　事务回滚
　　mysql>HELP CONTENTS;
　　mysql>HELP Transcations
　　mysql>START TRANSACTION  开启事务
　　mysql>COMMIT 提交
　　mysql>ROLLBACK 回滚
　　设置回滚点
mysql>SAVEPOINT>回滚到指定的位置

　　mysql>ROLLBACK [WORK] TO [SAVEPOINT]>
　　RELEASE SAVEPOINT>　　如果没有显示启动事务,每个语句都会当作一个单独的事务,其执行完成后会被自动提交；
　　查看自动提交是否启用
　　mysql>SHOW GLOBAL VARIABLES LIKE 'autocommit';
　　or
　　mysql>SELECT @@ GLOBAL.autocommit;
　　设置自动提交为关闭,一旦关闭我们就需要手动就行提交
mysql>SET GLOBAL autocommit  = 0;　　查看Mysql的事务隔离级别
　　mysql>SHOW {GLOBAL|SESSION} VARIABLES LIKE 'tx_isolation';
　　or
　　mysql>SELECT @@{GLOBAL|SESSION}.tx_isolation;
　　设置隔离级别：
mysql>set {GLOBAL|SESSION} tx_isolation '各隔离级别名'　　建议：
　　对事物要求不特别严格的场景下,可以使用读提交
　　MVCC :多版本并发控制
　　每个事务启动时,InnoDB会为每个启动的事务提供一个当下时刻的快照;
　　为了实现此功能,INNODB会为每个表提供两个隐藏的字段,一个用于保存行的创建时间，一个用于保存行的失效时间
　　Mysql存储引擎：
　　MySQL存储引擎：存储引擎也通常称作"表类型"
　　查看本mysql服务器所支持的存储引擎：
mysql>SHOW ENGINES;　　查看表的存储引擎
mysql>SHOW TABLE STATUS [{FROM | IN} db_name]　　[LIKE 'pattern' | WHERE expr]
　　一张表中的状态详细信息
　　Name:  表名
　　Engine:  存储引擎
　　Version:  版本
　　Row_format:  行格式
　　{DEFAULT|DUNAMIC|FIXED|COMPRESSED|PREDUNDANT|COMPACT}
　　Rows:  表中的行数
　　Avg_row_length:  平均每行包含的字节数
　　Data_length:  表数据的总体大小(表中的行数*平均每行包含的字节数)
　　Max_data_length:  表的最大占用空间的容量
　　Index_length:  索引     的大小
　　Data_free:  对于MyISAM表,表示已分配，但尚未使用的空间;包含了以前被删除的行，这些空间可以用于以后的INSERT语句
　　Auto_increment:  下一个AUTO_INCREMENT的值
　　Create_time:  表的创建时间
　　Update_time:  表数据的最近一次的修改时间
　　Check_time:  使用CHECK TABLE或myisamchk最近一次检测表的时间
　　Collation:  排序规则
　　Checksum:  如果启用,则为表的checksum
　　Create_options:  创建表时指定使用的其他选项
　　Comment:  表的注释
　　各存储引擎的特性：
　　InnoDB:
　　事务：事务日志
　　外键：
　　MVCC:多版本并发控制
　　聚簇索引：
　　聚簇索引(对于INNODB来说)之外的其他索引,通常成为辅助索引
　　辅助索引-->聚簇索引-->数据
　　索引类型:B树索引, R树索引, hash索引, 全文索引
　　行级锁：间隙锁
　　支持辅助索引：
　　支持自适应hash索引
　　支持热备份
　　MyISAM:
　　全文索引：
　　支持表压缩,用于是实现数据仓库,能节约存储空间并提升性能
　　空间索引
　　表级锁
　　延迟更新索引
　　不支持事务, 外键, MVCC, 行级锁;崩溃后无法安全恢复数据;
　　适用场景：只读数据, 较小的表, 多读少写
　　ARCHIVE:
　　仅支持INSERT和SELECT,支持很好的压缩功能
　　适用于存储日志信息,或其他按照时间序列实现的数据采集类的应用;
　　不支持事务,不能很好的支持索引;
　　CSV:
将数据存储为CSV格式,不支持索引;仅适用于数据交换场景;　　BLACKHOLE:
没有存储机制,任何发往此引擎的数据都会被丢弃;其会记录二进制日志,因此,常用于多级复制架构中作中转服务器;　　MEMORY:
　　保存数据在内存中,内存表;常用于保存中间数据,如周期性的聚合数据等;也用于实现临时表
　　支持hash索引,使用表级锁,不支持BLOB和TEXT数据类型;
　　MRG_MYISAM:
是MYISAM的一个变种,能够将多个MyISAM表合并一个虚表;　　NDB:
是MySQL CLUSTER中专用的存储引擎　　第三方存储引擎：
　　OLTP类：

XtraDB:增强的InnoDB,由Percona提供;

　　编译安装mysql时下载XtraDB的源码替换存储引擎中的InnoDB的源码
　　PBXT:MariaDB自带此存储引擎
　　支持引擎级别的复制、外键约束、对SSD磁盘提供适当的支持;
　　支持事务、MVCC
　　ToKuDB:使用Fractal Trees索引,适用于存储大数据,拥有很高的压缩比;已经被引入MariaDB;
　　列式存储引擎：
　　Infobright:适用于海量数据存储场景,如PB级别,专为数据分析和数据仓库设计;
　　InfiniDB
　　MonetDB
　　LucidDB
　　开源社区存储引擎
　　Aria:前身Maria,增强版的MyISAM(支持崩溃后安全恢复,支持数据缓存)
　　Groona:全文索引引擎;完成全站搜索——>改进版,基于Mroonga二次开发
　　OQGraph:由Open Query研发,支持图结构的存储引擎
　　SphinxSE:为Sphinx全文搜索服务器提供了SQL接口
　　Spider:能将数据切分成不同分片,比较高效透明地实现了分片,并支持在分片上支持并行查询;
　　更多详细信息可以参考此表

　　如何选择一个适合于自己的存储引擎：
　　1.是否需要事务
　　2.备份的类型的支持
　　3.崩溃后的恢复
　　4.特有的特性
　　综合考虑即可！
　　Mysql用户管理：
　　用户账号：'username'@'hostname',password
　　用户账号管理：
　　mysql>CREATE USER
　　mysql>DROP USER
　　mysql>RENAME USER
　　mysql>SET PASSWORD
　　权限管理：
　　mysql>GRANT
　　mysql>REVOKE
　　创建用户：默认获取到的授权为USAGE
mysql>CREATE USER 'username'@'hostname' [IDENTIFIED BY [PASSWORD] 'password' ];　　查看用户能够使用的权限：
mysql>SHOW GRANTS FOR 'username'@'hostname'　　用户名改名：
mysql>RENAME old_'username'@'hostname' TO new_'username'@'hostname'　　设置密码：
mysql>SET PASSWORD FOR 'username'@'hostname'　　Mysql用户权限管理：
　　MySQL的权限类别：
　　管理类权限：
CREATE TEMPORARY TABLES 创建临时表　　CREATE USER             创建用户
　　FILE                    在服务器上读取或写入文件
　　SUPER                   不便归类的权限，杂项
　　SHOW DATABASES          列出数据库
　　RELOAD                  重置
　　SHUTDOWN                关闭服务器
　　REPLICATION SLAVE       复制从服务器的权限，从服务器到主服务器上复制数据需要SLAVE权限从服务器上的用户必须具有上面两个权限才能从主服务器上复制数据
　　REPLICATION CLIENT      复制客户端权限，如果一个用户需要到服务器上获取复制主机的相关信息，需要CLIENT权限
　　LOCK TABLES             显示施加表锁
　　PROCESS                 查看线程列表(msyql>SHOW PROCESSLIST)
　　库级别和表级别：
ALTER                   修改TABLE　　ALTER ROUTINE           修改存储历程(存储过程、存储函数)
　　CREATE                  创建表和库
　　CREATE ROUTINE          创建存储过程或存储函数
　　CREATE VIEW             创建视图
　　DROP                    删除库或表
　　EXECUTE                 执行存储过程或存储函数
　　GRNAT OPTION            把自己获得的权限转让
　　INDEX                   创建和删除索引
　　SHOW VIEW               查看一个视图是如何创建的
　　数据操作（表级别）：
SELECT   删除　　INSERT   插入
　　UPDATE   更新
　　DELETE   删除
　　字段级别：
SELECT(col1,...)　　UPDATE(col1,...)
　　INSERT(col1,...)
　　所有权限：
ALL [PRIVILEGES]　　GRANT命令用法
　　GRANT ALL ON [FUNCTION] *.*
　　GRANT priv_type [(column_list)]
　　[, priv_type [(column_list)]] ...
　　ON [TABLE|FUNCTION|PROCEDURE] priv_level
　　TO username@hostname [IDENTIFIED BY 'password'], [username@hostname [],...]
　　[REQUIRE SSL]
　　[WITH with_option ...]
　　priv_level:
　　*                        所有对象
　　| *.*                      所有库的所有对象
　　| db_name.*                指定库的所有对象
　　| db_name.tbl_name         指定库的制定表
　　| tbl_name                 指定表
　　| db_name.routine_name     指定库的存储历程
　　with_option:
　　GRANT OPTION                   把自己获得的权限转赠给其他用户
　　| MAX_QUERIES_PER_HOUR count     每小时所执行的最多查询请求次数
　　| MAX_UPDATES_PER_HOUR count     每小时所执行的最多更新次数
　　| MAX_CONNECTIONS_PER_HOUR count 每小时所能建立连接的次数
　　| MAX_USER_CONNECTIONS count     指定某个用户帐号最多能同时使用一个帐号连接的次数
　　收回授权：
REVOKE　　priv_type [(column_list)]
　　[, priv_type [(column_list)]] ...
　　ON [object_type] priv_level
　　FROM user [, user] ...
　　REVOKE ALL PRIVILEGES, GRANT OPTION
　　FROM user [, user] ...
　　跟用户授权相关的表：
　　db:库级别权限
　　host:主机级别权限,已废弃
　　tables_priv:列级别的权限
　　procs_priv:存储过程和存储函数相关的权限
　　proxies_priv:代理用户权限
　　MySQL查询缓存：
　　MySQL查询缓存的功能主要是将查询计划执行结果缓存下来,当多个用户执行相同操作时会加快查询操作;
　　两种缓存机制：
　　1.查询语句提交给存储引擎执行之前，这个查询计划一定是做了解析的,如果能将这个查询计划缓存下来，当同一个用户请求相应操作时就不用去解析了;
　　2.将查询后返回的数据给缓存下来,它能够保存查询返回的完整结果;被命中时，MySQL会立即返回结果，省去解析、优化和执行等阶段。

　　缓存机制大致流程
　　当一个查询请求进入服务器检查是否被命中,如果命中则直接返回;如果否则需要解析语句、优化、执行,执行后的结果最后检查能否需要缓存,如果需要缓存则就将就结果存入缓存当中;所以这样一来只要我们开启了缓存功能它都会去检查缓存,这样一来就多了一些开销、步骤;
　　如何检查缓存？
　　MySQL保存结果于缓存中：
　　把SELECT语句本身做hash计算，计算的结果作为key，查询结果作为value
　　什么样的语句不会被缓存？
　　查询语句中有一些不确定数据时，不会缓存：例如NOW(), CURRENT_TIME()；一般来说，如果查询中包含用户自定义函数、存储函数、用户变量、临时表、mysql库中系统表、或者任何包含权限的表，一般都不会缓存；
　　缓存会带来额外开销：
　　1、每个查询都得先检查是否命中；
　　2、查询结果要先缓存；
　　查询缓存完全是在内存中实现的,而内存空间是有限的,能够分配给查询缓存使用的空间也是有限的,如果空间占满了,那么会通过缓存策略将那些老的缓存给清除,但是这个过程又会增加开销,反复存入、释放、存入、释放,内存中就会产生许多碎片,就会变得很麻烦,于是选择一个合适的缓存策略是一个值得思考问题.
　　在mysql服务器上设定缓存策略：
　　查看当前mysql服务器的缓存变量
　　mysql> SHOW GLOBAL VARIABLES LIKE 'query_cache%';
　　+------------------------------+----------+
　　| Variable_name                | Value    |
　　+------------------------------+----------+
　　| query_cache_limit            | 1048576  |
　　| query_cache_min_res_unit     | 4096     |
　　| query_cache_size             | 16777216 |
　　| query_cache_type             | ON       |
　　| query_cache_wlock_invalidate | OFF      |
　　+------------------------------+----------+
　　query_cache_type: 查询缓存类型；是否开启缓存功能，开启方式有三种{ON|OFF|DEMAND}；
　　DEMAND：按需缓存;意味着SELECT语句明确使用 SQL_CACHE 选项时才会缓存；
　　query_cache_size: 总空间，单位为字节，大小必须是1024的整数倍。MySQL启动时，会一次分配并立即初始化这里指定大小的内存空间；这意味着，如果修改此大小，会清空缓存并重新初始化的.
　　query_cache_min_res_unit: 存储缓存的最小内存块；(query_cache_size-Qcache_free_memory)/Qcache_queries_in_cache能够获得一个理想的值.
　　query_cache_limit: 单个缓存对象的最大值，超出时则不预缓存；手动使用SQL_NO_CACHE可以人为地避免尝试缓存返回结果超出此参数限定值的语句.
　　query_cache_wlock_invalidate: 如果某个表被其它用户连接锁住了，是否仍然从缓存中返回结果。OFF表示返回.
　　如何判断命令率：
　　查看当前mysql服务器的缓存状态变量(不可设定,只需观测)
　　mysql> SHOW GLOBAL STATUS LIKE 'Qcache%';
　　+-------------------------+----------+
　　| Variable_name           | Value    |
　　+-------------------------+----------+
　　| Qcache_free_blocks      | 1        |
　　| Qcache_free_memory      | 16751304 |
　　| Qcache_hits             | 1        |
　　| Qcache_inserts          | 8        |
　　| Qcache_lowmem_prunes    | 0        |
　　| Qcache_not_cached       | 33       |
　　| Qcache_queries_in_cache | 5        |
　　| Qcache_total_blocks     | 14       |
　　+-------------------------+----------+
　　Qcache_free_blocks     空闲的块数
　　Qcache_free_memory     尚且空闲缓存空间大小
　　Qcache_hits：          缓存命中次数
　　Qcache_inserts：       向缓存空间中缓存数据的次数
　　Qcache_lowmem_prunes   因内存太少,修减内存的次数
　　Qcache_not_cached      没有缓存的
　　Qcache_queries_in_cache在缓存中缓存的查询的个数
　　Qcache_total_blocks    总块数
　　缓存空间只有被初始化、分配以后用来缓存数据的;
　　空闲空间表示尚未划分用来存数据格式的空间
　　总空间 - 剩余空间 = 已用空间
　　并非已用空间都存了缓存,里面有的表示已划分好格式了,随时等待缓存,但尚未进行缓存
　　如果内存中确实有碎片了,而内存还有足够的空间;这是我们需要进行碎片整理
　　碎片整理：FLUSH QUERY_CACHE
　　清空缓存：RESET QUERY_CACHE
　　计算命中率：
　　1.通过以下语句获取查询次数和命中次数这两个值
　　mysql> SHOW GLOBAL STATUS WHERE Variable_name='Qcache_hits' OR Variable_name='Com_select';
　　+---------------+-------+
　　| Variable_name | Value |
　　+---------------+-------+
　　| Com_select    | 57    |
　　| Qcache_hits   | 8     |
　　+---------------+-------+
　　2.通过下面这个公式计算
　　Qcache_hits/(Com_select +Qcache_hits)
　　
　　也应该参考另外一个指标：命中和写入的比率，即Qcache_hits/Qcache_inserts的值，此比值如果能大于3:1，则表明缓存也是有效的。能达到10:1，为比较理想的情况。
　　缓存优化使用思路：
　　1、批量写入而非多次单个写入；
　　2、缓存空间不宜过大，因为大量缓存同时失效时会导致服务器假死；
　　3、必要时，使用SQL_CACHE和SQL_N0_CACHE手动控制缓存；
　　4、对写密集型的应用场景来说，禁用缓存反而能提高性能；
　　MySQL日志：
　　查询日志：用来保存当前系统上所有跟查询相关信息的(不仅仅只包含SELECT)
　　慢查询日志：查询执行时长超过指定时长的查询;未必是执行语句自身原因,可能是应为被依赖的资源不被许可访问.定位系统上查询操作、执行过慢时的一个评估工具,所以有必要启用之.
　　错误日志：MySQL启动时,复制
　　二进制日志：记录了mysql数据库中引起数据库改变的所有操作或有潜在跟修改相关的操作,如果要从头记录的话拿到其他服务器上还原后与原服务器基本相同;mysql的复制功能依赖于此日志复制功能.
　　中继日志：slave server从master server的二进制文件中复制事件过来,并保存至本地的日志文件中,此日志文件就叫做中继日志
　　事务日志：暂存事务提交的数据
　　将随机I/O转换为顺序I/O,保证事务的ACID
　　日志文件组：至少应该有两个/保证事务回滚、提交、一致性
　　查询日志：(默认是关闭的)
　　log={ON|OFF}:是否记录所有语句的日志信息于一般查询日志文件中(general_log)
　　log_output={TABLE|FILE|NONE}   TABLE|FILE 可以同时出现,用逗号分隔
　　general_log={ON|OFF}
　　general_log_file:定义一般查询日志保存位置
　　SET GLOBAL log='ON'
　　SET GLOBAL general_log='ON' --->数据目录下：hostname.log
　　都需要启用
　　如果记录到TABLE中  SET GLOBAL log_output='TABLE'
　　会记录到数据库的mysql数据库general_log表中
　　慢查询日志：
　　慢查询时长：超过此时间,就判定为慢查询;
　　mysql> show global variables like 'long%';

　　+-----------------+-----------+
　　| Variable_name   | Value     |
　　+-----------------+-----------+
　　| long_query_time | 10.000000 |
　　+-----------------+-----------+
　　1 row in set (0.01 sec)

　　设定是否启用慢查询日志：
　　是否启用慢查询日志,它的输出位置也取决于log_output={TABLE|FILE|NONE}
　　slow_query_log={ON|OFF}
　　定义日志文件路径及名称：
　　slow_query_log_flile=www-slow.log

　　#过滤器
　　log_slow_filter=admin,filesort,filesort_on_disk,full_join,full_scan,query_cache,query_cache_miss,tmp_table,tmp_table_on_disk
　　#是否启用记录慢查日志

　　log_slow_queries=ON
　　#定义慢查速率
　　log_slow_rate_limit=1
　　#是否记录详细格式的日志信息
　　log_slow_verbosity
　　错误日志：
　　服务器启动和关闭过程中的信息;
　　服务器运行过程中的错误信息;
　　时间调度器运行一个事件时产生的信息;
　　在复制架构中的从服务器上启动从服务器线程时产生的信息;
　　相关参数：
　　log_error   /data/mydata/www.stu41.com.err   -->错误日志路径及名称

　　log_warnings  1   -->默认记录了mysql服务器的警告信息

　　二进制日志：
　　时间点恢复
　　实现mysql的复制