MySQL(7)-mysql索引和存儲(chǔ)引擎

? ? ?本篇博客講的是MySQL的索引的功能和使用 , 以及存儲(chǔ)引擎的基本簡(jiǎn)介

?一. mysql索引

索引的簡(jiǎn)介和作用

索引在MySQL中叫做"鍵" , 是存儲(chǔ)引擎用于快速找到記錄的一種數(shù)據(jù)結(jié)構(gòu) . 索引對(duì)良好的性能非常關(guān)鍵 , 尤其是當(dāng)表中的數(shù)據(jù)量越來(lái)越大時(shí) , 索引對(duì)于性能的影響愈來(lái)愈發(fā)重要 .

作用 : 通過(guò)一定的算法將數(shù)據(jù)庫(kù)中的記錄按一定的規(guī)律進(jìn)行分組 , 這樣查信息時(shí)可以縮小數(shù)據(jù)的搜索范圍 , 從而提高溜了查詢(xún)效率

用生活實(shí)例來(lái)說(shuō) , 索引就好像書(shū)的目錄 , 清單上的列表 ; 好比人去吃火鍋 , 當(dāng)菜單拿到客戶(hù)手上 , 客戶(hù)可以根據(jù)菜單上的分類(lèi)(海鮮 , 蔬菜類(lèi) , 肉類(lèi) , 飲料類(lèi)等) , 根據(jù)自己的口味能第一時(shí)間找到自己想吃的菜 .

索引的分類(lèi)

索引可分為 : 普通索引 , 唯一索引 , 全文索引 , 單列索引 , 多列索引 , 空間索引

語(yǔ)法格式:

CREATE TABLE 表名 (
? ? ? ?字段名1 ?數(shù)據(jù)類(lèi)型 [完整性約束條件…],
? ? ? ?字段名2 ?數(shù)據(jù)類(lèi)型 [完整性約束條件…],
? ? ? ?[UNIQUE | FULLTEXT | SPATIAL ] ? INDEX | KEY
[索引名] ?(字段名[(長(zhǎng)度)] ?[ASC |DESC])
);

示例 :?

創(chuàng)建一個(gè)INDEX普通索引

CREATE TABLE dept(
? ? ?dept_id INT,
? ? ?dept_name VARCHAR(30) ,
? ? ?comment VARCHAR(50),
? ? ?index??(dept_name) ? ? ? ? ?? ? # 將表中的dept_name字段指定為普通索引字段
);

創(chuàng)建一個(gè)UNIQUE唯一索引:

CREATE TABLE dept2 (
? ? ?dept_id INT,
? ? ?dept_name VARCHAR(30) ,
? ? ?comment VARCHAR(50),
? ? ?UNIQUE INDEX ?(dept_name)
);

相比普通索引來(lái)說(shuō) , 比上面的索引選項(xiàng)多了一個(gè)unique選項(xiàng)

創(chuàng)建一個(gè)全文索引;

CREATE TABLE dept3 (
? ? ?dept_id INT,
? ? ?dept_name VARCHAR(30) ,
? ? ?comment VARCHAR(50),
? ? ?log text,
FULLTEXT INDEX ?(log)
)engine=myisam;

注:?只有MYISAM存儲(chǔ)引擎支持全文索引，innodb存儲(chǔ)引擎不支持全文索引

創(chuàng)建多列索引

CREATE TABLE dept13 (
? ? ?dept_id INT,
? ? ?dept_name VARCHAR(30) ,
? ? ?comment VARCHAR(50),
INDEX ?(dept_name, comment)
);

相比普通索引來(lái)說(shuō) , 就是將多個(gè)字段設(shè)置為索引

對(duì)已存在的表創(chuàng)建索引

語(yǔ)法一:

CREATE ?[UNIQUE | FULLTEXT | SPATIAL ] ?INDEX ?索引名? ON 表名 (字段名[(長(zhǎng)度)] ?[ASC |DESC]) ;

創(chuàng)建普通索引示例：此方法要指定索引名稱(chēng)
CREATE INDEX index_dept_name ON dept6 ?(dept_name);

創(chuàng)建唯一索引示例：
CREATE UNIQUE INDEX index_dept_name ON dept6 (dept_name);

創(chuàng)建全文索引示例：
CREATE FULLTEXT INDEX index_dept_name ON dept6 (dept_name);

創(chuàng)建多列索引示例：
CREATE INDEX index_dept_name_ comment ON dept6 (dept_name, comment);

語(yǔ)法二:

ALTER TABLE在已存在的表上創(chuàng)建索引:
ALTER ?TABLE ?表名 ?ADD ?[UNIQUE | FULLTEXT | SPATIAL ] INDEX
? ? ?索引名 (字段名[(長(zhǎng)度)] ?[ASC |DESC]) ;

管理索引:

查看索引: show ?create ?table ?表名\G

測(cè)試索引: explain ?select ?* ?from ?表名 ?where ?字段名='xx';

刪除索引: drop ?index ?索引名 ?on ?表名

索引檢測(cè)實(shí)例:

要求 : 創(chuàng)建一個(gè)school的數(shù)據(jù)庫(kù) , 創(chuàng)建一張t2表 , 用存儲(chǔ)過(guò)程腳本t2表插入1000W條數(shù)據(jù) , 然后查詢(xún)t2數(shù)據(jù)看看花費(fèi)了多長(zhǎng)時(shí)間 ; 再為t2創(chuàng)建一個(gè)索引 , 再次查看數(shù)據(jù)看看所花費(fèi)的時(shí)間?

準(zhǔn)備:

? ? ? ? ? ?create ?database ?school ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ?#創(chuàng)建school數(shù)據(jù)庫(kù)

? ? ? ? ? ?create table school.t2(id int,name varchar(30)); ? ?#創(chuàng)建一張t2表 , 里面記錄id號(hào)和名字

