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

select * from table a cross join table b;

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

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

select 主鍵欄位或者建立過索引的欄位 from table limit 300000,10

select * from table a
inner join (
select 建立索引的欄位 from table  limit 30000,10) b
on b.建立索引的欄位=a.建立索引的欄位 (也可以更換為 using (建立索引的欄位))

select * from table
where 主鍵欄位或者建立過索引的欄位
                  >=
(select 主鍵欄位或者建立過索引的欄位 from table 300000,1)
limit 10;

  select * from table
     where id > 100
     limit 10;

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

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

    /**
     * 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的

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

    select * from information_schema.TABLES
    where
       TABLE_SCHEMA='資料庫名稱'
    and
       TABLE_NAME ='表的名稱';

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

 explain select * from table

 //first_name,last_name已經在表裡建立了組合索引,emp_no為主鍵;

//此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;

     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;

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