python 之資料庫（內置函數、流程式控制制、索引）

10.17 內置函數強調：mysql內置的函數只能在sql語句中使用 date_format: 10.171 自定義函數函數中不要寫sql語句（否則會報錯），函數僅僅只是一個功能，是一個在sql中被應用的功能,若要想在begin...end...中寫sql，請用存儲過程. 刪除函數：執行函數： ...

10.17 內置函數

強調：mysql內置的函數只能在sql語句中使用

#數學函數
round(x,y)      #返回參數x的四捨五入的有y位小數的值
rand()          #返回０到１內的隨機值,可以通過提供一個參數(種子)使rand()隨機數生成器生成一個指定的值。

#加密函數
md5()           #計算字元串str的MD5校驗和
password(str)   #返回字元串str的加密版本，這個加密過程是不可逆轉的，和UNIX密碼加密過程使用不同的演算法。

date_format:

create table blog (
    id int primary key auto_increment,
    name char (32),
    sub_time datetime);
insert into blog (name, sub_time) values
('第1篇','2015-03-01 11:31:21'),
('第2篇','2015-03-11 16:31:21'),
('第3篇','2017-05-01 18:31:21');
select date_format(sub_time,'%Y-%m'),count(id) from blog group by date_format(sub_time,'%Y-%m');
+-------------------------------+-----------+
| date_format(sub_time,'%Y-%m') | count(id) |
+-------------------------------+-----------+
| 2015-03                       |         2 |
| 2017-05                       |         1 |
+-------------------------------+-----------+

10.171 自定義函數

函數中不要寫sql語句（否則會報錯），函數僅僅只是一個功能，是一個在sql中被應用的功能,若要想在begin...end...中寫sql，請用存儲過程.

delimiter //
create function f1(
    i1 int,
    i2 int)
returns int
BEGIN
    declare num int;
    set num = i1 + i2;
    return(num);
END //
delimiter ;

刪除函數：

drop function func_name;

執行函數：

# 獲取返回值
select UPPER('egon') into @res;
SELECT @res;
+------+
| @res |
+------+
| EGON |
+------+
# 在查詢中使用
select f1(11,nid) ,name from tb2;

10.18 流程式控制制

if條件語句:

delimiter //
create function f5(i int)
returns int
begin
    declare res int default 0;
    if i = 10 then
        set res=100;
    elseif i = 20 then
        set res=200;
    elseif i = 30 then
        set res=300;
    else
        set res=400;
    end if;
    return res;
end //
delimiter ;

控制流函數：

select 
case
    when name = 'egon' then name
    when name = 'alex' then concat(name,'_BIGSB')
    else concat(name,'_SB')
end
from emp;

迴圈語句:

delimiter //
create procedure proc_while ()
begin
    declare num int ;
    set num = 0 ;
    while num < 10 do
        select
            num ;
        set num = num + 1 ;
    end while ;
end //
delimiter ;

10.19 索引

索引就相當於書的目錄，是mysql中一種專門的數據結構，稱為key，索引的本質原理就是通過不斷地縮小查詢範圍，來降低 i/o 次數從而提升查詢性能，一旦為表創建了索引，以後的查詢都會先查索引，再根據索引定位的結果去找數據

索引的影響： 1、在表中有大量數據的前提下，創建索引速度會很慢 2、在索引創建完畢後，對錶的查詢性能會大幅度提升，但是寫性能會降低

聚集索引(primary key)：就是按照每張表的主鍵構造一棵B+樹，同時葉子結點存放的即為整張表的行記錄數據，也將聚集索引的葉子結點稱為數據頁。聚集索引的這個特性決定了索引組織表中數據也是索引的一部分。同B+樹數據結構一樣，每個數據頁都通過一個雙向鏈表來進行鏈接。特點：葉子節點存放的一整條數據

聚集索引的優點：1.它對主鍵的排序查找和範圍查找速度非常快，葉子節點的數據就是用戶所要查詢的數據。如用戶需要查找一張表，查詢最後的10位用戶信息，由於B+樹索引是雙向鏈表，所以用戶可以快速找到最後一個數據頁，並取出10條記錄 2.範圍查詢(range query),即如果要查找主鍵某一範圍內的數據，通過葉子節點的上層中間節點就可以得到頁的範圍，之後直接讀取數據頁即可

