本篇博客講的是MySQL的索引的功能和使用 , 以及存儲引擎的基本簡介

 一. mysql索引

       索引的簡介和作用

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

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

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

索引的分類

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

語法格式:

CREATE TABLE 表名 (

       字段名1  數(shù)據(jù)類型 [完整性約束條件…],

       字段名2  數(shù)據(jù)類型 [完整性約束條件…],

       [UNIQUE | FULLTEXT | SPATIAL ]   INDEX | KEY

[索引名]  (字段名[(長度)]  [ASC |DESC])

示例 :

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

CREATE TABLE dept(

     dept_id INT,

     dept_name VARCHAR(30) ,

     comment VARCHAR(50),

     index  (dept_name)              # 將表中的dept_name字段指定為普通索引字段

);

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

CREATE TABLE dept2 (

     dept_id INT,

     dept_name VARCHAR(30) ,

     comment VARCHAR(50),

     UNIQUE INDEX  (dept_name)

);

相比普通索引來說 , 比上面的索引選項多了一個unique選項

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

CREATE TABLE dept3 (

     dept_id INT,

     dept_name VARCHAR(30) ,

     comment VARCHAR(50),

     log text,

FULLTEXT INDEX  (log)

)engine=myisam;

注: 只有MYISAM存儲引擎支持全文索引，innodb存儲引擎不支持全文索引

創(chuàng)建多列索引

CREATE TABLE dept13 (

     dept_id INT,

     dept_name VARCHAR(30) ,

     comment VARCHAR(50),

INDEX  (dept_name, comment)

);

相比普通索引來說 , 就是將多個字段設(shè)置為索引

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

語法一:

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

創(chuà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);

語法二:

ALTER TABLE在已存在的表上創(chuàng)建索引:

ALTER  TABLE  表名  ADD  [UNIQUE | FULLTEXT | SPATIAL ] INDEX

     索引名 (字段名[(長度)]  [ASC |DESC]) ;

管理索引:

查看索引: show  create  table  表名/G

測試索引: explain  select  *  from  表名  where  字段名='xx';

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

索引檢測實例:

要求 : 創(chuàng)建一個school的數(shù)據(jù)庫 , 創(chuàng)建一張t2表 , 用存儲過程腳本t2表插入1000W條數(shù)據(jù) , 然后查詢t2數(shù)據(jù)看看花費了多長時間 ; 再為t2創(chuàng)建一個索引 , 再次查看數(shù)據(jù)看看所花費的時間

準(zhǔn)備:

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

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

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

mysql> delimiter $$                            //設(shè)置命令的界定符（也稱為結(jié)束符）

mysql> create procedure autoinsert1()          //創(chuàng)建autoinsert1這個存儲過程（類似于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();

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

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

二. MySQL存儲引擎介紹

了就是如何存儲數(shù)據(jù)、如何為存儲的數(shù)據(jù)建立索引和如何更新、查詢數(shù)據(jù)等技術(shù)的實現(xiàn)方法。因為在關(guān)系數(shù)據(jù)庫中數(shù)據(jù)的存儲是以表的形式存儲的，所以存儲引擎也可以稱為表類型（即存儲和操作此表的類型）

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

1. 查看存儲引擎

SHOW ENGINES;

SHOW ENGINES/G               查看MYSQL支持的存儲引擎

SHOW VARIABLES LIKE 'storage_engine%';    查看當(dāng)前的存儲引擎

SHOW VARIABLES LIKE 'auto_inc%';       查看自增長的設(shè)置狀態(tài)

show global variables   like  '%connet%'   查看connet環(huán)境變量設(shè)置

mysql> show variables/G          查看所有的環(huán)境變量

show variables當(dāng)前的會話

show global variables/G全局

2. 選擇存儲引擎

方法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

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

由于該存儲引擎在事務(wù)上具有優(yōu)勢，即支持具有提交、回滾及崩潰恢復(fù)能力等事務(wù)特性，所以比MyISAM存儲引擎占用更多的磁盤空間。

因此當(dāng)需要頻繁的更新、刪除操作，同時還對事務(wù)的完整性要求較高，需要實現(xiàn)并發(fā)控制，建議選擇。

MEMORY

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

BLACKHOLE

黑洞存儲引擎，可以應(yīng)用于主備復(fù)制中的分發(fā)主庫。

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