mysql索引及最佳化學習

標籤：mys   select   using   表達   class   row   化學   div   學習   

一般最佳化mysql首先看尋找的資料有沒有用到索引，沒有索引就加索引，有索引時候避免索引失效。

看下面兩條語句

MySQL [release_test_oa]> EXPLAIN SELECT * FROM WORKFLOW_LOGS WHERE ID -100 < 300000;+----+-------------+---------------+------+---------------+------+---------+------+------+-------------+| id | select_type | table         | type | possible_keys | key  | key_len | ref  | rows | Extra       |+----+-------------+---------------+------+---------------+------+---------+------+------+-------------+|  1 | SIMPLE      | WORKFLOW_LOGS | ALL  | NULL          | NULL | NULL    | NULL | 3998 | Using where |+----+-------------+---------------+------+---------------+------+---------+------+------+-------------+

 

MySQL [release_test_oa]> EXPLAIN SELECT * FROM WORKFLOW_LOGS WHERE ID < 300000-100 ;+----+-------------+---------------+-------+---------------+---------+---------+------+------+-------------+| id | select_type | table         | type  | possible_keys | key     | key_len | ref  | rows | Extra       |+----+-------------+---------------+-------+---------------+---------+---------+------+------+-------------+|  1 | SIMPLE      | WORKFLOW_LOGS | range | PRIMARY       | PRIMARY | 8       | NULL | 1999 | Using where |+----+-------------+---------------+-------+---------------+---------+---------+------+------+-------------+

 上面那句沒有使用到ID欄位上的索引，而下面使用到了，所以要避免在列上進行運算式計算。

mysql索引及最佳化學習