優化SQL語句的一般步驟
通過show status指令了解各種SQL的執行頻率
mysql> show [session|global]status;
mysql>show status;
mysql>show global status;
mysql>show status like ‘Com_%';
mysql>show global status like ‘Com_%';
參數說明：
Com_XXX表示每個XXX語句執行的次數如：
Com_select 執行select操作的次數，一次查詢隻累計加1
Com_update 執行update操作的次數
Com_insert 執行insert操作的次數，對批量插入隻算一次。
Com_delete 執行delete操作的次數
隻針對于InnoDB存儲引擎的：
InnoDB_rows_read 執行select操作的次數
InnoDB_rows_updated 執行update操作的次數
InnoDB_rows_inserted 執行insert操作的次數
InnoDB_rows_deleted 執行delete操作的次數
其他：
connections 連接配接mysql的數量
Uptime 伺服器已經工作的秒數
Slow_queries:慢查詢的次數
mysql> show [session|global]status;
mysql>show status;
mysql>show global status;
mysql>show status like ‘Com_%';
mysql>show global status like ‘Com_%';
explain
select
*
from
table
where
id=1000;
desc
select
*
from
table
where
id=1000;
mysql> explain
select
count(*) from stu where name like
"a%"
\G
*************************** 1. row ***************************
id
: 1
select_type: SIMPLE
table: stu
type
: range
possible_keys: name,ind_stu_name
key: name
key_len: 50
ref: NULL
rows: 8
Extra: Using where; Using index
1 row
in
set
(0.
00 sec)
每一列的簡單解釋
id: 1
select_type: SIMPLE
表示select的類型，常見的取值有SIMPLE（）簡單表，即不使用表連接配接或者子查詢）、PRIMARY（主查詢，即外層的查詢）、
UNION（UNION中的第二個或者後面的查詢語句）、SUBQUERY（子查詢中的第一個SESECT）等
table: stu 輸出結果集的表
type: range
表示表的連接配接類型，性能有好到差：system（表僅一行）、const（隻一行比對）、eq_ref（對于前面的每一行使用主鍵和唯一）、ref（同
eq_ref，但沒有使用主鍵和唯一）、ref_or_null（同前面對null查詢）、index_merge（索引合并優化）、
unique_subquery（主鍵子查詢）、index_subquery（非主鍵子查詢）、range（表單中的範圍查詢）、index（都通過查
詢索引來得到資料）、all（通過全表掃描得到的資料）
possible_keys: name,ind_stu_name 表查詢時可能使用的索引。
key: name 表示實際使用的索引。
key_len: 50 索引字段的長度
ref: NULL
rows: 8 掃描行的數量
Extra: Using where; Using index 執行情況的說明和描述
索引問題
MyISAM存儲引擎的表的資料和索引是自動分開存儲的，各自是獨一的一個檔案；InnoDB存儲引擎的表的資料和索引是存儲在同一個表空間裡面，
但可以有多個檔案組成。MySQL目前不支援函數索引，但是能對列的前面某一部分進行索引，例如name字段，可以隻取name的前4個字元進行索引，這
個特性可以大大縮小索引檔案的大小，使用者在設計表結構的時候也可以對文本列根據此特性進行靈活設計。
mysql>create index ind_company2_name on company2(name(4));
--其中company表名ind_company2_name索引名
MySQL如何使用索引
1、使用索引
(1)對于建立的多列索引，隻要查詢的條件中用到最左邊的列，索引一般就會被使用。如下建立一個複合索引。
mysql>create index ind_sales2_com_mon on sales2(company_id,moneys);
mysql>explain
select
* from sales2 where company_id=2006\G
mysql>explain
select
* from sales2 where moneys=1\G
|
如下這個使用到了索引，而下面例子能夠使用索引，差別就在于“%”的位置不同，上面的例子是吧“%”放在了第一位，而下面的例子則沒有
mysql> explain
select
* from company2 where name like
"3%"
\G
*************************** 1. row ***************************
id
: 1
select_type: SIMPLE
table: company2
type
: range
possible_keys: ind_company2_name
key: ind_company2_name
key_len: 11
ref: NULL
rows: 103
Extra: Using where
1 row
in
set
(0.00 sec)
(3)如果對大的文本進行搜尋，使用全文索引而不使用like“%...%”.
(4)如果列名是索引，使用column_name is null将使用索引。如下
mysql> explain
select
* from company2 where name is null\G
*************************** 1. row ***************************
id
: 1
select_type: SIMPLE
table: company2
type
: ref
possible_keys: ind_company2_name
key: ind_company2_name
key_len: 11
ref: const
rows: 1
Extra: Using where
1 row
in
set
(0.00 sec)
存在索引但不使用索引
(1)如果MySQL估計使用索引比全表掃描更慢，則不使用索引。例如如果列key_part1均勻分布在1到100之間，查詢時使用索引就不是很好
mysql>
select
* from table_name where key_part1>1 and key_part<90;
(2)如果使用MEMORY/HEAP表并且where條件中不使用“=”進行索引列，那麼不會用到索引。Heap表隻有在“=”的條件下會使用索引。
(3)用or分割開的條件，如果or前的條件中的列有索引，而後面的列中沒有索引，那麼涉及的索引都不會被用到。
mysql>show index from sales\G
*************************** 1. row ***************************
……
key_name: ind_sales_year
seq_in_index：1
Column_name: year
……
從上面可以發現隻有year列上面有索引。來看如下的執行計劃。
mysql> explain
select
* from sales where year=2001 or country=‘China'\G
*************************** 1. row ***************************
id
: 1
select_type: SIMPLE
table: sales
type
: ALL
possible_keys: ind_sales_year
key: NULL
key_len: NULL
ref: NULL
rows: 12
Extra: Using where
1 row
in
set
(0.00 sec)
(4)如果不是索引列的第一部分，如下例子:可見雖然在money上面建有複合索引，但是由于money不是索引的第一列，那麼在查詢中這個索引也不會被MySQL采用。
mysql> explain
select
* from sales2 where moneys=1 \G
*************************** 1. row ***************************
id
: 1
select_type: SIMPLE
table: sales2
type
: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 1000
Extra: Using where
1 row
in
set
(0.00 sec)
(5)如果like是以%開始，可見雖然在name上面建有索引，但是由于where 條件中like的值的“%”在第一位了，那麼MySQL也會采用這個索引。
(6)如果列類型是字元串，但在查詢時把一個數值型常量指派給了一個字元型的列名name，那麼雖然在name列上有索引，但是也沒有用到。
mysql> explain
select
* from company2 where name name=294\G
*************************** 1. row ***************************
id
: 1
select_type: SIMPLE
table: company2
type
: ALL
possible_keys: ind_company2_name
key: NULL
key_len: NULL
ref: NULL
rows: 1000
Extra: Using where
1 row
in
set
(0.00 sec)
而下面的sql語句就可以正确使用索引。
mysql> explain
select
* from company2 where name name=‘294'\G
*************************** 1. row ***************************
id
: 1
select_type: SIMPLE
table: company2
type
: ref
possible_keys: ind_company2_name
key: ind_company2_name
key_len: 23
ref: const
rows: 1
Extra: Using where
1 row
in
set
(0.00 sec)
檢視索引使用情況
如果索引正在工作，Handler_read_key的值将很高，這個值代表了一個行被索引值讀的次數。
Handler_read_rnd_next的值高則意味着查詢運作低效，并且應該建立索引補救。
mysql> show status like
'Handler_read%'
;
+-----------------------+-------+
| Variable_name | Value |
+-----------------------+-------+
| Handler_read_first | 0 |
| Handler_read_key | 5 |
| Handler_read_next | 0 |
| Handler_read_prev | 0 |
| Handler_read_rnd | 0 |
| Handler_read_rnd_next | 2055 |
+-----------------------+-------+
6 rows
in
set
(0.00 sec)
兩個簡單實用的優化方法
分析表的文法如下:(檢查一個或多個表是否有錯誤)
mysql> CHECK TABLE tbl_name[,tbl_name] …[option] …option =
{ QUICK | FAST | MEDIUM| EXTENDED | CHANGED}
mysql> check table sales;
+--------------+-------+----------+----------+
| Table | Op | Msg_type | Msg_text |
+--------------+-------+----------+----------+
| sakila.sales | check | status | OK |
+--------------+-------+----------+----------+
1 row
in
set
(0.01 sec)
優化表的文法格式：
OPTIMIZE [LOCAL | NO_WRITE_TO_BINLOG] TABLE tbl_name [,tbl_name]
如果已經删除了表的一大部分，或者如果已經對含有可變長度行的表進行了很多的改動，則需要做定期優化。這個指令可以将表中的空間碎片進行合并，但是此指令隻對MyISAM、BDB和InnoDB表起作用。
mysql> optimize table sales;
+--------------+----------+----------+----------+
| Table | Op | Msg_type | Msg_text |
+--------------+----------+----------+----------+
| sakila.sales | optimize | status | OK |
+--------------+----------+----------+----------+
1 row
in
set
(0.05 sec)
常用SQL的優化
大批量插入資料
當用load指令導入資料的時候，适當設定可以提高導入的速度。
對于MyISAM存儲引擎的表，可以通過以下方式快速的導入大量的資料。
ALTER TABLE tbl_name DISABLE KEYS
loading the data
ALTER TABLE tbl_name ENABLE KEYS
DISABLE KEYS 和ENABLE KEYS 用來打開或關閉MyISAM表非唯一索引的更新，可以提高速度，注意：對InnoDB表無效。
--沒有使用打開或關閉MyISAM表非唯一索引：
mysql> load data infile ‘
/home/mysql/film_test
.txt'into table film_test2;
Query OK,529056 rows affected (1 min 55.12 sec)
Records:529056 Deleted:0 Skipped:0 Warnings:0
--使用打開或關閉MyISAM表非唯一索引：
mysql> alter table film_test2 disable keys;
Query OK,0 rows affected (0.0 sec)
mysql> load data infile ‘
/home/mysql/film_test
.txt'into table film_test2;
Query OK,529056 rows affected (6.34 sec)
Records:529056 Deleted:0 Skipped:0 Warnings:0
mysql> alter table film_test2
enable
keys;
Query OK,0 rows affected (12.25 sec)
--以上對MyISAM表的資料導入，但對于InnoDB表并不能提高導入資料的效率
（1）針對于InnoDB類型表資料導入的優化
因為InnoDB表的按照主鍵順序儲存的，是以将導入的資料主鍵的順序排列，可以有效地提高導入資料的效率。
--使用test3.txt文本是按表film_test4主鍵存儲順序儲存的
mysql> load data infile ‘
/home/mysql/film_test3
.txt'into table film_test4;
Query OK, 1587168 rows affected (22.92 sec)
Records:1587168 Deleted:0 Skipped:0 Warnings:0
--使用test3.txt沒有任何順序的文本（效率慢了1.12倍）
mysql> load data infile ‘
/home/mysql/film_test4
.txt'into table film_test4;
Query OK, 1587168 rows affected (31.16 sec)
Records:1587168 Deleted:0 Skipped:0 Warnings:0
（2）關閉唯一性效驗可以提高導入效率
在導入資料前先執行set unique_checks=0，關閉唯一性效驗，在導入結束後執行set unique_checks=1，恢複唯一性效驗，可以提高導入效率。
--當unique_checks=1時
mysql> load data infile ‘
/home/mysql/film_test3
.txt'into table film_test4;
Query OK,1587168 rows affected (22.92 sec)
Records:1587168 Deleted:0 Skipped:0 Warnings:0
--當unique_checks=0時
mysql> load data infile ‘
/home/mysql/film_test3
.txt'into table film_test4;
Query OK,1587168 rows affected (19.92 sec)
Records:1587168 Deleted:0 Skipped:0 Warnings:0
（3）關閉自動送出可以提高導入效率
在導入資料前先執行set autocommit=0，關閉自動送出事務，在導入結束後執行set autocommit=1，恢複自動送出，可以提高導入效率。
--當autocommit=1時
mysql> load data infile ‘
/home/mysql/film_test3
.txt'into table film_test4;
Query OK,1587168 rows affected (22.92 sec)
Records:1587168 Deleted:0 Skipped:0 Warnings:0
--當autocommit=0時
mysql> load data infile ‘
/home/mysql/film_test3
.txt'into table film_test4;
Query OK,1587168 rows affected (20.87 sec)
Records:1587168 Deleted:0 Skipped:0 Warnings:0
優化insert語句
盡量使用多個值表的insert語句，這樣可以大大縮短客戶與資料庫的連接配接、關閉等損耗。
可以使用insert delayed（馬上執行）語句得到更高的效率。
将索引檔案和資料檔案分别存放不同的磁盤上。
可以增加bulk_insert_buffer_size 變量值的方法來提高速度，但是隻對MyISAM表使用當從一個檔案中裝載一個表時，使用LOAD DATA INFILE。這個通常比使用很多insert語句要快20倍。
優化group by語句
如果查詢包含group by但使用者想要避免排序結果的損耗，則可以使用使用order by null來禁止排序：
如下沒有使用order by null來禁止排序
mysql> explain
select
id
,
sum
(moneys) from sales2 group by
id
\G
*************************** 1. row ***************************
id
: 1
select_type: SIMPLE
table: sales2
type
: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 1000
Extra: Using temporary;Using filesort
1 row
in
set
(0.00 sec)
如下使用order by null的效果：
mysql> explain
select
id
,
sum
(moneys) from sales2 group by
id
order by null\G
*************************** 1. row ***************************
id
: 1
select_type: SIMPLE
table: sales2
type
: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 1000
Extra: Using temporary
1 row
in
set
(0.00 sec)
優化嵌套查詢
下面是采用嵌套查詢的效果（可以使用更有效的連結查詢（Join）替代）。
mysql> explain
select
* from sales2 where company_id not
in
(
select
id
from company2)\G
*************************** 1. row ***************************
id
: 1
select_type: SIMPLE
table: sales2
type
: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 1000
Extra: Using where
1 row
in
set
(0.00 sec)
*************************** 2. row ***************************
id
: 2
select_type: SIMPLE
table: company2
type
: index_subquery
possible_keys: ind_company2_id
key: ind_company2_id
key_len: 5
ref: func
rows: 2
Extra: Using index
1 row
in
set
(0.00 sec)
下面是使用更有效的連結查詢（Join）
mysql> explain
select
* from sales2 left
join
company2 on
sales2.company_id = company2.
id
where sales2.company_id is null\G
*************************** 1. row ***************************
id
: 1
select_type: SIMPLE
table: sales2
type
: ALL
possible_keys: ind_sales2_companyid_moneys
key: ind_sales2_companyid_moneys
key_len: 5
ref: count
rows: 1
Extra: Using where
1 row
in
set
(0.00 sec)
*************************** 2. row ***************************
id
: 2
select_type: SIMPLE
table: company2
type
: index_subquery
possible_keys: ind_company2_id
key: ind_company2_id
key_len: 5
ref: func
rows: 1
Extra:
1 row
in
set
(0.00 sec)
從執行計劃中可以明顯看出查詢掃描的記錄範圍和使用索引的情況都有了很大的改善。連接配接（JOIN）子是以更有效率一些，是因為MySQL不需要再記憶體中建立臨時表來完成這個邏輯上的需要兩個步驟的查詢工作。
資料庫優化
優化表的類型
在MySQL中，可以使用函數PROCEDUREANALYSE（）對目前應用的表進行分析，改函數可以對資料表中列的資料類型提出優化建議，使用者可以根據應用的實際情況酌情考慮是否實施優化。
mysql>
select
* from duck_cust procedure analyse()\G
*************************** 1. row ***************************
Field_name: sakila.duch_cust.cust_num
Min_value: 1
Max_value: 6
Min_length: 1
Max_length: 1
Empties_or_zeros: 0
Nulls: 0
Avg_value_or_avg_length: 3.5000
Std: 1.7078
Optimal_fieldtype: ENUM(‘1
',‘2'
,‘3
',‘4'
) NOT NULL
*************************** 2. row ***************************
……
大存儲量解決
1.分庫分表
2.分區
主要目的:
1.減少表的記錄數
2.減小對作業系統的負擔壓力
中間表
中間表的産生:
1.view 視圖
2.重新生成一個新表
Mysql伺服器優化
myisam讀鎖定
1.
lock table t1 read
2.開啟另一個mysql連接配接終端,接着去嘗試:
select * from t1
3.再insert、update和delete t1這張表，你會發現所有的資料都停留在終端上沒有真正的去操作
4.讀鎖定對我們在做備份大量資料時非常有用.
mysqldump -uroot -p123 test >test.sql
myisam寫鎖定
lock table t1 write
2.打開另一個mysql終端,嘗試去select、insert、update和delete這張表t1,你會發現都不能操作，都會停留在終端上，隻有等第一個終端操作完畢,第二個終端才能真正執行.
3.可見表的寫鎖定比讀鎖定更嚴格
4.一般情況下我們很少去顯式的去對表進行read 和write鎖定的,myisam會自動進行鎖定的.
Mysql伺服器優化
二進制日志
1.log-bin=mysql-bin
檢視bin-log日志:
mysql> show binary logs;
檢視最後一個bin-log日志:
mysql> show master status;
慢查詢日志
開戶和設定慢查詢時間:
vi /etc/my.cnf
log_slow_queries=slow.log
long_query_time=5
慢查詢次數:
mysql> show global status like "%quer%"
socket問題
mysql socket無法登入
1. 有時登入mysql時提示不能用socket登入，此時可以換成tcp方式去登入，但是可以測試時可以這樣用，但是必須要在php去用之前把這個事情解決了.
[root@localhost mysql]
# mysql -uroot -pwei --protocol tcp -hlocalhost
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection
id
is 34
Server version: 5.0.77-log Source distribution
Type
'help;'
or
'\h'
for
help. Type
'\c'
to
clear
the buffer.
mysql>
這樣就可以登入,這樣就不用mysql.sock來登入，而mysql.sock是啟動mysqld服務時産生的
root密碼丢失
root密碼丢失破解
1.service mysqld stop
2. mysqld_safe --skip-grant-tables --user=mysql &
//跳過授權表mysql.user和mysql.db這些表
3. mysql -uroot
4. set password=password("wei");
//用這一條語句結果報錯,就是因為加了--skip-grant-tables
4. mysql>update user set password=password("wei") where user='root'
and host='localhost';
5. mysql> set password for root@localhost=password("wei");
6. mysql> set password=password("wei");
//和第五步一樣,都可能成功修改密碼
![資料遷移方法資料遷移原則資料遷移之雙寫方案資料遷移之級聯同步方案[圖]](data:image/gif;base64,R0lGODlhAQABAIAAAP///wAAACwAAAAAAQABAAACAkQBADs=)