您當(dāng)前位置：首頁 > 數(shù)據(jù)庫 > 數(shù)據(jù)庫應(yīng)用 > [置頂] MySQL系列教程（二）

[置頂] MySQL系列教程（二）

來源：程序員人生發(fā)布時間：2016-12-03 09:58:50 閱讀次數(shù)：3828次

mySQL履行計劃

語法

explain <sql語句>

例如:

explain select * from t3 where id=3952602;

id

select_type

就是select類型,可以有以下幾種

SIMPLE

簡單SELECT(不使用UNION或子查詢等) 例如:
mysql> explain select * from t3 where id=3952602;
+----+-------------+-------+-------+-------------------+---------+---------+-------+------+-------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+-------+-------------------+---------+---------+-------+------+-------+
| 1 | SIMPLE | t3 | const | PRIMARY,idx_t3_id | PRIMARY | 4 | const | 1 | |
+----+-------------+-------+-------+-------------------+---------+---------+-------+------+-------+

PRIMARY

UNION

DEPENDENT UNION

UNION RESULT

SUBQUERY

子查詢中的第1個SELECT.
mysql> explain select * from t3 where id = (select id from t3 where id=3952602 ) ;
+----+-------------+-------+-------+-------------------+---------+---------+-------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+-------+-------------------+---------+---------+-------+------+-------------+
| 1 | PRIMARY | t3 | const | PRIMARY,idx_t3_id | PRIMARY | 4 | const | 1 | |
| 2 | SUBQUERY | t3 | const | PRIMARY,idx_t3_id | PRIMARY | 4 | | 1 | Using index |
+----+-------------+-------+-------+-------------------+---------+---------+-------+------+-------------+

DEPENDENT SUBQUERY

table

type

這列很重要,顯示了連接使用了哪一種種別,有沒有使用索引.
從最好到最差的連接類型為const、eq_reg、ref、range、indexhe和ALL

system

const

對每一個來自于前面的表的行組合，從該表中讀取1行。這多是最好的聯(lián)接類型，除const類型。它用在1個索引的所有部份被聯(lián)接使用并且索引是UNIQUE或PRIMARY KEY。
eq_ref可以用于使用= 操作符比較的帶索引的列。比較值可以為常量或1個使用在該表前面所讀取的表的列的表達(dá)式。
在下面的例子中，MySQL可使用eq_ref聯(lián)接來處理ref_tables：
SELECT * FROM ref_table,other_table
WHERE ref_table.key_column=other_table.column;
SELECT * FROM ref_table,other_table
WHERE ref_table.key_column_part1=other_table.column
AND ref_table.key_column_part2=1;

例如

mysql> create unique index idx_t3_id on t3(id) ;
Query OK, 1000 rows affected (0.03 sec)
Records: 1000 Duplicates: 0 Warnings: 0
mysql> explain select * from t3,t4 where t3.id=t4.accountid;
+----+-------------+-------+--------+-------------------+-----------+---------+----------------------+------+-------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+--------+-------------------+-----------+---------+----------------------+------+-------+
| 1 | SIMPLE | t4 | ALL | NULL | NULL | NULL | NULL | 1000 | |
| 1 | SIMPLE | t3 | eq_ref | PRIMARY,idx_t3_id | idx_t3_id | 4 | dbatest.t4.accountid | 1 | |
+----+-------------+-------+--------+-------------------+-----------+---------+----------------------+------+-------+

ref

對每一個來自于前面的表的行組合，所有有匹配索引值的即將從這張表中讀取。如果聯(lián)接只使用鍵的最左側(cè)的前綴，或如果鍵不是UNIQUE或PRIMARY KEY（換句話說，如果聯(lián)接不能基于關(guān)鍵字選擇單個行的話），則使用ref。如果使用的鍵僅僅匹配少許行，該聯(lián)接類型是不錯的。
ref可以用于使用=或<=>操作符的帶索引的列。
在下面的例子中，MySQL可使用ref聯(lián)接來處理ref_tables：
SELECT * FROM ref_table WHERE key_column=expr;
SELECT * FROM ref_table,other_table
WHERE ref_table.key_column=other_table.column;
SELECT * FROM ref_table,other_table
WHERE ref_table.key_column_part1=other_table.column
AND ref_table.key_column_part2=1;

例如:

mysql> drop index idx_t3_id on t3;
Query OK, 1000 rows affected (0.03 sec)
Records: 1000 Duplicates: 0 Warnings: 0
mysql> create index idx_t3_id on t3(id) ;
Query OK, 1000 rows affected (0.04 sec)
Records: 1000 Duplicates: 0 Warnings: 0
mysql> explain select * from t3,t4 where t3.id=t4.accountid;
+----+-------------+-------+------+-------------------+-----------+---------+----------------------+------+-------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+-------------------+-----------+---------+----------------------+------+-------+
| 1 | SIMPLE | t4 | ALL | NULL | NULL | NULL | NULL | 1000 | |
| 1 | SIMPLE | t3 | ref | PRIMARY,idx_t3_id | idx_t3_id | 4 | dbatest.t4.accountid | 1 | |
+----+-------------+-------+------+-------------------+-----------+---------+----------------------+------+-------+
2 rows in set (0.00 sec)

ref_or_null

該聯(lián)接類型猶如ref，但是添加了MySQL可以專門搜索包括NULL值的行。在解決子查詢中常常使用該聯(lián)接類型的優(yōu)化。
在下面的例子中，MySQL可使用ref_or_null聯(lián)接來處理ref_tables：
SELECT * FROM ref_table
WHERE key_column=expr OR key_column IS NULL;

index_merge

unique_subquery

該類型替換了下面情勢的IN子查詢的ref：
value IN (SELECT primary_key FROM single_table WHERE some_expr)
unique_subquery是1個索引查找函數(shù)，可以完全替換子查詢，效力更高。

index_subquery

該聯(lián)接類型類似于unique_subquery。可以替換IN子查詢，但只合適以下情勢的子查詢中的非唯1索引：

value IN (SELECT key_column FROM single_table WHERE some_expr)

range

index

該聯(lián)接類型與ALL相同，除只有索引樹被掃描。這通常比ALL快，由于索引文件通常比數(shù)據(jù)文件小。
當(dāng)查詢只使用作為單索引1部份的列時，MySQL可使用該聯(lián)接類型。

ALL

對每一個來自于先前的表的行組合，進(jìn)行完全的表掃描。如果表是第1個沒標(biāo)記const的表，這通常不好，并且通常在它情況下很差。通常可以增加更多的索引而不要使用ALL，使得行能基于前面的表中的常數(shù)值或列值被檢索出。

possible_keys

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

key

key列顯示MySQL實際決定使用的鍵（索引）。如果沒有選擇索引，鍵是NULL。要想強(qiáng)迫MySQL使用或忽視possible_keys列中的索引，在查詢中使用FORCE INDEX、USE INDEX或IGNORE INDEX。

key_len

key_len列顯示MySQL決定使用的鍵長度。如果鍵是NULL，則長度為NULL。
使用的索引的長度。在不損失精確性的情況下，長度越短越好

ref

ref列顯示使用哪一個列或常數(shù)與key1起從表當(dāng)選擇行。

rows

rows列顯示MySQL認(rèn)為它履行查詢時必須檢查的行數(shù)。

Extra

該列包括MySQL解決查詢的詳細(xì)信息,下面詳細(xì).

Distinct

1旦MYSQL找到了與行相聯(lián)合匹配的行，就不再搜索了

Not exists

MYSQL優(yōu)化了LEFT JOIN，1旦它找到了匹配LEFT JOIN標(biāo)準(zhǔn)的行，
就不再搜索了

Range checked for each

Record（index map:#）

沒有找到理想的索引，因此對從前面表中來的每個行組合，MYSQL檢查使用哪一個索引，并用它來從表中返回行。這是使用索引的最慢的連接之1

Using filesort

看到這個的時候，查詢就需要優(yōu)化了。MYSQL需要進(jìn)行額外的步驟來發(fā)現(xiàn)如何對返回的行排序。它根據(jù)連接類型和存儲排序鍵值和匹配條件的全部行的行指針來排序全部行

Using index

列數(shù)據(jù)是從僅僅使用了索引中的信息而沒有讀取實際的行動的表返回的，這產(chǎn)生在對表的全部的要求列都是同1個索引的部份的時候

Using temporary

看到這個的時候，查詢需要優(yōu)化了。這里，MYSQL需要創(chuàng)建1個臨時表來存儲結(jié)果，這通常產(chǎn)生在對不同的列集進(jìn)行ORDER BY上，而不是GROUP BY上

Using where

使用了WHERE從句來限制哪些即將與下1張表匹配或是返回給用戶。如果不想返回表中的全部行，并且連接類型ALL或index，這就會產(chǎn)生，或是查詢有問題

MySQL履行計劃的局限

EXPLAIN不會告知你關(guān)于觸發(fā)器、存儲進(jìn)程的信息或用戶自定義函數(shù)對查詢的影響情況
EXPLAIN不斟酌各種Cache
EXPLAIN不能顯示MySQL在履行查詢時所作的優(yōu)化工作
部份統(tǒng)計信息是估算的，并不是精確值
EXPALIN只能解釋SELECT操作，其他操作要重寫為SELECT后查看履行計劃

Mysql主從配置

大型網(wǎng)站為了減緩大量的并發(fā)訪問，除在網(wǎng)站實現(xiàn)散布式負(fù)載均衡，遠(yuǎn)遠(yuǎn)不夠。到了數(shù)據(jù)業(yè)務(wù)層、數(shù)據(jù)訪問層，如果還是傳統(tǒng)的數(shù)據(jù)結(jié)構(gòu)，或只是單單靠1臺服務(wù)器扛，如此多的數(shù)據(jù)庫連接操作，數(shù)據(jù)庫必定會崩潰，數(shù)據(jù)丟失的話，后果更是不堪假想。這時候候，我們會斟酌如何減少數(shù)據(jù)庫的聯(lián)接，1方面采取優(yōu)秀的代碼框架，進(jìn)行代碼的優(yōu)化，采取優(yōu)秀的數(shù)據(jù)緩存技術(shù)如：memcached,如果資金豐富的話，必定會想到架設(shè)服務(wù)器群，來分擔(dān)主數(shù)據(jù)庫的壓力。利用MySQL主從配置，實現(xiàn)讀寫分離，減輕數(shù)據(jù)庫壓力。這類方式，在如今很多網(wǎng)站里都有使用，也不是甚么新鮮事情，在這邊總結(jié)1下也方便大家學(xué)習(xí)參考1下。

概述

搭設(shè)1臺Master服務(wù)器（centos6.6系統(tǒng)，ip：192.168.0.101），搭設(shè)兩臺Slave服務(wù)器（虛擬機(jī)centos 6.6系統(tǒng) ip:192.168.0.102, ip:192.168.0.103）
原理：主服務(wù)器（Master）負(fù)責(zé)網(wǎng)站NonQuery操作，從服務(wù)器負(fù)責(zé)Query操作，用戶可以根據(jù)網(wǎng)站功能模特性塊固定訪問Slave服務(wù)器，或自己寫個池或隊列，自由為要求分配從服務(wù)器連接。主從服務(wù)器利用MySQL的2進(jìn)制日志文件，實現(xiàn)數(shù)據(jù)同步。2進(jìn)制日志由主服務(wù)器產(chǎn)生，從服務(wù)器響應(yīng)獲得同步數(shù)據(jù)庫。

配置Master主服務(wù)器

創(chuàng)建repl用戶

在Master MySQL上創(chuàng)建1個用戶‘repl’，并允許其他Slave服務(wù)器可以通過遠(yuǎn)程訪問Master，通過該用戶讀取2進(jìn)制日志，實現(xiàn)數(shù)據(jù)同步。

