Mysql性能调优与架构设计--笔记

登入mysql主机：

mysql -uadm -pbbnd_adm --prompt="\\u@\\h\\Y:\\m:\\r:\\m:\\s>" --tee=operat.log


--tee帮助拷贝屏幕

--prompt标记提示目前所处那个mysql,展示账号，ip等


查看mysql状态的工具
mysqladmin -uadm -pbbnd_adm -h localhost processlist  



Mysql的性能调优：

首要关注的是商业需求对于Mysql的性能的影响。 经常会有一些庞大的低优先级的数据，完全可以接受延迟， 或者稍微不精确的数据， 确在被用来实时计算，耗费巨量的系统资源。（比如论坛帖子的总数） 另外，还有一些无用的功能，没有及时移除，导致不必要的资源消耗。


有些数据不应该放在数据库里面，比如二级制数据，媒体文件， 还有就是流水型数据。 （比如按天算的统计数据，数据库只保存3天，势必造成多次的插入修改删除）；
流水型的数据，如果采用第三方的队列软件来实现，那么效率会有成倍的增加。


合理利用cache，对于系统的规则配置数据， 用户信息等不经常改变的数据，应该设置cache. 目前其实可以采用mysql+memocache的方式，增加二级缓存。相当于缓存尽可能多的数据。


最能优化的是数据实现层的优化， 优秀的query能够达到指数级别的性能提升。


数据库最大的性能瓶颈就在于磁盘的操作，也就是IO。 这个操作体现在扫描了数据的记录条数。每次扫描到一个，都会去磁盘取这条数据


找出频繁执行的语句，优先照顾语句，哪怕提升一点点，或者哪怕牺牲一点其他的性能， 对系统的提升也是非常有益的。


理解执行计划：

每个query被mysql分解以后，都有一个执行计划，执行计划的好坏直接决定查询的性能。

explain select * from xx，  在前面加上explain命令， 就能显示接下来的执行计划。

执行计划里面，有几个字段非常重要，如果出现了，就代表可能需要优化：

type:   告诉我们对于表的访问方式：  如果出现 all 全表扫描，然后行数很多的话，那么这里就是个很值得优化的地方。 其他的值基本上都是进行了索引的查询，性能上提高空间不大。

key:  如果这里面没有值，是null的话，说明缺少索引，行数稍微多一点，一定会遇到问题。 所以也很值得优化

rows:  估算统计出来的记录条数， 如果记录数很多的话，也很需要注意

extra:   其他信息： using filesort， 说明没有用到索引排序， 可以优化。  using where 说明有些结果没有用到索引    using temporary： Group by , order by , distinct等就会需要这个临时的缓存。




采用profiling找出瓶颈:   （只在5.0.37里面才有这个超级秘密武器）

当执行计划看上去没有什么特别的不对的时候，就要采用profiling分析工具，这个工具紧紧在5.0以上的版本中才存在。

profiling非常简单， 打印出这个sql在执行过程中的时间分布参数， 然后找出时间消耗比重很大的地方，这就是瓶颈所在。

select @@profiling;  先看看服务器的这个参数是否打开了， 0=no,  1=yes

set profiling=1;  打开这个参数

然后执行我们的SQL。

完毕后，执行show profiles; 看看系统中记录了哪些profiling， 每个都query都有1个序号，用来打开其详细信息

最后打开profiles的参数， show profile block ui,cpu for query 1      show profile all for query 1



join太多的表，有时候也可能造成资源的锁定，一些情况下，如果并发很多的时候，可以拆开join， 分小表查询，然后在程序中拼接。这样做的好处是减少锁表而导致的等待的时间。 join的表多，整个语句等待的时间就越长， 特别是在upate的情况下，千万不能join，或者子查询结果集，所表会非常严重。
，

通常情况下，如果tmp table的操作经常会很多，如果程序不需要distinct, order by, group by 的话，就尽量不用。能节省很多时间， Copying to tmp table 这项是吃性能的祖宗。


如果是排序导致了性能的下降，那么提高的办法在于掌握排序的时机， 让目标数据在查询过程中的小结果集，或者子集里面就完成排序，然后在和其他的表关联。

这个排序如果不是在索引上，其性能会下降比较明显



数据库Schema设计对系统的影响也比较大：


不同的应用优化方式不一样，手段有如下：

分表： 一个表的内容，按照行数，分到不同的表。  或者一个表的内容，按照字段的不同，分到不同的表。
适当的冗余：  能够减少join带来的压力
另外最强的方法，就是建立适当的索引， 






建立索引有好些规则：

分析所有sql语句的执行频率, 查看其查询条件的组合。

如果某一组合，或者这个组合的子集，出现非常频繁，那么一定要加索引。给子集添加索引是比较划算的，即不添加太重的负担，又可以提高效率。

索引一般以联合方式来建，如果有些联合中的字段，可能会单独出现在查询中，那么这个字段应该在所建索引的前面。特别是唯一性太差的，要和其他字段一起。

数据字典，如果不足百行，建不建索引，关系都不大


建立索引，应该为where句中，过滤性最好的字段建立索引，而不是全部。


联合索引比单个索引要优越：  如果几个条件都采用单索引，那么查询中，所有索引都涉及了，然后还要进行数据merge.


尽量让一个索引，能够被很多查询使用到。


可以适当采用前缀索引，减小索引的存储规格，提高访问效率。 用于前面几个字段不重复的内容 比如 xxx_bigband, 所有内容都以bigband结尾，那么就要用前面3个字为索引。


索引长度不能太长，MyISAM 1000的限制


在索引键上使用函数的时候，该索引不起作用


使用<>  或者!=的时候，索引无效



