" />

欧美bbbwbbbw肥妇,免费乱码人妻系列日韩,一级黄片

MySQL為數(shù)據(jù)表建立索引的原則詳解

 更新時(shí)間:2022年03月02日 10:11:39   作者:小小茶花女  
這篇文章主要為大家詳細(xì)介紹了MySQL為數(shù)據(jù)表建立索引的原則,文中示例代碼介紹的非常詳細(xì),具有一定的參考價(jià)值,感興趣的小伙伴們可以參考一下,希望能夠給你帶來幫助

面試題:

  • 索引是什么?
  • 索引的優(yōu)點(diǎn)?
  • 索引的缺點(diǎn)?
  • 在建立索引的時(shí)候都有哪些需要考慮的因素呢?
  • 為數(shù)據(jù)表建立索引的原則有哪些?
  • 什么是索引覆蓋?
  • 非聚簇索引一定會(huì)回表查詢嗎?

1. 索引是什么?

索引是幫助我們實(shí)現(xiàn)快速查詢數(shù)據(jù)庫的數(shù)據(jù)結(jié)構(gòu)。
在很多時(shí)候,表中存放的記錄非常的多,需要用到好多的數(shù)據(jù)頁來存放這些記錄,在很多頁中查找記錄可以分為2個(gè)步驟:

1、定位到記錄所在的頁

2、從所在頁內(nèi)查找相應(yīng)的記錄

在沒有索引的情況下,無論是根據(jù)主鍵列還是其他列的值進(jìn)行查找,由于我們不能快速的定位到記錄所在的頁,所以只能從第一頁沿著雙向鏈表一直往下找,因?yàn)樾枰闅v所有的數(shù)據(jù)頁,這種方式超級(jí)消耗性能。因此人們期望一種能高效完成搜索的方法,因此就出現(xiàn)了索引,索引就是一種數(shù)據(jù)結(jié)構(gòu)。

2. 索引的優(yōu)點(diǎn)?

索引可以讓服務(wù)器快速的定位到表的指定位置 (索引大大減少了服務(wù)器需要需要掃描的數(shù)據(jù)量)。最常見的B-Tree索引,按照順序存儲(chǔ)數(shù)據(jù),所以mysql可以來做order bygroup by操作(索引可以幫助服務(wù)器避免排序和臨時(shí)表)。因?yàn)閿?shù)據(jù)是有序的,所以B-Tree也就會(huì)將相關(guān)的列值都存儲(chǔ)在一起(索引可以將隨機(jī)IO變?yōu)轫樞騃O)。因?yàn)樗饕写鎯?chǔ)了實(shí)際的列值,所以某些查詢值使用索引就能完成全部查詢。因此,總結(jié)下來的索引的優(yōu)點(diǎn)為:

1、索引大大減少了服務(wù)器需要需要掃描的數(shù)據(jù)量;

2、索引可以幫助服務(wù)器避免排序和臨時(shí)表;

3、索引可以將隨機(jī)IO變?yōu)轫樞騃O;

3. 索引的缺點(diǎn)?

1、空間上的代價(jià):

建立索引需要占用物理空間

InnoDB存儲(chǔ)引擎默認(rèn)使用的B+樹索引,每建立一個(gè)索引,都要為它建立一顆B+樹,每一顆B+樹的每一個(gè)節(jié)點(diǎn)都是一個(gè)數(shù)據(jù)頁,一個(gè)數(shù)據(jù)頁默認(rèn)會(huì)占用16KB的存儲(chǔ)空間,而一顆很大的B+樹由許多數(shù)據(jù)頁組成,這將占用很大的一片存儲(chǔ)空間。

2、時(shí)間上的代價(jià):

因?yàn)槊看螌?duì)表記錄進(jìn)行增刪改,需要進(jìn)行動(dòng)態(tài)維護(hù)索引,導(dǎo)致增刪改時(shí)間變長

每當(dāng)對(duì)表中的數(shù)據(jù)進(jìn)行增刪改查操作時(shí),都需要修改各個(gè)B+樹索引,B+樹中的每層節(jié)點(diǎn)都按照索引列的值從小到大的順序組成了雙向鏈表,無論是葉子節(jié)點(diǎn)中的記錄還是非葉子節(jié)點(diǎn)中的記錄都按照索引列的值從小到大的順序排成了單向鏈表。而增刪改查操作可能會(huì)對(duì)節(jié)點(diǎn)和記錄的排序造成破壞,所以存儲(chǔ)引擎需要額外的時(shí)間進(jìn)行頁面分裂、頁面回收等操作,以維護(hù)節(jié)點(diǎn)和記錄的順序。

