索引补充
---恢復(fù)內(nèi)容開始---
索引補充
1、索引
索引是表的目錄，在查找內(nèi)容之前可以先在目錄中查找索引位置，以此快速定位查詢數(shù)據(jù)。對于索引，會保存在額外的文件中。
2、索引種類
- 普通索引：僅加速查詢
- 唯一索引：加速查詢 + 列值唯一（可以有null）
- 主鍵索引：加速查詢 + 列值唯一 + 表中只有一個（不可以有null）
- 組合索引：多列值組成一個索引，
? ? ? ? ? ? ? 專門用于組合搜索，其效率大于索引合并 - 全文索引：對文本的內(nèi)容進(jìn)行分詞，進(jìn)行搜索?
索引合并，使用多個單列索引組合搜索
覆蓋索引，select的數(shù)據(jù)列只用從索引中就能夠取得，不必讀取數(shù)據(jù)行，換句話說查詢列要被所建的索引覆蓋
3、相關(guān)命令
| 1 2 3 4 5 6 7 8 9 10 11 12 13 | - 查看表結(jié)構(gòu) ????desc?表名 - 查看生成表的SQL ????show?create?table?表名 - 查看索引 ????show?index?from??表名 - 查看執(zhí)行時間 ????set?profiling = 1; ????SQL... ????show profiles; |
4、使用索引和不使用索引
| 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 | 由于索引是專門用于加速搜索而生，所以加上索引之后，查詢效率會快到飛起來。 # 有索引 mysql>?select?*?from?tb1?where?name?=?'wupeiqi-888'; +-----+-------------+---------------------+----------------------------------+---------------------+ | nid |?name????????| email?????????????? | radom??????????????????????????? | ctime?????????????? | +-----+-------------+---------------------+----------------------------------+---------------------+ | 889 | wupeiqi-888 | wupeiqi888@live.com | 5312269e76a16a90b8a8301d5314204b | 2016-08-03 09:33:35 | +-----+-------------+---------------------+----------------------------------+---------------------+ 1 row?in?set?(0.00 sec) # 無索引 mysql>?select?*?from?tb1?where?email =?'wupeiqi888@live.com'; +-----+-------------+---------------------+----------------------------------+---------------------+ | nid |?name????????| email?????????????? | radom??????????????????????????? | ctime?????????????? | +-----+-------------+---------------------+----------------------------------+---------------------+ | 889 | wupeiqi-888 | wupeiqi888@live.com | 5312269e76a16a90b8a8301d5314204b | 2016-08-03 09:33:35 | +-----+-------------+---------------------+----------------------------------+---------------------+ 1 row?in?set?(1.23 sec) |
5、正確使用索引
數(shù)據(jù)庫表中添加索引后確實會讓查詢速度起飛，但前提必須是正確的使用索引來查詢，如果以錯誤的方式使用，則即使建立索引也會不奏效。
即使建立索引，索引也不會生效：
| 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 | -?like?'%xx' ????select?*?from?tb1?where?name?like?'%cn'; - 使用函數(shù) ????select?*?from?tb1?where?reverse(name) =?'wupeiqi'; -?or ????select?*?from?tb1?where?nid = 1?or?email =?'seven@live.com'; ????特別的：當(dāng)or條件中有未建立索引的列才失效，以下會走索引 ????????????select?*?from?tb1?where?nid = 1?or?name?=?'seven'; ????????????select?*?from?tb1?where?nid = 1?or?email =?'seven@live.com'?and?name?=?'alex' - 類型不一致 ????如果列是字符串類型，傳入條件是必須用引號引起來，不然... ????select?*?from?tb1?where?name?= 999; - != ????select?*?from?tb1?where?name?!=?'alex' ????特別的：如果是主鍵，則還是會走索引 ????????select?*?from?tb1?where?nid != 123 - > ????select?*?from?tb1?where?name?>?'alex' ????特別的：如果是主鍵或索引是整數(shù)類型，則還是會走索引 ????????select?*?from?tb1?where?nid > 123 ????????select?*?from?tb1?where?num > 123 -?order?by ????select?email?from?tb1?order?by?name?desc; ????當(dāng)根據(jù)索引排序時候，選擇的映射如果不是索引，則不走索引 ????特別的：如果對主鍵排序，則還是走索引： ????????select?*?from?tb1?order?by?nid?desc; - 組合索引最左前綴 ????如果組合索引為：(name,email) ????name?and?email???????-- 使用索引 ????name?????????????????-- 使用索引 ????email????????????????-- 不使用索引 |
6、其他注意事項
| 1 2 3 4 5 6 7 8 9 | - 避免使用select?* -?count(1)或count(列) 代替?count(*) - 創(chuàng)建表時盡量時?char?代替?varchar - 表的字段順序固定長度的字段優(yōu)先 - 組合索引代替多個單列索引（經(jīng)常使用多個條件查詢時） - 盡量使用短索引 - 使用連接（JOIN）來代替子查詢(Sub-Queries) - 連表時注意條件類型需一致 - 索引散列值（重復(fù)少）不適合建索引，例：性別不適合 |
7、limit分頁
無論是否有索引，limit分頁是一個值得關(guān)注的問題
?View Code8、執(zhí)行計劃
explain + 查詢SQL - 用于顯示SQL執(zhí)行信息參數(shù)，根據(jù)參考信息可以進(jìn)行SQL優(yōu)化
| 1 2 3 4 5 6 7 | mysql> explain?select?*?from?tb2; +----+-------------+-------+------+---------------+------+---------+------+------+-------+ | id | select_type |?table?| type | possible_keys |?key??| key_len | ref? |?rows?| Extra | +----+-------------+-------+------+---------------+------+---------+------+------+-------+ |? 1 | SIMPLE????? | tb2?? |?ALL??|?NULL??????????|?NULL?|?NULL????|?NULL?|??? 2 |?NULL??| +----+-------------+-------+------+---------------+------+---------+------+------+-------+ 1 row?in?set?(0.00 sec) |
更多參見：
http://www.cnblogs.com/xiaoboluo768/p/5400990.html
http://dev.mysql.com/doc/refman/5.7/en/explain-output.html#jointype_system
9、慢日志查詢
a、配置MySQL自動記錄慢日志
slow_query_log = OFF ? ? ? ? ? ? ? ? ? ? ? ? ? ?是否開啟慢日志記錄
long_query_time = 2 ? ? ? ? ? ? ? ? ? ? ? ? ? ? ?時間限制，超過此時間，則記錄
slow_query_log_file = /usr/slow.log ? ? ? ?日志文件
log_queries_not_using_indexes = OFF ? ? 為使用索引的搜索是否記錄
注：查看當(dāng)前配置信息：
? ? show variables like '%query%'
? ? ?修改當(dāng)前配置：
set global 變量名 = 值
b、查看MySQL慢日志
mysqldumpslow -s at -a ?/usr/local/var/mysql/MacBook-Pro-3-slow.log
| 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 | """ --verbose??? 版本 --debug????? 調(diào)試 --help?????? 幫助 -v?????????? 版本 -d?????????? 調(diào)試模式 -s ORDER???? 排序方式 ?????????????what to sort by (al, at, ar, c, l, r, t), 'at' is default ??????????????al: average lock time ??????????????ar: average rows sent ??????????????at: average query time ???????????????c: count ???????????????l: lock time ???????????????r: rows sent ???????????????t: query time -r?????????? 反轉(zhuǎn)順序，默認(rèn)文件倒序拍。reverse the sort order (largest last instead of first) -t NUM?????? 顯示前N條just show the top n queries -a?????????? 不要將SQL中數(shù)字轉(zhuǎn)換成N，字符串轉(zhuǎn)換成S。don't abstract all numbers to N and strings to 'S' -n NUM?????? abstract numbers with at least n digits within names -g PATTERN?? 正則匹配；grep: only consider stmts that include this string -h HOSTNAME? mysql機(jī)器名或者IP；hostname of db server for *-slow.log filename (can be wildcard), ?????????????default is '*', i.e. match all -i NAME????? name of server instance (if using mysql.server startup script) -l?????????? 總時間中不減去鎖定時間；don't subtract lock time from total time """ |
優(yōu)化MYSQL時，可以盡量使用短索引，如果只是為了提高讀取的速度，可以優(yōu)先使用聚合索引，把幾個字段聚集在一起，當(dāng)然缺點在于操作（寫）的時候會降低效率，短索引一般都是開頭幾個字符基本不同的時候，可以考慮創(chuàng)建，比長索引查詢時間更短
語法如下：
alter table student add index? `ix_xc` (class_id,sname(10));
轉(zhuǎn)載于:https://www.cnblogs.com/xc1234/p/9060157.html
總結(jié)
- 上一篇： React技术栈
- 下一篇： 【学时总结】 ◆学时 · I◆ A*算法