輔助索引(unique，index):非聚集索引即從輔助索引中就可以得到查詢記錄，而不需要查詢聚集索引中的記錄。 特點：輔助索引的葉子節點不包含行記錄的全部數據。如果是按照name欄位創建的索引，那麼葉子節點存放的是：{name：名字所在那條記錄的主鍵的值}

每張表上可以有多個輔助索引，但只能有一個聚集索引。當通過輔助索引來尋找數據時，InnoDB存儲引擎會遍歷輔助索引並通過葉子級別的指針獲得主鍵索引的主鍵，然後再通過主鍵索引來找到一個完整的行記錄。

使用覆蓋索引的一個好處是：輔助索引不包含整行記錄的所有信息，故其大小要遠小於聚集索引，因此可以減少大量的IO操作,InnoDB存儲引擎支持覆蓋索引（covering index，或稱索引覆蓋），即從輔助索引中就可以得到查詢記錄，而不需要查詢聚集索引中的記錄。

#覆蓋索引：在輔助索引的葉子節點中就已經找到了所有我們想要的數據
select name from user where name='egon';
#非覆蓋索引：命中輔助索引，但是未覆蓋索引，還需要從聚集索引中查找age
select age from user where name='egon';

聯合索引:聯合索引指對錶上的多個列合起來做一個索引,在第一個鍵相同的情況下，已經對第二個鍵進行了排序處理

mysql中的primary key，unique，index ,聯合唯一也都是索引，這些索引除了加速查找以外，還有約束的功能

普通索引 index：加速查找
唯一索引：
    -主鍵索引 primary key：加速查找+約束（not null  + unique）
    -唯一索引 unique:加速查找+約束（不能重覆）
聯合索引：
    -primary key(id,name):聯合主鍵索引，加速查找+約束
    -unique(id,name):聯合唯一索引，加速查找+約束
    -index(id,name):聯合普通索引

innodb:聚集索引，輔助索引 myisam:輔助索引

10.191 創建和刪除索引

#方式一
create table t1(
    id int,
    name char,
    age int,
    sex enum('male','female'),
    unique key uni_id(id),
    index ix_name(name)         #index沒有key
);
#方式二
create index ix_age on t1(age);
#方式三
alter table t1 add index ix_sex(sex);
alter table t1 add primary key(id);
#聯合索引
alter table t1 add primary key(id，name);
create index ix_age on t1(id，age);
+-------+-----------------------+------+-----+---------+-------+
| Field | Type                  | Null | Key | Default | Extra |
+-------+-----------------------+------+-----+---------+-------+
| id    | int(11)               | YES  | UNI | NULL    |       |
| name  | char(1)               | YES  | MUL | NULL    |       |
| age   | int(11)               | YES  | MUL | NULL    |       |
| sex   | enum('male','female') | YES  | MUL | NULL    |       |
+-------+-----------------------+------+-----+---------+-------+
#刪除索引：
drop index idx_name on t1;
alter table t1 drop primary key;

and工作原理：對於連續多個and：mysql會按照聯合索引，從左到右的順序找一個區分度高的索引欄位(這樣便可以快速鎖定很小的範圍)，加速查詢
or的工作原理：對於連續多個or：mysql會按照條件的順序，從左到右依次判斷,此時應該按照區分度高低設置聯合索引

10.192 正確使用索引

10.1921 索引未命中

並不是說我們創建了索引就一定會加快查詢速度，若想利用索引達到預想的提高查詢速度的效果，我們在添加索引時，必須遵循以下問題

1 範圍問題，或者說條件不明確，條件中出現這些符號或關鍵字：>、>=、<、<=、!= 、between...and...、like、
2 儘量選擇區分度高的列作為索引,區分度的公式是count(distinct col)/count(*)，表示欄位不重覆的比例，比例越大我們掃描的記錄數越少，唯一鍵的區分度是1，而一些狀態、性別欄位可能在大數據面前區分度就是0，那可能有人會問，這個比例有什麼經驗值嗎？使用場景不同，這個值也很難確定，一般需要join的欄位我們都要求是0.1以上，即平均1條掃描10條記錄
3 =和in可以亂序，比如a = 1 and b = 2 and c = 3 建立(a,b,c)索引可以任意順序，mysql的查詢優化器會幫你優化成索引可以識別的形式
4 索引列不能參與計算，保持列“乾凈”，比如from_unixtime(create_time) = ’2014-05-29’就不能使用到索引，原因很簡單，b+樹中存的都是數據表中的欄位值，但進行檢索時，需要把所有元素都應用函數才能比較，顯然成本太大。所以語句應該寫成create_time = unix_timestamp(’2014-05-29’)
5 and/or
#1、and與or的邏輯
    條件1 and 條件2:所有條件都成立才算成立，但凡要有一個條件不成立則最終結果不成立
    條件1 or 條件2:只要有一個條件成立則最終結果就成立
