真实的国产乱ⅩXXX66竹夫人,五月香六月婷婷激情综合,亚洲日本VA一区二区三区,亚洲精品一区二区三区麻豆

成都創(chuàng)新互聯(lián)網(wǎng)站制作重慶分公司

mysql怎么優(yōu)化一張表,mysql聯(lián)表查詢優(yōu)化

MySQL 對(duì)于大表,要怎么優(yōu)化

這個(gè)問(wèn)題涉及一下幾個(gè)知識(shí)點(diǎn)

10多年的英吉沙網(wǎng)站建設(shè)經(jīng)驗(yàn),針對(duì)設(shè)計(jì)、前端、開(kāi)發(fā)、售后、文案、推廣等六對(duì)一服務(wù),響應(yīng)快,48小時(shí)及時(shí)工作處理。成都營(yíng)銷(xiāo)網(wǎng)站建設(shè)的優(yōu)勢(shì)是能夠根據(jù)用戶設(shè)備顯示端的尺寸不同,自動(dòng)調(diào)整英吉沙建站的顯示方式,使網(wǎng)站能夠適用不同顯示終端,在瀏覽器中調(diào)整網(wǎng)站的寬度,無(wú)論在任何一種瀏覽器上瀏覽網(wǎng)站,都能展現(xiàn)優(yōu)雅布局與設(shè)計(jì),從而大程度地提升瀏覽體驗(yàn)。成都創(chuàng)新互聯(lián)公司從事“英吉沙網(wǎng)站設(shè)計(jì)”,“英吉沙網(wǎng)站推廣”以來(lái),每個(gè)客戶項(xiàng)目都認(rèn)真落實(shí)執(zhí)行。

一、一般情況表的優(yōu)化主要是索引,恰當(dāng)?shù)乃饕芗涌觳樵兊乃俣龋?dāng)然索引的建立與表的主要用途有關(guān),特別是表最頻繁的操作是查詢、添加、刪除,查詢是按哪些條件進(jìn)行查詢。

二、所謂的大表,對(duì)于數(shù)據(jù)庫(kù)來(lái)說(shuō),沒(méi)有絕對(duì)大小的限制,只要操作系統(tǒng)的硬盤(pán)、內(nèi)存合適,表在一定范圍內(nèi)大一點(diǎn)、小一點(diǎn)對(duì)性能影響不大。分表是一個(gè)痛苦的過(guò)程,因?yàn)閺倪壿嬌习驯矸殖蓭讉€(gè)部分,會(huì)把查詢、添加、修改的應(yīng)用程序變得相當(dāng)復(fù)雜,除非萬(wàn)不得已,沒(méi)人會(huì)這樣選擇。數(shù)據(jù)太大的時(shí)候,一般使用磁盤(pán)陣列來(lái)提高性能,物理上一個(gè)大文件是分片存在不同的硬盤(pán)上,不同的硬盤(pán)可以并行讀取,內(nèi)存組裝一下,達(dá)到了高速讀取的效果。

Mysql某個(gè)表有近千萬(wàn)數(shù)據(jù),CRUD比較慢,如何優(yōu)化?

數(shù)據(jù)千萬(wàn)級(jí)別之多,占用的存儲(chǔ)空間也比較大,可想而知它不會(huì)存儲(chǔ)在一塊連續(xù)的物理空間上,而是鏈?zhǔn)酱鎯?chǔ)在多個(gè)碎片的物理空間上。可能對(duì)于長(zhǎng)字符串的比較,就用更多的時(shí)間查找與比較,這就導(dǎo)致用更多的時(shí)間。

可以做表拆分,減少單表字段數(shù)量,優(yōu)化表結(jié)構(gòu)。

在保證主鍵有效的情況下,檢查主鍵索引的字段順序,使得查詢語(yǔ)句中條件的字段順序和主鍵索引的字段順序保持一致。

主要兩種拆分 垂直拆分,水平拆分。

垂直分表

也就是“大表拆小表”,基于列字段進(jìn)行的。一般是表中的字段較多,將不常用的, 數(shù)據(jù)較大,長(zhǎng)度較長(zhǎng)(比如text類(lèi)型字段)的拆分到“擴(kuò)展表“。 一般是針對(duì) 那種 幾百列的大表,也避免查詢時(shí),數(shù)據(jù)量太大造成的“跨頁(yè)”問(wèn)題。

