MySQL性能管理及架構(gòu)設(shè)計(jì)（一）：什么影響了數(shù)據(jù)庫查詢速度、什么影響了MySQL性能
一、數(shù)據(jù)庫結(jié)構(gòu)優(yōu)化（非常重要）
1.1 數(shù)據(jù)庫結(jié)構(gòu)優(yōu)化目的
1、減少數(shù)據(jù)冗余：（數(shù)據(jù)冗余是指在數(shù)據(jù)庫中存在相同的數(shù)據(jù)，或者某些數(shù)據(jù)可以由其他數(shù)據(jù)計(jì)算得到），注意，盡量減少不代表完全避免數(shù)據(jù)冗余；
2、盡量避免數(shù)據(jù)維護(hù)中出現(xiàn)更新，插入和刪除異常：
?
總結(jié)：要避免異常，需要對數(shù)據(jù)庫結(jié)構(gòu)進(jìn)行范式化設(shè)計(jì)。
3、節(jié)約數(shù)據(jù)存儲空間。
4、提高查詢效率。
10年架構(gòu)師領(lǐng)你架構(gòu)-成長之路-（附面試題（含答案））
（騰訊T3-T4）打造互聯(lián)網(wǎng)PHP架構(gòu)師教程目錄大全，只要你看完，薪資立馬提升2倍（持續(xù)更新）
1.2 數(shù)據(jù)庫結(jié)構(gòu)設(shè)計(jì)步驟
1、需求分析：全面了解產(chǎn)品設(shè)計(jì)的存儲需求、數(shù)據(jù)處理需求、數(shù)據(jù)安全性與完整性；
2、邏輯設(shè)計(jì)（重要）：設(shè)計(jì)數(shù)據(jù)的邏輯存儲結(jié)構(gòu)。數(shù)據(jù)實(shí)體之間的邏輯關(guān)系，解決數(shù)據(jù)冗余和數(shù)據(jù)維護(hù)異常。數(shù)據(jù)范式可以幫助我們設(shè)計(jì)；
3、物理設(shè)計(jì)：表結(jié)構(gòu)設(shè)計(jì)，存儲引擎與列的數(shù)據(jù)類型；
4、維護(hù)優(yōu)化：索引優(yōu)化、存儲結(jié)構(gòu)優(yōu)化。
1.3 數(shù)據(jù)庫范式設(shè)計(jì)與反范式化
傳送門：數(shù)據(jù)庫邏輯設(shè)計(jì)之三大范式通俗理解，一看就懂，書上說的太晦澀
1.4 物理設(shè)計(jì)
?
?
?
f="https://zhuanlan.zhihu.com/p/259786270">相關(guān)傳送門：MySQL中字段類型與合理的選擇字段類型；int(11)最大長度是多少？，varchar最大長度是多少
二、高可用架構(gòu)設(shè)計(jì)
?
?
2.1 讀寫分離
?
MaxScale：實(shí)現(xiàn)MySQL讀寫分離與負(fù)載均衡的中間件利器
三、數(shù)據(jù)庫索引優(yōu)化（非常重要）
3.1 兩種主要數(shù)據(jù)結(jié)構(gòu)：B-tree和Hash
3.1.1 B-tree結(jié)構(gòu)
?
B-tree索引的限制：
?
3.1.2 Hash結(jié)構(gòu)
?
感謝大家一直來支持，這是我準(zhǔn)備的1000粉絲福利
【1000粉絲福利】10年架構(gòu)師分享PHP進(jìn)階架構(gòu)資料，助力大家都能30K
Hash索引的限制：
Hash索引必須進(jìn)行二次查找
Hash索引無法用于排序
Hash索引不支持部分索引查找也不支持范圍查找
Hash索引中Hash碼的計(jì)算可能存在Hash沖突，不適合重復(fù)值很高的列，如性別，身份證比較合適。
3.1.3 MySQL常見索引和各種索引區(qū)別
<pre>PRIMARY KEY（主鍵索引） ALTER TABLE table_name ADD PRIMARY KEY ( column ) UNIQUE(唯一索引) ALTER TABLE table_name ADD UNIQUE (column) INDEX(普通索引) ALTER TABLE table_name ADD INDEX index_name ( column ) FULLTEXT(全文索引) ALTER TABLE table_name ADD FULLTEXT ( column ) 組合索引 ALTER TABLE table_name ADD INDEX index_name ( column1, column2, column3 )</pre>
普通索引：最基本的索引，沒有任何限制
唯一索引：與"普通索引"類似，不同的就是：索引列的值必須唯一，但允許有空值。
主鍵索引：它 是一種特殊的唯一索引，不允許有空值。
全文索引：僅可用于 MyISAM 表，針對較大的數(shù)據(jù)，生成全文索引很耗時(shí)好空間。
組合索引：為了更多的提高mysql效率可建立組合索引，遵循”最左前綴“原則。
3.2 使用索引好處和索引缺陷
3.2.1 為什么要使用索引
1、索引大大減少了存儲引擎需要掃描的數(shù)據(jù)量；
2、索引可以幫助我們進(jìn)行排序以避免使用臨時(shí)表；
3、索引可以把隨機(jī)I/O變?yōu)轫樞騃/O。
3.2.2 索引不是越多越好
1、索引會增加寫操作的成本；
2、太多的索引會增加查詢優(yōu)化器的選擇時(shí)間。
索引就好比一本書的目錄，它會讓你更快的找到內(nèi)容，顯然目錄（索引）并不是越多越好，假如這本書1000頁，而有500頁是目錄，它當(dāng)然效率低，目錄是要占紙張的,而索引是要占磁盤空間的。
3.3 索引優(yōu)化策略
3.3.1 索引列上不能使用表達(dá)式和函數(shù)
?
3.3.2 前綴索引和索引列的選擇性
Innodb索引列最大寬度為667個(gè)字節(jié)(utf-8 差不多255個(gè)字符),MyIsam索引類寬度最大為1000個(gè)字節(jié)，于是出現(xiàn)前綴索引，索引的選擇性。
對于列的值較長，比如BLOB、TEXT、VARCHAR，就必須建立前綴索引，即將值的前一部分作為索引。這樣既可以節(jié)約空間，又可以提高查詢效率。但無法使用前綴索引做 ORDER BY 和 GROUP BY，也無法使用前綴索引做覆蓋掃描。
語法： ALTER TABLE table_name ADD KEY(column_name(prefix_length))
?
如何選擇索引列的順序：
1、經(jīng)常會被使用到的列優(yōu)先（選擇性差的列不適合，如性別，查詢優(yōu)化器可能會認(rèn)為全表掃描性能更好）；
2、選擇性高的列優(yōu)先；
3、寬度小的列優(yōu)先（一頁中存儲的索引越多，降低I/O，查找越快）；
大廠2000道面試題（含答案）
PHP面試題匯總，看完這些面試題助力你面試成功，工資必有20-25K
3.3.3 組合/聯(lián)合索引策略
如果索引了多列，要遵守最左前綴法則。指的是查詢從索引的最左前列開始并且不跳過索引中的列。 "http://www.uml.org.cn/sjjm/201107145.asp#nav-4-2">深入理解請移步：最左前綴原理與相關(guān)優(yōu)化
3.3.4 覆蓋索引策略
跟組合索引有點(diǎn)類似，如果索引包含所有滿足查詢需要的數(shù)據(jù)的索引則成為覆蓋索引(Covering Index)，也就是平時(shí)所說的不需要回表操作。即索引的葉子節(jié)點(diǎn)上面包含了他們索引的數(shù)據(jù)(hash索引不可以)。
判斷標(biāo)準(zhǔn)：使用explain，可以通過輸出的extra列來判斷，對于一個(gè)索引覆蓋查詢，顯示為using index,MySQL查詢優(yōu)化器在執(zhí)行查詢前會決定是否有索引覆蓋查詢。
優(yōu)點(diǎn):
1、可以優(yōu)化緩存,減少磁盤IO操作； 2、可以減少隨機(jī)IO,變隨機(jī)IO操作變?yōu)轫樞騃O操作； 3、可以避免對InnoDB主鍵索引的二次查詢； 4、可以避免MyISAM表進(jìn)行系統(tǒng)調(diào)用；
無法使用覆蓋索引的情況：
1、存儲引擎不支持覆蓋索引； 2、查詢中使用了太多的列（如SELECT * ）； 3、使用了雙%號的like查詢（底層API所限制）；
3.3.5 SQL索引優(yōu)化總結(jié)口訣（套路重點(diǎn)）
全值匹配我最愛，最左前綴要遵守； 帶頭大哥不能死，中間兄弟不能斷； 索引列上不計(jì)算，范圍之后全失效； LIKE百分寫最右，覆蓋索引不寫 *； 不等空值還有or，索引失效要少用； 字符單引不可丟，SQL高級也不難 ；
3.4 使用索引來優(yōu)化查詢
3.4.1 利用索引排序
1、group by 實(shí)質(zhì)是先排序后分組，遵照索引的最佳左前綴。；
2、索引中所有列的方向(升序、降序)和Order By子句完全一致；
3、當(dāng)無法使用索引列，增大max_length_for_sort_data參數(shù)的設(shè)置+增大sort_buffer_size參數(shù)的設(shè)置；
4、如果最左列使用了范圍，則排序會失效；
5、where 高于having，能寫在where限定的條件就不要去having去限定了
3.5 索引的維護(hù)和優(yōu)化
3.5.1 刪除重復(fù)索引
?
注：主鍵約束相當(dāng)于(唯一約束 + 非空約束)
一張表中最多有一個(gè)主鍵約束,如果設(shè)置多個(gè)主鍵,就會出現(xiàn)如下提示：Multiple primary key defined!!!
3.5.2 刪除冗余索引
?
檢查工具：pt-duplicate-key-checker
喜歡我的文章就關(guān)注我吧，持續(xù)更新中.....
以上內(nèi)容希望幫助到大家，很多PHPer在進(jìn)階的時(shí)候總會遇到一些問題和瓶頸，業(yè)務(wù)代碼寫多了沒有方向感，不知道該從那里入手去提升，對此我整理了一些資料，包括但不限于：分布式架構(gòu)、高可擴(kuò)展、高性能、高并發(fā)、服務(wù)器性能調(diào)優(yōu)、TP6，laravel，YII2，Redis，Swoole、Swoft、Kafka、Mysql優(yōu)化、shell腳本、Docker、微服務(wù)、Nginx等多個(gè)知識點(diǎn)高級進(jìn)階干貨需要的可以免費(fèi)分享給大家，需要的可以點(diǎn)擊進(jìn)入暗號：知乎。
