MySQL High Performance - Sunday, 2019/06/09

[chunvv]

MySQL High-Performance Workshop on 09 June 2019

• Topic: MySQL High-Performance

• Content:

  • Optimizing Schema and Data Types
  • Indexing for High Performance
  • Query Performance Optimization
  • Advanced MySQL Features
  • Optimizing Server Settings
  • Replication
  • Scaling MySQL
  • High Availability

• Information:

  • Location: Trung's apartment, Meguro, Tokyo, Japan
  • Date time: 09 June 2019

[chunvv]

Rules:

• No one is master or dump, we all are engineer
• Keep communication
• Focus on pair reading/discussing

[chunvv]

Basic knowledge (Kiểm tra bài cũ):

1. What is MySQL CONSTRAINTs, list up all constraints?

* NOT NULL
* UNIQUE
* PRIMARY KEY
* FOREIGN KEY
* CHECK
* DEFAULTs

Examples for check:

CREATE TABLE IF NOT EXISTS 
newbook_mast (book_id varchar(15) NOT NULL UNIQUE,          
book_name varchar(50)  ,           
isbn_no varchar(15)  NOT NULL UNIQUE  ,           
cate_id varchar(8)  ,             
aut_id varchar(8) ,             
pub_id varchar(8) ,            
dt_of_pub date ,             
pub_lang varchar(15) ,           
no_page decimal(5,0) 
CHECK(no_page>0) ,            
book_price decimal(8,2) ,             
PRIMARY KEY (book_id)               
);

CREATE TABLE IF NOT EXISTS
newauthor(aut_id varchar(8) NOT NULL , 
aut_name varchar(50) NOT NULL,
country varchar(25) NOT NULL CHECK (country IN ('USA','UK','India')), 
home_city varchar(25) NOT NULL, 
PRIMARY KEY (aut_id,home_city));

[chunvv]

2. LIKE Operator: What is the meaning of each following queries:

WHERE CustomerName LIKE 'a%'
WHERE CustomerName LIKE '%a'
WHERE CustomerName LIKE '%or%'
WHERE CustomerName LIKE '_r%'
WHERE CustomerName LIKE 'a__%'
WHERE ContactName LIKE 'a%o'

[chunvv]

3. What is the differences between union, union all, minus, intersect?

SELECT City FROM Customers
UNION
SELECT City FROM Suppliers
ORDER BY City;

ELECT City FROM Customers
UNION ALL
SELECT City FROM Suppliers
ORDER BY City;

[chunvv]

3. Enum and Set: what are the differences?

• What happens if we insert a value does not exists in Enum?

CREATE TABLE setTest(
  attrib SET('bold','italic','underline')
);

INSERT INTO setTest (attrib) VALUES ('bold');
INSERT INTO setTest (attrib) VALUES ('bold,italic');
INSERT INTO setTest (attrib) VALUES ('bold,italic,underline'); # Check again here 

CREATE TABLE enumTest(
 color ENUM('red','green','blue')
);

INSERT INTO enumTest (color) VALUES ('red');
INSERT INTO enumTest (color) VALUES ('gray');
INSERT INTO enumTest (color) VALUES ('red,green'); # Check again here

[chunvv]

4. What is: AUTO_INCREMENT, PRIMARY KEY and SERIAL?

SERIAL is an alias for BIGINT UNSIGNED NOT NULL AUTO_INCREMENT UNIQUE.

[chunvv]

5. MySQL join types?

=> Check one again here

[chunvv]

6. What is Triggers?

A trigger is a set of actions that are run automatically when a specified change operation (SQL INSERT, UPDATE, or DELETE statement) is performed on a specified table. Triggers are useful for tasks such as enforcing business rules, validating input data, and keeping an audit trail.

CREATE TABLE contacts
( contact_id INT(11) NOT NULL AUTO_INCREMENT,
  last_name VARCHAR(30) NOT NULL,
  first_name VARCHAR(25),
  birthday DATE,
  created_date DATE,
  created_by VARCHAR(30),
  CONSTRAINT contacts_pk PRIMARY KEY (contact_id)
);

DELIMITER //

CREATE TRIGGER contacts_before_insert
BEFORE INSERT
   ON contacts FOR EACH ROW

BEGIN

   DECLARE vUser varchar(50);

   -- Find username of person performing INSERT into table
   SELECT USER() INTO vUser;

   -- Update create_date field to current system date
   SET NEW.created_date = SYSDATE();

   -- Update created_by field to the username of the person performing the INSERT
   SET NEW.created_by = vUser;

END; //

DELIMITER ;

[chunvv]

7. What is View in MySQL?

In SQL, a view is a virtual table based on the result-set of an SQL statement.

A view contains rows and columns, just like a real table. The fields in a view are fields from one or more real tables in the database.

CREATE VIEW [Brazil Customers] AS
SELECT CustomerName, ContactName
FROM Customers
WHERE Country = "Brazil";

Để rõ hơn thì mình lấy một ví dụ thế này: Giả sử trang Frontend có một Block gọi là hiển thị 10 tin mới nhất, như vậy ta sẽ truy vấn lấy 10 tin và sắp xếp giảm dần theo ID. Nhưng bạn biết trong SQL nó sẽ duyệt toàn bộ bảng rồi mới trả về kết quả và điều này làm cho truy vấn trở nên chậm chạm. Để giải quyết nó thì ta sẽ tạo một View gồm 10 tin mới nhất và lúc hiển thị ra chỉ cần lấy trong View nên tốc độ sẽ nhanh hơn rất nhiều lần.

[chunvv]

8. What is a temporary table? Performance? Live time?

Point: lưu trữ và xử lý kết quả trung gian 

[chunvv]

9. What is heap table in MySQL?

[chunvv]

ABD (Active Book Dialog): すべての人が、本を好きになるために​

Points:

• Able to read in a short time ⏳
• Summary remain 🌞
• High retention of rate memory 🎗
• Deep learning on sharing ideas/opinions/questions 💯
• Improve lots of skills ⛷
• Same language 💬
• Community 👥
• Fun ❤️

Refer:

http://www.abd-abd.com/

[chunvv]

昼会：

• Main purpose of hirukai in the company (KPT method):

  • To know what other members are doing?
  • What they are thinking: problems? solutions?

• But we are working on teh different company,so we will follow the following questions:

  • Your current working company name?
  • What kind of services you are developing?
  • What kind of technologies?
  • Your repositories in your team?
  • Do you love your company?
  • Your challenges in your company?

[chunvv]

ABD result:

From Tai Tran:

[chunvv]

From Trung Vu:

[chunvv]

From Quang Minh:

[chunvv]

From Son Tran:

[chunvv]

From Duc Phan:

[chunvv]

From Quy Vu: