外鍵和E.R圖

約束管理

1. 添加約束

添加普通約束的方式有兩種酌毡，一種是創(chuàng)建表的時(shí)候直接給字段添加相應(yīng)的約束骂远，另一種是通過(guò)修改表的方式添加約束

-- 1. 創(chuàng)建表的時(shí)候添加約束

-- 建表的時(shí)候可以在字段類型后面加一個(gè)或者多個(gè)約束

-- 2.通過(guò)添加約束索引的方式添加約束

-- alter table 表名 add constraint 索引名 約束(字段);

-- 說(shuō)明: 索引名 - 自己隨便命名;  約束 - 當(dāng)前想要添加的約束(但是只支持唯一約束园匹、主鍵約束和外鍵約束)

-- 示例:

alter table t_teacher add constraint uni_tel UNIQUE(teatel);   

2. 刪除約束

alter table 表名 drop index 約束索引;

-- 示例:

alter table t_teacher drop index uni_tel;

外鍵約束

1. 什么是外鍵：表中的某個(gè)字段的值是根據(jù)其他表中主鍵的值來(lái)確定的扎唾。那么這個(gè)字段就是外鍵

多對(duì)一的外鍵的添加： 將外鍵添加到多的一方對(duì)應(yīng)的表中

一對(duì)一的外鍵的添加： 將外鍵隨便添加到哪一方营密，同時(shí)添加值唯一約束

多對(duì)多的外鍵的添加： 關(guān)系型數(shù)據(jù)庫(kù)中，兩張表沒(méi)法實(shí)現(xiàn)多多的關(guān)系冰蘑，需要一個(gè)中間表和泌。(中間表有兩個(gè)外鍵分別參照多多的兩個(gè)表的主鍵)

2. 添加外鍵約束

alter table 表名1 add constraint 外鍵約束索引名 foreign key (字段1) references 表名2 (字段2);

-- 將表1中的字段1設(shè)置為外鍵，并且讓這個(gè)外鍵的值參照表2中的字段2

-- 也可在創(chuàng)建表的時(shí)候就添加外鍵約束

3. 刪除外鍵約束

alter table 表名 drop foreign key 外鍵索引名;

E.R圖

E-R圖也稱實(shí)體-聯(lián)系圖(Entity Relationship Diagram)祠肥，提供了表示實(shí)體類型武氓、屬性和聯(lián)系的方法，用來(lái)描述現(xiàn)實(shí)世界的概念模型。

它是描述現(xiàn)實(shí)世界關(guān)系概念模型的有效方法县恕。是表示概念關(guān)系模型的一種方式东羹。用“矩形框”表示實(shí)體型，矩形框內(nèi)寫明實(shí)體名稱忠烛；用“橢圓圖框”或圓角矩形表示實(shí)體的屬性属提，并用“實(shí)心線段”將其與相應(yīng)關(guān)系的“實(shí)體型”連接起來(lái)。

高級(jí)查詢

聚合

max() / min() / sum() / avg() / count()

SELECT max(score) as max_score FROM tb_score;  -- 獲取tb_score表中字段score的最大值

SELECT min(score) as min_score FROM tb_score;  -- 獲取tb_score表中字段score的最小值

SELECT sum(score) as sum_score FROM tb_score;  -- 獲取tb_score表中字段score的和

SELECT AVG(score) as avg_score FROM tb_score;  -- 獲取tb_score表中字段score的平均值

SELECT COUNT(score) as count_score FROM tb_score WHERE score>80;  -- 統(tǒng)計(jì)tb_score表中字段score大于80的個(gè)數(shù)

分組

-- SELECT 字段操作  FROM 表名 WHERE 條件 GROUP BY(字段2);

-- 將指定表中滿足條件的記錄按照字段2的進(jìn)行分組(值是一樣的在一個(gè)組里面), 然后再講每個(gè)分組作為整體按照指定字段進(jìn)行指定聚合操作

-- 注意:a.字段操作的位置除了分組字段不用聚合美尸，其他字段都必須聚合  b.分組的時(shí)候where要放到分組前對(duì)需要分組的數(shù)據(jù)進(jìn)行篩選

select stuid, avg(score) from tb_score group by(stuid);

-- having: 分組的時(shí)候冤议，在分組后用having代替where來(lái)對(duì)分組后的數(shù)據(jù)進(jìn)行篩選！Ｊ病恕酸！

select stuid, max(score) from tb_score group by(stuid) having max(score)>90;

select stuid, avg(score) from tb_score group by(stuid) having avg(score)>80;

去重

SELECT DISTINCT addr FROM t_student;

限制和分頁(yè)

-- 限制: select * from 表名 limit N;    -   查詢的時(shí)候只獲取前N條數(shù)據(jù)

-- 偏移: select * from 表名 limit M offset N;  -  跳過(guò)前N獲取M條數(shù)據(jù)

select * from tb_record limit 5;

select * from tb_record limit 4 offset 3; --  跳過(guò)前3條獲取4條數(shù)據(jù)

子查詢

