伺服器架構之性能擴充-第五章（6）

Mysql>create table t2 like t1; //複制表結構

Mysql>insert into t2 select * from t1; //複制資料内容

索引是一種快速查詢的有效方法，可以通過alter增加索引或create語句建立。

mysql>alter table t1 add ind_id (id);

mysql>alter table t1 add unique/primary key (id);

mysql>create index ind_id on table t1 (id);

mysql>show index from t1;

mysql>drop index ind_id on table t1;

mysql>alter table t1 drop index ind_id;

Unique索引時指唯一索引，是沒有重複行的索引。

Alter方法建立和删除索引

删除主鍵，如果索引列是自動增長的變量。首先要取消自動增長，然後才可以删除主鍵，因為自動增長序列預設為主鍵。

增加自動增長主鍵索引

視圖:是存在于表之外的另一張表，符合條件的查詢結果

Mysql>create view v_t1 as select * from t1 where id >10;

Mysql>drop view v_t1;

視圖時随着主表而變化的表

字元串函數：

Concat （string1，……） //連接配接字元串

Lcase（string1） //換成小寫

Ucase（string1）//換成大寫

Length（string1） //string1的長度

Rtrim（string1） //去除後端空格

Ltrim（string1）//取出前段空格

Repeat （string1，count）//重複count次

Replace（str，search_str,replace_str） //在str中，用replace_str代替search_str

Substring（str，position，length） //從str的position開始，取length個字元

Space（count） //生成count個空格

數學函數：

Bin（number）//十進制轉為二進制

Ceiling（string1）//向上取整

Floor（string）//向下取整

Max（col）//取最大值，聚合時使用

Min（col）//取最小值，聚合時使用

Sqrt（number）//開平方

Rand（）//傳回0-1内的随即取值

日期函數：

Curdate（） //傳回目前日志

Curtime（） //傳回目前時間

Now（） //傳回目前日期時間

Week（date） //傳回這是多少周

Year（date） //傳回年份

Datediff（date1，date2） //傳回開始時間date1和結束時間date2間天數

預處理是傳遞一個參數作為where判斷語句：

Mysql>prepare hello from “select * from t1 where id >?”;

Mysql>set @i=1;

Mysql>excute hello using @i;

Mysql>drop prepare stml;

mysql>set autocommit=0; //關閉自動送出

mysql>delete from t1 where id=11; //

mysql>savepoint p1; //建立一個還原點

mysql>delete from t1 where id=10;

mysql>savepoint2;

mysql>rollback to p1; //恢複到p1還原點，p2自動失效

mysql>rollback; //退回到原始還原點

修改引擎，使用事處理功能，要使用innodb引擎才可以。

復原恢複資料

設定復原點

存儲是一種批量插入資料的方法，需要先将結尾符改為//,最後再改回來，然後通過call指令來調用存儲

觸發器是一種當完成某項動作的同時，可以同時調用其它資料庫程序同時進行。通過create trigger建立。

提前存在的值則old，不存在則new。

Insert型觸發器：

Delete型觸發器

Update型觸發器：

聯合檢視：可以使用聯合檢視來查詢觸發與否

常用的删除資料表内容有如下兩種方法：

Mysql>delete from t1; //清除表的内容，改變結構

Mysql>truncate table t1; //清楚表的内容，不改變結構，速度快常用

Mysql>alter table t1 auto_increment=1; //如果自動增長值不是從1開始可以這樣調整

正規表達式：

Mysql>select name,email from t where email pegexp “@163[,.]com$”; //比對@163.com或@163，com郵箱

Mysql>select name,email from t where emal like “@163.com” or email kike “@163,com”;

Rand()随機：

Mysql>select * from t order by rand() limit 3; //随機3條資料

Group by的排序擴充

Mysql>select cname,pname,count(pname) from t group by cname,pame with rollup; //分别對個結果進行排序和統計

建立外鍵：

Mysql>create table t3(id int,name char(20), foreign kye (id) references t2(id) on delete cascade on update cascade); //建立t3使用外鍵t2

Mysql help使用：

Mysql>? Create 檢視指令和用法

首先檢視各種語句使用頻率

Mysql>show 【session/global】 status； //session目前連接配接，global表示資料啟動至今

登陸以來進行增删改查的次數

Com_update,com_delete分别表示更新和删除次數。

對于innodb引擎可以使用以下查詢語句，innodb_rows_delete/update/select/insert

Innodb引擎是影響的行數，myisam引擎是影響的次數。

