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: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 1592
        Extra: Using where
1 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: 0
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: 
*************************** 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: ref
possible_keys: index_id_order
          key: index_id_order
      key_len: 4
          ref: const
         rows: 1
        Extra: NULL
1 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: 0
mysql> 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行就得到結果了      

#使用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: ref
possible_keys: tbl_order_vendor_item_variation_id_order
          key: tbl_order_vendor_item_variation_id_order
      key_len: 5
          ref: const
         rows: 1
        Extra: NULL
1 row in set (0.00 sec)
#sql語句走的是剛建立的索引，共掃描1行