1.索引優化

1.1 索引的分類

分類角度索引名稱數據結構B+樹，Hash索引，B-Tree 等

存儲層面聚簇索引，非聚簇索引

邏輯層面主鍵索引，普通索引，組合索引，唯一索引，空間索引

1.2 回表

假設我們執行一條查詢語句

select * from person where ID = 6,因為直接使用的是主鍵ID查詢，所以就會用主鍵索引，由于主鍵索引直接關聯了整行所有數據，所以，引擎只要執行一次就能查詢出結果。

如果執行的sql語句是非主鍵索引

select * from person where age = 18

上述語句會走age的普通索引，索引先根據age搜索等于18的索引記錄，找到ID=10的記錄，然后再到主鍵索引搜索一次，然后拿出需要查詢的數據。

從普通索引查出主鍵索引，然后查詢出數據的過程叫做回表。由于回表需要多執行一次查詢，這也是為什么主鍵索引要比普通索引要快的原因，所以，我們要盡量使用主鍵查詢。

1.3 覆蓋索引

我們通常創建索引的依據都是根據查詢的where條件，但是這只是我們通常的做法，我們根據上面的分析可以知道，如果要想查詢效率高，第一，使用主鍵索引，第二，避免回表，也就是盡可能的在索引中就能獲取想要的數據。如果一個索引包含了需要查詢的字段，那么我們就叫做"覆蓋索引"

建立復合索引：

create index idx_staffs_nameAgePos on staffs(name,age,pos);

-- idx_為建立的復合索引

-- (name,age,pos)是為這三個字段所創建的索引

1.4 索引的口訣

全值匹配我最愛,最左前綴要遵守

帶頭大哥不能死,中間兄弟不能斷

索引列上少計算,范圍之后全失效

like百分寫最右,覆蓋索引不寫星

不等空值還有or,索引失效要少用

varchar引號不可丟,SQL高級也不難

2.0 索引優化案例

2.1 單表優化

#建表

create table article(

id int unsigned not null primary key auto_increment,

author_id int unsigned not null,

category_id int unsigned not null,

views int unsigned not null,

comments int unsigned not null,

title varchar(255) not null,

content text not null

);

#插入數據

insert into article(`author_id`,`category_id`,`views`,`comments`,`title`,`content`) values

(1,1,1,1,'1','1'),

(2,2,2,2,'2','2'),

(1,1,3,3,'3','3');

--需求：查詢category_id為1且comments大于1的情況下,views最多的article_id

select * from article where category_id = 1 and comments>1 order by views desc;

進行索引優化：

查詢結果出現文件內排序

優化方案，為字段category_id，views添加索引:

添加組合索引后，文件內排序消失。

2.2 雙表優化

-- 建表

-- 商品類別表

create table class(

id int unsigned not null primary key auto_increment,

card int unsigned not null

);

-- 圖書表

create table book(

bookid int unsigned not null auto_increment primary key,

card int unsigned not null

);

-- 驅動表的概念，mysql中指定了連接條件時，滿足查詢條件的記錄行數少的表為-驅動表；如未指定查詢條件，則掃描行數少的為驅動表。mysql優化器就是這么粗暴以小表驅動大表的方式來決定執行順序的。

兩個表關聯查詢：

(1)當使用左連接時，往右表添加索引

(2)當使用右連接時，往左表添加索引

(3)當使用內連接時，可添加組合索引如下：

3.0 Join語句優化

3.1 關聯查詢的算法

我們在使用數據庫查詢數據時，有時一張表并不能滿足我們的需求，很多時候都涉及到多張表的連接查詢。今天，我們就一起研究關聯查詢的一些優化技巧。在說關聯查詢優化之前，我們先看下跟關聯查詢有關的幾個算法：

關聯查詢的算法

? Nested-Loop Join 算法

? Block Nested-Loop Join 算法

Nested-Loop Join 算法

