MySQL总结

MySQL的优点：简单、高效、可靠、性能好、数据插入和查询的效果好。本身没有事务支持，通过第三方数据引擎Innodb支持，Innodb支持4种事务隔离级别。

主要适用场景：

1：Web网站系统

2：数据仓库系统（支持主从复制）

3：日志记录系统（数据插入和查询的效果好）

4：嵌入式系统

MySQL的核心架构：第一层：SQL Layer

                                 第二层：存储引擎层，底层数据存取操作

核心模块：初始化模块、连接模块、线程模块、连接线程、解析器模块、表管理器、表修改模块、优化模块、用户模块、访问控制模块、核心API、网络交互模块、表维护模块（主要是.frm文件）、高速缓存模块、命令调度模块、存储引擎模块、日志记录模块、主负责服务器模块、从复制服务器模块、Client/Server协议API等

一般过程：初始化->连接管理->Client/Server协议API->线程模块->连接线程->用户认证->解析器模块(查询或命令)->缓存->优化->表的操作模块->访问控制->表管理->存储引擎，全程都有日志模块参与

Mysql中有用的参数：--prompt=name：表示进入后的格式

                                  --tee=name：将所有的的输入和输出都记录到文件，方便查阅。

一般用在监控上的命令：mysqladmin -u用户 -p密码 -h主机  ping

                                            mysqladmin -u用户 -p密码 -h主机  status
                                            mysqladmin -u用户 -p密码 -h主机  processlist

存储引擎总结：默认是MyISAM

MyISAM：每个表只有三个文件：.frm存表结构信息，.MYD存表的数据，.MYI存索引的数据，

                  支持三个索引：B-Tree索引、R-Tree索引、全文索引(存储结构也是B-Tree)

                  若其中一个表出错，仅影响该表，不会影响其他表。

InnoDB：支持事务安全、支持MVCC（多版本并发，通过快照和Undo回滚数据）、行锁（索引完成）、外键

                表数据和索引数据存放在一起，且用共享表空间还是独享表空间(.ibd)又用户决定，并且重做日志文件好

NDB Cluster：主要用于分布式集群环境，属于内存式存储引擎

Memory：数据存储在内存中的引擎，不会将任何数据放在硬盘上，只放一个表结构相关信息的.frm文件，支持Hash和B-tree索引，支持页级锁定

BDB：每个表有两个文件：.frm和.db，数据和索引数据放在.db上，支持事务安全，有重做日志（REDO），支持页级锁定

BLACKHOLE：用在数据迁移上面，没有任何数据，但是会在二进制文件（binlog）中记录所有的查询，将这些查询法制利用，实施到最终端

MySQL的三道安全防线：网络->主机防火墙之类->MySQL本身的用户认证和访问控制

MySQL备份：

1：利用mysqldump进行逻辑备份，如果能在同一时刻取出所有数据，则要保证存储引擎为InnoDB或者BDB，保证能在同一事务中；

                                                              如果保证数据库中的数据静止，则要锁表，保证只读不写。

mysqldump原理是在指定数据库上追个扫描表获取数据，生成Insert语句在写入备份文件。

2：生成特定格式的纯文本备份文件，相比Insert比存储空间小，结构清晰，缺点是一个备份文件不能存多个表的数据。

3：对数据库的物理对象进行备份为物理备份，向对物理数据、日志、配置文件进行备份。

4：冷备份：停掉数据库之后的备份

5：增量备份：先必有某个时间点的完整备份，在这个点之后到现在这个点之间的内容我增量备份。

MySQL的锁定机制：行级锁定（InonoDB/NDB）、页级锁定（Memory/BDB）、表级锁定（MyISAM）

行级锁定：锁定对象的颗粒度小，则发生锁定资源的争用率小，但是获取锁跟释放锁操作多，消耗就大，最容易发生死锁；

表级锁定：逻辑简单，成本小，对于颗粒度大的锁定机制，会将所有表锁定，获取锁跟释放锁操作快，避免死锁，缺点是资源争用率高；

页级锁定：颗粒度介于前两者之间，会发生死锁

锁机制优化MySQL：   缩短锁定时间，将大查询拆成小查询；

                                    建立合适的索引；

                                    尽量让表只存放必要信息；

                                    合理利用同读写优先级；

                                    对于InnoDB来说，在业务允许下，尽量使用低级别的事务隔离级别，降低成本

在InnoDB下避免死锁：1、尽可以一次性将所用的表进行锁定；

                                     2、尽可能按照相同的顺序来访问；

查询优化(自带优化模块部分)：充分利用Explain和Profiling

索引优化：MySQL的4类索引：B树、哈希索引、全文索引、R树索引，索引是完全独立于基础数据之外的一部分数据

建立索引的好处：提高检索效率、降低数据排序成本；

                 缺点是更新数据时，索引数据也要更新，增加了计算量和IO量和索引数据的存储空间增大；

MyISAM优化：索引缓存优化（key cache(cache block)），选择适当的参数；

                       使用多个key cache，不同的key cache放不同性质的数据（频繁使用且少修改、多修改且少使用、其他）；

                       表读取缓存优化，即每次读取数据文件所需的内存缓冲区；

                       利用OPTIMIZE命令定期优化整理；

InnoDB优化：合理设置InonoDB_buffer_pool_size

                      两次写缓存，在写到硬盘之前，先写一份到表空间的系统保留的存储空间；

                      自适应哈希索引，给缓冲池中的数据做索引，提高Buffer Pool中的数据的访问效率；

                      事务优化，选择合适的事务隔离级别，READ UNCOMMITTED是脏读；READ COMMITTED不会出现脏读，可能有不可重复读和幻读；

                                                                                REPEATABLE READ不会出现脏读和不可重复读，有可能有幻读；默认隔离级别

                                                                                SERIALIZABLE隔离级别最高级，脏读，不可重复读，幻读都没有；

                      合适的事务日志大小，若大，则IO性能也就高，但遇Crash恢复的时候时间长；

                      非聚集索引尽量小，提高访问效率，且逐渐尽量不要更新，减少主键变化的移动成本；

MySQL日志优化：日志记录打开越多，IO资源所用越大

性能优化：

1：充分利用CPU多核的处理能力---innodb_read_io_threads/innodb_write_io_threads

2：提高刷新脏页数量和合并插入数量，改善磁盘I/O处理能力---innodb_io_capacity

3：增加自适应刷新脏页功能---innodb_adpative_flushing

4：让InnoDB_Buffer_Pool缓冲池中的热数据存活更久

5：适当加快数据的恢复时间

6：使InnoDB支持多个BufferPool，像哈希表一样，不同的功能使用不同的BufferPool---innodb_buffer_pool_instances

7：通常情况下，哈希索引能提高查询性能，但在高并发情况下，应该关闭自适应哈希索引，避免堵塞进程

8：可以适当选择合适的内存分配程序

9：提高默认的Innodb线程并发数

影响性能的因素：

1：硬件条件

2：操作系统层面-----磁盘I/O，内存--BufferPool，CPU，网络I/O

3：SQL语句