垂直分庫(kù)針對(duì)的是一個(gè)系統(tǒng)中的不同業(yè)務(wù)進(jìn)行拆分,比如用戶User一個(gè)庫(kù),商品Product一個(gè)庫(kù),訂單Order一個(gè)庫(kù)。 切分后,要放在多個(gè)服務(wù)器上,而不是一個(gè)服務(wù)器上。為什么? 我們想象一下,一個(gè)購(gòu)物網(wǎng)站對(duì)外提供服務(wù),會(huì)有用戶,商品,訂單等的CRUD。沒(méi)拆分之前, 全部都是落到單一的庫(kù)上的,這會(huì)讓數(shù)據(jù)庫(kù)的單庫(kù)處理能力成為瓶頸。按垂直分庫(kù)后,如果還是放在一個(gè)數(shù)據(jù)庫(kù)服務(wù)器上, 隨著用戶量增大,這會(huì)讓單個(gè)數(shù)據(jù)庫(kù)的處理能力成為瓶頸,還有單個(gè)服務(wù)器的磁盤(pán)空間,內(nèi)存,tps等非常吃緊。 所以我們要拆分到多個(gè)服務(wù)器上,這樣上面的問(wèn)題都解決了,以后也不會(huì)面對(duì)單機(jī)資源問(wèn)題。

數(shù)據(jù)庫(kù)業(yè)務(wù)層面的拆分,和服務(wù)的“治理”,“降級(jí)”機(jī)制類(lèi)似,也能對(duì)不同業(yè)務(wù)的數(shù)據(jù)分別的進(jìn)行管理,維護(hù),監(jiān)控,擴(kuò)展等。 數(shù)據(jù)庫(kù)往往最容易成為應(yīng)用系統(tǒng)的瓶頸,而數(shù)據(jù)庫(kù)本身屬于“有狀態(tài)”的,相對(duì)于Web和應(yīng)用服務(wù)器來(lái)講,是比較難實(shí)現(xiàn)“橫向擴(kuò)展”的。 數(shù)據(jù)庫(kù)的連接資源比較寶貴且單機(jī)處理能力也有限,在高并發(fā)場(chǎng)景下,垂直分庫(kù)一定程度上能夠突破IO、連接數(shù)及單機(jī)硬件資源的瓶頸。

水平分表

針對(duì)數(shù)據(jù)量巨大的單張表(比如訂單表),按照某種規(guī)則(RANGE,HASH取模等),切分到多張表里面去。 但是這些表還是在同一個(gè)庫(kù)中,所以庫(kù)級(jí)別的數(shù)據(jù)庫(kù)操作還是有IO瓶頸。不建議采用。

水平分庫(kù)分表

將單張表的數(shù)據(jù)切分到多個(gè)服務(wù)器上去,每個(gè)服務(wù)器具有相應(yīng)的庫(kù)與表,只是表中數(shù)據(jù)集合不同。 水平分庫(kù)分表能夠有效的緩解單機(jī)和單庫(kù)的性能瓶頸和壓力,突破IO、連接數(shù)、硬件資源等的瓶頸。

水平分庫(kù)分表切分規(guī)則

1. RANGE

從0到10000一個(gè)表,10001到20000一個(gè)表;

2. HASH取模

一個(gè)商場(chǎng)系統(tǒng),一般都是將用戶,訂單作為主表,然后將和它們相關(guān)的作為附表,這樣不會(huì)造成跨庫(kù)事務(wù)之類(lèi)的問(wèn)題。 取用戶id,然后hash取模,分配到不同的數(shù)據(jù)庫(kù)上。

3. 地理區(qū)域

比如按照華東,華南,華北這樣來(lái)區(qū)分業(yè)務(wù),七牛云應(yīng)該就是如此。

4. 時(shí)間

按照時(shí)間切分,就是將6個(gè)月前,甚至一年前的數(shù)據(jù)切出去放到另外的一張表,因?yàn)殡S著時(shí)間流逝,這些表的數(shù)據(jù) 被查詢的概率變小,所以沒(méi)必要和“熱數(shù)據(jù)”放在一起,這個(gè)也是“冷熱數(shù)據(jù)分離”。

分庫(kù)分表后面臨的問(wèn)題

事務(wù)支持

分庫(kù)分表后,就成了分布式事務(wù)了。如果依賴數(shù)據(jù)庫(kù)本身的分布式事務(wù)管理功能去執(zhí)行事務(wù),將付出高昂的性能代價(jià); 如果由應(yīng)用程序去協(xié)助控制,形成程序邏輯上的事務(wù),又會(huì)造成編程方面的負(fù)擔(dān)。

跨庫(kù)join

只要是進(jìn)行切分,跨節(jié)點(diǎn)Join的問(wèn)題是不可避免的。但是良好的設(shè)計(jì)和切分卻可以減少此類(lèi)情況的發(fā)生。解決這一問(wèn)題的普遍做法是分兩次查詢實(shí)現(xiàn)。在第一次查詢的結(jié)果集中找出關(guān)聯(lián)數(shù)據(jù)的id,根據(jù)這些id發(fā)起第二次請(qǐng)求得到關(guān)聯(lián)數(shù)據(jù)。

跨節(jié)點(diǎn)的count,order by,group by以及聚合函數(shù)問(wèn)題

這些是一類(lèi)問(wèn)題,因?yàn)樗鼈兌夹枰谌繑?shù)據(jù)集合進(jìn)行計(jì)算。多數(shù)的代理都不會(huì)自動(dòng)處理合并工作。解決方案:與解決跨節(jié)點(diǎn)join問(wèn)題的類(lèi)似,分別在各個(gè)節(jié)點(diǎn)上得到結(jié)果后在應(yīng)用程序端進(jìn)行合并。和join不同的是每個(gè)結(jié)點(diǎn)的查詢可以并行執(zhí)行,因此很多時(shí)候它的速度要比單一大表快很多。但如果結(jié)果集很大,對(duì)應(yīng)用程序內(nèi)存的消耗是一個(gè)問(wèn)題。

數(shù)據(jù)遷移,容量規(guī)劃,擴(kuò)容等問(wèn)題

來(lái)自淘寶綜合業(yè)務(wù)平臺(tái)團(tuán)隊(duì),它利用對(duì)2的倍數(shù)取余具有向前兼容的特性(如對(duì)4取余得1的數(shù)對(duì)2取余也是1)來(lái)分配數(shù)據(jù),避免了行級(jí)別的數(shù)據(jù)遷移,但是依然需要進(jìn)行表級(jí)別的遷移,同時(shí)對(duì)擴(kuò)容規(guī)模和分表數(shù)量都有限制??偟脕?lái)說(shuō),這些方案都不是十分的理想,多多少少都存在一些缺點(diǎn),這也從一個(gè)側(cè)面反映出了Sharding擴(kuò)容的難度。

ID問(wèn)題

一旦數(shù)據(jù)庫(kù)被切分到多個(gè)物理結(jié)點(diǎn)上,我們將不能再依賴數(shù)據(jù)庫(kù)自身的主鍵生成機(jī)制。一方面,某個(gè)分區(qū)數(shù)據(jù)庫(kù)自生成的ID無(wú)法保證在全局上是唯一的;另一方面,應(yīng)用程序在插入數(shù)據(jù)之前需要先獲得ID,以便進(jìn)行SQL路由.

一些常見(jiàn)的主鍵生成策略

UUID

使用UUID作主鍵是最簡(jiǎn)單的方案,但是缺點(diǎn)也是非常明顯的。由于UUID非常的長(zhǎng),除占用大量存儲(chǔ)空間外,最主要的問(wèn)題是在索引上,在建立索引和基于索引進(jìn)行查詢時(shí)都存在性能問(wèn)題。

Twitter的分布式自增ID算法Snowflake

在分布式系統(tǒng)中,需要生成全局UID的場(chǎng)合還是比較多的,twitter的snowflake解決了這種需求,實(shí)現(xiàn)也還是很簡(jiǎn)單的,除去配置信息,核心代碼就是毫秒級(jí)時(shí)間41位 機(jī)器ID 10位 毫秒內(nèi)序列12位。

跨分片的排序分頁(yè)

一般來(lái)講,分頁(yè)時(shí)需要按照指定字段進(jìn)行排序。當(dāng)排序字段就是分片字段的時(shí)候,我們通過(guò)分片規(guī)則可以比較容易定位到指定的分片,而當(dāng)排序字段非分片字段的時(shí)候,情況就會(huì)變得比較復(fù)雜了。為了最終結(jié)果的準(zhǔn)確性,我們需要在不同的分片節(jié)點(diǎn)中將數(shù)據(jù)進(jìn)行排序并返回,并將不同分片返回的結(jié)果集進(jìn)行匯總和再次排序,最后再返回給用戶。

