索引

1. 思考

在圖書館中是如何找到一本書的码耐？

一般的應(yīng)用系統(tǒng)對(duì)比數(shù)據(jù)庫的讀寫比例在10:1左右(即有10次查詢操作時(shí)有1次寫的操作)畴嘶，

而且插入操作和更新操作很少出現(xiàn)性能問題，

遇到最多凝垛、最容易出問題還是一些復(fù)雜的查詢操作懊悯，所以查詢語句的優(yōu)化顯然是重中之重

2. 解決辦法

當(dāng)數(shù)據(jù)庫中數(shù)據(jù)量很大時(shí)，查找數(shù)據(jù)會(huì)變得很慢

優(yōu)化方案：索引

3. 索引是什么

索引是一種特殊的文件(InnoDB數(shù)據(jù)表上的索引是表空間的一個(gè)組成部分)梦皮，它們包含著對(duì)數(shù)據(jù)表里所有記錄的引用指針炭分。

更通俗的說，數(shù)據(jù)庫索引好比是一本書前面的目錄剑肯，能加快數(shù)據(jù)庫的查詢速度

4. 索引目的

索引的目的在于提高查詢效率捧毛，可以類比字典，如果要查“mysql”這個(gè)單詞退子，我們肯定需要定位到m字母岖妄，然后從下往下找到y(tǒng)字母，再找到剩下的sql寂祥。如果沒有索引荐虐，那么你可能需要把所有單詞看一遍才能找到你想要的，如果我想找到m開頭的單詞呢丸凭？或者ze開頭的單詞呢福扬？是不是覺得如果沒有索引，這個(gè)事情根本無法完成惜犀？

5. 索引原理

除了詞典铛碑，生活中隨處可見索引的例子，如火車站的車次表虽界、圖書的目錄等汽烦。它們的原理都是一樣的，通過不斷的縮小想要獲得數(shù)據(jù)的范圍來篩選出最終想要的結(jié)果莉御，同時(shí)把隨機(jī)的事件變成順序的事件撇吞，也就是我們總是通過同一種查找方式來鎖定數(shù)據(jù)俗冻。

數(shù)據(jù)庫也是一樣，但顯然要復(fù)雜許多牍颈，因?yàn)椴粌H面臨著等值查詢迄薄，還有范圍查詢(>、<煮岁、between讥蔽、in)、模糊查詢(like)画机、并集查詢(or)等等冶伞。數(shù)據(jù)庫應(yīng)該選擇怎么樣的方式來應(yīng)對(duì)所有的問題呢？我們回想字典的例子色罚，能不能把數(shù)據(jù)分成段碰缔，然后分段查詢呢？最簡單的如果1000條數(shù)據(jù)戳护，1到100分成第一段金抡，101到200分成第二段，201到300分成第三段……這樣查第250條數(shù)據(jù)腌且，只要找第三段就可以了梗肝，一下子去除了90%的無效數(shù)據(jù)。

7178f37egw1err37xke42j20hc08caax

作用

• 約束

• 加速查找

索引種類：

• 普通索引：加速查找

• 主鍵索引：加速查找＋不能為空＋不能重復(fù)

• 唯一索引：加速查找＋不能重復(fù)

• 聯(lián)合索引（多列）： 　　　∑潭－　聯(lián)合主鍵索引 　　　∥谆鳌－　聯(lián)合唯一索引 　　　　－　聯(lián)合普通索引

主鍵索引： 　 - 創(chuàng)建主鍵時(shí)就創(chuàng)建了主鍵索引

普通索引：
    - create index 索引名稱 on 表名(列名,)
    - drop index 索引名稱 on 表名
唯一索引：
    - create unique index 索引名稱 on 表名(列名)
    - drop unique index 索引名稱 on 表名

組合索引(最左前綴匹配)：
    - create unique index 索引名稱 on 表名(列名,列名)
    - drop unique index 索引名稱 on 表名

    - create index ix_name_email on userinfo3(name,email,)
    - 最左前綴匹配
    組合索引效率 > 索引合并 
        組合索引:

        索引合并：

    名詞：
        覆蓋索引：
            - 在索引文件中直接獲取數(shù)據(jù)

        索引合并：
            - 把多個(gè)單列索引合并使用

無索引：從前到后依次查找

索引：
    索引 => 創(chuàng)建額外文件(某種格式存儲(chǔ))
   create index 索引名稱 on 表名(字段名);

索引種類（某種格式存儲(chǔ)）：