子查詢：將一個(gè)查詢的結(jié)果作為另外一個(gè)查詢的條件或者查詢對(duì)象

-- 1.將查詢結(jié)果作為另外一個(gè)查詢的條件

-- 獲取成績(jī)大于90分的學(xué)生姓名

select stuname from tb_student where stuid in

(select stuid from tb_score where score>90);

-- 2.將一個(gè)查詢的結(jié)果作為查詢對(duì)象提供給另外一個(gè)查詢。但是第一個(gè)查詢結(jié)果需要重命名

select score from (SELECT stuid,score from tb_score where score>80) as t2;

連接查詢

1. 直接連接

-- select * from 表名1,表名2,表名3 where 連接條件 查詢條件;

-- 查詢所有學(xué)生的名字和學(xué)院名字

select stuname, collname from tb_student, tb_college where tb_student.colid=tb_college.collid;

2. 內(nèi)連接

-- SELECT * FROM 表1 inner join 表2 on 表2的連接條件 inner join 表3 on 表3的連接條件 ...;

-- 查詢所有學(xué)生的名字和學(xué)院名字

select stuname, collname from tb_student inner join tb_college

on tb_student.colid=tb_college.collid;

3. 外連接

外連接分為左外連接胯陋、右外連接和全連接蕊温， 但是在MySQL中支持左外連接和右外連接

-- 左外連接：將左表中對(duì)應(yīng)字段的所有數(shù)據(jù)取出，然后再對(duì)應(yīng)的右表中字段的值遏乔，如果右表對(duì)應(yīng)的值不存在結(jié)果就為null

-- 右外連接：將右表中對(duì)應(yīng)字段的所有數(shù)據(jù)取出义矛，然后再對(duì)應(yīng)的左表中字段的值，如果左表對(duì)應(yīng)的值不存在結(jié)果就為null

select * from 表1 left join 表2 on 連接條件;

select * from 表1 right join 表2 on 連接條件;

-- 查詢所有學(xué)生的姓名和選課數(shù)量(左外連接和子查詢)

select stuname, ifnull(c_count,0) from tb_student as t1 left join

(select sid, count(cid) as c_count from tb_record group by (sid)) as t2 on stuid=sid;

-- 如果不用外連接按灶，沒(méi)有選課的學(xué)生選不出來(lái)Ｖ⒏铩筐咧！

事務(wù)

數(shù)據(jù)庫(kù)事務(wù)(Database Transaction) 鸯旁，是指作為單個(gè)邏輯工作單元執(zhí)行的一系列操作，要么完全地執(zhí)行量蕊，要么完全地不執(zhí)行铺罢。

一個(gè)數(shù)據(jù)庫(kù)事務(wù)通常包含對(duì)數(shù)據(jù)庫(kù)進(jìn)行讀或?qū)懙囊粋€(gè)操作序列。它的存在包含有以下兩個(gè)目的：

1残炮、為數(shù)據(jù)庫(kù)操作提供了一個(gè)從失敗中恢復(fù)到正常狀態(tài)的方法韭赘，同時(shí)提供了數(shù)據(jù)庫(kù)即使在異常狀態(tài)下仍能保持一致性的方法。
2势就、當(dāng)多個(gè)應(yīng)用程序在并發(fā)訪問(wèn)數(shù)據(jù)庫(kù)時(shí)泉瞻，可以在這些應(yīng)用程序之間提供一個(gè)隔離方法，以防止彼此的操作互相干擾苞冯。

并非任意的對(duì)數(shù)據(jù)庫(kù)的操作序列都是數(shù)據(jù)庫(kù)事務(wù)袖牙。事務(wù)應(yīng)該具有4個(gè)屬性：原子性、一致性舅锄、隔離性鞭达、持久性。這四個(gè)屬性通常稱為ACID特性。

原子性（Atomicity）：事務(wù)作為一個(gè)整體被執(zhí)行畴蹭，包含在其中的對(duì)數(shù)據(jù)庫(kù)的操作要么全部被執(zhí)行坦仍，要么都不執(zhí)行。
一致性（Consistency）：事務(wù)應(yīng)確保數(shù)據(jù)庫(kù)的狀態(tài)從一個(gè)一致狀態(tài)轉(zhuǎn)變?yōu)榱硪粋€(gè)一致狀態(tài)叨襟。一致狀態(tài)的含義是數(shù)據(jù)庫(kù)中的數(shù)據(jù)應(yīng)滿足完整性約束繁扎。
隔離性（Isolation）：多個(gè)事務(wù)并發(fā)執(zhí)行時(shí)，一個(gè)事務(wù)的執(zhí)行不應(yīng)影響其他事務(wù)的執(zhí)行芹啥。
持久性（Durability）：一個(gè)事務(wù)一旦提交锻离，他對(duì)數(shù)據(jù)庫(kù)的修改應(yīng)該永久保存在數(shù)據(jù)庫(kù)中。

-- 開啟事務(wù)環(huán)境

begin;

