“ 索引基本知識*哈希索引*組合索引*聚簇索引與非聚簇索引*覆蓋索引*索引優化*索引監控*優化案例”

??? 索引這個東西，個人的感覺是：平時大家都不怎么重視他，感覺哪個查詢慢了就對那個列創建索引或者多個列創建聯合索引，再分別創建單個。這是平時圖省事的時候就這么干，但是很少去看看到底是否使用了索引亦或是執行計劃的處于什么等級，總之，對于索引，一直很模糊，是那種不怎么會用的類型。另外，本篇內容較多，建議收藏后又用的時候拿出來看，不要硬記。

01

—

索引基本知識

先上MySQL對索引和優化的鏈接：

https://dev.mysql.com/doc/refman/8.0/en/optimization-indexes.html

注：

此處B-樹指的是B+樹。Innodb和Myisam索引用的是B+樹數據結構，而memory索引用的是Hash表數據結構。

記錄一個可以動態看數據結構的網站：

https://www.cs.usfca.edu/~galles/visualization/Algorithms.html

里面可以動態看數據結構，還可以看一些排序算法的動態過程。

*為什么MySQL選擇B+樹而不是其他的數據結構？

1.如果選用Hash表：

主要還是在于不能范圍查詢，Memory引擎用的這個。

2.如果選用二叉樹，平衡樹或者紅黑樹(紅黑樹是平衡樹的變種)

這幾個樹都因為只能分兩個叉所以造成樹深很大，而每有一層就多一次IO，搜索時間會變慢，所以這些樹都不合適。

3.如果選用B樹

B樹最大的問題就是在非葉子節點存儲了數據，而數據會占用空間，導致每個磁盤可以記錄的點就少了，也導致了樹的深度變深，而B+樹就是在B樹上進行了優化，只在葉子結點存儲數據。

B+樹

另外，雖然Innodb和Myisam用的都是B+樹，但是葉子結點存儲的數據內容不同，Innodb存儲數據而Myisam存儲的是存儲數據文件的磁盤位置(意思是Myisam找到葉子結點后還要去磁盤找文件讀數據)。

*索引的優點

1：大大減少了服務器需要掃描的數據量(減少IO量)。

2：幫助服務器避免排序和臨時表。(索引本身就是有序的，我們可以利用這個特點排序)

3：將隨機io變成順序io。

*索引的用處

1：快速查找匹配WHERE子句的行。(建議把索引列作為條件列)

2：從consideration中消除行,如果可以在多個索引之間進行選擇，mysql通常會使用找到最少行的索引。(執行計劃里posiibleKey有多個，但最后實際使用的Key只有一個)

3：如果表具有多列索引，則優化器可以使用索引的任何最左前綴來查找行。

4：當有表連接的時候，從其他表檢索行數據。

5：查找特定索引列的min或max值。

6：如果排序或分組時在可用索引的最左前綴上完成的，則對表進行排序和分組。

7：在某些情況下，可以優化查詢以檢索值而無需查詢數據行。

*索引分類

1：主鍵索引(唯一且非空)

2：唯一索引(數據庫建索引是給唯一鍵建的，而主鍵唯一且非空，所以我們通常看到數據庫會給主鍵建索引。)

3：普通索引(普通列)

4：全文索引(varchar,char,text，一般用的少)

5：組合索引(多列組合成一個索引)

*技術名詞(重點，了解了和面試官就有話說，不了解直接GG)

1：回表

????為普通列創建索引時，普通列索引里面存的是主鍵。如果使用select *

比如 select * from name = “pw”,其中name被設定為普通索引，這時候會找到"pw"葉子結點的主鍵然后用這個主鍵去再次查詢所有值。但是使用select name from name = "pw"則不會回表。這也是公司不推薦使用select *的原因之一。

2：覆蓋索引

????? 同上select id from name = “pw”,直接找到了主鍵，不需要回表。

3：最左匹配

?? 對于組合索引而有的概念，比如對"name,age"創建組合索引，select * from name =?,會使用索引，但是 select * from age =?則不會使用索引，因為跨過了name,這就是最左匹配原則。

????? Q:既想name有索引，又想age有索引如何優化？

????? A:可以給“name,age”加組合索引，再給age單獨加索引，這樣加的好處是，一般來說name肯定是比age的數據更長，消耗的空間也更多，所以把name放在組合索引里再把age單獨列為索引保證了效率也節省了空間。

4：謂詞下推

select t1.name ,t2.name from t1 join t2 on t1.id =t2.id,這句話用兩種執行方式：