在執(zhí)行查詢語句前,首先要生成一個(gè)執(zhí)行計(jì)劃,一般情況下,一條查詢語句在執(zhí)行過程中最多使用一個(gè)二級(jí)索引,因此,在生成執(zhí)行計(jì)劃時(shí)需要計(jì)算使用不同索引執(zhí)行查詢時(shí)所需的成本,最后選取成本最小的那個(gè)索引執(zhí)行查詢,如果建立了太多的索引,可能會(huì)導(dǎo)致成本分析耗時(shí)太多,從而影響查詢語句的執(zhí)行性能。

4. 在建立索引的時(shí)候,都有哪些需要考慮的因素呢?

在創(chuàng)建和使用索引是應(yīng)該注意下列事項(xiàng):

  • 只為用于搜索、排序、分組的列創(chuàng)建索引;
  • 當(dāng)列中不重復(fù)值的個(gè)數(shù)在總記錄條數(shù)中的占比很大時(shí),才為列建立索引;
  • 索引列的類型盡量?。?/li>
  • 可以只為索引列前綴創(chuàng)建索引,以減少蘇嘔心占用的存儲(chǔ)空間;
  • 盡量使用覆蓋索引進(jìn)行查詢,以畢淼回表操作帶來的性能損耗;
  • 讓索引列以列名的形式單獨(dú)出現(xiàn)在搜索條件中;
  • 為了盡可能少的讓聚簇索引發(fā)生頁面分裂的情況,建議讓主鍵擁有anto_increment屬性;
  • 定位并刪除表中的冗余和重復(fù)索引;

建立一張數(shù)據(jù)庫表:

create table single_table(
	id int not auto_increment, 
	key1 varchar(100),         
	key2 int,
	key3 varchar(100),
	key_part1 varchar(100),
	key_part2 varchar(100),
	key_part3 varchar(100),
    common_field varchar(100),
	primary key(id),          # 聚簇索引
	key idx_key1(key1),       # 二級(jí)索引
	unique key uk_key2(key2), # 二級(jí)索引,而且該索引是唯一二級(jí)索引
	key idx_key3(key3),       # 二級(jí)索引
	key idx_key_part(key_part1,key_part2,key_part3) # 二級(jí)索引,也是聯(lián)合索引
)Engine=InnoDB CHARSET=utf8;

1、只為用于搜索、排序、分組的列創(chuàng)建索引

我們只為出現(xiàn)在where子句中的列、order bygroup by子句中的列、連接子句中的連接列創(chuàng)建索引。僅出現(xiàn)在查詢列表中的列就沒有必要建立索引了。

比如下面的查詢語句:

select common_field,key_part3 from single_table where key1='a';

我們只需要為出現(xiàn)在where子句中的key1建立索引就可以了,而查詢列表中的common_field、key_part3這兩個(gè)列就沒有必要建立索引了。

2、索引列的類型盡量小

在定義表結(jié)構(gòu)時(shí),要顯式的指定列的類型。以整數(shù)類型為例,有tingint、mediumint、int、bigint這幾種,他們占用的存儲(chǔ)空間的大小依次遞增,他們能表示的整數(shù)范圍當(dāng)然也是依次遞增。如果想要對(duì)某個(gè)整數(shù)類型的列建立索引,在表示的整數(shù)范圍允許的情況下,盡量讓索引列使用較小的類型,比如使用int就不要用bigint,因?yàn)閿?shù)據(jù)類型越小,索引占用的存儲(chǔ)空間就越少,在一個(gè)數(shù)據(jù)頁內(nèi)就可以存放更多的記錄,磁盤IO帶來的性能損耗也就越小,讀寫效率也就越高。

這個(gè)建議對(duì)表的主鍵來說更加適用,因?yàn)椴粌H聚簇索引會(huì)存儲(chǔ)主鍵值,所有的二級(jí)索引的節(jié)點(diǎn)都會(huì)存儲(chǔ)一份記錄的主鍵值,如果主鍵使用更小的數(shù)據(jù)類型,也就意味著能節(jié)省更多的存儲(chǔ)空間。

3、為列前綴建立索引

一個(gè)字符串其實(shí)是由若干個(gè)字符組成的,假如字符串很長,那么存儲(chǔ)這個(gè)字符串就會(huì)占用很多的存儲(chǔ)空間。在為這個(gè)字符串所在的列建立索引時(shí),就需要在對(duì)應(yīng)的B+樹中,把列的完整字符串存儲(chǔ)起來,字符串越長,在索引中占用的存儲(chǔ)空間越大。

索引列的字符串前綴其實(shí)也是排好序的,所以索引的設(shè)計(jì)人員設(shè)計(jì)了一個(gè)方案,即只將字符串的前幾個(gè)字符存放到索引中,也就是說二級(jí)索引的記錄中只保留字符串的前幾個(gè)字符。

比如可以這樣修改idx_key1索引,讓索引中只保留字符串的前10個(gè)字符:

alert table single_table drop index idx_key1;
alert table single_table add index idx_key1(key1(10));

然后再執(zhí)行下面的查詢語句:

select * from single_table where key1='abcdefghijklmn';

由于在idx_key1的二級(jí)索引記錄中只保留字符串的前10個(gè)字符,所以我們只能定位到前綴為‘abcdefghij'的二級(jí)索引記錄,在掃描這些二級(jí)索引記錄時(shí)再判斷它們是否滿足key1='abcdefghijklmn'條件,當(dāng)列中存儲(chǔ)的字符串包含的字符較多時(shí),這種為列前綴建立索引的方式可以明顯減少索引大小。

不過,在只對(duì)前綴建立索引的情況下,下面這個(gè)查詢語句就不能使用索引來完成排序需求了:

select * from single_table order by key1 limit 10;

因?yàn)槎?jí)索引idx_key1中不包含完整的key1列信息,所以在僅使用idx_key1索引執(zhí)行查詢時(shí),無法對(duì)key1列前10個(gè)字符相同但其余字符不同的記錄進(jìn)行排序,也就是說,只為列前綴建立索引的方式無法支持使用索引進(jìn)行排序的需求。

4、覆蓋索引

為了徹底告別回表操作帶來的性能損耗,建議最好在查詢列表中只包含索引列,比如這個(gè)查詢語句:

select key1,id from single_table where key1>'a' and key1<'c';

由于我們只查詢key1列和id列的值,所以在使用idx_key1索引來掃描('a','c')區(qū)間中的二級(jí)索引時(shí),可以直接從獲取到的二級(jí)索引記錄中讀出key1列和id列的值,而不需要再通過id值到聚簇索引中執(zhí)行回表操作了,這樣就省去了回表操作帶來的性能損耗。

我們把這種索引中已經(jīng)包含了所有需要讀取的列的查詢方式稱為覆蓋索引。如果索引的葉子節(jié)點(diǎn)中已經(jīng)包含要查詢的數(shù)據(jù),那么還有必要再回表查詢呢?如果一個(gè)索引包含所有需要查詢的字段的值,就稱為覆蓋索引。

排序操作也優(yōu)先使用覆蓋索引進(jìn)行查詢,比如下面這個(gè)查詢語句:

select key1 from single_table order by key1;

雖然這個(gè)查詢語句中沒有l(wèi)imit子句,但是由于可以采用覆蓋索引,所以查詢優(yōu)化器會(huì)直接使用idx_key1索引進(jìn)行排序,而不需要執(zhí)行回表操作。

當(dāng)然,如果業(yè)務(wù)需要查詢索引列以外的列,還是要以保證業(yè)務(wù)需求為重,如無必要,最好把業(yè)務(wù)中需要的列放在查詢列表中,而不是以簡單的*替代。

5、讓索引列以列名的形式在搜索條件中單獨(dú)出現(xiàn)

在下面這兩個(gè)查詢語句中,搜索條件中的語義是一樣的:

select * from single_table where key2*2<4;
select * from single_table where key2<4/2;

