mysql優(yōu)化

mysql優(yōu)化概述

前面我們講頁(yè)面靜態(tài)化，memcache是通過減少對(duì)mysql 操作來(lái)提升訪問速度，但是一個(gè)網(wǎng)站總是要操作數(shù)據(jù)庫(kù)，我們?nèi)绾翁嵘龑?duì)mysql的操作速度。
方針：
設(shè)計(jì)角度：存儲(chǔ)引擎的選擇，字段類型選擇，范式。
利用mysql自身的特性：索引，查詢緩存，分區(qū)分表，存儲(chǔ)過程，sql語(yǔ)句的優(yōu)化
部署大負(fù)載架構(gòu)體系：主從復(fù)制（讀寫分離）

存儲(chǔ)引擎的選擇

存儲(chǔ)引擎是什么？是數(shù)據(jù)庫(kù)的文件系統(tǒng)，是mysql數(shù)據(jù)庫(kù)服務(wù)器存儲(chǔ)數(shù)據(jù)的數(shù)據(jù)結(jié)構(gòu)，處于最底層的狀態(tài)。

001.jpg-31kB

innodb存儲(chǔ)引擎

從mysql5.5.x開始，默認(rèn)的存儲(chǔ)引擎變更為innodb引擎，支持事務(wù)ACID屬性（原子性一致性，隔離性，持久性），是為處理巨大數(shù)據(jù)量時(shí)擁有最大性能而設(shè)計(jì)的。它的cpu效率可能是任何其他基于磁盤的關(guān)系數(shù)據(jù)庫(kù)引擎所不能匹敵的。

數(shù)據(jù)存儲(chǔ)方式

表結(jié)構(gòu)，單獨(dú)是一個(gè)文件，文件名為 table.frm
表數(shù)據(jù)和表的索引是存儲(chǔ)到data目錄下面的 ibdata1里面的。

002.jpg-17.6kB

數(shù)據(jù)記錄的存儲(chǔ)是按照主鍵順序插入的

create table t1(
    id int primary key,
    name varchar(32)
)engine innodb charset utf8;

insert into t1 values(4,'xiaogang'),(2,'xiaolong'),(1,'dagang'),(3,'xiaofeng');

當(dāng)有大量數(shù)據(jù)插入時(shí)，會(huì)變慢，會(huì)影響插入效率，因?yàn)槭前凑罩麈I順序插入，要有一個(gè)排序的過程。

并發(fā)性

實(shí)現(xiàn)了行鎖，擅長(zhǎng)并發(fā)處理，不會(huì)影響其他行的操作

數(shù)據(jù)完整性

支持事務(wù)ACID屬性（原子性一致性，隔離性，持久性）

myisam存儲(chǔ)引擎

索引序列管理方法是indexed sequential access method(索引順序存取方法)的縮寫優(yōu)勢(shì)，在索引的處理上索引獨(dú)立存儲(chǔ)

數(shù)據(jù)存儲(chǔ)方式

表結(jié)構(gòu)、表數(shù)據(jù)、表索引是分別來(lái)存儲(chǔ)的。創(chuàng)建一個(gè)myisam引擎的表后，會(huì)形成三個(gè)文件

002.png-4.6kB

數(shù)據(jù)記錄的存儲(chǔ)是按照插入順序存儲(chǔ)的

create table t2(
    id int primary key,
    name varchar(32)
)engine myisam charset utf8;

insert into t2 values(4,'xiaogang'),(2,'xiaolong'),(1,'dagang'),(3,'xiaofeng');

并發(fā)性

實(shí)現(xiàn)的表鎖，不擅長(zhǎng)并發(fā)處理，鎖定整張表后，會(huì)影響其他的進(jìn)程操作該表

支持全文索引

在最新的mysql5.6以后，innodb引擎也支持全文索引了

memory

一些訪問頻繁，變化頻繁，又沒有必要入庫(kù)的數(shù)據(jù)，比如用戶在線狀態(tài)
memory(數(shù)據(jù)是存儲(chǔ)到內(nèi)存里面的，重啟mysql服務(wù)會(huì)丟失) 如果沒有memcached或者redis, 但是數(shù)據(jù)操作頻繁，可以考慮使用memory存儲(chǔ)引擎,比如好友在線狀態(tài)。適合做高速緩存。
查看存儲(chǔ)引擎：show engines;

002.png-11.5kB

查找需要優(yōu)化的sql語(yǔ)句

要對(duì)，執(zhí)行速度比較慢的sql語(yǔ)句進(jìn)行優(yōu)化，如何查找執(zhí)行速度比較慢的sql語(yǔ)句呢？

慢查詢?nèi)罩?/h2>

是一種mysql提供的日志，記錄所有執(zhí)行時(shí)間超過某個(gè)時(shí)間界限的sql的語(yǔ)句。這個(gè)時(shí)間界限，我們可以指定。在mysql中默認(rèn)沒有開啟慢查詢，即使開啟了，只會(huì)記錄執(zhí)行的sql語(yǔ)句超過10秒的語(yǔ)句。
開啟慢查詢?nèi)罩荆涸谂渲梦募衜y.ini文件，

002.png-33.9kB

該慢查詢?nèi)罩敬鎯?chǔ)的位置是：默認(rèn)是和數(shù)據(jù)表同一個(gè)目錄里面。

002.png-20kB

使用命令查看慢查詢?nèi)罩镜臅r(shí)間界限：
show variables like ‘long_query_time’

002.png-2.8kB

也可以通過命令，在當(dāng)前會(huì)話下重新設(shè)置慢查詢?nèi)罩镜臅r(shí)間界限。
set long_query_time=1;

002.png-7kB

測(cè)試慢查詢?nèi)罩荆?/h3>

使用benchmark(count,expr)函數(shù)可以測(cè)試執(zhí)行count次expr操作需要的時(shí)間。

002.png-2.5kB

打開慢查詢?nèi)罩镜奈募M(jìn)行查看：

002.png-11.3kB

使用mysql的profiles機(jī)制

該機(jī)制精確的記錄執(zhí)行sql語(yǔ)句的時(shí)間，精確到小數(shù)點(diǎn)后8位。
開啟profile機(jī)制
執(zhí)行 set profiling=1

002.png-1.4kB

002.png-4.1kB

002.png-1.4kB

002.png-8.6kB

索引的講解

索引就是，利用關(guān)鍵字的某些特性，快速定位數(shù)據(jù)的一種技術(shù)。

索引的分類

普通索引：
利用特定的關(guān)鍵字，標(biāo)識(shí)數(shù)據(jù)記錄的位置（磁盤上的位置，盤號(hào)，柱面，扇面，磁道）。
唯一索引：
限制索引的關(guān)鍵字不能重復(fù)的索引，數(shù)據(jù)字段內(nèi)容可以為null，一個(gè)表中可以有多個(gè)唯一索引。
主鍵索引：
限制索引的關(guān)鍵字不能重復(fù)，并且不能為NULL。（不能為NULL的唯一索引）。一個(gè)表中只允許有一個(gè)主索引。
全文索引：
索引的關(guān)鍵字，不是某個(gè)字段的值，而是字段值中有意義的詞來(lái)作為關(guān)鍵字建立索引。
復(fù)合索引，如果一個(gè)索引（以上四種任何都可以），是依賴于多個(gè)字段創(chuàng)建的化，稱之為復(fù)合索引。

創(chuàng)建索引的語(yǔ)法

//是在創(chuàng)建表時(shí)，直接創(chuàng)建索引。
create table index1(
    id int auto_increment comment '主鍵索引',
    name varchar(32)  comment '唯一索引',
    age int comment '普通索引',
    intro varchar(256) comment '全文索引',
    primary key (id),
    unique key (name),
    index (age),
    fulltext index (intro)
)engine myisam charset utf8;

//在創(chuàng)建表完成后，再修改表結(jié)構(gòu)創(chuàng)建索引。
create table index2(
    id int auto_increment comment '主鍵索引',
    name varchar(32)  comment '唯一索引',
    age int comment '普通索引'
)engine myisam charset utf8;

//添加索引
alter table index2 add unique key (name),
add index (age),
add fulltext index (intro);

查看索引

show index from table_name;
show indexes from table_name
desc table_name
show create table_name

002.png-10kB

刪除索引

刪除主鍵索引
alter table table_name drop primary key ;
在主鍵索引時(shí)，如果有auto_increment屬性，則不能直接刪除主鍵索引的，要先刪除auto_increment屬性，再刪除主鍵索引。

002.png-11.5kB

刪除非主鍵索引；
alter table table_name drop index 索引名稱

002.png-12.3kB

創(chuàng)建索引的注意事項(xiàng)

//（1）較頻繁的作為查詢條件字段應(yīng)該創(chuàng)建索引
select * from emp where empno = 1 
//（2）唯一性太差的字段不適合單獨(dú)創(chuàng)建索引，即使頻繁作為查詢條件
select * from emp where sex = '男' ;
//（3）更新非常頻繁的字段不適合創(chuàng)建索引
select * from emp where logincount = 1 
//（4）不會(huì)出現(xiàn)在WHERE子句中字段不該創(chuàng)建索 

索引的數(shù)據(jù)結(jié)構(gòu)

002.png-5.5kB

1、myisam引擎的索引的數(shù)據(jù)結(jié)構(gòu)。

索引的節(jié)點(diǎn)中存儲(chǔ)的是數(shù)據(jù)的物理地址（磁道和扇區(qū)）
在查找數(shù)據(jù)時(shí)，查找到索引后，根據(jù)索引節(jié)點(diǎn)中的物理地址，查找到具體的數(shù)據(jù)內(nèi)容。排好序的快速查找結(jié)構(gòu)

002.png-21.2kB

2、innodb引擎的索引結(jié)構(gòu)

innodb的主鍵索引文件上 直接存放該行數(shù)據(jù),稱為聚簇索引,非主索引指向?qū)χ麈I的引用（非主鍵索引的節(jié)點(diǎn)存儲(chǔ)是主鍵的id）

002.png-35.7kB

注意: innodb來(lái)說,
1: 主鍵索引 既存儲(chǔ)索引值,又在存儲(chǔ)行的數(shù)據(jù)
2: 如果沒有主鍵, 則會(huì)Unique key做主鍵
3: 如果沒有unique,則系統(tǒng)生成一個(gè)內(nèi)部的rowid做主鍵.
4: 像innodb中,主鍵的索引結(jié)構(gòu)中,既存儲(chǔ)了主鍵值,又存儲(chǔ)了行數(shù)據(jù),這種結(jié)構(gòu)稱為”聚簇索引”

explain(執(zhí)行計(jì)劃)工具使用

主要分析索引的使用情況，分析 當(dāng)前查詢是否用到了索引，索引效率如何。
語(yǔ)法：explain sql語(yǔ)句\G或desc sql語(yǔ)句\G

002.png-5.3kB

以下添加索引和沒有索引的對(duì)比情況。

002.png-12.9kB

type列：是指查詢的方式，非常重要，是分析“查數(shù)據(jù)過程”的重要依據(jù)。
可能的值：all index range ref const

all：

是掃描所有的數(shù)據(jù)行。

002.png-5.5kB

index:

比all性能稍好一點(diǎn)，是指要掃描所有的節(jié)點(diǎn)，即在索引文件中進(jìn)行查找，無(wú)需根據(jù)物理地址查找具體的數(shù)據(jù)。

索引覆蓋的查詢情況下，能利用上索引，但是又必須全索引掃描。

002.png-7.6kB

是利用索引來(lái)排序，但只能取出索引的列。

002.png-5.4kB

range:

002.png-5.5kB

意思是查詢時(shí)，能根據(jù)索引做范圍掃描，根據(jù)索引查找出一部分?jǐn)?shù)據(jù)。id>10000就決定了要查找出一部分?jǐn)?shù)據(jù)。

ref:

是指，通過索引列，可以直接引用到某些數(shù)據(jù)行

002.png-5kB

const,system,null

這3個(gè)分別指查詢優(yōu)化到常量級(jí)別，甚至不需要查找時(shí)間。
一般按照主鍵來(lái)查詢時(shí)，易出現(xiàn) const,system
或者直接查詢某個(gè)表達(dá)式，不經(jīng)過表時(shí)，出現(xiàn)null.

002.png-4.9kB

002.png-5.5kB

rows:

是指估計(jì)要掃描多少行。

extra:

using index :是指用到了索引覆蓋(直接在索引文件中查找數(shù)據(jù)，無(wú)需定位數(shù)據(jù)所在的實(shí)際位置)，效率非常高
using where:是指光靠索引定位不了，還得where判斷一下。
using temporary:是指用上了臨時(shí)表，group by 與order by不同列時(shí)，或grop by,order by 別的表的列。
using filesort:文件排序（文件可能在磁盤，也可能在內(nèi)存）

索引的使用細(xì)節(jié)

多列索引（復(fù)合索引）

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

002.png-10.9kB

002.png-9.3kB

因?yàn)榻M合索引是需要按順序執(zhí)行的，比如c1234組合索引，要想在c2上使用索引，必須先在c1上使用索引，要想在c3上使用索引，必須先在c2上使用索引，依此。

//假設(shè)某個(gè)表有一個(gè)聯(lián)合索引（c1,c2,c3,c4）
alter table table_name add index (c1,c2,c3,c4)
A where c1=x and c2=x and c4>x and c3=x 
B where c1=x and c2=x and c4=x order by c3 
C where c1=x and c4= x group by c3,c2 
D where c1=x and c5=x order by c2,c3 
E where c1=x and c2=x and c5=x  order by c2,c3 

例如：where cat_id=5 and shop_price>100.00;//查詢第5個(gè)欄目，100元以上的商品。
誤區(qū)：cat_id和shop_price上都加上索引。
只能用上cat_id或shop_price索引，因?yàn)槭仟?dú)立的索引，同時(shí)只能用上一個(gè)
可以創(chuàng)建一個(gè)cat_id和價(jià)格的復(fù)合索引。

對(duì)于使用like的查詢，查詢?nèi)绻恰?aaa”,不會(huì)使用到索引，‘a(chǎn)aa%’會(huì)使用到索引

002.png-9.8kB

比如根據(jù)歌詞查找歌曲名稱，根據(jù)電影劇情來(lái)查找電影名稱，該場(chǎng)合一般使用like ‘%’開頭的查詢，使用后面講的sphinx解決。

如果條件中有or，則要求or的索引字段都必須有索引，否則不能用到索引

比如id建立了主鍵索引，name建立的普通索引，進(jìn)行測(cè)試查詢

002.png-11.5kB

如果列類型是字符串，一定要在條件中將數(shù)據(jù)使用引號(hào)引用起來(lái)，否則不使用索引

002.png-10.1kB

優(yōu)化group by語(yǔ)句

默認(rèn)情況下， mysql對(duì)所有的group by col1,col2進(jìn)行排序。這與在查詢中指定order by col1,col2類型，如果查詢中包括group by 但用戶想要避免排序結(jié)果的消耗，則可以使用order by null禁止排序。
數(shù)據(jù)輸出的結(jié)果：

002.png-5.2kB

使用group by輸出結(jié)果，發(fā)現(xiàn)根據(jù)classid排序了。

002.png-3.3kB

002.png-5.7kB

002.png-3.6kB

當(dāng)取出的數(shù)據(jù)量超過表中數(shù)據(jù)的20%，優(yōu)化器就不會(huì)使用索引，而是全表掃描

002.png-10.8kB

查看索引的使用情況

show status like ‘Handler_read%’;
大家可以注意：
handler_read_key:利用索引獲得紀(jì)錄的次數(shù)。 
這個(gè)值越高越好，越高表示使用索引查詢到的次數(shù)。
handler_read_rnd_next:這個(gè)值越高，說明查詢低效

002.png-5.4kB

索引覆蓋

索引覆蓋是指：如果查詢的列恰好是索引的一部分，那么查詢只需要在索引文件上進(jìn)行，不需要回行到磁盤再找數(shù)據(jù)，這種查詢速度非常快，稱為“索引覆蓋”
比如使用name 建立索引，要查的字段是name,就用到了索引覆蓋。
比如使用index (name,age,email)建立的復(fù)合索引，要查到字段是name,age,email此時(shí)就用到了索引覆蓋。

002.png-5.1kB

002.png-4.3kB

前綴索引

利用字段數(shù)據(jù)的前部分作為索引，稱為前綴索引。目標(biāo)：減少索引長(zhǎng)度，提高索引效率。
比如password字段（32）如果用該字段建立索引，則索引的長(zhǎng)度為32*3=96,如果我們使用該密碼字段前若干個(gè)字符作為索引字段，就能查找出該字段數(shù)據(jù)。
比如使用password來(lái)舉例子，
在user表，添加10000行數(shù)據(jù)，

002.png-1.8kB

在 user表添加一個(gè)字段：

002.png-2.5kB

002.png-2.6kB

最后確定密碼字段前幾位用于創(chuàng)建索引。

002.png-3.7kB

前綴索引的語(yǔ)法：

002.png-7.9kB

對(duì)于做前綴不易區(qū)分的列，建立索引的技巧
如 ：url列 http://www.baidu.com http://www.sohu.com
列的前11個(gè)字符都是一樣的，不易區(qū)分，可以用如下2個(gè)辦法來(lái)解決。
（1）把列的內(nèi)容到過來(lái)存儲(chǔ)，并建立索引，
（2）偽哈希索引效果，同時(shí)存儲(chǔ)url_hash列
create table t8 (id int,url varchar(32),crcurl int unsigned)
可以對(duì)url字段使用crc32函數(shù)，存儲(chǔ)建立索引，

select * from user where name=’’ and password=’sfsdf’;