索引补充
---恢復(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*算法