共計 6236 個字符，預(yù)計需要花費 16 分鐘才能閱讀完成。

丸趣 TV 小編給大家分享一下 mysql 中引擎、Explain 和權(quán)限的示例分析，相信大部分人都還不怎么了解，因此分享這篇文章給大家參考一下，希望大家閱讀完這篇文章后大有收獲，下面讓我們一起去了解一下吧！

引擎

簡介

Innodb 引擎

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

MyIASM 引擎

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

主要區(qū)別

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

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

3、MyIASM 支持全文類型索引，而 InnoDB 不支持全文索引

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

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

應(yīng)用場景

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

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

MySQL Explain 詳解

在日常工作中，我們會有時會開慢查詢?nèi)ビ涗浺恍﹫?zhí)行時間比較久的 SQL 語句，找出這些 SQL 語句并不意味著完事了，些時我們常常用到 explain 這個命令來查看一個這些 SQL 語句的執(zhí)行計劃，查看該 SQL 語句有沒有使用上了索引，有沒有做全表掃描，這都可以通過 explain 命令來查看。所以我們深入了解 MySQL 的基于開銷的優(yōu)化器，還可以獲得很多可能被優(yōu)化器考慮到的訪問策略的細(xì)節(jié)，以及當(dāng)運行 SQL 語句時哪種策略預(yù)計會被優(yōu)化器采用。（QEP：sql 生成一個執(zhí)行計劃 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 出來的信息有 10 列，分別是 id、select_type、table、type、possible_keys、key、key_len、ref、rows、Extra, 下面對這些字段出現(xiàn)的可能進(jìn)行解釋：

一、id

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

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

2. 如果是子查詢，id 的序號會遞增，id 值越大優(yōu)先級越高，越先被執(zhí)行

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

二、select_type

  示查詢中每個 select 子句的類型

(1) SIMPLE(簡單 SELECT, 不使用 UNION 或子查詢等)

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

(3) UNION(UNION 中的第二個或后面的 SELECT 語句)

(4) DEPENDENT UNION(UNION 中的第二個或后面的 SELECT 語句，取決于外面的查詢)

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

(6) SUBQUERY(子查詢中的第一個 SELECT)

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

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

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

三、table

顯示這一行的數(shù)據(jù)是關(guān)于哪張表的，有時不是真實的表名字, 看到的是 derivedx(x 是個數(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 在表中找到所需行的方式，又稱“訪問類型”。

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

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

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

range: 只檢索給定范圍的行，使用一個索引來選擇行

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

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

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

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

五、possible_keys

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

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

六、Key

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

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

七、key_len

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

不損失精確性的情況下，長度越短越好

八、ref

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

九、rows

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

十、Extra

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

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

Using temporary：表示 MySQL 需要使用臨時表來存儲結(jié)果集，常見于排序和分組查詢

Using filesort：MySQL 中無法利用索引完成的排序操作稱為“文件排序”

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

Impossible where：這個值強(qiáng)調(diào)了 where 語句會導(dǎo)致沒有符合條件的行。

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

總結(jié)：

? EXPLAIN 不會告訴你關(guān)于觸發(fā)器、存儲過程的信息或用戶自定義函數(shù)對查詢的影響情況

? EXPLAIN 不考慮各種 Cache

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

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

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

MySQL 授權(quán)管理

語法：

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

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

all privileges  除 grant 外的所有權(quán)限
  select  僅查權(quán)限
  select,insert  查和插入權(quán)限
  …
  usage  無訪問權(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 和存儲過程
  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  ?? 使用 change master、kill、logs、purge、master 和 set global。還允許 mysqladmin???? 調(diào)試登陸
  replication client  服務(wù)器位置的訪問
  replication slave  由復(fù)制從屬使用
 
對于權(quán)限
 
權(quán)限參數(shù)

數(shù)據(jù)庫參數(shù)

對于目標(biāo)數(shù)據(jù)庫以及內(nèi)部其他：
  數(shù)據(jù)庫名.*  數(shù)據(jù)庫中的所有
  數(shù)據(jù)庫名. 表   指定數(shù)據(jù)庫中的某張表
  數(shù)據(jù)庫名. 存儲過程   指定數(shù)據(jù)庫中的存儲過程
  *.*  所有數(shù)據(jù)庫

用戶 IP 參數(shù)

用戶名 @IP 地址   用戶只能在改 IP 下才能訪問
用戶名 @192.168.1.%  用戶只能在改 IP 段下才能訪問 (通配符 % 表示任意)
用戶名 @%  用戶可以再任意 IP 下訪問(默認(rèn) IP 地址為 %)

示例

grant all privileges on db1.tb1 TO  用戶名 @ IP； 
 grant select on db1.* TO  用戶名 @ IP； 
 grant select,insert on *.* TO  用戶名 @ IP； 
 revoke select on db1.tb1 from  用戶名 @ IP；

刷新權(quán)限

flush privileges，將數(shù)據(jù)讀取到內(nèi)存中，從而立即生效。

忘記密碼

#  啟動免授權(quán)服務(wù)端
mysqld --skip-grant-tables
 
#  客戶端
mysql -u root -p
 
#  修改用戶名密碼
update mysql.user set authentication_string=password(666) where user= root 
flush privileges;
 
忘記密碼

以上是“mysql 中引擎、Explain 和權(quán)限的示例分析”這篇文章的所有內(nèi)容，感謝各位的閱讀！相信大家都有了一定的了解，希望分享的內(nèi)容對大家有所幫助，如果還想學(xué)習(xí)更多知識，歡迎關(guān)注丸趣 TV 行業(yè)資訊頻道！