mysql中條件查詢加排序和索引的關係

標籤：光伏企業   mysql   article   title   

跟一個朋友，不錯公司的主管交流時，對於mysql中條件查詢和排序時 與索引的關係

mysql> explain select * from article where title=‘希望光伏企業挺過2個月‘ o

rder by id desc\\\\\\\\G

*************************** 1. row ***************************

           id: 1

  select_type: SIMPLE

        table: article

         type: index

possible_keys: NULL

          key: PRIMARY

      key_len: 4

          ref: NULL

         rows: 567

        Extra: Using where

1 row in set (0.00 sec)

mysql> select * from article where title=‘希望光伏企業挺過2個月‘;

+----+-------------+---------+--------+-----------------------------+----------+

-----------+-------+-------------+---------+---------+--------------------------

--------------------------------------------------------------------------------

--------------------------------------------------------------------+--------+--

---------+--------+------------+------------+

| id | category_id | user_id | app_id | title                       | subtitle |

 professor | money | cooperation | project | address | discription

                                                                    | source | r

ecommend | status | ctime      | mtime      |

+----+-------------+---------+--------+-----------------------------+----------+

-----------+-------+-------------+---------+---------+--------------------------

--------------------------------------------------------------------------------

--------------------------------------------------------------------+--------+--

---------+--------+------------+------------+

| 10 |           2 |       4 |      0 | 希望光伏企業挺過2個月 |          |

           |     0 |             |         |         | 希望企業挺過兩個月。 |        |

        1 |      0 | 1371524848 | 1371525226 |

+----+-------------+---------+--------+-----------------------------+----------+

-----------+-------+-------------+---------+---------+--------------------------

--------------------------------------------------------------------------------

--------------------------------------------------------------------+--------+--

---------+--------+------------+------------+

1 row in set (0.00 sec)

mysql> explain select * from article where title=‘希望光伏企業挺過2個月‘;

+----+-------------+---------+------+---------------+------+---------+------+---

---+-------------+

| id | select_type | table   | type | possible_keys | key  | key_len | ref  | ro

ws | Extra       |

+----+-------------+---------+------+---------------+------+---------+------+---

---+-------------+

|  1 | SIMPLE      | article | ALL  | NULL          | NULL | NULL    | NULL |  5

67 | Using where |

+----+-------------+---------+------+---------------+------+---------+------+---

---+-------------+

1 row in set (0.00 sec)

大部門的開發人員會認為第一種查詢時，不會用到索引

本文出自 “Linux營運” 部落格，請務必保留此出處http://2853725.blog.51cto.com/2843725/1562343

mysql中條件查詢加排序和索引的關係