mysql

1.概述 334;
2.性能 因果关系;
3.数据类型  4种;
4.索引 结构 类型  三星评价;

5.优化 3个方面+5个流程+8个配置项；

高级：分区+缓存+字符集+存储过程

6.复制：
方法-方式-方案-线程-结构-原则 | 流程-问题（库-日志-复制-数据）-文件-配置；
7.扩展：原因 方式 演变 | 分片-规则 方式 工具 | 负载-方法 优化；
8.可用性：提高实效+缩短恢复+转移
9.应用+缓存
10.硬件：cpu 内存 硬盘（固态）


1.概述 334

mysql三层架构 客户端 服务器 存储引擎
并发控制的锁粒度 行锁 表锁 死锁
事务的四个特性 隔离性 一致性 原子性 持久性
四个隔离级别：脏读 幻读 不可重复读 串行化
mysiam和innodb对于索引和锁的应用
其他，事务日志 崩溃修复 mvcc高并发等

2.性能 因果关系

性能：等待时间（阻塞时间）和执行时间
间歇性问题：DNS查询超时，缓存失效，互斥锁争用，并发度超极值
单条查询还是服务器问题：
--如果服务器上所有程序突然变慢，又突然变好，慢查询不一定是原因，慢查询有可能是结果，也可能是原因
--使用SHOW GLOBAL STATUS，threads_connected和threads_running对服务器级别偶尔停顿敏感性高
--使用SHOW PROCESSLIST，显示很多状态的响应时间，如mysiam表级锁，导致服务器级别的线程堆积
--使用慢查询日志：如慢查询的第一条语句，应该分析一下是不是原因

3.数据类型  4种

mysql存储的长度、精度和物理空间不同
类 型：整数（TINYINT SMALLINT MEDIUMINT INT BIGINT，8-16-24-32-64）、实数（decimal float double）、字符串(char varchar enum)、时间(timestamp datetime)和标识符
缓存表、汇总表和计数表，比范式（联表导致I/O次数增多）或冗余表更能提高效率
Alter table效率低，alter column比modify column 更快，mysiam对于索引和数据可分开补充

4.索引 结构 类型  三星评价

B-Tree（索引列（节点页）+数据行（页子页）是两条） vs 聚族（节点页+（索引列+数据行）为两条（页节点）
聚族和非聚族的二级索引对比P165-P167
非聚族是行号+索引列
聚集 vs 非聚集

存储引擎的区别，存储顺序和存储内容不同：

聚簇索引的顺序就是数据的物理存储顺序，而对非聚簇索引的解释是:索引顺序与数据物理排列顺序无关。正式因为如此，所以一个表最多只能有一个聚簇索引。索引是通过二叉树的数据结构来描述的，我们可以这么理解聚簇索引：索引的叶节点就是数据节点。而非聚簇索引的叶节点仍然是索引节点，只不过有一个指针指向对应的数据块。


内容不同：

--大小

还 是通过一道数学题来看看它们的区别吧：假设有一8000条记录的表，表中每条记录在磁盘上占用1000字节，如果在一个10字节长的字段上建立非聚簇索引 主键，需要二叉树节点16000个（这16000个节点中有8000个叶节点，每个页节点都指向一个数据记录），这样数据将占用8000条×1000字节 /8K字节=1000个页面；索引将占用16000个节点×10字节/8K字节=20个页面，共计1020个页面。

同样一张表，如果我们在对应字段上建立聚簇索引主键，由于聚簇索引的页节点就是数据节点，所以索引节点仅有8000个，占用10个页面，数据仍然占有1000个页面。

--速度

聚簇索引主键的插入速度要比非聚簇索引主键的插入速度慢很多，非聚集索引只检索索引页，但聚集索引还要检索庞大的数据页。向表中插入新数据行，非聚集索引直接在末尾加入，聚集索引如果数据页满了就拆分数据页，调整索引指针，如果设为自增就不拆分数据页而是添加数据页

存储内容和顺序不同：

聚集索和非聚集索引都会访问索引页和数据页，但前者是直接访问，后者要通过索引页映射到数据页面，所以非聚簇索引也好还是聚簇索引也好，都适合排序，聚簇索引仅仅比非聚簇索引快一点。