一個簡單的 Nested-Loop Join(NLJ) 算法一次一行循環地從第一張表(稱為驅動表)中讀取行，在這行數據中取到關聯字段，根據關聯字段在另一張表(被驅動表)里取出滿足條件的行，然后取出兩張表的結果合集。

我們試想一下，如果在被驅動表中這個關聯字段沒有索引，那么每次取出驅動表的關聯字段在被驅動表查找對應的數據時，都會對被驅動表做一次全表掃描，成本是非常高的(比如驅動表數據量是 m，被驅動表數據量是 n，則掃描行數為 m * n )。

好在 MySQL 在關聯字段有索引時，才會使用 NLJ，如果沒索引，就會使用 Block Nested-Loop Join。我們先來看下在有索引情況的情況下，使用 Nested-Loop Join 的場景(稱為：Index Nested-Loop Join)。

因為 MySQL 在關聯字段有索引時，才會使用 NLJ，因此本節后面的內容所用到的 NLJ 都表示 Index Nested-Loop Join。如下：

怎么確定這條 SQL 使用的是 NLJ 算法？

從執行計劃中可以看到這些信息：

? 驅動表是 t2，被驅動表是 t1。原因是：explain 分析 join 語句時，在第一行的就是驅動表；選擇 t2 做驅動表的原因：如果沒固定連接方式優化器會優先選擇小表做驅動表。所以使用 inner join 時，前面的表并不一定就是驅動表。

? 使用了 NLJ。原因是：一般 join 語句中，如果執行計劃 Extra 中未出現 Using join buffer (***)；則表示使用的 join 算法是 NLJ。

Block Nested-Loop Join 算法

Block Nested-Loop Join(BNL) 算法的思想是：把驅動表的數據讀?到 join_buffer 中，然后掃描被驅動表，把被驅動表每??取出來跟 join_buffer 中的數據做對?，如果滿? join 條件，則返回結果給客戶端。

我們?起看看下?這條 SQL 語句：

explain select * from s1 inner join s2 on s1.id=s2.id;

在 Extra 發現 Using join buffer (Block Nested Loop)，這個就說明該關聯查 詢使?的是 BNL 算法。

在沒有建立索引的情況下，(BNL)會比(NLJ)磁盤掃描更少，因此是更優的選擇。因此對于 MySQL 的關 聯查詢，如果被驅動表的關聯字段沒索引，會使? BNL 算法。

3.2 關聯查詢優化：

使用臨時表優化

由于表 s1 和表 s2 的字段 id 都沒索引，因此使?的是效率?較低的 BNL 算法。 現在?臨時表的?法對這條 SQL 進?優化：

CREATE TEMPORARY TABLE `s1_tmp` ( `id` int(11) NOT NULL AUTO_INCREMENT, `a` int(11) DEFAULT NULL, `b` int(11) DEFAULT NULL,`create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMME NT '記錄創建時間', `update_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UP DATE CURRENT_TIMESTAMP COMMENT '記錄更新時間', PRIMARY KEY (`id`), KEY `idx_a` (`a`), KEY `idx_b` (b)) ENGINE=InnoDB ;

#把 s1 表中的數據寫?臨時表 t1_tmp 中：

insert into s1_tmp select * from s1;

#執? join 語句

select * from t1_tmp join t2 on t1_tmp.b= t2.b;

#結果顯示：

Extra 沒出現 “Block Nested Loop”，說明使?的是 Index Nested-Loop Join，并且掃描?數也??降低了

所以當遇到 BNL 的 join 語句，如果不?便在關聯字段上添加索引，不妨嘗試 創建臨時表，然后在臨時表中的關聯字段上添加索引，然后通過臨時表來做關 聯查詢

本文地址：https://blog.csdn.net/baidu_39394442/article/details/108139982

如您對本文有疑問或者有任何想說的，請點擊進行留言回復，萬千網友為您解惑！

總結

以上是生活随笔為你收集整理的python mysql 优化_Python之MySQL优化(上)的全部內容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網站內容還不錯，歡迎將生活随笔推薦給好友。