MySQL數(shù)據(jù)庫(kù)性能優(yōu)化之分區(qū)分表分庫(kù)

分表是分散數(shù)據(jù)庫(kù)壓力的好方法。

分表,最直白的意思,就是將一個(gè)表結(jié)構(gòu)分為多個(gè)表,然后,可以再同一個(gè)庫(kù)里,也可以放到不同的庫(kù)。

當(dāng)然,首先要知道什么情況下,才需要分表。個(gè)人覺(jué)得單表記錄條數(shù)達(dá)到百萬(wàn)到千萬(wàn)級(jí)別時(shí)就要使用分表了。

分表的分類(lèi)

**1、縱向分表**

將本來(lái)可以在同一個(gè)表的內(nèi)容,人為劃分為多個(gè)表。(所謂的本來(lái),是指按照關(guān)系型數(shù)據(jù)庫(kù)的第三范式要求,是應(yīng)該在同一個(gè)表的。)

分表理由:根據(jù)數(shù)據(jù)的活躍度進(jìn)行分離,(因?yàn)椴煌钴S的數(shù)據(jù),處理方式是不同的)

案例:

對(duì)于一個(gè)博客系統(tǒng),文章標(biāo)題,作者,分類(lèi),創(chuàng)建時(shí)間等,是變化頻率慢,查詢次數(shù)多,而且最好有很好的實(shí)時(shí)性的數(shù)據(jù),我們把它叫做冷數(shù)據(jù)。而博客的瀏覽量,回復(fù)數(shù)等,類(lèi)似的統(tǒng)計(jì)信息,或者別的變化頻率比較高的數(shù)據(jù),我們把它叫做活躍數(shù)據(jù)。所以,在進(jìn)行數(shù)據(jù)庫(kù)結(jié)構(gòu)設(shè)計(jì)的時(shí)候,就應(yīng)該考慮分表,首先是縱向分表的處理。

這樣縱向分表后:

首先存儲(chǔ)引擎的使用不同,冷數(shù)據(jù)使用MyIsam 可以有更好的查詢數(shù)據(jù)?;钴S數(shù)據(jù),可以使用Innodb ,可以有更好的更新速度。

其次,對(duì)冷數(shù)據(jù)進(jìn)行更多的從庫(kù)配置,因?yàn)楦嗟牟僮鲿r(shí)查詢,這樣來(lái)加快查詢速度。對(duì)熱數(shù)據(jù),可以相對(duì)有更多的主庫(kù)的橫向分表處理。

其實(shí),對(duì)于一些特殊的活躍數(shù)據(jù),也可以考慮使用memcache ,redis之類(lèi)的緩存,等累計(jì)到一定量再去更新數(shù)據(jù)庫(kù)?;蛘適ongodb 一類(lèi)的nosql 數(shù)據(jù)庫(kù),這里只是舉例,就先不說(shuō)這個(gè)。

**2、橫向分表**

字面意思,就可以看出來(lái),是把大的表結(jié)構(gòu),橫向切割為同樣結(jié)構(gòu)的不同表,如,用戶信息表,user_1,user_2等。表結(jié)構(gòu)是完全一樣,但是,根據(jù)某些特定的規(guī)則來(lái)劃分的表,如根據(jù)用戶ID來(lái)取模劃分。

分表理由:根據(jù)數(shù)據(jù)量的規(guī)模來(lái)劃分,保證單表的容量不會(huì)太大,從而來(lái)保證單表的查詢等處理能力。

案例:同上面的例子,博客系統(tǒng)。當(dāng)博客的量達(dá)到很大時(shí)候,就應(yīng)該采取橫向分割來(lái)降低每個(gè)單表的壓力,來(lái)提升性能。例如博客的冷數(shù)據(jù)表,假如分為100個(gè)表,當(dāng)同時(shí)有100萬(wàn)個(gè)用戶在瀏覽時(shí),如果是單表的話,會(huì)進(jìn)行100萬(wàn)次請(qǐng)求,而現(xiàn)在分表后,就可能是每個(gè)表進(jìn)行1萬(wàn)個(gè)數(shù)據(jù)的請(qǐng)求(因?yàn)椋豢赡芙^對(duì)的平均,只是假設(shè)),這樣壓力就降低了很多很多。

延伸:為什么要分表和分區(qū)?

日常開(kāi)發(fā)中我們經(jīng)常會(huì)遇到大表的情況,所謂的大表是指存儲(chǔ)了百萬(wàn)級(jí)乃至千萬(wàn)級(jí)條記錄的表。這樣的表過(guò)于龐大,導(dǎo)致數(shù)據(jù)庫(kù)在查詢和插入的時(shí)候耗時(shí)太長(zhǎng),性能低下,如果涉及聯(lián)合查詢的情況,性能會(huì)更加糟糕。分表和表分區(qū)的目的就是減少數(shù)據(jù)庫(kù)的負(fù)擔(dān),提高數(shù)據(jù)庫(kù)的效率,通常點(diǎn)來(lái)講就是提高表的增刪改查效率。

什么是分表?

分表是將一個(gè)大表按照一定的規(guī)則分解成多張具有獨(dú)立存儲(chǔ)空間的實(shí)體表,我們可以稱為子表,每個(gè)表都對(duì)應(yīng)三個(gè)文件,MYD數(shù)據(jù)文件,.MYI索引文件,.frm表結(jié)構(gòu)文件。這些子表可以分布在同一塊磁盤(pán)上,也可以在不同的機(jī)器上。app讀寫(xiě)的時(shí)候根據(jù)事先定義好的規(guī)則得到對(duì)應(yīng)的子表名,然后去操作它。

什么是分區(qū)?

分區(qū)和分表相似,都是按照規(guī)則分解表。不同在于分表將大表分解為若干個(gè)獨(dú)立的實(shí)體表,而分區(qū)是將數(shù)據(jù)分段劃分在多個(gè)位置存放,可以是同一塊磁盤(pán)也可以在不同的機(jī)器。分區(qū)后,表面上還是一張表,但數(shù)據(jù)散列到多個(gè)位置了。app讀寫(xiě)的時(shí)候操作的還是大表名字,db自動(dòng)去組織分區(qū)的數(shù)據(jù)。

**MySQL分表和分區(qū)有什么聯(lián)系呢?**

1、都能提高mysql的性高,在高并發(fā)狀態(tài)下都有一個(gè)良好的表現(xiàn)。

2、分表和分區(qū)不矛盾,可以相互配合的,對(duì)于那些大訪問(wèn)量,并且表數(shù)據(jù)比較多的表,我們可以采取分表和分區(qū)結(jié)合的方式(如果merge這種分表方式,不能和分區(qū)配合的話,可以用其他的分表試),訪問(wèn)量不大,但是表數(shù)據(jù)很多的表,我們可以采取分區(qū)的方式等。

3、分表技術(shù)是比較麻煩的,需要手動(dòng)去創(chuàng)建子表,app服務(wù)端讀寫(xiě)時(shí)候需要計(jì)算子表名。采用merge好一些,但也要?jiǎng)?chuàng)建子表和配置子表間的union關(guān)系。

4、表分區(qū)相對(duì)于分表,操作方便,不需要?jiǎng)?chuàng)建子表。

我們知道對(duì)于大型的互聯(lián)網(wǎng)應(yīng)用,數(shù)據(jù)庫(kù)單表的數(shù)據(jù)量可能達(dá)到千萬(wàn)甚至上億級(jí)別,同時(shí)面臨這高并發(fā)的壓力。Master-Slave結(jié)構(gòu)只能對(duì)數(shù)據(jù)庫(kù)的讀能力進(jìn)行擴(kuò)展,寫(xiě)操作還是集中在Master中,Master并不能無(wú)限制的掛接Slave庫(kù),如果需要對(duì)數(shù)據(jù)庫(kù)的吞吐能力進(jìn)行進(jìn)一步的擴(kuò)展,可以考慮采用分庫(kù)分表的策略。

**1、分表**

在分表之前,首先要選中合適的分表策略(以哪個(gè)字典為分表字段,需要將數(shù)據(jù)分為多少?gòu)埍恚?,使?shù)據(jù)能夠均衡的分布在多張表中,并且不影響正常的查詢。在企業(yè)級(jí)應(yīng)用中,往往使用org_id(組織主鍵)做為分表字段,在互聯(lián)網(wǎng)應(yīng)用中往往是userid。在確定分表策略后,當(dāng)數(shù)據(jù)進(jìn)行存儲(chǔ)及查詢時(shí),需要確定到哪張表里去查找數(shù)據(jù),

數(shù)據(jù)存放的數(shù)據(jù)表 = 分表字段的內(nèi)容 % 分表數(shù)量

**2、分庫(kù)**

