mysql表復制
復制表結構+復制表數據
mysql> create table t3 like t1; mysql> insert into t3 select * from t1;
mysql索引
ALTER TABLE table_name ADD INDEX index_name (column_list) ALTER TABLE table_name ADD UNIQUE (column_list) ALTER TABLE table_name ADDPRIMARY KEY (column_list)
CREATE INDEX index_name ON table_name (column_list) CREATE UNIQUE INDEX index_name ON table_name (column_list)
DROP INDEX index_name ON talbe_name
ALTER TABLE table_name DROP INDEX index_name ALTER TABLE table_name DROP PRIMARY KEY
mysql視圖
mysql> create view v_t1 as select * from t1 where id>4 and id<11; Query OK, 0 rows affected (0.00 sec)
mysql> ? view ALTER VIEW CREATE VIEW DROP VIEW
mysql> show tables;
mysql> drop view v_t1;
mysql內置函數
CONCAT (string2 [,… ]) //連接字串 LCASE (string2 ) //轉換成小寫 UCASE (string2 ) //轉換成大寫 LENGTH (string ) //string長度 LTRIM (string2 ) //去除前端空格 RTRIM (string2 ) //去除後端空格 REPEAT (string2 ,count ) //重復count次 REPLACE (str ,search_str ,replace_str ) //在str中用replace_str替換search_str SUBSTRING (str , position [,length ]) //從str的position開始,取length個字符 SPACE(count) //生成count個空格
BIN (decimal_number ) //十進制轉二進制 CEILING (number2 ) //向上取整 FLOOR (number2 ) //向下取整 MAX(num1 ,num2) //取最大值 MIN(num1,num2) //取最小值 SQRT(number2) //開平方 RAND() //返回0-1內的隨機值
CURDATE() //返回當前日期 CURTIME() //返回當前時間 NOW() //返回當前的日期時間 UNIX_TIMESTAMP(date) //返回當前date的UNIX日間戳 FROM_UNIXTIME() //返回UNIX時間戳的日期值 WEEK(date) //返回日期date為一年中的第幾周 YEAR(date) //返回日期date的年份 DATEDIFF(expr,expr2) //返回起始時間expr和結束時間expr2間天數
mysql預處理語句
mysql> prepare stmt1 from 'select * from t1 where id>?';
mysql> set @i=1;
mysql> execute stmt1 using @i;
mysql> set @i=5;
mysql> execute stmt1 using @i;
mysql> drop prepare stmt1;
mysql事務處理
--關閉自動提交功能 mysql> set autocommit=0; --從表t1中刪除了一條記錄 mysql> delete from t1 where id=11; --此時做一個p1還原點: mysql> savepoint p1; --再次從表t1中刪除一條記錄: mysql> delete from t1 where id=10; --再次做一個p2還原點: mysql> savepoint p2; --此時恢復到p1還原點，當然後面的p2這些還原點自動會失效: mysql> rollback to p1; --退回到最原始的還原點: mysql> rollback ;
mysql存儲
mysql> \d // mysql> create procedure p1() -> begin -> set @i=0; -> while @i<10 do -> select @i; -> set @i=@i+1; -> end while; -> end; -> //
mysql> \d ; mysql> call p1(); --查看procedure p1()的status信息 mysql> show procedure status\G --查看procedure p1()的具體信息: mysql> show create procedure p1\G
mysql觸發器
mysql> \d //
mysql> create trigger tg1 before insert on t1 for each ro >begin >insert into t2(id) values(new.id); >end// --准備兩個空表t1和t2 mysql> select * from t1; mysql> select * from t2; --向t1表中插入多條數據: mysql> insert into t1 values(1),(2),(3),(4);
mysql>\d // mysql> create trigger tg2 beforedelete on t1 for each row >begin delete from t2 where id=old.id; >end// mysql>\d ;
mysql>\d // mysql> create trigger tg3 beforeupdate on t1 for each row >begin update t2 set id=new.id where id=old.id; >end// mysql>\d ;
mysql> show triggers;
重排auto_increment值
MYSQL數據庫自動增長的ID如何恢復，清空表的時候。不能用
delete from tablename;
而是要用：
truncatetable tablename;
這樣auto_increment 就恢復成1了
或者清空內容後直接用ALTER命令修改表:
altertable tablename auto_increment =1;
利用GROUP BY的WITH ROLLUP
mysql> select * from demo; +-------+-------+ | cname | pname | +-------+-------+ | bj | hd | | bj | xc | | bj | hd | | sh | dh | | sh | rg | | sh | dh | +-------+-------+ 9 rows in set (0.00 sec)
mysql> select cname,pname,count(pname) from demo group by cname,pname; +-------+-------+--------------+ | cname | pname | count(pname) | +-------+-------+--------------+ | bj | hd | 3 | | bj | xc | 2 | | sh | dh | 3 | | sh | rg | 1 | +-------+-------+--------------+ 4 rows in set (0.00 sec)
ysql> select cname,pname,count(pname) from demo group by cname,pname with rollup; +-------+-------+--------------+ | cname | pname | count(pname) | +-------+-------+--------------+ | bj | hd | 3 | | bj | xc | 2 | | bj | NULL | 5 | | sh | dh | 3 | | sh | rg | 1 | | sh | NULL | 4 | | NULL | NULL | 9 | +-------+-------+--------------+ 7 rows in set (0.00 sec)
使用外鍵需要注意的問題
mysql>create table temp( id int, name char(20), foreign key(id) references outTable(id) on delete cascade on update cascade)；
注意：Innodb類型的表支持外鍵，myisam類型的表，雖然創建外鍵可以成功，但是不起作用，主要原因是不支持外鍵。
優化SQL語句的一般步驟
mysql> show [session|global]status;
其中：session（默認）表示當前連接，global表示自數據庫啟動至今
mysql>show status; mysql>show global status; mysql>show status like ‘Com_%’; mysql>show global status like ‘Com_%’;
參數說明：
Com_XXX表示每個XXX語句執行的次數如： Com_select 執行select操作的次數，一次查詢只累計加1 Com_update 執行update操作的次數 Com_insert 執行insert操作的次數，對批量插入只算一次。 Com_delete 執行delete操作的次數
只針對於InnoDB存儲引擎的：
InnoDB_rows_read 執行select操作的次數 InnoDB_rows_updated 執行update操作的次數 InnoDB_rows_inserted 執行insert操作的次數 InnoDB_rows_deleted 執行delete操作的次數
其他：
connections 連接mysql的數量 Uptime 服務器已經工作的秒數 Slow_queries:慢查詢的次數
explain select * from table where id=1000; desc select * from table where id=1000;
mysql> explain select count(*) from stu where name like "a%"\G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: stu type: range possible_keys: name,ind_stu_name key: name key_len: 50 ref: NULL rows: 8 Extra: Using where; Using index 1 row in set (0.00 sec)
每一列的簡單解釋
id: 1 select_type: SIMPLE 表示select的類型，常見的取值有SIMPLE（）簡單表，即不使用表連接或者子查詢）、PRIMARY（主查詢，即外層的查詢）、UNION（UNION中的第二個或者後面的查詢語句）、SUBQUERY（子查詢中的第一個SESECT）等 table: stu 輸出結果集的表 type: range 表示表的連接類型，性能有好到差：system（表僅一行）、const（只一行匹配）、eq_ref（對於前面的每一行使用主鍵和唯一）、ref（同eq_ref，但沒有使用主鍵和唯一）、ref_or_null（同前面對null查詢）、index_merge（索引合並優化）、unique_subquery（主鍵子查詢）、index_subquery（非主鍵子查詢）、range（表單中的范圍查詢）、index（都通過查詢索引來得到數據）、all（通過全表掃描得到的數據） possible_keys: name,ind_stu_name 表查詢時可能使用的索引。 key: name 表示實際使用的索引。 key_len: 50 索引字段的長度 ref: NULL rows: 8 掃描行的數量 Extra: Using where; Using index 執行情況的說明和描述
索引問題
MyISAM存儲引擎的表的數據和索引是自動分開存儲的，各自是獨一的一個文件；InnoDB存儲引擎的表的數據和索引是存儲在同一個表空間裡面，但可以有多個文件組成。MySQL目前不支持函數索引，但是能對列的前面某一部分進行索引，例如name字段，可以只取name的前4個字符進行索引，這個特性可以大大縮小索引文件的大小，用戶在設計表結構的時候也可以對文本列根據此特性進行靈活設計。
mysql>create index ind_company2_name on company2(name(4)); --其中company表名ind_company2_name索引名
1、使用索引
(1)對於創建的多列索引，只要查詢的條件中用到最左邊的列，索引一般就會被使用。如下創建一個復合索引。
mysql>create index ind_sales2_com_mon on sales2(company_id,moneys);
然後按company_id進行查詢，發現使用到了復合索引
mysql>explain select * from sales2 where company_id=2006\G
使用下面的查詢就沒有使用到復合索引。
mysql>explain select * from sales2 where moneys=1\G
(2) 使用like的查詢，後面如果是常量並且只有%號不在第一個字符，索引才可能會被使用，如下：
mysql> explain select * from company2 where name like "%3"\G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: company2 type: ALL possible_keys: NULL key: NULL key_len: NULL ref: NULL rows: 1000 Extra: Using where 1 row in set (0.00 sec)
如下這個使用到了索引，而下面例子能夠使用索引，區別就在於“%”的位置不同，上面的例子是吧“%”放在了第一位，而下面的例子則沒有
mysql> explain select * from company2 where name like "3%"\G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: company2 type: range possible_keys: ind_company2_name key: ind_company2_name key_len: 11 ref: NULL rows: 103 Extra: Using where 1 row in set (0.00 sec)
(3)如果對大的文本進行搜索，使用全文索引而不使用like“%...%”.
(4)如果列名是索引，使用column_name is null將使用索引。如下
mysql> explain select * from company2 where name is null\G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: company2 type: ref possible_keys: ind_company2_name key: ind_company2_name key_len: 11 ref: const rows: 1 Extra: Using where 1 row in set (0.00 sec)
(1)如果MySQL估計使用索引比全表掃描更慢，則不使用索引。例如如果列key_part1均勻分布在1到100之間，查詢時使用索引就不是很好
mysql>select * from table_name where key_part1>1 and key_part<90;
(2)如果使用MEMORY/HEAP表並且where條件中不使用“=”進行索引列，那麼不會用到索引。Heap表只有在“=”的條件下會使用索引。
(3)用or分割開的條件，如果or前的條件中的列有索引，而後面的列中沒有索引，那麼涉及的索引都不會被用到。
mysql>show index from sales\G *************************** 1. row *************************** …… key_name: ind_sales_year seq_in_index：1 Column_name: year ……
從上面可以發現只有year列上面有索引。來看如下的執行計劃。
mysql> explain select * from sales where year=2001 or country=‘China’\G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: sales type: ALL possible_keys: ind_sales_year key: NULL key_len: NULL ref: NULL rows: 12 Extra: Using where 1 row in set (0.00 sec)
(4)如果不是索引列的第一部分，如下例子:可見雖然在money上面建有復合索引，但是由於money不是索引的第一列，那麼在查詢中這個索引也不會被MySQL采用。
mysql> explain select * from sales2 where moneys=1 \G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: sales2 type: ALL possible_keys: NULL key: NULL key_len: NULL ref: NULL rows: 1000 Extra: Using where 1 row in set (0.00 sec)
(5)如果like是以%開始，可見雖然在name上面建有索引，但是由於where 條件中like的值的“%”在第一位了，那麼MySQL也會采用這個索引。
(6)如果列類型是字符串，但在查詢時把一個數值型常量賦值給了一個字符型的列名name，那麼雖然在name列上有索引，但是也沒有用到。
mysql> explain select * from company2 where name name=294\G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: company2 type: ALL possible_keys: ind_company2_name key: NULL key_len: NULL ref: NULL rows: 1000 Extra: Using where 1 row in set (0.00 sec)
而下面的sql語句就可以正確使用索引。
mysql> explain select * from company2 where name name=‘294’\G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: company2 type: ref possible_keys: ind_company2_name key: ind_company2_name key_len: 23 ref: const rows: 1 Extra: Using where 1 row in set (0.00 sec)
如果索引正在工作，Handler_read_key的值將很高，這個值代表了一個行被索引值讀的次數。
Handler_read_rnd_next的值高則意味著查詢運行低效，並且應該建立索引補救。
mysql> show status like 'Handler_read%'; +-----------------------+-------+ | Variable_name | Value | +-----------------------+-------+ | Handler_read_first | 0 | | Handler_read_key | 5 | | Handler_read_next | 0 | | Handler_read_prev | 0 | | Handler_read_rnd | 0 | | Handler_read_rnd_next | 2055 | +-----------------------+-------+ 6 rows in set (0.00 sec)
兩個簡單實用的優化方法
mysql> CHECK TABLE tbl_name[,tbl_name] …[option] …option =
{ QUICK | FAST | MEDIUM| EXTENDED | CHANGED}
mysql> check table sales;
+--------------+-------+----------+----------+
| Table | Op | Msg_type | Msg_text |
+--------------+-------+----------+----------+
| sakila.sales | check | status | OK |
+--------------+-------+----------+----------+
1 row in set (0.01 sec)
優化表的語法格式：
OPTIMIZE [LOCAL | NO_WRITE_TO_BINLOG] TABLE tbl_name [,tbl_name]
如果已經刪除了表的一大部分，或者如果已經對含有可變長度行的表進行了很多的改動，則需要做定期優化。這個命令可以將表中的空間碎片進行合並，但是此命令只對MyISAM、BDB和InnoDB表起作用。
mysql> optimize table sales; +--------------+----------+----------+----------+ | Table | Op | Msg_type | Msg_text | +--------------+----------+----------+----------+ | sakila.sales | optimize | status | OK | +--------------+----------+----------+----------+ 1 row in set (0.05 sec)
常用SQL的優化
當用load命令導入數據的時候，適當設置可以提高導入的速度。
對於MyISAM存儲引擎的表，可以通過以下方式快速的導入大量的數據。
ALTER TABLE tbl_name DISABLE KEYS loading the data ALTER TABLE tbl_name ENABLE KEYS
DISABLE KEYS 和ENABLE KEYS 用來打開或關閉MyISAM表非唯一索引的更新，可以提高速度，注意：對InnoDB表無效。
--沒有使用打開或關閉MyISAM表非唯一索引： mysql> load data infile ‘/home/mysql/film_test.txt’into table film_test2; Query OK,529056 rows affected (1 min 55.12 sec) Records:529056 Deleted:0 Skipped:0 Warnings:0 --使用打開或關閉MyISAM表非唯一索引： mysql> alter table film_test2 disable keys; Query OK,0 rows affected (0.0 sec) mysql> load data infile ‘/home/mysql/film_test.txt’into table film_test2; Query OK,529056 rows affected (6.34 sec) Records:529056 Deleted:0 Skipped:0 Warnings:0 mysql> alter table film_test2 enable keys; Query OK,0 rows affected (12.25 sec) --以上對MyISAM表的數據導入，但對於InnoDB表並不能提高導入數據的效率
（1）針對於InnoDB類型表數據導入的優化
因為InnoDB表的按照主鍵順序保存的，所以將導入的數據主鍵的順序排列，可以有效地提高導入數據的效率。
--使用test3.txt文本是按表film_test4主鍵存儲順序保存的 mysql> load data infile ‘/home/mysql/film_test3.txt’into table film_test4; Query OK, 1587168 rows affected (22.92 sec) Records:1587168 Deleted:0 Skipped:0 Warnings:0 --使用test3.txt沒有任何順序的文本（效率慢了1.12倍） mysql> load data infile ‘/home/mysql/film_test4.txt’into table film_test4; Query OK, 1587168 rows affected (31.16 sec) Records:1587168 Deleted:0 Skipped:0 Warnings:0
（2）關閉唯一性效驗可以提高導入效率
在導入數據前先執行set unique_checks=0，關閉唯一性效驗，在導入結束後執行set unique_checks=1，恢復唯一性效驗，可以提高導入效率。
--當unique_checks=1時 mysql> load data infile ‘/home/mysql/film_test3.txt’into table film_test4; Query OK,1587168 rows affected (22.92 sec) Records:1587168 Deleted:0 Skipped:0 Warnings:0 --當unique_checks=0時 mysql> load data infile ‘/home/mysql/film_test3.txt’into table film_test4; Query OK,1587168 rows affected (19.92 sec) Records:1587168 Deleted:0 Skipped:0 Warnings:0
（3）關閉自動提交可以提高導入效率
在導入數據前先執行set autocommit=0，關閉自動提交事務，在導入結束後執行set autocommit=1，恢復自動提交，可以提高導入效率。
--當autocommit=1時 mysql> load data infile ‘/home/mysql/film_test3.txt’into table film_test4; Query OK,1587168 rows affected (22.92 sec) Records:1587168 Deleted:0 Skipped:0 Warnings:0 --當autocommit=0時 mysql> load data infile ‘/home/mysql/film_test3.txt’into table film_test4; Query OK,1587168 rows affected (20.87 sec) Records:1587168 Deleted:0 Skipped:0 Warnings:0
盡量使用多個值表的insert語句，這樣可以大大縮短客戶與數據庫的連接、關閉等損耗。
可以使用insert delayed（馬上執行）語句得到更高的效率。
將索引文件和數據文件分別存放不同的磁盤上。
可以增加bulk_insert_buffer_size 變量值的方法來提高速度，但是只對MyISAM表使用當從一個文件中裝載一個表時，使用LOAD DATA INFILE。這個通常比使用很多insert語句要快20倍。
如果查詢包含group by但用戶想要避免排序結果的損耗，則可以使用使用order by null來禁止排序：
如下沒有使用order by null來禁止排序
mysql> explain select id,sum(moneys) from sales2 group by id\G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: sales2 type: ALL possible_keys: NULL key: NULL key_len: NULL ref: NULL rows: 1000 Extra: Using temporary;Using filesort 1 row in set (0.00 sec)
如下使用order by null的效果：
mysql> explain select id,sum(moneys) from sales2 group by id order by null\G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: sales2 type: ALL possible_keys: NULL key: NULL key_len: NULL ref: NULL rows: 1000 Extra: Using temporary 1 row in set (0.00 sec)
下面是采用嵌套查詢的效果（可以使用更有效的鏈接查詢（Join）替代）。
mysql> explain select * from sales2 where company_id not in(select id from company2)\G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: sales2 type: ALL possible_keys: NULL key: NULL key_len: NULL ref: NULL rows: 1000 Extra: Using where 1 row in set (0.00 sec)
*************************** 2. row *************************** id: 2 select_type: SIMPLE table: company2 type: index_subquery possible_keys: ind_company2_id key: ind_company2_id key_len: 5 ref: func rows: 2 Extra: Using index 1 row in set (0.00 sec)
下面是使用更有效的鏈接查詢（Join）
mysql> explain select * from sales2 left join company2 on sales2.company_id = company2.id where sales2.company_id is null\G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: sales2 type: ALL possible_keys: ind_sales2_companyid_moneys key: ind_sales2_companyid_moneys key_len: 5 ref: count rows: 1 Extra: Using where 1 row in set (0.00 sec) *************************** 2. row *************************** id: 2 select_type: SIMPLE table: company2 type: index_subquery possible_keys: ind_company2_id key: ind_company2_id key_len: 5 ref: func rows: 1 Extra: 1 row in set (0.00 sec)
從執行計劃中可以明顯看出查詢掃描的記錄范圍和使用索引的情況都有了很大的改善。連接（JOIN）子所以更有效率一些，是因為MySQL不需要再內存中創建臨時表來完成這個邏輯上的需要兩個步驟的查詢工作。
數據庫優化
在MySQL中，可以使用函數PROCEDUREANALYSE（）對當前應用的表進行分析，改函數可以對數據表中列的數據類型提出優化建議，用戶可以根據應用的實際情況酌情考慮是否實施優化。
mysql> select * from duck_cust procedure analyse()\G *************************** 1. row *************************** Field_name: sakila.duch_cust.cust_num Min_value: 1 Max_value: 6 Min_length: 1 Max_length: 1 Empties_or_zeros: 0 Nulls: 0 Avg_value_or_avg_length: 3.5000 Std: 1.7078 Optimal_fieldtype: ENUM(‘1’,‘2’,‘3’,‘4’) NOT NULL *************************** 2. row *************************** ……
1.分庫分表
2.分區
主要目的:
1.減少表的記錄數
2.減小對操作系統的負擔壓力
中間表的產生:
1.view 視圖
2.重新生成一個新表
Mysql服務器優化
1.
lock table t1 read
2.開啟另一個mysql連接終端,接著去嘗試:
select * from t1
3.再insert、update和delete t1這張表，你會發現所有的數據都停留在終端上沒有真正的去操作
4.讀鎖定對我們在做備份大量數據時非常有用.
mysqldump -uroot -p123 test >test.sql
1.
lock table t1 write
2.打開另一個mysql終端,嘗試去select、insert、update和delete這張表t1,你會發現都不能操作，都會停留在終端上，只有等第一個終端操作完畢,第二個終端才能真正執行.
3.可見表的寫鎖定比讀鎖定更嚴格
4.一般情況下我們很少去顯式的去對表進行read 和write鎖定的,myisam會自動進行鎖定的.
Mysql服務器優化
1.log-bin=mysql-bin
查看bin-log日志:
mysql> show binary logs;
查看最後一個bin-log日志:
mysql> show master status;
開戶和設置慢查詢
(1).數據庫設計方面,這是DBA和Architect的責任,設計結構良好的數據庫,必要的時候,去正規化(英文是這個:denormalize,中文翻譯成啥我不知道),允許部分數據冗余,避免JOIN操作,以提高查詢效率
(2).系統架構設計方面,表散列,把海量數據散列到幾個不同的表裡面.快慢表,快表只留最新數據,慢表是歷史存檔.集群,主服務器Read & write,從服務器read only,或者N台服務器,各機器互為Master
(3).(1)和(2)超越PHP Programmer的要求了,會更好,不會沒關系.檢查有沒有少加索引
(4).寫高效的SQL語句,看看有沒有寫低效的SQL語句,比如生成笛卡爾積的全連接啊,大量的Group By和order by,沒有limit等等.必要的時候,把數據庫邏輯封裝到DBMS端的存儲過程裡面.緩存查詢結果,explain每一個sql語句
(5).所得皆必須,只從數據庫取必需的數據,比如查詢某篇文章的評論數,select count(*) ... where article_id = ? 就可以了,不要先select * ... where article_id = ?然後msql_num_rows.
只傳送必須的SQL語句,比如修改文章的時候,如果用戶只修改了標題,那就update ... set title = ? where article_id = ?不要set content = ?(大文本)
(6).必要的時候用不同的存儲引擎.比如InnoDB可以減少死鎖.HEAP可以提高一個數量級的查詢速度
我列舉幾個我熟悉的，
1，存儲引擎，根據應用選擇合適的引擎
2，索引 ----這個就有很多文章了，具體需要你自己去了解
3，sql語句優化，查詢條件的選擇之類
4，mysql自身系統配置，需要針對應用去定制
5，表的選擇，臨時表，或者分區表，也需要針對應用的情況去選擇使用
