對mysql優化時一個綜合性的技術,主要包含

a: 表的設計合理化(符合3NF)

b: 添加適當索引(index) [四種: 普通索引、主鍵索引、唯一索引unique、全文索引]

c: 分表技術(程度分割、垂直分割)

d: 讀寫[寫: update/delete/add]分離

e: 存儲過程 [模塊化編程,可以提高速度]

f: 對mysql配置優化 [配置最年夜并發數my.ini, 調整緩存年夜小 ]

g: mysql服務器硬件升級

h: 定時的去清除不必要的數據,定時進行碎片整理(MyISAM)

u什么樣的表才是符合3NF (范式)

表的范式,是首先符合1NF, 能力滿足2NF , 進一步滿足3NF

1NF: 即表的列的具有原子性,弗成再分解,即列的信息,不能分解, 只有數據庫是關系型數據庫(mysql/oracle/db2/informix/sysbase/sql server),就自動的滿足1NF

? 數據庫的分類

關系型數據庫: mysql/oracle/db2/informix/sysbase/sql server

非關系型數據庫: (特點: 面向對象或者集合)

NoSql數據庫: MongoDB(特點是面向文檔)

2NF: 表中的記錄是唯一的, 就滿足2NF, 通常我們設計一個主鍵來實現

3NF: 即表中不要有冗余數據, 就是說,表的信息,如果能夠被推導出來,就不應該單獨的設計一個字段來存放. 好比下面的設計就是不滿足3NF:

反3NF : 但是,沒有冗余的數據庫未必是最好的數據庫,有時為了提高運行效率,就必須降低范式標準,適當保存冗余數據.具體做法是： 在概念數據模型設計時遵守第三范式,降低范式標準的工作放到物理數據模型設計時考慮.降低范式就是增加字段,允許冗余.

案例 ：

Sql語句自己的優化

問題是： 如何從一個年夜項目中,迅速的定位執行速度慢的語句. (定位慢查詢)

①首先我們了解mysql數據庫的一些運行狀態如何查詢(好比想知道當前mysql運行的時間/一共執行了多少次select/update/delete.. / 當前連接)

show status

常用的:

show status like ‘uptime’ ;

show stauts like ‘com_select’ show stauts like ‘com_insert’ ...類推 update delete

? show [session|global] status like .... 如果你不寫 [session|global] 默認是session 會話,指取出當前窗口的執行,如果你想看所有(從mysql 啟動到現在,則應該 global)

show status like ‘connections’;

//顯示慢查詢次數

show status like ‘slow_queries’;

②如何去定位慢查詢

構建一個年夜表(400 萬)-> 存儲過程構建

默認情況下,mysql認為10秒才是一個慢查詢.

l修改mysql的慢查詢.

show variables like ‘long_query_time’ ; //可以顯示當前慢查詢時間

set long_query_time=1 ;//可以修改慢查詢時間

構建大表->大表中記錄有要求, 記錄是不同才有用,不然測試效果和真實的相差大.

創立:

CREATE TABLE dept( /*部分表*/

deptno MEDIUMINT UNSIGNED NOT NULL DEFAULT 0, /*編號*/

dname VARCHAR(20) NOT NULL DEFAULT "", /*名稱*/

loc VARCHAR(13) NOT NULL DEFAULT "" /*地點*/

) ENGINE=MyISAM DEFAULT CHARSET=utf8 ;

CREATE TABLE emp

(empno MEDIUMINT UNSIGNED NOT NULL DEFAULT 0, /*編號*/

ename VARCHAR(20) NOT NULL DEFAULT "", /*名字*/

job VARCHAR(9) NOT NULL DEFAULT "",/*工作*/

mgr MEDIUMINT UNSIGNED NOT NULL DEFAULT 0,/*上級編號*/

hiredate DATE NOT NULL,/*入職時間*/

sal DECIMAL(7,2) NOT NULL,/*薪水*/

comm DECIMAL(7,2) NOT NULL,/*紅利*/

deptno MEDIUMINT UNSIGNED NOT NULL DEFAULT 0 /*部分編號*/

)ENGINE=MyISAM DEFAULT CHARSET=utf8 ;

CREATE TABLE salgrade

(

grade MEDIUMINT UNSIGNED NOT NULL DEFAULT 0,

losal DECIMAL(17,2) NOT NULL,

hisal DECIMAL(17,2) NOT NULL

)ENGINE=MyISAM DEFAULT CHARSET=utf8;

測試數據

INSERT INTO salgrade VALUES (1,700,1200);

INSERT INTO salgrade VALUES (2,1201,1400);

INSERT INTO salgrade VALUES (3,1401,2000);

INSERT INTO salgrade VALUES (4,2001,3000);

INSERT INTO salgrade VALUES (5,3001,9999);

為了存儲過程能夠正常執行,我們必要把命令執行結束符修改

delimiter $$

create function rand_string(n INT)

returns varchar(255) #該函數會返回一個字符串

begin

#chars_str定義一個變量 chars_str,類型是 varchar(100),默認值'abcdefghijklmnopqrstuvwxyzABCDEFJHIJKLMNOPQRSTUVWXYZ';

declare chars_str varchar(100) default

'abcdefghijklmnopqrstuvwxyzABCDEFJHIJKLMNOPQRSTUVWXYZ';

declare return_str varchar(255) default '';

declare i int default 0;

while i < n do

set return_str =concat(return_str,substring(chars_str,floor(1+rand()*52),1));

set i = i + 1;

end while;

return return_str;

end $$

如果希望在法式中使用,是Ok!

創立一個存儲過程

create procedure insert_emp(in start int(10),in max_num int(10))

begin

declare i int default 0;

#set autocommit =0 把autocommit設置成0

set autocommit = 0;

repeat

set i = i + 1;

insert into emp values ((start+i) ,rand_string(6),'SALESMAN',0001,curdate(),2000,400,rand_num());

until i = max_num

end repeat;

commit;

end $$

#調用剛剛寫好的函數, 1800000條記錄,從100001號開始

call insert_emp(100001,4000000);

③這時我們如果出現一條語句執行時間跨越1秒中,就會統計到.

④如果把慢查詢的sql記錄到我們的一個日志中

在默認情況下,我們的mysql不會記錄慢查詢,必要在啟動mysql時候,指定記錄慢查詢才可以

bin\mysqld.exe - -safe-mode - -slow-query-log [mysql5.5 可以在my.ini指定]

bin\mysqld.exe –log-slow-queries=d:/abc.log [低版本mysql5.0可以在my.ini指定]

先關閉mysql,再啟動, 如果啟用了慢查詢日志,默認把這個文件放在

my.ini 文件中記錄的位置

#Path to the database root

datadir="C:/Documents and Settings/All Users/Application Data/MySQL/MySQL Server 5.5/Data/"

⑤測試,可以看到在日志中就記錄下我們的mysql慢sql語句.

優化問題.

通過 explain 語句可以闡發,mysql如何執行你的sql語句, 這個工具的使用放一下,一會說.

添加索引 【小建議: 】

u四種索引(主鍵索引/唯一索引/全文索引/普通索引)

1.添加

1.1主鍵索引添加

當一張表,把某個列設為主鍵的時候,則該列便是主鍵索引

create table aaa

(id int unsigned primary key auto_increment ,

name varchar(32) not null defaul ‘’);

這是id 列便是主鍵索引.

如果你創立表時,沒有指定主鍵索引,也可以在創立表后,在添加, 指令:

alter table 表名 add primary key (列名);

舉例:

create table bbb (id int , name varchar(32) not null default ‘’);

alter table bbb add primary key (id);

1.2普通索引

一般來說,普通索引的創立,是先創立表,然后在創立普通索引

好比:

create table ccc(

id int unsigned,

name varchar(32)

)

create index 索引名 on 表 (列1,列名2);

1.3創立全文索引

全文索引,主要是針對對文件,文本的檢索, 好比文章, 全文索引針對MyISAM有用.

創立 ：

CREATE TABLE articles (

id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,

title VARCHAR(200),

body TEXT,

FULLTEXT (title,body)

)engine=myisam charset utf8;

INSERT INTO articles (title,body) VALUES

('MySQL Tutorial','DBMS stands for DataBase ...'),

('How To Use MySQL Well','After you went through a ...'),

('Optimizing MySQL','In this tutorial we will show ...'),

('1001 MySQL Tricks','1. Never run mysqld as root. 2. ...'),

('MySQL vs. YourSQL','In the following database comparison ...'),

('MySQL Security','When configured properly, MySQL ...');

如何使用全文索引:

差錯用法:

select * from articles where body like ‘%mysql%’; 【不會使用到全文索引】

證明:

explain select * from articles where body like ‘%mysql%’

正確的用法是:

select * from articles where match(title,body) against(‘database’); 【可以】

? 闡明:

1.在mysql中fulltext 索引只針對 myisam生效

2.mysql本身提供的fulltext針對英文生效->sphinx (coreseek) 技術處理中文

3.使用辦法是 match(字段名..) against(‘關鍵字’)

4.全文索引一個 叫 停止詞, 因為在一個文本中,創立索引是一個無窮大的數,因此,對一些常用詞和字符,就不會創立,這些詞,稱為停止詞.

1.4唯一索引

①當表的某列被指定為unique約束時,這列便是一個唯一索引

create table ddd(id int primary key auto_increment , name varchar(32) unique);

這時, name 列便是一個唯一索引.

unique字段可以為NULL,并可以有多NULL, 但是如果是具體內容,則不克不及重復.

主鍵字段,不克不及為NULL,也不克不及重復.

②在創立表后,再去創立唯一索引

create table eee(id int primary key auto_increment, name varchar(32));

create unique index 索引名 on 表名 (列表..);

2.查詢索引

desc 表名 【該辦法的缺點是：　不能夠顯示索引名.】

show index(es) from 表名

show keys from 表名

3.刪除

alter table 表名 drop index 索引名;

如果刪除主鍵索引.

alter table 表名 drop primary key [這里有一個小問題]

4.修改

先刪除,再重新創立.

u為什么創立索引后,速度就會變快?

原理示意圖:

.

u索引使用的注意事項

索引的代價:

1.占用磁盤空間

2.對dml操作有影響,變慢

u在哪些列上適合添加索引?

總結: 滿足以下條件的字段,才應該創立索引.

a: 肯定在where條常常使用 b: 該字段的內容不是唯一的幾個值(sex) c: 字段內容不是頻繁變化.

u使用索引的注意事項

把dept表中,我增加幾個部分:

alter table dept add index my_ind (dname,loc); // dname 左邊的列,loc便是右邊的列

闡明,如果我們的表中有復合索引(索引作用在多列上), 此時我們注意:

1,對于創立的多列索引,只要查詢條件使用了最左邊的列,索引一般就會被使用.

explain select * from dept where loc='aaa'\G

就不會使用到索引

2,對于使用like的查詢,查詢如果是‘%aaa’ 不會使用到索引

‘aaa%’ 會使用到索引.

好比: explain select * from dept where dname like '%aaa'\G

不能使用索引,即,在like查詢時,關鍵的 ‘關鍵字’ , 最前面,不能使用 % 或者 _這樣的字符., 如果必定要前面有變化的值,則考慮使用 全文索引->sphinx.

3.如果條件中有or,即使其中有條件帶索引也不會使用.換言之,就是要求使用的所有字段,都必需建立索引, 我們建議大家盡量避免使用or 關鍵字

select * from dept where dname=’xxx’ or loc=’xx’ or deptno=45

4.如果列類型是字符串,那一定要在條件中將數據使用引號引用起來.否則不使用索引.(添加時,字符串必須’’), 也就是,如果列是字符串類型,就一定要用 ‘’ 把他包含起來.

5.如果mysql估計使用全表掃描要比使用索引快,則不使用索引.

explain 可以贊助我們在不真正執行某個sql語句時,就執行mysql怎樣執行,這樣利用我們去分析sql指令.

u如何查看索引使用的情況:

show status like ‘Handler_read%’;

年夜家可以注意：

handler_read_key:這個值越高越好,越高表現使用索引查詢到的次數.

handler_read_rnd_next:這個值越高,闡明查詢低效.

usql語句的小技巧

1.在使用group by 分組查詢是,默認分組后,還會排序,可能會降低速度.

好比:

在group by 后面增加 order by null 就可以防止排序.

2.有些情況下,可以使用連接來替代子查詢.因為使用join,MySQL不必要在內存中創建臨時表.

select * from dept, emp where dept.deptno=emp.deptno; [簡單處置方式]

select * from dept left join emp on dept.deptno=emp.deptno; [左外連接,更ok!]

u如何選擇mysql的存儲引擎

在開發中,我們常常使用的存儲引擎 myisam / innodb/ memory

myisam 存儲: 如果表對事務要求不高,同時是以查詢和添加為主的,我們考慮使用myisam存儲引擎. ,好比 bbs 中的 發帖表,回復表.

INNODB 存儲: 對事務要求高,保留的數據都是重要數據,我們建議使用INNODB,比如訂單表,賬號表.

問 MyISAM 和 INNODB的區別

1. 事務平安

2. 查詢和添加速度

3. 支持全文索引

4. 鎖機制

5. 外鍵 MyISAM 不支持外鍵, INNODB支持外鍵. (在PHP開發中,通常不設置外鍵,通常是在法式中保證數據的一致)

Memory 存儲,好比我們數據變化頻繁,不需要入庫,同時又頻繁的查詢和修改,我們考慮使用memory, 速度極快.

u如果你的數據庫的存儲引擎是myisam,請必定記住要定時進行碎片整理

舉例闡明:

create table test100(id int unsigned ,name varchar(32))engine=myisam;

insert into test100 values(1,’aaaaa’);

insert into test100 values(2,’bbbb’);

insert into test100 values(3,’ccccc’);

我們應該定義對myisam進行整理

optimize table test100;

mysql_query(“optimize tables $表名”);

技術便是窗戶紙.->經常和技術好人.

uPHP定時完成數據庫的備份

①手動備份數據庫(表的)辦法

cmd控制臺:

mysqldump –u root –proot 數據庫 [表名1 表名2..] > 文件路徑

好比: 把temp數據庫備份到 d:\temp.bak

mysqldump –u root –proot temp > d:\temp.bak

如果你希望備份是,數據庫的某幾張表

mysqldump –u root –prot temp dept > d:\temp.dept.bak

如何使用備份文件恢復我們的數據.

mysql控制臺

source d:\temp.dept.bak

②使用定時器來自定完成

把備份數據庫的指令,寫入到 bat文件, 然后通過任務管理器去定時調用 bat文件.

mytask.bat 內容是:

C:\myenv\mysql5.5.27\bin\mysqldump -u root -proot temp dept > d:\temp.dept.bak

? 如果你的mysqldump.exe文件路徑有空格,則一定要使用 “” 包含.

把mytask.bat 做成一個任務,并定時調用在 2:00 調用一次

步調 任務計劃->增加一個任務,選中你的mytask.bat文件 ,最后配置:

測試ok

現在問題是,每次都是覆蓋本來的備份文件,不利用我們分時間段進行備份, 我們可以這樣處理; 示意圖:

代碼是:

mytask2.bat 內容:

C:\myenv\php-5.3.5\php.exe C:\myenv\apache\htdocs\mytask.php

mytask.php代碼:

<?php

//定時備份我們的數據庫文件

date_default_timezone_set('PRC');

$bakfilename=date("YmdHis",time());

$command="C:\myenv\mysql5.5.27\bin\mysqldump -u root -proot temp dept > d:\\{$bakfilename}";

exec($command);

最后測試ok!

作用是,寫一個數據庫, 數據庫中有三張表,然后每天 2:00 備份一次,文件名以時間來命名. 測試.

u使用PHP完成定時發送郵件的功能

①看一個實際的需求

②設計一張郵件表

create table maillist

(id int unsigned primary key auto_increment,

getter varchar(64) not null default '',

sender varchar(64) not null default '',

title varchar(32) not null default '',

content varchar(2048) not null default '',

sendtime int unsigned not null default 0,

flag tinyint unsigned not null default 0)engine=myisam charset utf8;

insert into maillist values(null,'hsp@itcast.cn','hanshunping@tsinghua.org.cn','hello100','abc hello',unix_timestamp()+10*3600,0);

insert into maillist values(null,'hsp@itcast.cn','hanshunping@tsinghua.org.cn','hello200','abc hello200',unix_timestamp()+10*3600,0);

③寫代碼

1.怎樣可以定時的去檢索哪些郵件該發送., 只能每隔必定時間(1min)就看看哪些郵件該發送, mailtask.php

2.上面的代碼是模擬發送郵件,看看如何真正發送郵件.

在PHP中,有一個函數 mail , 是用于發送郵件,我們實際上可以使用 PHPMailer 類,我們使用他完成.

l要正確的使用PHPMailer 發送郵件,必要滿足如下條件

1.自己機器是可以聯網的

2.需要搭建本身的smtp郵件服務器->示意圖

3.搭建本身的郵件服務器.

卸載.

安裝時傻瓜式的,一步一步的走ok

配置 ：

3.1選擇access數據庫

3.2

3.3配置郵件服務器

點擊設置->郵箱域名設置

點擊設置->服務器設置

設置一個賬號(試用版本只能設置5個賬號)

代碼:

<?php

// 練習用PHPmailer發送郵件

require('./PHPMailer/class.phpmailer.php');

$mailer = new PHPMailer();

/*

from 來自于誰

to :寄給誰

cc : 抄送

subject: 郵件主題

Body: 郵件正文

// 發送怎么發 ?

*/

$cont = <<<EMAIL

hello,world yyy!;

EMAIL;

// echo $cont;exit;

$mailer->CharSet = 'utf-8';

$mailer->ContentType = 'text/html'; // 設置內容類型為html,這樣charset能力發揮作用

$mailer->Encoding = 'base64'; // 防止服務器中繼時,服務器能接收的編碼紛歧致,帶來問題.