聚族索是索引列+数据页，因此需要二次查询，第一次是找到主键，第二次找行号
B-TREE适合范围查找，索引适用于全键值、键值范围或键前缀查找，键前缀使用最左前缀查找
匹配类型：全值 最左前缀 列前缀 范围值 精确匹配某一列并范围匹配另外一列 只访问索引的查询，同时，支持order by排序
索引顺序：不能跳过索引 范围查询后面的无法使用索引 最左列
其他：哈希（innodb自适应哈希） 全文(sphinx) 空间（postgre）
大中小表索引适用情况
三星评价：索引过滤，覆盖索引抓取后过滤 抓取后过滤
索引优化：
--独立的索引列，不要在上面做加减法操作
--前缀索引，选择前缀长度除以总行数，如果值在0.310为比较合适的，也就是说出现的频率比较高，选择性高，前缀索引不能用order by和 group by,后缀索引也有用于，如查找邮箱，由于mysql不支持反向索引，但可以将字符串翻转，并建立前缀索引
--多列索引，mysql有索引合并策略，就是说创建多个单列索引的时候，它会自动优化索引，但这会消耗大量资源，不建议使用
--索引顺序，选择性最高的放在最前面，也就是说数据行数小但区分度高，也可以计算选择基数 P160，如果行数很多，那么索引失去了存在的必要，同时避免随机I/O和排序，考虑where子句中的排序、分组和范围条件等，
聚族索引，减少磁盘I/O，但是会产生页面碎片，Innodb最好指定一个自增AUTO INCREMENT，否则会导致页分裂和碎片
覆盖索引：如果查询中只有索引包含的列，那么就可以使用索引直接在引擎读取数据行，引擎不用再返回数据行给mysql服务器了，
select a from table_name where b (b,a)
冗余索引 重复索引 压缩索引
--索引扫描排序,就是说让索引既满足排序又满足查询，这是最好的。只有当索引的列顺序和order by子句顺完全一致，且所有列的排序方向一致时，才行
索引优点在于，减少扫描数据量，避免排序和临时表的生成，将随机I/O转换成顺序I/O
索引设定，频率高的设为最左前缀，将范围搜索字段放在最后，避免多个范围条件查询，使用延迟关联，通过索引返回主键，根据主键获取需要的行，减少MySQL扫描那些需要丢弃的行数。
维 护索引和表，check table查看是否损坏repair修复表，analyze table重新生成索引统计信息，optimize table减少索引和数据的碎片，或直接用ALTER TABLE <table> ENGINE=<engine>来减少碎片

5.优化 3个方面+5个流程+8个变量

优化三个方面，查询、索引和库表结构
慢查询，响应时间（waite+do） 扫描行数（explain）和返回行数
重写查询，大查询会锁住数据，占满事务日志、消耗资源并阻塞很多查询。分解关联查询可以让单表缓存效率更高，减少锁的竞争，减少冗余记录查询
mysql执行查询的过程是最主要的，先查看缓存，如果没有就进行SQL解析、预处理、再由优化器生成对应的执行计划，mysql根据优化器生成的执行计划，调用存储引擎的API来执行查询，并将结果返回给客户端
1、单向查询
2、是否缓存
3、 查询优化，包裹解析器和预处理，就是说找出语法或相关错误，如果没问题，进入优化阶段，mysql优化是根据统计信息得来的，统计信息依赖于存储引擎，包 括索引的页面个数、索引的基数、索引和数据行的长度、索引分布情况等，但由于统计信息并不准确或者说算法问题导致选择的方案并不是最快的，优化类型有重定 义关联表顺序、外连接转为内连接、等价变换、转换为常数表达式、覆盖索引扫描、提前终止查询等（In在mysql中不是or，而是O(log n)复杂度操作）
mysql的关联查询是采用嵌套循环关联操 作，如果有子查询那么先执行并存在临时表中，如果是右连接就转换成左连接，在执行方面对于多表联查，mysql采用的是左侧深度优先，优化的时候不是按照 连表的顺序，而是通过返回最小行或者说扫描最小行的算法来确定第一个表是谁，而后进行连表操作的
4.执行查询，服务器和存储引擎API调用
5.返回给客户端，开始生成第一条结果就返回，服务端避免存储更多数据，
mysql 的一些提示(优先级 返回行数 锁表 联表 索引 常用优化)和常用语法（group by order by distinct union join..using..on count limit in ）的优化以及用户自定义变量set @num :=10;
select ... where col<=@num;

【终结小结】

连接-- max_connections + connection
索引-- Key_readskey + buffer_size
临时-- Created_tmp_disk_tables + tmp_table_size
查询-- 碎片率-bock/利用率-cache/命中率-hit+ query_cache
扫描-- Handler_read_rnd_nextread + read_buffer_size
打开-- Open_tables + table_cache
缓冲-- Threads_created + thread_cache_size
表--innodb
+++++++++++++++++++++++++++++++++++++++++++++++++++