-- ...（需要執(zhí)行的多個(gè)操作對(duì)應(yīng)的sql語(yǔ)句）

-- 提交事務(wù)(只有begin到commit之間的所有的sql都執(zhí)行成功墓怀，才會(huì)執(zhí)行commit; 否則執(zhí)行rollback)

COMMIT;

-- 事務(wù)回滾(放棄beigin到commit之間執(zhí)行成功的所有sql語(yǔ)句的結(jié)果)

ROLLBACK;

視圖

視圖是關(guān)系型數(shù)據(jù)庫(kù)中將一組查詢指令構(gòu)成的結(jié)果集組合成可查詢的數(shù)據(jù)表的對(duì)象汽纠。簡(jiǎn)單的說(shuō)，視圖就是虛擬的表傀履，但與數(shù)據(jù)表不同的是虱朵，數(shù)據(jù)表是一種實(shí)體結(jié)構(gòu)，而視圖是一種虛擬結(jié)構(gòu)钓账，你也可以將視圖理解為保存在數(shù)據(jù)庫(kù)中被賦予名字的SQL語(yǔ)句碴犬。

使用視圖可以獲得以下好處：

• 可以將實(shí)體數(shù)據(jù)表隱藏起來(lái)，讓外部程序無(wú)法得知實(shí)際的數(shù)據(jù)結(jié)構(gòu)梆暮，讓訪問(wèn)者可以使用表的組成部分而不是整個(gè)表服协，降低數(shù)據(jù)庫(kù)被攻擊的風(fēng)險(xiǎn)。

• 在大多數(shù)的情況下視圖是只讀的（更新視圖的操作通常都有諸多的限制）啦粹，外部程序無(wú)法直接透過(guò)視圖修改數(shù)據(jù)偿荷。

• 重用SQL語(yǔ)句，將高度復(fù)雜的查詢包裝在視圖表中唠椭，直接訪問(wèn)該視圖即可取出需要的數(shù)據(jù)跳纳；也可以將視圖視為數(shù)據(jù)表進(jìn)行連接查詢。

• 視圖可以返回與實(shí)體數(shù)據(jù)表不同格式的數(shù)據(jù)贪嫂，

1. 創(chuàng)建視圖

create view 視圖名 as sql查詢語(yǔ)句;

-- 示例:

create view vw_student

as SELECT * FROM tb_student;

2. 使用視圖 - 視圖在用的時(shí)候可以直接當(dāng)成表來(lái)使用

-- 示例:

select * FROM vw_student;

select stuname, collname from vw_student, tb_college where vw_student.colid=tb_college.collid;

3.更新視圖 - 更新一個(gè)視圖將更新其基表
但是并非所有的視圖都是可更新的寺庄。基本上可以說(shuō)力崇，如果MySQL不能正確地確定被更新的基數(shù)據(jù)您旁，則不允許更新（包括插入和刪除）搁凸。這實(shí)際上意味著揭鳞，如果視圖定義中有以下操作湿滓，則不能進(jìn)行視圖更新：

• 分組
• 聯(lián)結(jié)
• 子查詢
• 并(union)
• 聚合函數(shù)
• Distinct

存儲(chǔ)過(guò)程

如果在實(shí)現(xiàn)用戶的某些需求時(shí)，需要編寫一組復(fù)雜的SQL語(yǔ)句才能實(shí)現(xiàn)的時(shí)候台猴，那么我們就可以將這組復(fù)雜的SQL語(yǔ)句集提前編寫在數(shù)據(jù)庫(kù)中朽合，由JDBC調(diào)用來(lái)執(zhí)行這組SQL語(yǔ)句俱两。把編寫在數(shù)據(jù)庫(kù)中的SQL語(yǔ)句集稱為存儲(chǔ)過(guò)程。

使用存儲(chǔ)過(guò)程的好處：
1.存儲(chǔ)過(guò)程是通過(guò)處理封裝在容易使用的單元中曹步，簡(jiǎn)化了復(fù)雜的操作宪彩。并且使用存儲(chǔ)過(guò)程比使用單獨(dú)的SQL語(yǔ)句要快。
2.存儲(chǔ)過(guò)程度任何應(yīng)用程序都是可重用的和透明的讲婚。存儲(chǔ)過(guò)程將數(shù)據(jù)庫(kù)接口暴露給所有的應(yīng)用程序尿孔，開發(fā)人員可以使用同一存儲(chǔ)過(guò)程，防止錯(cuò)誤筹麸，提高安全性活合。
3.簡(jiǎn)化對(duì)變動(dòng)的管理。如果表名列名或業(yè)務(wù)邏輯有變化物赶，只需要更改存儲(chǔ)過(guò)程的代碼白指。使用它的人員甚至不需要知道這些變化。

話句話說(shuō)酵紫，存儲(chǔ)過(guò)程的好處主要有3點(diǎn)：簡(jiǎn)單告嘲、安全、高性能奖地。不過(guò)它也有一定的缺陷：
1.存儲(chǔ)過(guò)程的開發(fā)和維護(hù)都不容易橄唬；
2.只有少數(shù)數(shù)據(jù)庫(kù)系統(tǒng)能調(diào)試存儲(chǔ)過(guò)程，而MySQL數(shù)據(jù)庫(kù)不支持調(diào)試存儲(chǔ)過(guò)程参歹；
3.可移植性比較差仰楚。