$mailer->From = 'shunping@192.168.1.152';

$mailer->FromName = '順平';

$mailer->Subject = '一份問候,你好,word';

$mailer->Body = $cont;

// 設置一下語言包

$mailer->SetLanguage('zh_cn');

// 增加收件人地址

// $mailer->AddAddress('328268186@qq.com','saozi');

$mailer->AddAddress('hanshunping@tsinghua.org.cn','shunping');

if($mailer->Send()) {

echo '發送okok';

} else{

echo 'fail ';

}

配置 php.ini 啟用賬號 shunping@192.168.1.152

[mail function]

; For Win32 only.

; http://php.net/smtp

SMTP = localhost

; http://php.net/smtp-port

smtp_port = 25

; For Win32 only.

; http://php.net/sendmail-from

sendmail_from = shunping@192.168.1.152

測試一把 勝利!

如安在linux下完成定時任務:

linux如何備份.

1. 直接執行PHP腳本, 必要在同一個服務器上執行.

# crontab -e

00 * * * * /usr/local/bin/php /home/htdocs/phptimer.php

2.通過HTTP哀求來觸發腳本, PHP文件允許不在同一服務器上

# crontab -e

00 * * * * /usr/bin/wget -q -O temp.txt http://www.phptimer.com/phptimer.php

上面是通過wget來哀求PHP文件, PHP輸出會保存在臨時文件temp.txt中

# crontab -e

00 * * * * /usr/bin/curl -o temp.txt http://www.phptimer.com/phptimer.php

上面是通過curl -o來哀求PHP文件, PHP輸出會保存在臨時文件temp.txt中

# crontab -e

00 * * * * lynx -dump http://www.phptimer.com/phptimer.php

上面是通過Lynx文本瀏覽器來哀求PHP文件

n分表技術

分表技術有(程度分割和垂直分割)

當一張越來越年夜時候,即使添加索引還慢的話,我們可以使用分表

以qq用戶表來具體的闡明一下分表的操作.

思路如圖 ：

首先我創建三張表 user0 / user1 /user2 , 然后我再創建 uuid表,該表的作用便是提供自增的id,

走代碼:

create table user0(

id int unsigned primary key ,

name varchar(32) not null default '',

pwd varchar(32) not null default '')

engine=myisam charset utf8;

create table user1(

id int unsigned primary key ,

name varchar(32) not null default '',

pwd varchar(32) not null default '')

engine=myisam charset utf8;

create table user2(

id int unsigned primary key ,

name varchar(32) not null default '',

pwd varchar(32) not null default '')

engine=myisam charset utf8;

create table uuid(

id int unsigned primary key auto_increment)engine=myisam charset utf8;

編寫addUser.php

<?php

//注冊一個用戶

$con=mysql_connect("localhost","root","root");

if(!$con){

die("連接失敗!");

}

mysql_select_db("temp",$con);

$name=$_GET['name'];

$pwd=$_GET['pwd'];

//這時我們先獲取用戶id,id是從uuid表獲取

$sql="insert into uuid values(null)";

if(mysql_query($sql,$con)){

$id=mysql_insert_id();

}

//計算表名,便是,你應該把這個用戶放入到哪個表

$talname='user'.$id%3;

$sql="insert into {$talname} values ($id,'$name','$pwd')";

if(mysql_query($sql,$con)){

echo '添加用戶到 '.$talname.'ok';

}

mysql_close($con);

//

<?php

//注冊一個用戶

$con=mysql_connect("localhost","root","root");

if(!$con){

die("連接失敗!");

}

mysql_select_db("temp",$con);

$id=intval($_GET['id']);

//計算表名

$tabname='user'.$id%3;

$sql="select pwd from {$tabname} where id=$id";

$res=mysql_query($sql,$con);

if($row=mysql_fetch_assoc($res)){

echo "在{$tabname}. 中發現 id號為 {$id}";

}

//.....

思考: 如果我們做的是一個安全保險公司的一個訂單(8999999999000000條)查詢功能更.

,如何處置海量表?->按時間.

1.分表的尺度是依賴業務邏輯(時間/地區/....)

2.安裝字符分歧. a-z

3.我們給用戶提供的查詢界面必定是有條件,不能讓用戶進行大范圍.(世界),如果需要的可以根據不同的規則,對應多套分表.

4.檢索時候,帶分頁條件,減少返回的數據.

5.項目中,靈活的根據需求來考慮.

n垂直分割

示意圖:

一句話: 如果一張表某個字段,信息量年夜,但是我們很少查詢,則可以考慮把這些字段,單獨的放入到一張表中,這種方式稱為垂直分割.