數(shù)據(jù)庫分類
關(guān)系型數(shù)據(jù)庫庫：Relational Database Management System (RDBMS)
- oracle
- mysql：web使用最廣泛的關(guān)系型數(shù)據(jù)庫
- sql server
- sqlite:輕量級數(shù)據(jù)庫
非關(guān)系型數(shù)據(jù)庫
- redis
- mysql
- mongodb
數(shù)據(jù)庫設(shè)計范式
設(shè)計關(guān)系數(shù)據(jù)庫時，遵從不同的規(guī)范要求，設(shè)計出合理的關(guān)系型數(shù)據(jù)庫，這些不同的規(guī)范要求被稱為不同的范式，各種范式呈遞次規(guī)范，越高的范式數(shù)據(jù)庫冗余越小。
目前關(guān)系數(shù)據(jù)庫有六種范式：第一范式（1NF）、第二范式（2NF）、第三范式（3NF）、巴斯-科德范式（BCNF）、第四范式(4NF）和第五范式（5NF，又稱完美范式）。
范式越高，冗余最低，一般到三范式，再往上，表越多，可能導(dǎo)致查詢效率下降。所以有時為了提高運行效率，可以讓數(shù)據(jù)冗余(反三范式，一般某個數(shù)據(jù)經(jīng)常被訪問時，比如數(shù)據(jù)表里存放了語文數(shù)學(xué)英語成績，但是如果在某個時間經(jīng)常要得到它的總分，每次都要進行計算會降低性能，可以加上總分這個冗余字段)。
后面的范式是在滿足前面范式的基礎(chǔ)上，比如滿足第二范式的一定滿足第一范式。
☆☆☆第一范式（1NF）：確保每一列的原子性
☆☆☆第二范式:非主鍵字段必須依賴于主鍵字段
☆☆☆第三范式：在1NF基礎(chǔ)上，除了主鍵以外的其它列都不傳遞依賴于主鍵列
mysql的安裝(ubuntu)
- 安裝：sudo apt-get install mysql-server
- 啟動：sudo service mysql start
- 停止：sudo service mysql stop
- 重啟：sudo service mysql restart
- 設(shè)置密碼：mysqladmin -u root password mysql（window）
- 啟動：net start mysql（window）
配置
數(shù)據(jù)庫的創(chuàng)建與使用
- 連接：mysql -uroot -p
- 創(chuàng)建：在登錄的狀態(tài)下執(zhí)行 create database 數(shù)據(jù)庫名 charset=utf8;
（☆☆☆不指定 charset 那么默認(rèn)是拉丁字符集，會有下面的報錯信息）
在進行數(shù)據(jù)庫遷移、創(chuàng)建時報錯，信息（ERROR 1366 (HY000): Incorrect string value: '\xE5\xB0\x84\xE9\x9B\x95...' for column 'name' at row 1
） - 顯示：show databases;
- 使用：use 數(shù)據(jù)庫名;
- 刪除：drop database 數(shù)據(jù)庫名;
- 備份：mysqldump -uroot -p 數(shù)據(jù)庫名 > python.sql
- 恢復(fù)：mysql -uroot -p 新數(shù)據(jù)庫名 < python.sql (新數(shù)據(jù)庫名已經(jīng)創(chuàng)建好了)
- 退出：exit 、ctrl+d（清屏ctrl+l、退出執(zhí)行語句ctrl+c+enter、掛起ctrl + z、從掛起返回fg）
1、Ctrl+C比較暴力，就是發(fā)送Terminal到當(dāng)前的程序，比如你正在運行一個查找功能，文件正在查找中，Ctrl+C就會強制結(jié)束當(dāng)前的這個進程。
2、Ctrl+Z是把當(dāng)前的程序掛起，暫停執(zhí)行這個程序，比如你正在mysql終端中，需要出來搞點其他的文件操作，又不想退出mysql終端（因為下次還得輸入用戶名密碼進入，挺麻煩），于是可以ctrl+z將mysql掛起，然后進行其他操作，然后輸入fg回車后就可以回來，當(dāng)然可以掛起好多進程到后臺，然后fg 加編號就能把掛起的進程返回到前臺。當(dāng)然，配合bg和fg命令進行前后臺切換會非常方便。
3、Ctrl+D 是發(fā)送一個exit信號，沒有那么強烈，類似ctrl+C的操作，比如你從管理員root退回到你的普通用戶就可以這么用。
表的創(chuàng)建和使用
- 創(chuàng)建： create table table_name(字段 類型 約束，字段2 類型 約束....);
例 create table students(id tinyint unsigned not null primary key auto_increment, name varchar(20) default "", height decimal(5,2), ..... );
mysql中主鍵用auto_increment關(guān)鍵字避免沖突 - 查看表結(jié)構(gòu)：desc 表名；
- 顯示：show tables;
- 刪除：drop table 表名;
- 表中字段的數(shù)據(jù)類型
整型：int, ,tinyint, bit（1byte = 8 bit）;bit只有0和1
定長字符串: char；可變長度字符串: varchar
浮點數(shù)：decimal(5,2) 共五位數(shù)，2位小數(shù)
日期：date, time, datetime
大文件存儲：text 字符串類型
枚舉類型(enum)
用法：在創(chuàng)建表時，gender enum('男','女','保密','人妖') - 表中字段的常用約束條件
主鍵： primary key 作用：可以通過唯一字段確定一行記錄
非空 ：not null 作用：不予許字段為空
唯一 ：unique 作用：字段的值不允許重復(fù)
默認(rèn)： default 作用：默認(rèn)參數(shù)，用戶不指定則使用默認(rèn)值
有符號和無符號： signed unsigned
自增長：auto_increment(一般用于id自動加1)
外鍵：foreign key - 表字段的增刪改查adcm(alter table 表名 ...)
添加：alter table 表名 add 字段 類型(長度) 約束 條件；例如（alter table student add id int primary key not null;）
修改（重命名）：alter table 表名 change 舊字段 新字段 類型(長度) 約束 條件；例如（alter table student add id id1 int primary key not null;）
修改（不重命名）：alter table 表名 modify 字段 類型(長度) 約束 條件；例如（alter table student add id int primary key not null;）
刪除：alter table 表名 drop 字段 ；例如（alter table student drop id;） - 表中記錄的增刪改查（crud）
curd的解釋: 代表創(chuàng)建（Create）、更新（Update）、讀?。≧etrieve）和刪除（Delete）
查詢(全列): select * from table_name;
指定列：select 字段1，字段2 from table_name;
表(字段)重命名：select 字段1 as 字段2 from table_name as ta_na;
消除重復(fù)行：select distinct 列1,... from 表名;
修改：uptdate 表名 set 列1=值1,列2=值2,... where 條件；
增加：insert into 表名 values(),(),()...; 指定列增加 insert into 表名 (字段1, 字段2,...) values(),(),()...;
當(dāng)用子查詢的方式獲取value時，寫法更改為insert into 表名 （字段） 子查詢表達式；（不要帶上values關(guān)鍵字）
實例：insert into bookinfo values(0,'新增２','2011-1-1',20,22,1)
指定列增加時按照自己指定的字段(順序可以和表中不一樣)按照順序插入值
邏輯刪除：isDelete ；當(dāng)值為1時代表要刪除，update 表名 set isDelete=1 where 條件；
刪除：delete from 表名 where 條件；
概要 - 表中記錄的查詢命令
條件查詢：select * from 表名 where 字段判斷條件;
比較運算符: = , >, <, >=, <=, != <>不等于的兩種形式 ；
邏輯運算符：and, not, or；
模糊查詢：like %表示任意多個字符；_表示一個任意字符；
select * from students where name like '黃_';
范圍查詢：in()非連續(xù)范圍；between and 連續(xù)范圍；
空判斷：is null ; is not null
優(yōu)先級：( ) > not > 比較運算符 > 邏輯運算符 ;
排序：select * from 表名 order by 列1 asc|desc [,列2 asc|desc,...]； asc 升序 desc 降序
應(yīng)用；☆☆☆☆select gender,group_concat(name order by age) from students group by gender; 分組后各組按照年齡排序后顯示對應(yīng)的姓名
結(jié)果以行的方式顯示
select readcount,group_concat(name) from bookinfo group by readcount order by null\G;
explain select readcount,group_concat(name) from bookinfo group by readcount order by null\G; - 聚合函數(shù)：select 聚合函數(shù)(
*或者字段 ) from表名；
總數(shù)：count(*)
最大值：max( )
最小值：min( )
求和：sum( )
平均值：avg( )
select gender,avg(age),group_concat(name) from students group by gender with rollup;
round(小數(shù), 保留的位數(shù)) - 分組：select 字段 from 表名 group by 字段；將查詢結(jié)果按照1個或多個字段進行分組，字段值相同的為一組
1 輸出：group_concat(字段名)可以作為一個輸出字段來使用；
2 group by + group_concat(字段名) 表示分組之后，根據(jù)分組結(jié)果，使用group_concat()來放置每一組的某字段的值的集合；
3 例如：select gender,group_concat(name) from students group by gender;
group by + 集合函數(shù)；例如select gender,avg(age) from students group by gender;
group by + having；用法和where相同
group by + with rollup； with rollup的作用是：在最后新增一行，來記錄所顯示字段所有記錄的總和(平均值、最大值、最小值)
實例：☆☆☆☆select gender,avg(age) from students group by gender with rollup;（顯示所有人年齡的平均值） - 獲取部分行：
select * from 表名 limit start,count;start從0開始 - 分頁-顯示第n頁的m條數(shù)據(jù)；
select * from students limit (n-1)*m,m；
第一個數(shù)字可以理解為要顯示數(shù)據(jù)的id，從該id開始；
可以隨意指定，按上述規(guī)則是均分顯示；
select * from students order by age,id limit 3,3; (order by 和limit處理時有mysql的bug存在，需要在限定id字段，可以防止bug出現(xiàn)) - 連接查詢select * from 左表 join 右表; (返回的笛卡爾積，用join實現(xiàn))
內(nèi)連接：select * from 左表 inner join 右表 on 條件；（在mysql中inner join和join是相同的，在其他數(shù)據(jù)庫中join是笛卡爾積，inner join 是內(nèi)連接）
右連接：select * from 左表 right join 右表 on 條件；（在連接的基礎(chǔ)上，添加額外數(shù)據(jù)-來自右表，左表中沒有對應(yīng)的數(shù)據(jù)用Null填充）
左連接：select * from 左表 right join 右表 on 條件；（在連接的基礎(chǔ)上，添加額外數(shù)據(jù)-來自左表，右表中沒有對應(yīng)的數(shù)據(jù)用Null填充）
又稱外連接 - 自關(guān)聯(lián)：
select * from 表 join 表 on 條件 where 條件;(相同的表進行笛卡爾積計算)
全局搜索-contains語法
1. 查詢住址在北京的學(xué)生
SELECT student_id,student_name FROM students WHERE CONTAINS( address, 'beijing' )
remark: beijing是一個單詞，要用單引號括起來。
2. 查詢住址在河北省的學(xué)生
SELECT student_id,student_name FROM students WHERE CONTAINS( address, '"HEIBEI province"' )
remark: HEBEI province是一個詞組，在單引號里還要用雙引號括起來。
3. 查詢住址在河北省或北京的學(xué)生
SELECT student_id,student_name FROM students WHERE CONTAINS( address, '"HEIBEI province" OR beijing' )
remark: 可以指定邏輯操作符(包括 AND ，AND NOT，OR )。
4. 查詢有 '南京路' 字樣的地址
SELECT student_id,student_name FROM students WHERE CONTAINS( address, 'nanjing NEAR road' )
remark: 上面的查詢將返回包含'nanjing road'，'nanjing east road'，'nanjing west road'等字樣的地址。
A NEAR B，就表示條件： A 靠近 B
5. 查詢以 '湖' 開頭的地址
SELECT student_id,student_name FROM students WHERE CONTAINS( address, '"hu*"' )
remark: 上面的查詢將返回包含 'hubei'，'hunan' 等字樣的地址。
記住是 *，不是 %。
6. 類似加權(quán)的查詢
SELECT student_id,student_name FROM students WHERE CONTAINS( address, 'ISABOUT (city weight (.8), county wright (.4))' )
remark: ISABOUT 是這種查詢的關(guān)鍵字，weight 指定了一個介于 0~1之間的數(shù)，類似系數(shù)(我的理解)。表示不同條件有不同的側(cè)重。
7. 單詞的多態(tài)查詢
SELECT student_id,student_name FROM students WHERE CONTAINS( address, 'FORMSOF (INFLECTIONAL,street)' )
remark: 查詢將返回包含 'street'，'streets'等字樣的地址。
對于動詞將返回它的不同的時態(tài)，如：dry，將返回 dry，dried，drying 等等。
子主題 5 - 子查詢
標(biāo)量子查詢：返回唯一值 select * from students where age > (select avg(age) from students);
列級子查詢：select * from classes where id in (select cls_id from students); （列級子查詢返回的數(shù)據(jù)是一個集合，有自動去重的作用）
行級子查詢：select * from students where (age,height) = (select max(age), max(height) from students); 行級子查詢返回的數(shù)據(jù)是一個元組
表子查詢：
mysql語句執(zhí)行順序：完整的select 語句 - 格式 - 執(zhí)行順序（各公司可能更改執(zhí)行順序）select distinct * from 表 where 條件 group by 字段 having 條件 order by 條件 limit start,count; (執(zhí)行順序從左到右)
子主題 1
模糊查詢用法總結(jié)
1，%：表示任意0個或多個字符?？善ヅ淙我忸愋秃烷L度的字符，有些情況下若是中文，請使用兩個百分號（%%）表示。
比如SELECT * FROM [user] WHERE u_name LIKE '%三%'
將會把u_name為“張三”，“張貓三”、“三腳貓”，“唐三藏”等等有“三”的記錄全找出來。
另外，如果需要找出u_name中既有“三”又有“貓”的記錄，請使用and條件
SELECT * FROM [user] WHERE u_name LIKE '%三%' AND u_name LIKE '%貓%'
若使用SELECT * FROM [user] WHERE u_name LIKE '%三%貓%'
雖然能搜索出“三腳貓”，但不能搜索出符合條件的“張貓三”。
2，_： 表示任意單個字符。匹配單個任意字符，它常用來限制表達式的字符長度語句：
比如SELECT * FROM [user] WHERE u_name LIKE '_三_'
只找出“唐三藏”這樣u_name為三個字且中間一個字是“三”的；
再比如SELECT * FROM [user] WHERE u_name LIKE '三__'; 只找出“三腳貓”這樣name為三個字且第一個字是“三”的；
3，[ ]：表示括號內(nèi)所列字符中的一個（類似正則表達式）。指定一個字符、字符串或范圍，要求所匹配對象為它們中的任一個。
比如SELECT * FROM [user] WHERE u_name LIKE '[張李王]三'將找出“張三”、“李三”、“王三”（而不是“張李王三”）；
如 [ ] 內(nèi)有一系列字符（01234、abcde之類的）則可略寫為“0-4”、“a-e”
SELECT * FROM [user] WHERE u_name LIKE '老[1-9]'
將找出“老1”、“老2”、……、“老9”；
4，[^ ]：表示不在括號所列之內(nèi)的單個字符。其取值和 [] 相同，但它要求所匹配對象為指定字符以外的任一個字符。
比如SELECT * FROM [user] WHERE u_name LIKE '[^張李王]三'將找出不姓“張”、“李”、“王”的“趙三”、“孫三”等；
SELECT * FROM [user] WHERE u_name LIKE '老[^1-4]';將排除“老1”到“老4”，尋找“老5”、“老6”、……
5，查詢內(nèi)容包含通配符時
由于通配符的緣故，導(dǎo)致我們查詢特殊字符“%”、“_”、“[”的語句無法正常實現(xiàn)，而把特殊字符用“[ ]”括起便可正常查詢。據(jù)此我們寫出以下函數(shù)：
function sqlencode(str) str=replace(str,"';","';';")
str=replace(str,"[","[[]") ';此句一定要在最先 str=replace(str,"","[]")str=replace(str,"%","[%]") sqlencode=str end function
python中操作mysql的步驟
開始：import pymysql
連接：con = mysql.connect(參數(shù)列表)
參數(shù)host：連接的mysql主機，如果本機是'localhost'
port：連接的mysql主機的端口，默認(rèn)是3306
參數(shù)database：數(shù)據(jù)庫的名稱
參數(shù)user：連接的用戶名
參數(shù)password：連接的密碼
參數(shù)charset：通信采用的編碼方式，推薦使用utf8(必須指定，否則拉丁編碼格式，容易出錯)創(chuàng)建Cursor對象：
cur = con.cursor()sql語句
例子：sql = 'insert into focus (info_id) select id from info where code =%s'執(zhí)行：cur.execute(sql,[參數(shù)列表])，除了執(zhí)行，還會返回查到符合條件的記錄的條數(shù)，沒有查詢到記錄則返回0
例子：cur.execute(sql,[code])提交：
con.commit()
在沒有提交前可以用con.rollback()進行回滾操作關(guān)閉：cur.close()
關(guān)閉：con.close()
☆☆防止參數(shù)化注入
1、問題引入# SQL注入問題 －> 后臺直接根據(jù)用戶給定的數(shù)據(jù) 直接用字符串方式進行拼接select * from hero where name='妲己' #' and id = 123456789;（這樣就把and id = 123456789注釋掉了，默認(rèn)不執(zhí)行）2、實際實現(xiàn)形式# 實際是內(nèi)置了一個函數(shù)mogrify， 對特殊字符進行轉(zhuǎn)義，打印出來結(jié)果
print(cur.mogrify(sql, [name, id]))：select * from hero where name='妲己\' #' and id = '1234567';3、解決方法# 參數(shù)化解決問題 - 防止SQL 對特殊字符進行\(zhòng)字符進行轉(zhuǎn)義
sql = "select * from hero where name=%s and id = %s;"
row_count = cur.execute(sql, [name, id])
msql高級
視圖
- 定義視圖：create view 試圖名稱 as select語句
- 查看視圖：show tables;
- 使用視圖：select * from 視圖名稱（一般定義以v_開頭）
- 刪除視圖：drop view 視圖名稱
- 視圖作用：1.提高重用性；2.不影響程序的基礎(chǔ)上對數(shù)據(jù)庫重構(gòu)；3.提高了安全性；4.讓數(shù)據(jù)更清晰。
事務(wù) - 原子性：事務(wù)不可分割，要么都成功，要么都失敗
- 一致性：總是從一個一致性狀態(tài)到另一個一致性狀態(tài)
- 隔離性：事務(wù)（在提交之前）對外不可見
- 持久性：一旦提交，永久保存
- 事務(wù)命令
開啟事務(wù)：begin;或者start transaction;
提交事務(wù)：commit
回滾事務(wù)：rollback;（在提交之前有效） - 注意點：
1、使用事務(wù)命令前提是表的引擎是innodb；
2、修改數(shù)據(jù)的命令會自動觸發(fā)事務(wù)；
3、在SQL語句中有手動開啟事務(wù)的原因是：可以進行多次數(shù)據(jù)的修改，如果成功一起成功，否則一起會滾到之前的數(shù)據(jù)。
索引 - 創(chuàng)建索引：
create index 索引名稱 on 表名(字段長度()); - 查看索引：
show index from 表名； - 查看語句執(zhí)行時間：set profiling=1; (操作后) show profiles;
- 刪除索引：drop index 索引名稱 on 表名; （索引名稱命名i_開頭）
- 作用：索引是一種特殊的文件，包含對數(shù)據(jù)表里所有記錄的引用指針。
索引能加快數(shù)據(jù)庫的查詢速度
賬戶管理 - 查看所有用戶：desc user; select host, user, authentication_string from user;
- 創(chuàng)建賬戶、授權(quán)：grant 權(quán)限名稱 on 數(shù)據(jù)庫名 to '用戶名'@'訪問主機' identified by '密碼';
all privileges： 所有權(quán)限
%：所有主機 - 修改權(quán)限： grant 權(quán)限名稱 on 數(shù)據(jù)庫名 to '用戶名'@'訪問主機' with grant option;
- 修改密碼：update user set authentication_string=password('新密碼') where user='用戶名';
- 刷新權(quán)限：flush privileges；
- 刪除用戶：drop user '用戶名'@'訪問主機';
- 為項目創(chuàng)建數(shù)據(jù)庫用戶
create user meiduo identified by 'meiduo';
grant all on meiduomail.* to 'meiduo'@'%';
flush privileges;
設(shè)置主從服務(wù)配置
常見問題
show full processlist ；
顯示的數(shù)據(jù)里有個id字段，就是sessionid，執(zhí)行 kill id就可，關(guān)閉session
?。ㄗⅲ簯?yīng)用程序一般和mysql都是做短連接的，執(zhí)行完sql后都會關(guān)閉session，除非是卡在那，或者執(zhí)行時間太長，才有機會在show processlist中看到）
mysql優(yōu)化原理
mysql查詢過程圖解
mysql查詢過程.png
優(yōu)化原理
我們總是希望MySQL能夠獲得更高的查詢性能，最好的辦法是弄清楚MySQL是如何優(yōu)化和執(zhí)行查詢的。
很多的查詢優(yōu)化工作實際上就是遵循一些原則讓MySQL的優(yōu)化器能夠按照預(yù)想的合理方式運行而已。
MySQL邏輯架構(gòu)
- 客戶端層：并非MySQL所獨有，諸如：連接處理、授權(quán)認(rèn)證、安全等功能均在這一層處理。
- 核心服務(wù)層：包括查詢解析、分析、優(yōu)化、緩存、內(nèi)置函數(shù)(比如：時間、數(shù)學(xué)、加密等函數(shù))。所有的跨存儲引擎的功能也在這一層實現(xiàn)：存儲過程、觸發(fā)器、視圖等。
- 存儲引擎：其負(fù)責(zé)MySQL中的數(shù)據(jù)存儲和提取。和Linux下的文件系統(tǒng)類似，每種存儲引擎都有其優(yōu)勢和劣勢。中間的服務(wù)層通過API與存儲引擎通信，這些API接口屏蔽了不同存儲引擎間的差異。
具體查詢過程
- 客戶端向MySQL服務(wù)器發(fā)送一條查詢請求
在任一時刻，要么是服務(wù)器向客戶端發(fā)送數(shù)據(jù)，要么是客戶端向服務(wù)器發(fā)送數(shù)據(jù)，這兩個動作不能同時發(fā)生。
當(dāng)服務(wù)器響應(yīng)客戶端請求時，客戶端必須完整的接收整個返回結(jié)果，而不能簡單的只取前面幾條結(jié)果，然后讓服務(wù)器停止發(fā)送。因而在實際開發(fā)中，盡量保持查詢簡單且只返回必需的數(shù)據(jù)，減小通信間數(shù)據(jù)包的大小和數(shù)量是一個非常好的習(xí)慣，這也是查詢中盡量避免使用SELECT *以及加上LIMIT限制的原因之一 - 服務(wù)器首先檢查查詢緩存，如果命中緩存，則立刻返回存儲在緩存中的結(jié)果。否則進入下一階段
- MySQL的查詢緩存系統(tǒng)會跟蹤查詢中涉及的每個表，如果這些表（數(shù)據(jù)或結(jié)構(gòu)）發(fā)生變化，那么和這張表相關(guān)的所有緩存數(shù)據(jù)都將失效。
任何的查詢語句在開始之前都必須經(jīng)過檢查，即使這條SQL語句永遠(yuǎn)不會命中緩存 - 如果查詢結(jié)果可以被緩存，那么執(zhí)行完成后，會將結(jié)果存入緩存，也會帶來額外的系統(tǒng)消耗
基于此，我們要知道并不是什么情況下查詢緩存都會提高系統(tǒng)性能，緩存和失效都會帶來額外消耗，只有當(dāng)緩存帶來的資源節(jié)約大于其本身消耗的資源時，才會給系統(tǒng)帶來性能提升
數(shù)據(jù)庫設(shè)計上針對緩問題的優(yōu)化
- 用多個小表代替一個大表，注意不要過度設(shè)計
- 批量插入代替循環(huán)單條插入
- 合理控制緩存空間大小，一般來說其大小設(shè)置為幾十兆比較合適
- 可以通過SQL_CACHE和SQL_NO_CACHE來控制某個查詢語句是否需要進行緩存
- 不要輕易打開查詢緩存，特別是寫密集型應(yīng)用。如果你實在是忍不住，可以將query_cache_type設(shè)置為DEMAND，這時只有加入SQL_CACHE的查詢才會走緩存，其他查詢則不會，這樣可以非常自由地控制哪些查詢需要被緩存。
- 服務(wù)器進行SQL解析、預(yù)處理
MySQL通過關(guān)鍵字將SQL語句進行解析，并生成一顆對應(yīng)的解析樹。
這個過程解析器主要通過語法規(guī)則來驗證和解析。
比如SQL中是否使用了錯誤的關(guān)鍵字或者關(guān)鍵字的順序是否正確等等。
預(yù)處理則會根據(jù)MySQL規(guī)則進一步檢查解析樹是否合法。比如檢查要查詢的數(shù)據(jù)表和數(shù)據(jù)列是否存在等等。
再由優(yōu)化器生成對應(yīng)的查詢計劃
MySQL使用基于成本的優(yōu)化器，它嘗試預(yù)測一個查詢使用某種執(zhí)行計劃時的成本，并選擇其中成本最小的一個。
在MySQL可以通過查詢當(dāng)前會話的last_query_cost的值來得到其計算當(dāng)前查詢的成本。
MySQL認(rèn)為的最優(yōu)跟我們想的不一樣（我們希望執(zhí)行時間盡可能短，但MySQL值選擇它認(rèn)為成本小的，但成本小并不意味著執(zhí)行時間短）等等。 - MySQL的查詢優(yōu)化器的優(yōu)化策略
重新定義表的關(guān)聯(lián)順序（多張表關(guān)聯(lián)查詢時，并不一定按照SQL中指定的順序進行，但有一些技巧可以指定關(guān)聯(lián)順序）
優(yōu)化MIN()和MAX()函數(shù)（找某列的最小值，如果該列有索引，只需要查找B+Tree索引最左端，反之則可以找到最大值
提前終止查詢（比如：使用Limit時，查找到滿足數(shù)量的結(jié)果集后會立即終止查詢）
優(yōu)化排序（在老版本MySQL會使用兩次傳輸排序，即先讀取行指針和需要排序的字段在內(nèi)存中對其排序，然后再根據(jù)排序結(jié)果去讀取數(shù)據(jù)行，而新版本采用的是單次傳輸排序，也就是一次讀取所有的數(shù)據(jù)行，然后根據(jù)給定的列排序。對于I/O密集型應(yīng)用，效率會高很多）
等等
MySQL根據(jù)執(zhí)行計劃，調(diào)用存儲引擎的API來執(zhí)行查詢
查詢執(zhí)行引擎根據(jù)執(zhí)行計劃給出的指令逐步執(zhí)行得出結(jié)果。整個執(zhí)行過程的大部分操作均是通過調(diào)用存儲引擎實現(xiàn)的接口來完成，這些接口被稱為handler API。
將結(jié)果返回給客戶端，同時緩存查詢結(jié)果
性能優(yōu)化建議
- Scheme設(shè)計與數(shù)據(jù)類型優(yōu)化
選擇數(shù)據(jù)類型只要遵循小而簡單的原則就好，越小的數(shù)據(jù)類型通常會更快，占用更少的磁盤、內(nèi)存，處理時需要的CPU周期也更少。 - 創(chuàng)建高性能索引
索引相關(guān)的數(shù)據(jù)結(jié)構(gòu)和算法
通常我們所說的索引是指B-Tree索引，它是目前關(guān)系型數(shù)據(jù)庫中查找數(shù)據(jù)最為常用和有效的索引，大多數(shù)存儲引擎都支持這種索引。
InnoDB就是使用的B+Tree。
B+Tree中的B是指balance，意為平衡。
B+Tree就是一種多路搜索樹。
理解B+Tree時，只需要理解其最重要的兩個特征即可
第一，所有的關(guān)鍵字（可以理解為數(shù)據(jù)）都存儲在葉子節(jié)點（Leaf Page），非葉子節(jié)點（Index Page）并不存儲真正的數(shù)據(jù)，所有記錄節(jié)點都是按鍵值大小順序存放在同一層葉子節(jié)點上。
其次，所有的葉子節(jié)點由指針連接。如下圖為高度為2的簡化了的B+Tree。
高性能策略
MySQL不會使用索引的情況：非獨立的列
“獨立的列”是指索引列不能是表達式的一部分，也不能是函數(shù)的參數(shù)。
前綴索引
如果列很長，通常可以索引開始的部分字符，這樣可以有效節(jié)約索引空間，從而提高索引效率。
多列索引和索引順序
當(dāng)出現(xiàn)多個索引做相交操作時（多個AND條件），通常來說一個包含所有相關(guān)列的索引要優(yōu)于多個獨立索引。
當(dāng)出現(xiàn)多個索引做聯(lián)合操作時（多個OR條件），對結(jié)果集的合并、排序等操作需要耗費大量的CPU和內(nèi)存資源，特別是當(dāng)其中的某些索引的選擇性不高，需要返回合并大量數(shù)據(jù)時，查詢成本更高。所以這種情況下還不如走全表掃描。
避免多個范圍條件
覆蓋索引
索引條目遠(yuǎn)小于數(shù)據(jù)行大小，如果只讀取索引，極大減少數(shù)據(jù)訪問量
索引是有按照列值順序存儲的，對于I/O密集型的范圍查詢要比隨機從磁盤讀取每一行數(shù)據(jù)的IO要少的多
使用索引掃描來排序
避免冗余和重復(fù)索引
刪除長期未使用的索引
特定類型查詢優(yōu)化
優(yōu)化COUNT()查詢
如果要統(tǒng)計行數(shù)，直接使用COUNT(*)，意義清晰，且性能更好。
優(yōu)化關(guān)聯(lián)查詢
確保ON和USING字句中的列上有索引。
確保任何的GROUP BY和ORDER BY中的表達式只涉及到一個表中的列，這樣MySQL才有可能使用索引來優(yōu)化。
優(yōu)化LIMIT分頁
優(yōu)化UNION
常見錯誤理解與技巧
通常來說把可為NULL的列改為NOT NULL不會對性能提升有多少幫助，只是如果計劃在列上創(chuàng)建索引，就應(yīng)該將該列設(shè)置為NOT NULL。
對整數(shù)類型指定寬度，比如INT(11)，沒有任何卵用。INT使用32位（4個字節(jié)）存儲空間，那么它的表示范圍已經(jīng)確定，所以INT(1)和INT(20)對于存儲和計算是相同的。
UNSIGNED表示不允許負(fù)值，大致可以使正數(shù)的上限提高一倍。比如TINYINT存儲范圍是-128 ~ 127，而UNSIGNED TINYINT存儲的范圍卻是0 - 255。
通常來講，沒有太大的必要使用DECIMAL數(shù)據(jù)類型。即使是在需要存儲財務(wù)數(shù)據(jù)時，仍然可以使用BIGINT。比如需要精確到萬分之一，那么可以將數(shù)據(jù)乘以一百萬然后使用BIGINT存儲。這樣可以避免浮點數(shù)計算不準(zhǔn)確和DECIMAL精確計算代價高的問題。
TIMESTAMP使用4個字節(jié)存儲空間，DATETIME使用8個字節(jié)存儲空間。因而，TIMESTAMP只能表示1970 - 2038年，比DATETIME表示的范圍小得多，而且TIMESTAMP的值因時區(qū)不同而不同。
大多數(shù)情況下沒有使用枚舉類型的必要，其中一個缺點是枚舉的字符串列表是固定的，添加和刪除字符串（枚舉選項）必須使用ALTER TABLE（如果只只是在列表末尾追加元素，不需要重建表）。
schema的列不要太多。原因是存儲引擎的API工作時需要在服務(wù)器層和存儲引擎層之間通過行緩沖格式拷貝數(shù)據(jù)，然后在服務(wù)器層將緩沖內(nèi)容解碼成各個列，這個轉(zhuǎn)換過程的代價是非常高的。如果列太多而實際使用的列又很少的話，有可能會導(dǎo)致CPU占用過高。
大表ALTER TABLE非常耗時，MySQL執(zhí)行大部分修改表結(jié)果操作的方法是用新的結(jié)構(gòu)創(chuàng)建一個張空表，從舊表中查出所有的數(shù)據(jù)插入新表，然后再刪除舊表。尤其當(dāng)內(nèi)存不足而表又很大，而且還有很大索引的情況下，耗時更久。當(dāng)然有一些奇技技巧可以解決這個問題，有興趣可自行查閱。
