一纵散、MySQL的索引

索引是通過某種算法梳码，構(gòu)建出一個數(shù)據(jù)模型，用于快速找出在某個列中有一特定值的行伍掀，不使用索引掰茶，MySQL必須從第一條記錄開始讀完整個表，直到找出相關(guān)的行蜜笤，表越大濒蒋，查詢數(shù)據(jù)所花費的時間就越多，如果表中查詢的列有一個索引，MySQL能夠快速到達(dá)一個位置去搜索數(shù)據(jù)文件沪伙，而不必查看所有數(shù)據(jù)瓮顽，那么將會節(jié)省很大一部分時間。

索引的優(yōu)點：

• 大大加快數(shù)據(jù)的查詢速度
• 使用分組和排序進(jìn)行數(shù)據(jù)查詢時围橡，可以顯著減少查詢時分組和排序的時間
• 創(chuàng)建唯一索引暖混，能夠保證數(shù)據(jù)庫表中每一行數(shù)據(jù)的唯一性
• 在實現(xiàn)數(shù)據(jù)的參考完整性方面，可以加速表和表之間的連接

索引的缺點

• 創(chuàng)建索引和維護(hù)索引需要消耗時間翁授，并且隨著數(shù)據(jù)量的增加拣播，時間也會增加

• 索引需要占據(jù)磁盤空間

• 對數(shù)據(jù)表中的數(shù)據(jù)進(jìn)行增加，修改收擦，刪除時贮配，索引也要動態(tài)的維護(hù)，降低了維護(hù)的速度

創(chuàng)建索引的原則

• 更新頻繁的列不應(yīng)設(shè)置索引

• 數(shù)據(jù)量小的表不要使用索引

• 重復(fù)數(shù)據(jù)多的字段不應(yīng)設(shè)為索引炬守，一般來說：重復(fù)的數(shù)據(jù)超過百分之15就不該建索引

• 首先應(yīng)該考慮對where 和 order by 涉及的列上建立索引

1.1 索引的分類

索引是存儲引擎用來快速查找記錄的一種數(shù)據(jù)結(jié)構(gòu)牧嫉，按照實現(xiàn)的方式類分，主要有Hash索引和B+Tree索引

圖片.png

圖片.png

按照功能劃分减途，索引劃為以下分類:

• 單列索引：一個索引只包含單個列酣藻，但一個表中可以有多個單列索引;
  • 普通索引：MySQL中基本索引類型，沒有什么限制鳍置，允許在定義索引的列中插入重復(fù)值和空值辽剧，純粹為了查詢數(shù)據(jù)更快一點。
  • 唯一索引：唯一索引與前面的普通索引類似税产，不同的就是：索引列的值必須唯一怕轿，但允許有空值。如果是組合索引辟拷，則列值的組合必須唯一撞羽。它有以下幾種創(chuàng)建方式：
  • 主鍵索引：每張表一般都會有自己的主鍵，當(dāng)我們在創(chuàng)建表時衫冻，MySQL會自動在主鍵列上建立一個索引诀紊，這就是主鍵索引。主鍵是具有唯一性并且不允許為NULL隅俘，所以他是一種特殊的唯一索引邻奠。
• 組合索引：組合索引也叫復(fù)合索引，指的是我們在建立索引的時候使用多個字段为居，例如同時使用身份證和手機號建立索引碌宴，同樣的可以建立為普通索引或者是唯一索引。復(fù)合索引的使用復(fù)合最左原則蒙畴。
• 全文索引：全文索引主要用來查找文本中的關(guān)鍵字贰镣，而不是直接與索引中的值相比較，它更像是一個搜索引擎，基于相似度的查詢八孝，而不是簡單的where語句的參數(shù)匹配董朝。
• 空間索引：空間索引是對空間數(shù)據(jù)類型的字段建立的索引，MYSQL中的空間數(shù)據(jù)類型有4種干跛，分別是GEOMETRY、POINT祟绊、LINESTRING楼入、POLYGON。

1.2 單列索引的操作

1.2.1 普通索引

1.2.1.1 創(chuàng)建索引

-- 方式1-創(chuàng)建表的時候直接指定
create  table student(
    name varchar(20),
    -- 省略部分代碼
    index index_name(name) -- 給name列創(chuàng)建索引
);

-- 方式2-直接創(chuàng)建
create index indexname on tablename(columnname); 

