MySQL數(shù)據(jù)庫優(yōu)化詳解

更新時(shí)間：2014年08月07日 17:42:43 投稿：hebedich

這篇文章主要介紹了MySQL數(shù)據(jù)庫優(yōu)化的方方面面，各種小技巧,需要的朋友可以參考下

mysql表復(fù)制

復(fù)制表結(jié)構(gòu)+復(fù)制表數(shù)據(jù)

mysql> create table t3 like t1;
mysql> insert into t3 select * from t1;

mysql索引

ALTER TABLE用來創(chuàng)建普通索引、UNIQUE索引或PRIMARY KEY索引
ALTER TABLE table_name ADD INDEX index_name (column_list)
ALTER TABLE table_name ADD UNIQUE (column_list)
ALTER TABLE table_name ADDPRIMARY KEY (column_list)
Create Index
CREATE INDEX index_name ON table_name (column_list)
CREATE UNIQUE INDEX index_name ON table_name (column_list)
drop index
DROP INDEX index_name ON talbe_name
alter table table drop
ALTER TABLE table_name DROP INDEX index_name
ALTER TABLE table_name DROP PRIMARY KEY

mysql視圖

創(chuàng)建視圖

mysql> create view v_t1 as select * from t1 where id>4 and id<11;
Query OK, 0 rows affected (0.00 sec)

view視圖的幫助信息

mysql> ? view
ALTER VIEW
CREATE VIEW
DROP VIEW

查看視圖

mysql> show tables;

刪除視圖v_t1

mysql> drop view v_t1;

mysql內(nèi)置函數(shù)

字符串函數(shù)

CONCAT (string2 [,… ]) //連接字串
LCASE (string2 ) //轉(zhuǎn)換成小寫
UCASE (string2 ) //轉(zhuǎn)換成大寫
LENGTH (string ) //string長度
LTRIM (string2 ) //去除前端空格
RTRIM (string2 ) //去除后端空格
REPEAT (string2 ,count ) //重復(fù)count次
REPLACE (str ,search_str ,replace_str ) //在str中用replace_str替換search_str
SUBSTRING (str , position [,length ]) //從str的position開始,取length個(gè)字符
SPACE(count) //生成count個(gè)空格

數(shù)學(xué)函數(shù)

BIN (decimal_number ) //十進(jìn)制轉(zhuǎn)二進(jìn)制
CEILING (number2 ) //向上取整
FLOOR (number2 ) //向下取整
MAX(num1 ,num2) //取最大值
MIN(num1,num2) //取最小值
SQRT(number2) //開平方
RAND() //返回0-1內(nèi)的隨機(jī)值

日期函數(shù)

CURDATE() //返回當(dāng)前日期
CURTIME() //返回當(dāng)前時(shí)間
NOW() //返回當(dāng)前的日期時(shí)間
UNIX_TIMESTAMP(date) //返回當(dāng)前date的UNIX日間戳
FROM_UNIXTIME() //返回UNIX時(shí)間戳的日期值
WEEK(date) //返回日期date為一年中的第幾周
YEAR(date) //返回日期date的年份
DATEDIFF(expr,expr2) //返回起始時(shí)間expr和結(jié)束時(shí)間expr2間天數(shù)

mysql預(yù)處理語句

設(shè)置stmt1預(yù)處理,傳遞一個(gè)數(shù)據(jù)作為一個(gè)where判斷條件

mysql> prepare stmt1 from 'select * from t1 where id>?';

設(shè)置一個(gè)變量
mysql> set @i=1;
執(zhí)行stmt1預(yù)處理
mysql> execute stmt1 using @i;
設(shè)置@i為5
mysql> set @i=5;
再次去執(zhí)行stmt1
mysql> execute stmt1 using @i;
如何刪除預(yù)處理stmt1
mysql> drop prepare stmt1;

mysql事務(wù)處理

--關(guān)閉自動(dòng)提交功能
mysql> set autocommit=0;
--從表t1中刪除了一條記錄
mysql> delete from t1 where id=11;
--此時(shí)做一個(gè)p1還原點(diǎn):
mysql> savepoint p1;
--再次從表t1中刪除一條記錄:
mysql> delete from t1 where id=10;
--再次做一個(gè)p2還原點(diǎn):
mysql> savepoint p2;
--此時(shí)恢復(fù)到p1還原點(diǎn)，當(dāng)然后面的p2這些還原點(diǎn)自動(dòng)會(huì)失效: 
mysql> rollback to p1;
--退回到最原始的還原點(diǎn):
mysql> rollback ;

