sql優化，sql優化的幾種方法
數據庫三范式
通俗地理解三個范式，對於數據庫設計大有好處。在數據庫設計中，為了更好地應用三個范式，就必須通俗地理解三個范式(通俗地理解是夠用的理解，並不是最科學最准確的理解)：
第一范式：1NF是對屬性的原子性約束，要求屬性具有原子性，不可再分解；
第二范式：2NF是對記錄的惟一性約束，要求記錄有惟一標識，即實體的惟一性；
第三范式：3NF是對字段冗余性的約束，即任何字段不能由其他字段派生出來，它要求字段沒有冗余。
沒有冗余的數據庫設計可以做到。但是，沒有冗余的數據庫未必是最好的數據庫，有時為了提高運行效率，就必須降低范式標准，適當保留冗余數據。具體做法是： 在概念數據模型設計時遵守第三范式，降低范式標准的工作放到物理數據模型設計時考慮。降低范式就是增加字段，允許冗余。
sql語句類型：
ddl(數據定義語言)：create ，alter ， drop
dml(數據操作語言)：insert ， delete ，update
select
dtl(數據事務語言)：rollback ，commit ，savepoint
dcl(數據控制語句)：grant ， revoke
show status命令
該命令可以顯示你的mysql數據庫的當前狀態，我們主要關心的是"Com"開頭的指令
show status like 'Com%' <=> show session status like 'Com%'//顯示當前控制台的狀況
show global status like 'Com%';//顯示數據庫從啟動到查詢的次數
重點關注 Com_select , Com_insert，Com_delete，Com_update，
show session status like 'Com_select';
顯示嘗試連接數據庫次數 show status like 'Connections';
服務器工作時長：show status like 'Uptime';(單位為妙)
慢查詢的次數：show status like 'Slow_queries';(默認是10 show variables like 'long_query_time';)
壓力測試腳本
創建表DEPT /*部門表*/
CREATE TABLE `dept` (
`deptno` mediumint(8) unsigned NOT NULL DEFAULT '0',
`dname` varchar(20) NOT NULL DEFAULT '',
`loc` varchar(13) NOT NULL DEFAULT ''
) ENGINE=MyISAM DEFAULT CHARSET=utf8;
創建表EMP /*員工表*/
CREATE TABLE `emp` (
`empno` int(11) NOT NULL DEFAULT '0',
`ename` varchar(20) NOT NULL DEFAULT '""',
`job` varchar(20) NOT NULL DEFAULT '“”',
`mgr` int(11) NOT NULL DEFAULT '0',
`hiredate` date NOT NULL,
`sal` decimal(10,0) NOT NULL,
`comm` decimal(10,0) NOT NULL,
`deptno` int(11) 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 $$ //新定義一個命令結束符
drop function rand_string$$
create function rand_string(n int)
returns varchar(255)
begin
declare chars_str varchar(100) default 'abcdefghijkmnopqrstuvwxyzABCDEFGHIJKMNOPQRSTUVWXYZ';
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$$
select rand_string(6);
//隨機產生部門編號
create function rand_num()
returns int(5)
begin
declare return_num int(5) default 0;
set return_num = floor(10+rand()*500);
return return_num;
end$$
select rand_num();
//向emp表中插入記錄（海量數據）
create procedure insert_emp(in start int(10),in max_num int(10))
begin
declare i int default 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$$
delimiter ;//將命令結束符改回為分號
//調用存儲過程
call insert_emp(100001,180000); //創建十八萬員工數據
delimiter $$
create procedure insert_dept(in start int(10),in max_num int(10))
begin
declare i int default 0;
set autocommit = 0;
repeat
set i = i + 1;
insert into dept values((start+i),rand_string(10),rand_string(8));
until i = max_num
end repeat;
commit;
end$$
delimiter ;
call insert_dept(100,10); //創建十個部門
如何在一個項目中，找到慢查詢的select，mysql數據庫支持把慢查詢語句記錄到日志中(默認情況下不啟動)
1.啟動mysql服務
進入到mysql安裝目錄
2.啟動xx>bin\mysqld.exe --slow-query-log
修改慢查詢時間set long_query_time=0.5;
show status like 'slow%';
建立索引是最優的sql優化方式
（1）能夠提高查詢效率，但是是以降低插入，刪除，更新效率為代價的，因為索引增加了大量的I/O
（2）會占用磁盤空間
explain ,可以對sql進行分析，預測sql的執行效率
explain select * from emp where empno = 123456\G;(\G縱向展示)
重點關注以下幾個字段：
type:
all -->全表查詢，要避免這樣的情況
system -->表僅有一行，這是const聯接類型的一個特例
const -->表最多有一行匹配
possible_keys : 可能用到的索引
key : 實際用到的索引
rows ： 從多少行數據中檢索出的結果
extra: （主要針對排序）
no tables : query語句中使用from dual 或不含任何from子句
using filesort : 當query語句需要排序(order by) ,且無法利用索引完成排序
using temporary : 某些操作必須使用臨時表，常見group by , order by
using where : 不用讀取表中的信息，僅通過索引就可以獲取到數據
索引分四種：
主鍵索引(primary) : 把某列設為索引，則就是主鍵索引
唯一索引(unique) : 該列具有唯一性，同時又是索引
普通索引(index) ： 普通索引
全文索引(fulltext) : //
綜合使用 --》復合索引
在哪些列上添加索引比較合適：
（1）較為頻繁的作為查詢條件字段應創建索引 select * from emp where emp.empno = '1';
（2）唯一性太差的字段不適合創建索引，即使頻繁作為過濾條件 select * from class where gender = '男';
（3）更新字段非常頻繁的字段不適合創建索引
（4）不會出現在where子句中字段不適合創建索引
alter table emp add primary key(emp);//添加主鍵索引
create [unique[FULLTEXT]] index 索引名 on 表名（列名...）
alter table 表名 add index [索引名]
alter table emp drop primary key;//刪除主鍵索引
drop index 索引名 on 表名;
alter table emp drop index 索引名;
如何查詢某表的所有索引：
show indexes from tablename;
show keys from tablename；
索引的使用（特殊情況）：
（1） 對於創建的多列索引，只要查詢條件使用了最左邊的列，索引一般會被使用。該說法是針對 alter table dept add index myindex (dname,loc);在這種情況下，索引會建在dname上，用loc去查詢的時候是沒有索引的；
（2） 對於使用like的查詢，%aaa是不用索引的，aaa%和aa%a都會用到索引；
（3） 如果條件中有or,則不會用到索引
（4） 如果加了索引的列類型是字符串，那一定要在條件中用引號引用起來才會用到索引，否則不用。
（5） 如果mysql的算法算出使用全表掃描會更快的話，那麼就不使用索引
如何檢測創建索引的是否有效：
show status like 'handler_read_key';這個值越高，說明使用索引查詢到的次數越多，索引有效
show status like 'handler_read_rnd_next';這個值越高，說明查詢效率越低
