SQL鞏固&數(shù)據(jù)庫優(yōu)化&SQL優(yōu)化(待補充完整細節(jié))
前言
對于初級程序開發(fā)工程師而言顿膨，SQL是很多人的弱項钞脂，為此我給大家來做一下總結(jié)，希望能夠幫到你們梳码。
課程說明
1、介紹項目實戰(zhàn)開發(fā)過程中常用的MySQL函數(shù)及常用語法伍掀，并且分析三種聯(lián)合查詢原理及如何使用掰茶、什么時候使用；
2蜜笤、針對數(shù)據(jù)庫層面的優(yōu)化方案做介紹說明濒蒋；
3、對MySQL SQL優(yōu)化方案做講解把兔，學(xué)習(xí)如何排查慢查詢沪伙；
SQL鞏固
初始化
說明：這里用大家熟知的電商業(yè)務(wù)來講解，進一步鞏固學(xué)習(xí)SQL
表結(jié)構(gòu)創(chuàng)建
訂單表
CREATE TABLE `tb_order` (
`id` int(11) NOT NULL AUTO_INCREMENT COMMENT '訂單id',
`user_id` int(11) NOT NULL COMMENT '用戶id',
`price` int(11) NOT NULL COMMENT '訂單金額',
`pay_time` datetime DEFAULT NULL COMMENT '支付時間',
`create_time` datetime NOT NULL COMMENT '創(chuàng)建時間',
`status` tinyint(4) NOT NULL DEFAULT '0' COMMENT '訂單狀態(tài)',
`update_time` datetime DEFAULT NULL COMMENT '最后更新時間',
PRIMARY KEY (`id`),
KEY `IDX_USER_ID` (`user_id`),
KEY `IDX_PAY_TIME` (`pay_time`),
KEY `IDX_CREATE_TIME` (`create_time`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 CHECKSUM=1 DELAY_KEY_WRITE=1 ROW_FORMAT=DYNAMIC COMMENT='訂單表'
商品表
CREATE TABLE `tb_goods` (
`id` int(11) NOT NULL AUTO_INCREMENT COMMENT '商品id',
`name` varchar(50) NOT NULL DEFAULT '' COMMENT '名稱',
`price` int(11) NOT NULL DEFAULT '0' COMMENT '單價',
`stock` int(11) NOT NULL DEFAULT '0' COMMENT '庫存',
`status` smallint(6) NOT NULL DEFAULT '0' COMMENT '狀態(tài)',
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 CHECKSUM=1 DELAY_KEY_WRITE=1 ROW_FORMAT=DYNAMIC COMMENT='商品表'
由于一個訂單可能包含多種商品县好，故存在
訂單商品關(guān)系表
CREATE TABLE `tb_order_goods` (
`id` int(11) NOT NULL AUTO_INCREMENT COMMENT '關(guān)聯(lián)數(shù)據(jù)id',
`order_id` int(11) NOT NULL COMMENT '訂單id',
`goods_id` int(11) NOT NULL COMMENT '商品id',
`goods_num` int(11) NOT NULL COMMENT '商品數(shù)量',
`goods_price` int(11) NOT NULL COMMENT '商品單價',
`sum_price` int(11) NOT NULL COMMENT '小計金額',
PRIMARY KEY (`id`),
KEY `IDX_ORDER_ID` (`order_id`),
KEY `IDX_GOODS_ID` (`goods_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 CHECKSUM=1 DELAY_KEY_WRITE=1 ROW_FORMAT=DYNAMIC COMMENT='商品訂單關(guān)系表'
用戶表
CREATE TABLE `tb_user` (
`id` int(11) NOT NULL AUTO_INCREMENT COMMENT '用戶id',
`name` varchar(32) DEFAULT NULL COMMENT '姓名',
`phone` varchar(15) NOT NULL DEFAULT '' COMMENT '電話',
`sex` varchar(1) DEFAULT NULL COMMENT '性別',
`password` varchar(64) DEFAULT NULL COMMENT '密碼',
`user_name` varchar(18) NOT NULL COMMENT '用戶名',
`status` tinyint(3) DEFAULT NULL COMMENT '狀態(tài)',
PRIMARY KEY (`id`),
KEY `IDX_PHONE` (`phone`),
KEY `IDX_USER_NAME` (`user_name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 CHECKSUM=1 DELAY_KEY_WRITE=1 ROW_FORMAT=DYNAMIC COMMENT='用戶表'
有的同學(xué)想要知道围橡，針對已存在的表結(jié)構(gòu)，如何添加索引呢缕贡？簡單演示如下
給tb_user表name字段添加普通索引
-- IDX_NAME 為索引名翁授，可自行定義
ALTER TABLE `tb_user` ADD INDEX `IDX_NAME` (`name`);
添加測試數(shù)據(jù)
INSERT INTO `tb_goods`(`id`,`name`,`price`,`stock`,`status`) VALUES (1,'華為P30',600000,20,1),(2,'美特斯邦威男士夾克衫',29900,100,1);
INSERT INTO `tb_order`(`id`,`user_id`,`price`,`pay_time`,`create_time`,`status`,`update_time`) VALUES (1,1,629900,'2020-02-01 00:00:00','2020-02-01 00:00:00',1,'2020-02-01 00:00:00'),(2,1,1200000,NULL,'2020-02-02 00:00:00',0,'2020-02-02 00:00:00'),(3,2,600000,'2020-03-01 00:00:00','2020-03-01 00:00:00',1,'2020-03-01 00:00:00');
INSERT INTO `tb_order_goods`(`id`,`order_id`,`goods_id`,`goods_num`,`goods_price`,`sum_price`) VALUES (1,1,1,1,600000,600000),(2,1,2,1,29900,29900),(3,2,1,2,600000,1200000),(4,3,1,1,600000,600000);
INSERT INTO `tb_user`(`id`,`name`,`phone`,`sex`,`password`,`user_name`,`status`) VALUES (1,'高M','18810531708','男','123456','gao_yue',1),(2,'明哥','18810532526','男','123654','tang_',1),(3,'培哥','15544455665','男','545466','pei_',1);
常見操作
count累計
count(值)，這個值如果不是null則計1晾咪，值為null則計0
所以select count(1) from dual收擦、select count(10000) from dual其結(jié)果都為1
而select count(null) from dual結(jié)果則為0
例如tb_user表中有三條數(shù)據(jù)，其中有一條數(shù)據(jù)的name字段為空谍倦，那么存在
1塞赂、select count(name) from tb_user結(jié)果則為2而非3，如果想要統(tǒng)計表中數(shù)據(jù)條數(shù)則用count(*)或count(id)
2昼蛀、另外值得一說的是宴猾，select 1000 from tb_user表，其結(jié)果為3行1000曹洽，所以存在如下
3鳍置、select count(1000) from tb_user結(jié)果為3
count結(jié)合distinct使用可統(tǒng)計某個字段出現(xiàn)(除空值之外的)不同值的次數(shù)
比如查詢訂單表有多少筆金額相同的訂單
select count(distinct(price)) from tb_order;
sum累加
sum(值)，會把這個值的數(shù)值進行一個累加送淆，sum(null)則為null
所以select sum(0) from dual結(jié)果為0，select sum(1000) from dual結(jié)果為1000
而select sum(null) from dual結(jié)果為null
故針對sum的字段如果可能出現(xiàn)null的話怕轿，需要提前做判斷轉(zhuǎn)換IFNULL(字段,代替值)偷崩，避免出現(xiàn)統(tǒng)計錯誤
比如統(tǒng)計訂單表訂單銷售總額(不區(qū)分訂單狀態(tài))
select sum(ifnull(price,0)) from tb_order;
distinct去重
比如我們要查詢所有下過單的用戶id集合辟拷，這里結(jié)果集中針對相同id要做去重處理
SELECT DISTINCT user_id FROM tb_order;
group by、having阐斜、order by
在查詢結(jié)果集之上再做查詢
結(jié)果集行號衫冻、rownum
case when then else end
不用max、min如何找最大谒出、最小
日期常見操作
now()
current_date隅俘、current_time
date_format()
unix_ timestamp
date_sub、date_add
字符串常見操作
concat
substr
left笤喳、right
if为居、男女性別互換
聯(lián)合更新
查詢結(jié)果集導(dǎo)入表
union、union all
聯(lián)合查詢
left join
right join
inner join杀狡、逗號連接
full join
這么多join方式如何選擇
你應(yīng)該知道的
聯(lián)合索引按左匹配
數(shù)據(jù)庫蒙畴、表結(jié)構(gòu)優(yōu)化
優(yōu)化的必要性
1、減輕關(guān)系型數(shù)據(jù)庫訪問壓力呜象，提升程序負載能力
2膳凝、加快響應(yīng)速度，提升用戶體驗
3恭陡、慢查詢引起的生產(chǎn)事故
4蹬音、表結(jié)構(gòu)重構(gòu)、數(shù)據(jù)遷移是巨大的工程
5休玩、關(guān)系型數(shù)據(jù)庫針對某些數(shù)據(jù)的存儲不是那么合適
數(shù)據(jù)庫表結(jié)構(gòu)合理設(shè)計
數(shù)據(jù)庫設(shè)計三范式
1著淆、每一列是不可再分的屬性值，同一列不能包含多個值哥捕，關(guān)系型數(shù)據(jù)庫的特點之一
地址：省牧抽、市、區(qū)遥赚、具體地址
2扬舒、數(shù)據(jù)庫表中每個實例或行必須可以被唯一地區(qū)分
需要有唯一能區(qū)分行數(shù)據(jù)的字段(主鍵)
3、一個數(shù)據(jù)表中不再包含已在其它表中包含的非主關(guān)鍵字信息（看情況）
訂單數(shù)據(jù)凫佛，包含用戶id讲坎，不再存儲用戶其它數(shù)據(jù)（看情況），（分布式系統(tǒng)中愧薛，比如下單時的用戶手機號...）
設(shè)計符合業(yè)務(wù)的表結(jié)構(gòu)
1晨炕、比如業(yè)務(wù)流程中，有明顯的區(qū)分使用用戶基礎(chǔ)數(shù)據(jù)跟用戶詳情數(shù)據(jù)可以對應(yīng)的建立用戶基礎(chǔ)表毫炉、用戶詳情表
2瓮栗、多對多的關(guān)系設(shè)計中，使用中間關(guān)聯(lián)表，如品牌表费奸、分類表弥激、品牌分類關(guān)聯(lián)表
主從同步、讀寫分離
緩存
MySQL緩存
服務(wù)本地緩存
分布式緩存
分庫分表
按業(yè)務(wù)垂直拆分
按數(shù)據(jù)水平拆分
NoSQL
NewSQL
存儲引擎優(yōu)化
數(shù)據(jù)歸檔
數(shù)據(jù)中臺愿阐、數(shù)倉
SQL優(yōu)化
分頁查詢優(yōu)化
建立索引微服、使用索引
索引應(yīng)建立在那些將用于JOIN、WHERE條件判斷缨历、ORDERBY排序的字段上
沒有命中索引的SQL不允許執(zhí)行
字段能小則小
索引不要建立在大字段上
索引建立在離散度大的字段上
批量插入
先導(dǎo)入數(shù)據(jù)再建立索引
某些情況下避免使用子查詢
索引字段查詢避免使用函數(shù)
禁止排序
group by ** order by null;
建立全文索引替換like模糊匹配
相同類型字段進行比較
減少鎖的獨占表的范圍
如何排查慢查詢
show [full] processlist
查看數(shù)據(jù)庫各SQL執(zhí)行進程情況
