啪啪欧美精品A片二区

系統城裝機大師 - 唯一官網:www.trattoriacinecitta.com!

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

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

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

目錄
  • 笛卡爾連接
  • 分頁limit的sql優化的幾種方法
  • count 優化方案

笛卡爾連接

例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';

表的設計原則-三范式:

  • 范式:表的字段都是原子性,既每個表的字段都是不可分割的,不是集合,數組,記錄等非原子數據項。
  • 范式:在第一范式的基礎上,每一行數據的唯一性,非主鍵字段要完全依賴于主鍵字段。
  • 范式:在滿足第二范式的基礎上,不能存在傳遞依賴。
分享到:

相關信息

  • 千萬級用戶系統SQL調優實戰分享

    千萬級用戶系統SQL調優實戰分享,用戶日活百萬級,注冊用戶千萬級,而且若還沒有進行分庫分表,則該DB里的用戶表可能就一張,單表上千萬的用戶數據,下面我們就來學習如何讓優化,需要的朋友可以參考一下...

    2022-03-01

  • mysql遠程跨庫聯合查詢的示例

    情況一2個庫在同一臺物理主機 情況二2個庫不在同一臺物理主機(即2個庫分別在不同的物理主機)...

    2022-03-01

系統教程欄目

欄目熱門教程

人氣教程排行

站長推薦

熱門系統下載