innodb_buffer_pool_size = 512M

# 如果将此参数设置为1，将在每次提交事务后将日志写入磁盘。为提供性能，可以设置为0或2，但要承担在发生故障时丢失数据的风险。设置为0表示事务日志写 入日志文件，而日志文件每秒刷新到磁盘一次,提交后不做操作。设置为2表示事务日志将在提交时写入日志，但日志文件每次刷新到磁盘一次。

innodb_flush_log_at_trx_commit = 2 //提交写入日志

innodb_log_buffer_size = 16M //内存

innodb_log_file_size = 128M //总大小

innodb_log_files_in_group = 3 //覆盖写入个数

innodb_file_per_table = 0 //单独表空间
+++++++++++++++++++++++++++++++++++++++++++++++++++
--show status/innodb processlist variables

innodb_flush_log_at_trx_commit，区分刷写，


通过提交区分
0

为0,log buffer每秒就会被刷写日志文件到磁盘，提交事务的时候不做任何操作。mysql挂起会丢数据
1

为1，每次提交事务的时候，都会将log buffer刷写到日志。不会丢数据
2

为2,每次提交事务都会写日志，但并不会执行刷的操作。每秒定时会刷到日志文件。系统挂掉会丢数据

MySQL高级特性

分区表是由存储引擎来实现，分区的过程是锁住底层表，然后过滤分区，最后执行增删改查操作。分区表主要是依范围进行分区，也有哈希分区、列表分区和键值分区，分区将数据拆分成小片，避免了互斥锁的竞争。分区表达式可以是列，也可以是包含列的表达式。分区表在使用的时候，尤其是大数据量的时候，B-Tree索引不起作用，分区列和索引不匹配会导致无法过滤分区。用explain查看是否使用了分区

字符集是从二进制到某类字符符号的映射，校对是字符集的排序规则。字符集的默认设置从库到表再到列，如果下面没设置就用上面默认的，通常先use database，然后 set names utf-8，最后才是导入数据，因为mysql加载数据的时候，用相同的字符集来处理所有数据，不管表中的列用了哪种字符集，mysql排序的时候，只有排序查询要求的字符集与服务器数据的字符集相同时候才能用索引，不然都是文件排序。当两个不同字符集表关联，会转换一个表的字符集，导致索引失效。utf-8是一个多字节编码，mysql会给它尽可能分配更多空间，可以用length char_length计算长度，而且会影响索引长度，通常做substring和计算length的时候会用到字符集


缓存查询执行，也能缓存查询结果，也就是查询缓存,缓存失效的原因有缓存碎片、内存不足和数据增删改。缓存读应该大于写，命中率不应效率30%
缓存判断:命中率低，看是否被缓存了，如果被缓存了，那么看query_cache_limit单个查询缓存是否足够大，如果压根没被缓存，那么看是不是缓存失效，是的话有三种可能的原因，如果碎片多，缩小 query_cache_min_res_unit或者使用命令FLUSH QUERY CACHE整理，如果内存不足就增大query_cache_size,如果增删改多那么就不适合缓存。如果缓存没有失效，那么判断查询是否预热，没有的话等待执行，如果预热过那就是查询没有被处理过。
缓存优化常用的方法有，用小表，小的缓存空间，批量增删改，写密集型或互斥资源关闭缓存，也可以通过SQL_CACHE和SQL_NO_CACHE控制是否需要缓存。上面讲的是服务端缓存，其实客户端缓存也可以分担mysql服务器的压力（第14章）


内部存储代码，存储过程和存储函数都可以接收参数然后返回值，触发器和事件不行。存储过程无须网络通信开销，解析开销和优化器开销，效率较高。触发器是在增删改的时候执行一些特定的操作，指定SQL语句执行前触发还是执行后触发，本身没返回值，mysql的触发是基于行的触发，效率较低。Innodb的事务特性可以保证触发的一致性。事件是数据的定时任务，如定期维护、重建缓存、构建汇总表和模拟物化视图（将视图结果数据存放在一个可以查看的表中，并定期从原始表中同步数据到这个表中），事件调度本身是一个单线程，但事件本身可以并行执行。

小结:

分区表是块粒度的，视图使用临时表算法效率低，外键最好通过应用程序实现，存储过程可以节省网络开销，绑定变量对于大量重复类型的查询语句有效，字符集转换会造成资源消耗，mysiam支持全文索引，一般用sphinx替代，查询缓存会立即返回结果，无须在数据库中重新执行，如果希望有更高的效率，还是用memcache、redis和mongodb等数据库系统

 

