优化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: 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行就得到结果了

范例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: 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行

原文：http://ly36843.blog.51cto.com/3120113/1640906