--創建普通索引
CREATE INDEX index_name ON table_name(col_name);
--創建唯一索引
CREATE UNIQUE INDEX index_name ON table_name(col_name);
--創建普通組合索引
CREATE INDEX index_name ON table_name(col_name_1,col_name_2);
--創建唯一組合索引
CREATE UNIQUE INDEX index_name ON table_name(col_name_1,col_name_2);

ALTER TABLE table_name ADD INDEX index_name(col_name);

--直接刪除索引
DROP INDEX index_name ON table_name;
--修改表結構刪除索引
ALTER TABLE table_name DROP INDEX index_name;

- 查看表結構
????desc table_name;
?
- 查看生成表的SQL
????show create table table_name;
?
- 查看索引
????show index from? table_name;
?
- 查看執行時間
????set profiling = 1;
????SQL...
????show profiles;

SELECT t.Name? FROM mytable_t LEFT JOIN mytable_m?ON t.Name=m.username WHERE m.age=20 AND m.city='鄭州' ;

- like '%xx'
????select * from tb1 where name like '%cn';
- 使用函數
????select * from tb1 where reverse(name) = 'wupeiqi';
- or
????select * from tb1 where nid = 1 or email = 'seven@live.com';
????特別的：當or條件中有未建立索引的列才失效,以下會走索引
????????????select * from tb1 where nid = 1 or name = 'seven';
????????????select * from tb1 where nid = 1 or email = 'seven@live.com' and name = 'alex'
- 類型不一致
????如果列是字符串類型,傳入條件是必須用引號引起來,不然...
????select * from tb1 where name = 999;
- !=
????select * from tb1 where name != 'alex'
????特別的：如果是主鍵,則還是會走索引
????????select * from tb1 where nid != 123
- >
????select * from tb1 where name > 'alex'
????特別的：如果是主鍵或索引是整數類型,則還是會走索引
????????select * from tb1 where nid > 123
????????select * from tb1 where num > 123
- order by
????select email from tb1 order by name desc;
????當根據索引排序時候,選擇的映射如果不是索引,則不走索引
????特別的：如果對主鍵排序,則還是走索引：
????????select * from tb1 order by nid desc;
?
- 組合索引最左前綴
????如果組合索引為：(name,email)
????name and email?????? -- 使用索引
????name???????????????? -- 使用索引
????email??????????????? -- 不使用索引

- 避免使用select *
- count(1)或count(列) 代替 count(*)
- 創建表時盡量時 char 代替 varchar
- 表的字段順序固定長度的字段優先
- 組合索引代替多個單列索引(經常使用多個條件查詢時)
- 盡量使用短索引
- 使用連接(JOIN)來代替子查詢(Sub-Queries)
- 連表時注意條件類型需一致
- 索引散列值(重復多)不適合建索引,例：性別不適合

--第一頁
SELECT * FROM table_name LIMIT 0,10;
--第二頁
SELECT * FROM table_name LIMIT 10,10;
--第三頁
SELECT * FROM table_name LIMIT 20,10;

--下一頁
SELECT * FROM table_name WHERE id in (SELECT id FROM table_name WHERE id > max_id LIMIT 10);
--上一頁
SELECT * FROM table_name WHERE id in (SELECT id FROM table_name WHERE id < min_id ORDER BY id DESC LIMIT 10);
--當前頁之后的某一頁
SELECT * FROM table_name WHERE id in (SELECT id FROM (SELECT id FROM (SELECT id FROM table_name WHERE id < min_id ORDER BY id desc LIMIT (頁數差*10)) AS N ORDER BY N.id ASC LIMIT 10) AS P ORDER BY P.id ASC);
--當前頁之前的某一頁
SELECT * FROM table_name WHERE id in (SELECT id FROM (SELECT id FROM (SELECT id FROM table_name WHERE id > max_id LIMIT (頁數差*10)) AS N ORDER BY N.id DESC LIMIT 10) AS P ORDER BY P.id ASC);

mysql> explain select * from tb2;
+----+-------------+-------+------+---------------+------+---------+------+------+-------+
| id | select_type | table | type | possible_keys | key? | key_len | ref? | rows | Extra |
+----+-------------+-------+------+---------------+------+---------+------+------+-------+
|? 1 | SIMPLE????? | tb2?? | ALL? | NULL????????? | NULL | NULL??? | NULL |??? 2 | NULL? |
+----+-------------+-------+------+---------------+------+---------+------+------+-------+
1 row in set (0.00 sec)

--查詢配置命令
show variables like '%query%';
--當前配置參數
binlog_rows_query_log_events    OFF
ft_query_expansion_limit    20
have_query_cache    YES
--時間限制,超過此時間,則記錄
long_query_time    10.000000
query_alloc_block_size    8192
query_cache_limit    1048576
query_cache_min_res_unit    4096
query_cache_size    1048576
query_cache_type    OFF
query_cache_wlock_invalidate    OFF
query_prealloc_size    8192
--是否開啟慢日志記錄
slow_query_log    OFF
--日志文件
slow_query_log_file    D:\Program Files (x86)\mysql-5.7.18-winx64\data\Jack-slow.log
--

set global 變量名 = 值;
--例如,修改時間限制為20s
long_query_time = 20;

mysqldumpslow -s at -a ?/usr/local/var/mysql/MacBook-Pro-3-slow.log

"""
--verbose??? 版本
--debug????? 調試
--help?????? 幫助
?
-v?????????? 版本
-d?????????? 調試模式
-s ORDER???? 排序方式
?????????????what to sort by (al, at, ar, c, l, r, t), 'at' is default
??????????????al: average lock time
??????????????ar: average rows sent
??????????????at: average query time
???????????????c: count
???????????????l: lock time
???????????????r: rows sent
???????????????t: query time
-r?????????? 反轉順序,默認文件倒序拍.reverse the sort order (largest last instead of first)
-t NUM?????? 顯示前N條just show the top n queries
-a?????????? 不要將SQL中數字轉換成N,字符串轉換成S.don't abstract all numbers to N and strings to 'S'
-n NUM?????? abstract numbers with at least n digits within names
-g PATTERN?? 正則匹配；grep: only consider stmts that include this string
-h HOSTNAME? mysql機器名或者IP；hostname of db server for *-slow.log filename (can be wildcard),
?????????????default is '*', i.e. match all
-i NAME????? name of server instance (if using mysql.server startup script)
-l?????????? 總時間中不減去鎖定時間；don't subtract lock time from total time
"""