mysql学习笔记（3）

影响mysql server性能的因素

//商业需求对性能的影响

1、在系统优化中，应该尽可能分析出那些不实时的和不完全精确的地方，做出相应的调整，可能会带来性能巨大的提升。
2、无用功能堆积使系统过度复杂影响整体性能。

//系统架构及实现对性能的影响

我们数据库中存放的都是适合在数据库中存放的吗？
以下数据都是不适合在数据库中存放的：
1、二进制多媒体数据
2、流水队列数据
3、超大文本数据

//是否合理的利用了应用层的cache机制？
适用于使用cache缓存的数据有：
1、系统各种配置及规则数据；
2、活跃用户的基本信息数据；
3、活跃用户的个性化定制信息数据；
4、准实时的统计信息数据；  
    所谓准实时统计信息，实际上就是基于时间段的统计信息。
5、其他一些访问频繁但变更较少的数据。

//我们的数据层实现都是精简的吗？
下面列举了一些较为常见的架构设计不当带来性能问题和资源浪费情况：
1、cache系统不合理导致cache命中率下降，造成数据库访问量上升。
2、过度依赖面向对象思想。
3、对可扩展性的过度追求，促使系统设计的时候将对象拆的过于离散，造成系统大量的复杂join操作。
4、对数据库过度依赖，将大量更适合存放到文件系统的数据存放到数据库中。
5、过度理想化系统的用户体验，使大量非核心业务消耗更多资源。

//打开profile
：set profiling = 1;
然后输入两个sql语句
query 1
query 2
使用 show profiles \G 命令来看profiles文件。
然后用：
SHOW profile CPU,BLOCK IO io FOR query 1;
SHOW profile CPU,BLOCK IO io FOR query 2;
可以看出每一个query消耗cpu和io资源的详情。

//schema设计对系统性能的影响
尽量将经常一起查询的属性放到一个表里。

mysql锁定机制简介：

mysql各存储引擎使用了三种类型（级别）的锁定机制：行级锁定、页级锁定和表级锁定。
1、行级锁定：锁定粒度小，提高并发处理能力，但是消耗资源，并且有可能造成死锁。
2、页级锁定: 消耗资源与并发处理能力处于行级锁定和表级锁定之间，它也会造成死锁。
3、表级锁定：锁定粒度大，资源消耗少，解决死锁问题，但是并发度比较小。

InnodB用的是行级锁定，myisam使用的是表级锁定。

表级锁定：
    表级锁定主要有两种：一种是读锁定，另一种是写锁定。主要通过四个队列来维护这两种锁定：
    Current read-lock queue (lock->read)
    Pending read-lock queue (lock->read_wait)
    Current write-lock queue (lock->write)
    Pending write-lock queue (lock->write_wait)

当前所有持有读锁的线程信息都能在     Current read-lock queue (lock->read) 队列中找到，队列中的线程信息按照获取锁的时间进行排序。
而正在等待速锁的线程信息则放在     Pending read-lock queue (lock->read_wait) 队列里面。

一个新的客户端请求在申请获取读锁定资源的时候，需要满足两个条件：
    1、请求锁定的资源当前没有被锁定。
    2、写锁定等待队列中没有更高有限级别的写锁定等待。
       
一个新的客户端请求在申请获取写锁定资源的时候，需要先检查  Current write-lock queue (lock->write) 队列中是否已经有锁定相同资源的写锁定存在。
如果 Current write-lock queue (lock->write) 队列中没有，还需要检查 Pending write-lock queue (lock->write_wait) 队列中有没有等待请求相同资源的信息，
如果有，则该请求也需要进入 Pending write-lock queue (lock->write_wait) 队列中等待。


InnodB使用的是行锁，要想合理利用InnodB的行锁，做到扬长避短，我们必须做好以下工作：

1、尽可能的让所有的数据检索都用索引完成，从而避免InnodB因为无法通过索引键加锁而升级到表锁。
2、合理设计索引，让InnodB在索引键上加锁的时候尽量准确，尽可能的缩小锁定范围，避免造成不必要的锁定而影响其他query的执行。
3、尽可能减少基于范围的数据检索过滤条件，避免因为间隙锁带来的负面影响而锁定了不该锁定的记录。
4、尽量控制事务的大小，减少锁定资源量和锁定时间长度。
5、在业务环境允许下，尽量使用较低级别的事务隔离，以减少mysql因为事务隔离级别所带来的附加成本。


系统锁定争用情况查询：

1、对于表级锁定情况查询：
     show status like 'table%';
     
2、对于InnodB的行级锁定情况查询：
     show status like 'innodb_row_lock%';
     
 查看mysql系统即时详细信息查询：
1、通过创建 InnodB monitor 表来打开 InnodB的monitor功能
      create table innodb_monitor(a int) engin=innodb;
2、然后通过 SHOW INNODB STATUS 来查看详细信息。

//MYSQL数据库query优化

理解mysql的optimizer：  用来优化从query parser 接收的sql语句 优化出最有的数据检索方式，生成执行计划。

query优化的思路和原则主要有以下几个方面：
1、优化更需要优化的query。
    什么更需要优化？   一般来说，高并发低消耗的query（相对的）对整个系统的影响远比地并发高消耗的query大，所以，高并发低消耗的query更需要优化。
     高并发的query对系统的危险性远比低并发的query大。
         
2、定位优化对象的性能瓶颈。
   先问自己：这条query有什么问题？我为什么要优化它？  首先要确定是io band 的还是 cpu band的。
   我们可以通过mysql的profiling清楚的找到query的瓶颈。
   
3、明确优化目标。
   没有目标，优化过程将是一个漫无目的且低效的过程。如何确定优化目标，这是个麻烦的问题，好比 问你自己 如何给自己制定一个合理的目标？
   一般来说，首先要理解数据库的当前整体状态，query涉及到的表的信息，而且还要了解query在整个系统中实现的功能。
   对于数据库整体信息、数据库表、以及query在整个系统中实现的功能，可以分别分析一下该query在这几个层面的影响，包括最好的和最坏的情况。
   然后开始优化。
   
4、从explain入手。
   为什么从explain入手？因为只有explain才能告诉你，这个query在数据库是以一个什么样的执行计划来实现的。
   我们需要清楚的认识到，explain只是用来获取一个query在当前数据库的执行计划，在执行优化之前，我们需要有一个清晰的目标执行计划。    
5、多使用profile。
6、永远用小结果集驱动大结果集（具体含义是？）
7、尽可能在索引中完成排序。
8、只取出需要的columns。
   尤其是需要排序的query中，更有遵守这条原则。
9、仅仅使用最有效的过滤条件。
10、尽可能避免复杂的join和子查询。
    优化本身就是平衡取舍的艺术，只有懂得取舍，平衡整体，才能让系统更优。