mysql中引擎、Explain和權限的示例分析

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

引擎

簡介

Innodb引擎

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

MyIASM引擎

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

主要區別

1、MyIASM是非事務安全的，而InnoDB是事務安全的

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

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

4、MyIASM相對簡單，效率上要優于InnoDB，小型應用可以考慮使用MyIASM

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

應用場景

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

2、InnoDB用于事務處理，具有ACID事務支持等特性，如果在應用中執行大量insert和update操作，應該選擇InnoDB

MySQL Explain詳解

在日常工作中，我們會有時會開慢查詢去記錄一些執行時間比較久的SQL語句，找出這些SQL語句并不意味著完事了，些時我們常常用到explain這個命令來查看一個這些SQL語句的執行計劃，查看該SQL語句有沒有使用上了索引，有沒有做全表掃描，這都可以通過explain命令來查看。所以我們深入了解MySQL的基于開銷的優化器，還可以獲得很多可能被優化器考慮到的訪問策略的細節，以及當運行SQL語句時哪種策略預計會被優化器采用。（QEP：sql生成一個執行計劃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,下面對這些字段出現的可能進行解釋：

一、 id

     我的理解是SQL執行的順序的標識,SQL從大到小的執行

1. id相同時，執行順序由上至下

2. 如果是子查詢，id的序號會遞增，id值越大優先級越高，越先被執行

3.id如果相同，可以認為是一組，從上往下順序執行；在所有組中，id值越大，優先級越高，越先執行

二、select_type

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

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

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

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

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

(5) UNION RESULT(UNION的結果)

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

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

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

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

三、table

顯示這一行的數據是關于哪張表的，有時不是真實的表名字,看到的是derivedx(x是個數字,我的理解是第幾步執行的結果)

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區別為index類型只遍歷索引樹

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

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

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

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

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

五、possible_keys

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

該列完全獨立于EXPLAIN輸出所示的表的次序。這意味著在possible_keys中的某些鍵實際上不能按生成的表次序使用。
如果該列是NULL，則沒有相關的索引。在這種情況下，可以通過檢查WHERE子句看是否它引用某些列或適合索引的列來提高你的查詢性能。如果是這樣，創造一個適當的索引并且再次用EXPLAIN檢查查詢

六、Key

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

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

七、key_len

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

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

八、ref

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

九、rows

 表示MySQL根據表統計信息及索引選用情況，估算的找到所需的記錄所需要讀取的行數

十、Extra

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

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

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

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

Using join buffer：改值強調了在獲取連接條件時沒有使用索引，并且需要連接緩沖區來存儲中間結果。如果出現了這個值，那應該注意，根據查詢的具體情況可能需要添加索引來改進能。

Impossible where：這個值強調了where語句會導致沒有符合條件的行。

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

總結：

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

? EXPLAIN不考慮各種Cache

? EXPLAIN不能顯示MySQL在執行查詢時所作的優化工作

? 部分統計信息是估算的，并非精確值

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

MySQL授權管理

語法：

show grants for '用戶'@'IP地址'；                  -- 查看權限
grant  權限 on 數據庫.表 to   '用戶'@'IP地址'；      -- 授權
revoke 權限 on 數據庫.表 from '用戶'@'IP地址'；      -- 取消權限

權限參數

all privileges  除grant外的所有權限
            select          僅查權限
            select,insert   查和插入權限
            ...
            usage                   無訪問權限
            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(關閉MySQL)
            super                   ??使用change master、kill、logs、purge、master和set global。還允許mysqladmin????調試登陸
            replication client      服務器位置的訪問
            replication slave       由復制從屬使用
 
對于權限
 
權限參數

數據庫參數

對于目標數據庫以及內部其他：
            數據庫名.*           數據庫中的所有
            數據庫名.表          指定數據庫中的某張表
            數據庫名.存儲過程     指定數據庫中的存儲過程
            *.*                所有數據庫

用戶IP參數

用戶名@IP地址         用戶只能在改IP下才能訪問
用戶名@192.168.1.%   用戶只能在改IP段下才能訪問(通配符%表示任意)
用戶名@%             用戶可以再任意IP下訪問(默認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'；

刷新權限

flush privileges，將數據讀取到內存中，從而立即生效。

忘記密碼

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

以上是“mysql中引擎、Explain和權限的示例分析”這篇文章的所有內容，感謝各位的閱讀！相信大家都有了一定的了解，希望分享的內容對大家有所幫助，如果還想學習更多知識，歡迎關注億速云行業資訊頻道！