复制+扩展+可用+应用
方法-方式-方案-线程-结构-原则
流程-问题（库-日志-复制-数据）-文件-配置

方法：行-便于回滚 语句-便于找问题
方式：半同步，主库的二进制日志至少同步到一个备库，如果备库长时间没反应，就转成异步
方案：选择性复制 分离功能 数据归档 备库只读 日志服务器
线程：二进制(同步-偏移量+快照 安全-sync_binlog=1) 中继 写入执行
结构：一主多从（投入>产出），主-主，环库，树形
原则：不能一备多主，备ID唯一，能升级为主



流程：
主库锁表 flush tables with readlock
备库只读 read only
主库二进制日志的位置show-master-status
备库延迟 heartbeat record
在备库上执行SELECT MASTER POST_WAIT()阻塞
主备一致 pt-table-checksum
主备切换 change master to,计划内外--选备库

问题：日志+主备库+复制+数据
主库关闭--sync_binlog
备库关闭通过pt-slave-restart重启
重启备库：执行STOP SLAVE,检查变量，关闭备库。
如果innodb事务日志和二进制日志不同步,而中继日志也没有，没办法
二进制日志损坏，通过mysqlbinlog获取偏移量
中继日志：CHANGE_MASTER_TO丢弃并重新获取损坏事件
复制延迟：读写分离 写备库 trx_commit=2 单独归档
复制中断：不要在主库创建备库没有的表
数据不一致:innodb_lock_unsafe_for_binlog=1会导致数据不一致，提交和执行顺序不一致
配置：
innodb_flush_logs_at_trx_commit=2
innodb_support_xa=1
innodb_safe_binlog
log_bin=绝对路径/mysql-bin
==================================
relay_log=绝对路径/relay-bin
skip_slave_start//防止备库崩溃修复后自动同步
read_only//权限控制

文件：
mysql-bin.index（识别二进制文件）
mysql-relay-bin-index(中继日志的索引文件)
master.info（备库连接到主库所需要的信息）
relay-log.info(备库查找从主库开始的位置)
expire_logs_days(日志过期清理)
log_slave_updates备库能传数据


===============================================
扩展 原因 方式 演变
分片 规则 方式 工具
负载 方法 优化

原因：系统内的串行化和交互是扩展的瓶颈
方式，垂直扩展（向上扩展）、水平扩展（向外扩展）和向内扩展（及时数据归档、解归档）。
演变：是单个服务器->向外扩展拥有读备库->数据分片和按需分区


规则：一般是每个分片对应一个数据库，把分片号写到数据库名和表名中，
方式：动态,分片转移到另一个分片，给每个分片两台备库.活跃数据创建两个分片，活跃节点放新数据，备库旧数据--静态：取模
分片工具： Shared-Query

方法：一主多备的均衡，通过功能分区、过滤数据分区（分离读缓存相当于加和，随机读只是一台的）、写操作转移备库、缩小备库和主库的复制延迟和同步写操作 （mysql5.5的半同步能让需要的备库接收数据）（MASTER_POST_WAIT函数阻塞知道备库赶上主库，还有pt-heart检查延迟）等来 达成
优化：读写分离导致不一致-->mysql负载均衡--lvs：读写分离(监控复制延迟,脏数据归档，会话看用户操作与延迟)，多个节点均衡，节点内部主从均衡,长连接和连接池需要mysql上加载http去探测状态，来均衡
===============================================
高可用性就是更少的宕机时间：提高实效+缩短恢复+转移

--提高时效时间（应该把InnoDB当做默认存储引擎，禁用查询缓存、复制完整性-同步复制、备库只读、mysql审查，归档，服务器配置、参数和性能监控）主从+功能+数据

--缩短恢复时间，,冗余+故障转移（a出问题->替换成b->修复a->替换成a）,故障转移主要是修复，比负载均衡多了替换的功能,用双主结构

如何转移：
复制不一致性-->负载均衡实现，
复制消耗大-->分区
故障转移,要快就半同步和异步，要安全就同步
===============================================
资源消耗：应用+缓存
mysql和apache连接数,让连接排队而不是扩展，apache keep-alive gzip,缓存用varnish squid dns缓存数据
缓存：
--维度：缓存找到粒度和过期，缓存内容和位置，缓存分主动缓存-squid，被动缓存-memcached（节点崩溃和增加节点,因此缓存分布方式很重要）
--开销：没缓存测量生成数据开销，有缓存测量检查+缓存不命中*生成数据+缓存命中*缓存提供数据开销，
--失效：写时 读时
--策略：缓存多个单元小块数据，预生成内容，防止缓存失效，评估禁用缓存或丢失缓存时的性能

