mysql-最佳化一

標籤：啟用   5.7   sts   自己   lan   相關   loop   stack   bsp   

建立索引

建立索引是最佳化查詢的一種方式，我們通常會對where列上建立相關索引，可以是單列的索引，也可以是複合索引。

加索引要注意

在where、order by的相關列上可以考慮添加索引；

如果where列中已經存在索引，order by列的索引（假設存在）不會被利用（explain顯示using filesort就表示order by沒有用到索引，where列與order by列建立複合索引，就可以了，也可以在業務層進行排序）；

where列中多個“且”條件列都添加了索引，mysql只會利用“價值最大”的一列（這種情況可以考慮複合索引）；

複合索引的最前匹配原則；

字串列的索引，“abc%”會走索引，“%abc”不會走索引；

為字串建立索引，最好遵循短索引，比如一個CHAR(255)的列，如果該列值的前10位就可保證唯一（或者區分度很高，近似唯一），就可以以前10為建立索引；

盡量避免使用否定條件，如NOT IN、IS NOT、NOT LIKE、!=、<>等（否定句不走索引）；

 

理解以上一系列的規則並不困難，比如說多個“且”條件只會利用一個索引，而多個“或”條件則可能會利用多個索引。因為對於“且”條件，使用一個索引找到相關資料項目後只需要在這些資料項目中進一步條件過濾就可以了，沒有必要再次通過索引做任何事，而對於“或”條件，一個索引找到的條目可能並不滿足其他條件，這就需要多次經過索引進行尋找（當然也並非一定會經過索引尋找，mysql會做出“明智”的決定）。

 NULL值的索引

網上對where name is null這種查詢走不走索引說法不一，有些觀點說不走索引，推薦將列均設為NOT NULL，將null值替換為0、""等形式。但我從官方文檔找到了相關說明，並且也做了相應測試，NULL列確實可以走索引。

MySQL can perform the same optimization on col_name IS NULL that it can use for col_name = constant_value. For example, MySQL can use indexes and ranges to search for NULL with IS NULL.

Examples

1 SELECT * FROM tbl_name WHERE key_col IS NULL;2 3 SELECT * FROM tbl_name WHERE key_col <=> NULL;4 5 SELECT * FROM tbl_name6   WHERE key_col=const1 OR key_col=const2 OR key_col IS NULL;

 

附：可以通過explain查看查詢是否使用了索引；

　　通過show profiles可以查看查詢耗時，show profile for query n可以查詢某條查詢語句的詳細執行情況。資料庫預設是不開啟profiling的，變數profiling是使用者變數，每次都得通過set profiling=1;重新啟用，可以通過set profiling=0關閉。

　　

=、IS、LIKE

IS通常只用於NULL；=就是等值判斷，注意，NULL與任何值都不相等，包括它自己；LIKE常用於字串匹配，如“a_b%”，_代表任意一個字元，%代表任意個字元。

 

IN、JOIN和EXISTS

 select * from t1 where exists (select null from t2 where t2.x=t1.x);的執行邏輯相當於

items = select * from t1for x in items   loop      if ( exists ( select null from t2 where t2.x = x.x )      then          OUTPUT THE RECORD      end ifend loop

select * from t1 where t1.x in (select distinct x from t2);的執行邏輯相當於

select *   from t1, ( select distinct x from t2 ) t2where t1.x = t2.x;

根據以上執行邏輯可以推斷，外表為大表時適合使用in（因為mysql會建立一個內表查詢結果的暫存資料表，然後利用外表索引與暫存資料表做聯合，這種情況，暫存資料表不宜過大，join和in類似，省去了暫存資料表，效能比in要好一些），內表為大表時適合使用exists（因為查詢會遍曆外表項，外表越大遍曆項就越多，而內表可以使用索引）

NOT EXISTS依舊會使用內表的索引，而NOT IN則不會再利用外表的索引，但依然會使用暫存資料表，所以並不意味著NOT EXIST一定優於NOT IN，如果外表遠遠大於內表，NOT IN是有優勢的，反之則應當選用NOT EXISTS

 

隱式轉換

• If one or both arguments are NULL, the result of the comparison is NULL, except for the NULL-safe <=> equality comparison operator. For NULL <=> NULL, the result is true. No conversion is needed.
• If both arguments in a comparison operation are strings, they are compared as strings.
• If both arguments are integers, they are compared as integers.
• Hexadecimal values are treated as binary strings if not compared to a number.
• If one of the arguments is a TIMESTAMP or DATETIME column and the other argument is a constant, the constant is converted to a timestamp before the comparison is performed. This is done to be more ODBC-friendly. Note that this is not done for the arguments to IN()! To be safe, always use complete datetime, date, or time strings when doing comparisons. For example, to achieve best results when using BETWEEN with date or time values, use CAST() to explicitly convert the values to the desired data type.
• A single-row subquery from a table or tables is not considered a constant. For example, if a subquery returns an integer to be compared to a DATETIME value, the comparison is done as two integers. The integer is not converted to a temporal value. To compare the operands as DATETIME values, use CAST() to explicitly convert the subquery value to DATETIME.
• If one of the arguments is a decimal value, comparison depends on the other argument. The arguments are compared as decimal values if the other argument is a decimal or integer value, or as floating-point values if the other argument is a floating-point value.
• In all other cases, the arguments are compared as floating-point (real) numbers.

 Examples

mysql> select 1+1;+-----+| 1+1 |+-----+|   2 |+-----+mysql> select ‘a‘ + ‘55‘;+------------+| ‘a‘ + ‘55‘ |+------------+|         55 |+------------+mysql> select 55 = 55;+--------------+| 55 = 55 |+--------------+|            1 |+--------------+mysql> select ‘55aaa‘ = 55;+--------------+| ‘55aaa‘ = 55 |+--------------+|            1 |+--------------+mysql> select ‘aaa55‘ = 55;+--------------+| ‘aaa55‘ = 55 |+--------------+|            0 |+--------------+

 隱式類型轉換會引發安全和效能問題

安全問題在於字串和數字0比較時，大機率為true，比如select * from user where username=‘zyong‘ and password=0;，知道使用者名稱就可以登入成功了（password的首字元不為0）。

效能問題在於字串呵數字比較時，字串被隱式轉換為浮點型（相當於對列進行了運算），這樣就無法利用原索引了。

LIKE會將數字類型轉換為字串，可以避免上述問題，比如select * from user where username=‘zyong‘ and password like 0;會將password=0中的0作為字串‘0‘處理，但如果是select * from user where id like 123456;（或者select * from user where id like ‘123%‘;）則id列（id為int型）會被轉換為字串來與‘123‘進行比較，不會走索引，所以LIKE只建議用在字串類型上。

 

 

參考：https://stackoverflow.com/questions/229179/null-in-mysql-performance-storage

　　http://muyue123.blog.sohu.com/146930118.html

　　http://www.cnblogs.com/rollenholt/p/5442825.html

　　https://dev.mysql.com/doc/refman/5.7/en/string-comparison-functions.html

mysql-最佳化一