??? 1.先把t1和t2表作笛卡爾積，再把t1.id=t2.id的篩選出來。(計算量大)

??? 2.先把t1.name,t1.id和t2.name,t2.id取出來，再把t1.id=t2.id進行關聯(計算量小)。

5：索引下推

???? 組合索引(name,age)

????? select * from t where name = "pw" and age ="27";

????? 老版本：把name ="pw"的值從存儲引擎取出來，然后在server層過濾。

?????索引下推：在存儲引擎獲取數據時，直接就把name和age取出來然后傳給server。

*索引采用的數據結構

哈希表：Memory引擎

B+樹 ：Innodb引擎，Myisam引擎

*索引的匹配方式(重點)

這個我們用mysql官方提供的數據庫以及數據來測試。

https://dev.mysql.com/doc/index-other.html

下載下來，把壓縮包內的這兩個文件丟到linux里用mysql source

好的，這樣就省的我們自己去造一些數據了。

在附上各表之間的關系圖：

https://dev.mysql.com/doc/sakila/en/sakila-structure.html

# sakila數據庫說明ZIP格式：http://downloads.mysql.com/docs/sakila-db.ziptar格式 http://downloads.mysql.com/docs/sakila-db.tar.gz官方文檔 [http://dev.mysql.com/doc/sakila/en/index.html](https://dev.mysql.com/doc/sakila/en/index.html)解壓后得到三個文件：1. sakila-schema.sql 文件包含創建Sakila數據庫的結構：表、視圖、存儲過程和觸發器2. sakila-data.sql文件包含：使用 INSERT語句填充數據及在初始數據加載后，必須創建的觸發器的定義3. sakila.mwb文件是一個MySQL Workbench數據模型，可以在MySQL的工作臺打開查看數據庫結構。```sql--登錄mysqlmysql -uroot -p123456--導入表的結構數據source /root/sakila-schema.sql--導入表的數據source /root/sakila-data.sql```##### 梳理各個表的名稱和字段名稱1、actor：演員表，演員表列出了所有演員的信息。演員表和電影表之間是多對多的關系，通過film_actor表建立關系 actor_id:代理主鍵，用于唯一標識表中的每個演員 first_name: 演員的名字 last_name: 演員的姓氏 last_update: 該行已創建或最近更新的時間2、address:地址表，地址表包含客戶、員工和商店的地址信息。地址表的主鍵出現在顧客、 員工、和存儲表的外鍵 address_id: 代理主鍵用于唯一標識表中的每個地址 address: 地址的第一行 address2: 一個可選的第二行地址 district: 該地區的所屬地區，這可以是國家，省，縣等 city_id: 指向城市表的外鍵 postal_code: 郵政編碼 phone: 地址的電話號碼 last_update: 該行已創建或最近更新的時間3、category：分類表，類別表列出了可以分配到一個電影類別。分類和電影是多對多的關系，通過表film_category建立關系 category_id: 代理主鍵用于唯一標識表中的每個類別 name: 類別名稱 last_update: 該行已創建或最近更新的時間4、city:城市表，城市表包含的城市名單。城市表使用外鍵來標示國家；在地址表中被作為外鍵來使用。 city_id: 代理主鍵用于唯一標識表中的每個城市 city: 城市的名字 country_id: 外鍵，用于標示城市所屬的國家 last_update: 該行已創建或最近更新的時間5、country：國家表，國家表中包含的國家名單。國家表是指在城市表的外鍵 。 country_id: 代理主鍵用于唯一標識表中的每個國家 country: 國家的名稱 last_update: 該行已創建或最近更新的時間6、customer：客戶表，客戶表包含了所有客戶的列表 。客戶表在支付表和租金表被作為外鍵使用；客戶表使用外鍵來表示地址和存儲。 customer_id: 代理主鍵用于唯一標識表中的每個客戶 store_id: 一個外鍵，確定客戶所屬的store。 first_name: 客戶的名字 last_name: 客戶的姓氏 email: 客戶的電子郵件地址 address_id: 使用在地址 表的外鍵來確定客戶的地址 active: 表示客戶是否是活躍的客戶 create_date: 顧客被添加到系統中的日期。使用 INSERT 觸發器自動設置。 last_update: 該行已創建或最近更新的時間7、film:電影表，電影表是一個可能在商店庫存的所有影片名單。每部影片的拷貝的實際庫存信息保存在庫存表。電影表指使用外鍵來標示語言表；在film_category、film_actor和庫存表中作為外鍵使用。 film_id: 代理主鍵用于唯一標識表中的每個電影 title: 影片的標題 description: 一個簡短的描述或電影的情節摘要 release_year: 電影發行的年份 language_id: 使用外鍵來標示語言 original_language_id: 電影的原始語音。使用外鍵來標示語言 rental_duration: 租賃期限的長短，以天作為單位 rental_rate: 指定的期限內電影的租金 length: 影片的長度，以分鐘為單位。 replacement_cost: 如果電影未被歸還或損壞狀態向客戶收取的款項 rating: 分配給電影評級。可以是 G， PG，PG - 13 ， R 或NC - 17 special_features: 包括DVD上常見的特殊功能的列表 last_update: 該行已創建或最近更新的時間8、film_actor:電影演員表，film_actor表是用來支持許多電影和演員之間的多對多關系 。對于每一個給定的電影演員，將有film_actor表中列出的演員和電影中的一個行 。 actor_id: 用于識別演員的外鍵 film_id: 用于識別電影的外鍵 last_update: 該行已創建或最近更新的時間9、film_category:電影類別表，film_category表是用來支持許多電影和類別之間的多對多關系 。應用于電影的每個類別中，將有film_category表中列出的類別和電影中的一個行。 film_id: 用于識別電影的外鍵 category_id: 用于識別類別的外鍵 last_update: 該行已創建或最近更新的時間10、film_text:電影信息表，film_text表是Sakila樣例數據庫唯一使用MyISAM存儲引擎的表。MyISAM類型不支持事務處理等高級處理，而InnoDB類型支持。MyISAM類型的表強調的是性能，其執行數度比InnoDB類型更快。此表提供允許全文搜索電影表中列出的影片的標題和描述。film_text表包含的film_id，標題和描述的列電影表，保存的內容與電影表上的內容同步(指電影表的插入、更新和刪除操作) film_id: 代理主鍵用于唯一標識表中的每個電影 title: 影片的標題 description: 一個簡短的描述或電影的情節摘要11、inventory:庫存表，庫存表的一行為存放在一個給定的商店里的一個給定的電影的copy副本。庫存表是使用外鍵來識別電影和存儲；在出租表中使用外鍵來識別庫存。 inventory_id: 理主鍵用于唯一標識每個項目在庫存 film_id: 使用外鍵來識別電影 store_id: 使用外鍵來識別物品所在的商店 last_update: 該行已創建或最近更新的時間12、language:語言表，語言表是一個查找表，列出可能使用的語言，電影可以有自己的語言和原始語言值 。語言表在電壓表中被作為外鍵來使用。 language_id: 代理主鍵用于唯一標識每一種語言 name: 語言的英文名稱 last_update: 該行已創建或最近更新的時間13、payment：付款表，付款表記錄每個客戶的付款，如支付的金額和租金的資料。付款表使用外鍵來表示客戶、出租、和工作人員。 payment_id: 代理主鍵用于唯一標識每個付款 customer_id: 使用外鍵來標識付款的客戶 staff_id: 工作人員，負責處理支付 。使用外鍵來標識 rental_id: 租借ID, 外鍵，參照rental表 amount: 付款金額 payment_date: 處理付款的日期 last_update: 該行已創建或最近更新的時間14 、rental：租金表，租借表的一行表示每個inventory的租借客戶、租借時間、歸還時間租借表是使用外鍵來標識庫存 ，顧客 和工作人員；在支付表中使用了外鍵來標識租金 。 rental_id: 代理主鍵唯一標識的租金 rental_date: 該項目租用的日期和時間 inventory_id: 該項目被租用 customer_id: 租用該項目的客戶 return_date: 歸還日期 staff_id: 處理該項業務的工作人員 last_update: 該行已創建或最近更新的時間15、staff：工作人員表，工作人員表列出了所有的工作人員，包括電子郵件地址，登錄信息和圖片信息 。工作人員表是指使用外鍵來標識存儲和地址表；在出租、支付和商店表中作為外鍵。 staff_id: 代理主鍵唯一標識的工作人員 first_name: 工作人員的名字 last_name: 工作人員的姓氏 address_id: 工作人員的地址在地址表的外鍵 picture: 工作人員的照片，使用了 BLOB屬性 email: 工作人員的電子郵件地址 store_id: 工作人員所在的商店，用外鍵標識 active: 是否是活躍的工作人員。 username: 用戶名，由工作人員用來訪問租賃系統 password: 工作人員訪問租賃系統所使用的密碼。使用了 SHA1 函數 last_update: 該行已創建或最近更新的時間 active: 是否有效，刪除時設置為False 16、store：商店表，store表列出了系統中的所有商店 。store使用外鍵來標識工作人員和地址；在員工、客戶、庫存表被作為外鍵使用。 store_id: 代理主鍵唯一標識的商店 manager_staff_id: 使用外鍵來標識這家商店的經理 address_id: 使用外鍵來確定這家店的地址 last_update: 該行已創建或最近更新的時間##### 視圖表1、actor_info視圖提供了所有演員的列表及所演的電影, 電影按category分組.```sqlSELECTa.actor_id,a.first_name,a.last_name,GROUP_CONCAT(DISTINCT CONCAT(c.name, ‘: ‘, (SELECT GROUP_CONCAT(f.title ORDER BY f.title SEPARATOR ‘, ‘) FROM sakila.film f INNER JOIN sakila.film_category fc ON f.film_id = fc.film_id INNER JOIN sakila.film_actor fa ON f.film_id = fa.film_id WHERE fc.category_id = c.category_id AND fa.actor_id = a.actor_id ) ) ORDER BY c.name SEPARATOR ‘; ‘)AS film_infoFROM sakila.actor aLEFT JOIN sakila.film_actor fa ON a.actor_id = fa.actor_idLEFT JOIN sakila.film_category fc ON fa.film_id = fc.film_idLEFT JOIN sakila.category c ON fc.category_id = c.category_idGROUP BY a.actor_id, a.first_name, a.last_name```2、customer_list：客戶列表，firstname和lastname連接成fullname，將address, city, country 集成在一個視圖里```sqlSELECT cu.customer_id AS ID, CONCAT( cu.first_name, _utf8 ‘ ‘, cu.last_name ) AS NAME, a.address AS address, a.postal_code AS `zip code`, a.phone AS phone, city.city AS city, country.country AS country,IF ( cu.active, _utf8 ‘active‘, _utf8 ‘‘) AS notes, cu.store_id AS SIDFROM customer AS cuJOIN address AS a ON cu.address_id = a.address_idJOIN city ON a.city_id = city.city_idJOIN country ON city.country_id = country.country_id```3、film_list:電影列表視圖，包含了每一部電影的信息及電影所對應的演員。電影對應的演員以逗號作為分隔符。連接了 film, film_category, category,film_actor and actor 表的數據```sqlSELECT film.film_id AS FID, film.title AS title, film.description AS description, category. NAME AS category, film.rental_rate AS price, film.length AS length, film.rating AS rating, GROUP_CONCAT( CONCAT( actor.first_name, _utf8 ‘ ‘, actor.last_name ) SEPARATOR ‘, ‘ ) AS actorsFROM categoryLEFT JOIN film_category ON category.category_id = film_category.category_idLEFT JOIN film ON film_category.film_id = film.film_idJOIN film_actor ON film.film_id = film_actor.film_idJOIN actor ON film_actor.actor_id = actor.actor_idGROUP BY film.film_id```4、nicer_but_slower_film_list:電影列表視圖，包含了每一部電影的信息及電影所對應的演員。電影對應的演員以逗號作為分隔符。連接了 film, film_category, category,film_actor` and `actor 表的數據。和The film_list View不同，演員名字只有單詞首字母大寫了。```sqlSELECT film.film_id AS FID, film.title AS title, film.description AS description, category. NAME AS category, film.rental_rate AS price, film.length AS length, film.rating AS rating, GROUP_CONCAT( CONCAT( CONCAT( UCASE( SUBSTR(actor.first_name, 1, 1) ), LCASE( SUBSTR( actor.first_name, 2, LENGTH(actor.first_name) ) ), _utf8 ‘ ‘, CONCAT( UCASE( SUBSTR(actor.last_name, 1, 1) ), LCASE( SUBSTR( actor.last_name, 2, LENGTH(actor.last_name) ) ) ) ) ) SEPARATOR ‘, ‘ ) AS actorsFROM categoryLEFT JOIN film_category ON category.category_id = film_category.category_idLEFT JOIN film ON film_category.film_id = film.film_idJOIN film_actor ON film.film_id = film_actor.film_idJOIN actor ON film_actor.actor_id = actor.actor_idGROUP BY film.film_id```5、sales_by_film_category:每個電影種類的銷售額 , payment →` `rental →inventory → film → film_category → category```sqlSELECTc.name AS category, SUM(p.amount) AS total_salesFROM payment AS pINNER JOIN rental AS r ON p.rental_id = r.rental_idINNER JOIN inventory AS i ON r.inventory_id = i.inventory_idINNER JOIN film AS f ON i.film_id = f.film_idINNER JOIN film_category AS fc ON f.film_id = fc.film_idINNER JOIN category AS c ON fc.category_id = c.category_idGROUP BY c.nameORDER BY total_sales DESC```6、sales_by_store:每個商店的manager及銷售額。payment → rental → inventory → store → staff```sqlSELECTCONCAT(c.city, _utf8‘,‘, cy.country) AS store, CONCAT(m.first_name, _utf8‘ ‘, m.last_name) AS manager, SUM(p.amount) AS total_salesFROM payment AS pINNER JOIN rental AS r ON p.rental_id = r.rental_idINNER JOIN inventory AS i ON r.inventory_id = i.inventory_idINNER JOIN store AS s ON i.store_id = s.store_idINNER JOIN address AS a ON s.address_id = a.address_idINNER JOIN city AS c ON a.city_id = c.city_idINNER JOIN country AS cy ON c.country_id = cy.country_idINNER JOIN staff AS m ON s.manager_staff_id = m.staff_idGROUP BY s.store_idORDER BY cy.country, c.city```7、staff_list:工作人員的列表```sqlSELECT s.staff_id AS ID, CONCAT( s.first_name, _utf8 ‘ ‘, s.last_name ) AS NAME, a.address AS address, a.postal_code AS `zip code`, a.phone AS phone, city.city AS city, country.country AS country, s.store_id AS SIDFROM staff AS sJOIN address AS a ON s.address_id = a.address_idJOIN city ON a.city_id = city.city_idJOIN country ON city.country_id = country.country_id```各個表的結構關系：

