mysql

存储架构优化的那些事(二)

2021-08-31  本文已影响0人  勤_
mysql.jpeg

概述

上文我们通过explain分析大概了解了怎么去做分析SQL，本文将具体的深入Mysql SQL优化的相关方案。

初始化脚本

通过如下脚本，在mysql数据库中新建对应的实验表和数据。

CREATE TABLE `user_info` (
  `id`   BIGINT(20)  NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(50) NOT NULL DEFAULT '',
  `age`  INT(11)              DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `name_index` (`name`)
)
  ENGINE = INNODB
  DEFAULT CHARSET = utf8;

INSERT INTO user_info (NAME, age) VALUES ('xys', 20);
INSERT INTO user_info (NAME, age) VALUES ('a', 21);
INSERT INTO user_info (NAME, age) VALUES ('b', 23);
INSERT INTO user_info (NAME, age) VALUES ('c', 50);
INSERT INTO user_info (NAME, age) VALUES ('d', 15);
INSERT INTO user_info (NAME, age) VALUES ('e', 20);
INSERT INTO user_info (NAME, age) VALUES ('f', 21);
INSERT INTO user_info (NAME, age) VALUES ('g', 23);
INSERT INTO user_info (NAME, age) VALUES ('h', 50);
INSERT INTO user_info (NAME, age) VALUES ('i', 15);

CREATE TABLE `order_info` (
  `id`           BIGINT(20)  NOT NULL AUTO_INCREMENT,
  `user_id`      BIGINT(20)           DEFAULT NULL,
  `product_name` VARCHAR(50) NOT NULL DEFAULT '',
  `productor`    VARCHAR(30)          DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `user_product_detail_index` (`user_id`, `product_name`, `productor`)
)
  ENGINE = INNODB
  DEFAULT CHARSET = utf8;

INSERT INTO order_info (user_id, product_name, productor) VALUES (1, 'p1', 'WHH');
INSERT INTO order_info (user_id, product_name, productor) VALUES (1, 'p2', 'WL');
INSERT INTO order_info (user_id, product_name, productor) VALUES (1, 'p1', 'DX');
INSERT INTO order_info (user_id, product_name, productor) VALUES (2, 'p1', 'WHH');
INSERT INTO order_info (user_id, product_name, productor) VALUES (2, 'p5', 'WL');
INSERT INTO order_info (user_id, product_name, productor) VALUES (3, 'p3', 'MA');
INSERT INTO order_info (user_id, product_name, productor) VALUES (4, 'p1', 'WHH');
INSERT INTO order_info (user_id, product_name, productor) VALUES (6, 'p1', 'WHH');
INSERT INTO order_info (user_id, product_name, productor) VALUES (9, 'p8', 'TE');
INSERT INTO order_info (user_id, product_name, productor) VALUES (1, 'p199', 'WHH99');

-- 验证分区 
CREATE TABLE user_temp (
  id   INT UNSIGNED NOT NULL AUTO_INCREMENT,
  NAME VARCHAR(20),
  PRIMARY KEY (id)
)ENGINE = INNODB PARTITION BY KEY (id) PARTITIONS 3;

INSERT INTO user_temp VALUES(1,"hello") ;
INSERT INTO user_temp VALUES(2,"world") ;
INSERT INTO user_temp VALUES(3,"nice") ;

DROP PROCEDURE IF EXISTS batchInsert; 

-- 批量插入数据
DELIMITER //
CREATE PROCEDURE batchInsert()
BEGIN
    DECLARE num INT; 
    SET num=20;
    WHILE num<=100000 DO
        INSERT INTO user_info (id, NAME, age) VALUES (num ,CONCAT('xys',num) , 20);
        INSERT INTO order_info (user_id, product_name, productor) VALUES (num, CONCAT('p',num) , CONCAT('WHH',num));
        SET num=num+1;
    END WHILE;
END
// 
DELIMITER ; #恢复;表示结束
CALL batchInsert;

SELECT COUNT(1) FROM `user_info`;
SELECT COUNT(1) FROM `order_info`;

-- user_info增加一列
ALTER TABLE user_info ADD COLUMN `oid` BIGINT(20);
UPDATE user_info u , order_info o SET u.oid = o.user_id WHERE o.user_id = u.id ;

ALTER TABLE order_info ADD COLUMN `description` VARCHAR(255) ;

Mysql SQL优化方案

总体方向：

细分方向：

参考

Optimizing SQL Statements

上一篇下一篇

猜你喜欢

热点阅读