10 Câu Hỏi Nâng Cao Về MySQL & PHP

 10 Câu Hỏi Nâng Cao Về MySQL

1. Trong một hệ thống có hàng triệu bản ghi, làm thế nào để bạn tối ưu hóa hiệu suất của các truy vấn phức tạp liên quan đến nhiều bảng, đặc biệt khi sử dụng các phép JOIN và GROUP BY?

(Kiểm tra khả năng tối ưu hóa truy vấn ở quy mô lớn.)

2. Bạn đã từng làm việc với MySQL partitioning chưa? Hãy mô tả một tình huống cụ thể mà bạn đã sử dụng partitioning để cải thiện hiệu suất, và kết quả ra sao?

(Tập trung vào kỹ thuật nâng cao để quản lý dữ liệu lớn.)

3. Khi thiết kế một hệ thống cần đảm bảo tính sẵn sàng cao (high availability) với MySQL, bạn sẽ triển khai kiến trúc nào, và làm thế nào để xử lý các vấn đề như failover và đồng bộ dữ liệu?

(Kiểm tra kinh nghiệm với kiến trúc hệ thống và HA.)

4. Làm thế nào để bạn xử lý các vấn đề về deadlock trong MySQL khi nhiều giao dịch đồng thời truy cập cùng một tập dữ liệu? Hãy đưa ra một ví dụ cụ thể.

(Tập trung vào quản lý giao dịch và giải quyết xung đột.)

5. MySQL có hỗ trợ full-text search. Bạn đã từng sử dụng tính năng này chưa? Nếu có, hãy mô tả cách bạn triển khai và những hạn chế bạn gặp phải.

(Kiểm tra kinh nghiệm với tính năng nâng cao của MySQL.)

6. Khi làm việc với MySQL trong một ứng dụng Laravel, làm thế nào để bạn xử lý các truy vấn phức tạp mà Eloquent không thể đáp ứng một cách hiệu quả?

(Kết hợp kinh nghiệm Laravel với MySQL nâng cao.)

7. Bạn đã từng sử dụng EXPLAIN ANALYZE trong MySQL chưa? Hãy giải thích cách bạn sử dụng nó để tối ưu hóa một truy vấn cụ thể trong một dự án.

(Tập trung vào phân tích và tối ưu hóa truy vấn.)

8. Làm thế nào để bạn triển khai một hệ thống sao lưu (backup) và khôi phục (restore) cho MySQL trong môi trường production, và bạn xử lý các vấn đề về dữ liệu lớn như thế nào?

(Kiểm tra kinh nghiệm DevOps và quản lý cơ sở dữ liệu.)

9. MySQL có hỗ trợ các tính năng như CTE (Common Table Expressions) và window functions. Bạn đã từng sử dụng chúng chưa? Hãy đưa ra một ví dụ cụ thể.

(Tập trung vào các tính năng SQL nâng cao.)

10. Khi làm việc với một hệ thống phân tán, làm thế nào để bạn đảm bảo tính nhất quán dữ liệu (data consistency) giữa các node MySQL, và bạn đã gặp phải thách thức gì?

(Kiểm tra kinh nghiệm với hệ thống phân tán và đồng bộ dữ liệu.)

10 Câu Hỏi Nâng Cao Về PHP

1. Trong một ứng dụng Laravel có lưu lượng truy cập cao, làm thế nào để bạn triển khai một hệ thống hàng đợi (queue) để xử lý các tác vụ nặng như gửi email hoặc xử lý dữ liệu lớn? Hãy mô tả chi tiết.

(Tập trung vào xử lý tác vụ bất đồng bộ và tối ưu hóa hiệu suất.)

2. Bạn đã từng sử dụng PHP để xây dựng một hệ thống microservices chưa? Nếu có, hãy mô tả cách bạn thiết kế và những thách thức bạn gặp phải.

(Kiểm tra kinh nghiệm với kiến trúc microservices.)

3. Làm thế nào để bạn triển khai một hệ thống caching phân tán (distributed caching) trong Laravel, và bạn đã sử dụng nó để giải quyết vấn đề gì trong một dự án?

(Tập trung vào tối ưu hóa hiệu suất với caching.)

4. Khi làm việc với Laravel, làm thế nào để bạn xử lý các vấn đề về hiệu suất khi ứng dụng phải xử lý hàng nghìn request mỗi giây? Hãy đưa ra ví dụ cụ thể.

(Kiểm tra kinh nghiệm tối ưu hóa hiệu suất ở quy mô lớn.)

5. Bạn đã từng sử dụng PHP để triển khai một hệ thống event-driven chưa? Hãy mô tả cách bạn thiết kế và những công cụ bạn sử dụng.

(Tập trung vào kiến trúc event-driven và xử lý sự kiện.)

6. Làm thế nào để bạn triển khai một hệ thống xác thực (authentication) tùy chỉnh trong Laravel cho một ứng dụng doanh nghiệp, và bạn xử lý các yêu cầu bảo mật phức tạp như thế nào?

(Kiểm tra kinh nghiệm với bảo mật và tùy chỉnh Laravel.)

7. Bạn đã từng làm việc với PHP để xử lý các tác vụ đồng thời (concurrency) chưa? Hãy mô tả cách bạn sử dụng các công cụ như Swoole hoặc các kỹ thuật khác.

(Tập trung vào xử lý đồng thời trong PHP.)

8. Khi làm việc với một ứng dụng Laravel lớn, làm thế nào để bạn quản lý các dependency và đảm bảo mã có thể kiểm thử (testable)? Hãy đưa ra ví dụ cụ thể.

(Kiểm tra kiến thức về thiết kế phần mềm và kiểm thử.)

9. Bạn đã từng sử dụng PHP để xây dựng một hệ thống xử lý dữ liệu thời gian thực (real-time) chưa? Nếu có, hãy mô tả cách bạn triển khai và những thách thức bạn gặp phải.

(Tập trung vào ứng dụng thời gian thực và WebSocket.)

10. Làm thế nào để bạn triển khai một hệ thống logging và monitoring trong một ứng dụng Laravel để phát hiện và xử lý lỗi trong môi trường production?

(Kiểm tra kinh nghiệm DevOps và quản lý ứng dụng.)

New features in MySQL 8

MySQL 8 introduced several powerful new features and improvements that make it a significant update over previous versions. Here are some of the key new features in MySQL 8:

1. Window Functions

• MySQL 8 added support for window functions like ROW_NUMBER(), RANK(), DENSE_RANK(), NTILE(), LEAD(), and LAG(). These functions allow for more advanced analytical queries, letting users calculate moving averages, ranks, cumulative sums, and more.
  

* Use RANK

 SELECT  s.student_id, s.student_name, SUM(p.score) AS total_score,
 RANK() OVER (ORDER BY SUM(p.score) DESC) AS rank_score
 FROM demo.student s
 JOIN demo.point p ON s.student_id = p.student_id
 GROUP BY s.student_id, s.student_name
 ORDER BY rank_score DESC

* Error

 SELECT  s.student_id, s.student_name, SUM(p.score) AS total_score,
 RANK() OVER (ORDER BY SUM(p.score) DESC) AS rank_score
 FROM demo.student s
 JOIN demo.point p ON s.student_id = p.student_id
 WHERE rank_score > 3
 GROUP BY s.student_id, s.student_name
 ORDER BY rank_score DESC

=> The issue here is that window functions like RANK() cannot be used directly in the WHERE clause because they are applied after the GROUP BY and SELECT operations. You need to compute the ranking in a subquery or CTE and then filter on it in the outer query.

* Fix use subquery

SELECT student_id, student_name, total_score, rank_score
FROM (
    SELECT s.student_id, s.student_name,
           SUM(p.score) AS total_score,
           RANK() OVER (ORDER BY SUM(p.score) DESC) AS rank_score
    FROM demo.student s
    JOIN demo.point p ON s.student_id = p.student_id
    GROUP BY s.student_id, s.student_name
) AS ranked_students
WHERE rank_score > 3
ORDER BY rank_score DESC;

* Fix use CTE

WITH RankedStudents AS (
    SELECT s.student_id, s.student_name,
           SUM(p.score) AS total_score,
           RANK() OVER (ORDER BY SUM(p.score) DESC) AS rank_score
    FROM demo.student s
    JOIN demo.point p ON s.student_id = p.student_id
    GROUP BY s.student_id, s.student_name
)
SELECT student_id, student_name, total_score, rank_score
FROM RankedStudents
WHERE rank_score > 3
ORDER BY rank_score DESC;