當然我們這里只用簡單的幾張表。

1：全值匹配

全值匹配指的是和索引中的所有列進行匹配

show index from staff;

staff有索引staff_id,store_id,address_id

explain select * from staff where store_id = 1 and address_id = 3;

這里先看type為ref前面一篇有說到type可以代表語句的執行效率；

system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL

我們只需要記住6種:system>const>ref>range>index>ALL

2：匹配最左前綴

只匹配前面的幾列

3：匹配列前綴

可以匹配某一列的值的開頭部分(> x%等，注意百分號不能%x,否則無法使用索引)

4：匹配范圍值

可以查找某一個范圍的數據

5.精確匹配某一列并范圍匹配另外一列

可以查詢第一列的全部和第二列的部分

6.只訪問索引的查詢

查詢的時候只需要訪問索引，不需要訪問數據行，本質上就是覆蓋索引

可以看到搜索條件指定成了索引列，這就是覆蓋索引，覆蓋索引現象出現的標志是Extra那里出現的Using index。

02

—

哈希索引(了解即可)

1：基于哈希表的實現，只有精確匹配索引所有列的查詢才有效。
2：在mysql中，只有memory的存儲引擎顯式支持哈希索引

3：哈希索引自身只需存儲對應的hash值，所以索引的結構十分緊湊，這讓哈希索引查找的速度非常快。

