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) #单独开启调用 `
~~~
