笛卡爾連接

沒有攜帶on的條件字句，此條slq查詢的結(jié)構(gòu)集等價于，a表包含的條數(shù)*b表包含的乘積

select * from table a cross join table b;

擁有攜帶on字句的sql，等價于inner join

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

分頁limit的sql優(yōu)化的幾種方法

表包含的數(shù)據(jù)較少，作為驅(qū)動表(小表驅(qū)動大表,一般MySQL的優(yōu)化器會做出相應(yīng)的優(yōu)化的,但是為了防止一些抽風(fēng)現(xiàn)象可以用STRAIGHT_JOIN,作用會強(qiáng)制使用左邊的表作為驅(qū)動表)

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

方案①：覆蓋索引

select 主鍵字段或者創(chuàng)建過索引的字段 from table limit 300000,10

方案②：索引覆蓋+inner (業(yè)界常用的優(yōu)化方案)

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

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

select * from table
where 主鍵字段或者創(chuàng)建過索引的字段
>=
(select 主鍵字段或者創(chuàng)建過索引的字段 from table 300000,1)
limit 10;

方案④：范圍查詢+limit語句 獲取上一頁的主鍵最大值,然后進(jìn)行獲取后面的數(shù)據(jù)

上一頁的最大主鍵值為100

     select * from table
     where id > 100
     limit 10;

方案⑤：需要獲取起始主鍵值和結(jié)束主鍵值

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

方案⑥：禁止傳入過大的頁碼 (百度就是采用這種方式)

count 優(yōu)化方案總結(jié)

例1:

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

例2:

    /**
     * count(字段) 只會針對該字段進(jìn)行統(tǒng)計，使用這個字段上的索引（如果包含索引的情況）
     * count(子段) 會排出字段值為null的數(shù)據(jù)
     * count(*) 不會排出字段值為null的數(shù)據(jù)
     * count(*) 和 count(1) 沒有區(qū)別
     * 對于MyISAM引擎，如果 count(*) 沒有where條件，查詢效率會特別的快，因為把數(shù)據(jù)存儲到MyISAM引擎里了
     * 對于MySQL 8.0.13，InnoDB引擎，如果count(*) 沒有where條件查詢速度，也是特別的快，做出了相應(yīng)的優(yōu)化
     *
     *
    **/
select count(某個字段) from table 會把此字段的值為null過濾掉,僅僅只統(tǒng)計字段值不為null的

例3:

    //做完本條查詢,去執(zhí)行count的操作

    select sql_calc_found_rows * from table limit 0,10;
    select found_rows() as count ;  通過此sql來獲取count的結(jié)果(須在終端進(jìn)行執(zhí)行)

缺點在mysql8.0.17這種用法已經(jīng)被廢棄,未來會被永久刪除

例4：

// 優(yōu)點不操作具體的表,無論表的數(shù)據(jù)量有多大,都可以迅速執(zhí)行. 
// 缺點:統(tǒng)計的是一個估算值,適合要求統(tǒng)計數(shù)的精度不是太高的場景    
select * from information_schema.TABLES
    where
       TABLE_SCHEMA='數(shù)據(jù)庫名稱'
    and
       TABLE_NAME ='表的名稱';

例5:

// 優(yōu)點不操作具體的表,無論表的數(shù)據(jù)量有多大,都可以迅速執(zhí)行. 
// 缺點:統(tǒng)計的是一個估算值,適合要求統(tǒng)計數(shù)的精度不是太高的場景  
show table status where NAME='表的名稱隔行'

例6:

// 優(yōu)點不操作具體的表,無論表的數(shù)據(jù)量有多大,都可以迅速執(zhí)行. 
// 缺點:統(tǒng)計的是一個估算值,適合要求統(tǒng)計數(shù)的精度不是太高的場景 
explain select * from table

例7:

優(yōu)化案例; 目前有一張數(shù)量非常大的表,需要統(tǒng)計id值大于100的有多少條

一般寫法:

select count(*) from table where id>100;

mysql8.18版:

逆向思維的寫法:

select count()-(select count() from table where id <100) from table

order by 的優(yōu)化: 原則利用索引,避免排序

 //first_name,last_name已經(jīng)在表里創(chuàng)建了組合索引,emp_no為主鍵;

例1:

//此sql是不能利用到索引的,原因是:mysql的優(yōu)化器,是根據(jù)成本計算的,如果全表掃描比使用索引,成本更低時會使用全表掃描
//如何鑒定是否使用索引避免了排序呢? 通過explain 查看sql的性能如果Extra的值為null時,說明是可以通過索引避免排序的.如果Extra的值是Using filesort 是不可以進(jìn)行索引排序的
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: 在=號的左邊,進(jìn)行加減操作

阿里規(guī)約一般join的表數(shù),最好不要超過三張表; 如果超過的話就要就行做相應(yīng)的拆分.

例1:

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

表的設(shè)計原則（三范式）:
一范式：表的字段都是原子性，即每個表的字段都是不可分割的，不是集合，數(shù)組，記錄等非原子數(shù)據(jù)項
二范式：在第一范式的基礎(chǔ)上，每一行數(shù)據(jù)的唯一性，非主鍵字段要完全依賴于主鍵字段.
三范式：在滿足第二范式的基礎(chǔ)上，不能存在傳遞依賴

原文鏈接：
https://css.dandelioncloud.cn/article/details/1412232643810512897