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?

```sql SELECT City FROM Customers UNION SELECT City FROM Suppliers ORDER BY City; ``` ```sql 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?

```sql 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 ``` ```sql 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. ```sql 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) ); ``` ```sql 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. ```sql 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: