一、常見數據庫的優化操作
1、表的設計要符合三范式。
2、添加適當的索引，索引對查詢速度影響很大，必須添加索引。主鍵索引，唯一索引，普通索引，全文索引
3、添加適當存儲過程，觸發器，事務等。
4、讀寫分離（主從數據庫）
5、對sql語句的一些優化，（查詢執行速度比較慢的sql語句）
6、分表分區
分表：把一張大表分成多張表。分區：把一張表裡面的分配到不同的區域存儲，
7、對mysql服務器硬件的升級操作。
二、適時提高效率
第一范式
原子性：表裡面的字段不能再分割，只要是關系型數據庫，就天然的自動滿足第一范式。
關系型數據庫：（有行和列的概念）mysql,sql server,oracle,db2,infomix,sybase,postgresql
在設計時，先有庫-》表-》字段-》具體記錄（內容）：在存儲數據時，要設計字段。
非關系型數據庫（泛指nosql數據庫）：memcache/redis/momgodb/等
一個表中沒有完全相同的記錄，通過一個主鍵即能解決。
表中不能存儲冗余數據，
慢查詢：找出在一個網站中，查詢速度比較慢的語句，可以開啟一個日志文件，記錄查詢速度比較慢的sql語句。在默認情況下，慢查詢日志是關閉的，默認記錄時間是超過10秒 的sql語句。
1、以記錄慢查詢的方式來啟動mysql,
先關閉mysql,進入到mysql的安裝目錄。
關閉mysql服務：可以通過計算機-》管理->服務-》mysqld的服務名稱，單擊停止。
mysql的安裝目錄
通過慢查詢日志定位執行效率較低的SQL語句。慢查詢日志記錄了所有執行時間超過long_query_time所設置的SQL語句。
2、查看慢查詢日志的存儲路徑。
開啟了慢查詢日志後，會建立一個慢查詢日志文件。該日志文件保存在數據庫的目錄下，數據庫的目錄可以通過配置文件查看。
慢查詢日志存放在mysql的data目錄下. 在my.ini配置文件有data目錄
#Pathto the database root
datadir="C:/Documentsand Settings/All Users/Application Data/MySQL/MySQL Server 5.5/Data/“
3、進程測試：
查看當前數據庫下慢查詢記錄時間：
show variables like ‘long_query_time’;
修改慢查詢時間：
set long_query_time=2;
通過如下的一個函數來進行測試：
benchmark(count,expr)函數可以測試執行count次expr操作需要的時間
一般情況下，出現查詢比較慢的語句，是沒有添加索引導致的。
建立的測試表：
create table user( id int primary key auto_increment, name varchar(32) not null default '', age tinyint unsigned not null default 0, email varchar(32) not null default '', classid int not null default 1 )engine myisam charset utf8; insert into uservalues(null,'xiaogang',12,'[email protected]',4), (null,'xiaohong',13,'[email protected]',2), (null,'xiaolong',31,'[email protected]',2), (null,'xiaofeng',22,'[email protected]',3), (null,'xiaogui',42,'[email protected]',3);
創建一個班級表：
create table class( id int not null default 0, classname varchar(32) not null default '' )engine myisam charset utf8; insert into classvalues(1,'java'),(2,'.net'),(3,'php'),(4,'c++'),(5,'ios');
1、主鍵索引
可以在建立表的添加createtable emp(id int primary key)
可以在建立完表之後，添加：altertable tablename add primary key(列1,列2)
主鍵索引的特點：
（1）一個表中最多只有一個主鍵索引
（2）一個主鍵索引可以指向多個列
（3）主鍵索引的列，不能有重復的值，也不能有null
（4）主鍵索引的效率高。
2、唯一索引
可以在建立表的時候添加：createtable emp(name varchar(32) unique)
在建完表之後，添加：
alter table tablename add unique [索引名](列名)
唯一索引的特點：
（1）一個表中可以有多個唯一索引
（2）一個唯一索引可以指向多個列 ，
比如alter tabletablename add unique [索引名](列1，列2)
（3）如果在唯一索引上，沒有指定not null，則該列可以為空，同時可以有多個null,
（4）唯一索引的效率較高。
3、普通索引
使用普通索引主要是提高查詢效率
添加alter table tablename add index [索引名]（列1，列3）
4、全文索引
mysql自帶的全文索引mysql5.5不支持中文，支持英文，同時要求表的存儲引擎是myisam。如果希望支持中文，有兩個方案，
（1）使用aphinx中文版coreseek (來替代全文索引)
（2）插件mysqlcft。
5、查看索引
（1）show index from 表名
（2）show indexes from 表名
（3）show keys from 表名
（4）desc 表名
6、刪除索引
（1）主鍵索引的刪除：
alter table tablename drop primary key;要注意：在刪除主鍵索引時，要首先去掉auto_increment屬性。
（2）唯一索引的刪除
alter table tablename drop index 唯一索引的名字
（3）普通索引的刪除：
alter table tablename drop index 普通索引的名字
7、添加索引主要的問題：
（1）較頻繁的作為查詢條件字段應該創建索引
select* from emp where empno = 1
唯一性太差的字段不適合單獨創建索引，即使頻繁作為查詢條件
select* from emp where sex = '男‘
更新非常頻繁的字段不適合創建索引
select* from emp where logincount = 1
（2）不會出現在WHERE子句中字段不該創建索
索引是由代價的，雖然是查詢速度提高了，但是，會影響增該刪的效率。而且索引文件會占用空間。
該工具能夠分析sql執行效率，但是並不執行sql語句。主要是查看sql語句是否用到索引。
語法：explain sql語句\G 或 desc sql語句\G
使用索引時：
沒有使用索引時：
explain工具的參數說明：
會產生如下信息：
select_type:表示查詢的類型。
table:輸出結果集的表
type:表示表的連接類型
possible_keys:表示查詢時，可能使用的索引
key:表示實際使用的索引
key_len:索引字段的長度
rows:掃描出的行數(估算的行數)
Extra:執行情況的描述和說明
1、對於創建的多列(復合)索引，只要查詢條件使用了最左邊的列，索引一般就會被使用。
mysql> alter table user add index (name,email); Query OK, 5 rows affected (0.08 sec) Records: 5 Duplicates: 0 Warnings: 0
該條件中使用了復合索引的最左邊的列，因此該查詢會使用到索引。
2、對於使用like的查詢，查詢如果是”%XXX”,不會使用到索引，‘XXX%’會使用到索引。
注意：在有些情況下，還是會用到like查詢，比如通過歌詞搜索歌名，通過劇情搜索電影名稱。借助於工具，sphinx裡面的coreseek軟件。
3、如果條件中有or，則要求or的索引字段都必須有索引，否則不能用到索引。
該email添加索引後，在測試，會用到索引
4、如果列類型是字符串，一定要在條件中將數據使用引號引用起來，否則不使用索引。
5、優化group by語句
默認情況下， mysql對所有的group by col1,col2進行排序。這與在查詢中指定order by col1,col2類型，如果查詢中包括group by 但用戶想要避免排序結果的消耗，則可以使用order by null禁止排序。
6、當取出的數據量超過表中數據的20%，優化器就不會使用索引，而是全表掃描。掃描的行數太多了，優化器認為全表掃描比索引來的塊。
7、查看索引的使用情況
注意：
handler_read_key:這個值越高越好，越高表示使用索引查詢到的次數。
handler_read_rnd_next:這個值越高，說明查詢低效。
8、對應大批量插入數據
對於MyISAM：
先禁用索引：
alter table table_name disable keys;
loading data//insert語句; 執行插入語句
執行完成插入語句後，開啟索引，統一添加索引。
alter table table_name enable keys;
對於Innodb：
1，將要導入的數據按照主鍵排序
2，setunique_checks=0,關閉唯一性校驗。
3，setautocommit=0,關閉自動提交。
比如執行如下操作：
（1）從數據庫中取出id值，
（2）進行加1操作。
（3）修改完成後，再保存到數據庫中。
比如原來 id的值為100,==》101
以上步驟執行100次，最後變成200
有兩個用戶同時執行的話。
a用戶：
100
101
b用戶：
100
101
通過鎖機制來進行解決
鎖機制：在執行時，只有一個用戶獲得鎖，其他用戶處於阻塞狀態，需要等待解鎖。
mysql 的鎖有以下幾種形式：
表級鎖：開銷小，加鎖快，發生鎖沖突的概率最高，並發度最低。myisam引擎屬於這種類型。
行級鎖：開銷大，加鎖慢，發生鎖沖突的概率最低，並發度也最高。innodb屬於這種類型。
1、表鎖的演示；
對myisam表的讀操作（加讀鎖），不會阻塞其他進程對同一表的讀請求，但會阻塞對同一表的寫請求。只有當讀鎖釋放後，才會執行其他進程的操作。
表添加讀鎖後，其他進程對該表只能查詢操作，修改時會被阻塞。
當前進程，能夠執行查詢操作，不能執行修改操作。不能對沒有鎖定的表進行操作。
鎖表的語法：
lock table 表名 read|write
也可以鎖定多個表，語法是：locktable 表1 read|wirte,表2 read|wirte
對myisam表的寫操作（加寫鎖），會阻塞其他進程對鎖定表的任何操作，不能讀寫，
表加寫鎖後，則只有當前進程對鎖定的表，可以執行任何操作。其他進程的操作會被阻塞。
2、行鎖的演示
innodb存儲引擎是通過給索引上的索引項加鎖來實現的，這就意味著：只有通過索引條件檢索數據，innodb才會使用行級鎖，否則，innodb使用表鎖。
行鎖的語法：
begin
sql語句
commit
開啟行鎖後，當前進程在針對某條記錄執行操作時，其他進程不能操作和當前進程相同id的記錄。
php裡面有文件鎖，在實際的項目中多數使用文件鎖，因為表鎖，會阻塞，當對一些表添加寫鎖後，其他進程就不能操作了。這樣會阻塞整個網站，會拖慢網站的速度。
類似的問題：
一件商品，庫存量還有一件，這時有兩個用戶同時請求下訂單，如何防止都下訂單成功，卻沒有貨發。
分表：把一個大表分成幾個小表：
垂直分割：
在dedecms裡面，垂直分割：
在一個數據庫中想要存儲各種數據，比如說文章數據，電影，音樂，商品數據，
內容主表+附加表：
內容主表：存儲各種數據的一些公共信息，比如數據的名稱，添加時間等，
可以使用多個附加表，附加表存儲一些數據的獨特的信息。
主要原因：是內容主表裡面的數據訪問比較頻繁。
水平分割：
通過id取模
就是把一個表存儲到磁盤不同區域，仍然是一張表。
1、基本的概念：
mysql5.1後有4種分區類型：
（1）Range（范圍）–這種模式允許將數據劃分不同范圍。例如可以將一個表通過年份劃分成若干個分區。
（2）List（預定義列表）–這種模式允許系統通過預定義的列表的值來對數據進行分割
（3）Hash（哈希）–這中模式允許通過對表的一個或多個列的Hash Key進行計算，最後通過這個Hash碼不同數值對應的數據區域進行分區。例如可以建立一個對表主鍵進行分區的表。
（4）Key（鍵值）-上面Hash模式的一種延伸，這裡的Hash Key是MySQL系統產生的。
2 range分區：
假如你創建了一個如下的表，該表保存有20家超市的職員記錄，這20家超市的編號從1到20.如果你想將其分成4個小分區，可以采用range分區，創建的數據表如下。
創建range分區語法：
create table emp(
id int not null,
namevarchar(32) not null default '' comment ‘職員的名稱’,
store_id int not null comment ‘超市的編號范圍是1-20’
)engine myisam charset utf8
partition by range(store_id)(
partition p0 values less than(6), //是store_id的值小於6的存儲區域。
partition p1 values less than(11), //是store_id的值大於等於6小於11的存儲區域。
partition p2 values less than(16),
partition p3 values less than(21)
)
insert into emp values(1,’哈哈’,1)--à數據是存儲到p0區
insert into emp values(23,’呵呵呵’,15)--à數據是存儲到p2區
insert into emp values(100,’嘻嘻嘻’,11)=à數據是存儲到p2區。
測試使用取出數據時是否用到分區：
在取出數據時，條件中必須partitionby range(store_id)，range裡面的字段。
3、list分區與range分區有類似的地方
例子：假如你創建一個如下的一個表，該表保存有20家超市的職員記錄，這20家超市的編號從1到20.而這20家超市分布在4個有經銷權的地區，如下表所示：
create table emp( id int not null, name varchar(32) not null default '', store_id int not null ) partition by list(store_id)( partition p0 values in(5,6,7,8), partition p1 values in(11,3,12,11), partition p2 values in(16), partition p3 values in(21) )
注意：在使用分區時，where後面的字段必須是分區字段，才能使用到分區。
4、分區表的限制
（1）只能對數據表的整型列進行分區，或者數據列可以通過分區函數轉化成整型列
（2）最大分區數目不能超過1024
（3）如果含有唯一索引或者主鍵，則分區列必須包含在所有的唯一索引或者主鍵在內
（4）按日期進行分區很非常適合，因為很多日期函數可以用。但是對於字符串來說合適的分區函數不太多 。
1、選擇合適的存儲引擎（myisam innodb）
? MyISAM:默認的MySQL存儲引擎。如果應用是以讀操作和插入操作為主，只有很少的更新和刪除操作，並且對事務的完整性要求不是很高。其優勢是訪問的速度快。(尤其適合論壇的帖子表)
? InnoDB：提供了具有提交、回滾和崩潰恢復能力的事務安全。但是對比MyISAM，寫的處理效率差一些並且會占用更多的磁盤空間(如果對安全要求高，則使用innodb)。[賬戶，積分]
? Memory/heap [一些訪問頻繁，變化頻繁，又沒有必要入庫的數據：比如用戶在線狀態]
? 說明: memory表的數據都在內存中，因此操作速度快，但是缺少是當mysql重啟後，數據丟失，但表的結構在.
? 注：從mysql5.5.x開始，默認的存儲引擎變更為innodb，innodb是為處理巨大數據量時擁有最大性能而設計的。它的 cpu效率可能是任何其他基於磁盤的關系數據庫引擎所不能匹敵的。
2、數據類型的選擇
（1）在精度要求高的應用中，建議使用定點數來存儲數值，以保證結果的准確性。decimal不要用float
（2）要用於存儲手機號，哪個類型比較合適。假如我們要用char(11),如果字符集是utf8 則占用多少個字節。11*3==33,如果是gbk字符集則占用11*2=22個字節，
如果用bigint型存儲，則占用8個字節，
（3）如果要存儲ip地址。假如用char(15)è占用很多字節，能否用整型來存儲呢？
可以通過一個函數，把ip地址轉換成整數。可以使用int來存儲
inet_aton()：把ip地址轉換成整數。
inet_ntoa()：把整數轉換成ip地址。
取出時：
（4）根據需求選擇最小整數類型。比如用戶在線狀態：離線，在線，離開，忙碌，隱式等，可以采用0,1,2,3,5來表示，沒有必要用char()或varchar()型來存儲字符串。
3、myisam表的定時維護
對於myisam 存儲引擎而言，需要定時執行optimize table 表名，通過optimize table語句可以消除刪除和更新造成的磁盤碎片，從而減少空間的浪費。
語法格式：optimizetable 表名
清理完成碎片之後會減少空間的浪費！
