單表優(yōu)化

最佳左前綴原則為，保持索引的定義和使用順序的一致性

將含In的范圍查詢，放到where條件語(yǔ)句的最后。

索引需要逐步優(yōu)化

兩表優(yōu)化

小表驅(qū)動(dòng)大表

避免索引失效的一些原則：

復(fù)合索引，不要跨列或無(wú)序使用（最佳左前綴）

復(fù)合索引，盡量使用全索引匹配。就是盡量避免復(fù)合索引有三列，而只用到兩列。

復(fù)合索引不能使用范圍查詢（!= <>）或is null (is not null),否則自身及右側(cè)索引全部失效。

不要對(duì)索引進(jìn)行操作（計(jì)算，函數(shù)，類型轉(zhuǎn)換），否則索引失效。

select .. where A.x*3 = ... -- A.x列存在索引，索引失效

==注意：SQL優(yōu)化，是一種概率層次的優(yōu)化，原因是服務(wù)層中有SQL優(yōu)化器，可能會(huì)影響我們的優(yōu)化，出現(xiàn)與我們預(yù)期不符的情況。==

盡量不要使用or，會(huì)使左右兩邊索引全部失效。

其他的優(yōu)化方法

exist 和 in ：如果主查詢的數(shù)據(jù)極大，則使用in，如果子查詢的數(shù)據(jù)大，則使用exist。

order by ：選擇使用單路還是雙路；調(diào)整buffer容量的大小；避免使用select *...；復(fù)合索引不要跨列使用。

SQL慢查詢?nèi)罩?#xff1a;

MySQL提供的一種日志記錄，用于記錄MySQL中響應(yīng)超過(guò)閾值的SQL語(yǔ)句（默認(rèn)是10秒），慢查詢?nèi)罩灸J(rèn)是關(guān)閉的，建議：開(kāi)發(fā)時(shí)打開(kāi)，最終部署時(shí)關(guān)閉。

• 查看慢查詢?nèi)罩臼欠耖_(kāi)啟

sql show variables like '%slow_query_log%';

• 臨時(shí)開(kāi)啟慢查詢?nèi)罩?在內(nèi)存中開(kāi)啟)，將服務(wù)重啟后關(guān)閉。

set global slow_query_log = 1;

• 永久開(kāi)啟慢查詢?nèi)罩?#xff0c;修改MySQL配置文件

ini [mysqld] ... + slow_query_log = 1 + slow_query_log_file = 日志存放地址

• 查看慢查詢閾值并修改

```sql -- 查看慢查詢閾值時(shí)間 show variables like '%long_query_log%'

-- 修改閾值（臨時(shí)修改），重新登錄MySQL后生效（不需要重啟服務(wù)） set global long_query_time = 5

--修改閾值（永久修改），修改MySQL配置文件，重啟服務(wù)生效 [mysqld] ... + long_query_time = 5 ```

• 查詢超過(guò)閾值的SQL

sql show global status like '%slow_queries%' -- 當(dāng)然這種方法只能查看超過(guò)閾值的SQL條數(shù)，具體的SQL語(yǔ)句還是要通過(guò)查看上面設(shè)置的慢查詢?nèi)罩緛?lái)確定

• 使用mysqldumpslow工具來(lái)查詢慢SQL

? 通過(guò)查看日志的方式雖然可行，但是當(dāng)我們的SQL語(yǔ)句多了之后，慢SQL語(yǔ)句也隨之變多，這樣一來(lái)查看慢sql日志就成了一件費(fèi)時(shí)費(fèi)力的事，且日志內(nèi)容較多，容易造成混亂。使用mysqldumpslow工具可以主動(dòng)的過(guò)濾出我們希望查看的慢SQL語(yǔ)句，比如可以對(duì)嗎慢SQL語(yǔ)句進(jìn)行排序，進(jìn)行正則匹配，只取10條數(shù)據(jù)等等。

PROFILES分析海量數(shù)據(jù)

-- 查看profiles是否開(kāi)啟 show variables like '%profiling%';

