mysql单表查询语句优化

wfds

Mysql语句优化

范例1：优化语句SELECT * FROM `tbl_order_buy_eta` WHERE `id_order`=1843

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85

#通过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);

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35

#通过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行就得到结果了

范例3：优化语句SELECT * FROM `tbl_order_vendor_item_variation` WHERE `id_order`=1989;

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30

#使用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行