在第一個(gè)查詢語句的搜索條件中,key2列并不是以單獨(dú)列名的形式出現(xiàn)的,而是以key2*2這樣的形式表現(xiàn)的,MySQL并不會(huì)嘗試簡化key2*2<4表達(dá)式,而是直接認(rèn)為這個(gè)搜索條件不能形成合適的掃描區(qū)間來減少需要掃描的記錄數(shù)量,所以該查詢語句只能以全表掃描的方式來執(zhí)行。

在第二個(gè)查詢語句的搜索條件中,key2列并是以單獨(dú)列名的形式出現(xiàn)的,MySQL可以分析出key2<2,這樣可以減少需要掃描的記錄數(shù)量。所以MySQL可能使用uk_key2來執(zhí)行查詢。

所以,如果想讓某個(gè)查詢使用索引來執(zhí)行,請(qǐng)讓索引列以列名的形式單獨(dú)出現(xiàn)在搜索條件中。

6、新插入記錄時(shí)主鍵大小對(duì)效率的影響

我們知道,對(duì)于一個(gè)使用InnoDB存儲(chǔ)引擎的表來說,在沒有顯式創(chuàng)建索引時(shí),表中的數(shù)據(jù)實(shí)際上存儲(chǔ)在聚簇索引的葉子節(jié)點(diǎn)中,而且B+樹的每一層數(shù)據(jù)頁以及頁面中的記錄都是按照主鍵值從小到大的順序排序的,如果新插入記錄的主鍵值是依次遞增的話,每插滿一個(gè)數(shù)據(jù)頁就換到下一個(gè)數(shù)據(jù)頁繼續(xù)插入,如果新插入記錄的主鍵值忽大忽小,就比較麻煩了。

假設(shè)某個(gè)數(shù)據(jù)頁存儲(chǔ)的聚簇索引記錄已經(jīng)滿了,它存儲(chǔ)的主鍵值在1~100之間,此時(shí)如果再插入一提哦啊主鍵值為9的記錄,因?yàn)檫@個(gè)數(shù)據(jù)頁已經(jīng)滿了,新紀(jì)錄應(yīng)該插入到哪里呢?

我們需要把當(dāng)前頁面分裂成兩個(gè)頁面,把本頁中的一些記錄移動(dòng)到新創(chuàng)建的一些頁中,頁分裂意味著性能損耗,所以如果想盡量避免這種無謂的性能損耗,最好讓插入記錄的主鍵值依次遞增。即讓主鍵擁有auto_increment屬性,MySQl會(huì)自動(dòng)為新插入的記錄生成遞增的主鍵值。

7、冗余和重復(fù)索引

針對(duì)single_table表,可以單獨(dú)針對(duì)key_part1列建立一個(gè)idx_key_part1索引:

alert table single_table and index idx_key_part1(key_part1);

而此時(shí)我們已經(jīng)有了一個(gè)針對(duì)key_part1、key_part2、key_part3列建立的聯(lián)合索引idx_key_par。idx_key_part索引的二級(jí)索引記錄本身就是按照key_part1列的值排序的,此時(shí)再單獨(dú)為key_part1列建立一個(gè)索引其實(shí)是沒必要的,我們可以把整個(gè)新建的idx_key_part1索引看作一個(gè)冗余索引,該冗余索引是沒有必要的。

有時(shí),我們可能會(huì)對(duì)同一個(gè)列建立多個(gè)索引,比如下面兩個(gè)添加索引的語句:

alert table single_table add unique key uk_id(id);
alert table single_table add index idx_id(id);

我們針對(duì)id列又建立了一個(gè)唯一二級(jí)索引uk_id,還建立了一個(gè)普通二級(jí)索引idx_id,可是id列本身就是single_table表的主鍵,InnoDB自動(dòng)為該列建立了聚簇索引,此時(shí)uk_ididx_id就是重復(fù)的,這種重復(fù)索引應(yīng)該避免。

總結(jié)

本篇文章就到這里了,希望能夠給你帶來幫助,也希望您能夠多多關(guān)注腳本之家的更多內(nèi)容!   