如果有primary index主键索引的话，主键索引在Innodb上访问数据，其效率是最高的。其他叫做secondary index.   Secondary-index的指向都是指向primary的，属于间接使用primary。 所以，直接用主键索引，效率会很高。


如果有的查询，只查单值，不查范围，那么采用hash-index,  其余的，用B-tree会比较好。 一般不用fulltext-index


B-Tree索引  balance tree， 到每个节点的时间，路径是一样的。
Hash索引   速度最快，但是只能=， in和不等于，   不能范围查，不能排序， 不能利用部分键查询，而且不能避免IO查表，  数据重复较多的时候性能急剧下降。
Full-text索引不建议使用，采用第三方工具来实现。
R-Tree:  空间数据索引，主要用于GIS等







Join表的优化：没有Hash Join，也没有 Sort Merge Join，而是nested loop join


减少Nested Loop的循环次数，最有效地方式，就是减少内层循环的次数， 这样就能减少总次数。 采用小结果集驱动大结果集。注意不是小表驱动大表，而是带上条件之后的结果
集。

join条件的字段千万要索引起来



设置足够的join buffer.  join_buffer_size   当join的条件没有办法使用到索引的时候，如Type: ALL，index，rang 或者是 index_merge. 给予足够的空间来放buffer.



调优提升的经验显示：  业务调优55%,  query调优35%，  数据库自身调优： 15% 







Mysql的锁定机制优化：

Mysql中有行锁，表锁和页锁。  行锁效率最高，但是容易发生死锁， 表锁效率最低，但是没有死锁。  页锁处于之间，也会有死锁。

Mysql中有读锁定，采用两个队列来实现，逻辑如下：

如果需要读的资源当前没有被写锁锁定，或者没有更高优先级的写锁在等待，那么这个读锁定的请求就进入到 current read-lock queue, 如果任何一个没有满足，那么就进入pending read-lock queue中.


写锁获取资源前，先看资源上是否有写锁，如果没有，再看前面队列中是否有写锁，如果还没有，再看是否有读锁， 如果还没有，则进入current writelock queue. 反之就进入pending write-lock queue等待。



InnoDB的锁定机制：  行锁定：（共享锁和排他锁） 表锁定（意向锁）， 他的实现是通过在第一个索引键值之前和最后一个索引键值之间之后添加标记来实现。 也称为“间隙锁”。

它是一个范围锁定，query的时候，查询一个范围， 锁定一定范围的键值，即使某些不存在的键值，如果在这个范围内，也被锁定。这样就无法在这个范围内增加数据。对性能的危害很大。其实这也不是真正意义上的行锁。


如果没有对索引进行锁定的话，那么有innodb会切换到表级别锁定上面。
query的结果集可能是锁定范围的子集，这个锁定影响到了其他不相干的行。
索引键值被锁定的时候，该键值不能被访问，即使通过索引访问其他的列也不行。



mysql有4个事物隔离级别：  读取还未提交的数据， 读取已经提交的数据，同个事物中两次读表内容一定相同， 串行序列操作不能并行。 一般采用读取提交的数据级别。


Mysql的死锁解决有两种方式：  1. 超时解决， 2. 按照不同事务的大小，大事务提交，小事务回滚。



行锁：  锁定其中的一条记录的时候，这条记录不释放的话，其他session是不可以修改这条记录的，如果要修改，那么就会进入等待队列中。

行锁升级为表锁：  采用update锁定一条记录的时候，如果条件中没有使用到索引，那么锁定的就不是这条记录，而是整个表。

间隙锁： 当update利用了范围索引，其中的索引键值，即使不是结果集的一部分，也会被锁住。




使用innodb的时候，尤其要注意update的查询要建立在索引上，不然升级为表锁的话，效率急剧下降。

update的时候，减少范围查询，尽量做到精确。

控制事务的大小，尽量小， 减少锁定的时间。

业务允许的条件下，使用较低级别的事务隔离。




减少死锁：

死锁区域，尽量按一样的顺序来锁定行

同一个事务中，一次锁定所有资源，不要分批锁定

逼不得已的情况下，可以升级到表锁定。



查看所表，所行的记录： 

show status like 'table%'   查看所表的次数时间
show status like 'innodb_row_lock%'  查看所行的次数时间
show status like '%slow%'  查看执行很慢的query
show status like '%connection%'   查看是否connection资源瓶颈
show status like '%key_read%' 查看读取命中内存， 如果大于0.1%
show status like '%wait%'  查1.物理备份和还原

2.增量备份还原

3.二进制Log来实现还原以及SQL的备份还原

4.采用Replication来进行备份辑备份：

通过mysqldump生成可执行的insert语句， 另外就是通过mysqldump或者 select ... to outfile from ...来实现，生成带分隔符的数据文件

mysqldump  --singe-transaction  可以保持备份过程中的事务一致，数据一直
   --lock-tables  和 --lock-all-tables  在备份的时候就把表给锁了。  lock tables是只锁一张表， lock all tables，锁的是所有的表

   --master--data=1  或者2    =1 是用来生成slave server的时候采用，添加了change master to的命令， =2的情况下将这个命令给注释掉了。



回复逻辑备份：

mysql -uadm </tmp/mysql.sql

>source '/tmp/mysql.sql'

或者采用mysqlimport工具来实现。



物理备份分为冷备份和热备份：

冷备份只要复制文件的目录，然后覆盖新server的同样路径即可。

热备份，需要一些相关的命令，比如 mysqlhotcopy ADM2 *, 另外也可以通过所表的方式，Flush tables with read lock;  然后cp命令行拷贝，然后在unlock tables.


Mysql没有增量备份。 可以用备份软件来控制，比如 Zmanda