mysql單表查詢語句最佳化

標籤：mysql單表查詢語句最佳化

Mysql語句最佳化

範例1：最佳化語句SELECT * FROM `tbl_order_buy_eta` WHERE `id_order`=1843

#通過explain分析語句結果如下mysql> explain SELECT * FROM `tbl_order_buy_eta` WHERE `id_order`=1843\G*************************** 1. row ***************************           id: 1  select_type: SIMPLE        table: tbl_order_buy_eta         type: ALLpossible_keys: NULL          key: NULL      key_len: NULL          ref: NULL         rows: 1592        Extra: Using where1 row in set (0.00 sec)#從上面我們能看出該語句沒有使用任何索引，查詢到結果掃描了1592行。#查看錶索引mysql> show index from tbl_order_buy_eta\G*************************** 1. row ***************************        Table: tbl_order_buy_eta   Non_unique: 0     Key_name: PRIMARY Seq_in_index: 1  Column_name: id    Collation: A  Cardinality: 1592     Sub_part: NULL       Packed: NULL         Null:    Index_type: BTREE      Comment: Index_comment: 1 row in set (0.00 sec)#結果顯示該表沒有任何索引的存在#我們在id_order列上建立索引mysql> create index index_id_order on tbl_order_buy_eta(id_order);Query OK, 0 rows affected (0.29 sec)Records: 0  Duplicates: 0  Warnings: 0mysql> show index from tbl_order_buy_eta\G*************************** 1. row ***************************        Table: tbl_order_buy_eta   Non_unique: 0     Key_name: PRIMARY Seq_in_index: 1  Column_name: id    Collation: A  Cardinality: 1592     Sub_part: NULL       Packed: NULL         Null:    Index_type: BTREE      Comment: Index_comment: *************************** 2. row ***************************        Table: tbl_order_buy_eta   Non_unique: 1     Key_name: index_id_order Seq_in_index: 1  Column_name: id_order    Collation: A  Cardinality: 1592     Sub_part: NULL       Packed: NULL         Null:    Index_type: BTREE      Comment: Index_comment: 2 rows in set (0.00 sec)     #這一行是我們剛建立的索引#再重新執行該查詢語句，看看查詢結果mysql> explain SELECT * FROM `tbl_order_buy_eta` WHERE `id_order`=1843\G*************************** 1. row ***************************           id: 1  select_type: SIMPLE        table: tbl_order_buy_eta         type: refpossible_keys: index_id_order          key: index_id_order      key_len: 4          ref: const         rows: 1        Extra: NULL1 row in set (0.03 sec)#添加索引後查詢語句走的索引，掃描了1行就得到結果了

範例2：最佳化語句SELECT * FROM `tbl_order_buy` WHERE (`id_order`=1989) AND (`pay_status`=0) AND (`finish_status`=0);

#通過explain分析語句mysql> explain SELECT * FROM `tbl_order_buy` WHERE (`id_order`=1989) AND (`pay_status`=0) AND (`finish_status`=0);+----+-------------+---------------+------+---------------+------+---------+------+------+-------------+| id | select_type | table         | type | possible_keys | key  | key_len | ref  | rows | Extra       |+----+-------------+---------------+------+---------------+------+---------+------+------+-------------+|  1 | SIMPLE      | tbl_order_buy | ALL  | NULL          | NULL | NULL    | NULL | 1592 | Using where |+----+-------------+---------------+------+---------------+------+---------+------+------+-------------+1 row in set (0.00 sec)#該表中沒有任何索引，查詢語句走的是全表掃，一共掃描1592行#建立索引mysql> create index tbl_id_pay_finish on tbl_order_buy(id_order,pay_status,finish_status);Query OK, 0 rows affected (0.59 sec)Records: 0  Duplicates: 0  Warnings: 0mysql> show index from tbl_order_buy;+---------------+------------+-------------------+--------------+---------------+-----------+-------------+----------+--------+------+------------+---------+---------------+| Table         | Non_unique | Key_name          | Seq_in_index | Column_name   | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |+---------------+------------+-------------------+--------------+---------------+-----------+-------------+----------+--------+------+------------+---------+---------------+| tbl_order_buy |          0 | PRIMARY           |            1 | id            | A         |        1592 |     NULL | NULL   |      | BTREE      |         |               || tbl_order_buy |          1 | tbl_id_pay_finish |            1 | id_order      | A         |        1592 |     NULL | NULL   |      | BTREE      |         |               || tbl_order_buy |          1 | tbl_id_pay_finish |            2 | pay_status    | A         |        1592 |     NULL | NULL   | YES  | BTREE      |         |               || tbl_order_buy |          1 | tbl_id_pay_finish |            3 | finish_status | A         |        1592 |     NULL | NULL   | YES  | BTREE      |         |               |+---------------+------------+-------------------+--------------+---------------+-----------+-------------+----------+--------+------+------------+---------+---------------+4 rows in set (0.00 sec)#再次分析sql語句mysql> explain SELECT * FROM `tbl_order_buy` WHERE (`id_order`=1989) AND (`pay_status`=0) AND (`finish_status`=0);+----+-------------+---------------+------+-------------------+-------------------+---------+-------------------+------+-------+| id | select_type | table         | type | possible_keys     | key               | key_len | ref               | rows | Extra |+----+-------------+---------------+------+-------------------+-------------------+---------+-------------------+------+-------+|  1 | SIMPLE      | tbl_order_buy | ref  | tbl_id_pay_finish | tbl_id_pay_finish | 14      | const,const,const |    1 | NULL  |+----+-------------+---------------+------+-------------------+-------------------+---------+-------------------+------+-------+1 row in set (0.06 sec)#可以看到只掃描了1行就得到結果了

範例3：最佳化語句SELECT * FROM `tbl_order_vendor_item_variation` WHERE `id_order`=1989;

#使用explain分析語句mysql> explain SELECT * FROM `tbl_order_vendor_item_variation` WHERE `id_order`=1989;+----+-------------+---------------------------------+------+---------------+------+---------+------+------+-------------+| id | select_type | table                           | type | possible_keys | key  | key_len | ref  | rows | Extra       |+----+-------------+---------------------------------+------+---------------+------+---------+------+------+-------------+|  1 | SIMPLE      | tbl_order_vendor_item_variation | ALL  | NULL          | NULL | NULL    | NULL | 2581 | Using where |+----+-------------+---------------------------------+------+---------------+------+---------+------+------+-------------+1 row in set (0.00 sec)#結果顯示沒有索引，走的是全表掃，一共掃描2581行#建立索引mysql> create index tbl_order_vendor_item_variation_id_order on tbl_order_vendor_item_variation(id_order);Query OK, 0 rows affected (0.36 sec)Records: 0  Duplicates: 0  Warnings: 0#重新分析sql語句mysql> explain SELECT * FROM `tbl_order_vendor_item_variation` WHERE `id_order`=1989\G*************************** 1. row ***************************           id: 1  select_type: SIMPLE        table: tbl_order_vendor_item_variation         type: refpossible_keys: tbl_order_vendor_item_variation_id_order          key: tbl_order_vendor_item_variation_id_order      key_len: 5          ref: const         rows: 1        Extra: NULL1 row in set (0.00 sec)#sql語句走的是剛建立的索引，共掃描1行

本文出自 “ly36843營運” 部落格，請務必保留此出處http://ly36843.blog.51cto.com/3120113/1640906

mysql單表查詢語句最佳化