Key Differences:

Aspect	Subquery	CTE
Structure	Defined inline in the FROM clause.	Defined before the SELECT statement.
Readability	Can be harder to read for complex queries.	Easier to read and maintain, especially for complex queries.
Reuse	Cannot be reused within the same query.	Can be reused multiple times in the query (in case of multiple references to the same CTE).
Scope	Available only within the query block in which it is defined.	Available throughout the query until the end.
Performance	Often optimized by the database engine but may involve recalculating the subquery each time it is referenced.	Sometimes optimized by the database, and CTEs are often materialized (especially in PostgreSQL, but not always in MySQL).
Materialization	Subquery results might be materialized only once in certain scenarios, but not guaranteed.	In MySQL, CTEs are generally not materialized, so they are evaluated each time they are referenced.

Conclusion:

• Use Subqueries when:
  • You have a simpler query.
  • You don’t need to reference the same intermediate result multiple times.
  • Performance is slightly more important than readability.
• Use CTEs when:
  • You have a complex query that would benefit from being split into logical steps.
  • You need to reference the same intermediate result multiple times in the query.
  • You prioritize readability and maintainability over minor performance differences.

In Your Case:

Since both the subquery and the CTE versions are likely to perform similarly in MySQL 8.0, the decision comes down to readability. If you are working with a simple query, a subquery might suffice. If you expect the query to grow in complexity or if you want a cleaner structure, a CTE would be more maintainable.

2. Common Table Expressions (CTEs)

• With CTEs, users can define temporary result sets to be referenced within a SELECT, INSERT, UPDATE, or DELETE query. MySQL 8 supports recursive CTEs, making it easier to work with hierarchical data, such as organizational charts or file systems.

WITH RECURSIVE cte AS (
    SELECT 1 AS n
    UNION ALL
    SELECT n + 1 FROM cte WHERE n < 5
)
SELECT * FROM cte;

3. JSON Enhancements

• MySQL 8 includes additional functions for handling JSON data, including JSON_TABLE() (which turns JSON data into a relational format) and JSON_OVERLAPS() (to check if two JSON arrays have common elements).

4. Improved Indexing with Invisible Indexes

• MySQL 8 introduced invisible indexes, which let you mark an index as invisible to the query optimizer. This is useful for testing how queries perform without a specific index, without fully deleting it.

ALTER TABLE table_name ALTER INDEX index_name INVISIBLE;

5. Roles for Better Access Control

• MySQL 8 added support for roles, allowing for easier management of user permissions. Roles can be created with specific privileges and then assigned to users, simplifying complex privilege assignments.

CREATE ROLE read_only;
GRANT SELECT ON database.* TO read_only;
GRANT read_only TO user_name;

6. Generated Columns

• MySQL 8 introduced generated (computed) columns, which can store data based on expressions or other column values. Generated columns can be virtual (calculated on-the-fly) or stored (physically stored in the table).

ALTER TABLE employees
ADD COLUMN full_name VARCHAR(255) GENERATED ALWAYS AS (CONCAT(first_name, ' ', last_name)) VIRTUAL;

7. Better Support for Geographic Data with Spatial Indexing

• MySQL 8 improved spatial data support by adding spatial indexes for InnoDB tables, making it more efficient for geographic or location-based queries.

8. Atomic DDL Statements

• In MySQL 8, DDL (Data Definition Language) operations are atomic and crash-safe, meaning that a failure during a CREATE, ALTER, DROP, or RENAME operation can be rolled back automatically.

9. Improved UTF-8 Support with utf8mb4

• MySQL 8 made utf8mb4 the default character set, supporting a wider range of Unicode characters, including emojis. The utf8mb4 encoding is now recommended for improved compatibility with modern web applications.

10. Descending Indexes

• MySQL 8 allows users to create descending indexes, optimizing queries that need sorted data in descending order without needing additional sorting at query time.

CREATE INDEX idx_desc ON table_name(column_name DESC);

11. Histograms for Better Query Optimization

• MySQL 8 supports histograms, which provide more accurate statistics for the query optimizer, helping it choose the best execution plans for complex queries. Histograms work especially well for non-indexed columns.