相關(guān)文章

  • 在MySQL中修改密碼及訪問限制的設(shè)置方法詳解

    在MySQL中修改密碼及訪問限制的設(shè)置方法詳解

    MySQL是一個(gè)真正的多用戶、多線程SQL數(shù)據(jù)庫服務(wù)器。MySQL是以一個(gè)客戶機(jī)/服務(wù)器結(jié)構(gòu)的實(shí)現(xiàn),它由一個(gè)服務(wù)器守護(hù)程序mysqld和很多不同的客戶程序和庫組成。
    2007-03-03
  • MySQL各個(gè)特殊時(shí)間段的查詢方法

    MySQL各個(gè)特殊時(shí)間段的查詢方法

    在MySQL數(shù)據(jù)庫中,經(jīng)常需要查詢某個(gè)時(shí)間段內(nèi)的數(shù)據(jù),所以本文給大家介紹了MySQL各個(gè)特殊時(shí)間段的查詢方法,并提供相應(yīng)的源代碼示例,具有一定的參考借鑒價(jià)值,需要的朋友可以參考下
    2024-01-01
  • MySQL8的主要目錄結(jié)構(gòu)解讀

    MySQL8的主要目錄結(jié)構(gòu)解讀

    這篇文章主要介紹了MySQL8的主要目錄結(jié)構(gòu),具有很好的參考價(jià)值,希望對(duì)大家有所幫助,如有錯(cuò)誤或未考慮完全的地方,望不吝賜教
    2023-09-09
  • mysql count提高方法總結(jié)

    mysql count提高方法總結(jié)

    在本文里小編給大家分享了關(guān)于mysql count提高方法和具體步驟,需要的朋友們跟著學(xué)習(xí)參考下。
    2019-03-03
  • mysql優(yōu)化取隨機(jī)數(shù)據(jù)慢的方法

    mysql優(yōu)化取隨機(jī)數(shù)據(jù)慢的方法

    mysql取隨機(jī)數(shù)據(jù)慢,怎么辦?下面小編與大家一起來看看mysql取隨機(jī)數(shù)據(jù)慢優(yōu)化的過程。
    2013-11-11
  • Mysql如何查詢某條記錄在分頁的第幾頁詳析

    Mysql如何查詢某條記錄在分頁的第幾頁詳析

    查詢是我們?nèi)粘9ぷ髦薪?jīng)常會(huì)遇到的一個(gè)功能,下面這篇文章主要給大家介紹了關(guān)于Mysql如何查詢某條記錄在分頁的第幾頁的相關(guān)資料,文中通過示例代碼介紹的非常詳細(xì),需要的朋友可以參考下
    2018-11-11
  • 虛擬主機(jī)中phpMyAdmin的安裝配置方法

    虛擬主機(jī)中phpMyAdmin的安裝配置方法

    phpMyAdmin 是一套可以通過WEB來管理 MySQL-server 以及單一數(shù)據(jù)庫的 PHP 程序。對(duì)于一些虛擬空間的站點(diǎn)來說,應(yīng)該是不可缺少的吧!!!
    2010-06-06
  • MySQL數(shù)據(jù)庫終端—常用操作指令代碼

    MySQL數(shù)據(jù)庫終端—常用操作指令代碼

    這篇文章主要介紹了MySQL數(shù)據(jù)庫終端—常用操作指令代碼,添加用戶、更改用戶名和host主機(jī)、更改密碼、刪除用戶等等,具有一定的參考價(jià)值,需要的小伙伴可以參考一下
    2022-01-01
  • linux/mac安裝mysql忘記密碼的解決辦法

    linux/mac安裝mysql忘記密碼的解決辦法

    這篇文章主要給大家介紹了關(guān)于linux/mac安裝mysql忘記密碼的解決辦法,文中通過示例代碼介紹的非常詳細(xì),對(duì)大家的學(xué)習(xí)或者工作具有一定的參考學(xué)習(xí)價(jià)值,需要的朋友們下面隨著小編來一起學(xué)習(xí)學(xué)習(xí)吧。
    2017-10-10
  • 很全面的Mysql數(shù)據(jù)庫、數(shù)據(jù)庫表、數(shù)據(jù)基礎(chǔ)操作筆記(含代碼)

    很全面的Mysql數(shù)據(jù)庫、數(shù)據(jù)庫表、數(shù)據(jù)基礎(chǔ)操作筆記(含代碼)

    這篇文章主要為大家分享了很全面的Mysql數(shù)據(jù)庫、數(shù)據(jù)庫表、數(shù)據(jù)基礎(chǔ)操作筆記,感興趣的小伙伴們可以參考一下
    2016-04-04

最新評(píng)論