4：哈希索引的限制

1)哈希索引只包含哈希值和行指針，而不存儲字段值，索引不能使用索引中的值來避免讀取行

2)哈希索引數據并不是按照索引值順序存儲的，所以無法進行排序

3)哈希索引不支持部分列匹配查找，哈希索引是使用索引列的全部內容來計算哈希值

4)哈希索引支持等值比較查詢，也不支持任何范圍查詢

5)訪問哈希索引的數據非常快，除非有很多哈希沖突，當出現哈希沖突的時候，存儲引擎必須遍歷鏈表中的所有行指針，逐行進行比較，直到找到所有符合條件的行

6)哈希沖突比較多的話，維護的代價也會很高

案例

當需要存儲大量的URL，并且根據URL進行搜索查找，如果使用B+樹，存儲的內容就會很大

select id from url where url=""

也可以利用將url使用CRC32做哈希，可以使用以下查詢方式：

select id fom url where url="" and url_crc=CRC32("")

此查詢性能較高原因是使用體積很小的索引來完成查找

03

—

組合索引

當包含多個列作為索引，需要注意的是正確的順序依賴于該索引的查詢，同時需要考慮如何更好的滿足排序和分組的需要。

案例，建立組合索引a,b,c

04

—

聚簇索引與非聚簇索引