ANALYZE TABLE table_name UPDATE HISTOGRAM ON column_name;

12. Improved Performance Schema and Sys Schema

• MySQL 8 expanded the Performance Schema and introduced the Sys Schema as a helper schema, making it easier to monitor and optimize database performance. The Sys Schema provides user-friendly views for checking performance metrics.

13. Resource Groups

• Resource Groups in MySQL 8 allow users to allocate specific CPU resources to certain queries or workloads, improving control over query performance for different tasks.

CREATE RESOURCE GROUP rg1 TYPE = USER VCPU = 1,2;

14. Optimizer Hints with SET_VAR

• MySQL 8 introduced the SET_VAR hint, allowing users to override session-level system variables for specific queries, such as changing the max_execution_time for a single query.

SELECT /*+ SET_VAR(max_execution_time=5000) */ * FROM large_table;

15. Improved InnoDB and Backup Features

• Enhancements to InnoDB include improved support for bulk loading, reduced redo log size, and better crash recovery. Backup performance and reliability are also improved with these changes.

These features make MySQL 8 far more powerful, flexible, and performant for complex, high-demand applications.

==============

Example database

CREATE TABLE IF NOT EXISTS `point` (
  `point_id` int NOT NULL,
  `student_id` int DEFAULT NULL,
  `subject` varchar(50) DEFAULT NULL,
  `score` decimal(5,2) DEFAULT NULL,
  PRIMARY KEY (`point_id`),
  KEY `student_id` (`student_id`),
  CONSTRAINT `point_ibfk_1` FOREIGN KEY (`student_id`) REFERENCES `student` (`student_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

-- Dumping data for table demo.point: ~10 rows (approximately)
INSERT INTO `point` (`point_id`, `student_id`, `subject`, `score`) VALUES
    (1, 1, 'Math', 9.00),
    (2, 1, 'English', 9.00),
    (3, 2, 'Math', 8.00),
    (4, 2, 'English', 8.00),
    (5, 3, 'Math', 8.00),
    (6, 3, 'English', 8.00),
    (7, 4, 'Math', 6.00),
    (8, 4, 'English', 6.00),
    (9, 5, 'Math', 5.00),
    (10, 5, 'English', 5.00);

-- Dumping structure for table demo.student
CREATE TABLE IF NOT EXISTS `student` (
  `student_id` int NOT NULL,
  `student_name` varchar(50) DEFAULT NULL,
  `age` int DEFAULT NULL,
  PRIMARY KEY (`student_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

-- Dumping data for table demo.student: ~5 rows (approximately)
INSERT INTO `student` (`student_id`, `student_name`, `age`) VALUES
    (1, 'Alice', 20),
    (2, 'Bob', 22),
    (3, 'Charlie', 21),
    (4, 'David', 23),
    (5, 'Eva', 20);

 Thank you

SQL clauses and the order in which they are executed

SQL Execution Order (Logical Order):

1. FROM:
   • This clause specifies the tables (or other data sources) from which to retrieve data. It happens first because it determines which rows are available to the rest of the query.
2. JOIN:
   • If you're using JOIN, it’s performed immediately after FROM. The join operation combines data from multiple tables based on a condition.
3. WHERE:
   • Filters the rows returned by the FROM and JOIN clauses based on a condition. The rows are selected after the join operations.
4. GROUP BY:
   • Groups the rows based on the columns you specify. After filtering (WHERE), SQL groups the data based on specified columns.
5. HAVING:
   • Similar to WHERE, but it is used to filter groups created by GROUP BY. You would use HAVING to filter out groups based on aggregated results, such as SUM, COUNT, etc.
6. SELECT:
   • Selects the columns to be displayed in the final output. The SELECT clause is executed after FROM, JOIN, WHERE, and GROUP BY (and HAVING if used), because it operates on the filtered and grouped data.
7. DISTINCT:
   • If you’re using DISTINCT, it happens immediately after SELECT to remove duplicate rows from the result set.
8. ORDER BY:
   • The data is sorted based on the columns specified in the ORDER BY clause. Sorting occurs after SELECT and DISTINCT (if used), and it organizes the rows in ascending or descending order.
9. LIMIT / OFFSET:
   • These clauses are used to restrict the number of rows returned. They occur last, after all sorting and selection of rows are done.

Example:

SELECT DISTINCT s.student_id, s.student_name, SUM(p.score) AS total_score
FROM student s
JOIN point p ON s.student_id = p.student_id
WHERE s.age > 18
GROUP BY s.student_id, s.student_name
HAVING SUM(p.score) > 50
ORDER BY total_score DESC
LIMIT 1 OFFSET 2;
Execution Order for the Above Query:
FROM: Start with the student and point tables.
JOIN: Combine student and point based on the student_id column.
WHERE: Filter the students who are older than 18 years.
GROUP BY: Group the remaining rows by student_id and student_name.
HAVING: Filter the groups where the total score is greater than 50.
SELECT: Select the columns student_id, student_name, and the aggregated SUM(p.score) as total_score.
DISTINCT: Remove any duplicate rows if necessary.
ORDER BY: Sort the results by total_score in descending order.
LIMIT/OFFSET: Select the 3rd row in the sorted result (because of LIMIT 1 OFFSET 2).

Thank you

Big data import process

Big data import process:

1. Stream Data Line-by-Line: Read the file line-by-line to keep memory usage low.

2. Batch into Groups of 1,000: Accumulate rows into batches of 1,000 for efficient bulk inserts/updates.

3. Queue Each Batch for Processing: Dispatch each batch to a job queue, enabling asynchronous processing and distributing the load.

4. Use Transactions for Data Consistency: Wrap database operations in a transaction within each job to ensure that either all rows are processed or none, preserving data integrity.

5. Execute Multiple Jobs Concurrently: Configure Laravel’s queue workers to process multiple jobs in parallel, speeding up the import process.
   

Thank you

Config SSL / TLS for MySQL on Ubuntu

 

Reference

- https://galaxyz.net/cach-cau-hinh-ssl-tls-cho-mysql-tren-ubuntu-1804.136.anews 

Thank you.

Remote MySQL database connection

1. Edit nano /etc/mysql/mysql.conf.d/mysqld.cnf

bind-address = 0.0.0.0

2. Create user and set permission for user remote accesss

mysql -u root -p
CREATE USER 'your_user'@'%' IDENTIFIED BY 'your_password';
GRANT ALL PRIVILEGES ON database_name.* TO 'your_user'@'%';
GRANT ALL PRIVILEGES ON *.* TO 'your_user'@'%';
FLUSH PRIVILEGES;

3. Restart mysql 

sudo systemctl restart mysql

4. Open port mysql 3306 

sudo ufw allow 3306/tcp

Thank you

Fulltext Search in Mysql

1. What to use Fulltext Search for?

- Using for searching of text data in a large volumes, for text data stored in columns of type Char, Varchar, Text.

- Mode search in Fulltext search:

+ Natural language
+ Boolean search

- How Fulltext search working, ex:
We have 3 documents D1, D2, D3

D1 = "This is first document"
D2 = "This is second one"
D3 = "one two"

=> inverted index of 3 documents is:

"this" => {D1, D2}
"is" => {D1, D2}
"first" => {D1}
"document" => {D1}
"second" => {D2}
"one" => {D2, D3}
"two" => {D3}

=> search result of "this is first" is

{D1, D2} union {D1, D2} union {D1} = {D1}

- N-gram technique

 - Is technique device a string to children strings has same length (N) example: unigram (N=1), bigram (N=2), trigram (N=3). Ex apply when search Japanese language (because Japanese don't has space)

- Morphological Analysis: Using for searching natural language.

2. How to use Fulltext Search?

CREATE TABLE documents (
id INT AUTO_INCREMENT,
content TEXT,
PRIMARY KEY (id),
FULLTEXT (content)
);

-- Perform a Full-Text Search
SELECT * FROM documents WHERE MATCH(content) AGAINST('search query');

3. What does Fulltext Search include? 

Thank you

Explain statement in Mysql

1. What to use Explain statement for?

- Using explain in mysql to obtain information about how Mysql execute a select statement.
- Information Explain Statement provide:
+ Type: follow all->index->range->ref->eq->ref->const->system.
+ select_type: SIMPLE for simple select; SUBQUERY for a subquery
+ Rows: number of rows checked
+ Table: table involved in the select operation.
......

2. How to use Explain statement ?

EXPLAIN SELECT * FROM my_table WHERE id = 1;

Example

explain SELECT * FROM `order_info` WHERE note LIKE 'keywork'

explain SELECT * FROM `order_info` WHERE client_id = 1

3. What does Explain statement include? 

Thank you

Mysql tutorial from basic to advanced

1. Outline learning Mysql
- Mysql 

- Fulltext Search in Mysql

- Explain statement in Mysql

- Configuration for Quick add in Laragon

- Install Mysql for Ubuntu

- Install Mysql for Laragon

- Guide to install Apache2, Php 8.1, MySql 5.6, Mssql driver

- Remote MySQL database connection 

- Config SSL / TLS for MySQL on Ubuntu

2. What to use Mysql for?

3. How to use Mysql ?

(1) sudo nano /etc/mysql/my.cnf

#
# The MySQL database server configuration file.
#
# You can copy this to one of:
# - "/etc/mysql/my.cnf" to set global options,
# - "~/.my.cnf" to set user-specific options.
#
# One can use all long options that the program supports.
# Run program with --help to get a list of available options and with
# --print-defaults to see which it would actually understand and use.
#
# For explanations see
# http://dev.mysql.com/doc/mysql/en/server-system-variables.html

#
# * IMPORTANT: Additional settings that can override those from this file!
# The files must end with '.cnf', otherwise they'll be ignored.
#

!includedir /etc/mysql/conf.d/
!includedir /etc/mysql/mysql.conf.d/

(2) sudo nano /etc/mysql.conf.d/mysqld.cnf

#

# The MySQL database server configuration file.

#

# One can use all long options that the program supports.

# Run program with --help to get a list of available options and with

# --print-defaults to see which it would actually understand and use.

#

# For explanations see

# http://dev.mysql.com/doc/mysql/en/server-system-variables.html

# Here is entries for some specific programs

# The following values assume you have at least 32M ram

[mysqld]

#

# * Basic Settings

#

user            = mysql

# pid-file      = /var/run/mysqld/mysqld.pid

# socket        = /var/run/mysqld/mysqld.sock

# port          = 3306

# datadir       = /var/lib/mysql

# If MySQL is running as a replication slave, this should be

# changed. Ref https://dev.mysql.com/doc/refman/8.0/en/server-system-variables.html#sysvar_tmpdir

# tmpdir                = /tmp

#

# Instead of skip-networking the default is now to listen only on

# localhost which is more compatible and is not less secure.

bind-address            = 0.0.0.0

mysqlx-bind-address     = 127.0.0.1

#

# * Fine Tuning

#

key_buffer_size         = 16M

# max_allowed_packet    = 64M

# thread_stack          = 256K

# thread_cache_size       = -1

# This replaces the startup script and checks MyISAM tables if needed

# the first time they are touched

myisam-recover-options  = BACKUP

# max_connections        = 151

# table_open_cache       = 4000

#

# * Logging and Replication

#

# Both location gets rotated by the cronjob.

#

# Log all queries

# Be aware that this log type is a performance killer.

# general_log_file        = /var/log/mysql/query.log

# general_log             = 1

#

# Error log - should be very few entries.

#

log_error = /var/log/mysql/error.log

#

# Here you can see queries with especially long duration

# slow_query_log                = 1

# slow_query_log_file   = /var/log/mysql/mysql-slow.log

# long_query_time = 2

# log-queries-not-using-indexes

#

# The following can be used as easy to replay backup logs or for replication.

# note: if you are setting up a replication slave, see README.Debian about

#       other settings you may need to change.

# server-id             = 1

# log_bin                       = /var/log/mysql/mysql-bin.log

# binlog_expire_logs_seconds    = 2592000

max_binlog_size   = 100M

# binlog_do_db          = include_database_name

# binlog_ignore_db      = include_database_name

Explain config

4. Quizzes for Mysql

- MySQL Quiz (part 01)

5. Errors in Mysql 

- SQLSTATE[42000]: Syntax error or access violation: 1071 Specified key was too long; max key length is 767 bytes (in Mysql 5.6) 

 

Reference
- https://www.jenkins.io/doc/tutorials/
- https://www.tutorialspoint.com/jenkins/index.htm

Thank you