mysql存儲(chǔ)

創(chuàng)建一個(gè)存儲(chǔ)p1()

mysql> \d //
mysql> create procedure p1()
-> begin
-> set @i=0;
-> while @i<10 do
-> select @i;
-> set @i=@i+1;
-> end while;
-> end;
-> //

執(zhí)行存儲(chǔ)p1()

mysql> \d ;
mysql> call p1();
--查看procedure p1()的status信息
mysql> show procedure status\G
--查看procedure p1()的具體信息:
mysql> show create procedure p1\G

mysql觸發(fā)器

修改delimiter為//
mysql> \d //
創(chuàng)建一個(gè)名字為tg1的觸發(fā)器,當(dāng)向t1表中插入數(shù)據(jù)時(shí)，就向t2表中插入一條數(shù)據(jù)
 
mysql> create trigger tg1 before insert on t1 for each ro
>begin
>insert into t2(id) values(new.id); 
>end//
--準(zhǔn)備兩個(gè)空表t1和t2
mysql> select * from t1;
mysql> select * from t2;
--向t1表中插入多條數(shù)據(jù):
mysql> insert into t1 values(1),(2),(3),(4);

如何制作刪除表t1后t2表中的記錄也會(huì)跟著刪除呢
mysql>\d //
mysql> create trigger tg2 beforedelete on t1 for each row 
>begin delete from t2 where id=old.id;
>end//
mysql>\d ;
如何制作更改表t1后t2表中的記錄跟著個(gè)性呢
mysql>\d //
mysql> create trigger tg3 beforeupdate on t1 for each row 
>begin update t2 set id=new.id where id=old.id; 
>end//
mysql>\d ;
查看觸發(fā)器
mysql> show triggers;

重排auto_increment值

MYSQL數(shù)據(jù)庫自動(dòng)增長的ID如何恢復(fù)，清空表的時(shí)候。不能用

delete from tablename;
而是要用：

truncatetable tablename;
這樣auto_increment 就恢復(fù)成1了

或者清空內(nèi)容后直接用ALTER命令修改表:

altertable tablename auto_increment =1;

利用GROUP BY的WITH ROLLUP

mysql> select * from demo; 
+-------+-------+ 
| cname | pname | 
+-------+-------+ 
| bj | hd | 
| bj | xc | 
| bj | hd | 
| sh | dh | 
| sh | rg | 
| sh | dh | 
+-------+-------+ 
9 rows in set (0.00 sec)

對(duì)demo表按照cname、pname列分組對(duì)pname列進(jìn)行聚合計(jì)算如下

mysql> select cname,pname,count(pname) from demo group by 
cname,pname;
+-------+-------+--------------+
| cname | pname | count(pname) |
+-------+-------+--------------+
| bj | hd | 3 |
| bj | xc | 2 |
| sh | dh | 3 |
| sh | rg | 1 |
+-------+-------+--------------+
4 rows in set (0.00 sec)

同樣使用with rollup關(guān)鍵字后，統(tǒng)計(jì)出更多的信息，如下。注意：with rollup不可以和ordery by同時(shí)使用

ysql> select cname,pname,count(pname) from demo group by cname,pname 
with rollup;
+-------+-------+--------------+
| cname | pname | count(pname) |
+-------+-------+--------------+
| bj | hd | 3 |
| bj | xc | 2 |
| bj | NULL | 5 |
| sh | dh | 3 |
| sh | rg | 1 |
| sh | NULL | 4 |
| NULL | NULL | 9 |
+-------+-------+--------------+
7 rows in set (0.00 sec)

使用外鍵需要注意的問題

創(chuàng)建外鍵的方式

mysql>create table temp( id int, name char(20), foreign key(id) 
references outTable(id) on delete cascade on update cascade)；

注意：Innodb類型的表支持外鍵，myisam類型的表，雖然創(chuàng)建外鍵可以成功，但是不起作用，主要原因是不支持外鍵。

優(yōu)化SQL語句的一般步驟

通過show status命令了解各種SQL的執(zhí)行頻率

mysql> show [session|global]status;

其中：session（默認(rèn)）表示當(dāng)前連接，global表示自數(shù)據(jù)庫啟動(dòng)至今

mysql>show status;
mysql>show global status;
mysql>show status like ‘Com_%';
mysql>show global status like ‘Com_%';

參數(shù)說明：