-- 方式3-修改表結(jié)構(gòu)(添加索引)
alter table tablename add index indexname(columnname)

1.2.1.2 查看索引

-- 1牧抽、查看數(shù)據(jù)庫所有索引 
select * from mysql.`innodb_index_stats` a where a.`database_name` = '數(shù)據(jù)庫名'; 

-- 2嘉熊、查看表中所有索引 
select * from mysql.`innodb_index_stats` a where a.`database_name` = '數(shù)據(jù)庫名' and a.table_name like '%表名%'; 

-- 3、查看表中所有索引 
show index from table_name; 

1.2.1.3 刪除索引

-- 方法1
drop index 索引名 on 表名 

-- 方法2
alter table 表名 drop index 索引名 

1.2.2 唯一索引

1.2.2.1 創(chuàng)建索引

-- 方式1-創(chuàng)建表的時候直接指定
create  table student2(
    card_id varchar(20),
    -- 省略部分代碼
    unique index_card_id(card_id) -- 給card_id列創(chuàng)建索引
);

-- 方式2-直接創(chuàng)建
create unique index 索引名 on 表名(列名) 

-- 方式3-修改表結(jié)構(gòu)(添加索引)
alter table 表名 add unique [索引名] (列名)

1.2.2.2 刪除索引

-- 方法1
drop index index_card_id on student2 

-- 方法2
alter table student2 drop index index_phone_num

1.3 組合索引

-- 創(chuàng)建索引的基本語法-- 普通索引
create index indexname on table_name(column1(length),column2(length)); 

-- 操作-刪除索引
 drop index index_phone_name on student; 
 
-- 創(chuàng)建索引的基本語法-- 唯一索引
create  unique index index_phone_name on student(phone_num,name); 

1.4 全文索引

全文索引的關(guān)鍵字是fulltext扬舒，MySQL 中的全文索引阐肤，有兩個變量，最小搜索長度和最大搜索長度讲坎，對于長度小于最小搜索長度和大于最大搜索長度的詞語孕惜，都不會被索引。通俗點就是說晨炕，想對一個詞語使用全文索引搜索衫画，那么這個詞語的長度必須在以上兩個變量的區(qū)間內(nèi)。這兩個的默認(rèn)值可以使用以下命令查看:

show variables like '%ft%';

參數(shù)解釋如下表

操作如下

-- 修改表結(jié)構(gòu)添加全文索引
alter table t_article add fulltext index_content(content)
 
-- 直接添加全文索引
create fulltext index index_content on t_article(content);

使用全文索引使用 match 和 against 關(guān)鍵字瓮栗，格式:

match (col1,col2,...)  against(expr [search_modifier])

1.5 空間索引

• MySQL在5.7之后的版本支持了空間索引削罩，而且支持OpenGIS幾何數(shù)據(jù)模型

• 空間索引是對空間數(shù)據(jù)類型的字段建立的索引，MYSQL中的空間數(shù)據(jù)類型有4種费奸，分別是GEOMETRY弥激、POINT、LINESTRING愿阐、POLYGON微服。

• MYSQL使用SPATIAL關(guān)鍵字進(jìn)行擴展，使得能夠用于創(chuàng)建正規(guī)索引類型的語法創(chuàng)建空間索引换况。

• 創(chuàng)建空間索引的列职辨，必須將其聲明為NOT NULL。

例如：

create table shop_info (
  id  int  primary key auto_increment comment 'id',
  shop_name varchar(64) not null comment '門店名稱',
  geom_point geometry not null comment '經(jīng)緯度',
  -- 空間索引
  spatial key geom_index(geom_point)
);

二戈二、MySQL的存儲引擎

數(shù)據(jù)庫存儲引擎是數(shù)據(jù)庫底層軟件組織舒裤，數(shù)據(jù)庫管理系統(tǒng)（DBMS）使用數(shù)據(jù)引擎進(jìn)行創(chuàng)建、查詢觉吭、更新和刪除數(shù)據(jù)腾供。不同的存儲引擎提供不同的存儲機制、索引技巧、鎖定水平等功能“楸睿現(xiàn)在許多不同的數(shù)據(jù)庫管理系統(tǒng)都支持多種不同的數(shù)據(jù)引擎节值。MySQL的核心就是存儲引擎。用戶可以根據(jù)不同的需求為數(shù)據(jù)表選擇不同的存儲引擎

可以使用 SHOW ENGINES 命令 可以查看Mysql的所有執(zhí)行引擎我們 可以看到默認(rèn)的執(zhí)行引擎是innoDB榜聂，支持事務(wù)搞疗，行級鎖定和外鍵。

圖片.png

• MyISAM：Mysql 5.5之前的默認(rèn)數(shù)據(jù)庫引擎须肆，最為常用匿乃。擁有較高的插入，查詢速度豌汇，但不支持事務(wù)
• InnoDB：事務(wù)型速記的首選引擎幢炸，支持ACID事務(wù)，支持行級鎖定拒贱，MySQL5.5成為默認(rèn)數(shù)據(jù)庫引擎
• Memory： 所有數(shù)據(jù)置于內(nèi)存的存儲引擎节仿，擁有極高的插入觅捆，更新和查詢效率。但是會占用和數(shù)據(jù)量成正比的內(nèi)存空間。并且其內(nèi)容會在MYSQL重新啟動是會丟失放妈。
• Archive ：非常適合存儲大量的獨立的倔撞，作為歷史記錄的數(shù)據(jù)盒犹。因為它們不經(jīng)常被讀取权悟。Archive 擁有高效的插入速度，但其對查詢的支持相對較差
• Federated ：將不同的 MySQL 服務(wù)器聯(lián)合起來陨享，邏輯上組成一個完整的數(shù)據(jù)庫葱淳。非常適合分布式應(yīng)用
• CSV ：邏輯上由逗號分割數(shù)據(jù)的存儲引擎。它會在數(shù)據(jù)庫子目錄里為每個數(shù)據(jù)表創(chuàng)建一個 .csv 文件抛姑。這是一種普通文本文件赞厕，每個數(shù)據(jù)行占用一個文本行。CSV 存儲引擎不支持索引定硝。
• BlackHole： 黑洞引擎皿桑，寫入的任何數(shù)據(jù)都會消失，一般用于記錄 binlog 做復(fù)制的中繼
• ERFORMANCE_SCHEMA存儲引擎該引擎主要用于收集數(shù)據(jù)庫服務(wù)器性能參數(shù)蔬啡。
• Mrg_Myisam Merge存儲引擎诲侮，是一組MyIsam的組合，也就是說箱蟆，他將MyIsam引擎的多個表聚合起來沟绪，但是他的內(nèi)部沒有數(shù)據(jù)，真正的數(shù)據(jù)依然是MyIsam引擎的表中空猜，但是可以直接進(jìn)行查詢绽慈、刪除更新等操作恨旱。

一些引擎用的操作如下：

-- 查詢當(dāng)前數(shù)據(jù)庫支持的存儲引擎：
show engines;
 
-- 查看當(dāng)前的默認(rèn)存儲引擎：
show variables like ‘%storage_engine%’;

-- 查看某個表用了什么引擎(在顯示結(jié)果里參數(shù)engine后面的就表示該表當(dāng)前用的存儲引擎): 
show create table student; 
 
-- 創(chuàng)建新表時指定存儲引擎：
create table(...) engine=MyISAM;
 
-- 修改數(shù)據(jù)庫引擎
alter table student engine = INNODB;
alter table student engine = MyISAM;

三、MySQL的事物

3.1 概述

數(shù)據(jù)庫存儲引擎是數(shù)據(jù)庫底層軟件組織坝疼，數(shù)據(jù)庫管理系統(tǒng)（DBMS）使用數(shù)據(jù)引擎進(jìn)行創(chuàng)建搜贤、查詢、更新和刪除數(shù)據(jù)钝凶。不同的存儲引擎提供不同的存儲機制仪芒、索引技巧、鎖定水平等功能「荩現(xiàn)在許多不同的數(shù)據(jù)庫管理系統(tǒng)都支持多種不同的數(shù)據(jù)引擎桌硫。MySQL的核心就是存儲引擎。用戶可以根據(jù)不同的需求為數(shù)據(jù)表選擇不同的存儲引擎

可以使用 SHOW ENGINES 命令 可以查看Mysql的所有執(zhí)行引擎我們 可以到 默認(rèn)的執(zhí)行引擎是innoDB 支持事務(wù)啃炸，行級鎖定和外鍵。

