一、方案概述

優(yōu)化現(xiàn)有mysql數(shù)據(jù)庫(kù)。

二、方案優(yōu)缺點(diǎn)

優(yōu)點(diǎn)：不影響現(xiàn)有業(yè)務(wù)，源程序不需要修改代碼，成本最低。

缺點(diǎn)：有上限，數(shù)據(jù)量過(guò)億就不行了。

三、方案實(shí)施細(xì)則

1.數(shù)據(jù)庫(kù)設(shè)計(jì)和表創(chuàng)建時(shí)就要考慮性能

設(shè)計(jì)表時(shí)要注意：

(1)表字段避免null值出現(xiàn)，null值很難查詢(xún)優(yōu)化且占用額外的索引空間，推薦默認(rèn)數(shù)字0代替null。

(2)盡量使用INT而非BIGINT，如果非負(fù)則加上UNSIGNED(這樣數(shù)值容量會(huì)擴(kuò)大一倍)，當(dāng)然能使用TINYINT、SMALLINT、MEDIUM_INT更好。

(3)使用枚舉或整數(shù)代替字符串類(lèi)型

(4)盡量使用TIMESTAMP而非DATETIME

(5)單表不要有太多字段，建議在20以?xún)?nèi)

(6)用整型來(lái)存IP

2.創(chuàng)建索引

(1)索引并不是越多越好，要根據(jù)查詢(xún)有針對(duì)性的創(chuàng)建，考慮在WHERE和ORDER BY命令上涉及的列建立索引，可根據(jù)EXPLAIN來(lái)查看是否用了索引還是全表掃描

(2)應(yīng)盡量避免在WHERE子句中對(duì)字段進(jìn)行NULL值判斷，否則將導(dǎo)致引擎放棄使用索引而進(jìn)行全表掃描

(3)值分布很稀少的字段不適合建索引，例如"性別"這種只有兩三個(gè)值的字段

(4)字符字段只建前綴索引

(5)字符字段最好不要做主鍵

(6)不用外鍵，由程序保證約束

(7)盡量不用UNIQUE，由程序保證約束

(8)使用多列索引時(shí)主意順序和查詢(xún)條件保持一致，同時(shí)刪除不必要的單列索引

簡(jiǎn)言之就是使用合適的數(shù)據(jù)類(lèi)型，選擇合適的索引

選擇合適的數(shù)據(jù)類(lèi)型

(1)使用可存下數(shù)據(jù)的最小的數(shù)據(jù)類(lèi)型，整型 < date,time < char,varchar < blob

(2)使用簡(jiǎn)單的數(shù)據(jù)類(lèi)型，整型比字符處理開(kāi)銷(xiāo)更小，因?yàn)樽址谋容^更復(fù)雜。如，int類(lèi)型存儲(chǔ)時(shí)間類(lèi)型，bigint類(lèi)型轉(zhuǎn)ip函數(shù)

(3)使用合理的字段屬性長(zhǎng)度，固定長(zhǎng)度的表會(huì)更快。使用enum、char而不是varchar

(4)盡可能使用not null定義字段

(5)盡量少用text，非用不可最好分表

選擇合適的索引列

(1)查詢(xún)頻繁的列，在where，group by，order by，on從句中出現(xiàn)的列

(2)where條件中，>=，between，in，以及l(fā)ike 字符串+通配符(%)出現(xiàn)的列

(3)長(zhǎng)度小的列，索引字段越小越好，因?yàn)閿?shù)據(jù)庫(kù)的存儲(chǔ)單位是頁(yè)，一頁(yè)中能存下的數(shù)據(jù)越多越好

(4)離散度大(不同的值多)的列，放在聯(lián)合索引前面。查看離散度，通過(guò)統(tǒng)計(jì)不同的列值來(lái)實(shí)現(xiàn)，count越大，離散程度越高

3.sql的編寫(xiě)需要注意優(yōu)化

(1)使用limit對(duì)查詢(xún)結(jié)果的記錄進(jìn)行限定

(2)避免select *，將需要查找的字段列出來(lái)

(3)使用連接(join)來(lái)代替子查詢(xún)

(4)拆分大的delete或insert語(yǔ)句

(5)可通過(guò)開(kāi)啟慢查詢(xún)?nèi)罩緛?lái)找出較慢的SQL

(6)不做列運(yùn)算：SELECT id WHERE age + 1 = 10，任何對(duì)列的操作都將導(dǎo)致表掃描，它包括數(shù)據(jù)庫(kù)教程函數(shù)、計(jì)算表達(dá)式等等，查詢(xún)時(shí)要盡可能將操作移至等號(hào)右邊

(7)sql語(yǔ)句盡可能簡(jiǎn)單：一條sql只能在一個(gè)cpu運(yùn)算；大語(yǔ)句拆小語(yǔ)句，減少鎖時(shí)間；一條大sql可以堵死整個(gè)庫(kù)

(8)OR改寫(xiě)成IN：OR的效率是n級(jí)別，IN的效率是log(n)級(jí)別，in的個(gè)數(shù)建議控制在200以?xún)?nèi)(9)不用函數(shù)和觸發(fā)器，在應(yīng)用程序?qū)崿F(xiàn)

(10)避免%xxx式查詢(xún)

(11)少用JOIN

(12)使用同類(lèi)型進(jìn)行比較，比如用'123'和'123'比，123和123比

(13)盡量避免在WHERE子句中使用!=或<>操作符，否則將引擎放棄使用索引而進(jìn)行全表掃描

(14)對(duì)于連續(xù)數(shù)值，使用BETWEEN不用IN：SELECT id FROM t WHERE num BETWEEN 1 AND 5

(15)列表數(shù)據(jù)不要拿全表，要使用LIMIT來(lái)分頁(yè)，每頁(yè)數(shù)量也不要太大

4.分區(qū)

MySQL在5.1版引入的分區(qū)是一種簡(jiǎn)單的水平拆分，用戶(hù)需要在建表的時(shí)候加上分區(qū)參數(shù)，對(duì)應(yīng)用是透明的無(wú)需修改代碼

對(duì)用戶(hù)來(lái)說(shuō)，分區(qū)表是一個(gè)獨(dú)立的邏輯表，但是底層由多個(gè)物理子表組成，實(shí)現(xiàn)分區(qū)的代碼實(shí)際上是通過(guò)對(duì)一組底層表的對(duì)象封裝，但對(duì)SQL層來(lái)說(shuō)是一個(gè)完全封裝底層的黑盒子。MySQL實(shí)現(xiàn)分區(qū)的方式也意味著索引也是按照分區(qū)的子表定義，沒(méi)有全局索引

用戶(hù)的SQL語(yǔ)句是需要針對(duì)分區(qū)表做優(yōu)化，SQL條件中要帶上分區(qū)條件的列，從而使查詢(xún)定位到少量的分區(qū)上，否則就會(huì)掃描全部分區(qū)，可以通過(guò)EXPLAIN PARTITIONS來(lái)查看某條SQL語(yǔ)句會(huì)落在那些分區(qū)上，從而進(jìn)行SQL優(yōu)化。

分區(qū)的好處是：

(1)可以讓單表存儲(chǔ)更多的數(shù)據(jù)

(2)分區(qū)表的數(shù)據(jù)更容易維護(hù)，可以通過(guò)清楚整個(gè)分區(qū)批量刪除大量數(shù)據(jù)，也可以增加新的分區(qū)來(lái)支持新插入的數(shù)據(jù)。另外，還可以對(duì)一個(gè)獨(dú)立分區(qū)進(jìn)行優(yōu)化、檢查、修復(fù)等操作

(3)部分查詢(xún)能夠從查詢(xún)條件確定只落在少數(shù)分區(qū)上，速度會(huì)很快

