MySQL教學欄位透過47張圖帶你了解MySQL進階。
我們在MySQL 入門文章主要介紹了基本的SQL 指令、資料型別和函數,局部以上知識後,你就可以進行MySQL 的開發工作了,但如果要成為合格的開發人員,你還要具備一些更高階的技能,以下我們就來探討一下MySQL 都需要哪些高階的技能
資料庫最核心的一點就是用來儲存數據,資料儲存就避免不了和磁碟打交道。那麼資料以哪種方式進行存儲,如何儲存是儲存的關鍵所在。所以儲存引擎就相當於資料儲存的發動機,來驅動資料在磁碟層面進行儲存。
MySQL 的架構可以依照三層模式來理解
#儲存引擎也是MySQL 的組建,它是一種軟體,它所能做的和支援的功能主要有
MySQL 預設支援多種儲存引擎,來適用不同資料庫應用,使用者可以根據需要選擇適當的儲存引擎,以下是MySQL 支援的儲存引擎
預設情況下,如果建立表格不指定儲存引擎,會使用預設的儲存引擎,如果要修改預設的儲存引擎,那麼就可以在參數檔中設定default-table-type
,能夠查看目前的儲存引擎
show variables like 'table_type';复制代码
奇怪,為什麼沒有了?網路求證一下,在5.5.3 取消了這個參數
可以透過下面兩種方法查詢目前資料庫支援的儲存引擎
show engines \g复制代码
在建立新表的時候,可以透過增加ENGINE
關鍵字來設定新建表的儲存引擎。
create table cxuan002(id int(10),name varchar(20)) engine = MyISAM;复制代码
上圖我們指定了MyISAM
的儲存引擎。
如果你不知道表的儲存引擎怎麼辦?你可以透過show create table
來查看
如果沒有指定儲存引擎的話,從MySQL 5.1 版本之後,MySQL 的預設內建儲存引擎已經是InnoDB了。建立一張表格看一下
如上圖所示,我們沒有指定預設的儲存引擎,下面查看一下表格
#可以看到,預設的儲存引擎是InnoDB
。
如果你的儲存引擎想要更換,可以使用
alter table cxuan003 engine = myisam;复制代码
來更換,更換完成後回顯示0 rows affected,但其實已經操作成功
#我們使用show create table
檢視一下表格的sql 就知道
在5.1 版本之前,MyISAM 是MySQL 的預設儲存引擎,MyISAM 並發性比較差,使用的場景比較少,主要特點是
#不支援事務
操作,ACID 的特性也就不存在了,這項設計是為了效能和效率考慮的。
不支援外鍵
操作,如果強行增加外鍵,MySQL 不會報錯,只不過外鍵不起作用。
MyISAM 預設的鎖定粒度是表級鎖定
,所以並發效能比較差,加鎖比較快,鎖定衝突比較少,不太容易發生死鎖的情況。
MyISAM 會在磁碟上儲存三個文件,檔案名稱和表名相同,副檔名分別是.frm(儲存表定義)
、. MYD(MYData,儲存資料)
、MYI(MyIndex,儲存索引)
。這裡要特別注意的是 MyISAM 只快取索引檔
,不快取資料檔。
MyISAM 支援的索引類型有全域索引(Full-Text)
、B-Tree 索引
、R-Tree 索引
Full-Text 索引:它的出現是為了解決針對文字的模糊查詢效率較低的問題。
B-Tree 索引:所有的索引節點都按照平衡樹的資料結構來存儲,所有的索引資料節點都在葉節點
R-Tree索引:它的儲存方式和B-Tree 索引有一些區別,主要設計用於存儲空間和多維資料的字段做索引,目前的MySQL 版本僅支援geometry 類型的字段作索引,相對於BTREE,RTREE 的優勢在於範圍查找。
資料庫所在主機如果當機,MyISAM 的資料檔案容易損壞,而且難以復原。
增刪改查效能方面:SELECT 效能較高,適用於查詢較多的情況
自從MySQL 5.1 之後,預設的儲存引擎變成了InnoDB 儲存引擎,相對於MyISAM,InnoDB 儲存引擎有了較大的改變,它的主要特點是
可重複讀取(repetable-read)
、透過MVCC(並發版本控制)
來實現的。能夠解決髒讀
和不可重複讀取
的問題。行級鎖定
,並發效能比較好,會發生死鎖的情況。.frm檔案儲存表結構
定義,但不同的是,InnoDB 的表資料與索引資料是儲存在一起的,都位於B 數的葉子節點上,而MyISAM 的表資料和索引資料是分開的。MEMORY 儲存引擎使用存在記憶體中的內容來建立表格。每個 MEMORY 表實際上只對應一個磁碟文件,格式是.frm
。 MEMORY 類型的表格存取速度很快,因為其資料存放在記憶體中。預設使用HASH 索引
。
MERGE 儲存引擎是一組MyISAM 表的組合,MERGE 表本身沒有數據,對MERGE 類型的表進行查詢、更新、刪除的操作,實際上是對內部的MyISAM 表進行的。 MERGE 表在磁碟上保留兩個文件,一個是.frm
檔案儲存表定義、一個是.MRG
檔案儲存 MERGE 表的組成等。
在實際開發過程中,我們傾向於根據應用功能選擇合適的儲存引擎。
我們會經常遇見的一個問題是,在建表時如何選擇合適的資料類型,通常選擇合適的資料類型能夠提高效能、減少不必要的麻煩,下面我們就來一起探討一下,如何選擇合適的資料類型。
char 和varchar 是我們經常要用到的兩個儲存字串的資料類型,char 一般儲存定長的字串,它屬於固定長度的字元類型,例如下面
值 | char(5) | 儲存位元組 |
---|---|---|
'' | ' ' | 5個位元組 |
'cx' | ##' cx '5個位元組 | |
'cxuan' | 5個位元組 | |
'cxuan' | #5個位元組 |
嚴格模式的話,上面表格最後一行是可以儲存的。如果 MySQL 使用了
如果使用了varchar 字元類型,我們來看看範例嚴格模式
的話,那麼表格上面最後一行儲存會報錯。
可以看到,如果使用 varchar 的話,那麼儲存的位元組將根據實際的值進行儲存。你可能會懷疑為什麼varchar 的長度是5 ,但是卻需要儲存3 個位元組或6 個位元組,這是因為使用varchar 資料類型進行儲存時,預設會在最後增加一個字串長度,佔用1個字節(如果列聲明的長度超過255,則使用兩個位元組)。 varchar 不會填入空餘的字串。
一般使用 char 來儲存定長的字串,例如身分證號、手機號碼、信箱等;使用 varchar 來儲存不定長的字串。由於char 長度是固定的,所以它的處理速度要比VARCHAR 快很多,但是缺點是浪費儲存空間,但隨著MySQL 版本的不斷演進,varchar 資料類型的效能也在不斷改進和提高,所以在許多應用中,VARCHAR 類型更多的被使用。
在MySQL 中,不同的儲存引擎對CHAR 和VARCHAR 的使用原則也有不同
一般在保存較少的文字的時候,我們會選擇CHAR 和VARCHAR,在保存大數據量的文字時,我們往往選擇TEXT 和BLOB;TEXT 和BLOB 的主要差別是BLOB 能夠保存二進位資料
;而TEXT 只能保存字元資料
,TEXT 往下細分有
TEXT 和BLOB 在刪除資料後會存在一些效能上的問題,為了提高效能,建議使用
功能對資料表進行碎片整理。也可以使用合成索引來提高文字欄位(BLOB 和 TEXT)的查詢效能。合成索引就是根據大文本(BLOB 和 TEXT)欄位的內容建立一個雜湊值,把這個值存在對應列中,這樣就能夠根據雜湊值查找到對應的資料行。一般使用雜湊演算法例如md5() 和SHA1() ,如果雜湊演算法產生的字串帶有尾部空格,就不要把它們存在CHAR 和VARCHAR 中,下面我們就來看看這個使用方式
首先建立一張表,表中記錄blob 欄位和hash 值
其他優化BLOB 和TEXT 的方式:
和double
,定點數指的是decimal
,定點數能夠更精確的保存和顯示資料。下面透過一個範例來講解一下浮點數精確性問題先建立一個表格cxuan006 ,只為了測試浮點數問題,所以這裡我們選擇的資料型別是float
然後執行查詢,可以看到查詢出來的兩個資料執行的捨入不同
為了清楚的看清楚浮點數與定點數的精確度問題,再來看一個例子
先修改cxuan006 的兩個欄位為相同的長度和小數位數
然後插入兩個資料
#執行查詢操作,可以發現,浮點數相較於定點數來說,會產生誤差
在MySQL 中,用來表示日期類型的有DATE、TIME、DATETIME、TIMESTAMP,在
138 張圖帶你MySQL 入門
這篇文中介紹過了日期類型的區別,我們這裡就不再闡述了。以下主要介紹選擇
下面來認識 MySQL 字元集,簡單來說字元集就是一套文字符號和編碼、比較規則的集合。 1960 年美國標準化組織 ANSI 發布了第一個電腦字元集,就是著名的ASCII(American Standard Code for Information Interchange)
。自從 ASCII 編碼後,每個國家、國際組織都研究了一套自己的字元集,例如ISO-8859-1
、GBK
等。
但是每個國家都使用自己的字元集為移植性帶來了很大的困難。所以,為了統一字元編碼,國際標準化組織(ISO)
指定了統一的字元標準 - Unicode 編碼,它容納了幾乎所有的字元編碼。以下是一些常見的字元編碼
#varchar(5) | 儲存位元組 | |
---|---|---|
'' | 1個位元組 | |
'cx ' | 3個位元組 | |
6個位元組 | 'cxuan007' | |
6個位元組 | BLOBOPTIMIZE TABLE非必要的時候不要檢索BLOB 和TEXT 索引float |
对数据库来说,字符集是很重要的,因为数据库存储的数据大多数都是各种文字,字符集对数据库的存储、性能、系统的移植来说都非常重要。
MySQL 支持多种字符集,可以使用show character set;
来查看所有可用的字符集
或者使用
select character_set_name, default_collate_name, description, maxlen from information_schema.character_sets;复制代码
来查看。
使用information_schema.character_set
来查看字符集和校对规则。
我们上面介绍到了索引的几种类型并对不同的索引类型做了阐述,阐明了优缺点等等,下面我们从设计角度来聊一下索引,关于索引,你必须要知道的一点就是:索引是数据库用来提高性能的最常用工具。
所有的 MySQL 类型都可以进行索引,对相关列使用索引是提高SELECT
查询性能的最佳途径。MyISAM 和 InnoDB 都是使用BTREE
作为索引,MySQL 5 不支持函数索引
,但是支持前缀索引
。
前缀索引顾名思义就是对列字段的前缀做索引,前缀索引的长度和存储引擎有关系。MyISAM 前缀索引的长度支持到 1000 字节,InnoDB 前缀索引的长度支持到 767 字节,索引值重复性越低,查询效率也就越高。
在 MySQL 中,主要有下面这几种索引
全局索引(FULLTEXT)
:全局索引,目前只有 MyISAM 引擎支持全局索引,它的出现是为了解决针对文本的模糊查询效率较低的问题,并且只限于 CHAR、VARCHAR 和 TEXT 列。哈希索引(HASH)
:哈希索引是 MySQL 中用到的唯一 key-value 键值对的数据结构,很适合作为索引。HASH 索引具有一次定位的好处,不需要像树那样逐个节点查找,但是这种查找适合应用于查找单个键的情况,对于范围查找,HASH 索引的性能就会很低。默认情况下,MEMORY 存储引擎使用 HASH 索引,但也支持 BTREE 索引。B-Tree 索引
:B 就是 Balance 的意思,BTree 是一种平衡树,它有很多变种,最常见的就是 B+ Tree,它被 MySQL 广泛使用。R-Tree 索引
:R-Tree 在 MySQL 很少使用,仅支持 geometry 数据类型,支持该类型的存储引擎只有MyISAM、BDb、InnoDb、NDb、Archive几种,相对于 B-Tree 来说,R-Tree 的优势在于范围查找。索引可以在创建表的时候进行创建,也可以单独创建,下面我们采用单独创建的方式,我们在 cxuan004 上创建前缀索引
我们使用explain
进行分析,可以看到 cxuan004 使用索引的情况
如果不想使用索引,可以删除索引,索引的删除语法是
创建索引的时候,要尽量考虑以下原则,便于提升索引的使用效率。
索引位置
,选择索引最合适的位置是出现在where
语句中的列,而不是select
关键字后的选择列表中的列。唯一索引
,顾名思义,唯一索引的值是唯一的,可以更快速的确定某条记录,例如学生的学号就适合使用唯一性索引,而学生的性别则不适合使用,因为不管搜索哪个值,都差不多有一半的行。前缀索引
,如果索引的值很长,那么查询速度会受到影响,这个时候应该使用前缀索引,对列的某几个字符进行索引,可以提高检索效率。MySQL 从 5.0 开始就提供了视图功能,下面我们对视图功能进行介绍。
视图的英文名称是view
,它是一种虚拟存在的表。视图对于用户来说是透明的,它并不在数据库中实际存在,视图是使用数据库行和列动态组成的表,那么视图相对于数据库表来说,优势体现在哪里?
视图相对于普通的表来说,优势包含下面这几项
视图的操作包括创建或者修改视图、删除视图以及查看视图定义。
使用create view
来创建视图
为了演示功能,我们先创建一张表product
表,有三个字段,id,name,price,下面是建表语句
create table product(id int(11),name varchar(20),price float(10,2));复制代码
然后我们向其中插入几条数据
insert into product values(1, "apple","3.5"),(2,"banana","4.2"),(3,"melon","1.2");复制代码
插入完成后的表结构如下
然后我们创建视图
create view v1 as select * from product;复制代码
然后我们查看一下 v1 视图的结构
可以看到我们把 product 中的数据放在了视图中,也相当于是创建了一个 product 的副本,只不过这个副本跟表无关。
视图使用
show tables;复制代码
也能看到所有的视图。
删除视图的语法是
drop view v1;复制代码
能够直接进行删除。
视图还有其他操作,比如查询操作
你还可以使用
describe v1;复制代码
查看表结构
更新视图
update v1 set name = "grape" where id = 1;复制代码
MySQL 从 5.0 开始起就支持存储过程和函数了。
那么什么是存储过程呢?
存储过程是在数据库系统中完成一组特定功能的 SQL 语句集,它存储在数据库系统中,一次编译后永久有效。那么使用存储过程有什么优点呢?
使用存储过程有什么缺点?
在认识到存储过程是什么之后,我们就来使用一下存储过程,这里需要先了解一个小技巧,也就是delimiter
的用法,delimiter 用于自定义结束符,什么意思呢,如果你使用
delimiter ?复制代码
的话,那么你在 sql 语句末使用;
是不能使 SQL 语句执行的,不信?我们可以看下
可以看到,我们在 SQL 语句的行末使用了;
但是我们却没有看到执行结果。下面我们使用
delimiter ;复制代码
恢复默认的执行条件再来看下
我们创建存储过程首先要把;
替换为?
,下面是一个存储过程的创建语句
mysql> delimiter ? mysql> create procedure sp_product() -> begin -> select * from product; -> end ?复制代码
存储过程实际上是一种函数,所以创建完毕后,我们可以使用call
方法来调用这个存储过程
因为我们上面定义了使用 delimiter ? 来结尾,所以这里也应该使用。
存储过程也可以接受参数,比如我们定义一种接收参数的情况
然后我们使用call
调用这个存储过程
可以看到,当我们调用 id = 2 的时候,存储过程的 SQL 语句相当于是
select * from product where id = 2;复制代码
所以只查询出 id = 2 的结果。
一次只能删除一个存储过程,删除存储过程的语法如下
drop procedure sp_product ;复制代码
直接使用 sp_product 就可以了,不用加()
。
存储过程创建后,用户可能需要需要查看存储过程的状态等信息,便于了解存储过程的基本情况
我们可以使用
show create procedure proc_name;复制代码
在 MySQL 中,变量可分为两大类,即系统变量
和用户变量
,这是一种粗略的分法。但是根据实际应用又被细化为四种类型,即局部变量、用户变量、会话变量和全局变量。
用户变量是基于会话变量
实现的,可以暂存,用户变量与连接有关,也就是说一个客户端定义的变量不能被其他客户端使用看到。当客户端退出时,链接会自动释放。我们可以使用set
语句设置一个变量
set @myId = "cxuan";复制代码
然后使用select
查询条件可以查询出我们刚刚设置的用户变量
用户变量是和客户端有关系,当我们退出后,这个变量会自动消失,现在我们退出客户端
exit复制代码
现在我们重新登陆客户端,再次使用select
条件查询
发现已经没有这个@myId
了。
MySQL 中的局部变量与 Java 很类似 ,Java 中的局部变量是 Java 所在的方法或者代码块,而 MySQL 中的局部变量作用域是所在的存储过程。MySQL 局部变量使用declare
来声明。
服务器会为每个连接的客户端维护一个会话变量。可以使用
show session variables;复制代码
显示所有的会话变量。
我们可以手动设置会话变量
set session auto_increment_increment=1; 或者使用 set @@session.auto_increment_increment=2;复制代码
然后进行查询,查询会话变量使用
或者使用
当服务启动时,它将所有全局变量初始化为默认值。其作用域为 server 的整个生命周期。
可以使用
show global variables;复制代码
查看全局变量
可以使用下面这两种方式设置全局变量
set global sql_warnings=ON; -- global不能省略 /** 或者 **/ set @@global.sql_warnings=OFF;复制代码
查询全局变量时,可以使用
或者是
MySQL 支持下面这些控制语句
IF 用于实现逻辑判断,满足不同条件执行不同的 SQL 语句
IF ... THEN ...复制代码
CASE 实现比 IF 稍微复杂,语法如下
CASE ... WHEN ... THEN... ... END CASE复制代码
CASE 语句也可以使用 IF 来完成
LOOP 用于实现简单的循环
label:LOOP ... END LOOP label;复制代码
如果...
中不写 SQL 语句的话,那么就是一个简单的死循环语句
用来表示从标注的流程构造中退出,通常和 BEGIN...END 或者循环一起使用
ITERATE 语句必须用在循环中,作用是跳过当前循环的剩下的语句,直接进入下一轮循环。
带有条件的循环控制语句,当满足条件的时候退出循环。
REPEAT ... UNTIL END REPEAT;复制代码
WHILE 语句表示的含义和 REPEAT 相差无几,WHILE 循环和 REPEAT 循环的区别在于:WHILE 是满足条件才执行循环,REPEAT 是满足条件退出循环;
MySQL 从 5.0 开始支持触发器
,触发器一般作用在表上,在满足定义条件时触发,并执行触发器中定义的语句集合,下面我们就来一起认识一下触发器。
举个例子来认识一下触发器:比如你有一个日志表和金额表,你每录入一笔金额就要进行日志表的记录,你会怎么样?同时在金额表和日志表插入数据吗?如果有了触发器,你可以直接在金额表录入数据,日志表会自动插入一条日志记录,当然,触发器不仅只有新增操作,还有更新和删除操作。
我们可以用如下的方式创建触发器
create trigger triggername triggertime triggerevent on tbname for each row triggerstmt复制代码
上面涉及到几个参数,我知道你有点懵逼,解释一下。
triggername
:这个指的就是触发器的名字triggertime
:这个指的就是触发器触发时机,是BEFORE
还是AFTER
triggerevent
: 这个指的就是触发器触发事件,一共有三种事件:INSERT、UPDATE 或者 DELETE。tbname
:这个参数指的是触发器创建的表名,在哪个表上创建triggerstmt
: 触发器的程序体,也就是 SQL 语句所以,可以创建六种触发器
BEFORE INSERT、AFTER INSERT、BEFORE UPDATE、AFTER UPDATE、BEFORE DELETE、AFTER DELETE
上面的for each now
表示任何一条记录上的操作都会触发触发器。
下面我们通过一个例子来演示一下触发器的操作
我们还是用上面的 procuct 表做例子,我们创建一个 product_info 产品信息表。
create table product_info(p_info varchar(20)); 复制代码
然后我们创建一个trigger
我们在 product 表中插入一条数据
insert into product values(4,"pineapple",15.3);复制代码
我们进行 select 查询,可以看到现在 product 表中有四条数据
我们没有向 product_info 表中插入数据,现在我们来看一下 product_info 表中,我们预想到是有数据的,具体来看下
这条数据是什么时候插入的呢?我们在创建触发器tg_pinfo
的时候插入了的这条数据。
触发器可以使用drop
进行删除,具体删除语法如下
drop trigger tg_pinfo;复制代码
和删除表的语法是一样的
我们经常会查看触发器,可以通过执行show triggers
命令查看触发器的状态、语法等信息。
另一种查询方式是查询表中的information_schema.triggers
表,这个可以查询指定触发器的指定信息,操作起来方便很多
注意:触发器的使用有两个限制
- 触发程序不能调用将数据返回客户端的存储程序。也不能使用 CALL 语句的动态 SQL 语句。
- 不能在触发器中开始和结束语句,例如 START TRANSACTION
更多相关免费学习推荐:mysql教程(视频)
字元集 | 是否定長 | 編碼方式 |
---|---|---|
#ASCII | 是 | 單字節7 位元編碼 |
是 | 單字節8 位元編碼 | |
是 | 雙位元組編碼 | |
否 | 1 - 4 位元組編碼 | |
否 | 2 位元組或4 位元組編碼 | |
是 | 4 位元組編碼 |
以上是透過47 張圖帶你 MySQL 進階的詳細內容。更多資訊請關注PHP中文網其他相關文章!