分表能夠解決單表數(shù)據(jù)量過(guò)大帶來(lái)的查詢效率下降的問(wèn)題,但是不能給數(shù)據(jù)庫(kù)的并發(fā)訪問(wèn)帶來(lái)質(zhì)的提升,面對(duì)高并發(fā)的寫(xiě)訪問(wèn),當(dāng)Master無(wú)法承擔(dān)高并發(fā)的寫(xiě)入請(qǐng)求時(shí),不管如何擴(kuò)展Slave服務(wù)器,都沒(méi)有意義了。我們通過(guò)對(duì)數(shù)據(jù)庫(kù)進(jìn)行拆分,來(lái)提高數(shù)據(jù)庫(kù)的寫(xiě)入能力,即所謂的分庫(kù)。分庫(kù)采用對(duì)關(guān)鍵字取模的方式,對(duì)數(shù)據(jù)庫(kù)進(jìn)行路由。

數(shù)據(jù)存放的數(shù)據(jù)庫(kù)=分庫(kù)字段的內(nèi)容%數(shù)據(jù)庫(kù)的數(shù)量

**3、即分表又分庫(kù)**

數(shù)據(jù)庫(kù)分表可以解決單表海量數(shù)據(jù)的查詢性能問(wèn)題,分庫(kù)可以解決單臺(tái)數(shù)據(jù)庫(kù)的并發(fā)訪問(wèn)壓力問(wèn)題。

當(dāng)數(shù)據(jù)庫(kù)同時(shí)面臨海量數(shù)據(jù)存儲(chǔ)和高并發(fā)訪問(wèn)的時(shí)候,需要同時(shí)采取分表和分庫(kù)策略。一般分表分庫(kù)策略如下:

中間變量 = 關(guān)鍵字%(數(shù)據(jù)庫(kù)數(shù)量*單庫(kù)數(shù)據(jù)表數(shù)量)

庫(kù) = 取整(中間變量/單庫(kù)數(shù)據(jù)表數(shù)量)

表 = (中間變量%單庫(kù)數(shù)據(jù)表數(shù)量)

實(shí)例:

1、分庫(kù)分表

很明顯,一個(gè)主表(也就是很重要的表,例如用戶表)無(wú)限制的增長(zhǎng)勢(shì)必嚴(yán)重影響性能,分庫(kù)與分表是一個(gè)很不錯(cuò)的解決途徑,也就是性能優(yōu)化途徑,現(xiàn)在的案例是我們有一個(gè)1000多萬(wàn)條記錄的用戶表members,查詢起來(lái)非常之慢,同事的做法是將其散列到100個(gè)表中,分別從members0到members99,然后根據(jù)mid分發(fā)記錄到這些表中,牛逼的代碼大概是這樣子:

復(fù)制代碼 代碼如下:

?php

for($i=0;$i 100; $i++ ){

//echo "CREATE TABLE db2.members{$i} LIKE db1.members

";

echo "INSERT INTO members{$i} SELECT * FROM members WHERE mid%100={$i}

";

}

?

2、不停機(jī)修改mysql表結(jié)構(gòu)

同樣還是members表,前期設(shè)計(jì)的表結(jié)構(gòu)不盡合理,隨著數(shù)據(jù)庫(kù)不斷運(yùn)行,其冗余數(shù)據(jù)也是增長(zhǎng)巨大,同事使用了下面的方法來(lái)處理:

先創(chuàng)建一個(gè)臨時(shí)表:

/*創(chuàng)建臨時(shí)表*/

CREATE TABLE members_tmp LIKE members

然后修改members_tmp的表結(jié)構(gòu)為新結(jié)構(gòu),接著使用上面那個(gè)for循環(huán)來(lái)導(dǎo)出數(shù)據(jù),因?yàn)?000萬(wàn)的數(shù)據(jù)一次性導(dǎo)出是不對(duì)的,mid是主鍵,一個(gè)區(qū)間一個(gè)區(qū)間的導(dǎo),基本是一次導(dǎo)出5萬(wàn)條吧,這里略去了

接著重命名將新表替換上去:

/*這是個(gè)頗為經(jīng)典的語(yǔ)句哈*/

RENAME TABLE members TO members_bak,members_tmp TO members;

就是這樣,基本可以做到無(wú)損失,無(wú)需停機(jī)更新表結(jié)構(gòu),但實(shí)際上RENAME期間表是被鎖死的,所以選擇在線少的時(shí)候操作是一個(gè)技巧。經(jīng)過(guò)這個(gè)操作,使得原先8G多的表,一下子變成了2G多。

