1. 修改mysql兼容模式;
~~~
mysql> set global sql_mode = 'NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION';
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)
~~~
2.分頁優化 between ,in 索引,數據類型,limit
~~~
SELECT * FROM table WHERE id BETWEEN 1000000 AND 1000010;
SELECT * FROM table WHERE id IN(10000, 100000, 1000000...);
~~~
3.mysql 使用變量開啟緩存查詢
~~~
// 開啟查詢緩存
$today = date("Y-m-d");
$r = mysql_query("SELECT username FROM user WHERE signup_date >= '$today'");
~~~
4.不使用NOT IN和<>操作
NOT IN和<>操作都不會使用索引將進行全表掃描。NOT IN可以NOT EXISTS代替,id<>3則可使用id>3 or id<3來代替
5.常見配置
1、連接設置
show variables like '%max_connection%';
show status like '%Max_used_connections%';
Max_used_connections/max_connection <=85%,參數配置項的值可對照修改
2、存儲在堆棧中的連接數量
show variables like '%back_log%';
back_log 默認為50 建議修改為 128~512
3、數據連接關閉前等待時間
show variables like '%timeout%';
修改interactive_timeout wait_timeout 2項的值,默認為28800,建議修改為7200
4、索引緩沖區的大小
show status like '%read%';
索引未緩存命中率 key_read/key_request_reads ~=0.001~0.01
5、查詢緩沖區的大小(query_cache_size)
show variables like '%cache%';
show status like '%qcache%';
緩存碎片率 Qcache_free_blocks/Qcache_total_blocks <20%
緩存利用率 (query_cache_size-Qcache_free_memory)/query_cache_size<25%
緩存命中率 Qcache_hits/Qcache_inserts>95%
6、順序讀、隨機讀、排序、連接緩沖區的大小,每個線程獨占,建議設置為16MB
show status like '%buffer%';
read_buffer_size
read_rnd_buffer_size
sort_buffer_size
join_buffer_size
7、表緩沖區大小
show status like '%tables%';
table_cache 根據 open_tables opented_tables 大小來調整
8、內存表和臨時表
show status like '%table%';
max_heap_table_size
tmp_table_size
內存表超過臨時表大小,才需要調整內存表的大小
9、磁盤上臨時表大小
show status like '%tmp%';
(Created_tmp_disk_tables/Created_tmp_tables)*100<25%
10、緩存線程的數量
show variables like '%tmp%';
thread_cache_size
11、并發線程的數量
show variables like '%thread%';
innodb_thread_concurrency (cpu+磁盤)數量的2倍
12、其他
數據和索引緩沖區的大小 innodb_buffer_pool_size 物理內容的80%
日志緩沖區的大小 innodb_log_buffer_size 1~8MB
數據字段和其他數據結構的大小 innodb_additional_mem_pool_size 20MB
事物處理機制 innodb_flush_log_at_trx_commit
0 提交事物不寫入日志,每秒日志文件寫入和flush磁盤
1 每秒或每次事物提交時,日志文件寫入 flush磁盤
2 每次事物提交時,日志文件寫入,每秒flush磁盤
6.復制表
`
create table number like nums
`
7.用存儲過程生成數字輔助表
~~~
CREATE PROCEDURE pCreateNums ( cnt INT UNSIGNED ) BEGIN
DECLARE
s INT UNSIGNED DEFAULT 1;
TRUNCATE TABLE nums;
WHILE
s * 2 <= cnt DO
BEGIN
INSERT INTO nums SELECT
a + s
FROM
Nums;
SET s = s * 2;
END;
END WHILE;
END;
call pCreateNums(100000) #單獨開啟調用 `
~~~