博客

mysql 优化；
1、优化方向
优化成本
 硬件升级>系统配置>表结构设计>sql语句及索引
优化效果：
 硬件升级<系统配置<表结构设计<sql语句及索引

######################下面是具体优化方法#############

1、系统配置优化
1)、内存比硬盘快
第一步：show global status like 'innodb_buffer_pool_page_%';
当Innodb_buffer_pool_pages_free 为0时候，说明 innodb_buffer_pool_size (默认128M) 内存被用光，以后会从硬盘查数据

第二步：修改my.cnf
#默认128M,理论扩大到内存3/4或4/5
innodb_buffer_pool_size=750M  
重启：systemctl restart mysqld (或者service mysqld restart)

2)数据预热
mysql启动后

SELECT DISTINCT
	  CONCAT( 'SELECT ', ndxcollist, ' FROM ', db, '.', tb,     ' ORDER BY ', ndxcollist, ';' ) SelectQueryToLoadCache     
FROM
	  (
	        SELECT
	          ENGINE,
	table_schema db,
	table_name tb,
	          index_name,
	GROUP_CONCAT( column_name ORDER BY seq_in_index ) ndxcollist         
FROM
	      (
	            SELECT
	              B.ENGINE,
	A.table_schema,
	A.table_name,
	              A.index_name,
	A.column_name,
	A.seq_in_index             
FROM
	              information_schema.statistics A
	INNER JOIN               (                     SELECT ENGINE, table_schema, table_name                     FROM information_schema.TABLES WHERE    ENGINE = 'InnoDB'               ) B USING ( table_schema, table_name )             
WHERE
	B.table_schema NOT IN ( 'information_schema', 'mysql' )             
ORDER BY
	table_schema,
	table_name,
	index_name,
	seq_in_index       
	) A         
GROUP BY
	table_schema,
	table_name,
	index_name   
	) AA 
ORDER BY
	db,tb;

将该脚本保存为:loadtomem.sql
执行命令：
mysql -uroot -p123456 -AN < /home/www/loadtomem.sql > /home/www/loadtomem.sql 
在需要数据预热时，比如重启数据库,执行命令：
mysql -uroot < /home/www/loadtomem.sql > /dev/null 2>&1
 
2、表结构设计优化
1）中间表（如报表，晚上计算放在中间表）
2）冗余字段
3）拆表
3）要有主键，最好int类型，建议自增主键，分布式系统采用雪花算法，雪花算法是数字且自增的
4）字段，能用数字别用字符，最好notnull

3、sql语句及索引
第一步：设计一个表
CREATE TABLE tbiguser (
id INT PRIMARY KEY auto_increment,
nickname VARCHAR ( 255 ),
loginname VARCHAR ( 255 ),
age INT,
sex CHAR ( 1 ),
STATUS INT,
address VARCHAR ( 255 ) 
);

向该表中写入10000000条数据

执行存储过程
call test_insert();