原創 MySQL的索引與事務、存儲引擎

索引的概念

數據庫中的索引與書籍中的目錄類似

在一本書中，無須閱讀整本書，利用目錄就可以快速查找所需信息
書中的目錄是一個詞語列表，其中注明了包含各個詞的頁碼

數據庫索引

在數據庫中，索引數據庫程序無須對整個表進行掃描，就可以在其中找到所需數據
數據庫中的索引是某個表中一列或者若干列值的集合，以及物理標識這些值的數據頁的邏輯指針清單

索引的作用

設置了合適的索引之后，數據庫利用各種快速的定位技術，能夠大大加快查詢速率；
特別是當表很大時，或者查詢涉及到多個表時，使用索引可使查詢加快成千倍；
可以降低數據庫的IO成本，并且索引還可以降低數據庫的排序成本；
通過創建唯一性索引保證數據表數據的唯一性；
可以加快表與表之間的連接；
在使用分組和排序時，可大大減少分組和排序時間；

索引的分類

普通索引

這種索引和前面的"普通索引"基本相同，但有一個區別：索引列的所有值都只能出現一次，即必須唯一

主鍵

主鍵是一種唯一性索引，但它必須制定為"PRIMARY KEY"

全文索引

全文索引的類型是FULLTEXT，可以在VARCHAR或者TEXT類型的列上創建

單列索引與多列索引

索引可以是單列上創建的索引，也可以是在多列上創建的索引

創建索引的原則依據

表的主鍵，外鍵必須有索引；
數據量超過300行的表應該有索引；
經常與其他表進行連接的表，在連接字段上應該建立索引；
唯一性太差的字段不適合建立索引；
更新太頻繁的字段不適合創建索引；
經常出現在where子句中的字段，特別是大表的字段，應該建立索引；
索引應該建在選擇性高的字段上；
索引應該建在小字段上，對于大的文本字段甚至超長字段，不要建索引；

創建索引的方法

根據企業需求選擇了合適的索引之后，可使用CREATE INDEX創建索引

CREATE INDEX加上各個索引關鍵字便可創建各個類型的索引

創建普通索引

CREATE INDEX <索引的名字> ON tablename (列的列表)；

創建普通索引

CREATE INDEX salary_index ON IT_salary(薪資)；

創建唯一性索引

CREATE UNIQUE INDEX <索引的名字> ON tablename (列的列表)；

創建唯一性索引例子

CREATE UNIQUE INDEX salary_unique_index ON IT_salary(姓名)；

創建主鍵索引

CREATE TABLE tablename ( [...],PRIMARY KEY (列的列表))；
ALTER TABLE tablename ADD PRIMARY KEY (列的列表)；

主鍵索引例子

ALTER TABLE IT_salary ADD PRIMARY KEY (員工ID)；

索引查看

SHOW INDEX FROM tablename；
SHOW KEYS FROM tablename；

事務的概念

事務是一種機制，一個操作序列，包含了一組數據庫操作命令，并且把所有的命令作為一個整體一起向系統提交或撤銷操作請求，即這一組數據庫命令要么都執行，要么都不執行；
?
?事務是一個不可分割的工作邏輯單元，在數據庫系統上執行并發操作時，事務是最小的控制單元；
?
?使用于多用戶同時操作的數據庫系統的場景，如銀行，保險公司及證券交易系統等等；
?
?通過事務的整體性以保證數據的一致性；

事務的ACID特點

原子性：事務是一個完整的操作，事務的各元素是不可分的（原子的）
事務中的所有元素必須作為一個整體提交或回滾
如果事務中的任何元素失敗，則整個事務將失敗

一致性：當事務完成時，數據必須處于一致狀態：在事務開始之前，數據庫中存儲的數據處于一致狀態；在正在進行的事務中，數據可能處于不一致的狀態；當事務成功完成時，數據必須再次回到已知的一致狀態

隔離性:對數據進行修改的所有并發事務是彼此隔離的，這表明事務必須是獨立的，他不應以任何方式依賴于或影響其他事務
修改數據的事務可以在另一個使用相同數據的事務開始之前訪問這些數據，或者再另一個使用相同的數據的事務結束之后訪問這些數據

持久性:事務持久性指不管系統是否發生故障，事務處理的結果都是永久的
一旦事務被提交，事務的效果會被永久的保留在數據庫中

事務處理命令控制事務：

begin：開始一個事務
commit：提交一個事務
rollback：回滾一個事務

使用set命令進行控制：

set autocommit=0：禁止自動提交
set autocommit=1：開啟自動提交

存儲引擎概念介紹

?MySQL中的數據用各種不同的技術存儲在文件中，每一種技術都使用不同的存儲機制，索引技巧，鎖定水平并最終提供不同的功能和能力，這些不同的技術以及配套的功能在MySQL中稱為存儲引擎
?存儲引擎就是MySQL將數據存儲在文件系統中的存儲方式或存儲格式
?目前MySQL常用的兩種存儲引擎：
??MyISQM
??InnoDB
?MySQL存儲引擎是MySQL數據庫服務器中的組件，負責為數據庫執行實際的數據I/O操作
?使用特殊存儲引擎的主要優點之一在于，僅需提供特殊應用所需的特性，數據庫中的系統開銷較小，具有更有效和更高的數據庫性能
?MySQL系統中，存儲引擎處于文件系統之上，在數據保存到數據文件之前會傳輸存儲引擎，之后按照各個存儲引擎的存儲格式進行存儲

MyISAM的概述

ISAM的特點：
ISAM執行讀取操作的速度很快
而且不占用大量的內存和存儲資源
他不支持事務處理
不能夠容錯

MyISAM的特點：

1、不支持事務
2、表級鎖定形式，數據在更新時鎖定整個表
3、數據庫在讀寫過程中相互阻塞
會在數據寫入的過程阻塞用戶數據的讀取
也會在數據讀取的過程中阻塞用戶的數據寫入
4、可通過key_buffer_size來設置緩存索引，提高訪問性能，減少磁盤IO的壓力
但緩存只會緩存索引文件，不會緩存數據
5、采用MyISAM存儲引擎數據單獨寫入或讀取，速度過程較快且占用資源相對少
6、MyISAM存儲引擎它不支持外鍵約束，只支持全文索引
7、每個MyISAM在磁盤.上存儲成三個文件，每- -個文件的名字以表的名字開始，擴展名指出文件類型
8、MyISAM在磁盤.上存儲的文件
.frm文件存儲表定義
數據文件的擴展名為.MYD (MYData)
索引文件的擴展名是.MYI (MYIndex)

MyISAM適用的生產場景
公司業務不需要事務的支持
一般單方面讀取數據比較多的業務，或單方面寫入數據比較多的業務
MyISAM存儲引擎數據讀寫都比較頻繁場景不適合
使用讀寫并發訪問相對較低的業務
數據修改相對較少的業務
對數據業務一致性要求不是非常高的業務
服務器硬件資源相對比較差

配置存儲引擎MyISA

使用show engines查看系統支持的存儲引擎

方法1:
show table status from 庫名 where name='表名;
方法2:
show create table 表名;

修改存儲引擎

方法1: alter table修改;
alter table table name engine=引擎;
方法2:修改my.cnf,指定默認存儲引擎并重啟服務
default-storage-engine=InnDB
方法3: create table創建表時指定存儲引擎
create table 表名 (字段) engine= 引擎
方法4: Mysql convert table format 轉化存儲引擎
Mysql convert table_ format -user=root -password=密碼

• sock=/tmp/mysql.sock - engine =引擎 庫名 表名