引擎
簡介
Innodb引擎
Innodb引擎提供了對數(shù)據庫ACID事務的支持，并且實現(xiàn)了SQL標準的四種隔離級別。該引擎還提供了行級鎖和外鍵約束，它的設計目標是處理大容量數(shù)據庫系統(tǒng)，它本身其實就是基于MySQL后臺的完整數(shù)據庫系統(tǒng)，MySQL運行時Innodb會在內存中建立緩沖池，用于緩沖數(shù)據和索引。但是該引擎不支持FULLTEXT類型的索引，而且它沒有保存表的行數(shù)，當SELECT COUNT(*) FROM TABLE時需要掃描全表。當需要使用數(shù)據庫事務時，該引擎當然是首選。由于鎖的粒度更小，寫操作不會鎖定全表，所以在并發(fā)較高時，使用Innodb引擎會提升效率。但是使用行級鎖也不是絕對的，如果在執(zhí)行一個SQL語句時MySQL不能確定要掃描的范圍，InnoDB表同樣會鎖全表。
MyIASM引擎
MyIASM是MySQL默認的引擎，但是它沒有提供對數(shù)據庫事務的支持，也不支持行級鎖和外鍵，因此當INSERT(插入)或UPDATE(更新)數(shù)據時即寫操作需要鎖定整個表，效率便會低一些。不過和Innodb不同，MyIASM中存儲了表的行數(shù)，于是SELECT COUNT(*) FROM TABLE時只需要直接讀取已經保存好的值而不需要進行全表掃描。如果表的讀操作遠遠多于寫操作且不需要數(shù)據庫事務的支持，那么MyIASM也是很好的選擇。
主要區(qū)別
1、MyIASM是非事務安全的，而InnoDB是事務安全的
2、MyIASM鎖的粒度是表級的，而InnoDB支持行級鎖
3、MyIASM支持全文類型索引，而InnoDB不支持全文索引
4、MyIASM相對簡單，效率上要優(yōu)于InnoDB，小型應用可以考慮使用MyIASM
5、MyIASM表保存成文件形式，跨平臺使用更加方便
應用場景
1、MyIASM管理非事務表，提供高速存儲和檢索以及全文搜索能力，如果再應用中執(zhí)行大量select操作，應該選擇MyIASM
2、InnoDB用于事務處理，具有ACID事務支持等特性，如果在應用中執(zhí)行大量insert和update操作，應該選擇InnoDB
MySQL Explain詳解
在日常工作中，我們會有時會開慢查詢去記錄一些執(zhí)行時間比較久的SQL語句，找出這些SQL語句并不意味著完事了，些時我們常常用到explain這個命令來查看一個這些SQL語句的執(zhí)行計劃，查看該SQL語句有沒有使用上了索引，有沒有做全表掃描，這都可以通過explain命令來查看。所以我們深入了解MySQL的基于開銷的優(yōu)化器，還可以獲得很多可能被優(yōu)化器考慮到的訪問策略的細節(jié)，以及當運行SQL語句時哪種策略預計會被優(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)的可能進行解釋：
一、 id
我的理解是SQL執(zhí)行的順序的標識,SQL從大到小的執(zhí)行
1. id相同時，執(zhí)行順序由上至下
2. 如果是子查詢，id的序號會遞增，id值越大優(yōu)先級越高，越先被執(zhí)行
3.id如果相同，可以認為是一組，從上往下順序執(zhí)行；在所有組中，id值越大，優(yōu)先級越高，越先執(zhí)行
二、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
顯示這一行的數(shù)據是關于哪張表的，有時不是真實的表名字,看到的是derivedx(x是個數(shù)字,我的理解是第幾步執(zhí)行的結果)
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作為關聯(lián)條件
const、system: 當MySQL對查詢某部分進行優(yōu)化，并轉換為一個常量時，使用這些類型訪問。如將主鍵置于where列表中，MySQL就能將該查詢轉換為一個常量,system是const類型的特例，當查詢的表只有一行的情況下，使用system
NULL: MySQL在優(yōu)化過程中分解語句，執(zhí)行時甚至不用訪問表或索引，例如從一個索引列里選取最小值可以通過單獨索引查找完成。
五、possible_keys
指出MySQL能使用哪個索引在表中找到記錄，查詢涉及到的字段上若存在索引，則該索引將被列出，但不一定被查詢使用
該列完全獨立于EXPLAIN輸出所示的表的次序。這意味著在possible_keys中的某些鍵實際上不能按生成的表次序使用。
如果該列是NULL，則沒有相關的索引。在這種情況下，可以通過檢查WHERE子句看是否它引用某些列或適合索引的列來提高你的查詢性能。如果是這樣，創(chuàng)造一個適當?shù)乃饕⑶以俅斡肊XPLAIN檢查查詢
六、Key
key列顯示MySQL實際決定使用的鍵（索引）
如果沒有選擇索引，鍵是NULL。要想強制MySQL使用或忽視possible_keys列中的索引，在查詢中使用FORCE INDEX、USE INDEX或者IGNORE INDEX。
七、key_len
表示索引中使用的字節(jié)數(shù)，可通過該列計算查詢中使用的索引的長度（key_len顯示的值為索引字段的最大可能長度，并非實際使用長度，即key_len是根據表定義計算而得，不是通過表內檢索出的）
不損失精確性的情況下，長度越短越好
八、ref
表示上述表的連接匹配條件，即哪些列或常量被用于查找索引列上的值
九、rows
表示MySQL根據表統(tǒng)計信息及索引選用情況，估算的找到所需的記錄所需要讀取的行數(shù)
十、Extra
該列包含MySQL解決查詢的詳細信息,有以下幾種情況：
Using where:列數(shù)據是從僅僅使用了索引中的信息而沒有讀取實際的行動的表返回的，這發(fā)生在對表的全部的請求列都是同一個索引的部分的時候，表示mysql服務器將在存儲引擎檢索行后再進行過濾
Using temporary：表示MySQL需要使用臨時表來存儲結果集，常見于排序和分組查詢
Using filesort：MySQL中無法利用索引完成的排序操作稱為“文件排序”
Using join buffer：改值強調了在獲取連接條件時沒有使用索引，并且需要連接緩沖區(qū)來存儲中間結果。如果出現(xiàn)了這個值，那應該注意，根據查詢的具體情況可能需要添加索引來改進能。
Impossible where：這個值強調了where語句會導致沒有符合條件的行。
Select tables optimized away：這個值意味著僅通過使用索引，優(yōu)化器可能僅從聚合函數(shù)結果中返回一行
總結：
• EXPLAIN不會告訴你關于觸發(fā)器、存儲過程的信息或用戶自定義函數(shù)對查詢的影響情況
• EXPLAIN不考慮各種Cache
• EXPLAIN不能顯示MySQL在執(zhí)行查詢時所作的優(yōu)化工作
• 部分統(tǒng)計信息是估算的，并非精確值
• EXPALIN只能解釋SELECT操作，其他操作要重寫為SELECT后查看執(zhí)行計劃。
MySQL授權管理
語法：
show grants for '用戶'@'IP地址'； -- 查看權限
grant 權限 on 數(shù)據庫.表 to '用戶'@'IP地址'； -- 授權
revoke 權限 on 數(shù)據庫.表 from '用戶'@'IP地址'； -- 取消權限
權限參數(shù)
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
注：相關教程知識閱讀請移步到MYSQL教程頻道。
新聞熱點
疑難解答
圖片精選