Com_XXX表示每個(gè)XXX語句執(zhí)行的次數(shù)如：
Com_select 執(zhí)行select操作的次數(shù)，一次查詢只累計(jì)加1
Com_update 執(zhí)行update操作的次數(shù)
Com_insert 執(zhí)行insert操作的次數(shù)，對(duì)批量插入只算一次。
Com_delete 執(zhí)行delete操作的次數(shù)
只針對(duì)于InnoDB存儲(chǔ)引擎的：

InnoDB_rows_read 執(zhí)行select操作的次數(shù)
InnoDB_rows_updated 執(zhí)行update操作的次數(shù)
InnoDB_rows_inserted 執(zhí)行insert操作的次數(shù)
InnoDB_rows_deleted 執(zhí)行delete操作的次數(shù)
其他：

connections 連接mysql的數(shù)量
Uptime 服務(wù)器已經(jīng)工作的秒數(shù)
Slow_queries:慢查詢的次數(shù)
定位執(zhí)行效率較低的SQL語句

explain select * from table where id=1000;
desc select * from table where id=1000;

通過EXPLAIN分析較低效SQL的執(zhí)行計(jì)劃

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中的第二個(gè)或者后面的查詢語句）、SUBQUERY（子查詢中的第一個(gè)SESECT）等
table: stu 輸出結(jié)果集的表
type: range 表示表的連接類型，性能有好到差：system（表僅一行）、const（只一行匹配）、eq_ref（對(duì)于前面的每一行使用主鍵和唯一）、ref（同eq_ref，但沒有使用主鍵和唯一）、ref_or_null（同前面對(duì)null查詢）、index_merge（索引合并優(yōu)化）、unique_subquery（主鍵子查詢）、index_subquery（非主鍵子查詢）、range（表單中的范圍查詢）、index（都通過查詢索引來得到數(shù)據(jù)）、all（通過全表掃描得到的數(shù)據(jù)）
possible_keys: name,ind_stu_name 表查詢時(shí)可能使用的索引。
key: name 表示實(shí)際使用的索引。
key_len: 50 索引字段的長度
ref: NULL
rows: 8 掃描行的數(shù)量
Extra: Using where; Using index 執(zhí)行情況的說明和描述

索引問題

MyISAM存儲(chǔ)引擎的表的數(shù)據(jù)和索引是自動(dòng)分開存儲(chǔ)的，各自是獨(dú)一的一個(gè)文件；InnoDB存儲(chǔ)引擎的表的數(shù)據(jù)和索引是存儲(chǔ)在同一個(gè)表空間里面，但可以有多個(gè)文件組成。MySQL目前不支持函數(shù)索引，但是能對(duì)列的前面某一部分進(jìn)行索引，例如name字段，可以只取name的前4個(gè)字符進(jìn)行索引，這個(gè)特性可以大大縮小索引文件的大小，用戶在設(shè)計(jì)表結(jié)構(gòu)的時(shí)候也可以對(duì)文本列根據(jù)此特性進(jìn)行靈活設(shè)計(jì)。

mysql>create index ind_company2_name on company2(name(4));

--其中company表名ind_company2_name索引名
MySQL如何使用索引
1、使用索引

(1)對(duì)于創(chuàng)建的多列索引，只要查詢的條件中用到最左邊的列，索引一般就會(huì)被使用。如下創(chuàng)建一個(gè)復(fù)合索引。

mysql>create index ind_sales2_com_mon on sales2(company_id,moneys);

然后按company_id進(jìn)行查詢，發(fā)現(xiàn)使用到了復(fù)合索引

mysql>explain select * from sales2 where company_id=2006\G

使用下面的查詢就沒有使用到復(fù)合索引。

mysql>explain select * from sales2 where moneys=1\G

(2) 使用like的查詢，后面如果是常量并且只有%號(hào)不在第一個(gè)字符，索引才可能會(huì)被使用，如下：

mysql> explain select * from company2 where name like "%3"\G 
*************************** 1. row *************************** 
id: 1 
select_type: SIMPLE 
table: company2 
type: ALL 
possible_keys: NULL 
key: NULL 
key_len: NULL 
ref: NULL 
rows: 1000 
Extra: Using where 
1 row in set (0.00 sec)

如下這個(gè)使用到了索引，而下面例子能夠使用索引，區(qū)別就在于“%”的位置不同，上面的例子是吧“%”放在了第一位，而下面的例子則沒有

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)如果對(duì)大的文本進(jìn)行搜索，使用全文索引而不使用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估計(jì)使用索引比全表掃描更慢，則不使用索引。例如如果列key_part1均勻分布在1到100之間，查詢時(shí)使用索引就不是很好