create user repl; //創(chuàng)建新用戶 //repl用戶必須具有REPLICATION SLAVE權(quán)限，除此以外沒有必要添加沒必要要的權(quán)限，密碼為mysql。說明1下192.168.0.%，這個配置是指明repl用戶所在服務(wù)器，這里%是通配符，表示192.168.0.0⑴92.168.0.255的Server都可以以repl用戶登陸主服務(wù)器。固然你也能夠指定固定Ip。 mysql> GRANT REPLICATION SLAVE ON *.* TO 'repl'@'192.168.0.%' IDENTIFIED BY 'mysql';

開啟bin-long

找到mySQL安裝文件夾修改my.Ini文件。mySQL中有好幾種日志方式，這不是今天的重點。我們只要啟動2進(jìn)制日志log-bin就ok。
在[mysqld]下面增加下面幾行代碼

log-bin=master-bin log-bin-index=master-bin.index server-id=101

重啟服務(wù)后查看mySQL日志信息

配置Slave從服務(wù)器

修改my.cnf文件
關(guān)鍵在于下面幾行

[mysqld] init_connect='SET autocommit=0' server-id=102 log-bin=slave-bin log-bin-index=slave-bin.index

在mySQL層面建立MASTER-SLAVE的連接

第1步：重啟MASTER和SLAVE的兩個mySQL進(jìn)程

service mysqld restart

第2步：查看master狀態(tài)

登錄myaster mysql

記下這兩個值

第3步：在slave上建立指向master的連接

先暫停slave的mySQL Service，使用命令：

stop slave;

以上這步 log_file和 master_log_pos必須和當(dāng)前master上的show master status命令所顯示的值完全1模1樣，比如説我們現(xiàn)在加了1個slave，再加1個slave，再加第2個slave。都是如此操作。
啟動 slave的mySQL Service使用

start slave;

第4步：查看slave上的log

實驗master slave上的replic功能

第1步

在master上查看，有1張表叫user_info。然后在slave上查看，也有1張一樣的表

第2步

刪除master上的user_info表，視察slave上，該表也自動被刪除。

因此，你在master上做的任何操作，都會被自動同步至slave上。

mysql中master宕機(jī)后，將slave升級為master的進(jìn)程

1、從slave（slave1）大眾當(dāng)選定1個slave，準(zhǔn)備錢換成master；

3、停止slave1的slave進(jìn)程，并reset稱master：
mysql> STOP SLAVE ;
Query OK, 0 rows affected (0.00 sec)
mysql> RESET MASTER;
Query OK, 0 rows affected, 8 warnings (0.02 sec)

4、將slave 群中的其他slave（slave2）的Master切換成新的master（由原slave1 reset成的）：

1) 停slave進(jìn)程：
mysql> stop slave;
Query OK, 0 rows affected (0.00 sec)
2) 更換master：
mysql> CHANGE MASTER TO
MASTER_HOST=’10.0.65.106′,
MASTER_USER=’repl’,
MASTER_PASSWORD=’slavepass’
;
Query OK, 0 rows affected (0.00 sec)
3) 開啟slave：
mysql> start slave;
Query OK, 0 rows affected (0.00 sec)

5、檢查slave的狀態(tài)：
mysql> show slave status;

沒有宕機(jī)的情況下master和slave的線上熱切換步驟

1、必須使要被降級的master變成只讀狀態(tài)。

set global read_only=1

2、履行show master status，檢查并確認(rèn)已進(jìn)入只讀狀態(tài)。
3、履行stop slave停止與master的同步，履行reset slave消除與master的slave關(guān)系。
4、履行change master與另外一臺mysql建立slave關(guān)系。
5、依照《實驗master slave上的replic功能》小節(jié)中的內(nèi)容再把被降級的master做成1個新的slave掛到new master下便可。

生活不易，碼農(nóng)辛苦
如果您覺得本網(wǎng)站對您的學(xué)習(xí)有所幫助,可以手機(jī)掃描二維碼進(jìn)行捐贈
程序員人生

------分隔線----------------------------

上一篇 Leetcode 63 Unique Paths II

下一篇 HDU - 2041 超級樓梯

分享到: