引擎

簡(jiǎn)介

Innodb引擎

Innodb引擎提供了對(duì)數(shù)據(jù)庫(kù)ACID事務(wù)的支持，并且實(shí)現(xiàn)了SQL標(biāo)準(zhǔn)的四種隔離級(jí)別。該引擎還提供了行級(jí)鎖和外鍵約束，它的設(shè)計(jì)目標(biāo)是處理大容量數(shù)據(jù)庫(kù)系統(tǒng)，它本身其實(shí)就是基于MySQL后臺(tái)的完整數(shù)據(jù)庫(kù)系統(tǒng)，MySQL運(yùn)行時(shí)Innodb會(huì)在內(nèi)存中建立緩沖池，用于緩沖數(shù)據(jù)和索引。但是該引擎不支持FULLTEXT類(lèi)型的索引，而且它沒(méi)有保存表的行數(shù)，當(dāng)SELECT COUNT(*) FROM TABLE時(shí)需要掃描全表。當(dāng)需要使用數(shù)據(jù)庫(kù)事務(wù)時(shí)，該引擎當(dāng)然是首選。由于鎖的粒度更小，寫(xiě)操作不會(huì)鎖定全表，所以在并發(fā)較高時(shí)，使用Innodb引擎會(huì)提升效率。但是使用行級(jí)鎖也不是絕對(duì)的，如果在執(zhí)行一個(gè)SQL語(yǔ)句時(shí)MySQL不能確定要掃描的范圍，InnoDB表同樣會(huì)鎖全表。

MyIASM引擎

MyIASM是MySQL默認(rèn)的引擎，但是它沒(méi)有提供對(duì)數(shù)據(jù)庫(kù)事務(wù)的支持，也不支持行級(jí)鎖和外鍵，因此當(dāng)INSERT(插入)或UPDATE(更新)數(shù)據(jù)時(shí)即寫(xiě)操作需要鎖定整個(gè)表，效率便會(huì)低一些。不過(guò)和Innodb不同，MyIASM中存儲(chǔ)了表的行數(shù)，于是SELECT COUNT(*) FROM TABLE時(shí)只需要直接讀取已經(jīng)保存好的值而不需要進(jìn)行全表掃描。如果表的讀操作遠(yuǎn)遠(yuǎn)多于寫(xiě)操作且不需要數(shù)據(jù)庫(kù)事務(wù)的支持，那么MyIASM也是很好的選擇。

主要區(qū)別

1、MyIASM是非事務(wù)安全的，而InnoDB是事務(wù)安全的

2、MyIASM鎖的粒度是表級(jí)的，而InnoDB支持行級(jí)鎖

3、MyIASM支持全文類(lèi)型索引，而InnoDB不支持全文索引

4、MyIASM相對(duì)簡(jiǎn)單，效率上要優(yōu)于InnoDB，小型應(yīng)用可以考慮使用MyIASM

5、MyIASM表保存成文件形式，跨平臺(tái)使用更加方便

應(yīng)用場(chǎng)景

1、MyIASM管理非事務(wù)表，提供高速存儲(chǔ)和檢索以及全文搜索能力，如果再應(yīng)用中執(zhí)行大量select操作，應(yīng)該選擇MyIASM

2、InnoDB用于事務(wù)處理，具有ACID事務(wù)支持等特性，如果在應(yīng)用中執(zhí)行大量insert和update操作，應(yīng)該選擇InnoDB

MySQL Explain詳解

在日常工作中，我們會(huì)有時(shí)會(huì)開(kāi)慢查詢(xún)?nèi)ビ涗浺恍﹫?zhí)行時(shí)間比較久的SQL語(yǔ)句，找出這些SQL語(yǔ)句并不意味著完事了，些時(shí)我們常常用到explain這個(gè)命令來(lái)查看一個(gè)這些SQL語(yǔ)句的執(zhí)行計(jì)劃，查看該SQL語(yǔ)句有沒(méi)有使用上了索引，有沒(méi)有做全表掃描，這都可以通過(guò)explain命令來(lái)查看。所以我們深入了解MySQL的基于開(kāi)銷(xiāo)的優(yōu)化器，還可以獲得很多可能被優(yōu)化器考慮到的訪問(wèn)策略的細(xì)節(jié)，以及當(dāng)運(yùn)行SQL語(yǔ)句時(shí)哪種策略預(yù)計(jì)會(huì)被優(yōu)化器采用。（QEP：sql生成一個(gè)執(zhí)行計(jì)劃query Execution plan）

mysql> explain select * from servers;+----+-------------+---------+------+---------------+------+---------+------+------+-------+| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |+----+-------------+---------+------+---------------+------+---------+------+------+-------+| 1 | SIMPLE | servers | ALL | NULL  | NULL | NULL | NULL | 1 | NULL |+----+-------------+---------+------+---------------+------+---------+------+------+-------+row in set (0.03 sec)

expain出來(lái)的信息有10列，分別是id、select_type、table、type、possible_keys、key、key_len、ref、rows、Extra,下面對(duì)這些字段出現(xiàn)的可能進(jìn)行解釋?zhuān)?/p>

一、 id

     我的理解是SQL執(zhí)行的順序的標(biāo)識(shí),SQL從大到小的執(zhí)行

1. id相同時(shí)，執(zhí)行順序由上至下

2. 如果是子查詢(xún)，id的序號(hào)會(huì)遞增，id值越大優(yōu)先級(jí)越高，越先被執(zhí)行

3.id如果相同，可以認(rèn)為是一組，從上往下順序執(zhí)行；在所有組中，id值越大，優(yōu)先級(jí)越高，越先執(zhí)行

二、select_type

      示查詢(xún)中每個(gè)select子句的類(lèi)型

(1) SIMPLE(簡(jiǎn)單SELECT,不使用UNION或子查詢(xún)等)

(2) PRIMARY(查詢(xún)中若包含任何復(fù)雜的子部分,最外層的select被標(biāo)記為PRIMARY)

(3) UNION(UNION中的第二個(gè)或后面的SELECT語(yǔ)句)

(4) DEPENDENT UNION(UNION中的第二個(gè)或后面的SELECT語(yǔ)句，取決于外面的查詢(xún))

(5) UNION RESULT(UNION的結(jié)果)

(6) SUBQUERY(子查詢(xún)中的第一個(gè)SELECT)

(7) DEPENDENT SUBQUERY(子查詢(xún)中的第一個(gè)SELECT，取決于外面的查詢(xún))

(8) DERIVED(派生表的SELECT, FROM子句的子查詢(xún))

(9) UNCACHEABLE SUBQUERY(一個(gè)子查詢(xún)的結(jié)果不能被緩存，必須重新評(píng)估外鏈接的第一行)

三、table

顯示這一行的數(shù)據(jù)是關(guān)于哪張表的，有時(shí)不是真實(shí)的表名字,看到的是derivedx(x是個(gè)數(shù)字,我的理解是第幾步執(zhí)行的結(jié)果)

mysql> explain select * from (select * from ( select * from t1 where id=2602) a) b;+----+-------------+------------+--------+-------------------+---------+---------+------+------+-------+| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |+----+-------------+------------+--------+-------------------+---------+---------+------+------+-------+| 1 | PRIMARY | <derived2> | system | NULL  | NULL | NULL | NULL | 1 | || 2 | DERIVED | <derived3> | system | NULL  | NULL | NULL | NULL | 1 | || 3 | DERIVED | t1  | const | PRIMARY,idx_t1_id | PRIMARY | 4 | | 1 | |+----+-------------+------------+--------+-------------------+---------+---------+------+------+-------+

四、type

表示MySQL在表中找到所需行的方式，又稱(chēng)“訪問(wèn)類(lèi)型”。

常用的類(lèi)型有： ALL, index,  range, ref, eq_ref, const, system, NULL（從左到右，性能從差到好）

ALL：Full Table Scan， MySQL將遍歷全表以找到匹配的行

index: Full Index Scan，index與ALL區(qū)別為index類(lèi)型只遍歷索引樹(shù)

range:只檢索給定范圍的行，使用一個(gè)索引來(lái)選擇行

ref: 表示上述表的連接匹配條件，即哪些列或常量被用于查找索引列上的值

eq_ref: 類(lèi)似ref，區(qū)別就在使用的索引是唯一索引，對(duì)于每個(gè)索引鍵值，表中只有一條記錄匹配，簡(jiǎn)單來(lái)說(shuō)，就是多表連接中使用primary key或者 unique key作為關(guān)聯(lián)條件

const、system: 當(dāng)MySQL對(duì)查詢(xún)某部分進(jìn)行優(yōu)化，并轉(zhuǎn)換為一個(gè)常量時(shí)，使用這些類(lèi)型訪問(wèn)。如將主鍵置于where列表中，MySQL就能將該查詢(xún)轉(zhuǎn)換為一個(gè)常量,system是const類(lèi)型的特例，當(dāng)查詢(xún)的表只有一行的情況下，使用system

NULL: MySQL在優(yōu)化過(guò)程中分解語(yǔ)句，執(zhí)行時(shí)甚至不用訪問(wèn)表或索引，例如從一個(gè)索引列里選取最小值可以通過(guò)單獨(dú)索引查找完成。

五、possible_keys

指出MySQL能使用哪個(gè)索引在表中找到記錄，查詢(xún)涉及到的字段上若存在索引，則該索引將被列出，但不一定被查詢(xún)使用

該列完全獨(dú)立于EXPLAIN輸出所示的表的次序。這意味著在possible_keys中的某些鍵實(shí)際上不能按生成的表次序使用。
如果該列是NULL，則沒(méi)有相關(guān)的索引。在這種情況下，可以通過(guò)檢查WHERE子句看是否它引用某些列或適合索引的列來(lái)提高你的查詢(xún)性能。如果是這樣，創(chuàng)造一個(gè)適當(dāng)?shù)乃饕⑶以俅斡肊XPLAIN檢查查詢(xún)

六、Key

key列顯示MySQL實(shí)際決定使用的鍵（索引）

如果沒(méi)有選擇索引，鍵是NULL。要想強(qiáng)制MySQL使用或忽視possible_keys列中的索引，在查詢(xún)中使用FORCE INDEX、USE INDEX或者IGNORE INDEX。

七、key_len

表示索引中使用的字節(jié)數(shù)，可通過(guò)該列計(jì)算查詢(xún)中使用的索引的長(zhǎng)度（key_len顯示的值為索引字段的最大可能長(zhǎng)度，并非實(shí)際使用長(zhǎng)度，即key_len是根據(jù)表定義計(jì)算而得，不是通過(guò)表內(nèi)檢索出的）

不損失精確性的情況下，長(zhǎng)度越短越好

八、ref

表示上述表的連接匹配條件，即哪些列或常量被用于查找索引列上的值

九、rows

 表示MySQL根據(jù)表統(tǒng)計(jì)信息及索引選用情況，估算的找到所需的記錄所需要讀取的行數(shù)

十、Extra

該列包含MySQL解決查詢(xún)的詳細(xì)信息,有以下幾種情況：

Using where:列數(shù)據(jù)是從僅僅使用了索引中的信息而沒(méi)有讀取實(shí)際的行動(dòng)的表返回的，這發(fā)生在對(duì)表的全部的請(qǐng)求列都是同一個(gè)索引的部分的時(shí)候，表示mysql服務(wù)器將在存儲(chǔ)引擎檢索行后再進(jìn)行過(guò)濾

Using temporary：表示MySQL需要使用臨時(shí)表來(lái)存儲(chǔ)結(jié)果集，常見(jiàn)于排序和分組查詢(xún)

Using filesort：MySQL中無(wú)法利用索引完成的排序操作稱(chēng)為“文件排序”

Using join buffer：改值強(qiáng)調(diào)了在獲取連接條件時(shí)沒(méi)有使用索引，并且需要連接緩沖區(qū)來(lái)存儲(chǔ)中間結(jié)果。如果出現(xiàn)了這個(gè)值，那應(yīng)該注意，根據(jù)查詢(xún)的具體情況可能需要添加索引來(lái)改進(jìn)能。

Impossible where：這個(gè)值強(qiáng)調(diào)了where語(yǔ)句會(huì)導(dǎo)致沒(méi)有符合條件的行。

Select tables optimized away：這個(gè)值意味著僅通過(guò)使用索引，優(yōu)化器可能僅從聚合函數(shù)結(jié)果中返回一行

總結(jié)：

• EXPLAIN不會(huì)告訴你關(guān)于觸發(fā)器、存儲(chǔ)過(guò)程的信息或用戶(hù)自定義函數(shù)對(duì)查詢(xún)的影響情況

• EXPLAIN不考慮各種Cache

• EXPLAIN不能顯示MySQL在執(zhí)行查詢(xún)時(shí)所作的優(yōu)化工作

• 部分統(tǒng)計(jì)信息是估算的，并非精確值

• EXPALIN只能解釋SELECT操作，其他操作要重寫(xiě)為SELECT后查看執(zhí)行計(jì)劃。

MySQL授權(quán)管理

語(yǔ)法：

show grants for '用戶(hù)'@'IP地址'；                  -- 查看權(quán)限
grant  權(quán)限 on 數(shù)據(jù)庫(kù).表 to   '用戶(hù)'@'IP地址'；      -- 授權(quán)
revoke 權(quán)限 on 數(shù)據(jù)庫(kù).表 from '用戶(hù)'@'IP地址'；      -- 取消權(quán)限

權(quán)限參數(shù)

all privileges  除grant外的所有權(quán)限
            select          僅查權(quán)限
            select,insert   查和插入權(quán)限
            ...
            usage                   無(wú)訪問(wèn)權(quán)限
            alter                   使用alter table
            alter routine           使用alter procedure和drop procedure
            create                  使用create table
            create routine          使用create procedure
            create temporary tables 使用create temporary tables
            create user             使用create user、drop user、rename user和revoke  all privileges
            create view             使用create view
            delete                  使用delete
            drop                    使用drop table
            execute                 使用call和存儲(chǔ)過(guò)程
            file                    使用select into outfile 和 load data infile
            grant option            使用grant 和 revoke
            index                   使用index
            insert                  使用insert
            lock tables             使用lock table
            process                 使用show full processlist
            select                  使用select
            show databases          使用show databases
            show view               使用show view
            update                  使用update
            reload                  使用flush
            shutdown                使用mysqladmin shutdown(關(guān)閉MySQL)
            super                  
注：相關(guān)教程知識(shí)閱讀請(qǐng)移步到MYSQL教程頻道。