1.常見的ddl,dql,dml聯(lián)合場景
ddl：對于表結(jié)構(gòu)的管理sql語句
dql:常見的sql查詢語句
dml:常見的修改數(shù)據(jù)的update,insert,delete語句
- 將一個表里面的數(shù)據(jù)插入到另外一個表中的純sql操作,一般可用于建立臨時表使得大表變小表
insert into tbl_quick_msg_group
(wechatapp_id,user_id,group_name,default_type,update_time,create_time)
(select wechatapp_id,user_id,'未分組' group_name,1 default_type,update_time,create_time from tbl_quick_reply group by user_id,wechatapp_id ORDER BY wechatapp_id);
- php配合sql的大表瘦身
public function summary()
{
$sql="DROP TEMPORARY TABLE IF EXISTS tmp_staff_group_info";
LogicUtil::db_run_update($sql);
$sql="DROP TEMPORARY TABLE IF EXISTS tmp_staff_group_rel";
LogicUtil::db_run_update($sql);
$sql="CREATE TEMPORARY TABLE tmp_staff_group_info
SELECT sgi._id, s.user_id
FROM tbl_staff_group_info sgi
INNER JOIN tbl_staff_group_rel sgr ON sgr.group_id = sgi._id
where sgi.group_name LIKE '%白三涉水%' ";
LogicUtil::db_run_update($sql);
$sql="CREATE TEMPORARY TABLE tmp_staff_group_rel
SELECT b._id, a.user_id
FROM tbl_staff_wechatapp_relation AS a
LEFT JOIN tbl_wechatapp AS b ON a.wechatapp_id = b._id
WHERE b.user_id > 0 && b.app_name LIKE '%白三涉水%' ";
LogicUtil::db_run_update($sql);
$sql=" select s.*, 0 group_id
from tbl_staff s
inner join tmp_staff_group_info A on A._id = s.user_id
inner join tmp_staff_group_rel B on B._id = s.user_id
where s.parent_user_id = 16695397
ORDER BY s.user_id ASC LIMIT 0, 20";
$result = LogicUtil::db_run_sql($sql);
$sql="DROP TEMPORARY TABLE IF EXISTS tmp_staff_group_info";
LogicUtil::db_run_update($sql);
$sql="DROP TEMPORARY TABLE IF EXISTS tmp_staff_group_rel";
LogicUtil::db_run_update($sql);
return $result;
}
2.1. mysql的索引優(yōu)化選擇器未必絕對可信，我發(fā)現(xiàn)以下2條sql，指定索引執(zhí)行的效率比mysql索引優(yōu)化器默認(rèn)的要快很多颜骤，說明不要太依賴優(yōu)化器，特定場景特事特辦(優(yōu)化器使用order 的 last_chat_time的索引诫龙，而我指定where條件中的wechatapp_id索引)
select * from tbl_customer where wechatapp_id in (11739) && kefu_user_id in(798268289) ORDER BY last_chat_time desc limit 0,10； /執(zhí)行30多秒
select * from tbl_customer FORCE INDEX(`wechatapp_id`) where wechatapp_id in (11739) && kefu_user_id in(798268289) ORDER BY last_chat_time desc limit 0,10; //執(zhí)行0.4
- 游標(biāo)場景,缺點內(nèi)存開銷大，容易鎖，是一個存儲過程
BEGIN
--定義變量
declare testrangeid BIGINT;
declare versionid BIGINT;
declare done int;
--創(chuàng)建游標(biāo)谈火，并存儲數(shù)據(jù)
declare cur_test CURSOR for
select id as testrangeid,version_id as versionid from tp_testrange;
--游標(biāo)中的內(nèi)容執(zhí)行完后將done設(shè)置為1
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done=1;
--打開游標(biāo)
open cur_test;
--執(zhí)行循環(huán)
posLoop:LOOP
--判斷是否結(jié)束循環(huán)
IF done=1 THEN
LEAVE posLoop;
END IF;
--取游標(biāo)中的值
FETCH cur_test into testrangeid,versionid;
--執(zhí)行更新操作
update tp_data_execute set version_id=versionid where testrange_id = testrangeid;
END LOOP posLoop;
--釋放游標(biāo)
CLOSE cur_test;
- sql執(zhí)行順序分析:參考文獻(xiàn)
第一步：加載from子句的前兩個表計算笛卡爾積，生成虛擬表vt1匙奴；
第二步：篩選關(guān)聯(lián)表符合on表達(dá)式的數(shù)據(jù)堆巧，保留主表，生成虛擬表vt2泼菌；
第三步：如果使用的是外連接，執(zhí)行on的時候啦租，會將主表中不符合on條件的數(shù)據(jù)也加載進(jìn)來哗伯，做為外部行
第四步：如果from子句中的表數(shù)量大于2，則重復(fù)第一步到第三步篷角，直至所有的表都加載完畢焊刹，更新vt3；
第五步：執(zhí)行where表達(dá)式恳蹲，篩選掉不符合條件的數(shù)據(jù)生成vt4虐块；
第六步：執(zhí)行g(shù)roup by子句。group by 子句執(zhí)行過后嘉蕾，會對子句組合成唯一值并且對每個唯一值只包含一行贺奠，生成vt5,。一旦執(zhí)行g(shù)roup by错忱，后面的所有步驟只能得到vt5中的列（group by的子句包含的列）和聚合函數(shù)儡率。
第七步：執(zhí)行聚合函數(shù)，生成vt6以清；
第八步：執(zhí)行having表達(dá)式儿普，篩選vt6中的數(shù)據(jù)。having是唯一一個在分組后的條件篩選掷倔，生成vt7;
第九步：從vt7中篩選列眉孩，生成vt8；
第十步：執(zhí)行distinct，對vt8去重浪汪，生成vt9障贸。其實執(zhí)行過group by后就沒必要再去執(zhí)行distinct，因為分組后吟宦，每組只會有一條數(shù)據(jù)篮洁，并且每條數(shù)據(jù)都不相同。
第十一步：對vt9進(jìn)行排序殃姓，此處返回的不是一個虛擬表袁波，而是一個游標(biāo)，記錄了數(shù)據(jù)的排序順序蜗侈，此處可以使用別名篷牌；
第十二步：執(zhí)行l(wèi)imit語句，將結(jié)果返回給客戶端
4.1 sql綁定參數(shù)的那些事：
php在pdo那一層是支持prepare去sql預(yù)編譯的踏幻，參數(shù)已bindParam()的是形式綁定進(jìn)去枷颊。之前一直以為參數(shù)綁定只是為了防止sql注入，現(xiàn)補充2點：
1.1: 預(yù)編譯參數(shù)綁定執(zhí)行的sql過程是该面，php先將sql發(fā)送到mysql夭苗，mysql執(zhí)行預(yù)編譯sql（值得注意的是預(yù)編譯的過程的sql是開發(fā)自己寫的sql，并不存在來自客戶端用戶數(shù)據(jù)參與的隔缀，如果預(yù)編譯的sql都有注入的問題题造，那也是開發(fā)自己的問題），后面綁定參數(shù)走的是不同協(xié)議參與到編譯的sql中執(zhí)行的猾瘸，如此自然不怕其sql注入了
1.2 : 多次執(zhí)行相同的sql（只是綁定參數(shù)不同）界赔，mysql只會分析一次，生成執(zhí)行計劃一次牵触，編譯一次淮悼，減少過程自然效率會有優(yōu)勢（但你需要注意，綁定的參數(shù)最好不會對執(zhí)行計劃產(chǎn)生很大影響揽思，mysql這個時候使用的是第一次的執(zhí)行計劃袜腥，如果因為你綁定參數(shù)使得執(zhí)行效率很差，一般只有數(shù)據(jù)不均勻會造成绰更，那你就注意了還有綁定的必要不）
//預(yù)處理1--操作數(shù)據(jù)庫
$mysqli = new mysqli('localhost','root','mayi1991','mysqldemo');
$mysqli->query('set names utf8');
//創(chuàng)建預(yù)編譯對象,這條sql就是先取mysql進(jìn)行編譯處理的瞧挤，
//我們可以理解成 php與mysql先交互通訊一次，這條sql可是你自己寫的儡湾，再有sql注入那也是醉了特恬。。徐钠。
$mysqli_stmt = $mysqli->prepare("insert account (balance,name) values (?,?)");
//綁定參數(shù)
$balance = 122;
$name = '小白';
//TODO 走不同協(xié)議去與上一步編譯好的sql進(jìn)行綁定執(zhí)行的癌刽，這里的參數(shù)值有可能來自客戶端的輸入，
//哪怕是用于注入sql的參數(shù)值，也沒用了显拜，因為之前編譯好的sql根本不信任它衡奥，只會拿它當(dāng)參數(shù)綁定執(zhí)行，
//不會因為它去破壞sql的語義远荠。矮固。。
$mysqli_stmt->bind_param("ds",$balance,$name);
//執(zhí)行 返回boolean值
$mysqli_stmt->execute();
- 數(shù)據(jù)庫大表統(tǒng)計
select
table_schema as '數(shù)據(jù)庫',
table_name as '表明',
table_rows as '記錄數(shù)',
truncate(data_length/1024/1024/1024,2) as '數(shù)據(jù)容量(GB)',
truncate(index_length/1024/1024/1024,2) as '索引容量'
from information_schema.tables
where table_schema ='wechatapp_customer_service'
ORDER BY data_length desc,index_length desc;
- mysql單機儲存空間到達(dá)極限,刪除索引并立即釋放空間,再遷移數(shù)據(jù)后進(jìn)行索引恢復(fù)
ALTER TABLE `wechatapp_customer_service`.`tbl_message202002`
DROP INDEX `openid`;
OPTIMIZE table tbl_message201906; //mysql刪除數(shù)據(jù)后并不會立即騰出空間譬淳，執(zhí)行optimize table 可以快速立即釋放
- mysql表設(shè)計之三范式思想 MySQL設(shè)計之三范式的理解
1NF:字段不可分,就是每個字段屬性設(shè)計都應(yīng)該保持原子性(比如地址里面包含省份市級縣城,這里就不具備原子性档址，因為省份，市級邻梆，縣是可以拆分的)
2NF:有主鍵守伸，非主鍵字段依賴主鍵; （一個表中所有的屬性應(yīng)該只能更主鍵具備唯一識別的關(guān)系，而不能跟非主鍵浦妄，比如用戶表中,id與uid尼摹，既然你的uid就是可以定位用戶信息的，這個時候就不應(yīng)該再使用id）
3NF:非主鍵字段不能相互依賴; (A表明明已經(jīng)存在B表的主鍵id屬性剂娄，現(xiàn)在又在A表中建立了B表的其他屬性字段蠢涝，這就不滿足第三范式了,因為我們明明可以通過A表中的B的唯一索引推出來B的所有屬性，故不能在A表中建立B的非主鍵屬性)