mysql>select * from table_name where key_part1>1 and key_part<90;

(2)如果使用MEMORY/HEAP表并且where條件中不使用“=”進(jìn)行索引列，那么不會(huì)用到索引。Heap表只有在“=”的條件下會(huì)使用索引。

(3)用or分割開的條件，如果or前的條件中的列有索引，而后面的列中沒有索引，那么涉及的索引都不會(huì)被用到。

mysql>show index from sales\G 
*************************** 1. row *************************** 
…… 
key_name: ind_sales_year 
seq_in_index：1 
Column_name: year 
……

從上面可以發(fā)現(xiàn)只有year列上面有索引。來看如下的執(zhí)行計(jì)劃。

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上面建有復(fù)合索引，但是由于money不是索引的第一列，那么在查詢中這個(gè)索引也不會(huì)被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 條件中l(wèi)ike的值的“%”在第一位了，那么MySQL也會(huì)采用這個(gè)索引。

(6)如果列類型是字符串，但在查詢時(shí)把一個(gè)數(shù)值型常量賦值給了一個(gè)字符型的列名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的值將很高，這個(gè)值代表了一個(gè)行被索引值讀的次數(shù)。

Handler_read_rnd_next的值高則意味著查詢運(yùn)行低效，并且應(yīng)該建立索引補(bǔ)救。

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)

兩個(gè)簡單實(shí)用的優(yōu)化方法

分析表的語法如下:(檢查一個(gè)或多個(gè)表是否有錯(cuò)誤)

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)

優(yōu)化表的語法格式：

OPTIMIZE [LOCAL | NO_WRITE_TO_BINLOG] TABLE tbl_name [,tbl_name]
如果已經(jīng)刪除了表的一大部分，或者如果已經(jīng)對(duì)含有可變長度行的表進(jìn)行了很多的改動(dòng)，則需要做定期優(yōu)化。這個(gè)命令可以將表中的空間碎片進(jìn)行合并，但是此命令只對(duì)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的優(yōu)化

大批量插入數(shù)據(jù)
當(dāng)用load命令導(dǎo)入數(shù)據(jù)的時(shí)候，適當(dāng)設(shè)置可以提高導(dǎo)入的速度。

對(duì)于MyISAM存儲(chǔ)引擎的表，可以通過以下方式快速的導(dǎo)入大量的數(shù)據(jù)。

ALTER TABLE tbl_name DISABLE KEYS
loading the data
ALTER TABLE tbl_name ENABLE KEYS
DISABLE KEYS 和ENABLE KEYS 用來打開或關(guān)閉MyISAM表非唯一索引的更新，可以提高速度，注意：對(duì)InnoDB表無效。

--沒有使用打開或關(guān)閉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

--使用打開或關(guān)閉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)

--以上對(duì)MyISAM表的數(shù)據(jù)導(dǎo)入，但對(duì)于InnoDB表并不能提高導(dǎo)入數(shù)據(jù)的效率

（1）針對(duì)于InnoDB類型表數(shù)據(jù)導(dǎo)入的優(yōu)化

因?yàn)镮nnoDB表的按照主鍵順序保存的，所以將導(dǎo)入的數(shù)據(jù)主鍵的順序排列，可以有效地提高導(dǎo)入數(shù)據(jù)的效率。

--使用test3.txt文本是按表film_test4主鍵存儲(chǔ)順序保存的

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）關(guān)閉唯一性效驗(yàn)可以提高導(dǎo)入效率

在導(dǎo)入數(shù)據(jù)前先執(zhí)行set unique_checks=0，關(guān)閉唯一性效驗(yàn)，在導(dǎo)入結(jié)束后執(zhí)行set unique_checks=1，恢復(fù)唯一性效驗(yàn)，可以提高導(dǎo)入效率。

--當(dāng)unique_checks=1時(shí)

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

--當(dāng)unique_checks=0時(shí)

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）關(guān)閉自動(dòng)提交可以提高導(dǎo)入效率

在導(dǎo)入數(shù)據(jù)前先執(zhí)行set autocommit=0，關(guān)閉自動(dòng)提交事務(wù)，在導(dǎo)入結(jié)束后執(zhí)行set autocommit=1，恢復(fù)自動(dòng)提交，可以提高導(dǎo)入效率。

--當(dāng)autocommit=1時(shí)

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

--當(dāng)autocommit=0時(shí)

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