#2、and的工作原理
    條件：
        a = 10 and b = 'xxx' and c > 3 and d =4
    索引：
        製作聯合索引(d,a,b,c)
    工作原理:
        對於連續多個and：mysql會按照聯合索引，從左到右的順序找一個區分度高的索引欄位(這樣便可以快速鎖定很小的範圍)，加速查詢，即按照d—>a->b->c的順序
#3、or的工作原理
    條件：
        a = 10 or b = 'xxx' or c > 3 or d =4
    索引：
        製作聯合索引(d,a,b,c)       
    工作原理:
        對於連續多個or：mysql會按照條件的順序，從左到右依次判斷，即a->b->c->d,所以應該將區分度高的放在前面
 6 最左首碼匹配原則，非常重要的原則，對於組合索引mysql會一直向右匹配直到遇到範圍查詢(>、<、between、like)就停止匹配(指的是範圍大了，有索引速度也慢)，比如 a = 1 and b = 2 and c > 3 and d = 4 如果建立(a,b,c,d)順序的索引，d是用不到索引的，如果建立(a,b,d,c)的索引則都可以用到，a,b,d的順序可以任意調整。所以建立索引時將關於範圍比較的欄位放在後面
7 其他情況
- 使用函數
    select * from tb1 where reverse(email) = 'egon';           
- 類型不一致
    如果列是字元串類型，傳入條件是必須用引號引起來，不然...
    select * from tb1 where email = 999;  
#排序條件為索引，則select欄位必須也是索引欄位，否則無法命中
- order by
    select name from s1 order by email desc;
    當根據索引排序時候，select查詢的欄位如果不是索引，則速度仍然很慢
    select email from s1 order by email desc;
    特別的：如果對主鍵排序，則還是速度很快：
    select * from tb1 order by nid desc;
- 組合索引最左首碼
    如果組合索引為：(name,email)
    name and email       -- 命中索引
    name                 -- 命中索引
    email                -- 未命中索引
- count(1)或count(列)代替count(*)在mysql中沒有差別了
- create index xxxx  on tb(title(19)) #text類型，必須制定長度

索引未命中

10.1922 其他註意事項

1 避免使用select *
2 count(1)或count(列) 代替 count(*)
3 創建表時儘量時 char 代替 varchar
4 表的欄位順序固定長度的欄位優先
5 組合索引代替多個單列索引（經常使用多個條件查詢時）
6 儘量使用短索引
7 使用連接（JOIN）來代替子查詢(Sub-Queries)
8 連表時註意條件類型需一致
9 索引散列值不適合建索引，例：性別不適合

總結：

一定是為搜索條件的欄位創建索引，比如select * from s1 where id = 333;就需要為id加上索引
在表中已經有大量數據的情況下，建索引會很慢，且占用硬碟空間，建完後查詢速度加快
需要註意的是：innodb表的索引會存放於s1.ibd文件中，而myisam表的索引則會有單獨的索引文件table1.MYI

10.193 慢查詢優化的基本步驟

0.先運行看看是否真的很慢，註意設置SQL_NO_CACHE
1.where條件單表查，鎖定最小返回記錄表。這句話的意思是把查詢語句的where都應用到表中返回的記錄數最小的表開始查起，單表每個欄位分別查詢，看哪個欄位的區分度最高
2.explain查看執行計劃，是否與1預期一致（從鎖定記錄較少的表開始查詢）
3.order by limit 形式的sql語句讓排序的表優先查
4.瞭解業務方使用場景
5.加索引時參照建索引的幾大原則
6.觀察結果，不符合預期繼續從0分析

python 之 資料庫（內置函數、流程式控制制、索引）