查看SQL語(yǔ)句執(zhí)行的Query_id以及持續(xù)時(shí)間

show profiles;

? 注意： show profiles可以看到profiling打開(kāi)之后的SQL語(yǔ)句，會(huì)顯示ID，持續(xù)時(shí)間，以及SQL語(yǔ)句，但是我們無(wú)法精準(zhǔn)的看到各個(gè)硬件消費(fèi)的時(shí)間。

查看SQL語(yǔ)句執(zhí)行的具體硬件消費(fèi)情況

-- 查看全部信息 show profile all for query Query_id;-- 查看部分信息 show profile cpu, block io for query Query_id;

全局查詢?nèi)罩?/h2>全局查看日志會(huì)記錄開(kāi)啟之后的全部SQL語(yǔ)句，因此比較耗費(fèi)性能。所以我們僅在調(diào)優(yōu)、開(kāi)發(fā)時(shí)使用。而且全局查詢?nèi)罩拘畔⑤^少，所以使用較少。

使用方法

-- 查看是否開(kāi)啟 show variables like '%general_log%';-- 開(kāi)啟全局查詢?nèi)罩?SET GLOBAL general_log = 1; SET GLOBAL log_output='table'; -- 表明我們將SQL存在表里。mysql.general_log表。SET GLOBAL log_output='file'; SET GLOBAL general_log_file='目錄/general.log'; -- 表明將SQL存儲(chǔ)在文件里 SET GLOBAL general_log = 1; -- 如果無(wú)效就需要再開(kāi)啟一下

鎖機(jī)制

MyISAM只支持表鎖，InnoDB支持表鎖和行鎖

-- 加鎖 lock table 表1 read/write, 表1 read/write...; -- 釋放鎖 unlock tables;-- 查看表加鎖情況 show open tables;

讀鎖（共享鎖）

會(huì)話1 對(duì) A 表加 讀 鎖

• 會(huì)話1：可以讀取A表數(shù)據(jù)，但不能修改。不可以讀寫(xiě)其它表數(shù)據(jù)。
• 其它會(huì)話：可以讀取A表數(shù)據(jù)，如果修改的話必須要等會(huì)話1將讀鎖釋放。可以讀寫(xiě)其它表數(shù)據(jù)。

| | 會(huì)話1 | 其他會(huì)話 | | ------ | ---------------- | --------------------------------------- | | A表 | 可以讀，不可以寫(xiě) | 可以讀取，想要更新必須等待會(huì)話1釋放表鎖 | | 其他表 | 不可以讀寫(xiě) | 可以讀寫(xiě) |

寫(xiě)鎖（互斥鎖）

會(huì)話1 對(duì) A 表加 寫(xiě) 鎖

• 會(huì)話1：可以對(duì)A表進(jìn)行增刪改查，但是不能操作其他表。
• 其他會(huì)話：想要讀寫(xiě)A表，必須等會(huì)話1釋放寫(xiě)鎖，可以正常操作其他表。

關(guān)閉自動(dòng)提交

-- 以下三種方式都可以 set autocommit=0; start transaction; begin;

主從復(fù)制

集群和分布式：用板磚來(lái)比喻，一開(kāi)始一個(gè)人負(fù)責(zé)搬磚和運(yùn)磚，后來(lái)工頭嫌他慢，又找了幾個(gè)人幫他一起搬磚運(yùn)磚，這叫集群（多個(gè)服務(wù)器支持一個(gè)項(xiàng)目）。后來(lái)工頭還不滿足，就讓一群人專門(mén)負(fù)責(zé)搬磚，另一群人專門(mén)負(fù)責(zé)運(yùn)磚，這叫分布式（一個(gè)項(xiàng)目劃分為幾個(gè)部分）。

主從復(fù)制大概過(guò)程：

總結(jié)

以上是生活随笔為你收集整理的mysql 查看表是否存在_MySQL优化篇二的全部?jī)?nèi)容，希望文章能夠幫你解決所遇到的問(wèn)題。

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