(4)分區(qū)表的數(shù)據(jù)還可以分布在不同的物理設(shè)備上，從而搞笑利用多個(gè)硬件設(shè)備

(5)可以使用分區(qū)表賴(lài)避免某些特殊瓶頸，例如InnoDB單個(gè)索引的互斥訪(fǎng)問(wèn)、ext3文件系統(tǒng)的inode鎖競(jìng)爭(zhēng)

(6)可以備份和恢復(fù)單個(gè)分區(qū)

分區(qū)的限制和缺點(diǎn)：

(1)一個(gè)表最多只能有1024個(gè)分區(qū)

(2)如果分區(qū)字段中有主鍵或者唯一索引的列，那么所有主鍵列和唯一索引列都必須包含進(jìn)來(lái)

(3)分區(qū)表無(wú)法使用外鍵約束

(4)NULL值會(huì)使分區(qū)過(guò)濾無(wú)效

(5)所有分區(qū)必須使用相同的存儲(chǔ)引擎

分區(qū)的類(lèi)型：

(1)RANGE分區(qū)：基于屬于一個(gè)給定連續(xù)區(qū)間的列值，把多行分配給分區(qū)

(2)LIST分區(qū)：類(lèi)似于按RANGE分區(qū)，區(qū)別在于LIST分區(qū)是基于列值匹配一個(gè)離散值集合中的某個(gè)值來(lái)進(jìn)行選擇

(3)HASH分區(qū)：基于用戶(hù)定義的表達(dá)式的返回值來(lái)進(jìn)行選擇的分區(qū)，該表達(dá)式使用將要插入到表中的這些行的列值進(jìn)行計(jì)算。這個(gè)函數(shù)可以包含MySQL中有效的、產(chǎn)生非負(fù)整數(shù)值的任何表達(dá)式

(4)KEY分區(qū)：類(lèi)似于按HASH分區(qū)，區(qū)別在于KEY分區(qū)只支持計(jì)算一列或多列，且MySQL服務(wù)器提供其自身的哈希函數(shù)。必須有一列或多列包含整數(shù)值

(5)具體關(guān)于mysql分區(qū)的概念請(qǐng)自行查詢(xún)官方文檔。

5.分表

分表就是把一張大表，按照如上過(guò)程都優(yōu)化了，還是查詢(xún)卡死，那就把這個(gè)表分成多張表，把一次查詢(xún)分成多次查詢(xún)，然后把結(jié)果組合返回給用戶(hù)。

分表分為垂直拆分和水平拆分，通常以某個(gè)字段做拆分項(xiàng)。比如以id字段拆分為100張表： 表名為 tableName_id%100

但：分表需要修改源程序代碼，會(huì)給開(kāi)發(fā)帶來(lái)大量工作，極大的增加了開(kāi)發(fā)成本，故：只適合在開(kāi)發(fā)初期就考慮到了大量數(shù)據(jù)存在，做好了分表處理，不適合應(yīng)用上線(xiàn)了再做修改，成本太高！！！而且選擇這個(gè)方案，都不如選擇我提供的第二第三個(gè)方案的成本低！故不建議采用。

6.分庫(kù)

把一個(gè)數(shù)據(jù)庫(kù)分成多個(gè)，建議做個(gè)讀寫(xiě)分離就行了，真正的做分庫(kù)也會(huì)帶來(lái)大量的開(kāi)發(fā)成本，得不償失！不推薦使用。

此方案為個(gè)人綜合總結(jié)，僅供參考！

總結(jié)

以上是生活随笔為你收集整理的mysql数据库技术方案,MySql数据库优化方案的全部?jī)?nèi)容，希望文章能夠幫你解決所遇到的問(wèn)題。

如果覺(jué)得生活随笔網(wǎng)站內(nèi)容還不錯(cuò)，歡迎將生活随笔推薦給好友。