1. 思考
在圖書館中是如何找到一本書的?在字典中查找一個單詞?
一般的應用系統對比資料庫的讀寫比例在10:1左右(即有10次查詢操作時有1次寫的操作),
而且插入操作和更新操作很少出現性能問題,
遇到最多、最容易出問題還是一些複雜的查詢操作,是以查詢語句的優化顯然是重中之重
2. 解決辦法
當資料庫中資料量很大時,查找資料會變得很慢
優化方案:建立索引
3. 索引是什麼
索引是一種特殊的檔案(InnoDB資料表上的索引是表空間的一個組成部分),它們包含着對資料表裡所有記錄的引用指針。
更通俗的說,資料庫索引好比是一本書前面的目錄,能加快資料庫的查詢速度
4. 索引目的
索引的目的在于提高資料查詢效率
5. 索引原理(了解)
除了詞典,生活中随處可見索引的例子,如火車站的車次表、圖書的目錄等。它們的原理都是一樣的,通過不斷的縮小想要獲得資料的範圍來篩選出最終想要的結果,同時把随機的事件變成順序的事件,也就是我們總是通過同一種查找方式來鎖定資料。
資料庫也是一樣,但顯然要複雜許多,因為不僅面臨着等值查詢,還有範圍查詢(>、<、between、in)、模糊查詢(like)、并集查詢(or)等等。資料庫應該選擇怎麼樣的方式來應對所有的問題呢?我們回想字典的例子,能不能把資料分成段,然後分段查詢呢?最簡單的如果1000條資料,1到100分成第一段,101到200分成第二段,201到300分成第三段……這樣查第250條資料,隻要找第三段就可以了,一下子去除了90%的無效資料。
6. 索引的使用
- 檢視索引
show index from 表名;
- 建立索引
- 如果指定字段是字元串,需要指定長度,建議長度與定義字段時的長度一緻
- 字段類型如果不是字元串,可以不填寫長度部分
create index 索引名稱 on 表名(字段名稱(長度))
- 删除索引:
drop index 索引名稱 on 表名;
7. 索引demo
create table test_index(title varchar(10));
from pymysql import connect
def main():
# 建立Connection連接配接
conn = connect(host='localhost',port=3306,database='jing_dong',user='root',password='mysql',charset='utf8')
# 獲得Cursor對象
cursor = conn.cursor()
# 插入10萬次資料
for i in range(100000):
cursor.execute("insert into test_index values('ha-%d')" % i)
# 送出資料
conn.commit()
if __name__ == "__main__":
main()
- 開啟運作時間監測:
set profiling=1;
- 查找第1萬條資料ha-99999
select * from test_index where title='ha-99999';
- 檢視執行的時間:
show profiles;
- 為表title_index的title列建立索引:
create index title_index on test_index(title(10));
- 執行查詢語句:
select * from test_index where title='ha-99999';
- 再次檢視執行的時間
show profiles;
8. 注意:
- 索引最主要解決的問題:當資料量較大時,且這些資料不需要經常修改,使用索引來加快查詢速度
- 對于比較小的表,查詢開銷不會很大,也沒有必要建立另外的索引
- 建立太多的索引将會影響更新和插入的速度,因為它需要同樣更新每個索引檔案
- 對于一個經常需要更新和插入的表格,就沒有必要為一個很少使用的where字句單獨建立索引了
- 建立索引會占用磁盤空間