mysql表復制
復制表結構+復制表數據
mysql> create table t3 like t1; mysql> insert into t3 select * from t1;
mysql索引
ALTER TABLE用來創建普通索引、UNIQUE索引或PRIMARY KEY索引 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 CREATE INDEX index_name ON table_name (column_list) CREATE UNIQUE INDEX index_name ON table_name (column_list) drop index DROP INDEX index_name ON talbe_name alter table table drop 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)
view視圖的幫助信息
mysql> ? view ALTER VIEW CREATE VIEW DROP VIEW
查看視圖
mysql> show tables;
刪除視圖v_t1
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預處理語句
設置stmt1預處理,傳遞一個數據作為一個where判斷條件
mysql> prepare stmt1 from 'select * from t1 where id>?';
設置一個變量
mysql> set @i=1;
執行stmt1預處理
mysql> execute stmt1 using @i;
設置@i為5
mysql> set @i=5;
再次去執行stmt1
mysql> execute stmt1 using @i;
如何刪除預處理stmt1
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存儲
創建一個存儲p1()
mysql> \d // mysql> create procedure p1() -> begin -> set @i=0; -> while @i<10 do -> select @i; -> set @i=@i+1; -> end while; -> end; -> //
執行存儲p1()
mysql> \d ; mysql> call p1(); --查看procedure p1()的status信息 mysql> show procedure status\G --查看procedure p1()的具體信息: mysql> show create procedure p1\G
mysql觸發器
修改delimiter為// mysql> \d // 創建一個名字為tg1的觸發器,當向t1表中插入數據時，就向t2表中插入一條數據 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); 如何制作刪除表t1後t2表中的記錄也會跟著刪除呢 mysql>\d // mysql> create trigger tg2 beforedelete on t1 for each row >begin delete from t2 where id=old.id; >end// mysql>\d ; 如何制作更改表t1後t2表中的記錄跟著個性呢 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)
對demo表按照cname、pname列分組對pname列進行聚合計算如下
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)
同樣使用with rollup關鍵字後，統計出更多的信息，如下。注意：with rollup不可以和ordery by同時使用
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語句的一般步驟
通過show status命令了解各種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:慢查詢的次數
定位執行效率較低的SQL語句
explain select * from table where id=1000; desc select * from table where id=1000;
通過EXPLAIN分析較低效SQL的執行計劃
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索引名
MySQL如何使用索引
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語句，這樣可以大大縮短客戶與數據庫的連接、關閉等損耗。
可以使用insert delayed（馬上執行）語句得到更高的效率。
將索引文件和數據文件分別存放不同的磁盤上。
可以增加bulk_insert_buffer_size 變量值的方法來提高速度，但是只對MyISAM表使用當從一個文件中裝載一個表時，使用LOAD DATA INFILE。這個通常比使用很多insert語句要快20倍。
優化group by語句
如果查詢包含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服務器優化
myisam讀鎖定
1.
lock table t1 read
2.開啟另一個mysql連接終端,接著去嘗試:
select * from t1
3.再insert、update和delete t1這張表，你會發現所有的數據都停留在終端上沒有真正的去操作
4.讀鎖定對我們在做備份大量數據時非常有用.
mysqldump -uroot -p123 test >test.sql
myisam寫鎖定
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;
慢查詢日志
開戶和設置慢查詢
這樣就可以登錄,這樣就不用mysql.sock來登錄，而mysql.sock是啟動mysqld服務時產生的
root密碼丟失
root密碼丟失破解
1.service mysqld stop
2. mysqld_safe --skip-grant-tables --user=mysql &
//跳過授權表mysql.user和mysql.db這些表
3. mysql -uroot
4. set password=password("wei");
//用這一條語句結果報錯,就是因為加了--skip-grant-tables
4. mysql>update user set password=password("wei") where user='root'
and host='localhost';
5. mysql> set password for root@localhost=password("wei");
6. mysql> set password=password("wei");
//和第五步一樣,都可能成功修改密碼