在MySQL中的事務(wù)（Transaction）是由存儲引擎實現(xiàn)的卓舵，在MySQL中南用，只有InnoDB存儲引擎才支持事務(wù)。事務(wù)處理可以用來維護(hù)數(shù)據(jù)庫的完整性掏湾，保證成批的 SQL 語句要么全部執(zhí)行裹虫，要么全部不執(zhí)行。事務(wù)用來管理 DDL融击、DML筑公、DCL 操作，比如 insert,update,delete 語句尊浪，默認(rèn)是自動提交的匣屡。

3.2 事物的操作

• 開啟事務(wù)：Start Transaction

  • 任何一條DML語句(insert、update拇涤、delete)執(zhí)行捣作，標(biāo)志事務(wù)的開啟
  • 命令：BEGIN 或 START TRANSACTION

• 提交事務(wù)：Commit Transaction

  • 成功的結(jié)束，將所有的DML語句操作歷史記錄和底層硬盤數(shù)據(jù)來一次同步
  • 命令：COMMIT

• 回滾事務(wù)：Rollback Transaction

  • 失敗的結(jié)束鹅士，將所有的DML語句操作歷史記錄全部清空
  • 命令：ROLLBACK

之前的所有SQL操作其實也有事務(wù)券躁，只是MySQL自動幫我們完成的，每執(zhí)行一條SQL時MySQL就幫我們自動提交事務(wù)掉盅，因此如果想要手動控制事務(wù)也拜，則必須關(guān)閉MySQL的事務(wù)自動提交。在MySQL中直接用 SET 來改變 MySQL 的自動提交模式:

select @@autocommit;
set autocommit=0 禁止自動提交 
set autocommit=1 開啟自動提交 

3.3 事物的隔離級別

• 讀未提交(Read uncommitted)：一個事務(wù)可以讀取另一個未提交事務(wù)的數(shù)據(jù)趾痘，最低級別慢哈，任何情況都無法保證,會造成臟讀。
• 讀已提交(Read committed)：一個事務(wù)要等另一個事務(wù)提交后才能讀取數(shù)據(jù)扼脐，可避免臟讀的發(fā)生岸军，會造成不可重復(fù)讀奋刽。
• 可重復(fù)讀(Repeatable read)：就是在開始讀取數(shù)據(jù)（事務(wù)開啟）時，不再允許修改操作艰赞，可避免臟讀佣谐、不可重復(fù)讀的發(fā)生，但是會造成幻讀方妖。
• 串行(Serializable)：是最高的事務(wù)隔離級別狭魂，在該級別下，事務(wù)串行化順序執(zhí)行党觅，可以避免臟讀雌澄、不可重復(fù)讀與幻讀。但是這種事務(wù)隔離級別效率低下杯瞻，比較耗數(shù)據(jù)庫性能镐牺，一般不使用。

Mysql的默認(rèn)隔離級別是Repeatable read魁莉。

-- 查看隔離級別 
show variables like '%isolation%'; 

-- 設(shè)置隔離級別
/*
set session transaction isolation level 級別字符串
級別字符串：read uncommitted睬涧、read committed、repeatable read旗唁、serializable
*/
-- 設(shè)置read uncommitted
set session transaction isolation level read uncommitted;
 
-- 設(shè)置read committed
set session transaction isolation level read committed;
 
-- 設(shè)置repeatable read
set session transaction isolation level repeatable read;
 
-- 設(shè)置serializable
set session transaction isolation level serializable;

事務(wù)A 按照一定條件進(jìn)行數(shù)據(jù)讀取畦浓， 期間事務(wù)B 插入了相同搜索條件的新數(shù)據(jù)，事務(wù)A再次按照原先條件進(jìn)行讀取時检疫，發(fā)現(xiàn)了事務(wù)B 新插入的數(shù)據(jù) 稱為幻讀

如果事務(wù)A 按一定條件搜索讶请， 期間事務(wù)B刪除了符合條件的某一條數(shù)據(jù)，導(dǎo)致事務(wù)A 再次讀取時數(shù)據(jù)少了一條屎媳。這種情況歸為不可重復(fù)讀

四夺溢、MySQL的鎖機制

4.1 概述

鎖是計算機協(xié)調(diào)多個進(jìn)程或線程并發(fā)訪問某一資源的機制（避免爭搶）。在數(shù)據(jù)庫中剿牺，除傳統(tǒng)的計算資源（如 CPU企垦、RAM、I/O 等）的爭用以外晒来，數(shù)據(jù)也是一種供許多用戶共享的資源钞诡。如何保證數(shù)據(jù)并發(fā)訪問的一致性、有效性是所有數(shù)據(jù)庫必須解決的一個問題湃崩，鎖沖突也是影響數(shù)據(jù)庫并發(fā)訪問性能的一個重要因素荧降。從這個角度來說，鎖對數(shù)據(jù)庫而言顯得尤其重要攒读，也更加復(fù)雜朵诫。

