mysql的邏輯分層：連接層 服務層 引擎層 存儲層

引擎層主要分為兩類：InnoDB、MyIsam(使用show engines;查看)

InnoDB是事務優(yōu)先的，會進行行鎖，適合高并發(fā)操作

MyIsam是性能優(yōu)先的，進行表鎖

sql優(yōu)化點：等待時間長，執(zhí)行時間長，性能低下，多表連接查詢，服務器參數(shù)設置不合理(線程數(shù)、緩沖區(qū))

編寫過程 select ... from ... join... on ... where ... group by ... having... order by...

解析過程 from ... on ... join... where ...group by... having...select... order by...

具體內(nèi)容可以看步步深入：MySQL架構總覽->查詢執(zhí)行流程->SQL解析順序 - AnnsShadoW - 博客園?www.cnblogs.com

sql優(yōu)化主要是優(yōu)化索引，索引是一個高效查詢數(shù)據(jù)的數(shù)據(jù)結構，索引一般是一顆樹(樹：b樹或者hash樹等)示例如下。

索引的弊端也是有的：索引本身需要空間可以存放內(nèi)存或者硬盤內(nèi)部；索引在有的情況下不適用：數(shù)據(jù)量少，頻繁更新的數(shù)據(jù)，很少使用的字段，索引會提高查詢但是減低增刪改效率(但是數(shù)據(jù)庫的大部分操作是查詢，增刪改較少)。

優(yōu)勢：提高查詢效率(降低IO使用率)、降低CPU使用率(...order by xx desc，直接在B樹排好了)。

B樹在磁盤中的結構是怎么樣的呢？

三層Btree可以存放上百萬條數(shù)據(jù)，每一層可以超過兩個分支。B樹一般是值B+樹，數(shù)據(jù)全部放到葉節(jié)點中，B+樹中查詢?nèi)我鈹?shù)據(jù)都是n次(n為樹的高度)。

B樹與B+樹的區(qū)別，可以看以下文章https://blog.csdn.net/login_sonata/article/details/75268075?blog.csdn.net

索引的分類：單值索引(單列，一個表可以有多個單值索引)、唯一索引(不能重復)、復合索引(多個列構成的索引，符合索引不一定是多個列全中)。

第一種

# 單值索引 create 索引類型 索引名 on 表(字段)

create index dept_index on tb(dept);

# 唯一索引

create unique index name_index on tb(name);

# 復合索引

create index dept_name_index on tb(dept, name);

第二種

# alter table 表名 索引類型 索引名(字段)

# 單值索引

alter table tb add index dept_index(dept);

# 唯一索引

alter table tb add unique index name_index(name);

# 符合索引

alter table tb add index dept_name_index(dept, name);

事務操作只對DML增刪改有效，DDL會自動提交。

主鍵索引是唯一索引的一種，但是主鍵索引不能為null。

刪除索引

drop index 索引名 on 表名

drop index name_index on tb;

SQL優(yōu)化問題

1、分析SQL的執(zhí)行計劃：explain。可以模擬SQL優(yōu)化器優(yōu)化SQL執(zhí)行語句。

2、mysql查詢自動優(yōu)化，會干擾優(yōu)化。

explain一般返回這么幾個字段：id，select_type，type

不過SQL執(zhí)行計劃不同版本不一樣，看具體版本，不用糾結。

這里用explain返回結果時，id越大越先執(zhí)行(本質是嵌套查詢)，id相同時，順序執(zhí)行。

select_type：primary(主查詢)、subquery(子查詢)、simple(簡單查詢，不包含子查詢和union)、derived(衍生查詢，用到了臨時表。在from子查詢中只有一張表，或者union前一個查詢)

type：索引類型

一般system > const > eq_ref > ref > range > index > all性能依次降低，實際能達到ref > range即可。前提是必須建立索引。

這個理解即可。

eq_ref：查詢結果1v1；ref：查詢結果1vN(N>=0)；range(where between，<=, >=，in等)，其中in在查詢數(shù)據(jù)在一半以上時，退化為all或index。盡量避免使用in；index：查某一索引字段的；all：全表所有字段或者沒有索引的字段。

下期講索引優(yōu)化方案。

總結

以上是生活随笔為你收集整理的数mysql据分析优化_从零开始学习数据分析-mysql架构与优化理论的全部內(nèi)容，希望文章能夠幫你解決所遇到的問題。

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