索引是在MYSQL的存儲(chǔ)引擎層中實(shí)現(xiàn)的根據(jù)搜索引擎分類：

    B-Tree 索引：最常見的索引類型精续，大部分引擎都支持B樹索引坝锰。
    HASH 索引：只有Memory引擎支持，使用場(chǎng)景簡單重付。
    R-Tree 索引(空間索引)：空間索引是MyISAM的一種特殊索引類型顷级，主要用于地理空間數(shù)據(jù)類型。
    Full-text (全文索引)：全文索引也是MyISAM的一種特殊索引類型确垫，主要用于全文索引弓颈，InnoDB從MYSQL5.6版本提供對(duì)全文索引的支持。
    hash索引： 
        單值快
        范圍(慢)
    btree索引： btree索引
        二叉樹　(一種算法實(shí)現(xiàn)的)

6. 索引的使用

• 查看索引

show index from 表名;

• 創(chuàng)建索引

1. 如果指定字段是字符串删掀，需要指定長度翔冀，建議長度與定義字段時(shí)的長度一致
2. 字段類型如果不是字符串，可以不填寫長度部分

• 創(chuàng)建表時(shí)指定索引

CREATE TABLE 表名 ( [...], INDEX 索引名 (列名1披泪，列名 2,...) );

• 創(chuàng)建表后創(chuàng)建索引

CREATE INDEX 索引名稱 ON 表名(字段名稱(長度))

• 刪除索引：

DROP INDEX 索引名稱 ON 表名;

7. 索引demo

7.1. 創(chuàng)建測(cè)試表testindex

create table test_index(title varchar(10));

7.2 使用python程序（ipython也可以）通過pymsql模塊 向表中加入十萬條數(shù)據(jù)

from pymysql import connect

def main():
    # 創(chuàng)建Connection連接
    conn = connect(host='localhost',port=3306,database='jing_dong',user='root',password='mysql',charset='utf8')
    # 獲得Cursor對(duì)象
    cursor = conn.cursor()
    # 插入10萬次數(shù)據(jù)
    for i in range(100000):
        cursor.execute("insert into test_index values('ha-%d')" % i)
    # 提交數(shù)據(jù)
    conn.commit()

if __name__ == "__main__":
    main()

7.3. 查詢

• 開啟運(yùn)行時(shí)間監(jiān)測(cè)：

set profiling=1;

• 查找第1萬條數(shù)據(jù)ha-99999

select * from test_index where title='ha-99999';

• 查看執(zhí)行的時(shí)間：

show profiles;

• 為表title_index的title列創(chuàng)建索引：

create index title_index on test_index(title(10));

• 執(zhí)行查詢語句：

select * from test_index where title='ha-99999';

• 再次查看執(zhí)行的時(shí)間

show profiles;

8. 注意：

要注意的是纤子，建立太多的索引將會(huì)影響更新和插入的速度，因?yàn)樗枰瑯痈旅總€(gè)索引文件。對(duì)于一個(gè)經(jīng)常需要更新和插入的表格控硼，就沒有必要為一個(gè)很少使用的where字句單獨(dú)建立索引了跌捆，對(duì)于比較小的表，排序的開銷不會(huì)很大象颖，也沒有必要建立另外的索引。 建立索引會(huì)占用磁盤空間

• a. 額外的文件保存特殊的數(shù)據(jù)結(jié)構(gòu)姆钉、

• b. 查詢快说订；插入更新刪除慢

• c. 命中索引　（創(chuàng)建索引要正確的使用索引）

  • like '%xx'

    select * from tb1 where email like '%cn';

  • 使用函數(shù)

    select * from tb1 where reverse(email) = 'wupeiqi';

  • or

    select * from tb1 where nid = 1 or name = 'seven@live.com';

    特別的：當(dāng)or條件中有未建立索引的列才失效潮瓶，以下會(huì)走索引 select * from tb1 where nid = 1 or name = 'seven'; select * from tb1 where nid = 1 or name = 'seven@live.com' and email = 'alex'

  • 類型不一致

    如果列是字符串類型陶冷，傳入條件是必須用引號(hào)引起來，不然...

    select * from tb1 where email = 999;

  • !=

    select * from tb1 where email != 'alex'

    特別的：如果是主鍵毯辅，則還是會(huì)走索引 select * from tb1 where nid != 123

  • select * from tb1 where email > 'alex'

    特別的：如果是主鍵或索引是整數(shù)類型埂伦，則還是會(huì)走索引 select * from tb1 where nid > 123 select * from tb1 where num > 123

  • order by

    select name from tb1 order by email desc;

    當(dāng)根據(jù)索引排序時(shí)候，選擇的映射如果不是索引思恐，則不走索引 特別的：如果對(duì)主鍵排序沾谜，則還是走索引： select * from tb1 order by nid desc;

  • 組合索引最左前綴

    如果組合索引為：(name,email) name and email -- 使用索引 name -- 使用索引 email -- 不使用索引

索引選擇原則