--創(chuàng)建存儲(chǔ)過(guò)程
create procedure productpricing()    -- 括號(hào)內(nèi)可以帶參數(shù)
begin
  select avg(prod_price) as priceaverage from products;
end;

-- 使用存儲(chǔ)過(guò)程
call productpricing();  -- 可以傳參數(shù)

-- 刪除存儲(chǔ)過(guò)程
drop procedure productpricing;

索引

索引相當(dāng)于書本的目錄，為表創(chuàng)建索引可以加速查詢（用空間換時(shí)間）犬庇。

索引的優(yōu)點(diǎn)：

• 索引大大減少了服務(wù)器需要掃描的數(shù)據(jù)量僧界；
• 索引可以幫助服務(wù)器避免排序和臨時(shí)表；
• 索引可以將隨機(jī)I/O變?yōu)轫樞騃/O械筛。

索引雖然很好捎泻，但是不能濫用：

• 索引會(huì)占用額外的空間

• 索引會(huì)讓增刪改變得更慢

如果哪個(gè)列經(jīng)常被用于查詢的篩選條件那么就應(yīng)該在這個(gè)列上建立索引飒炎÷裼矗總地來(lái)說(shuō)，只有當(dāng)索引幫助存儲(chǔ)引擎快速查找到記錄帶來(lái)的好處大于其帶來(lái)的額外工作時(shí)郎汪，索引才是有效的赤赊。

主鍵上有默認(rèn)索引(唯一索引)

1. 創(chuàng)建索引

-- create index 索引名 on 表名 (字段);    -- 給指定表的指定字段添加索引

-- create unique index 索引名 on 表名 (字段);  -- 給指定表的指定字段添加唯一索引

-- 示例:

create index idx_stuname on tb_student(stuname);

create unique index idx_stuname on tb_student(stuname);

2. 刪除索引

-- alter table 表名 drop index 索引名; -- 刪除指定索引，唯一索引也是這樣刪

-- 示例:

alter table tb_student drop index idx_stuname;

3. 執(zhí)行索引

-- explain： 獲取執(zhí)行計(jì)劃

explain select * from tb_student where stuid=110;

explain select * from tb_student where stuname='張三';

-- 注意：模糊查詢?nèi)绻?和_開頭煞赢，索引無(wú)效!!!

explain select * from tb_student where stuname='%三';  -- error

explain select * from tb_student where stuname='_三';  -- error

索引類型

在MySQL中抛计，索引是在存儲(chǔ)引擎層而不是服務(wù)器層實(shí)現(xiàn)的。所以照筑，并沒(méi)有統(tǒng)一的索引標(biāo)準(zhǔn)：不同存儲(chǔ)引擎的索引的工作方式并不一樣吹截，也不是所有的存儲(chǔ)引擎都支持所有類型的索引瘦陈。即使多個(gè)存儲(chǔ)引擎支持同一種索引，其底層的實(shí)現(xiàn)也可能不同波俄。

主鍵索引 PRIMARY KEY

主鍵索引是一種特殊的索引晨逝，我們一般在創(chuàng)建表時(shí)同時(shí)創(chuàng)建主鍵，主鍵是唯一的且不能為空

唯一索引 UNIQUE

唯一索引要求索引的列是唯一的懦铺，但可以為空

普通索引 INDEX

普通索引是最基本的索引捉貌，它沒(méi)有任何限制《睿可以在創(chuàng)建表的時(shí)候指定趁窃，也可以在之后創(chuàng)建，僅加速查詢

組合索引

組合索引急前，即一個(gè)索引包含多個(gè)列醒陆，專門用于組合搜索，其效率大于索引合并

全文索引 FULLTEXT

全文索引（也稱全文檢索）是目前搜索引擎使用的一種關(guān)鍵技術(shù)裆针。它能夠利用分詞技術(shù)等多種算法智能分析出文本文字中關(guān)鍵字詞的頻率及重要性统求，然后按照一定的算法規(guī)則智能地篩選出我們想要的搜索結(jié)果。

索引結(jié)構(gòu)類型

B-Tree索引

談?wù)撍饕龝r(shí)据块，如果沒(méi)有特別指明類型码邻，那說(shuō)的多半是B-Tree索引，它使用B-Tree數(shù)據(jù)結(jié)構(gòu)來(lái)存儲(chǔ)數(shù)據(jù)另假。大多數(shù)MySQL引擎都支持這種索引像屋，Archive引擎是一個(gè)例外。