鎖有以下幾種分類

1. 從對數(shù)據(jù)操作的粒度分 ：

• 表鎖：操作時，會鎖定整個表薄扁。
• 行鎖：操作時剪返，會鎖定當(dāng)前操作行

2. 從對數(shù)據(jù)操作的類型分：

• 讀鎖（共享鎖）：針對同一份數(shù)據(jù)废累，多個讀操作可以同時進(jìn)行而不會互相影響。
• 寫鎖（排它鎖）：當(dāng)前操作沒有完成之前脱盲，它會阻斷其他寫鎖和讀鎖邑滨。

4.2 MySQL鎖的特點

相對其他數(shù)據(jù)庫而言，MySQL的鎖機制比較簡單钱反，其最顯著的特點是不同的存儲引擎支持不同的鎖機制掖看。

MySQL鎖的特性可大致歸納如下 ：

僅從鎖的角度來說：

• 表級鎖更適合于以查詢?yōu)橹骷独郑挥猩倭堪此饕龡l件更新數(shù)據(jù)的應(yīng)用疙咸，如Web 應(yīng)用；

• 行級鎖則更適合于有大量按索引條件并發(fā)更新少量不同數(shù)據(jù)风科，同時又有并查詢的應(yīng)用撒轮，如一些在線事務(wù)處理（OLTP）系統(tǒng)。

4.3 MyISAM表鎖

MyISAM 在執(zhí)行查詢語句（SELECT）前贼穆，會自動給涉及的所有表加讀鎖题山，在執(zhí)行更新操作（UPDATE、DELETE故痊、INSERT 等）前顶瞳，會自動給涉及的表加寫鎖，這個過程并不需要用戶干預(yù)愕秫，因此慨菱，用戶一般不需要直接用 LOCK TABLE 命令給 MyISAM 表顯式加鎖

-- 加讀鎖 ： 
lock table table_name read; 
-- 加寫鎖 ： 
lock table table_name write；

表鎖特點：

• 對MyISAM 表的讀操作戴甩，不會阻塞其他用戶對同一表的讀請求符喝，但會阻塞對同一表的寫請求；
• 對MyISAM 表的寫操作甜孤，則會阻塞其他用戶對同一表的讀和寫操作协饲；

此外畏腕，MyISAM 的讀寫鎖調(diào)度是寫優(yōu)先，這也是MyISAM不適合做寫為主的表的存儲引擎的原因茉稠。因為寫鎖后描馅，其他線程不能做任何操作，大量的更新會使查詢很難得到鎖战惊，從而造成永遠(yuǎn)阻塞流昏。

4.4 InnoDB行鎖

InnoDB 與 MyISAM 的最大不同有兩點：一是支持事務(wù)；二是 采用了行級鎖吞获。InnoDB 實現(xiàn)了以下兩種類型的行鎖：

• 共享鎖（S）：又稱為讀鎖况凉，簡稱S鎖，共享鎖就是多個事務(wù)對于同一數(shù)據(jù)可以共享一把鎖各拷，都能訪問到數(shù)據(jù)刁绒，但是只能讀不能修改。
• 排他鎖（X）：又稱為寫鎖烤黍，簡稱X鎖知市，排他鎖就是不能與其他鎖并存，如一個事務(wù)獲取了一個數(shù)據(jù)行的排他鎖速蕊，其他事務(wù)就不能再獲取該行的其他鎖嫂丙，包括共享鎖和排他鎖，但是獲取排他鎖的事務(wù)是可以對數(shù)據(jù)就行讀取和修改规哲。

對于UPDATE跟啤、DELETE和INSERT語句，InnoDB會自動給涉及數(shù)據(jù)集加排他鎖（X)唉锌；而對于普通SELECT語句隅肥，InnoDB不會加任何鎖；可以通過以下語句顯示給記錄集加共享鎖或排他鎖 袄简。

-- 共享鎖（S）：
SELECT * FROM table_name WHERE ... LOCK IN SHARE MODE 
-- 排他鎖（X) ：
SELECT * FROM table_name WHERE ... FOR UPDATE