1. 較頻繁的作為查詢條件的字段應(yīng)該創(chuàng)建索引
2. 唯一性太差的字段不適合單獨(dú)創(chuàng)建索引，即使頻繁作為查詢條件
3. 更新非常頻繁的字段不適合創(chuàng)建索引
4. 不會(huì)出現(xiàn)在 WHERE 子句中的字段不該創(chuàng)建索引

性能優(yōu)化過程中胀莹，選擇在哪個(gè)列上創(chuàng)建索引是最非常重要的基跑。可以考慮使用索引的主要有 兩種類型的列：在where子句中出現(xiàn)的列描焰，在join子句中出現(xiàn)的列媳否，而不是在SELECT關(guān)鍵字后選擇列表的列；

一般兩種情況下不建議建索引： 表記錄比較少荆秦，例如一兩千條甚至只有幾百條記錄的表篱竭，沒必要建索引，讓查詢做全表掃描就好了;

至于多少條記錄才算多步绸，這個(gè)個(gè)人有個(gè)人的看法掺逼，我個(gè)人的經(jīng)驗(yàn)是以5000作為分界線，記錄數(shù)不超過 5000可以考慮不建索引靡努，超過5000條可以酌情考慮索引坪圾。

最后再次強(qiáng)調(diào)： 不要過度索引，只保持所需的索引惑朦。每個(gè)額外的索引都要占用額外的磁盤空間兽泄，并降低寫操作的性能。 在修改表的內(nèi)容時(shí)漾月，索引必須進(jìn)行更新病梢，有時(shí)可能需要重構(gòu)，因此，索引越多蜓陌，所花的時(shí)間越長

總結(jié)一些其他注意事項(xiàng)： 其他注意事項(xiàng)

• 避免使用select *

• count(1)或count(列) 代替 count(*)

• 創(chuàng)建表時(shí)盡量時(shí) char 代替 varchar

• 表的字段順序固定長度的字段優(yōu)先

• 組合索引代替多個(gè)單列索引（經(jīng)常使用多個(gè)條件查詢時(shí)）

• 盡量使用短索引

• 使用連接（JOIN）來代替子查詢(Sub-Queries)

• 連表時(shí)注意條件類型需一致

• 索引散列值（重復(fù)少的列做索引）不適合建索引觅彰，例：性別不適合

    # -*- coding:utf8 -*-
    import pymysql as c
    from faker import Faker
    import random
    import sys
    import datetime
  
    #創(chuàng)建一個(gè)用戶表
    #create table usersinfo( use_id int auto_increment, name varchar(20) not null, gender char(5) not null, age int, brithday char(10), id_type varchar(15) default '身份證', id_card char(18), phone char(11), email varchar(30), native_place varchar(60), address varchar(255), join_time char(10), hobby text, primary key(use_id) );
  
    mysqlConn = c.connect(user='root',password="ljh1314",database='class1804')
    cursor = mysqlConn.cursor()
  
    fake = Faker("zh_CN")
  
    def get_native_place(address, key="縣市"):
  
        return [address[:address.index(k)+1] for k in key if k in address][0]
  
    def gen_stu_obj():
        #隨機(jī)生產(chǎn)一個(gè)地址
        address = fake.address()
        #從地址里面截取
        native_place = get_native_place(address)
        #隨機(jī)生成一個(gè)出生日期
        brithday = fake.date_of_birth(tzinfo=None, minimum_age=20, maximum_age=30)
        #隨機(jī)生成一個(gè)郵箱
        email = fake.ascii_free_email()
        #隨機(jī)生辰一個(gè)姓名
        name = fake.name()
        #隨機(jī)生成一個(gè)電話號(hào)碼
        phone = fake.phone_number()
        id_type = "身份證"
        #隨機(jī)生成一個(gè)年齡
        age = random.randint(20,30)
        #隨機(jī)產(chǎn)生一個(gè)身份證號(hào)
        id_card = fake.ssn(min_age=20, max_age=30)
        #產(chǎn)生一個(gè)時(shí)間
        join_time = fake.date_between(start_date="-2y", end_date="today")
        #生成一個(gè)座右銘
        hobby = fake.sentence(nb_words=6, variable_nb_words=True, ext_word_list=None)
        #隨機(jī)篩選男或者女
        gender = random.choice(["男","女"])
        # print(address,native_place,birthday,email,name,phone,age,id_code,join_time,hobby,sex)
  
        sql = 'insert into usersinfo(use_id,name,gender,age,brithday,id_type,id_card,phone,email,' \
        'native_place,address,join_time,hobby) values (%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s)'
        cursor.execute(sql, (
        None, name, gender, age, brithday, id_type, id_card, phone, email, native_place, address, join_time, hobby,))
  
        mysqlConn.commit()
  
    if __name__ == "__main__":
  
        a=datetime.datetime.now()
        gen_stu_obj()
        # for _ in range(100000):
            # gen_stu_obj()
        b=datetime.datetime.now()
        # print(b-a)