【宋红康 MySQL数据库 】【高级篇】数据库其它调优策略

数据库调优 (opens new window)的措施

调优的目标

如何定位调优问题

调优的维度和步骤

优化MySQL服务器

优化服务器硬件

优化MySQL的参数

优化数据库结构

拆分表：冷热数据隔离

增加中间表

增加冗余字段

优化数据类型

优化插入记录的速度

使用非空约束

分析表、检查表、优化表

CREATE TABLE `user1` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(255) DEFAULT NULL,
  `age` INT DEFAULT NULL,
  `sex` VARCHAR(255) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_name` (`name`) USING BTREE
) ENGINE=INNODB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb3;

#######
SET GLOBAL log_bin_trust_function_creators = 1;

DELIMITER //
CREATE FUNCTION  rand_num (from_num INT ,to_num INT) RETURNS INT(11)
BEGIN   
DECLARE i INT DEFAULT 0;  
SET i = FLOOR(from_num +RAND()*(to_num - from_num+1))   ;
RETURN i;  
END //
DELIMITER ;

###
DELIMITER //
CREATE PROCEDURE  insert_user( max_num INT )
BEGIN  
DECLARE i INT DEFAULT 0;   
 SET autocommit = 0;    
 REPEAT  
 SET i = i + 1;  
 INSERT INTO `user1` ( NAME,age,sex ) 
 VALUES ("atguigu",rand_num(1,20),"male");  
 UNTIL i = max_num  
 END REPEAT;  
 COMMIT; 
END //
DELIMITER;

##
CALL insert_user(1000);

SHOW INDEX FROM user1;

SELECT * FROM user1;

UPDATE user1 SET NAME = 'atguigu03' WHERE id = 3;

#分析表
ANALYZE TABLE user1;

#检查表
CHECK TABLE user1;

#优化表
CREATE TABLE t1(id INT,NAME VARCHAR(15)) ENGINE = MYISAM;

OPTIMIZE TABLE t1;


CREATE TABLE t2(id INT,NAME VARCHAR(15)) ENGINE = INNODB;

OPTIMIZE TABLE t2;

大表优化

限定查询的范围

读/写分离

垂直拆分

水平拆分

其它调优策略

服务器语句超时处理

创建全局通用表空间

CREATE TABLESPACE atguigu1 ADD DATAFILE 'atguigu1.ibd' file_block_size=16k;

CREATE TABLE test(id INT,NAME VARCHAR(10)) ENGINE=INNODB DEFAULT CHARSET utf8mb4 TABLESPACE atguigu1;

ALTER TABLE test TABLESPACE atguigu1;

DROP TABLESPACE atguigu1;

DROP TABLE test;

隐藏索引对调优的帮助

参考