B-Tree索引能夠加快訪問(wèn)數(shù)據(jù)的速度边篮，因?yàn)榇鎯?chǔ)引擎不在需要進(jìn)行全表掃描來(lái)獲取需要的數(shù)據(jù)己莺，取而代之的是從索引的根節(jié)點(diǎn)開始進(jìn)行搜索。根節(jié)點(diǎn)的槽中存放了指向子節(jié)點(diǎn)的指針戈轿，存儲(chǔ)引擎根據(jù)這些指針指向下層查找凌受。通過(guò)比較節(jié)點(diǎn)頁(yè)的值和要查找的值可以找到合適的指針進(jìn)入下層子節(jié)點(diǎn)，這些指針實(shí)際上定義了子節(jié)點(diǎn)頁(yè)中值的上限和下限思杯。最終存儲(chǔ)引擎要么是找到對(duì)應(yīng)的值胜蛉，要么該記錄不存在。

可以使用B-Tree索引的查詢類型色乾。B-Tree索引適用于全鍵值誊册、鍵值范圍或鍵前綴查找。其中鍵前綴查找只適用于根據(jù)最左前綴的查找暖璧。

全值匹配
全值匹配指的是和索引中的所有列進(jìn)行匹配案怯，例如可用于查找姓名為 Cuba Allen、出生于1980-01-01的人澎办。

匹配最左前綴
前面提到的索引可用于查找所有姓為Allen的人嘲碱，即只使用索引的第一列金砍。

匹配列前綴
也可以只匹配某一列的值的開頭部分。例如索引可用于查找所有以J開頭的姓的人麦锯。這里也只使用了索引的第一列捞魁。

匹配范圍值
例如索引可用于查找姓在Allen和Barry之間的人。這里也只使用了索引的第一列离咐。

精確匹配某一列并范圍匹配另外一列
索引也可用于查找所有姓為Allen谱俭，并且名字是字母K開頭（比如Kim、Karl等）的人宵蛀。即第一列l(wèi)ast_name全匹配昆著，第二列first_name范圍匹配。

只訪問(wèn)索引的查詢
B-Tree通呈跆眨可以支持“只訪問(wèn)索引的查詢”凑懂，即查詢只需要訪問(wèn)索引，而無(wú)須訪問(wèn)數(shù)據(jù)行梧宫。

哈希索引

哈希索引基于哈希表實(shí)現(xiàn)接谨，只有精確匹配索引所有列的查詢才有效。對(duì)于每一行數(shù)據(jù)塘匣，存儲(chǔ)引擎都會(huì)對(duì)所有的索引列計(jì)算一個(gè)哈希碼脓豪。在MySQL中，只有Memory引擎顯式支持哈希索引忌卤。這也是Memory引擎表的默認(rèn)索引類型扫夜，Memory引擎同時(shí)也支持B-Tree索引。

空間數(shù)據(jù)索引(R-Tree)

MyISAM表支持空間索引驰徊，可以用作地理數(shù)據(jù)存儲(chǔ)笤闯。必須使用MySQL的GIS函數(shù)來(lái)維護(hù)數(shù)據(jù)，但MySQL的GIS支持并不完善棍厂，開源關(guān)系數(shù)據(jù)庫(kù)系統(tǒng)中對(duì)GIS的解決方案做的比較好的是PostgreSQL的PostGIS颗味。

全文索引 FULLTEXT

目前只有MyISAM引擎支持。其可以在CREATE TABLE 牺弹，ALTER TABLE 浦马，CREATE INDEX 使用，不過(guò)目前只有 CHAR例驹、VARCHAR 捐韩，TEXT 列上可以創(chuàng)建全文索引退唠。

索引優(yōu)化（創(chuàng)建索引策略）

1.對(duì)維度高（數(shù)據(jù)列不重復(fù)出現(xiàn)的數(shù)量越多鹃锈，維度就越高）的列創(chuàng)建索引；

2.對(duì) where...on..., group by, order by 中出現(xiàn)的列使用索引瞧预；

3.對(duì)較小的數(shù)據(jù)列使用索引屎债，這樣會(huì)使索引文件更小仅政，同時(shí)內(nèi)存中也可以裝載更多的索引鍵；

4.為較長(zhǎng)的字符串使用前綴索引盆驹；

5.不要過(guò)多創(chuàng)建索引圆丹，除了增加額外的磁盤空間外，對(duì)于DML操作的速度影響很大躯喇，因?yàn)槠涿吭鰟h改一次就得從新建立索引辫封；

6.使用組合索引，可以減少文件索引大小廉丽，在使用時(shí)速度要優(yōu)于多個(gè)單列索引倦微；

7.不要在索引列上加函數(shù)或者運(yùn)算，這樣不會(huì)使用索引正压；

8.避免前導(dǎo)模糊查詢欣福，如select * from user where name like '%明' 不適用索引；

9.少使用負(fù)向條件(!=焦履、<>拓劝、not in、not like嘉裤、not exists...)郑临，因?yàn)椴粫?huì)使用索引（不絕對(duì)，有時(shí)會(huì)走范圍索引屑宠，取決于范圍大心恋帧）；

10....

索引失效

索引失效的幾種情況：
1.當(dāng)使用左或者左右模糊匹配的時(shí)候侨把，比如 like %明 或者 like %明% 這兩種方式都會(huì)造成索引失效犀变。

2.當(dāng)我們?cè)诓樵儣l件中對(duì)索引列使用聚合函數(shù)或者進(jìn)行表達(dá)式計(jì)算，也是無(wú)法走索引的秋柄。

3.對(duì)索引隱式類型轉(zhuǎn)換
MySQL 在遇到字符串和數(shù)字比較的時(shí)候获枝，會(huì)自動(dòng)把字符串轉(zhuǎn)為數(shù)字，然后再進(jìn)行比較骇笔。如果字符串是索引列省店，而條件語(yǔ)句中的輸入?yún)?shù)是數(shù)字的話，那么索引列會(huì)發(fā)生隱式類型轉(zhuǎn)換笨触，由于隱式類型轉(zhuǎn)換是通過(guò) CAST 函數(shù)實(shí)現(xiàn)的懦傍，等同于對(duì)索引列使用了函數(shù)，所以就會(huì)導(dǎo)致索引失效芦劣。

4.聯(lián)合索引要能正確使用需要遵循最左匹配原則粗俱，也就是按照最左優(yōu)先的方式進(jìn)行索引的匹配，否則就會(huì)導(dǎo)致索引失效虚吟。

5.在 WHERE 子句中寸认，如果在 OR 前的條件列是索引列签财，而在 OR 后的條件列不是索引列，那么索引會(huì)失效偏塞。

數(shù)據(jù)庫(kù)設(shè)計(jì)原則（MySQL）

1.一般情況下唱蒸，應(yīng)該盡量使用可以正確存儲(chǔ)數(shù)據(jù)的最小數(shù)據(jù)類型。數(shù)據(jù)類型不一樣灸叼，存儲(chǔ)的執(zhí)行效率也不一樣神汹。最好使用適度的整型數(shù)據(jù)類型，例如int之類的數(shù)據(jù)古今，這樣在做查詢或者字段排序的時(shí)候速度是最快的慎冤。

2.盡量避免NULL值的時(shí)候，因?yàn)檫@樣會(huì)增加數(shù)據(jù)庫(kù)處理的開銷沧卢。但是也要考慮實(shí)際情況蚁堤，不要一味的為了避免空值而全部設(shè)置為not null，具體的設(shè)置情況要根據(jù)項(xiàng)目的具體業(yè)務(wù)來(lái)但狭。

3.注意char和varchar的使用披诗，char適合存儲(chǔ)的大小基本固定在一個(gè)范圍之內(nèi)，經(jīng)常發(fā)生變動(dòng)的數(shù)據(jù)立磁。而varchar則不一樣呈队，varchar適合那種大小不固定，并未經(jīng)常發(fā)生改動(dòng)的數(shù)據(jù)唱歧。需要注意的是varchar定義的長(zhǎng)度最好可以剛好夠用宪摧，不然會(huì)照成資源的浪費(fèi)，影響整體數(shù)據(jù)庫(kù)的性能和存儲(chǔ)空間颅崩。

4.能用整數(shù)標(biāo)示几于，最好利用整數(shù)標(biāo)示，因?yàn)檫@樣開銷最小沿后，效率也是最高的沿彭。

5.適當(dāng)使用索引，使用索引會(huì)大大提升查詢效率尖滚，同時(shí)降低在被索引的表上INSERT和DELETE效率喉刘；

6.在設(shè)計(jì)表和設(shè)計(jì)查詢語(yǔ)言的時(shí)候就要主要盡量避免大規(guī)模的關(guān)聯(lián)查詢，因?yàn)檫@樣會(huì)嚴(yán)重影響數(shù)據(jù)庫(kù)的查詢效率漆弄。

7.在設(shè)計(jì)數(shù)據(jù)庫(kù)的時(shí)候要根據(jù)具體的業(yè)務(wù)設(shè)計(jì)出合理的數(shù)據(jù)庫(kù)表和字段睦裳。不僅要考慮范式，也要考慮反范式撼唾。在不影響數(shù)據(jù)安全和數(shù)據(jù)冗余的情況下廉邑，可以適當(dāng)?shù)目紤]混合范式的設(shè)計(jì)。

• 第一范式：屬性具有原子性，不可再分解鬓催，即不能表中有表肺素；

• 第二范式：唯一性約束恨锚，每條記錄有唯一標(biāo)示宇驾，所有的非主鍵字段均需依賴于主鍵字段；

• 第三范式：冗余性約束猴伶，非主鍵字段間不能相互依賴课舍；

8.使用緩存表和匯總表進(jìn)行數(shù)據(jù)庫(kù)的查詢優(yōu)化。

9.大型數(shù)據(jù)庫(kù)：
（1）數(shù)據(jù)分離：長(zhǎng)文本短文本分離他挎， 長(zhǎng)文本存儲(chǔ)在k-v系統(tǒng)中筝尾；當(dāng)前數(shù)據(jù)與歷史數(shù)據(jù)分離。
（2）負(fù)載均衡办桨；
（3）分布式存儲(chǔ)筹淫。

數(shù)據(jù)庫(kù)優(yōu)化

數(shù)據(jù)庫(kù)優(yōu)化可以分為架構(gòu)優(yōu)化、硬件優(yōu)化呢撞、DB優(yōu)化损姜、SQL優(yōu)化。

架構(gòu)優(yōu)化

一般來(lái)說(shuō)在高并發(fā)的場(chǎng)景下對(duì)架構(gòu)層進(jìn)行優(yōu)化其效果最為明顯殊霞，常見的優(yōu)化手段有：分布式緩存摧阅，讀寫分離，分庫(kù)分表等绷蹲，每種優(yōu)化手段又適用于不同的應(yīng)用場(chǎng)景棒卷。

分布式緩存
有句老話說(shuō)的好，性能不夠祝钢，緩存來(lái)湊比规。當(dāng)需要在架構(gòu)層進(jìn)行優(yōu)化時(shí)我們第一時(shí)間就會(huì)想到緩存這個(gè)神器，在應(yīng)用與數(shù)據(jù)庫(kù)之間增加一個(gè)緩存服務(wù)拦英，如Redis或Memcache苞俘。
當(dāng)接收到查詢請(qǐng)求后，我們先查詢緩存龄章，判斷緩存中是否有數(shù)據(jù)吃谣，有數(shù)據(jù)就直接返回給應(yīng)用，如若沒(méi)有再查詢數(shù)據(jù)庫(kù)做裙，并加載到緩存中岗憋，這樣就大大減少了對(duì)數(shù)據(jù)庫(kù)的訪問(wèn)次數(shù)，自然而然也提高了數(shù)據(jù)庫(kù)性能锚贱。
不過(guò)需要注意的是仔戈，引入分布式緩存后系統(tǒng)需要考慮如何應(yīng)對(duì)緩存穿透、緩存擊穿和緩存雪崩的問(wèn)題。

讀寫分離
一主多從监徘，讀寫分離晋修，主動(dòng)同步，是一種常見的數(shù)據(jù)庫(kù)架構(gòu)優(yōu)化手段凰盔。
一般來(lái)說(shuō)當(dāng)你的應(yīng)用是讀多寫少墓卦，數(shù)據(jù)庫(kù)扛不住讀壓力的時(shí)候，采用讀寫分離户敬，通過(guò)增加從庫(kù)數(shù)量可以線性提升系統(tǒng)讀性能落剪。
主庫(kù)，提供數(shù)據(jù)庫(kù)寫服務(wù)尿庐；從庫(kù)忠怖，提供數(shù)據(jù)庫(kù)讀能力；主從之間抄瑟，通過(guò)binlog同步數(shù)據(jù)凡泣。

分庫(kù)分表
分庫(kù)分表就是要將大量數(shù)據(jù)分散到多個(gè)數(shù)據(jù)庫(kù)和數(shù)據(jù)表中，使每個(gè)數(shù)據(jù)庫(kù)中數(shù)據(jù)量小響應(yīng)速度快皮假，以此來(lái)提升數(shù)據(jù)庫(kù)整體性能鞋拟。
當(dāng)應(yīng)用業(yè)務(wù)數(shù)據(jù)量很大，單庫(kù)容量成為性能瓶頸后钞翔，采用水平切分严卖，可以降低數(shù)據(jù)庫(kù)單庫(kù)容量，提升數(shù)據(jù)庫(kù)寫性能布轿。
根據(jù)業(yè)務(wù)耦合性哮笆，將關(guān)聯(lián)度低的不同表存儲(chǔ)在不同的數(shù)據(jù)庫(kù)。垂直切分汰扭，做法與大系統(tǒng)拆分為多個(gè)小系統(tǒng)類似稠肘，按業(yè)務(wù)分類進(jìn)行獨(dú)立劃分。與"微服務(wù)治理"的做法相似萝毛，每個(gè)微服務(wù)使用單獨(dú)的一個(gè)數(shù)據(jù)庫(kù)项阴。

硬件優(yōu)化

我們使用數(shù)據(jù)庫(kù)，不管是讀操作還是寫操作笆包，最終都是要訪問(wèn)磁盤环揽，所以說(shuō)磁盤的性能決定了數(shù)據(jù)庫(kù)的性能。一塊PCIE固態(tài)硬盤的性能是普通機(jī)械硬盤的幾十倍不止庵佣。

DB優(yōu)化

SQL執(zhí)行慢有時(shí)候不一定完全是SQL問(wèn)題歉胶，手動(dòng)安裝一臺(tái)數(shù)據(jù)庫(kù)而不做任何參數(shù)調(diào)整，再怎么優(yōu)化SQL都無(wú)法讓其性能最大化巴粪。要讓一臺(tái)數(shù)據(jù)庫(kù)實(shí)例完全發(fā)揮其性能通今，首先我們就得先優(yōu)化數(shù)據(jù)庫(kù)的實(shí)例參數(shù)粥谬。