超詳細(xì)MySQL數(shù)據(jù)庫(kù)優(yōu)化

數(shù)據(jù)庫(kù)優(yōu)化一方面是找出系統(tǒng)的瓶頸,提高M(jìn)ySQL數(shù)據(jù)庫(kù)的整體性能,而另一方面需要合理的結(jié)構(gòu)設(shè)計(jì)和參數(shù)調(diào)整,以提高用戶的相應(yīng)速度,同時(shí)還要盡可能的節(jié)約系統(tǒng)資源,以便讓系統(tǒng)提供更大的負(fù)荷.

1. 優(yōu)化一覽圖

2. 優(yōu)化

筆者將優(yōu)化分為了兩大類(lèi),軟優(yōu)化和硬優(yōu)化,軟優(yōu)化一般是操作數(shù)據(jù)庫(kù)即可,而硬優(yōu)化則是操作服務(wù)器硬件及參數(shù)設(shè)置.

2.1 軟優(yōu)化

2.1.1 查詢語(yǔ)句優(yōu)化

1.首先我們可以用EXPLAIN或DESCRIBE(簡(jiǎn)寫(xiě):DESC)命令分析一條查詢語(yǔ)句的執(zhí)行信息.

2.例:

顯示:

其中會(huì)顯示索引和查詢數(shù)據(jù)讀取數(shù)據(jù)條數(shù)等信息.

2.1.2 優(yōu)化子查詢

在MySQL中,盡量使用JOIN來(lái)代替子查詢.因?yàn)樽硬樵冃枰短撞樵?嵌套查詢時(shí)會(huì)建立一張臨時(shí)表,臨時(shí)表的建立和刪除都會(huì)有較大的系統(tǒng)開(kāi)銷(xiāo),而連接查詢不會(huì)創(chuàng)建臨時(shí)表,因此效率比嵌套子查詢高.

2.1.3 使用索引

索引是提高數(shù)據(jù)庫(kù)查詢速度最重要的方法之一,關(guān)于索引可以參高筆者M(jìn)ySQL數(shù)據(jù)庫(kù)索引一文,介紹比較詳細(xì),此處記錄使用索引的三大注意事項(xiàng):

2.1.4 分解表

對(duì)于字段較多的表,如果某些字段使用頻率較低,此時(shí)應(yīng)當(dāng),將其分離出來(lái)從而形成新的表,

2.1.5 中間表

對(duì)于將大量連接查詢的表可以創(chuàng)建中間表,從而減少在查詢時(shí)造成的連接耗時(shí).

2.1.6 增加冗余字段

類(lèi)似于創(chuàng)建中間表,增加冗余也是為了減少連接查詢.

2.1.7 分析表,,檢查表,優(yōu)化表

分析表主要是分析表中關(guān)鍵字的分布,檢查表主要是檢查表中是否存在錯(cuò)誤,優(yōu)化表主要是消除刪除或更新造成的表空間浪費(fèi).

1. 分析表: 使用 ANALYZE 關(guān)鍵字,如ANALYZE TABLE user;

2. 檢查表: 使用 CHECK關(guān)鍵字,如CHECK TABLE user [option]

option 只對(duì)MyISAM有效,共五個(gè)參數(shù)值:

3. 優(yōu)化表:使用OPTIMIZE關(guān)鍵字,如OPTIMIZE [LOCAL|NO_WRITE_TO_BINLOG] TABLE user;

LOCAL|NO_WRITE_TO_BINLOG都是表示不寫(xiě)入日志.,優(yōu)化表只對(duì)VARCHAR,BLOB和TEXT有效,通過(guò)OPTIMIZE TABLE語(yǔ)句可以消除文件碎片,在執(zhí)行過(guò)程中會(huì)加上只讀鎖.

2.2 硬優(yōu)化

2.2.1 硬件三件套

1.配置多核心和頻率高的cpu,多核心可以執(zhí)行多個(gè)線程.

2.配置大內(nèi)存,提高內(nèi)存,即可提高緩存區(qū)容量,因此能減少磁盤(pán)I/O時(shí)間,從而提高響應(yīng)速度.

3.配置高速磁盤(pán)或合理分布磁盤(pán):高速磁盤(pán)提高I/O,分布磁盤(pán)能提高并行操作的能力.

2.2.2 優(yōu)化數(shù)據(jù)庫(kù)參數(shù)

