收集mysql查询语句及其分析优化

软件版本：     PHP71     MYSQL5.7      CI3.0             本文以ci框架内核为例子展开叙述。众所周知，慢查询优化一直是难题，而慢查询导致的页面加载过慢十分影响用户体验，索引是解决这类问题的最简单粗暴的方法。         有人会通过广泛的添加索引提高查询效率，但是要知道建立的索引不一定合理，查询也不一定能命中。还有，索引是占用内存的，过多后者过大的索引会影响到表写入的速度，一个需要多步复杂处理的业务处理万一出现了超时而且保证不了数据的原子性那就非常危险了。所以怎么建是有点技巧的。   定位语句：         优化的第一步是要找出问题在哪，即是要系统收集查询的sql。在此要引入面向切面的概念，把收集工作放在业务处理层是不实际的。放在model层自定义析构函数收集sql，有个局限性，只能查到最后一句。比较方便的是在db driver做标记，执行后处理。ci3的文件是system/database/DB_driver.php（ci4已经出了，有个稳定版本就可以考虑替换到项目中）。我在方法query做了个标记   #system/database/DB_driver.php /** * Execute the query * * Accepts an SQL string as input and returns a result object upon * successful execution of a "read" type query. Returns boolean TRUE * upon successful execution of a "write" type query. Returns boolean * FALSE upon failure, and if the $db_debug variable is set to TRUE * will raise an error. * * @param string $sql * @param array $binds = FALSE An array of binding data * @param bool $return_object = NULL * @return mixed */ public function query($sql, $binds = FALSE, $return_object = NULL) { if ($sql === '') { log_message('error', 'Invalid query: '.$sql); return ($this->db_debug) ? $this->display_error('db_invalid_query') : FALSE; } elseif ( ! is_bool($return_object)) { $return_object = ! $this->is_write_type($sql); } // Verify table prefix and replace if necessary if ($this->dbprefix !== '' && $this->swap_pre !== '' && $this->dbprefix !== $this->swap_pre) { $sql = preg_replace('/(\W)'.$this->swap_pre.'(\S+?)/', '\\1'.$this->dbprefix.'\\2', $sql); } // Compile binds if needed if ($binds !== FALSE) { $sql = $this->compile_binds($sql, $binds); } // Is query caching enabled? If the query is a "read type" // we will load the caching class and return the previously // cached query if it exists if ($this->cache_on === TRUE && $return_object === TRUE && $this->_cache_init()) { $this->load_rdriver(); if (FALSE !== ($cache = $this->CACHE->read($sql))) { return $cache; } } // Save the query for debugging if ($this->save_queries === TRUE) { $this->queries[] = $sql; } // Start the Query Timer $time_start = microtime(TRUE); // Run the Query if (FALSE === ($this->result_id = $this->simple_query($sql))) { if ($this->save_queries === TRUE) { $this->query_times[] = 0; } // This will trigger a rollback if transactions are being used if ($this->_trans_depth !== 0) { $this->_trans_status = FALSE; } // Grab the error now, as we might run some additional queries before displaying the error $error = $this->error(); // Log errors log_message('error', 'Query error: '.$error['message'].' - Invalid query: '.$sql); if ($this->db_debug) { // We call this function in order to roll-back queries // if transactions are enabled. If we don't call this here // the error message will trigger an exit, causing the // transactions to remain in limbo. while ($this->_trans_depth !== 0) { $trans_depth = $this->_trans_depth; $this->trans_complete(); if ($trans_depth === $this->_trans_depth) { log_message('error', 'Database: Failure during an automated transaction commit/rollback!'); break; } } // Display errors return $this->display_error(array('Error Number: '.$error['code'], $error['message'], $sql)); } return FALSE; } // Stop and aggregate the query time results $time_end = microtime(TRUE); $this->benchmark += $time_end - $time_start; if ($this->save_queries === TRUE) { $this->query_times[] = $time_end - $time_start; } // Increment the query counter $this->query_count++; // Will we have a result object instantiated? If not - we'll simply return TRUE if ($return_object !== TRUE) { // If caching is enabled we'll auto-cleanup any existing files related to this particular URI if ($this->cache_on === TRUE && $this->cache_autodel === TRUE && $this->_cache_init()) { $this->CACHE->delete(); } return TRUE; } // Load and instantiate the result driver $driver = $this->load_rdriver(); $RES = new $driver($this); // Is query caching enabled? If so, we'll serialize the // result object and save it to a cache file. if ($this->cache_on === TRUE && $this->_cache_init()) { // We'll create a new instance of the result object // only without the platform specific driver since // we can't use it with cached data (the query result // resource ID won't be any good once we've cached the // result object, so we'll have to compile the data // and save it) $CR = new CI_DB_result($this); $CR->result_object = $RES->result_object(); $CR->result_array = $RES->result_array(); $CR->num_rows = $RES->num_rows(); // Reset these since cached objects can not utilize resource IDs. $CR->conn_id = NULL; $CR->result_id = NULL; $this->CACHE->write($sql, $CR); } $_SERVER['MYSQL_SELECT_SIGNAL'] = 1; $_SERVER['MYSQl_SELECT_QUERY'][] = $this->last_query(); return $RES; }

 

    记录：         标记完之后就要记录了。一开始我是想用钩子，在post_system触发。但是实际中发现业务代码存在exit()，根本就没走到post_system就强制退出。所以exit要慎用啊，需要断点的地方可以抛出/捕获异常的方式处理。幸好输入、输出都用封装的方法处理，退而求其次记录的锚点放在输出的公共方法里。         /*          * 记录mysql查询sql          */         if (isset($_SERVER['MYSQL_SELECT_SIGNAL']) && $_SERVER['MYSQL_SELECT_SIGNAL']==1){             $handle = fopen($file_path.'mysql.log', 'a+');             $content = '';             foreach ($_SERVER['MYSQl_SELECT_QUERY'] as $query){                 $content .= $query.';||'.PHP_EOL;             }             fwrite($handle, $content);         }     语句分析：         接下来是分析log里面的sql。mysql有个自带方法叫explain，功能足够强大，不需要安装其他软件。下面简单介绍下用法。 mysql> explain select * from 9thleaf_order_book_info where customer_id = 'a1'; +----+-------------+-------------------------+------------+------+---------------+-------------+---------+-------+------+----------+-------+ | id | select_type | table                   | partitions | type | possible_keys | key         | key_len | ref   | rows | filtered | Extra | +----+-------------+-------------------------+------------+------+---------------+-------------+---------+-------+------+----------+-------+ |  1 | SIMPLE      | 9thleaf_order_book_info | NULL       | ref  | customer_id   | customer_id | 144     | const |    1 |   100.00 | NULL  | +----+-------------+-------------------------+------------+------+---------------+-------------+---------+-------+------+----------+-------+ 1 row in set, 1 warning (0.00 sec)   其中比较关键的信息解释： id：     Id如果相同，可以认为是一组，从上往下顺序执行；     在所有组中，id值越大，优先级越高，越先执行；   Select_type：     主要有SIMPLE、PRIMARY、SUBQUERY、DERIVED、UNION、UNION RESULT。分别用来表示查询的类型，主要用户区别普通查询、联合查询、子查询的复杂查询     SIMPLE  ：    简单的select查询，查询中不包含子查询或者UNION     PRIMARY    :    查询中若包含任何复杂的字部分，最外层查询则被标记为PRIMARY     SUBQUERY    :    在select或where列表中包含的子查询     DERIVED    :    在from列表中包含的子查询被标记为derived（衍生），mysql会递归执行这些子查询，把结果放在临时表中     UNION    :    若第二个select出现在union之后，则被标记问union；若union包含在from子句的子查询中，外层select将被标记为：derived     UNION RESULT     :     从union表获取结果的select   type：     Type所显示的是查询使用了哪种类型，包括all、index、range、ref、eq_ref、const，system、null     从好到坏排行：system > const > eq_ref > ref > range > index > all     system    ：    系统表专用，可以忽略     const    ：    用于比较primary key或unique index，只通过索引一次就能找到     ed_ref    ：    唯一性索引扫描，对于每个索引键，表中只有一条记录与之匹配，常见于主键或唯一索引扫描     ref    ：    非唯一碎银扫描     range    ：    只检索给定范围的行，使用一个索引来选择行，key列显示使用了哪个索引，一般就是使用between、<、>、in等where查询。     index    ：    full index scan，index与all区别为index类型只遍历索引树，这通常比all快，因为索引文件通常比数据文件下。     all    ：    full table scan 遍历权标以找到匹配的行   extra：     额外信息     using filesort    ：    说明mysql会对数据使用一个外部的索引排序，而不是按照表内的索引顺序进行读取，mysql中无法利用索引完成的排序操作成为文件排序。效率评分为差     using temporary    ：    使用了用临时表保存中间结果，mysql在对查询结果排序使用临时表。常见于排序ordr by和分组查询group by。效率评分为很差     using index    ：    标识相应的select操作中使用了覆盖索引，避免访问了表的数据行，效率不错。如果同时出现using where，表明索引被用来执行索引键值的查找；如果没有出现using where，表明索引用来读取数据而非执行查找动作。     using where    ：    表明使用where过滤     using join buffer    ：    表明使用了链接缓存，比如说在查询的时候，多表join的次数非常多，name将配置文件找那个的缓冲区的join buffer调大一些。     索引选择：                中期rows为遍历条数，多表联查获取含子查询的情况，总遍历条数是乘积关系。按照mysql的性能，遍历条数超过百万级就需要添加索引了。然后如何选择添加怎样的索引呢？首先要知道索引是针对where、order by的，按照最左原则。多个where条件时通过计算索引的选择性来选取一个或多个字段。有可能的话，多向索引覆盖靠。以下是总结的几条原则： ·   1）通过索引的选择性确定该索引是否合理（计算值>70%）         SELECT * FROM employees WHERE first_name='Martial' AND last_name='DuCasse';         1.单列的选择性：distinct first_name)/count(*) from employees;  --0.45%                select count(distinct last_name)/count(*) from employees;   --0.58%         2.组合列的选择性：                select count(distinct(concat(first_name,last_name)))/count(*) from employees  --99.32%  --30     2）创建组合索引时，根据最左原则，选择性高的列为前导列。因此1）的案例创建语句为：create index idx_name01 on employees(last_name,first_name);     3）每个索引的叶子节点都是又索引列+主键组成。     4）当索引列长度过长，且列前缀的选择性接近全列索引，可以用前缀索引代替，因为索引变短能减少索引的叶子节点，从而降低内存的负载。建立语句：create index idx_name02 on employees(last_name,first_name(4));     5）innodb默认聚蔟索引，根据主键查询条目时不用回表，叶子节点就是行记录。     6）一个索引包含了所有需要查询的字段，则成为索引覆盖，即只需扫描索引而无需回表。使用了索引覆盖在explain的extra列为using index。       特殊案例优化： 大数据分页查询：         实验样本我准备了一个student表，内含一千六百万条数据。 mysql> desc student; +----------+-------------+------+-----+---------+----------------+ | Field    | Type        | Null | Key | Default | Extra          | +----------+-------------+------+-----+---------+----------------+ | id       | int(11)     | NO   | PRI | NULL    | auto_increment | | name     | varchar(32) | YES  |     | NULL    |                | | birthday | date        | YES  |     | NULL    |                | | sex      | varchar(2)  | YES  |     | NULL    |                | +----------+-------------+------+-----+---------+----------------+ 4 rows in set (0.00 sec)     mysql> select count(*) from student; +----------+ | count(*) | +----------+ | 16000000 | +----------+ 1 row in set (17.10 sec)     然后进行简单的查询比较 mysql> select * from student limit 100,10;       ----10 rows in set (0.00 sec) mysql> select * from student limit 100000,10;    —---10 rows in set (0.04 sec) mysql> select * from student limit 1000000,10;   ----10 rows in set (1.34 sec) mysql> select * from student limit 1000000,10;  ----10 rows in set (12.25 sec)           由查询时间可以看出，十万级有少幅度增长，百万级成十倍增长，千万级呈现五十倍的增长，可以得出查询效率与遍历条数成反比，几何关系呈指数增长； 在实际业务中，当翻页翻到千万条后需要十几秒，这是必须要优化的程度。然后我们很自然的想到利用索引覆盖来提高效率，  innodb默认是聚簇索引表，其叶子节点就是行记录，但是，即使走索引覆盖，其效果也不是最理想的，因为叶子节点比较大。             这时候可以使用二级索引，走索引覆盖，其叶子节点为:id + id，这个叶子节点比较小，也就是最理想的索引覆盖      #修改查询语句 mysql> create index idx_id on student(id); mysql> Select * from student as a inner join (select id from student limit 100000,10) as b on b.id=a.id;        ----10 rows in set (0.13 sec) mysql> Select * from student as a inner join (select id from student limit 1000000,10) as b on b.id=a.id;        ----10 rows in set (0.54 sec) mysql> Select * from student as a inner join (select id from student limit 10000000,10) as b on b.id=a.id;        ----10 rows in set (6.74 sec)           改装了查询语句之后，百万级有明显提升，千万级也有一定幅度的加速，但是还是太慢，这大概就是mysql的局限性，没有办法。   其他案例欢迎大家补充。。。