MySQL效能最佳化（一）

標籤：mysql效能最佳化一

<pre name="code" class="sql">效能最佳化一共有6個點：    1.MySQL基礎操作    2.常用的SQL技巧    3.SQL語句最佳化    4.MySQL資料庫最佳化    5.Myisam表鎖    6.MySQL伺服器最佳化後期將逐步補全今天先分享下11.MySQL基礎操作一：MySQL基礎操作1：MySQL表複製複製表結構 + 複製表資料create table t3 like t1;  --建立一個和t1一樣的表，用like（表結構也一樣）insert into t3 select * from t1;  --t1的資料全部拿過來，注意是表結構一致才select* ，否則選擇相應的的欄位列插入create table t1(id int unsigned not null auto_increment primary key,name varchar(30));2：MySQL索引（create不能建立主鍵索引，得用alter，建議全部用alter建立索引）*設定主鍵後預設就是主鍵索引一：alter table用來建立普通索引，unique索引或primary key索引普通索引：alter table t1 add index in_name(name)   --t1表中的那麼欄位添加索引名為in_name唯一索引：alter table t1 add unique(name)   --不給名字，預設是欄位名  alter table t1 add unique un_name(name)  主鍵索引(自增才有意義)：alter table t1 add primary key(id)         主鍵索引不是自增記得改成自增：alter table t1 modify id int unsigned not null auto_increment；查看：show index from t1;刪除：alter table t1 drop index in_name;@*刪除主鍵索引單獨處理*二：alter table table_name drop index index_namealter型刪除索引:alter table t1 drop index in_name;@*刪除主鍵索引*：注意：刪除主鍵索引的時候，如果你的主鍵索引是自增（如：id）刪除是會報錯的  實在想玩的話就把主鍵的auto_increment消掉  alter table t1 modify id int unsigned not null;  接著刪除主鍵：alter table t1 drop primary key;    改成自增：alter table t1 modify id int unsigned not null auto_increment；三：create index（不常用學習下，不能對主鍵索引操作，只能操作普通和唯一索引）   *建立普通索引：create index in_name on t1(name);  --將t1表中的那麼欄位添加為普通索引查看索引：     show index from t1;刪除索引：      drop index in_name on t1;    --t1表中的in_name索引刪除    ->在做唯一索引之前欄位不能有重複值,否則建立不成功   *建立唯一索引：  create unique index un_name on t1(name);四：刪除create建立的索引：drop indexdrop index in_name on t1;3：MySQL視圖定義：視圖是一個虛擬表，其內容由查詢定義，是根據建立視圖的sql語句拿到的資料儲存在一張表中而建立的表-視圖  *根據從表裡面拿出來的資料而建立出來的一張表建立視圖：create view v_t1 as select * from t1 where id>4 and id<11;  作用：  如果 t1表裡的資料某條記錄被刪除了，那麼視圖v_t1表的對應資料也會刪除，類似主從（主表無則從無）  所以：視圖也可以充當一個中間表：查資料的時候可以不去查主t1 去查視圖表v_t1  *視圖表示依賴於，建立時sql的表t_name,如果表t_name損壞的了（刪除了），對應的視圖將會發生錯誤不能使用 查看視圖：show tables;刪除視圖：drop view v_t1;視圖協助資訊：？view;4：MySQL內建函數字串函數：selectconcat("hello","word");        連結字串 ->hello worldlcase("MYSQL")    轉換成小寫ucase("mysql")    轉換成大寫length("leyangjun")          string長度ltrim("   userName")           去除前端空格rtrim("userName   ")           去除後端空格repeat("linux",count)       重複count次( select repeat('d',2);重複輸出2次dd)replace(str,search_str,replace_str)  在str中使用replace_str替換search_strsubstring(str,position[length])      從str的position開始，取length個字串->substring 和 substr一樣select substr("leyangjun",1,5);      從第一個開始取5個字串space(count)            產生count（數字）個空格數學函數bin(decimal_number)         十進位轉二進位（select bin(120);）ceiling(number2)            向上取整(select ceiling(10.10);--->11)floor（number2）            向下取整(select ceiling(10.10);--->10)Max(列)              取最大值MIN(列)              取最小值sqrt(number2)               開平方rand()                      返回0-1內的隨機值日期函數：curdate();       返回當前日期curtime();       返回目前時間now();           返回當前的日期和時間unix_timestamp(date)   返回date的unix時間戳記from_unixtime()        返回unix時間戳記日期值week(date)             返回日期date為一年中的第幾周year(date)             返回日期中的年份datediff(expr,expr2)   返回起始時間expr和結束時間expr2間隔天數select datediff("2014-08-03","2014-08-04");5：MySQL預先處理語句一：設定一個預先處理語句：prepare stmt1 from 'select * from t1 where id>?';二：設定一個變數：set @=i1;三：執行stmt1預先處理：execute stmt1 using @i;設定@i=5set @i=5;execute stmt1 using @i;刪除預先處理：drop prepare stmt1;應用情境：比如你是老闆我要看1,2,3,4,5,6、、、、12月份入職人員的情況  *就可以把SQL做成預先處理，這樣就不需要每次去請求MySQL直接傳個值就可以（MySQL是將預先處理的SQL儲存起來，用的時候傳值直接就執行，就不需要每次請求串連MySQL在重新執行）6：MySQL交易處理（增刪改查後只要沒有commit，全可以復原）    *myisam引擎不支援事務，innodb（支援外鍵和事務）才支援事務修改表引擎方法：alter table t1 engine=innodb一：事務操作查看是否自動認可：select @@autocommit;關閉自動認可set autocommit=0;delete from t1 where id>5;  資料只是臨時刪除，如果commit就真正的執行刪除語句rollback;     只要沒commit還原剛才刪除的資料commit；二：還原點的使用：insert into t1 values("user4");savepoint p1;insert into t1 values("user5");savepoint p2;insert into t1 values("user6");savepoint p3;--3個資料已經插進去啦，能後你覺得user6不要，你就找到還原點 savepoint p2就行rollback to p2;    --還原到P2  user6不要commit；7：MySQL儲存（可以認為是自訂的函數）建立一個儲存：\d //create procedure p1()beginset @i=0;while @i<10 doinsert into t2(name) values(concat("user",@i));        --這裡可以做增刪改查。。。都行set @[email protected]+1;end while;end;//執行一個儲存：\d ;call p1();查看儲存：show procedure status;show create procedure p1\G   --查看p1儲存的基本資料8：MySQL觸發器（自動執行）*查詢沒有必要做觸發器！：select * from t1 union select * from t2;一：增加觸發器\d //建立一個名字為tg1的觸發器，當向表中插入資料時，就向t2表中插入一條資料create trigger tg1 before insert on t1 for each rowbegininsert into t2(id) values(new.id);    --new.id 比如向t1表裡面插入了id=4的    能後new.id=4 直接複製到這end//準備好t1 表 和 t1表向t1表中插入多條資料查看：show triggers；刪除觸發器：drop trigger t2;二：刪除觸發器(注意下：刪除的時候2個表的值一定要對稱，比如t1：1,2,3 t2：11,12,13這麼刪除是不行會報錯，以一定要對稱比如t1和t2表都有user1，這樣刪除就是沒問題 )\d //create trigger tg2 before delete on t1 for each rowbegin delete from t2 where id=old.id;   --插入的時候裡面沒有這個值叫new.id  提前有的值叫old.idend//三：更改觸發器：create trigger tg3 before update on t1 for each rowbegin update t2 set id=new.id where old.id;  --（update t1 set name="leyangjun"-new.id where name="zhangfei"-old.id）end//9：重排auto_increment值MySQL資料庫自動成長的ID如何恢複：清空表的時候，不能用delete from tableName;  而是用truncate tableName;這樣auto_increment就恢複成1了或者清空內容後直接用alter命令修改表：alter table tableName auto_increment=1;情境：1：t1表裡面有id欄位分別對應 1,2,3,4,5,6,7,8,9的記錄，    2：能後我們delete刪除，能後在插入資料，你會發現插入的時候是從10,11,12,13.....開始而不是13：清空的時候我們執行下這個歸檔為1即可：alter table tableName auto_increment=1;4：能後在插入的時候就是從1開始的啦