數(shù)據(jù)庫(kù)實(shí)例參數(shù)優(yōu)化遵循三句口訣：日志不能小、緩存足夠大辫塌、連接要夠用漏策。

SQL優(yōu)化

SQL優(yōu)化流程：

1.查看執(zhí)行計(jì)劃 explain sql
2.如果有告警信息，查看告警信息 show warnings;
3.查看SQL涉及的表結(jié)構(gòu)和索引信息
4.根據(jù)執(zhí)行計(jì)劃臼氨，思考可能的優(yōu)化點(diǎn)
5.按照可能的優(yōu)化點(diǎn)執(zhí)行表結(jié)構(gòu)變更掺喻、增加索引、SQL改寫等操作
6.查看優(yōu)化后的執(zhí)行時(shí)間和執(zhí)行計(jì)劃
7.如果優(yōu)化效果不明顯一也，重復(fù)第四步操作

SQL優(yōu)化方法：

1.避免使用select *巢寡，select *不走覆蓋索引喉脖，會(huì)出現(xiàn)大量的回表操作椰苟，從而導(dǎo)致SQL的性能很低。

2.使用union all 代替union树叽，因?yàn)閡nion去重的過(guò)程需要遍歷舆蝴，排序和比較，更加消耗資源和時(shí)間题诵。

3.小表驅(qū)動(dòng)大表洁仗，in和exists的核心思想就是小表驅(qū)動(dòng)大表

-- 假如有order和user兩張表，其中order表有10000條數(shù)據(jù)性锭，而user表有100條數(shù)據(jù)赠潦。這時(shí)如果想查一下，所有有效的用戶下過(guò)的訂單列表草冈。
select * from order where user_id in (select id from user where status=1)

select * from order where exists (select 1 from user where order.user_id = user.id and status=1)

-- 前面提到的這種業(yè)務(wù)場(chǎng)景她奥，使用in關(guān)鍵字去實(shí)現(xiàn)業(yè)務(wù)需求，更加合適怎棱。
-- 因?yàn)槿绻鹲ql語(yǔ)句中包含了in關(guān)鍵字哩俭，則它會(huì)優(yōu)先執(zhí)行in里面的子查詢語(yǔ)句，然后再執(zhí)行in外面的語(yǔ)句拳恋。
-- 如果in里面的數(shù)據(jù)量很少凡资，作為條件查詢速度更快。
-- 而如果sql語(yǔ)句中包含了exists關(guān)鍵字谬运，它優(yōu)先執(zhí)行exists左邊的語(yǔ)句（即主查詢語(yǔ)句）隙赁。

4.批量操作

-- 逐條插入
insert into order(id,code,user_id) values(123,'001',100)；
-- 批量插入
insert into order(id,code,user_id) values(123,'001',100),(124,'002',100),(125,'003',101);
-- 這樣只需要遠(yuǎn)程請(qǐng)求一次數(shù)據(jù)庫(kù)梆暖，sql性能會(huì)得到提升伞访，數(shù)據(jù)量越多，提升越大式廷。
-- 但不建議一次批量操作太多的數(shù)據(jù)咐扭，建議每批數(shù)據(jù)盡量控制在500以內(nèi)。如果數(shù)據(jù)多于500，則分多批次處理蝗肪。

5.增量查詢

-- 如果數(shù)據(jù)很多的話袜爪，性能會(huì)很差；
select * from user;
-- 按id和時(shí)間升序薛闪，分批次查詢辛馆，每次只查100條記錄。通過(guò)這種增量查詢的方式豁延，能夠提升單次查詢的效率昙篙。
select * from user 
where id>#{lastId} and create_time >= #{lastCreateTime} 
limit 100;

6.連接查詢代替子查詢

mysql執(zhí)行子查詢時(shí)，需要?jiǎng)?chuàng)建臨時(shí)表诱咏，查詢完畢后苔可，需要再刪除這些臨時(shí)表，有一些額外的性能消耗袋狞。

select * from order
where user_id in (select id from user where status=1);

select o.* from order o
inner join user u on o.user_id = u.id
where u.status=1

7.join的表不宜超過(guò)3個(gè)

如果join太多焚辅，mysql在選擇索引的時(shí)候會(huì)非常復(fù)雜，很容易選錯(cuò)索引苟鸯。

8.使用left join關(guān)聯(lián)查詢時(shí)同蜻，左邊要用小表，右邊可以用大表早处。如果能用inner join的地方湾蔓，盡量少用left join。

9.索引優(yōu)化砌梆，注意有時(shí)SQL語(yǔ)句會(huì)導(dǎo)致不走索引

10.提升group by的效率

使用where條件在分組前默责，就把多余的數(shù)據(jù)過(guò)濾掉了，這樣分組時(shí)效率就會(huì)更高一些么库。
其實(shí)這是一種思路傻丝，不僅限于group by的優(yōu)化。我們的sql語(yǔ)句在做一些耗時(shí)的操作之前诉儒，應(yīng)盡可能縮小數(shù)據(jù)范圍葡缰，這樣能提升sql整體的性能。