優(yōu)化insert語句
盡量使用多個(gè)值表的insert語句，這樣可以大大縮短客戶與數(shù)據(jù)庫的連接、關(guān)閉等損耗。

可以使用insert delayed（馬上執(zhí)行）語句得到更高的效率。

將索引文件和數(shù)據(jù)文件分別存放不同的磁盤上。

可以增加bulk_insert_buffer_size 變量值的方法來提高速度，但是只對(duì)MyISAM表使用當(dāng)從一個(gè)文件中裝載一個(gè)表時(shí)，使用LOAD DATA INFILE。這個(gè)通常比使用很多insert語句要快20倍。

優(yōu)化group by語句
如果查詢包含group by但用戶想要避免排序結(jié)果的損耗，則可以使用使用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)

優(yōu)化嵌套查詢
下面是采用嵌套查詢的效果（可以使用更有效的鏈接查詢（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)

從執(zhí)行計(jì)劃中可以明顯看出查詢掃描的記錄范圍和使用索引的情況都有了很大的改善。連接（JOIN）子所以更有效率一些，是因?yàn)镸ySQL不需要再內(nèi)存中創(chuàng)建臨時(shí)表來完成這個(gè)邏輯上的需要兩個(gè)步驟的查詢工作。

數(shù)據(jù)庫優(yōu)化

優(yōu)化表的類型
在MySQL中，可以使用函數(shù)PROCEDUREANALYSE（）對(duì)當(dāng)前應(yīng)用的表進(jìn)行分析，改函數(shù)可以對(duì)數(shù)據(jù)表中列的數(shù)據(jù)類型提出優(yōu)化建議，用戶可以根據(jù)應(yīng)用的實(shí)際情況酌情考慮是否實(shí)施優(yōu)化。

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 *************************** 
……

大存儲(chǔ)量解決
1.分庫分表

2.分區(qū)

主要目的:

1.減少表的記錄數(shù)

2.減小對(duì)操作系統(tǒng)的負(fù)擔(dān)壓力

中間表
中間表的產(chǎn)生:
1.view 視圖
2.重新生成一個(gè)新表

Mysql服務(wù)器優(yōu)化

myisam讀鎖定
1.

lock table t1 read
2.開啟另一個(gè)mysql連接終端,接著去嘗試:

select * from t1
3.再insert、update和delete t1這張表，你會(huì)發(fā)現(xiàn)所有的數(shù)據(jù)都停留在終端上沒有真正的去操作

4.讀鎖定對(duì)我們?cè)谧鰝浞荽罅繑?shù)據(jù)時(shí)非常有用.

mysqldump -uroot -p123 test >test.sql
myisam寫鎖定
1.

lock table t1 write
2.打開另一個(gè)mysql終端,嘗試去select、insert、update和delete這張表t1,你會(huì)發(fā)現(xiàn)都不能操作，都會(huì)停留在終端上，只有等第一個(gè)終端操作完畢,第二個(gè)終端才能真正執(zhí)行.

3.可見表的寫鎖定比讀鎖定更嚴(yán)格

4.一般情況下我們很少去顯式的去對(duì)表進(jìn)行read 和write鎖定的,myisam會(huì)自動(dòng)進(jìn)行鎖定的.

Mysql服務(wù)器優(yōu)化

二進(jìn)制日志
1.log-bin=mysql-bin

查看bin-log日志:

mysql> show binary logs;
查看最后一個(gè)bin-log日志:

mysql> show master status;
慢查詢?nèi)罩?br /> 開戶和設(shè)置慢查詢時(shí)間:

vi /etc/my.cnf
log_slow_queries=slow.log
long_query_time=5
慢查詢次數(shù):

mysql> show global status like "%quer%"
socket問題
mysql socket無法登錄

1. 有時(shí)登錄mysql時(shí)提示不能用socket登錄，此時(shí)可以換成tcp方式去登錄，但是可以測(cè)試時(shí)可以這樣用，但是必須要在php去用之前把這個(gè)事情解決了.

[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是啟動(dòng)mysqld服務(wù)時(shí)產(chǎn)生的

root密碼丟失

root密碼丟失破解

1.service mysqld stop
2. mysqld_safe --skip-grant-tables --user=mysql & 
//跳過授權(quán)表mysql.user和mysql.db這些表
3. mysql -uroot
4. set password=password("wei"); 
//用這一條語句結(jié)果報(bào)錯(cuò),就是因?yàn)榧恿?-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"); 
//和第五步一樣,都可能成功修改密碼

您可能感興趣的文章: