MySQL-視圖-觸發器-事務-存儲過程-函數-流程式控制制-索引與慢查詢優化-06
- 2019 年 10 月 7 日
- 筆記
目錄
小科普
核心業務邏輯程式碼一般都是放在服務端的 客戶端容易被懂行點的人修改源碼，造成損失
視圖***
什麼是視圖
一個查詢語句的結果是虛擬表，將（查詢出）這張虛擬表（的sql語句）保存下來，他就變成了一個視圖（mysql中還是以表的形式存在的）
為什麼要用視圖
當頻繁需要用到多張表的聯表結果，你就可以事先生成好視圖，之後直接調用即可，避免了反覆寫聯表操作的 sql 語句（實際效果相當於再次執行語句）
如何生成視圖
# 語法 create view 視圖名 as 生成虛擬表的查詢語句 create view teacher_course as select * from teacher inner join course on teacher.tid = course.teacher_id; show tables # 即可看到該視圖（當做表了） select * from teacher_course; # 看到的就是
修改視圖 –> 最好（千萬）不要
關聯表數據改動前
關聯表數據改動之後
視圖中的數據自動更新了–> 執行查詢視圖記錄語句 = 重新執行了創建視圖的那個sql 語句
注意： 1.視圖只有表結構，視圖中的數據還是來源於原來的表 2.不要改動視圖表中的數據（可能會報錯，也可能會改其他表的數據） —> 我在cmd終端改動影響到了其他表 3.不要太依賴視圖，盡量少用視圖來寫業務邏輯
- 視圖會影響性能，佔用硬碟資源、資料庫資源
- 工作中可能是別的部門的人管理資料庫，跨部門交流比較麻煩，而且如果別人改動了視圖關聯表，或刪了視圖那涉及到的業務邏輯就很危險了。
觸發器
什麼是觸發器
到達某個條件自動觸發
觸發條件
當你在對數據進行增刪改的情況下會自動觸發觸發器的運行
觸發器語法結構
修改mysql的默認結束符（;）
delimiter $$ # --> 默認 ; 改成了 $$，只對當前窗口有效，重新登錄也會無效
delimiter $$ # 改變當前窗口sql 語句的結束符 create trigger 觸發器的名字 after/before insert/update/delete on 表名 for each row begin # 操作其他表的sql 語句 end $$ delimiter ; # 把sql結束符改回 ; # 觸發器常見命名格式：tri_before/after_insert/update/delete_表名
觸發器死循環
觸發器裡面的程式碼會觸發觸發器本身的執行，造成了死循環
在觸發器裡面千萬不要寫操作本表的語句，增刪改查都不要，會報錯
比如：user表的 新增前觸發器 程式碼塊內寫的是 user表的新增插值操作，就會造成觸發器死循環（直接報錯）
六個觸發器的執行時機
新增前、新增後、刪除前、刪除後、更新前、更新後
小案例（新增後）
create table user( id int primary key auto_increment, name varchar(32) not null, password varchar(255) not null ) create table log( id int primary key auto_increment, message varchar(255) ); drop trigger tri_after_insert_user; # 刪除已存在的觸發器 tri_after_insert_user delimiter $$ create trigger tri_after_insert_user after insert on user for each row begin # insert into user(name, password) values('老子翻車了', '123'); # 死循環了，像遞歸，不斷觸發這個觸發器 # select * from user; # 也會報錯，觸發器里不能返回值 Not allowed to return a result set from a trigger insert into log(message) values ('看到我就說明你沒翻車'); end $$ delimiter ; insert into user(name, password) values('會翻車嗎', '可能吧'); select * from user; select * from log; # 刪除觸發器 drop trigger tri_after_insert_user;
擴展： 視圖、存儲過程的查看
摘抄自：mysql查看存儲過程函數
# 查詢資料庫中的存儲過程和函數 select `name` from mysql.proc where db = 'xx' and `type` = 'PROCEDURE' # 存儲過程 select `name` from mysql.proc where db = 'xx' and `type` = 'FUNCTION' # 函數 show procedure statusG; # 存儲過程(G 豎式排版查看，橫的太長了 看不清) show function statusG; # 函數 # 查看存儲過程或函數的創建程式碼 show create procedure proc_nameG; show create function func_nameG; # 查看視圖 SELECT * from information_schema.VIEWS; # 視圖 SELECT * from information_schema.TABLES; # 表 # 查看觸發器 SHOW TRIGGERS [FROM db_name] [LIKE expr] SELECT * FROM triggers T WHERE trigger_name="mytrigger"G;
NEW對象指代的就是當前記錄（對象）
NEW 對象可以取到觸發這個觸發器的sql語句的記錄對象，通過 . 欄位名 的方式來獲取到欄位值
# 案例 (mysql大小寫不敏感) CREATE TABLE cmd ( id INT PRIMARY KEY auto_increment, USER CHAR (32), priv CHAR (10), cmd CHAR (64), sub_time datetime, #提交時間 success enum ('yes', 'no') #0代表執行失敗 ); CREATE TABLE errlog ( id INT PRIMARY KEY auto_increment, err_cmd CHAR (64), err_time datetime ); delimiter $$ # 將mysql默認的結束符由;換成$$ create trigger tri_after_insert_cmd after insert on cmd for each row begin if NEW.success = 'no' then # 新記錄都會被MySQL封裝成NEW對象 insert into errlog(err_cmd,err_time) values(NEW.cmd,NEW.sub_time); end if; end $$ delimiter ; # 結束之後記得再改回來，不然後面結束符就都是$$了 #往表cmd中插入記錄，觸發觸發器，根據IF的條件決定是否插入錯誤日誌 INSERT INTO cmd ( USER, priv, cmd, sub_time, success ) VALUES ('egon','0755','ls -l /etc',NOW(),'yes'), ('egon','0755','cat /etc/passwd',NOW(),'no'), ('egon','0755','useradd xxx',NOW(),'no'), ('egon','0755','ps aux',NOW(),'yes'); # 查詢errlog表記錄 select * from errlog; # 刪除觸發器 drop trigger tri_after_insert_cmd;
事務 *****
什麼是事務
事務包含一堆sql語句，要麼全部成功，要麼都不成功
事務的四大特性 ACID
用自己的話背下來
A：原子性 atomicity
一個事務是一個不可分割的工作單位，事務中包括的諸操作要麼都做，要麼都不做。
C：一致性 consistency
事務必須是使資料庫從一個一致性狀態變到另一個一致性狀態。 一致性與原子性是密切相關的。
I：隔離性 isolation
一個事務的執行不能被其他事務干擾。 即一個事務內部的操作及使用的數據對並發的其他事務是隔離的，並發執行的各個事務之間不能互相干擾。
D：持久性 durability
持久性也稱永久性（permanence），指一個事務一旦提交，它對資料庫中數據的改變就應該是永久性的。 接下來的其他操作或故障不應該對其有任何影響。
如何開啟事務
start transaction 標誌下面的語句都是 事務
事務開始後，只會在記憶體中修改
只有commit 之後才會寫到硬碟上
事務回滾
rollback 事務開始的地方到這裡的語句都會回滾（失效）
永久性更改
commit 把數據刷在硬碟上，後面再 rollback 就回滾不回去了
start transaction、rollback、commit 有點像python異常捕獲的 try … except … else …
小案例
create table user( id int primary key auto_increment, name char(32), balance int ); insert into user(name,balance) values ('wsb',1000), ('egon',1000), ('ysb',1000); # 修改數據之前先開啟事務操作 start transaction; # 修改操作 update user set balance=900 where name='wsb'; #買支付100元 update user set balance=1010 where name='egon'; #中介拿走10元 update user set balance=1090 where name='ysb'; #賣家拿到90元 # 回滾到上一個狀態 rollback; # 開啟事務之後，只要沒有執行commit操作，數據其實都沒有真正刷新到硬碟 commit; """開啟事務檢測操作是否完整，不完整主動回滾到上一個狀態，如果完整就應該執行commit操作""" # 站在python程式碼的角度，應該實現的偽程式碼邏輯， try: update user set balance=900 where name='wsb'; #買支付100元 update user set balance=1010 where name='egon'; #中介拿走10元 update user set balance=1090 where name='ysb'; #賣家拿到90元 except 異常: rollback; else: commit; # 那如何檢測異常？
存儲過程
什麼是存儲過程
就類似於python中的自定義函數
內部封裝了 sql 語句，後續想要實現相應的操作，只需要調用存儲過程即可
如何創建存儲過程
語法結構
# 無參數版 delimiter $$ # 改mysql的結束符 create procedure 存儲結構名字() begin sql 語句; end delimiter ; # 改回來 call 存儲結構名字() # 調用寫好的存儲過程 # 有參數版 delimiter $$ # 改mysql的結束符 create procedure 存儲結構名字( in m int, # in 只能傳進來，不能返回 in n int, # 參數對應的意思---> 進還是出， 變數名， 數據類型 out res int, # out 只能返回，不能傳 inout xxx int, # inout 可以傳進來，也可以被返回 ) begin sql 語句; end delimiter ; # 改回來 call 存儲結構名字(m, n) # 參數怎麼傳不知道。。
案例
存儲過程在哪個庫里定義就只能在哪個庫裡面使用
定義存儲過程
delimiter $$ create procedure p1( in m int, # in 只能傳進來，不能被返回 in n int, # 參數對應的意思---> 進還是出， 變數名， 數據類型 out res int # out 只能被返回，不能傳入 ) begin select tname from teacher where tid > m and tid < n; set res=0; # 就類似於一個標誌位，用來標識存儲器是否執行成功 end $$ delimiter ; show procedure statusG; # 查看存儲過程（豎式展示排版）
在mysql中調用存儲過程
存變數（設置初始值）
set @res=10; # 設置全局變數 @res 等於10，select @res; # 查看全局變數 @res 的值
調用
call p1(1,5,@res)；
將變數 @res 傳入，之後可以通過 select @res 來查看存儲過程執行完成後的返回結果
在pymysql中調用存儲過程
# 使用的是上一步創建的存儲過程 import pymysql conn = pymysql.connect( host='127.0.0.1', port=3306, user='root', password='000000', database='day38', charset='utf8', autocommit=True, ) cursor = conn.cursor(pymysql.cursors.DictCursor) # -------------------------------------------- # cursor.callproc() 調用存儲過程 # 內部自動用變數名存儲對應值（看下面案例注釋） # -------------------------------------------- cursor.callproc('p1', (1, 5, 10)) # 這裡就不需要設置那個全局變數了（@res=10）,內部自動用變數名存儲了對應的值 print(cursor.fetchall()) # [{'tname': '李平老師'}, {'tname': '劉海燕老師'}, {'tname': '朱雲海老師'}] ''' callproc 內部自動用變數名存儲了對應的值 @_p1_0=1 @_p1_1=5 @_p1_2=10 # 自動取名規律： @_存儲過程名_標號 ''' cursor.execute('select @_p1_0=1') print(cursor.fetchall()) # [{'@_p1_0=1': 1}] cursor.execute('select @_p1_1=5') print(cursor.fetchall()) # [{'@_p1_1=5': 1}] cursor.execute('select @_p1_2=10') print(cursor.fetchall()) # [{'@_p1_2=10': 0}]
案例– 使用存儲過程監測事務
監測一個事務是否成功，通常使用存儲過程包起來
# 大前提:存儲過程在哪個庫下面創建的只能在對應的庫下面才能使用！！！ delimiter // create PROCEDURE p5( OUT p_return_code tinyint ) BEGIN DECLARE exit handler for sqlexception BEGIN -- ERROR set p_return_code = 1; rollback; END; DECLARE exit handler for sqlwarning BEGIN -- WARNING set p_return_code = 2; rollback; END; START TRANSACTION; update user set balance=900 where id =1; update user123 set balance=1010 where id = 2; update user set balance=1090 where id =3; COMMIT; -- SUCCESS set p_return_code = 0; #0代表執行成功 END // delimiter ;
函數
注意與存儲過程的區別，mysql內置的函數只能在sql語句中使用！
參考部落格：函數
MySQL內置函數
常見函數及練習
一、數學函數 ROUND(x,y) 返回參數x的四捨五入的有y位小數的值 RAND() 返回０到１內的隨機值,可以通過提供一個參數(種子)使RAND()隨機數生成器生成一個指定的值。 二、聚合函數(常用於GROUP BY從句的SELECT查詢中) AVG(col)返回指定列的平均值 COUNT(col)返回指定列中非NULL值的個數 MIN(col)返回指定列的最小值 MAX(col)返回指定列的最大值 SUM(col)返回指定列的所有值之和 GROUP_CONCAT(col) 返回由屬於一組的列值連接組合而成的結果 三、字元串函數 CHAR_LENGTH(str) 返回值為字元串str 的長度，長度的單位為字元。一個多位元組字元算作一個單字元。 CONCAT(str1,str2,...) 字元串拼接 如有任何一個參數為NULL ，則返回值為 NULL。 CONCAT_WS(separator,str1,str2,...) 字元串拼接（自定義連接符） CONCAT_WS()不會忽略任何空字元串。 (然而會忽略所有的 NULL）。 CONV(N,from_base,to_base) 進位轉換 例如： SELECT CONV('a',16,2); 表示將 a 由16進位轉換為2進位字元串表示 FORMAT(X,D) 將數字X 的格式寫為'#,###,###.##',以四捨五入的方式保留小數點後 D 位， 並將結果以字元串的形式返回。若 D 為 0, 則返回結果不帶有小數點，或不含小數部分。 例如： SELECT FORMAT(12332.1,4); 結果為： '12,332.1000' INSERT(str,pos,len,newstr) 在str的指定位置插入字元串 pos：要替換位置其實位置 len：替換的長度 newstr：新字元串 特別的： 如果pos超過原字元串長度，則返回原字元串 如果len超過原字元串長度，則由新字元串完全替換 INSTR(str,substr) 返回字元串 str 中子字元串的第一個出現位置。 LEFT(str,len) 返回字元串str 從開始的len位置的子序列字元。 LOWER(str) 變小寫 UPPER(str) 變大寫 REVERSE(str) 返回字元串 str ，順序和字元順序相反。 SUBSTRING(str,pos) , SUBSTRING(str FROM pos) SUBSTRING(str,pos,len) , SUBSTRING(str FROM pos FOR len) 不帶有len 參數的格式從字元串str返回一個子字元串，起始於位置 pos。帶有len參數的格式從字元串str返回一個長度同len字元相同的子字元串，起始於位置 pos。 使用 FROM的格式為標準 SQL 語法。也可能對pos使用一個負值。假若這樣，則子字元串的位置起始於字元串結尾的pos 字元，而不是字元串的開頭位置。在以下格式的函數中可以對pos 使用一個負值。 mysql> SELECT SUBSTRING('Quadratically',5); -> 'ratically' mysql> SELECT SUBSTRING('foobarbar' FROM 4); -> 'barbar' mysql> SELECT SUBSTRING('Quadratically',5,6); -> 'ratica' mysql> SELECT SUBSTRING('Sakila', -3); -> 'ila' mysql> SELECT SUBSTRING('Sakila', -5, 3); -> 'aki' mysql> SELECT SUBSTRING('Sakila' FROM -4 FOR 2); -> 'ki' 四、日期和時間函數 CURDATE()或CURRENT_DATE() 返回當前的日期 CURTIME()或CURRENT_TIME() 返回當前的時間 DAYOFWEEK(date) 返回date所代表的一星期中的第幾天(1~7) DAYOFMONTH(date) 返回date是一個月的第幾天(1~31) DAYOFYEAR(date) 返回date是一年的第幾天(1~366) DAYNAME(date) 返回date的星期名，如：SELECT DAYNAME(CURRENT_DATE); FROM_UNIXTIME(ts,fmt) 根據指定的fmt格式，格式化UNIX時間戳ts HOUR(time) 返回time的小時值(0~23) MINUTE(time) 返回time的分鐘值(0~59) MONTH(date) 返回date的月份值(1~12) MONTHNAME(date) 返回date的月份名，如：SELECT MONTHNAME(CURRENT_DATE); NOW() 返回當前的日期和時間 QUARTER(date) 返回date在一年中的季度(1~4)，如SELECT QUARTER(CURRENT_DATE); WEEK(date) 返回日期date為一年中第幾周(0~53) YEAR(date) 返回日期date的年份(1000~9999) 重點: DATE_FORMAT(date,format) 根據format字元串格式化date值 mysql> SELECT DATE_FORMAT('2009-10-04 22:23:00', '%W %M %Y'); -> 'Sunday October 2009' mysql> SELECT DATE_FORMAT('2007-10-04 22:23:00', '%H:%i:%s'); -> '22:23:00' mysql> SELECT DATE_FORMAT('1900-10-04 22:23:00', -> '%D %y %a %d %m %b %j'); -> '4th 00 Thu 04 10 Oct 277' mysql> SELECT DATE_FORMAT('1997-10-04 22:23:00', -> '%H %k %I %r %T %S %w'); -> '22 22 10 10:23:00 PM 22:23:00 00 6' mysql> SELECT DATE_FORMAT('1999-01-01', '%X %V'); -> '1998 52' mysql> SELECT DATE_FORMAT('2006-06-00', '%d'); -> '00' 五、加密函數 MD5() 計算字元串str的MD5校驗和 PASSWORD(str) 返回字元串str的加密版本，這個加密過程是不可逆轉的，和UNIX密碼加密過程使用不同的演算法。 六、控制流函數 CASE WHEN[test1] THEN [result1]...ELSE [default] END 如果testN是真，則返回resultN，否則返回default CASE [test] WHEN[val1] THEN [result]...ELSE [default]END 如果test和valN相等，則返回resultN，否則返回default IF(test,t,f) 如果test是真，返回t；否則返回f IFNULL(arg1,arg2) 如果arg1不是空，返回arg1，否則返回arg2 NULLIF(arg1,arg2) 如果arg1=arg2返回NULL；否則返回arg1 七、控制流函數小練習 #7.1、準備表 /* Navicat MySQL Data Transfer Source Server : localhost_3306 Source Server Version : 50720 Source Host : localhost:3306 Source Database : student Target Server Type : MYSQL Target Server Version : 50720 File Encoding : 65001 Date: 2018-01-02 12:05:30 */ SET FOREIGN_KEY_CHECKS=0; -- ---------------------------- -- Table structure for course -- ---------------------------- DROP TABLE IF EXISTS `course`; CREATE TABLE `course` ( `c_id` int(11) NOT NULL, `c_name` varchar(255) DEFAULT NULL, `t_id` int(11) DEFAULT NULL, PRIMARY KEY (`c_id`), KEY `t_id` (`t_id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; -- ---------------------------- -- Records of course -- ---------------------------- INSERT INTO `course` VALUES ('1', 'python', '1'); INSERT INTO `course` VALUES ('2', 'java', '2'); INSERT INTO `course` VALUES ('3', 'linux', '3'); INSERT INTO `course` VALUES ('4', 'web', '2'); -- ---------------------------- -- Table structure for score -- ---------------------------- DROP TABLE IF EXISTS `score`; CREATE TABLE `score` ( `id` int(11) NOT NULL AUTO_INCREMENT, `s_id` int(10) DEFAULT NULL, `c_id` int(11) DEFAULT NULL, `num` double DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=12 DEFAULT CHARSET=utf8; -- ---------------------------- -- Records of score -- ---------------------------- INSERT INTO `score` VALUES ('1', '1', '1', '79'); INSERT INTO `score` VALUES ('2', '1', '2', '78'); INSERT INTO `score` VALUES ('3', '1', '3', '35'); INSERT INTO `score` VALUES ('4', '2', '2', '32'); INSERT INTO `score` VALUES ('5', '3', '1', '66'); INSERT INTO `score` VALUES ('6', '4', '2', '77'); INSERT INTO `score` VALUES ('7', '4', '1', '68'); INSERT INTO `score` VALUES ('8', '5', '1', '66'); INSERT INTO `score` VALUES ('9', '2', '1', '69'); INSERT INTO `score` VALUES ('10', '4', '4', '75'); INSERT INTO `score` VALUES ('11', '5', '4', '66.7'); -- ---------------------------- -- Table structure for student -- ---------------------------- DROP TABLE IF EXISTS `student`; CREATE TABLE `student` ( `s_id` varchar(20) NOT NULL, `s_name` varchar(255) DEFAULT NULL, `s_age` int(10) DEFAULT NULL, `s_sex` char(1) DEFAULT NULL, PRIMARY KEY (`s_id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; -- ---------------------------- -- Records of student -- ---------------------------- INSERT INTO `student` VALUES ('1', '魯班', '12', '男'); INSERT INTO `student` VALUES ('2', '貂蟬', '20', '女'); INSERT INTO `student` VALUES ('3', '劉備', '35', '男'); INSERT INTO `student` VALUES ('4', '關羽', '34', '男'); INSERT INTO `student` VALUES ('5', '張飛', '33', '女'); -- ---------------------------- -- Table structure for teacher -- ---------------------------- DROP TABLE IF EXISTS `teacher`; CREATE TABLE `teacher` ( `t_id` int(10) NOT NULL, `t_name` varchar(50) DEFAULT NULL, PRIMARY KEY (`t_id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; -- ---------------------------- -- Records of teacher -- ---------------------------- INSERT INTO `teacher` VALUES ('1', '大王'); INSERT INTO `teacher` VALUES ('2', 'alex'); INSERT INTO `teacher` VALUES ('3', 'egon'); INSERT INTO `teacher` VALUES ('4', 'peiqi'); #7.2、統計各科各分數段人數.顯示格式:課程ID,課程名稱,[100-85],[85-70],[70-60],[ <60] select score.c_id, course.c_name, sum(CASE WHEN num BETWEEN 85 and 100 THEN 1 ELSE 0 END) as '[100-85]', sum(CASE WHEN num BETWEEN 70 and 85 THEN 1 ELSE 0 END) as '[85-70]', sum(CASE WHEN num BETWEEN 60 and 70 THEN 1 ELSE 0 END) as '[70-60]', sum(CASE WHEN num < 60 THEN 1 ELSE 0 END) as '[ <60]' from score,course where score.c_id=course.c_id GROUP BY score.c_id;
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篇','2016-07-01 10:21:31'), ('第4篇','2016-07-22 09:23:21'), ('第5篇','2016-07-23 10:11:11'), ('第6篇','2016-07-25 11:21:31'), ('第7篇','2017-03-01 15:33:21'), ('第8篇','2017-03-01 17:32:21'), ('第9篇','2017-03-01 18:31:21'); select date_format(sub_time,'%Y-%m'),count(id) from blog group by date_format(sub_time,'%Y-%m');
自定義函數
注意
- 函數中不要寫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 ; # 在查詢中使用函數 select f1(11,nid) ,name from tb2; # 刪除函數 drop function func_name;
流程式控制制
if 條件語句
# if條件語句 delimiter // # 修改mysql 默認的語句結束符 CREATE PROCEDURE proc_if () BEGIN declare i int default 0; if i = 1 THEN SELECT 1; ELSEIF i = 2 THEN SELECT 2; ELSE SELECT 7; END IF; END // delimiter ; # 將sql語句默認結束符改回 ;
while 循環
# while循環 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 ;
索引與慢查詢優化 **
mysql 默認有查詢優化機制，我們不需要再上面花多少精力，能優化的，mysql基本都直接給我們優化了（可適當增加幾個索引）
詳細內容參考egon 的部落格
第八篇：索引原理與慢查詢優化 ，寫的很詳細，案例也很充分，我這個寫的不好?
本篇僅作快速了解，第一遍最好是去看 egon 的部落格（能有個詳細的了解）感覺寫的著實不錯，直接複製過來了。。。
前言（摘抄）
索引是應用程式設計和開發的一個重要方面。
若索引太多，應用程式的性能可能會受到影響，而索引太少，對查詢性能又會產生影響，要找到一個平衡點，這對應用程式的性能至關重要。
一些開發人員總是在事後才想起添加索引—-我一直認為，這源於一種錯誤的開發模式，如果知道數據的使用，從一開始就應該在需要處添加索引。
開發人員往往對資料庫的使用停留在應用的層面，比如編寫SQL語句、存儲過程之類，他們甚至可能不知道索引的存在，或認為事後讓相關DBA加上即可，但DBA往往不夠了解業務的數據流，而添加索引需要通過監控大量的SQL語句進而從中找到問題，這個步驟所需的時間肯定是遠大於初始添加索引所需的時間，並且可能會遺漏一部分的索引。
當然索引也並不是越多越好，我曾經遇到過這樣一個問題： 某台MySQL伺服器io stat顯示磁碟使用率一直處於100%，經過分析後發現是由於開發人員添加了太多的索引，在刪除一些不必要的索引之後，磁碟使用率馬上下降為20%。 可見索引的添加也是非常有技術含量的。
這一塊了解個大概即可，能在寫sql語句時稍微考慮下性能就可以了，畢竟我是要搞開發的呀~
索引
索引的目的在於提高查詢效率，與我們查閱圖書所用的目錄是一個道理：先定位到章，然後定位到該章下的一個小節，然後找到頁數。
索引在MySQL中也叫「鍵 key」， 是存儲引擎用於快速找到記錄的一種數據結構
擴展閱讀
索引原理
資料庫比起查字典，飛機航班等顯然要複雜的多，因為不僅面臨著等值查詢，還有範圍查詢(>、<、between、in)、模糊查詢(like)、並集查詢(or)等等。 那資料庫應該選擇怎麼樣的方式來應對所有的問題呢？我們回想字典的例子，能不能把數據分成段，然後分段查詢呢？ 最簡單的如果1000條數據，1到100分成第一段，101到200分成第二段，201到300分成第三段……這樣查第250條數據，只要找第三段就可以了，一下子去除了90%的無效數據。 但如果是1千萬的記錄呢，分成幾段比較好？稍有演算法基礎的同學會想到搜索樹，其平均複雜度是lgN，具有不錯的查詢性能。 但這裡我們忽略了一個關鍵的問題，複雜度模型是基於每次相同的操作成本來考慮的。 而資料庫實現比較複雜，一方面數據是保存在磁碟上的，另外一方面為了提高性能，每次又可以把部分數據讀入記憶體來計算，因為我們知道訪問磁碟的成本大概是訪問記憶體的十萬倍左右，所以簡單的搜索樹難以滿足複雜的應用場景。
磁碟IO與預讀
前面提到了訪問磁碟，那麼這裡先簡單介紹一下磁碟IO和預讀。 磁碟讀取數據靠的是機械運動，每次讀取數據花費的時間可以分為尋道時間、旋轉延遲、傳輸時間三個部分， 尋道時間指的是磁臂移動到指定磁軌所需要的時間，主流磁碟一般在5ms以下； 旋轉延遲就是我們經常聽說的磁碟轉速，比如一個磁碟7200轉，表示每分鐘能轉7200次，也就是說1秒鐘能轉120次，旋轉延遲就是1/120/2 = 4.17ms； 傳輸時間指的是從磁碟讀出或將數據寫入磁碟的時間，一般在零點幾毫秒，相對於前兩個時間可以忽略不計。 那麼訪問一次磁碟的時間，即一次磁碟IO的時間約等於5+4.17 = 9ms左右，聽起來還挺不錯的，但要知道一台500 -MIPS（Million Instructions Per Second）的機器每秒可以執行5億條指令，因為指令依靠的是電的性質，換句話說執行一次IO的時間可以執行約450萬條指令，資料庫動輒十萬百萬乃至千萬級數據，每次9毫秒的時間，顯然是個災難。 下圖是電腦硬體延遲的對比圖，供大家參考：
考慮到磁碟IO是非常高昂的操作，電腦作業系統做了一些優化，當一次IO時，不光把當前磁碟地址的數據，而是把相鄰的數據也都讀取到記憶體緩衝區內，因為局部預讀性原理告訴我們，當電腦訪問一個地址的數據的時候，與其相鄰的數據也會很快被訪問到。 每一次IO讀取的數據我們稱之為一頁(page)。具體一頁有多大數據跟作業系統有關，一般為4k或8k，也就是我們讀取一頁內的數據時候，實際上才發生了一次IO，這個理論對於索引的數據結構設計非常有幫助。
常見索引
- primary key 主鍵索引
- unique key 唯一性索引
- index key 普通索引
- 聯合索引（上面三個展開），下面的聯合索引有介紹
上述三個鍵都可以加快查詢，primary key 和 unique key 除索引外還有額外的約束 外鍵是用來創建表與表之間關聯關係的，不算索引
索引一定是自己建的（key），普通欄位沒有 索引（之前我理解錯了）
索引的本質
通過不斷地縮小想要獲取數據的範圍來篩選出最終想要的結果，同時把隨機的事件變成順序的事件，也就是說，有了這種索引機制，我們可以總是用同一種查找方式來鎖定數據。
索引的缺點
- 在表中有大量數據時，創建索引速度會很慢
- 在索引創建完畢後，對錶的查詢性能會大幅度提升
- 往創建好索引的表（有大量數據）里插入數據會變得非常慢（更新索引導致慢）
小結
雖然索引好用，但應該在合理範圍內去用，並不是越多越好
索引的數據結構 — B+ 樹
前面講了索引的基本原理，資料庫的複雜性，又講了作業系統的相關知識，目的就是讓大家了解，任何一種數據結構都不是憑空產生的，一定會有它的背景和使用場景，我們現在總結一下：
我們需要這種數據結構能夠做些什麼，其實很簡單，那就是：每次查找數據時把磁碟IO次數控制在一個很小的數量級，最好是常數數量級。
那麼我們就想到如果一個高度可控的多路搜索樹是否能滿足需求呢？
就這樣，b+樹應運而生（B+樹是通過二叉查找樹，再由平衡二叉樹，B樹演化而來）。
如上圖，是一顆b+樹，關於b+樹的定義可以參見B+樹，這裡只說一些重點.
淺藍色的塊我們稱之為一個磁碟塊，可以看到每個磁碟塊包含幾個數據項（深藍色所示）和指針（黃色所示），
如磁碟塊1包含數據項17和35，包含指針P1、P2、P3，P1表示小於17的磁碟塊，P2表示在17和35之間的磁碟塊，P3表示大於35的磁碟塊。
真實的數據存在於葉子節點即3、5、9、10、13、15、28、29、36、60、75、79、90、99。
非葉子節點只不存儲真實的數據，只存儲指引搜索方向的數據項，如17、35並不真實存在於數據表中。
b+樹的查找過程
如圖所示，如果要查找數據項29，那麼首先會把磁碟塊1由磁碟載入到記憶體，此時發生一次IO，在記憶體中用二分查找確定29在17和35之間，鎖定磁碟塊1的P2指針，記憶體時間因為非常短（相比磁碟的IO）可以忽略不計，通過磁碟塊1的P2指針的磁碟地址把磁碟塊3由磁碟載入到記憶體，發生第二次IO，29在26和30之間，鎖定磁碟塊3的P2指針，通過指針載入磁碟塊8到記憶體，發生第三次IO，同時記憶體中做二分查找找到29，結束查詢，總計三次IO。
真實的情況是，3層的b+樹可以表示上百萬的數據，如果上百萬的數據查找只需要三次IO，性能提高將是巨大的，如果沒有索引，每個數據項都要發生一次IO，那麼總共需要百萬次的IO，顯然成本非常非常高。
案例：查 71
b+樹性質
索引欄位要盡量的小
通過上面的分析，我們知道IO次數取決於b+數的高度h，假設當前數據表的數據為N，每個磁碟塊的數據項的數量是m，則有h=㏒(m+1)N，當數據量N一定的情況下，m越大，h越小；而m = 磁碟塊的大小 / 數據項的大小，磁碟塊的大小也就是一個數據頁的大小，是固定的，如果數據項占的空間越小，數據項的數量越多，樹的高度越低。
這就是為什麼每個數據項，即索引欄位都要盡量的小，比如int佔4位元組，要比bigint8位元組少一半。這也是為什麼b+樹要求把真實的數據放到葉子節點而不是內層節點，一旦放到內層節點，磁碟塊的數據項會大幅度下降，導致樹增高。當數據項等於1時將會退化成線性表。
索引的最左匹配特性
當b+樹的數據項是複合的數據結構，比如(name,age,sex)的時候，b+數是按照從左到右的順序來建立搜索樹的，比如當(張三,20,F)這樣的數據來檢索的時候，b+樹會優先比較name來確定下一步的所搜方向，如果name相同再依次比較age和sex，最後得到檢索的數據；但當(20,F)這樣的沒有name的數據來的時候，b+樹就不知道下一步該查哪個節點，因為建立搜索樹的時候name就是第一個比較因子，必須要先根據name來搜索才能知道下一步去哪裡查詢。比如當(張三,F)這樣的數據來檢索時，b+樹可以用name來指定搜索方向，但下一個欄位age的缺失，所以只能把名字等於張三的數據都找到，然後再匹配性別是F的數據了， 這個是非常重要的性質，即索引的最左匹配特性。
只有葉子結點存放真實數據，根和樹枝節點存的僅僅是虛擬數據
查詢次數由樹的層級決定，層級越低次數越少查詢速度越快（磁碟塊存的數據越多層級越少越容易拿到數據）
————> 這也是把 id 作為主鍵的原因
一個磁碟塊兒的大小是一定的，那也就意味著能存的數據量是一定的。
如何保證樹的層級最低呢？一個磁碟塊兒存放佔用空間比較小的數據項
聚集索引非聚集索引
索引也有不同的種類，按不同的要求去分（不單單是一個欄位作為索引）
聚集索引(primary key)
聚集索引其實指的就是表的主鍵（一般都是 id欄位）
InnoDB 引擎規定一張表中必須要有主鍵 InnoDB 在建表的時候對應到硬碟上是兩個文件，
.frm 表結構文件只存放表結構，不可能放索引，也就意味著 InnoDB 的索引跟數據都放在.ibd 表數據文件中
聚集索引特點：葉子結點放的是一條條完整的記錄
輔助索引(unique,index)
查詢數據的時候不可能都是用 id 作為篩選條件，也可能會用 id 之外的 name，password 等欄位資訊，那麼這個時候就無法利用到聚集索引的加速查詢優勢。
此時就需要給其他欄位建立（的）索引，這些索引就叫輔助索引
輔助索引特點：葉子結點存放的是輔助索引欄位對應的那條記錄的主鍵的值（比如：按照name欄位創建索引，那麼葉子節點存放的是：{name對應的值：name所在的那條記錄的主鍵值}）找到後再拿著id 去聚集索引裡面去查
聚集索引和非聚集索引的不同
葉子結點存放的是否是一整行的資訊
覆蓋索引
InnoDB存儲引擎支援覆蓋索引（covering index，或稱索引覆蓋） 使用覆蓋索引的一個好處是：輔助索引不包含整行記錄的所有資訊，故其大小要遠小於聚集索引，因此可以減少大量的IO操作
select name from user where name='jason';
覆蓋索引：只在輔助索引的葉子節點中就已經找到了所有我們想要的數據（條件和要查的欄位相同的時候）
where 條件後面的欄位作為輔助索引，select 後面的欄位正好是（沒有多餘的欄位）要找的數據，如果輔助索引和要找的數據一樣，那就找到了，就是覆蓋索引 –> 個人解釋，沒有權威性
非覆蓋索引
select age from user where name='jason';
雖然查詢的時候查到了輔助索引name，但是要查的是age欄位，所以查到name欄位後還需要利用查到的聚集索引id 才去查找那條記錄中 age 欄位的值
聯合索引
- primary key(host, port) 聯合主鍵索引（一般不用聯合主鍵，主鍵一般都是專門的 id 欄位）
- unique(host, port) 聯合唯一索引
- index(host, port) 聯合普通索引
select count(id) from s1 where name='jason' and gender = 'male' and id > 3 and email = 'xxx'; # 如果上述四個欄位區分度都很高，那給誰建都能加速查詢 # 給email加然而不用email欄位 select count(id) from s1 where name='jason' and gender = 'male' and id > 3; # 給name加然而不用name欄位 select count(id) from s1 where gender = 'male' and id > 3; # 給gender加然而不用gender欄位 select count(id) from s1 where id > 3; # 帶來的問題是所有的欄位都建了索引然而都沒有用到，還需要花費四次建立的時間 create index idx_all on s1(email,name,gender,id); # 最左匹配原則，區分度高的往左放 select count(id) from s1 where name='jason' and gender = 'male' and id > 3 and email = 'xxx'; # 速度變快
慢查詢優化
查詢優化神器-explain 通過分析sql語句來提升效率
設定一個時間檢測所有超出改時間的sql語句，然後針對性的進行優化！
根據使用場景管理並設置合適的索引
注意這塊的小點能提高sql語句性能
摘自egon部落格：，放自己部落格方便看
測試索引
數據準備
#1. 準備表 create table s1( id int, name varchar(20), gender char(6), email varchar(50) ); #2. 創建存儲過程，實現批量插入記錄 delimiter $$ #聲明存儲過程的結束符號為$$ create procedure auto_insert1() BEGIN declare i int default 1; while(i<3000000)do insert into s1 values(i,'egon','male',concat('egon',i,'@oldboy')); set i=i+1; end while; END$$ #$$結束 delimiter ; #重新聲明分號為結束符號 #3. 查看存儲過程 show create procedure auto_insert1G #4. 調用存儲過程 call auto_insert1();
在沒有索引的前提下測試查詢速度
#無索引：mysql根本就不知道到底是否存在id等於333333333的記錄，只能把數據表從頭到尾掃描一遍，此時有多少個磁碟塊就需要進行多少IO操作，所以查詢速度很慢 mysql> select * from s1 where id=333333333; Empty set (0.33 sec)
在表中已經存在大量數據的前提下，為某個欄位段建立索引，建立速度會很慢
在索引建立完畢後，以該欄位為查詢條件時，查詢速度提升明顯
1. mysql先去索引表裡根據b+樹的搜索原理很快搜索到id等於333333333的記錄不存在，IO大大降低，因而速度明顯提升 2. 我們可以去mysql的data目錄下找到該表，可以看到佔用的硬碟空間多了 3.需要注意，如下圖
總結
#1. 一定是為搜索條件的欄位創建索引，比如select * from s1 where id = 333;就需要為id加上索引 #2. 在表中已經有大量數據的情況下，建索引會很慢，且佔用硬碟空間，建完後查詢速度加快 比如create index idx on s1(id);會掃描表中所有的數據，然後以id為數據項，創建索引結構，存放於硬碟的表中。 建完以後，再查詢就會很快了。 #3. 需要注意的是：innodb表的索引會存放於s1.ibd文件中，而myisam表的索引則會有單獨的索引文件table1.MYI MySAM索引文件和數據文件是分離的，索引文件僅保存數據記錄的地址。而在innodb中，表數據文件本身就是按照B+Tree（BTree即Balance True）組織的一個索引結構，這棵樹的葉節點data域保存了完整的數據記錄。這個索引的key是數據表的主鍵，因此innodb表數據文件本身就是主索引。 因為inndob的數據文件要按照主鍵聚集，所以innodb要求表必須要有主鍵（Myisam可以沒有），如果沒有顯式定義，則mysql系統會自動選擇一個可以唯一標識數據記錄的列作為主鍵，如果不存在這種列，則mysql會自動為innodb表生成一個隱含欄位作為主鍵，這欄位的長度為6個位元組，類型為長整型.
正確使用索引
索引未命中
並不是說我們創建了索引就一定會加快查詢速度， 若想利用索引達到預想的提高查詢速度的效果，我們在添加索引時，必須遵循以下問題
範圍問題
範圍問題，或者說條件不明確，條件中出現這些符號或關鍵字：>、>=、<、<=、!= 、between…and…、like、
大於號、小於號
不等於！=
between …and…
like
建立索引的欄位選擇
盡量選擇區分度高的列作為索引，區分度的公式是count(distinct col)/count(*)，表示欄位不重複的比例，比例越大我們掃描的記錄數越少，唯一鍵的區分度是1，而一些狀態、性別欄位可能在大數據面前區分度就是0，那可能有人會問，這個比例有什麼經驗值嗎？使用場景不同，這個值也很難確定，一般需要join的欄位我們都要求是0.1以上，即平均1條掃描10條記錄
#先把表中的索引都刪除，讓我們專心研究區分度的問題 mysql> desc s1; +--------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +--------+-------------+------+-----+---------+-------+ | id | int(11) | YES | MUL | NULL | | | name | varchar(20) | YES | | NULL | | | gender | char(5) | YES | | NULL | | | email | varchar(50) | YES | MUL | NULL | | +--------+-------------+------+-----+---------+-------+ rows in set (0.00 sec) mysql> drop index a on s1; Query OK, 0 rows affected (0.20 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> drop index d on s1; Query OK, 0 rows affected (0.18 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> desc s1; +--------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +--------+-------------+------+-----+---------+-------+ | id | int(11) | YES | | NULL | | | name | varchar(20) | YES | | NULL | | | gender | char(5) | YES | | NULL | | | email | varchar(50) | YES | | NULL | | +--------+-------------+------+-----+---------+-------+ rows in set (0.00 sec) #先把表中的索引都刪除，讓我們專心研究區分度的問題
我們編寫存儲過程為表s1批量添加記錄，name欄位的值均為egon，也就是說name這個欄位的區分度很低（gender欄位也是一樣的，我們稍後再搭理它） 回憶b+樹的結構，查詢的速度與樹的高度成反比，要想將樹的高低控制的很低，需要保證：在某一層內數據項均是按照從左到右，從小到大的順序依次排開，即左1<左2<左3<... 而對於區分度低的欄位，無法找到大小關係，因為值都是相等的，毫無疑問，還想要用b+樹存放這些等值的數據，只能增加樹的高度，欄位的區分度越低，則樹的高度越高。極端的情況，索引欄位的值都一樣，那麼b+樹幾乎成了一根棍。本例中就是這種極端的情況，name欄位所有的值均為'egon' #現在我們得出一個結論：為區分度低的欄位建立索引，索引樹的高度會很高，然而這具體會帶來什麼影響呢？？？ #1：如果條件是name='xxxx',那麼肯定是可以第一時間判斷出'xxxx'是不在索引樹中的（因為樹中所有的值均為'egon』），所以查詢速度很快 #2：如果條件正好是name='egon',查詢時，我們永遠無法從樹的某個位置得到一個明確的範圍，只能往下找，往下找，往下找。。。這與全表掃描的IO次數沒有多大區別，所以速度很慢
條件中的 = 和 in 可以亂序（mysql查詢優化器自動優化）
=和in可以亂序，比如a = 1 and b = 2 and c = 3 建立(a,b,c)索引可以任意順序，mysql的查詢優化器會幫你優化成索引可以識別的形式
索引列不能參加運算
索引列不能參與計算，保持列「乾淨」，比如from_unixtime(create_time) = 』2014-05-29』就不能使用到索引，原因很簡單，b+樹中存的都是數據表中的欄位值，但進行檢索時，需要把所有元素都應用函數才能比較，顯然成本太大。所以語句應該寫成create_time = unix_timestamp(』2014-05-29』)
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
在左邊條件成立但是索引欄位的區分度低的情況下（name與gender均屬於這種情況），會依次往右找到一個區分度高的索引欄位，加速查詢
經過分析，在條件為name='egon' and gender='male' and id>333 and email='xxx'的情況下，我們完全沒必要為前三個條件的欄位加索引，因為只能用上email欄位的索引，前三個欄位的索引反而會降低我們的查詢效率
最左匹配原則
最左前綴匹配原則，是非常重要的原則，對於組合索引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的順序可以任意調整。
其他情況
- 使用函數 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類型，必須制定長度
其他注意事項
- 避免使用select * - count(1)或count(列) 代替 count(*) (默認是用id 聚集索引去查，效率會高很多) - 創建表時盡量時 char 代替 varchar (自行選擇執行效率還是硬碟資源) - 表的欄位順序固定長度的欄位優先 - 組合索引代替多個單列索引（經常使用多個條件查詢時） - 盡量使用短索引 （單個磁碟片上的數據多，層級少，查的快） - 使用連接（JOIN）來代替子查詢(Sub-Queries) - 連表時注意條件類型需一致 - 索引散列值（重複少）不適合建索引，例：性別不適合（大量重複的，分層不好找）