Innodb是影響的行數，myisam是影響的次數。

Connections代表連接配接數，uptime代表連接配接時間，slow_queries代表慢查詢次數。

定位執行效率低的語句

Mysql>explain select * from t where id=1000;

Mysql>desc select * from t where id=1000;

我們重點看一下影響行數。

對一個表增加索引之後，查詢範圍由9變2，快多了。

索引是優化mysql資料庫最常用的方法，使用索引注意以下幾點。

1， ,對于複合索引，一般隻對左邊的索引有效。

2，使用通配符時，like左邊不能用通配符

例：mysql>explain select * from t where name like “%3”\G //不會使用索引

3.對于null的搜尋is null

例：mysql>desc select * from t where name is null \G

1.對于行數低于100的資料表，索引效果不是很好

2.聯合查詢語句語句要都使用索引，才會使用索引

3.如果索引列是字元串，則查詢時要加“”

4.對于handler_read_rnd_next參數較大的，應該建立索引

5.3.1提高資料的導入效率

常用導入資料的方法是通過outfile進行的。通過outfile導出資料，這時是表内容，非表結構。例如導出資料

導入資料

對于innodb引擎表可以通過以下方法提高速度：

1、通過set unique_checks=0,關閉唯一性效驗，導入資料完畢後再set unique_checks=1；恢複唯一性效驗。

2、關閉自動送出，提高導入效率。導入資料前執行set autocommit=0關閉自動送出；導入後set autocommit=1,恢複自動送出。

使用insert delayed可以使資料庫得到更高的效率。并且可以增加bulk_inser_buffer_size的變量值來提高速度

資料庫的優化一般可以通過拆分表提高表的通路效率，這也可以解決大存儲量;使用中間表技術提高查詢速度，中間表一般就是視圖技術。

資料庫鎖定可以使用lock table t read/write;

讀鎖，有一個人讀鎖，其他人可讀不可以寫。

寫鎖，隻有本人可以進行增删改查，其他人不能讀寫。

Mysql>status;檢視系統字元集。它包括伺服器字元集，資料庫字元集，用戶端字元集，連結字元集

字元集可以通過/etc/my.cnf配置檔案進行修改

【client】用于定義用戶端字元集和連結字元集

【mysqld】控制着伺服器字元集和資料庫字元集

下面的collation-server是校驗字元集

Mysql>show character set; //檢視校驗字元集

Bin_log日志是系統進行恢複的重要日志

通過修改/etc/my.cnf檔案，去掉#号即可

慢查詢日志是進行資料庫優化的基礎。

Vi /etc/my.cnf

Log_slow_queries=slow.log //開啟慢查詢

Log_query_time=5 //設定慢查詢的時間

Socket預設是位于/tmp/mysql.sock, mysql的啟動需要socket檔案，當然可以通過重新開機資料庫自動建立socket，也可以不用socket便實作登陸。可以使用

Mysql>mysql –u root –p –protocol tcp –hlocalhost

Service mysqld stop

Mysql_safe –skip-grant-tables –user=mysql & //跳過授權表

Mysql –uroot

Mysql>update user set password=password(“123”) where user=”root” and host=”localhost”;

或mysql>set password for root@loaclhost=password(“123”)；

或mysql>set password=password(“123”); //修改密碼

本文轉自zsaisai 51CTO部落格，原文連結:http://blog.51cto.com/3402313/967194

伺服器架構之性能擴充-第五章（6）

繼續閱讀

報錯：'mysql' 不是内部或外部指令，也不是可運作的程式或批處理檔案。

Ubuntu16.04安裝Apache+MySQL+PHP1. 安裝Apache2. 安裝MySQL3. 安裝PHP4. 安裝phpMyAdmin

Linxu常用指令技巧彙總

ERROR 1 (HY000): Can't create/write to file '/tmp/#sql_4188_1.MYI' (Errcode: 28)

艱難安裝LDAP,SSL認證

《Linux指令行與Shell腳本程式設計大全第2版.布盧姆》pdf

MySQL的4種隔離級别？出現問題

XX系統實施過程問題總結

無元件上傳圖檔到資料庫中，最完整解決方案

【MySQL資料庫】資料庫索引事務1.索引2.事務

neo4j之cypher使用文檔

NOSQL安全攻擊

mybatis_入門程式Mybatis入門

mysql使用source指令導入.sql檔案

登入plsql 報錯 the account is locked --使用者被鎖

SequoiaDB巨杉資料庫C++驅動概述