聚簇索引

不是單獨的索引類型，而是一種數據存儲方式，指的是數據行跟相鄰的鍵值緊湊的存儲在一起

優缺點：

非聚簇索引

數據文件跟索引文件分開存放

05

—

覆蓋索引

# 覆蓋索引1、當發起一個被索引覆蓋的查詢時，在explain的extra列可以看到using index的信息，此時就使用了覆蓋索引```sqlmysql> explain select store_id,film_id from inventory\G*************************** 1. row *************************** id: 1 select_type: SIMPLE table: inventory partitions: NULL type: indexpossible_keys: NULL key: idx_store_id_film_id key_len: 3 ref: NULL rows: 4581 filtered: 100.00 Extra: Using index1 row in set, 1 warning (0.01 sec)```2、在大多數存儲引擎中，覆蓋索引只能覆蓋那些只訪問索引中部分列的查詢。不過，可以進一步的進行優化，可以使用innodb的二級索引來覆蓋查詢。例如：actor使用innodb存儲引擎，并在last_name字段又二級索引，雖然該索引的列不包括主鍵actor_id，但也能夠用于對actor_id做覆蓋查詢```sqlmysql> explain select actor_id,last_name from actor where last_name='HOPPER'\G*************************** 1. row *************************** id: 1 select_type: SIMPLE table: actor partitions: NULL type: refpossible_keys: idx_actor_last_name key: idx_actor_last_name key_len: 137 ref: const rows: 2 filtered: 100.00 Extra: Using index1?row?in?set,?1?warning?(0.00?sec)

06

—

索引優化(重點)

這里是面試里用到較多的，問你平時咋優化索引，用的例子還是sakila里面的。

1：當使用索引列進行查詢的時候盡量不要使用表達式，把計算放到業務層而不是數據庫層。

????select actor_id from actor where actor_id=4;

????select actor_id from actor where actor_id+1=5;

type:const>index,可見表達式影響效率。

2.盡量使用主鍵查詢，而不是其他索引，因此主鍵查詢不會觸發回表查詢。

3.使用前綴索引。

# 前綴索引實例說明 有時候需要索引很長的字符串，這會讓索引變的大且慢，通常情況下可以使用某個列開始的部分字符串，這樣大大的節約索引空間，從而提高索引效率，但這會降低索引的選擇性，索引的選擇性是指不重復的索引值和數據表記錄總數的比值，范圍從1/#T到1之間。索引的選擇性越高則查詢效率越高，因為選擇性更高的索引可以讓mysql在查找的時候過濾掉更多的行。 一般情況下某個列前綴的選擇性也是足夠高的，足以滿足查詢的性能，但是對應BLOB,TEXT,VARCHAR類型的列，必須要使用前綴索引，因為mysql不允許索引這些列的完整長度，使用該方法的訣竅在于要選擇足夠長的前綴以保證較高的選擇性，通過又不能太長。案例演示：```sql--創建數據表create table citydemo(city varchar(50) not null);insert into citydemo(city) select city from city;--重復執行5次下面的sql語句insert into citydemo(city) select city from citydemo;--更新城市表的名稱update citydemo set city=(select city from city order by rand() limit 1);--查找最常見的城市列表，發現每個值都出現45-65次，select count(*) as cnt,city from citydemo group by city order by cnt desc limit 10;--查找最頻繁出現的城市前綴，先從3個前綴字母開始，發現比原來出現的次數更多，可以分別截取多個字符查看城市出現的次數select count(*) as cnt,left(city,3) as pref from citydemo group by pref order by cnt desc limit 10;select count(*) as cnt,left(city,7) as pref from citydemo group by pref order by cnt desc limit 10;--此時前綴的選擇性接近于完整列的選擇性--還可以通過另外一種方式來計算完整列的選擇性，可以看到當前綴長度到達7之后，再增加前綴長度，選擇性提升的幅度已經很小了select count(distinct left(city,3))/count(*) as sel3,count(distinct left(city,4))/count(*) as sel4,count(distinct left(city,5))/count(*) as sel5,count(distinct left(city,6))/count(*) as sel6,count(distinct left(city,7))/count(*) as sel7,count(distinct left(city,8))/count(*) as sel8 from citydemo;--計算完成之后可以創建前綴索引alter table citydemo add key(city(7));--注意：前綴索引是一種能使索引更小更快的有效方法，但是也包含缺點：mysql無法使用前綴索引做order by 和 group by。```

cardinanity(hyperLogLog算法獲取)意思是基數，表示該索引不重復的值，PLAP(歷史數據分析)中的基準值。

4.使用索引掃描來排序

# 使用索引掃描來做排序 mysql有兩種方式可以生成有序的結果：通過排序操作或者按索引順序掃描，如果explain出來的type列的值為index,則說明mysql使用了索引掃描來做排序 掃描索引本身是很快的，因為只需要從一條索引記錄移動到緊接著的下一條記錄。但如果索引不能覆蓋查詢所需的全部列，那么就不得不每掃描一條索引記錄就得回表查詢一次對應的行，這基本都是隨機IO，因此按索引順序讀取數據的速度通常要比順序地全表掃描慢 mysql可以使用同一個索引即滿足排序，又用于查找行，如果可能的話，設計索引時應該盡可能地同時滿足這兩種任務。 只有當索引的列順序和order by子句的順序完全一致，并且所有列的排序方式都一樣時，mysql才能夠使用索引來對結果進行排序，如果查詢需要關聯多張表，則只有當orderby子句引用的字段全部為第一張表時，才能使用索引做排序。order by子句和查找型查詢的限制是一樣的，需要滿足索引的最左前綴的要求，否則，mysql都需要執行順序操作，而無法利用索引排序```sql--sakila數據庫中rental表在rental_date,inventory_id,customer_id上有rental_date的索引--使用rental_date索引為下面的查詢做排序explain select rental_id,staff_id from rental where rental_date='2005-05-25' order by inventory_id,customer_id\G*************************** 1. row *************************** id: 1 select_type: SIMPLE table: rental partitions: NULL type: refpossible_keys: rental_date key: rental_date key_len: 5 ref: const rows: 1 filtered: 100.00 Extra: Using index condition1 row in set, 1 warning (0.00 sec)--order by子句不滿足索引的最左前綴的要求，也可以用于查詢排序，這是因為所以你的第一列被指定為一個常數--該查詢為索引的第一列提供了常量條件，而使用第二列進行排序，將兩個列組合在一起，就形成了索引的最左前綴explain select rental_id,staff_id from rental where rental_date='2005-05-25' order by inventory_id desc\G*************************** 1. row *************************** id: 1 select_type: SIMPLE table: rental partitions: NULL type: refpossible_keys: rental_date key: rental_date key_len: 5 ref: const rows: 1 filtered: 100.00 Extra: Using where1 row in set, 1 warning (0.00 sec)--下面的查詢不會利用索引explain select rental_id,staff_id from rental where rental_date>'2005-05-25' order by rental_date,inventory_id\G*************************** 1. row *************************** id: 1 select_type: SIMPLE table: rental partitions: NULL type: ALLpossible_keys: rental_date key: NULL key_len: NULL ref: NULL rows: 16005 filtered: 50.00 Extra: Using where; Using filesort--該查詢使用了兩中不同的排序方向，但是索引列都是正序排序的explain select rental_id,staff_id from rental where rental_date>'2005-05-25' order by inventory_id desc,customer_id asc\G*************************** 1. row *************************** id: 1 select_type: SIMPLE table: rental partitions: NULL type: ALLpossible_keys: rental_date key: NULL key_len: NULL ref: NULL rows: 16005 filtered: 50.00 Extra: Using where; Using filesort1 row in set, 1 warning (0.00 sec)--該查詢中引用了一個不再索引中的列explain select rental_id,staff_id from rental where rental_date>'2005-05-25' order by inventory_id,staff_id\G*************************** 1. row *************************** id: 1 select_type: SIMPLE table: rental partitions: NULL type: ALLpossible_keys: rental_date key: NULL key_len: NULL ref: NULL rows: 16005 filtered: 50.00 Extra: Using where; Using filesort1 row in set, 1 warning (0.00 sec)```

簡單來說orderby的時候如果出現了Using filesort，Using index condition，那么就沒有使用索引排序。

5.union all,in,or都能夠使用索引，但是推薦使用in

in的效率略高。

6.范圍列可以用到索引

范圍條件是：、>=、between

范圍列可以用到索引，但是范圍列后面的列無法用到索引，索引最多用于一個范圍列

7.強制類型轉換會全表掃描

create table user(id int,name varchar(10),phone varchar(11));

alter table user add index idx_1(phone);

8.更新十分頻繁，數據區分度不高的字段上不宜建立索引

9.創建索引的列，不允許為null，可能會得到不符合預期的結果。

10.當需要進行表連接的時候，最好不要超過三張表，因為需要join的字段，數據類型必須一致。

11.能使用limit的時候盡量使用limit(limit 代表限制，當確定取到的值是固定行時，盡量使用limit來避免多余的查找)

12.單表索引建議控制在5個以內

13.單索引字段數不允許超過5個(組合索引)

14.創建索引的時候應該避免以下錯誤概念(索引越多越好，過早優化，在不了解系統的情況下進行優化)

07

—

索引監控

????命令行為：

????show status like 'Handler_read%';

????解釋：

我們在新建一個索引之后可以跑代碼測試建立索引的使用率，已評估索引建的好不好，其中看，Handler_read_key，Handler_read_rnd_next兩個值，值高說明頻率高索引好，相反則不好。

索引優化案例：

#?索引優化分析案例預先準備好數據```sqlSET FOREIGN_KEY_CHECKS=0;DROP TABLE IF EXISTS `itdragon_order_list`;CREATE TABLE `itdragon_order_list` ( `id` bigint(11) NOT NULL AUTO_INCREMENT COMMENT '主鍵id，默認自增長', `transaction_id` varchar(150) DEFAULT NULL COMMENT '交易號', `gross` double DEFAULT NULL COMMENT '毛收入(RMB)', `net` double DEFAULT NULL COMMENT '凈收入(RMB)', `stock_id` int(11) DEFAULT NULL COMMENT '發貨倉庫', `order_status` int(11) DEFAULT NULL COMMENT '訂單狀態', `descript` varchar(255) DEFAULT NULL COMMENT '客服備注', `finance_descript` varchar(255) DEFAULT NULL COMMENT '財務備注', `create_type` varchar(100) DEFAULT NULL COMMENT '創建類型', `order_level` int(11) DEFAULT NULL COMMENT '訂單級別', `input_user` varchar(20) DEFAULT NULL COMMENT '錄入人', `input_date` varchar(20) DEFAULT NULL COMMENT '錄入時間', PRIMARY KEY (`id`)) ENGINE=InnoDB AUTO_INCREMENT=10003 DEFAULT CHARSET=utf8;INSERT INTO itdragon_order_list VALUES ('10000', '81X97310V32236260E', '6.6', '6.13', '1', '10', 'ok', 'ok', 'auto', '1', 'itdragon', '2017-08-28 17:01:49');INSERT INTO itdragon_order_list VALUES ('10001', '61525478BB371361Q', '18.88', '18.79', '1', '10', 'ok', 'ok', 'auto', '1', 'itdragon', '2017-08-18 17:01:50');INSERT INTO itdragon_order_list VALUES ('10002', '5RT64180WE555861V', '20.18', '20.17', '1', '10', 'ok', 'ok', 'auto', '1', 'itdragon', '2017-09-08 17:01:49');```逐步開始進行優化：第一個案例：```sqlselect * from itdragon_order_list where transaction_id = "81X97310V32236260E";--通過查看執行計劃發現type=all,需要進行全表掃描explain select * from itdragon_order_list where transaction_id = "81X97310V32236260E";--優化一、為transaction_id創建唯一索引 create unique index idx_order_transaID on itdragon_order_list (transaction_id);--當創建索引之后，唯一索引對應的type是const，通過索引一次就可以找到結果，普通索引對應的type是ref，表示非唯一性索引賽秒，找到值還要進行掃描，直到將索引文件掃描完為止，顯而易見，const的性能要高于ref explain select * from itdragon_order_list where transaction_id = "81X97310V32236260E"; --優化二、使用覆蓋索引，查詢的結果變成 transaction_id,當extra出現using index,表示使用了覆蓋索引 explain select transaction_id from itdragon_order_list where transaction_id = "81X97310V32236260E";```第二個案例```sql--創建復合索引create index idx_order_levelDate on itdragon_order_list (order_level,input_date);--創建索引之后發現跟沒有創建索引一樣，都是全表掃描，都是文件排序explain select * from itdragon_order_list order by order_level,input_date;--可以使用force index強制指定索引explain select * from itdragon_order_list force index(idx_order_levelDate) order by order_level,input_date;--其實給訂單排序意義不大，給訂單級別添加索引意義也不大，因此可以先確定order_level的值，然后再給input_date排序explain select * from itdragon_order_list where order_level=3 order by input_date;```

就是普及兩個知識點，

一個創建唯一索引

create unique index idx_order_transaID on itdragon_order_list (transaction_id);(唯一索引比普通索引更快)

一個強制指定索引

explain select * from itdragon_order_list force index(idx_order_levelDate) order by order_level,input_date;

好了，今天就分享到這了，感覺mysql老師的筆記太瘋狂了。

項目源碼：

https://github.com/pengwenqq/studyDemo

總結

以上是生活随笔為你收集整理的sql server修改索引名称_索引基本知识和索引优化的全部內容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網站內容還不錯，歡迎將生活随笔推薦給好友。