當前位置：首頁 > 數據庫 > Mysql > 詳細頁面

SQL性能優化方法及性能測試

時間：2022-05-17來源：www.www.newsthatmovesu.com作者：電腦系統城

笛卡爾連接

例1： 沒有攜帶on的條件字句,此條slq查詢的結構集等價于,a表包含的條數*b表包含的乘積：

1	`select` `*` `from` `table` `a` `cross` `join` `table` `b;`

例2：擁有攜帶on字句的sql,等價于inner join：

1	`select` `*` `from` `table` `a` `cross` `join` `table` `b` `on` `a.id=b.id;`

分頁limit的sql優化的幾種方法

規則;表包含的數據較少的數據量,作為驅動表(小表驅動大表,一般mysql的優化器會做出相應的優化的,但是為了防止一些抽風現象可以用STRAIGHT_JOIN,作用會強制使用左邊的表作為驅動表)。

例1:

1	`select` `*` `from` `table` `c straight_join` `table` `d` `on` `c.id=d.id;`

覆蓋索引:

1	`select` `主鍵字段或者創建過索引的字段` `from` `table` `limit 300000,10`

索引覆蓋+inner (業界常用的優化方案)

1
2
3
4 select * from table a
inner join (
select 創建索引的字段 from table limit 30000,10) b
on b.創建索引的字段=a.創建索引的字段 (也可以更換為 using (創建索引的字段))

索引覆蓋+子查詢先獲取分頁起始的最小值,然后再獲取后10條 (業界常用的優化方案)

1
2
3
4
5 select * from table
where 主鍵字段或者創建過索引的字段
>=
(select 主鍵字段或者創建過索引的字段 from table 300000,1)
limit 10;

范圍查詢+limit語句獲取上一頁的主鍵最大值,然后進行獲取后面的數據;

例1; 上一頁的最大主鍵值為100

1
2
3 select * from table
where id > 100
limit 10;

需要獲取起始主鍵值和結束主鍵值

1 2	`select` `*` `from` `table` `where` `id` `between` `起始主鍵值` `and` `結束主鍵值;`

禁止傳入過大的頁碼 (例如;百度就是采用這種方式)

count 優化方案

實例1：

1
2
3
4
5
6
7
8
9
10
11
12 /**
* 1：如果不包含非主鍵的索引，就會使用主鍵索引
* 2：如果包含非主鍵的索引就會使用非主鍵索引；
* 3：如果存在多個非主鍵索引，會使用key_len值較小的索引
* 為什么會有這種規律呢？
* -innodb非主鍵索引：葉子結點儲存的是：索引+主鍵
* 主鍵索引葉子結點儲存的是：主鍵+表數據
* 在1page里面，非主鍵索引可以存儲更多的條目，對于一張表，假如擁有10000000數據
* 使用非主鍵索引，掃描page 500，主鍵索引 100 非主鍵索引掃描的條目多，可以減少掃描的次數
*
**/
select count(*) from table

實例2：

1
2
3
4
5
6
7
8
9
10
11 /**
* count(字段) 只會針對該字段進行統計，使用這個字段上的索引（如果包含索引的情況）
* count(子段) 會排出字段值為null的數據
* count(*) 不會排出字段值為null的數據
* count(*) 和 count(1) 沒有區別
* 對于MyISAM引擎，如果 count(*) 沒有where條件，查詢效率會特別的快，因為把數據存儲到MyISAM引擎里了
* 對于MySQL 8.0.13，InnoDB引擎，如果count(*) 沒有where條件查詢速度，也是特別的快，做出了相應的優化
*
*
**/
select count(某個字段) from table 會把此字段的值為null過濾掉,僅僅只統計字段值不為null的

實例3：

1
2
3 //做完本條查詢,去執行count的操作
select sql_calc_found_rows * from table limit 0,10;
select found_rows() as count ; 通過此sql來獲取count的結果(須在終端進行執行)

注意：缺點在mysql8.0.17這種用法已經被廢棄,未來會被永久刪除

實例4：優點不操作具體的表,無論表的數據量有多大,都可以迅速執行. 缺點:統計的是一個估算值,適合要求統計數的精度不是太高的場景。

1
2
3
4
5 select * from information_schema.TABLES
where
TABLE_SCHEMA='數據庫名稱'
and
TABLE_NAME ='表的名稱';

實例5: //優點不操作具體的表,無論表的數據量有多大,都可以迅速執行. 缺點:統計的是一個估算值,適合要求統計數的精度不是太高的場景。

1	`show` `table` `status` `where` `NAME='表的名稱隔行'`

實例6: //優點不操作具體的表,無論表的數據量有多大,都可以迅速執行. 缺點:統計的是一個估算值,適合要求統計數的精度不是太高的場景。

1	`explain` `select` `*` `from` `table`

實例7: 優化案例; 目前有一張數量非常大的表,需要統計id值大于100的有多少條

一般寫法：select count(*) from table where id>100;。
mysql8.18版：逆向思維的寫法: select count()-(select count() from table where id <100) from table。
order by 的優化：原則利用索引,避免排序。

1	`//first_name,last_name已經在表里創建了組合索引,emp_no為主鍵;`

實例1：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27 //此sql是不能利用到索引的,原因是:mysql的優化器,是根據成本計算的,如果全表掃描比使用索引,成本更低時會使用全表掃描
//如何鑒定是否使用索引避免了排序呢? 通過explain 查看sql的性能如果Extra的值為null時,說明是可以通過索引避免排序的.如果Extra的值是Using filesort 是不可以進行索引排序的
select * from table order by first_name,last_name;
//此sql可以使用索引避免排序的
select * from table order by first_name,last_name limit 10;
//此sql可以使用索引避免排序的
/**
*[Bader,last_name,emp_no]
*[Bader,last_name,emp_no]
*[Bader,last_name,emp_no]
*[Bader,last_name,emp_no]
*
**/
select * from table where fist_name='Bader' order by last_name;
//此sql可以使用索引避免排序的
/**
*[Bader,last_name,emp_no]
*[Ba,last_name,emp_no]
*[Bad,last_name,emp_no]
*[Bade,last_name,emp_no]
*
**/
select * from table where fist_name<'Bader' order by last_name
//此sql可以使用索引避免排序的
select * from table where fist_name='Bader' and last_name>'Peng' order by last_name
//此sql可以使用索引避免排序的,原因排序的倆個字段,分別存在倆個索引中
select * from table order by first_name,emp_no;

索引失效的場景:

1: join 字段的類型不一致
2: 在=號的左邊,進行加減操作

實例1：

1
2
3
4 select * from employees e
left join dept_emp de on e.emp_no=de.emp_no
left join departments d on de.dept_no=d.dept_no
where e.emp_no=1001;

拆分后：

1
2
3 select * from employees where emp_no='1001';
select * from dept_emp where emp_no='1001';
select * from departments where dept_no='d005';