复制：方法-线程-结构-原则-类型-方案 | 流程-问题（库-日志-复制-数据）-文件-配置
扩展： 瓶颈 方式（分片 规则 方式 工具） 过程 负载均衡 方法和优化
高可用性：提高时效时间 缩短恢复时间 转移资源消耗：应用层+缓存


CPU


最常见的瓶颈是CPU、内存和I/O资源，mysql倾向更快的CPU而不是更多，比如复制是单线程的，不会利用多核来执行；增加内存是解决I/O的方法，多次写入、一次刷新以及I/O合并把随机I/O转化成连续I/O，合理的内存和硬盘比例要看工作集大小。

固态存储优化mysql，增加InnoDBd I/O容量--innodb_io-capacity ，增加InnoDB日志文件，禁用预读，配置刷新算法--innodb_flush_neighbor_pages=0，禁用双写缓冲，限制插入缓冲，日志文件单独存放，备库如果延迟大也可以用

mysql文件存放，数据和日志分开存放，（数据和索引 事务日志 二进制日志  常规日志  临时文件），网络配置开启skip_name_resolve

选择操作系统更多要考虑故障恢复时间，状态监控命令有vmstat iostat等，如通过vmstat能区分CPU密集-us列  I/O密集--wa列  内存交换密集--swpd列 空闲机器--id列，同时配合iostat显示硬盘使用率--%util列，本质上，写入可以异步，但读取是同步的，系统能满足大量写入需求，却无法满足读请求

原因转移：由于内存不足导致I/O频繁，所以要查找主要原因

 

 

备份：分类 二进制日志（分类 修复 删除 读取（整个和时间点--快照）)  工具 脚本

分类：物理，直接拷贝数据库文件；逻辑，使用mysqldump备份数据库（完全+增量+差异备份，适合中型数据库备份）；实时，使用主从复制机制(replication)


二进制
分类：备份内容包括复制配置（innodb日志、二进制日志、中继日志和日志索引文件以及.info文件）、服务器配置和操作系统文件，

快照：数据一致和文件一致，通过LVM获取日志和数据文件保持一致性；LVM采用写时复制原理，它只包含差异数据，方便回滚。

整体恢复：从备份恢复数据流程，最主要的是载入备份文件并检查和重放二进制日志，innodb恢复时要恢复日志和表空间，还有逻辑备份，还原后给mysql相应权限，对每个数据库运行SHOW TABLE STATUS来检测错误日志。还原逻辑备份方法有加载SQL文件和加载符号分隔文件。

时间点的恢复：停掉mysql然后从备份中恢复数据，更改配置mysql连接，启动服务器后用grep检查二进制mysqlbinlog，START SLAVE UNTIL重放至问题语句，SET GLOBAL SQL_SLAVE_SKIP_CONUTER=1跳过问题语句，START SLAVE备库执行完中继日志。备库读取日志服务器的二进制日志，使其成为日志服务器备库，读取坐标，重放日志，日志用mysqlbinlog查看，expire_log_days清除,PURGE MASTER LOGS BEFORE CURRENT_DATE - INTERVAL N DAY执行。

修复：Innodb二级索引--optimize table，聚族索引--innodb-force_recovery,损坏系统结构--事务日志+表空间日志+数据字典。二级索引不丢失数据，其他会丢失，损坏的文件里提取数据，用SELECT INTO OUTFILE导出，innodb_force_recovery控制

备份：mysqldump--opt会优化，--datebases + --lock-all-tables保持数据一致性，innodb备份，增加--single-transaction，使用innodb 的MVCC特性在单个时间点创建一个一致的备份，不用LOCK TABLES，--master-data,备份就包括备份服务器的二进制日志文件的位置，获取日志位置要用到FLUSH TALBES WITH READ LOCK

脚本：安全检测-连接mysql-停止和启动mysql-获取数据库和数据表的列表--对表加锁、刷新和解锁--刷新二进制日志-获取二进制日志的位置-导出数据-复制数据等P632

小结：备份要可恢复，记录恢复点目标和恢复时间目标；备库不是备份；备份可以通过DROP TABLE 测试，用pt-table-checnsum验证复制完整性；备份方式有两种，文件系统物理备份和XtraBackup热备份，逻辑备份和二进制备份都要有，innodb对于自身修复能力强，不用特别的修复备份工具。常用的备份工具是MySQL Enterprise Backup，使用FLUSH TABLES WITH READ LOCK操作不适合热备份