Rails常见数据库优化

ActiveRecord的爱恨情仇

ActiveRecord无疑是生产力的一大利器,可以帮你在前期脱离手写SQL语句生存下来。不过这个是个双刃剑,使用不当也会带来一些问题。当应用简单,数据不多的时候,它基本上可以让你不需要考虑数据库查询问题。不过当应用变得复杂,数据量稍微大一些的时候,就得要稍微捡起SQL用心优化一下。ActiveRecord跟SQL的配合还是非常棒的,至少可以帮你生成没语法错误的SQL语句:~D

还是要有些SQL的基础,不然ActiveRecord会让你有错觉。忘记了没关系,它可以帮你捡起来。

用includes, preload等解决1+n问题

比如取出了ShipmentOrder,每次还要取相应的ShipmentHeader

ShipmentOrder.where(...)

# 改成以下方式
ShipmentOrder.includes(:shipment_header).where(...)  

解决前

解决后

主键外键等查找到用的key都要加Index

所有经查被查询的字段,例如订单号,运单号等都应该加上index,特别是数据量大的时候的多表查询,以下用GROUP BY做例子, 其中fSKU没有加index,而fSKUID加了index,数据量是12W条。

mysql> SELECT  fSKU, fSKUName, fBarcode,  SUM(shipment_details.fOrderedQty) AS sku_sum FROM `shipment_details`  GROUP BY fSKU LIMI  
T 3 OFFSET 0;  
+----------+---------------------------------------------------------------+----------+---------+
| fSKU     | fSKUName                                                      | fBarcode | sku_sum |
+----------+---------------------------------------------------------------+----------+---------+
| 04116916 | 水宝宝防晒喷雾170gSPF50(粉)                                 | 04116916 |      11 |
| 04117119 | 水宝宝儿童防晒乳液237MLspf50绿色(无泪无香)                  | 04117119 |       1 |
| 04117313 | 水宝宝清爽透气防晒喷雾SPF50 177g                              | 04117313 |       3 |
+----------+---------------------------------------------------------------+----------+---------+
3 rows in set (1.03 sec)  

以上用的1.03秒多,下面看看用加索引的fSKUID的情况,基本上是0.0秒:

mysql> SELECT  fSKU, fSKUName, fBarcode,  SUM(shipment_details.fOrderedQty) AS sku_sum FROM `shipment_details`  GROUP BY fSKUID LIMIT 3 OFFSET 0;  
+---------------+---------------------------------------+---------------+---------+
| fSKU          | fSKUName                              | fBarcode      | sku_sum |
+---------------+---------------------------------------+---------------+---------+
| SWSAHC0001    | 韩国AHC B5玻尿酸洗面奶180ml           | 8809471951116 |    1102 |
| 8809428449680 | RE:CIPEmissage指甲油3#5ml*3可爱       | 8809428449680 |       9 |
| STOSHDO0001   | 131 粉底刷 BB霜斜头刷 1把/盒          | 4901872636501 |     420 |
+---------------+---------------------------------------+---------------+---------+
3 rows in set (0.00 sec)  

12W数据不算多,但能直观地体现这种优劣,这种性能上的巨大差异,其实可以用MySQL的EXPLAIN语句看下它具体处理上的不同。

mysql> EXPLAIN SELECT  fSKU, fSKUName, fBarcode,  SUM(shipment_details.fOrderedQty) AS sku_sum FROM `shipment_details`  GROUP BY fSKU LIMIT 3 OFFSET 0;  
+----+-------------+------------------+------+---------------+------+---------+------+--------+---------------------------------+
| id | select_type | table            | type | possible_keys | key  | key_len | ref  | rows   | Extra                           |
+----+-------------+------------------+------+---------------+------+---------+------+--------+---------------------------------+
|  1 | SIMPLE      | shipment_details | ALL  | NULL          | NULL | NULL    | NULL | 127065 | Using temporary; Using filesort |
+----+-------------+------------------+------+---------------+------+---------+------+--------+---------------------------------+
1 row in set (0.00 sec)  

上面可以看到它实际上要处理127,065条数据,因为没有任何优化,所以它使用的是简单粗暴的filesort。看看使用加索引的fSKUID如何:

mysql> EXPLAIN SELECT  fSKU, fSKUName, fBarcode,  SUM(shipment_details.fOrderedQty) AS sku_sum FROM `shipment_details`  GROUP BY fSKUID LIMIT 3 OFFSET 0;  
+----+-------------+------------------+-------+---------------+----------------------------------+---------+------+------+-------+
| id | select_type | table            | type  | possible_keys | key                              | key_len | ref  | rows | Extra |
+----+-------------+------------------+-------+---------------+----------------------------------+---------+------+------+-------+
|  1 | SIMPLE      | shipment_details | index | NULL          | index_shipment_details_on_fSKUID | 768     | NULL |   45 |       |
+----+-------------+------------------+-------+---------------+----------------------------------+---------+------+------+-------+
1 row in set (0.00 sec)  

这里可以看到它使用了index_shipment_details_on_fSKUID这个只要处理45条记录。

使用数据库内置函数

类似COUNT, SUM之类的这些数据,都是可以通过内置函数算出来,不需要一个个取出来自己算,数据量大的时候效率比较高。

@order_details = @q_params.result.group("fSKUID").select('fSKU, fSKUName, fBarcode, shipment_detai    ls.fShipmentID, SUM(shipment_details.fOrderedQty) AS sku_sum').paginate(:page => params[:page], :per_p    age => 20)

生成以下语句:

SELECT  fSKU, fSKUName, fBarcode, shipment_details.fShipmentID,  
        SUM(shipment_details.fOrderedQty) AS sku_sum 
FROM `shipment_details`  
LEFT OUTER JOIN `shipment_headers`  
ON `shipment_headers`.`fShipmentID` = `shipment_details`.`fShipmentID`  
WHERE `shipment_headers`.`fCheckStatusCode` = 'Finished'  
GROUP BY fSKUID  
LIMIT 20 OFFSET 0  

MySQL使用

看处理状态

当数据量大的时间,建立索引或跑Migration的时候,可能要花比较多时间,可以看目前在处理什么任务。

mysql> show processlist;  
+------+-------------+------------------+-------------+---------+------+-------+------------------+
| Id   | User        | Host             | db          | Command | Time | State | Info             |
+------+-------------+------------------+-------------+---------+------+-------+------------------+
| 5231 | testuser | 127.0.0.1:2177 | testdb | Sleep   |   49 |       | NULL             |
| 5377 | testuser | 127.0.0.1:2308 | testdb | Query   |    0 | NULL  | show processlist |
+------+-------------+------------------+-------------+---------+------+-------+------------------+
2 rows in set (0.00 sec)

工具

可视化

DBeaver这个跨平台的可视化工具,用来理清各种表的关系相当不错,而且支持多种数据库 DBeaver

ActiveRecord输出

大部分情况下,可以通过查看ActiveRecord的输出的看性能瓶颈在哪,只不过

其他

  • 数据量特别大的时候,只select相应的column而不是用*有大差异
  • 复杂或多表查询时,可以通过自己写查询语句来提高效率,如果忘记了怎么写的话可以在ActiveRecord生成语句的基础上去做改进。

Pro Active Record

英文版PDF

参考

Guimin Lin

Read more posts by this author.