引擎

簡介

Innodb引擎

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

MyIASM引擎

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

主要區(qū)別

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

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

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

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

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

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

一、 id

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

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

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

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

二、select_type

      示查詢中每個(gè)select子句的類型

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

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

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

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

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

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

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

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

(9) UNCACHEABLE SUBQUERY(一個(gè)子查詢的結(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在表中找到所需行的方式，又稱“訪問類型”。

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

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

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

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

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

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

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

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

五、possible_keys

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

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

六、Key

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

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

七、key_len

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

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

八、ref

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

九、rows

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

十、Extra

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

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

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

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

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

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

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

總結(jié)：

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

• EXPLAIN不考慮各種Cache

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

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

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

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和存儲(chǔ)過程
            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ù)制從屬使用
 
對(duì)于權(quán)限
 
權(quán)限參數(shù)

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

對(duì)于目標(biāo)數(shù)據(jù)庫以及內(nèi)部其他：
            數(shù)據(jù)庫名.*           數(shù)據(jù)庫中的所有
            數(shù)據(jù)庫名.表          指定數(shù)據(jù)庫中的某張表
            數(shù)據(jù)庫名.存儲(chǔ)過程     指定數(shù)據(jù)庫中的存儲(chǔ)過程
            *.*                所有數(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)存中，從而立即生效。

忘記密碼

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

總結(jié)

以上就是這篇文章的全部內(nèi)容了，希望本文的內(nèi)容對(duì)大家的學(xué)習(xí)或者工作具有一定的參考學(xué)習(xí)價(jià)值，如果有疑問大家可以留言交流，謝謝大家對(duì)腳本之家的支持。