當(dāng)前位置：首頁(yè) > 运维知识 > 数据库 >内容正文

数据库

阿里P7架构师谈：MySQL慢查询优化、索引优化、以及表等优化总结

發(fā)布時(shí)間：2025/3/21 数据库 31 豆豆

生活随笔收集整理的這篇文章主要介紹了阿里P7架构师谈：MySQL慢查询优化、索引优化、以及表等优化总结小編覺(jué)得挺不錯(cuò)的,現(xiàn)在分享給大家,幫大家做個(gè)參考.

MySQL優(yōu)化概述

MySQL數(shù)據(jù)庫(kù)常見(jiàn)的兩個(gè)瓶頸是：CPU和I/O的瓶頸。

CPU在飽和的時(shí)候一般發(fā)生在數(shù)據(jù)裝入內(nèi)存或從磁盤上讀取數(shù)據(jù)時(shí)候。

磁盤I/O瓶頸發(fā)生在裝入數(shù)據(jù)遠(yuǎn)大于內(nèi)存容量的時(shí)候，如果應(yīng)用分布在網(wǎng)絡(luò)上，那么查詢量相當(dāng)大的時(shí)候那么平瓶頸就會(huì)出現(xiàn)在網(wǎng)絡(luò)上。

我們可以用mpstat, iostat, sar和vmstat來(lái)查看系統(tǒng)的性能狀態(tài)。除了服務(wù)器硬件的性能瓶頸，對(duì)于MySQL系統(tǒng)本身，我們可以使用工具來(lái)優(yōu)化數(shù)據(jù)庫(kù)的性能。

MySQL優(yōu)化方案

Mysql的優(yōu)化，大體可以分為三部分：索引的優(yōu)化，sql語(yǔ)句的優(yōu)化，表的優(yōu)化

索引優(yōu)化

1.索引

一般的應(yīng)用系統(tǒng)，讀寫比例在10：1左右，而且插入操作和一般的更新操作很少出現(xiàn)性能問(wèn)題，在生產(chǎn)環(huán)境中，我們遇到最多的也是最容易出現(xiàn)問(wèn)題的，還是一些復(fù)雜的查詢操作，因此對(duì)查詢語(yǔ)句的優(yōu)化是重中之重，加速查詢最好的方法就是索引。

索引：簡(jiǎn)單的說(shuō)，相當(dāng)于圖書的目錄，可以幫助用戶快速的找到需要的內(nèi)容。

在MySQL中也叫做“鍵”，是存儲(chǔ)引擎用于快速找到記錄的一種數(shù)據(jù)結(jié)構(gòu)。能夠大大提高查詢效率。特別是當(dāng)數(shù)據(jù)量非常大，查詢涉及多個(gè)表時(shí)，使用索引往往能使查詢速度加快成千上萬(wàn)倍。

總結(jié)：索引的目的在于提高查詢效率，與我們查詢圖書所用的目錄是一個(gè)道理：先定位到章，然后定位到該章下的一個(gè)小結(jié)，然后找到頁(yè)數(shù)。相似的例子還有：查字典，查地圖等。

2.索引類型

普通索引

是最基本的索引，它沒(méi)有任何限制。

唯一索引

與前面的普通索引類似，不同的就是：索引列的值必須唯一，但允許有空值。如果是組合索引，則列值的組合必須唯一。

組合索引

指多個(gè)字段上創(chuàng)建的索引，只有在查詢條件中使用了創(chuàng)建索引時(shí)的第一個(gè)字段，索引才會(huì)被使用。

主鍵索引

是一種特殊的唯一索引，一個(gè)表只能有一個(gè)主鍵，不允許有空值。一般是在建表的時(shí)候同時(shí)創(chuàng)建主鍵索引

全文索引

主要用來(lái)查找文本中的關(guān)鍵字，而不是直接與索引中的值相比較。fulltext索引跟其它索引大不相同，它更像是一個(gè)搜索引擎，而不是簡(jiǎn)單的where語(yǔ)句的參數(shù)匹配。fulltext索引配合match against操作使用，而不是一般的where語(yǔ)句加like。它可以在create table，alter table ，create index使用，不過(guò)目前只有char、varchar，text 列上可以創(chuàng)建全文索引。值得一提的是，在數(shù)據(jù)量較大時(shí)候，現(xiàn)將數(shù)據(jù)放入一個(gè)沒(méi)有全局索引的表中，然后再用CREATE index創(chuàng)建fulltext索引，要比先為一張表建立fulltext然后再將數(shù)據(jù)寫入的速度快很多。

3.索引優(yōu)化

