Geekly Articles each Day

ループ内でSQLクエリを回避する方法

この記事では、コードの「腐敗」の兆候の1つ、つまりループ内のSQLクエリを回避する方法を示します。 例は、OOPを使用しない単純なPHPにあります。 これにより、理解が大幅に促進されます。 読み取りには5〜10分かかります。

ループ内のクエリを避ける必要があるのはなぜですか？


すべてが非常に簡単です。 各要求は、データベースにアクセスするための「準備」および「最終」操作の時間の損失です。 製品ごとに、写真、割引、およびいくつかのオプション（ドレスの色の選択など）の個別のテーブルがあるとします。 つまり、各製品に関する情報を取得するには、3つのリクエストを完了する必要があります。 つまり 100個の商品の引き出しの300件のリクエスト+商品リストのリクエスト。 100製品の合計301リクエスト。 その結果、アプリケーションのパフォーマンスが大幅に低下します。 これは避けることができ、避けるべきです。

以下は、OpenCart 3のコードです。

public function getProducts($data = array()) { //..... $query = $this->db->query($sql); foreach ($query->rows as $result) { // for never get one more time with same product id if(!isset($product_data[$result['product_id']])){ $product_data[$result['product_id']] = $this->getProduct($result['product_id']); } } return $product_data; } /* $this->getProduct($result['product_id'])   1   . ..  getProducts  product_id-  ,     ""      .    (). */

最適化のタスク


製品と価格という2つのテーブルを持つタスクを考えます。 1つの製品には、異なるタイプの複数の価格があります。 結果は配列として取得する必要があります。

 print_r($products) /* Array ( [1] => Array ( [product_id] => 1 [name] =>  1 [prices] => Array ( [0] => Array ( [price_id] => 45 [product_id] => 1 [type] => 3 [price] => 95.00 ) [1] => Array ( [price_id] => 55 [product_id] => 1 [type] => 1 [price] => 90.00 ) [2] => Array ( [price_id] => 58 [product_id] => 1 [type] => 2 [price] => 90.00 ) ) ) ) */

製品表：
 CREATE TABLE `product` ( `product_id` int(11) NOT NULL AUTO_INCREMENT, `name` varchar(255) DEFAULT NULL, PRIMARY KEY (`product_id`) ) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=utf8; INSERT INTO `product` (`product_id`, `name`) VALUES (1, ' 1'); INSERT INTO `product` (`product_id`, `name`) VALUES (2, ' 2'); INSERT INTO `product` (`product_id`, `name`) VALUES (3, ' 3'); INSERT INTO `product` (`product_id`, `name`) VALUES (4, ' 4'); INSERT INTO `product` (`product_id`, `name`) VALUES (5, ' 5'); INSERT INTO `product` (`product_id`, `name`) VALUES (6, ' 6'); INSERT INTO `product` (`product_id`, `name`) VALUES (7, ' 7'); INSERT INTO `product` (`product_id`, `name`) VALUES (8, ' 8'); INSERT INTO `product` (`product_id`, `name`) VALUES (9, ' 9'); INSERT INTO `product` (`product_id`, `name`) VALUES (10, ' 10');


価格表：
 CREATE TABLE `product_price` ( `price_id` int(11) NOT NULL AUTO_INCREMENT, `product_id` int(11) NOT NULL, `type` enum('1','2','3') NOT NULL, `price` decimal(10,2) NOT NULL, PRIMARY KEY (`price_id`) ) ENGINE=InnoDB AUTO_INCREMENT=71 DEFAULT CHARSET=utf8; INSERT INTO `product_price` (`price_id`, `product_id`, `type`, `price`) VALUES (55, 1, '1', 90.00); INSERT INTO `product_price` (`price_id`, `product_id`, `type`, `price`) VALUES (58, 1, '2', 90.00); INSERT INTO `product_price` (`price_id`, `product_id`, `type`, `price`) VALUES (45, 1, '3', 95.00); INSERT INTO `product_price` (`price_id`, `product_id`, `type`, `price`) VALUES (56, 2, '1', 90.00); INSERT INTO `product_price` (`price_id`, `product_id`, `type`, `price`) VALUES (49, 2, '2', 45.00); INSERT INTO `product_price` (`price_id`, `product_id`, `type`, `price`) VALUES (42, 2, '3', 96.00); INSERT INTO `product_price` (`price_id`, `product_id`, `type`, `price`) VALUES (57, 3, '1', 23.00); INSERT INTO `product_price` (`price_id`, `product_id`, `type`, `price`) VALUES (47, 3, '2', 53.00); INSERT INTO `product_price` (`price_id`, `product_id`, `type`, `price`) VALUES (51, 3, '3', 12.00); INSERT INTO `product_price` (`price_id`, `product_id`, `type`, `price`) VALUES (43, 4, '1', 89.00); INSERT INTO `product_price` (`price_id`, `product_id`, `type`, `price`) VALUES (46, 4, '2', 4.00); INSERT INTO `product_price` (`price_id`, `product_id`, `type`, `price`) VALUES (52, 4, '3', 15.00); INSERT INTO `product_price` (`price_id`, `product_id`, `type`, `price`) VALUES (54, 5, '1', 43.00); INSERT INTO `product_price` (`price_id`, `product_id`, `type`, `price`) VALUES (41, 5, '2', 44.00); INSERT INTO `product_price` (`price_id`, `product_id`, `type`, `price`) VALUES (48, 5, '3', 34.00); INSERT INTO `product_price` (`price_id`, `product_id`, `type`, `price`) VALUES (44, 6, '1', 26.00); INSERT INTO `product_price` (`price_id`, `product_id`, `type`, `price`) VALUES (59, 6, '2', 26.00); INSERT INTO `product_price` (`price_id`, `product_id`, `type`, `price`) VALUES (60, 6, '3', 26.00); INSERT INTO `product_price` (`price_id`, `product_id`, `type`, `price`) VALUES (53, 7, '1', 87.00); INSERT INTO `product_price` (`price_id`, `product_id`, `type`, `price`) VALUES (61, 7, '2', 87.00); INSERT INTO `product_price` (`price_id`, `product_id`, `type`, `price`) VALUES (50, 7, '3', 77.00); INSERT INTO `product_price` (`price_id`, `product_id`, `type`, `price`) VALUES (62, 8, '1', 77.00); INSERT INTO `product_price` (`price_id`, `product_id`, `type`, `price`) VALUES (63, 8, '2', 77.00); INSERT INTO `product_price` (`price_id`, `product_id`, `type`, `price`) VALUES (64, 8, '3', 77.00); INSERT INTO `product_price` (`price_id`, `product_id`, `type`, `price`) VALUES (65, 9, '1', 77.00); INSERT INTO `product_price` (`price_id`, `product_id`, `type`, `price`) VALUES (66, 9, '2', 77.00); INSERT INTO `product_price` (`price_id`, `product_id`, `type`, `price`) VALUES (67, 9, '3', 77.00); INSERT INTO `product_price` (`price_id`, `product_id`, `type`, `price`) VALUES (68, 10, '1', 77.00); INSERT INTO `product_price` (`price_id`, `product_id`, `type`, `price`) VALUES (69, 10, '2', 77.00); INSERT INTO `product_price` (`price_id`, `product_id`, `type`, `price`) VALUES (70, 10, '3', 77.00);


ループ内にクエリがあるオプション：

 <?php define('DB_HOST' , 'localhost'); define('DB_USER' , 'mysqluser'); define('DB_PASSWORD' , 'password'); define('DB_NAME' , 'habr'); $conn = mysqli_connect(DB_HOST , DB_USER , DB_PASSWORD) or die('Can not connect to db'); mysqli_select_db($conn , DB_NAME) or die(mysqli_error($conn)); mysqli_query($conn , 'SET NAMES utf8'); //   - 7 . $sql = 'select * from product'; $product_result = mysqli_query($conn , $sql); //       $products = array(); while ($product = mysqli_fetch_assoc($product_result)) { //     . $prices = array(); $sql = 'select * from product_price WHERE product_id = ' . (int) $product['product_id']; $price_result = mysqli_query($conn , $sql); while ($price = mysqli_fetch_assoc($price_result)) { $prices[] = $price; } //      $product['prices'] = $prices; //     $products[] = $product; } print_r($products); if ($conn) { mysqli_close($conn); }

したがって、7つの製品を表示するために、1 + 7のリクエストを行いました。 最適化する方法：

  1. 表から製品を選択します。 それらを配列に入れますが、product_idをキーとして使用します。
  2. 見つかった商品のすべての価格を選択します。
  3. 商品の「プッシュ」価格を見つけます。

同じことですが、明確なPHP言語では：

 <?php define('DB_HOST' , 'localhost'); define('DB_USER' , 'mysqluser'); define('DB_PASSWORD' , 'password'); define('DB_NAME' , 'habr'); $conn = mysqli_connect(DB_HOST , DB_USER , DB_PASSWORD) or die('Can not connect to db'); mysqli_select_db($conn , DB_NAME) or die(mysqli_error($conn)); mysqli_query($conn , 'SET NAMES utf8'); // 3  $sql = 'select * from product LIMIT 3'; $product_result = mysqli_query($conn , $sql); //       $products = array(); while ($product = mysqli_fetch_assoc($product_result)) { //  $product['prices'] = array(); //  .      product_id $products[(int) $product['product_id']] = $product; } //   ,     . if (count($products) > 0 ) { //  product_id-       . $product_ids = array_keys($products); //    $sql = 'select * from product_price where product_id in (' . implode(',' , $product_ids ). ')'; $prices_result = mysqli_query($conn , $sql) or die(mysqli_error($conn)); while ($price = mysqli_fetch_assoc($prices_result)) { $products[(int) $price['product_id']]['prices'][] = $price; } } print_r($products); if ($conn) { mysqli_close($conn); }

したがって、商品の数量を選択するには、2つのクエリを完了するだけで済みます。 このようなスクリプトの作業には、多数の製品のサンプルに顕著な違いがあります。 このアプローチは一般化できます。 たとえば、可能であれば、外部リソース（ファイルシステム、memcache、redis）へのループを避けます。 そして、決定の合理性の原則を覚えておいてください。

Source: https://habr.com/ru/post/J350468/

More articles:


All Articles