定義一個(gè)插入1000W條數(shù)據(jù)的存儲(chǔ)過(guò)程 , 并調(diào)用此存儲(chǔ)過(guò)程

mysql>?delimiter?$$????????????????????????????//設(shè)置命令的界定符（也稱(chēng)為結(jié)束符）
mysql>?create?procedure?autoinsert1()??????????//創(chuàng)建autoinsert1這個(gè)存儲(chǔ)過(guò)程（類(lèi)似于shell腳本）
????->?BEGIN
????->?declare?i?int?default?1;
????->?while(i<100000)do
????->?insert?into?school.t2?values(i,'ccc');
????->?set?i=i+1;
????->?end?while;
????->?END$$
mysql>?delimiter?;
???????call??atuoinsert1();

分兩次查詢(xún)數(shù)據(jù) , 對(duì)比所花的時(shí)間(實(shí)驗(yàn)中途插入1000W條數(shù)據(jù)花了33分鐘 , 插100W估計(jì)就能看出效果了):

?從上述實(shí)驗(yàn)可以看到 , 在一個(gè)存放1000W的表中 , 查詢(xún)一條數(shù)據(jù)跟創(chuàng)建索引后查詢(xún)一條數(shù)據(jù)相差了4S的時(shí)間 , 隨著數(shù)據(jù)更大 , 查詢(xún)時(shí)間也會(huì)不斷增大 , 所以足以證明 , 創(chuàng)建索引會(huì)大大提高M(jìn)ySQL的查詢(xún)工作效率!!!

二. MySQL存儲(chǔ)引擎介紹

了就是如何存儲(chǔ)數(shù)據(jù)、如何為存儲(chǔ)的數(shù)據(jù)建立索引和如何更新、查詢(xún)數(shù)據(jù)等技術(shù)的實(shí)現(xiàn)方法。因?yàn)樵陉P(guān)系數(shù)據(jù)庫(kù)中數(shù)據(jù)的存儲(chǔ)是以表的形式存儲(chǔ)的，所以存儲(chǔ)引擎也可以稱(chēng)為表類(lèi)型（即存儲(chǔ)和操作此表的類(lèi)型）

在Oracle 和SQL Server等數(shù)據(jù)庫(kù)中只有一種存儲(chǔ)引擎，所有數(shù)據(jù)存儲(chǔ)管理機(jī)制都是一樣的。而MySql數(shù)據(jù)庫(kù)提供了多種存儲(chǔ)引擎。用戶(hù)可以根據(jù)不同的需求為數(shù)據(jù)表選擇不同的存儲(chǔ)引擎，用戶(hù)也可以根據(jù)自己的需要編寫(xiě)自己的存儲(chǔ)引擎。

1. 查看存儲(chǔ)引擎
SHOW ENGINES;
SHOW ENGINES\G ? ? ? ? ? ? ? 查看MYSQL支持的存儲(chǔ)引擎
SHOW VARIABLES LIKE 'storage_engine%'; ? ?查看當(dāng)前的存儲(chǔ)引擎
SHOW VARIABLES LIKE 'auto_inc%'; ? ? ? 查看自增長(zhǎng)的設(shè)置狀態(tài)
show global variables ? like ?'%connet%' ? 查看connet環(huán)境變量設(shè)置
mysql> show variables\G ? ? ? ? ?查看所有的環(huán)境變量
show variables當(dāng)前的會(huì)話(huà)
show global variables\G全局

2. 選擇存儲(chǔ)引擎
方法1.
mysql> create table innodb1(
-> id int
-> )engine=innodb;
mysql> show create table innodb1;

create tables test100(id init)engine=inodb;

方法2.
/etc/my.cnf
[mysqld] ? ? 在此行下添加下面的一行內(nèi)容
default-storage-engine=INNODB

MySQL常用的存儲(chǔ)引擎

MyISAM存儲(chǔ)引擎
由于該存儲(chǔ)引擎不支持事務(wù)、也不支持外鍵，所以訪(fǎng)問(wèn)速度較快。因此當(dāng)對(duì)事務(wù)完整性沒(méi)有要求并以訪(fǎng)問(wèn)為主的應(yīng)用適合使用該存儲(chǔ)引擎。

InnoDB存儲(chǔ)引擎（MYSQL默認(rèn)用此存儲(chǔ)引擎）
由于該存儲(chǔ)引擎在事務(wù)上具有優(yōu)勢(shì)，即支持具有提交、回滾及崩潰恢復(fù)能力等事務(wù)特性，所以比MyISAM存儲(chǔ)引擎占用更多的磁盤(pán)空間。
因此當(dāng)需要頻繁的更新、刪除操作，同時(shí)還對(duì)事務(wù)的完整性要求較高，需要實(shí)現(xiàn)并發(fā)控制，建議選擇。

MEMORY
MEMORY存儲(chǔ)引擎存儲(chǔ)數(shù)據(jù)的位置是內(nèi)存，因此訪(fǎng)問(wèn)速度最快，但是安全上沒(méi)有保障。適合于需要快速的訪(fǎng)問(wèn)或臨時(shí)表。

BLACKHOLE
黑洞存儲(chǔ)引擎，可以應(yīng)用于主備復(fù)制中的分發(fā)主庫(kù)。
使用BLACKHOLE存儲(chǔ)引擎的表不存儲(chǔ)任何數(shù)據(jù)，但如果mysql啟用了二進(jìn)制日志，SQL語(yǔ)句被寫(xiě)入日志（并被復(fù)制到從服務(wù)器）。這樣使用BLACKHOLE存儲(chǔ)引擎的mysqld可以作為主從復(fù)制中的中繼重復(fù)器或在其上面添加過(guò)濾器機(jī)制。