mysql性能.docVIP

Topsql分析 找出占用耗用时间长的SQL语句，然后进行优化；找出哪些sql磁盘读写多、缓存命中率低、优化成本几乎为0的sql语句进行优化，先从语法优化，再从业务逻辑优化 MySQL锁定机制 1. 行级锁定 InnooDB 2. 表级锁定 MyISAM 3. 页级锁定 BerkeleyDB 表级锁定又包括读锁定和写锁定 获得读锁定的2个条件： 1 请求锁定的资源当前没有写锁定 2 写锁定等待队列中没有更高优先级的写锁定 加读锁定：LOCK TABLE test_table READ; 解除锁定：UNLOCK TABLES; 行级锁定又包括共享锁和排他锁； 系统锁定争用情况查询 （MYISAM）表级锁定： 主要看下面2个变量： table_locks_immediate 产生表级锁定的次数； Tabel_locks_waited 出现表级锁定争用而发生等待的次数； mysql SHOW STATUS LIKE Table%; +-----------------------+---------+ | Variable_name | Value | +-----------------------+---------+ | Table_locks_immediate | 6452370 | | Table_locks_waited | 0 | +-----------------------+---------+ 2 rows in set (0.00 sec) 两个状态都是从系统启动后开始记录，如果wait的值很高，说明表级锁争用严重； (INNODB)行级锁： mysql SHOW STATUS LIKE innodb_row_lock% ; +-------------------------------+-------+ | Variable_name | Value | +-------------------------------+-------+ | Innodb_row_lock_current_waits | 0 | | Innodb_row_lock_time | 0 | | Innodb_row_lock_time_avg | 0 | | Innodb_row_lock_time_max | 0 | | Innodb_row_lock_waits | 0 |(系统启动到此刻总共等待的次数)不能太高 +-------------------------------+-------+ 5 rows in set (0.00 sec) Mysql Query Optimizer ：MYSQL中专门负责优化select语句的优化器模块 Query 优化基本思路： 1. 优化更需优化的Query 怎么理解一个query每小时执行1000次，每次消耗20个IO；另一个query每小时执行10次，每次消耗2000个IO；这种情况优化第一个query能节省更多的资源； 通过cpu消耗来分析，原理同上面一样，尤其是在排序，分组这些对cpu消耗较大的操作； 2. 定位优化对象的性能瓶颈 在拿到一条需要优化的query后，首先判断这个query的瓶颈到底是IO还是CPU；到底是因为在数据访问上消耗太多的时间，还是在数据的运算（如分组排序等）方面花费太多资源； 可以使用profiling清楚地找到一个query瓶颈，在版本5.0.30后才添加； 3. 明确的优化目标 首先要清楚数据库目前的状态，同时也要清楚数据库中和该query相关的数据库对象的各种信息，而且还要了解该query在整个应用系统中所时间的功能；把握了该query相关的数据库对象信息，就应该知道实现该query最理想情况下需要消耗的cpu资源，最糟糕需要消耗多少cpu资源；最后，通过该query所实现的功能点在整个应用系统中的重要地位，可以大概分析出该query占用的系统资源比例，还能知道该query的效率给客户带来的体验影响到底有多大； 4. 从Explian入手 Explain只是用来获取一个query在当前状态的数据库中的执行计划的； 5. 多使用profile 6. 永远用小结果集驱动大的结果集 很多人喜欢在优化sql的时候使用小表驱动大表，这样也不一定对，因为大表在经过where条件过滤后返回的结果集也许比小表的还小；这种情况下，用大表驱动小表，得到相反的性能效果； 其实这也容易理解，在mysql中只有nested loop一种join方式，也就是说mysql的join都是通过嵌套循环