只要列中含有NULL值，就最好不要在此例設(shè)置索引，復(fù)合索引如果有NULL值，此列在使用時(shí)也不會(huì)使用索引
盡量使用短索引，如果可以，應(yīng)該制定一個(gè)前綴長(zhǎng)度
對(duì)于經(jīng)常在where子句使用的列，最好設(shè)置索引，這樣會(huì)加快查找速度
對(duì)于有多個(gè)列where或者order by子句的，應(yīng)該建立復(fù)合索引
對(duì)于like語(yǔ)句，以%或者‘-’開(kāi)頭的不會(huì)使用索引，以%結(jié)尾會(huì)使用索引
盡量不要在列上進(jìn)行運(yùn)算（函數(shù)操作和表達(dá)式操作）
盡量不要使用not in和<>操作

?

SQL慢查詢的優(yōu)化

1.如何捕獲低效sql

1）slow_query_log

這個(gè)參數(shù)設(shè)置為ON，可以捕獲執(zhí)行時(shí)間超過(guò)一定數(shù)值的SQL語(yǔ)句。

2）ong_query_time

當(dāng)SQL語(yǔ)句執(zhí)行時(shí)間超過(guò)此數(shù)值時(shí)，就會(huì)被記錄到日志中，建議設(shè)置為1或者更短。

3）slow_query_log_file

記錄日志的文件名。

4）log_queries_not_using_indexes

這個(gè)參數(shù)設(shè)置為ON，可以捕獲到所有未使用索引的SQL語(yǔ)句，盡管這個(gè)SQL語(yǔ)句有可能執(zhí)行得挺快。

2.慢查詢優(yōu)化的基本步驟

1)先運(yùn)行看看是否真的很慢，注意設(shè)置SQL_NO_CACHE

2）where條件單表查，鎖定最小返回記錄表。這句話的意思是把查詢語(yǔ)句的where都應(yīng)用到表中返回的記錄數(shù)最小的表開(kāi)始查起，單表每個(gè)字段分別查詢，看哪個(gè)字段的區(qū)分度最高

3)explain查看執(zhí)行計(jì)劃，是否與1預(yù)期一致（從鎖定記錄較少的表開(kāi)始查詢）

4)order by limit 形式的sql語(yǔ)句讓排序的表優(yōu)先查

5)了解業(yè)務(wù)方使用場(chǎng)景

6)加索引時(shí)參照建索引的幾大原則

7)觀察結(jié)果，不符合預(yù)期繼續(xù)從1開(kāi)始分析

2.優(yōu)化原則

查詢時(shí)，能不要*就不用*，盡量寫全字段名
大部分情況連接效率遠(yuǎn)大于子查詢
多使用explain和profile分析查詢語(yǔ)句
查看慢查詢?nèi)罩?#xff0c;找出執(zhí)行時(shí)間長(zhǎng)的sql語(yǔ)句優(yōu)化
多表連接時(shí)，盡量小表驅(qū)動(dòng)大表，即小表 join 大表
在千萬(wàn)級(jí)分頁(yè)時(shí)使用limit
對(duì)于經(jīng)常使用的查詢，可以開(kāi)啟緩存

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

表的字段盡可能用NOT NULL
字段長(zhǎng)度固定的表查詢會(huì)更快
把數(shù)據(jù)庫(kù)的大表按時(shí)間或一些標(biāo)志分成小表
將表拆分

數(shù)據(jù)表拆分：主要就是垂直拆分和水平拆分。

水平切分:將記錄散列到不同的表中，各表的結(jié)構(gòu)完全相同，每次從分表中查詢, 提高效率。

垂直切分:將表中大字段單獨(dú)拆分到另外一張表, 形成一對(duì)一的關(guān)系。

總之：

Mysql的優(yōu)化主要就在于：索引的優(yōu)化，sql語(yǔ)句的優(yōu)化，表的優(yōu)化，在高并發(fā)網(wǎng)絡(luò)環(huán)境下，除了優(yōu)化數(shù)據(jù)庫(kù)外，還會(huì)涉及到分布式緩存，CDN，數(shù)據(jù)庫(kù)讀寫分離等高并發(fā)優(yōu)化技術(shù)。

以上就是MySQL的優(yōu)化技術(shù)方案，以下是最新阿里P7架構(gòu)師談架構(gòu)設(shè)計(jì)系列。

加群：878249276，獲取往期Java高級(jí)架構(gòu)資料、源碼、筆記、視頻
Dubbo、Redis、Netty、zookeeper、Spring cloud、分布式、高并發(fā)等架構(gòu)技術(shù)