目錄
1.mysql體系結(jié)構(gòu)?
2.存儲(chǔ)引擎
2.1.存儲(chǔ)引擎概述
2.2.1.InnoDB
2.2.2.MyISAM
2.2.3.存儲(chǔ)引擎選擇
3.存儲(chǔ)過(guò)程
3.1.存儲(chǔ)過(guò)程和函數(shù)概述?
3.2.創(chuàng)建存儲(chǔ)過(guò)程
3.3.調(diào)用存儲(chǔ)過(guò)程
3.4.查看存儲(chǔ)過(guò)程
3.5.刪除存儲(chǔ)過(guò)程
3.6.語(yǔ)法
3.6.1.變量
3.6.2.if條件判斷
3.6.3.傳遞參數(shù)
3.6.4.case結(jié)構(gòu)
3.6.5.while循環(huán)
3.6.6.repeat結(jié)構(gòu)
3.6.7.游標(biāo)/光標(biāo)
3.7.存儲(chǔ)函數(shù) ---存儲(chǔ)過(guò)程 函數(shù)：函數(shù)有返回值
4.索引
4.1.索引概述
4.2.索引優(yōu)勢(shì)劣勢(shì)
4.3.索引結(jié)構(gòu)
4.3.1.二叉樹(shù)
4.3.2.B-TREE 結(jié)構(gòu)
4.3.3.B+Tree結(jié)構(gòu)-->Mysql
4.3.4.數(shù)據(jù)庫(kù)中B+tree的結(jié)構(gòu)
4.4.索引分類(lèi)
4.5.索引的語(yǔ)法?
4.6.性能分析
4.7.索引使用規(guī)則
1.mysql體系結(jié)構(gòu)?
1） 連接層
主要完成一些類(lèi)似于連接處理、授權(quán)認(rèn)證、及相關(guān)的安全方案。在該層上引入了線程池的概念，為通過(guò)認(rèn)證安全接入的客戶端提供線程。同樣在該層上可以實(shí)現(xiàn)基于SSL的安全鏈接。服務(wù)器也會(huì)為安全接入的每個(gè)客戶端驗(yàn)證它所具有的操作權(quán)限。
2） 服務(wù)層
第二層架構(gòu)主要完成大多數(shù)的核心服務(wù)功能，如SQL接口，并完成緩存的查詢，SQL的分析和優(yōu)化，部分內(nèi)置函數(shù)的執(zhí)行。所有跨存儲(chǔ)引擎的功能也在這一層實(shí)現(xiàn)，如 過(guò)程、函數(shù)等。在該層，服務(wù)器會(huì)解析查詢并創(chuàng)建相應(yīng)的內(nèi)部解析樹(shù)，并對(duì)其完成相應(yīng)的優(yōu)化如確定表的查詢的順序，是否利用索引等， 最后生成相應(yīng)的執(zhí)行操作。如果是select語(yǔ)句，服務(wù)器還會(huì)查詢內(nèi)部的緩存，如果緩存空間足夠大，這樣在解決大量讀操作的環(huán)境中能夠很好的提升系統(tǒng)的性能。
3） 引擎層 [存儲(chǔ)引擎]
存儲(chǔ)引擎層， 存儲(chǔ)引擎真正的負(fù)責(zé)了MySQL中數(shù)據(jù)的存儲(chǔ)和提取，服務(wù)器通過(guò)API和存儲(chǔ)引擎進(jìn)行通信。不同的存儲(chǔ)引擎具有不同的功能，這樣我們可以根據(jù)自己的需要，來(lái)選取合適的存儲(chǔ)引擎。 在之前[MyISAM]: MySQL5.5之后，MySQL默認(rèn)的存儲(chǔ)引擎就是InnoDB,InnoDB默認(rèn)使用的索引結(jié)構(gòu)就是B+樹(shù),上面的服務(wù)層就是通過(guò)API接口與存儲(chǔ)引擎層進(jìn)行交互的
4）存儲(chǔ)層
數(shù)據(jù)存儲(chǔ)層， 主要是將數(shù)據(jù)存儲(chǔ)在文件系統(tǒng)之上，并完成與存儲(chǔ)引擎的交互。
和其他數(shù)據(jù)庫(kù)相比，MySQL有點(diǎn)與眾不同，它的架構(gòu)可以在多種不同場(chǎng)景中應(yīng)用并發(fā)揮良好作用。主要體現(xiàn)在存儲(chǔ)引擎上，插件式的存儲(chǔ)引擎架構(gòu)，將查詢處理和其他的系統(tǒng)任務(wù)以及數(shù)據(jù)的存儲(chǔ)提取分離。這種架構(gòu)可以根據(jù)業(yè)務(wù)的需求和實(shí)際需要選擇合適的存儲(chǔ)引擎。
2.存儲(chǔ)引擎
2.1.存儲(chǔ)引擎概述
和大多數(shù)的數(shù)據(jù)庫(kù)不同, MySQL中有一個(gè)存儲(chǔ)引擎的概念, 針對(duì)不同的存儲(chǔ)需求可以選擇最優(yōu)的存儲(chǔ)引擎。
存儲(chǔ)引擎就是存儲(chǔ)數(shù)據(jù)，建立索引，更新查詢數(shù)據(jù)等技術(shù)的實(shí)現(xiàn)方式 。存儲(chǔ)引擎是基于表的，而不是基于庫(kù)的。
Oracle，SqlServer 等數(shù)據(jù)庫(kù)只有一種存儲(chǔ)引擎。MySQL提供了插件式的存儲(chǔ)引擎架構(gòu)。所以MySQL存在多種存儲(chǔ)引擎，可以根據(jù)需要使用相應(yīng)引擎。
可以通過(guò)指定 show engines ， 來(lái)查詢當(dāng)前數(shù)據(jù)庫(kù)支持的存儲(chǔ)引擎 ：\
創(chuàng)建新表時(shí)如果不指定存儲(chǔ)引擎，那么系統(tǒng)就會(huì)使用默認(rèn)的存儲(chǔ)引擎，MySQL5.5之前的默認(rèn)存儲(chǔ)引擎是MyISAM，5.5之后就改為了InnoDB。
查看Mysql數(shù)據(jù)庫(kù)默認(rèn)的存儲(chǔ)引擎 ， 指令 ：
show variables like '%storage_engine%' ；
2.2.1.InnoDB
InnoDB存儲(chǔ)引擎是Mysql的默認(rèn)存儲(chǔ)引擎。InnoDB存儲(chǔ)引擎提供了具有提交、回滾、崩潰恢復(fù)能力的事務(wù)安全。但是對(duì)比MyISAM的存儲(chǔ)引擎，InnoDB寫(xiě)的處理效率差一些，并且會(huì)占用更多的磁盤(pán)空間以保留數(shù)據(jù)和索引。
InnoDB存儲(chǔ)引擎不同于其他存儲(chǔ)引擎的特點(diǎn) ：
事務(wù)控制
-- 創(chuàng)建表 create table goods_innodb( id int NOT NULL AUTO_INCREMENT, name varchar(20) NOT NULL, primary key(id) )ENGINE=innodb DEFAULT CHARSET=utf8;-- innodb支持事務(wù) start transaction; -- 開(kāi)啟事務(wù) INSERT INTO goods_innodb VALUES (1,'aaa'); -- 添加數(shù)據(jù) commit; -- 提交事務(wù) 因?yàn)橹С质聞?wù)所以不提交則添加失敗測(cè)試，發(fā)現(xiàn)在InnoDB中是存在事務(wù)的
外鍵約束
MySQL支持外鍵的存儲(chǔ)引擎只有InnoDB， 在創(chuàng)建外鍵的時(shí)候， 要求父表必須有對(duì)應(yīng)的索引 ， 子表在創(chuàng)建外鍵的時(shí)候， 也會(huì)自動(dòng)的創(chuàng)建對(duì)應(yīng)的索引。
下面兩張表中 ， country_innodb是父表 ， country_id為主鍵索引，city_innodb表是子表，country_id字段為外鍵，對(duì)應(yīng)于country_innodb表的主鍵country_id 。
-- 創(chuàng)建表 測(cè)試是否支持外鍵 create table country_innodb( country_id int NOT NULL AUTO_INCREMENT, country_name varchar(100) NOT NULL, primary key(country_id) )ENGINE=InnoDB DEFAULT CHARSET=utf8; create table city_innodb( city_id int NOT NULL AUTO_INCREMENT, city_name varchar(50) NOT NULL, country_id int NOT NULL, -- 外鍵 primary key(city_id), key idx_fk_country_id(country_id), CONSTRAINT `fk_city_country` FOREIGN KEY(country_id) REFERENCES country_innodb(country_id) ON DELETE RESTRICT ON UPDATE CASCADE )ENGINE=InnoDB DEFAULT CHARSET=utf8; insert into country_innodb values(null,'China'),(null,'America'),(null,'Japan'); insert into city_innodb values(null,'Xian',1),(null,'NewYork',2),(null,'BeiJing',1);在創(chuàng)建索引時(shí)， 可以指定在刪除、更新父表時(shí)，對(duì)子表進(jìn)行的相應(yīng)操作，包括 RESTRICT、CASCADE、SET NULL 和 NO ACTION。
- RESTRICT和NO ACTION相同， 是指限制在子表有關(guān)聯(lián)記錄的情況下， 父表不能更新；
- CASCADE表示父表在更新或者刪除時(shí)，更新或者刪除子表對(duì)應(yīng)的記錄；
- SET NULL 則表示父表在更新或者刪除的時(shí)候，子表的對(duì)應(yīng)字段被SET NULL 。
針對(duì)上面創(chuàng)建的兩個(gè)表， 子表的外鍵指定是ON DELETE RESTRICT ON UPDATE CASCADE 方式的， 那么在主表刪除記錄的時(shí)候， 如果子表有對(duì)應(yīng)記錄， 則不允許刪除， 主表在更新記錄的時(shí)候， 如果子表有對(duì)應(yīng)記錄， 則子表對(duì)應(yīng)更新 。
表中數(shù)據(jù)如下圖所示 ：
刪除country_id為1 的country數(shù)據(jù)：
delete from country_innodb where country_id = 1;有外鍵約束，刪除失敗
?更新主表country表的字段 country_id :
update country_innodb set country_id = 100 where country_id = 1;更新后， 子表的數(shù)據(jù)信息為 ：
2.2.2.MyISAM
MyISAM 不支持事務(wù)、也不支持外鍵，其優(yōu)勢(shì)是訪問(wèn)的速度快，對(duì)事務(wù)的完整性沒(méi)有要求或者以SELECT、INSERT為主的應(yīng)用基本上都可以使用這個(gè)引擎來(lái)創(chuàng)建表 。有以下兩個(gè)比較重要的特點(diǎn)：
不支持事務(wù)
-- 創(chuàng)建表 create table goods_myisam( id int NOT NULL AUTO_INCREMENT, name varchar(20) NOT NULL, primary key(id) )ENGINE=myisam DEFAULT CHARSET=utf8;-- MyISAM不支持事務(wù) start transaction; -- 開(kāi)啟事務(wù) insert into goods_MyISAM VALUES (1,'aaa'); -- 添加數(shù)據(jù)通過(guò)測(cè)試，我們發(fā)現(xiàn)，在MyISAM存儲(chǔ)引擎中，是沒(méi)有事務(wù)控制的 ；
不支持外鍵
create table country_myisam( country_id int NOT NULL AUTO_INCREMENT, country_name varchar(100) NOT NULL, primary key(country_id) )ENGINE=MyISAM DEFAULT CHARSET=utf8; create table city_myisam( city_id int NOT NULL AUTO_INCREMENT, city_name varchar(50) NOT NULL, country_id int NOT NULL, primary key(city_id), key idx_fk_country_id(country_id), CONSTRAINT `fk_city_country` FOREIGN KEY(country_id) REFERENCES country_myisam(country_id) ON DELETE RESTRICT ON UPDATE CASCADE )ENGINE=MyISAM DEFAULT CHARSET=utf8; insert into country_myisam values(null,'China'),(null,'America'),(null,'Japan'); insert into city_myisam values(null,'Xian',1),(null,'NewYork',2),(null,'BeiJing',1);
?通過(guò)刪除測(cè)試，我們發(fā)現(xiàn)，在MyISAM存儲(chǔ)引擎中，是沒(méi)有外鍵約束的
- innodb: 支持事務(wù)和外鍵，而且表結(jié)構(gòu)存在一個(gè)文件中，索引和數(shù)據(jù)存儲(chǔ)在idb文件中
- myisam: 不支持事務(wù)和外鍵，它的表結(jié)構(gòu)存在在frm中，索引和數(shù)據(jù)分別存在在myi和myd文件中
2.2.3.存儲(chǔ)引擎選擇
在選擇存儲(chǔ)引擎時(shí)，應(yīng)該根據(jù)應(yīng)用系統(tǒng)的特點(diǎn)選擇合適的存儲(chǔ)引擎。對(duì)于復(fù)雜的應(yīng)用系統(tǒng)，還可以根據(jù)實(shí)際情況選擇多種存儲(chǔ)引擎進(jìn)行組合。
- InnoDB:是Mysql的默認(rèn)存儲(chǔ)引擎，支持事務(wù)、外鍵。如果應(yīng)用對(duì)事務(wù)的完整性有比較高的要求，在并發(fā)條件下要求數(shù)據(jù)的一致性，數(shù)據(jù)操作除了插入和查詢之外，還包含很多的更新、刪除操作，那么InnoDB存儲(chǔ)引擎是比較合適的選擇。
- MyISAM:如果應(yīng)用是以讀操作和插入操作為主，只有很少的更新和刪除操作，并且對(duì)事務(wù)的完整性、并發(fā)性要求不是很高，那么選擇這個(gè)存儲(chǔ)引擎是非常合適的。
3.存儲(chǔ)過(guò)程
3.1.存儲(chǔ)過(guò)程和函數(shù)概述?
存儲(chǔ)過(guò)程和函數(shù)是 事先經(jīng)過(guò)編譯并存儲(chǔ)在數(shù)據(jù)庫(kù)中的一段 SQL 語(yǔ)句的集合，調(diào)用存儲(chǔ)過(guò)程和函數(shù)可以簡(jiǎn)化應(yīng)用開(kāi)發(fā)人員的很多工作，減少數(shù)據(jù)在數(shù)據(jù)庫(kù)和應(yīng)用服務(wù)器之間的傳輸，對(duì)于提高數(shù)據(jù)處理的效率是有好處的。
存儲(chǔ)過(guò)程和函數(shù)的區(qū)別在于函數(shù)必須有返回值，而存儲(chǔ)過(guò)程沒(méi)有。
函數(shù) ： 是一個(gè)有返回值的過(guò)程 ；
過(guò)程 ： 是一個(gè)沒(méi)有返回值的函數(shù) ；
3.2.創(chuàng)建存儲(chǔ)過(guò)程
語(yǔ)法：
CREATE PROCEDURE procedure_name ([proc_parameter[,...]]) begin -- SQL語(yǔ)句 end ;示例 ：
-- 存儲(chǔ)過(guò)程入門(mén)01 delimiter $ -- 定義結(jié)束符 CREATE PROCEDURE p01() BEGIN select 'Hello 存儲(chǔ)過(guò)程' ; END $ delimiter ;DELIMITER：該關(guān)鍵字用來(lái)聲明SQL語(yǔ)句的分隔符 , 告訴 MySQL 解釋器，該段命令是否已經(jīng)結(jié)束了，mysql是否可以執(zhí)行了。默認(rèn)情況下，delimiter是分號(hào);。在命令行客戶端中，如果有一行命令以分號(hào)結(jié)束，那么回車(chē)后，mysql將會(huì)執(zhí)行該命令。
3.3.調(diào)用存儲(chǔ)過(guò)程
CALL p01();3.4.查看存儲(chǔ)過(guò)程
-- 查詢mysqlg數(shù)據(jù)庫(kù)中的所有的存儲(chǔ)過(guò)程 select name from mysql.proc where db='mysqlg'; -- 查詢存儲(chǔ)過(guò)程的狀態(tài)信息 show procedure status;3.5.刪除存儲(chǔ)過(guò)程
DROP PROCEDURE p01
3.6.語(yǔ)法
存儲(chǔ)過(guò)程是可以編程的，意味著可以使用變量，表達(dá)式，控制結(jié)構(gòu) ， 來(lái)完成比較復(fù)雜的功能。
3.6.1.變量
DECLARE：
通過(guò) DECLARE 可以定義一個(gè)局部變量，該變量的作用范圍只能在 BEGIN…END 塊中。
DECLARE var_name[,...] type [DEFAULT value]示例 :
-- 聲明變量 delimiter $ create procedure p02() -- 創(chuàng)建存儲(chǔ)過(guò)程 begin declare num int default 5; -- 聲明num，默認(rèn)為5 select num+ 10; -- 輸出加10 end $ delimiter ; CALL p02(); -- 調(diào)用存儲(chǔ)過(guò)程SET：直接賦值使用 SET，可以賦常量或者賦表達(dá)式，具體語(yǔ)法如下：
SET var_name = expr [, var_name = expr] ...示例 :
-- 為變量重新賦值 DELIMITER $ CREATE PROCEDURE p03() BEGIN declare num int default 5; -- 設(shè)置變量nun默認(rèn)為5 SET num = 20; -- 重新賦值num為20 SELECT num ; -- 輸出 END$ DELIMITER ; CALL p03(); -- 調(diào)用存儲(chǔ)過(guò)程也可以通過(guò)select ... into 方式進(jìn)行賦值操作 :
-- 通過(guò)select ... into 方式進(jìn)行賦值操作 : DELIMITER $ CREATE PROCEDURE p04() BEGIN declare num int default 5; -- num默認(rèn)為5 select count(*) into num from city_innodb; -- 將表city_innodb中共幾條數(shù)據(jù)賦值給num select num; -- 輸出 END$ DELIMITER ; CALL p04(); -- 調(diào)用存儲(chǔ)過(guò)程
3.6.2.if條件判斷
語(yǔ)法結(jié)構(gòu) :
if search_condition then statement_list [elseif search_condition then statement_list] ... [else statement_list] end if;需求：
根據(jù)定義的身高變量，判定當(dāng)前身高的所屬的身材類(lèi)型 并輸出該身材 180 及以上 ----------> 身材高挑 170 - 180 ---------> 標(biāo)準(zhǔn)身材 170 以下 ----------> 一般身材示例 :
delimiter $ create procedure p05() begin declare height int default 175; declare description varchar(50); if height >= 180 then set description = '身材高挑'; elseif height >= 170 and height < 180 then set description = '標(biāo)準(zhǔn)身材'; else set description = '一般身材'; end if; select description ; end$ delimiter ; call p05();
3.6.3.傳遞參數(shù)
語(yǔ)法格式 :
create procedure procedure_name([in/out/inout] 參數(shù)名 參數(shù)類(lèi)型) ... IN : 該參數(shù)可以作為輸入，也就是需要調(diào)用方傳入值 , 默認(rèn) OUT: 該參數(shù)作為輸出，也就是該參數(shù)可以作為返回值 INOUT: 既可以作為輸入?yún)?shù)，也可以作為輸出參數(shù)IN - 輸入
需求 :根據(jù)定義的身高變量，判定當(dāng)前身高的所屬的身材類(lèi)型?
示例 :
delimiter $ create procedure p06(in height int) begin declare description varchar(50) default ''; if height >= 180 then set description='身材高挑'; elseif height >= 170 and height < 180 then set description='標(biāo)準(zhǔn)身材'; else set description='一般身材'; end if; select concat('身高 ', height , '對(duì)應(yīng)的身材類(lèi)型為:',description); end$ delimiter ; call p06(200);OUT-輸出
需求 :根據(jù)傳入的身高變量，獲取當(dāng)前身高的所屬的身材類(lèi)型
示例:
delimiter $ CREATE PROCEDURE p07(in height int ,out description VARCHAR(50)) BEGIN if height >= 180 then set description = '111' ; ELSEIF height >= 170 and height < 180 THEN set description = '222' ; ELSE set description = '333' ; END if ; end $ delimiter ;?調(diào)用:
CALL p07(180,@a); -- @代表定義一個(gè)局部變量 SELECT @a;@a: 這種變量要在變量名稱前面加上“@”符號(hào)，叫做用戶會(huì)話變量，代表整個(gè)會(huì)話過(guò)程他都是有作用的，這個(gè)類(lèi)似于全局變量一樣。
@@global.sort_buffer_size : 這種在變量前加上 "@@" 符號(hào), 叫做 系統(tǒng)變量
3.6.4.case結(jié)構(gòu)
語(yǔ)法結(jié)構(gòu) :
方式一 : CASE case_value WHEN when_value THEN statement_list [WHEN when_value THEN statement_list] ... [ELSE statement_list] END CASE; ----傳遞一個(gè)int類(lèi)型的數(shù)字 如果為1 則輸出星期一 方式二 : 返回 CASE WHEN search_condition THEN statement_list [WHEN search_condition THEN statement_list] ... [ELSE statement_list] END CASE;需求: 給定一個(gè)月份, 然后計(jì)算出所在的季度 ?
?示例 :兩種方式
delimiter $ create procedure p08(month int) begin declare result varchar(20); case when month >= 1 and month <=3 then set result = '第一季度'; when month >= 4 and month <=6 then set result = '第二季度'; when month >= 7 and month <=9 then set result = '第三季度'; when month >= 10 and month <=12 then set result = '第四季度'; end case; select concat('您輸入的月份為 :', month , ' , 該月份為 : ' , result) as content ; end$ delimiter ; call p08(12); -- /// delimiter $ create procedure p09(in m int) begin case m when 1 then SELECT '第一季度'; when 2 then SELECT '第一季度'; when 3 then SELECT '第一季度'; when 4 then SELECT '第二季度'; when 5 then SELECT '第二季度'; when 6 then SELECT '第二季度'; when 7 then SELECT '第三季度'; when 8 then SELECT '第三季度'; when 9 then SELECT '第三季度'; when 10 then SELECT '第四季度'; when 11 then SELECT '第四季度'; when 12 then SELECT '第四季度'; else SELECT '輸入有誤' ; END case ; end$ delimiter ; CALL p09(10);
3.6.5.while循環(huán)
語(yǔ)法結(jié)構(gòu):
while search_condition do statement_list end while; --別忘記分號(hào)需求: 計(jì)算從1加到n的數(shù)的和值 ??
示例 :
delimiter $ create procedure p10(n int) begin declare total int default 0; declare num int default 1; while num<=n do set total = total + num; set num = num + 1; end while; select total; end$ delimiter ; CALL p10(100);
3.6.6.repeat結(jié)構(gòu)
有條件的循環(huán)控制語(yǔ)句, 當(dāng)滿足條件的時(shí)候退出循環(huán) 。while 是滿足條件才執(zhí)行，repeat 是滿足條件就退出循環(huán)。
語(yǔ)法結(jié)構(gòu) :
REPEAT statement_list UNTIL search_condition --不要加分號(hào) END REPEAT;需求: 計(jì)算從1加到n的值?
示例 :
-- repeat 結(jié)構(gòu) delimiter $ create procedure p11(n int) begin declare total int default 0; repeat set total = total + n; set n = n - 1; until n=0 end repeat; select total ; end$ delimiter ; call p11(100);
3.6.7.游標(biāo)/光標(biāo)
游標(biāo)是用來(lái)存儲(chǔ)查詢結(jié)果集的數(shù)據(jù)類(lèi)型 , 在存儲(chǔ)過(guò)程和函數(shù)中可以使用游標(biāo)對(duì)結(jié)果集進(jìn)行循環(huán)的處理。游標(biāo)的使用包括游標(biāo)的聲明、OPEN、FETCH 和 CLOSE，其語(yǔ)法分別如下。
聲明游標(biāo)：
DECLARE cursor_name CURSOR FOR select_statement ; -- select語(yǔ)句OPEN 游標(biāo)：
OPEN cursor_name ;FETCH 游標(biāo)：
FETCH cursor_name INTO var_name [, var_name] ...CLOSE游標(biāo)：
CLOSE cursor_name ;示例 :
初始化腳本:
create table emp( id int(11) not null auto_increment , name varchar(50) not null comment '姓名', age int(11) comment '年齡', salary int(11) comment '薪水', primary key(`id`) )engine=innodb default charset=utf8 ; insert into emp(id,name,age,salary) values(null,'金毛獅王',55,3800),(null,'白眉鷹王',60,4000),(null,'青翼蝠王',38,2800),(null,'紫衫龍王',42,1800);create PROCEDURE p13() begin DECLARE n varchar(20); DECLARE s int; DECLARE has_data int default 1; -- 判斷游標(biāo)中是否還有數(shù)據(jù) -- 聲明游標(biāo) DECLARE my CURSOR for select name,salary from emp; -- DECLARE EXIT HANDLER FOR NOT FOUND set has_data = 0; create table if not EXISTS tb_my( id int primary key auto_increment, name varchar(20), salary int ); -- 開(kāi)啟游標(biāo)-- open my; while has_data=1 do -- 取出游標(biāo)的數(shù)據(jù) FETCH my INTO n,s; insert into tb_my(name,salary) values(n,s); end while; close my; end; call p13();
使用mybatis調(diào)用到存儲(chǔ)過(guò)程。
通過(guò)循環(huán)結(jié)構(gòu) , 獲取游標(biāo)中的數(shù)據(jù) :
DELIMITER $ create procedure pro_test12() begin DECLARE id int(11); DECLARE name varchar(50); DECLARE age int(11); DECLARE salary int(11); DECLARE has_data int default 1; DECLARE emp_result CURSOR FOR select * from emp; -- 若沒(méi)有數(shù)據(jù)返回,程序繼續(xù),并將變量has_data設(shè)為0 DECLARE EXIT HANDLER FOR NOT FOUND set has_data = 0; open emp_result; repeat fetch emp_result into id , name , age , salary; select concat('id為',id, ', name 為' ,name , ', age為 ' ,age , ', 薪水為: ', salary); until has_data = 0 end repeat; close emp_result; end$ DELIMITER ;
?java如何調(diào)用存儲(chǔ)過(guò)程
<mapper namespace="com.aaa.qy160springsecurity03.dao.EmpDao"> <select id="find" parameterType="java.util.Map" resultType="com.aaa.qy160springsecurity03.entity.Emp" statementType="CALLABLE"> { call p7(#{name,jdbcType=VARCHAR ,mode=IN}) } </select> </mapper>
3.7.存儲(chǔ)函數(shù) ---存儲(chǔ)過(guò)程 函數(shù)：函數(shù)有返回值
注意:mysql5.8需要設(shè)置如下內(nèi)容
SET GLOBAL log_bin_trust_function_creators = 1;語(yǔ)法結(jié)構(gòu): public 返回類(lèi)型 函數(shù)名(參數(shù)){}
CREATE FUNCTION function_name([param type ... ]) RETURNS type BEGIN ... END;案例 :
定義一個(gè)存儲(chǔ)函數(shù), 請(qǐng)求滿足條件的總記錄數(shù) ;
delimiter $ create function count_city(countryId int) returns int begin declare cnum int ; select count(*) into cnum from city where country_id = countryId; return cnum; end$ delimiter ;調(diào)用:
select count_city(1); select count_city(2);
4.索引
4.1.索引概述
MySQL官方對(duì)索引的定義為：索引（index）是幫助MySQL高效獲取數(shù)據(jù)的數(shù)據(jù)結(jié)構(gòu)（有序）。在數(shù)據(jù)之外，數(shù)據(jù)庫(kù)系統(tǒng)還維護(hù)著滿足特定查找算法的數(shù)據(jù)結(jié)構(gòu)，這些數(shù)據(jù)結(jié)構(gòu)以某種方式引用（指向）數(shù)據(jù)， 這樣就可以在這些數(shù)據(jù)結(jié)構(gòu)上實(shí)現(xiàn)高效查找算法，這種數(shù)據(jù)結(jié)構(gòu)就是索引。如下面的示意圖所示 :
索引就是一種數(shù)據(jù)結(jié)構(gòu)B+Tree，為了提高數(shù)據(jù)庫(kù)查詢數(shù)據(jù)的效率。
左邊是數(shù)據(jù)表，一共有三列10條記錄，最左邊的是數(shù)據(jù)記錄的物理地址（注意邏輯上相鄰的記錄在磁盤(pán)上也并不是一定物理相鄰的）。為了加快age的查找，可以維護(hù)一個(gè)右邊所示的二叉查找樹(shù)，每個(gè)節(jié)點(diǎn)分別包含索引鍵值和一個(gè)指向?qū)?yīng)數(shù)據(jù)記錄物理地址的指針，這樣就可以運(yùn)用二叉查找快速獲取到相應(yīng)數(shù)據(jù)。
一般來(lái)說(shuō)索引本身也很大，不可能全部存儲(chǔ)在內(nèi)存中，因此索引往往以索引文件的形式存儲(chǔ)在磁盤(pán)上。索引是數(shù)據(jù)庫(kù)中用來(lái)提高性能的最常用的手段。
4.2.索引優(yōu)勢(shì)劣勢(shì)
4.3.索引結(jié)構(gòu)
索引是在MySQL的存儲(chǔ)引擎層中實(shí)現(xiàn)的，而不是在服務(wù)層實(shí)現(xiàn)的。所以每種存儲(chǔ)引擎的索引都不一定完全相同，也不是所有的存儲(chǔ)引擎都支持所有的索引類(lèi)型的。MySQL目前提供了以下4種索引結(jié)構(gòu)算法：
? ? ? ? ? ? ? ? ? MyISAM、InnoDB、Memory三種存儲(chǔ)引擎對(duì)各種索引類(lèi)型的支持
B+TREE 索引 ： 最常見(jiàn)的索引類(lèi)型，大部分索引都支持 B 樹(shù)索引。
HASH 索引：只有Memory引擎支持 ， 使用場(chǎng)景簡(jiǎn)單 。
R-tree 索引（空間索引）：空間索引是MyISAM引擎的一個(gè)特殊索引類(lèi)型，主要用于地理空間數(shù)據(jù)類(lèi)型，通常使用較少，不做特別介紹。
Full-text （全文索引 倒排索引--ES） ：全文索引也是MyISAM的一個(gè)特殊索引類(lèi)型，主要用于全文索引，InnoDB從Mysql5.6版本開(kāi)始支持全文索引。
索引 NNODB引擎 MYISAM引擎 MEMORY引擎 B+TREE索引 支持 支持 支持 HASH 索引 不支持 不支持 支持 R-tree 索引 不支持 支持 不支持 Full-text 5.6版本之后支持 支持 不支持 我們平常所說(shuō)的索引，如果沒(méi)有特別指明，都是指B+樹(shù)（多路搜索樹(shù)，并不一定是二叉的）結(jié)構(gòu)組織的索引。其中聚集索引、復(fù)合索引、前綴索引、唯一索引默認(rèn)都是使用 B+tree 索引結(jié)構(gòu)，統(tǒng)稱為 索引。
4.3.1.二叉樹(shù)
4.3.2.B-TREE 結(jié)構(gòu)
動(dòng)態(tài)演示
樹(shù)演示地址
什么是B-樹(shù)
4.3.3.B+Tree結(jié)構(gòu)-->Mysql
?什么是B+樹(shù)
4.3.4.數(shù)據(jù)庫(kù)中B+tree的結(jié)構(gòu)
BTree: n階 存放n-1個(gè)元素，可以有n個(gè)孩子。 每個(gè)元素中掛載相應(yīng)的數(shù)據(jù)。
B+Tree: n階 存放n-1個(gè)元素，可以有n個(gè)孩子。 所有元素都在葉子節(jié)點(diǎn)，而且葉子節(jié)點(diǎn)是一個(gè)閉環(huán)雙向鏈表
4.4.索引分類(lèi)
例如:
思考：
?
id查詢更快，根據(jù)id可以直接查詢到所有數(shù)據(jù)；根據(jù)name還需要回表查詢id
4.5.索引的語(yǔ)法?
例子:
SET NAMES utf8mb4; SET FOREIGN_KEY_CHECKS = 0; -- ---------------------------- -- Table structure for student -- ---------------------------- DROP TABLE IF EXISTS `student`; CREATE TABLE `student` ( `id` int(11) NOT NULL, `name` varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL, `phone` varchar(11) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL, `email` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL, `profession` varchar(30) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL, `age` int(11) NULL DEFAULT NULL, `gender` tinyint(4) NULL DEFAULT NULL, `status` tinyint(4) NULL DEFAULT NULL, `createtime` datetime(0) NULL DEFAULT NULL, PRIMARY KEY (`id`) USING BTREE ) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic; -- ---------------------------- -- Records of student -- ---------------------------- INSERT INTO `student` VALUES (1, '呂布', '17799990000', 'lvbu666@163.com', '軟件工程', 23, 1, 6, '2001-02-02 00:00:00'); INSERT INTO `student` VALUES (2, '曹操', '17799990001', 'caocao666@qq.com', '通信工程', 33, 1, 0, '2001-03-05 00:00:00'); INSERT INTO `student` VALUES (3, '趙云', '17799990002', '17799990@139.com', '英語(yǔ)', 34, 1, 2, '2002-03-02 00:00:00'); INSERT INTO `student` VALUES (4, '孫悟空', '17799990003', '17799990@sina.com', '工程造價(jià)', 54, 1, 0, '2001-07-02 00:00:00'); INSERT INTO `student` VALUES (5, '花木蘭', '17799990004', '199980729@sina.com', '軟件工程', 23, 2, 1, '2001-04-22 00:00:00'); INSERT INTO `student` VALUES (6, '大喬', '17799990005', 'daqiao666@sina.com', '舞蹈', 22, 2, 0, '2001-02-07 00:00:00'); INSERT INTO `student` VALUES (7, '露娜', '17799990006', 'luna_love@sina.com', '應(yīng)用數(shù)學(xué)', 24, 2, 0, '2001-02-08 00:00:00'); INSERT INTO `student` VALUES (8, '程咬金', '17799990007', 'chengyaojin@163.com', '化工', 38, 1, 5, '2001-05-23 00:00:00'); INSERT INTO `student` VALUES (9, '項(xiàng)羽', '17799990008', 'xiaoyu666@qq.com', '金屬材料', 43, 1, 0, '2022-08-25 17:00:45'); INSERT INTO `student` VALUES (10, '白起', '17799990009', 'baiqi666@sina.com', '機(jī)械工程及其自動(dòng)化', 27, 1, 2, '2022-08-25 17:23:51'); INSERT INTO `student` VALUES (11, '韓信', '17799990010', 'hanxin520@163.com', '無(wú)機(jī)非金屬材料工程', 27, 1, 0, '2022-08-25 17:23:51'); INSERT INTO `student` VALUES (12, '荊軻', '17799990011', 'jinke123@163.com', '會(huì)計(jì)', 29, 1, 0, '2022-08-25 17:23:51'); INSERT INTO `student` VALUES (13, '蘭陵王', '17799990012', 'lanlinwang666@126.com', '工程造價(jià)', 44, 1, 1, '2022-08-25 17:23:51'); INSERT INTO `student` VALUES (14, '狂鐵', '17799990013', 'kuangtie@sina.com', '應(yīng)用數(shù)學(xué)', 43, 1, 2, '2022-08-25 17:23:51'); INSERT INTO `student` VALUES (15, '貂蟬', '17799990014', '84958948374@qq.com', '軟件工程', 40, 2, 3, '2022-08-25 17:23:51'); INSERT INTO `student` VALUES (16, '妲己', '17799990015', '2783238293@qq.com', '軟件工程', 31, 2, 0, '2022-08-25 17:23:51'); INSERT INTO `student` VALUES (17, '羋月', '17799990016', 'xiaomin2001@sina.com', '工業(yè)經(jīng)濟(jì)', 35, 2, 0, '2022-08-25 17:23:51'); INSERT INTO `student` VALUES (18, '嬴政', '17799990017', '8839434342@qq.com', '化工', 38, 1, 1, '2022-08-25 17:23:51'); INSERT INTO `student` VALUES (19, '狄仁杰', '17799990018', 'jujiamlm8166@163.com', '國(guó)際貿(mào)易', 30, 1, 0, '2022-08-25 17:23:51'); INSERT INTO `student` VALUES (20, '安琪拉', '17799990019', 'jdodmlh@126.com', '城市規(guī)劃', 51, 2, 0, '2022-08-25 17:23:51'); INSERT INTO `student` VALUES (21, '典韋', '17799990020', 'ycaunanjian@163.com', '城市規(guī)劃', 52, 1, 2, '2022-08-25 17:23:51'); INSERT INTO `student` VALUES (22, '廉頗', '17799990021', 'lianpo321@126.com', '土木工程', 19, 1, 3, '2022-08-25 17:23:51'); INSERT INTO `student` VALUES (23, '后裔', '17799990022', 'altycj2000@139.com', '城市園林', 20, 1, 0, '2022-08-25 17:23:51'); INSERT INTO `student` VALUES (24, '姜子牙', '17799990023', '37483844@qq.com', '工程造價(jià)', 29, 1, 4, '2022-08-25 17:23:51'); SET FOREIGN_KEY_CHECKS = 1;?需求:
-- 1. name字段為姓名字段，該字段的值可能會(huì)重復(fù)，為該字段創(chuàng)建索引。 CREATE INDEX name_index on student(name); -- 單值索引【普通索引】 -- 2. phone手機(jī)號(hào)字段的值，是非空，且唯一的，為該字段創(chuàng)建唯一索引。 CREATE UNIQUE INDEX phone_index on student(phone); -- 唯一索引 -- 3. 為profession、age、status創(chuàng)建聯(lián)合索引。 CREATE INDEX pro_age_status_index on student(profession,age,status); -- 復(fù)合索引【組合索引】 -- 4. 為email建立合適的索引來(lái)提升查詢效率。 CREATE INDEX email_index on student(email); -- 普通索引SQL優(yōu)化：為相應(yīng)的列添加索引
4.6.性能分析
Windows---》my.ini中修改
查看慢查詢?nèi)罩臼欠耖_(kāi)啟
show VARIABLES like 'slow_quert_log'
查詢慢查詢?nèi)罩揪涂梢灾滥菞lsql語(yǔ)句執(zhí)行速度慢
環(huán)境準(zhǔn)備
CREATE TABLE `t_role` ( `id` varchar(32) NOT NULL, `role_name` varchar(255) DEFAULT NULL, `role_code` varchar(255) DEFAULT NULL, `description` varchar(255) DEFAULT NULL, PRIMARY KEY (`id`), UNIQUE KEY `unique_role_name` (`role_name`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; CREATE TABLE `t_user` ( `id` varchar(32) NOT NULL, `username` varchar(45) NOT NULL, `password` varchar(96) NOT NULL, `name` varchar(45) NOT NULL, PRIMARY KEY (`id`), UNIQUE KEY `unique_user_username` (`username`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; CREATE TABLE `user_role` ( `id` int(11) NOT NULL auto_increment , `user_id` varchar(32) DEFAULT NULL, `role_id` varchar(32) DEFAULT NULL, PRIMARY KEY (`id`), KEY `fk_ur_user_id` (`user_id`), KEY `fk_ur_role_id` (`role_id`), CONSTRAINT `fk_ur_role_id` FOREIGN KEY (`role_id`) REFERENCES `t_role` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION, CONSTRAINT `fk_ur_user_id` FOREIGN KEY (`user_id`) REFERENCES `t_user` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION ) ENGINE=InnoDB DEFAULT CHARSET=utf8; insert into `t_user` (`id`, `username`, `password`, `name`) values('1','super','$2a$10$TJ4TmCdK.X4wv/tCqHW14.w70U3CC33CeVncD3SLmyMXMknstqKRe','超級(jí)管理員'); insert into `t_user` (`id`, `username`, `password`, `name`) values('2','admin','$2a$10$TJ4TmCdK.X4wv/tCqHW14.w70U3CC33CeVncD3SLmyMXMknstqKRe','系統(tǒng)管理員'); insert into `t_user` (`id`, `username`, `password`, `name`) values('3','ykq','$2a$10$8qmaHgUFUAmPR5pOuWhYWOr291WJYjHelUlYn07k5ELF8ZCrW0Cui','test02'); insert into `t_user` (`id`, `username`, `password`, `name`) values('4','stu1','$2a$10$pLtt2KDAFpwTWLjNsmTEi.oU1yOZyIn9XkziK/y/spH5rftCpUMZa','學(xué)生1'); insert into `t_user` (`id`, `username`, `password`, `name`) values('5','stu2','$2a$10$nxPKkYSez7uz2YQYUnwhR.z57km3yqKn3Hr/p1FR6ZKgc18u.Tvqm','學(xué)生2'); insert into `t_user` (`id`, `username`, `password`, `name`) values('6','t1','$2a$10$TJ4TmCdK.X4wv/tCqHW14.w70U3CC33CeVncD3SLmyMXMknstqKRe','老師1'); INSERT INTO `t_role` (`id`, `role_name`, `role_code`, `description`) VALUES('5','學(xué)生','student','學(xué)生'); INSERT INTO `t_role` (`id`, `role_name`, `role_code`, `description`) VALUES('7','老師','teacher','老師'); INSERT INTO `t_role` (`id`, `role_name`, `role_code`, `description`) VALUES('8','教學(xué)管理員','teachmanager','教學(xué)管理員'); INSERT INTO `t_role` (`id`, `role_name`, `role_code`, `description`) VALUES('9','管理員','admin','管理員'); INSERT INTO `t_role` (`id`, `role_name`, `role_code`, `description`) VALUES('10','超級(jí)管理員','super','超級(jí)管理員'); INSERT INTO user_role(id,user_id,role_id) VALUES(NULL, '1', '5'),(NULL, '1', '7'),(NULL, '2', '8'),(NULL, '3', '9'),(NULL, '4', '8'),(NULL, '5', '10') ;
?一般來(lái)說(shuō)， 我們需要保證查詢至少達(dá)到 range 級(jí)別， 最好達(dá)到ref 。
?
4.7.索引使用規(guī)則
注意: 根據(jù)id查詢?yōu)槭裁礇](méi)有執(zhí)行覆蓋索引。
如何避免索引失效---措施。
聯(lián)合索引要遵循最左前綴法則
不要再索引列上進(jìn)行運(yùn)算
模糊查詢時(shí)盡量右匹配
不要select *
or連接時(shí)如果右側(cè)沒(méi)有索引會(huì)導(dǎo)致所有的索引失效文章來(lái)源：http://www.zghlxwxcb.cn/news/detail-645020.html
字符串類(lèi)型的索引一定要加''號(hào)文章來(lái)源地址http://www.zghlxwxcb.cn/news/detail-645020.html
到了這里，關(guān)于MySQL高級(jí)-存儲(chǔ)引擎+存儲(chǔ)過(guò)程+索引(詳解01)的文章就介紹完了。如果您還想了解更多內(nèi)容，請(qǐng)?jiān)谟疑辖撬阉鱐OY模板網(wǎng)以前的文章或繼續(xù)瀏覽下面的相關(guān)文章，希望大家以后多多支持TOY模板網(wǎng)！