優(yōu)化數(shù)據(jù)庫(kù)參數(shù)可以提高資源利用率,從而提高M(jìn)ySQL服務(wù)器性能.MySQL服務(wù)的配置參數(shù)都在my.cnf或my.ini,下面列出性能影響較大的幾個(gè)參數(shù).

2.2.3 分庫(kù)分表

因?yàn)閿?shù)據(jù)庫(kù)壓力過(guò)大,首先一個(gè)問(wèn)題就是高峰期系統(tǒng)性能可能會(huì)降低,因?yàn)閿?shù)據(jù)庫(kù)負(fù)載過(guò)高對(duì)性能會(huì)有影響。另外一個(gè),壓力過(guò)大把你的數(shù)據(jù)庫(kù)給搞掛了怎么辦?所以此時(shí)你必須得對(duì)系統(tǒng)做分庫(kù)分表 + 讀寫(xiě)分離,也就是把一個(gè)庫(kù)拆分為多個(gè)庫(kù),部署在多個(gè)數(shù)據(jù)庫(kù)服務(wù)上,這時(shí)作為主庫(kù)承載寫(xiě)入請(qǐng)求。然后每個(gè)主庫(kù)都掛載至少一個(gè)從庫(kù),由從庫(kù)來(lái)承載讀請(qǐng)求。

2.2.4 緩存集群

如果用戶量越來(lái)越大,此時(shí)你可以不停的加機(jī)器,比如說(shuō)系統(tǒng)層面不停加機(jī)器,就可以承載更高的并發(fā)請(qǐng)求。然后數(shù)據(jù)庫(kù)層面如果寫(xiě)入并發(fā)越來(lái)越高,就擴(kuò)容加數(shù)據(jù)庫(kù)服務(wù)器,通過(guò)分庫(kù)分表是可以支持?jǐn)U容機(jī)器的,如果數(shù)據(jù)庫(kù)層面的讀并發(fā)越來(lái)越高,就擴(kuò)容加更多的從庫(kù)。但是這里有一個(gè)很大的問(wèn)題:數(shù)據(jù)庫(kù)其實(shí)本身不是用來(lái)承載高并發(fā)請(qǐng)求的,所以通常來(lái)說(shuō),數(shù)據(jù)庫(kù)單機(jī)每秒承載的并發(fā)就在幾千的數(shù)量級(jí),而且數(shù)據(jù)庫(kù)使用的機(jī)器都是比較高配置,比較昂貴的機(jī)器,成本很高。如果你就是簡(jiǎn)單的不停的加機(jī)器,其實(shí)是不對(duì)的。所以在高并發(fā)架構(gòu)里通常都有緩存這個(gè)環(huán)節(jié),緩存系統(tǒng)的設(shè)計(jì)就是為了承載高并發(fā)而生。所以單機(jī)承載的并發(fā)量都在每秒幾萬(wàn),甚至每秒數(shù)十萬(wàn),對(duì)高并發(fā)的承載能力比數(shù)據(jù)庫(kù)系統(tǒng)要高出一到兩個(gè)數(shù)量級(jí)。所以你完全可以根據(jù)系統(tǒng)的業(yè)務(wù)特性,對(duì)那種寫(xiě)少讀多的請(qǐng)求,引入緩存集群。具體來(lái)說(shuō),就是在寫(xiě)數(shù)據(jù)庫(kù)的時(shí)候同時(shí)寫(xiě)一份數(shù)據(jù)到緩存集群里,然后用緩存集群來(lái)承載大部分的讀請(qǐng)求。這樣的話,通過(guò)緩存集群,就可以用更少的機(jī)器資源承載更高的并發(fā)。

一個(gè)完整而復(fù)雜的高并發(fā)系統(tǒng)架構(gòu)中,一定會(huì)包含:各種復(fù)雜的自研基礎(chǔ)架構(gòu)系統(tǒng)。各種精妙的架構(gòu)設(shè)計(jì).因此一篇小文頂多具有拋磚引玉的效果,但是數(shù)據(jù)庫(kù)優(yōu)化的思想差不多就這些了.


網(wǎng)頁(yè)標(biāo)題:mysql怎么優(yōu)化一張表,mysql聯(lián)表查詢優(yōu)化
標(biāo)題網(wǎng)址:http://weahome.cn/article/dscjsih.html

其他資訊

在線咨詢

微信咨詢

電話咨詢

028-86922220(工作日)

18980820575(7×24)

提交需求

返回頂部