???? ?? ?? MySQL ??: ??, ??? ? ??? ?????

MySQL? ?? ?? ???? ??? ?????? ? ???? ???? ?? ???? ???? ??? ??????? ??? ? ?? ??? ??? ?????. ??? MySQL? ???? ??? ????? ???? ?? ??? ??? ? ?? ????? ???. ? ?????? ??? ?????, ??? ????, ??????? ????? ???? ? ??? ? ? ?? ?? ?????? ? ???? ?? ? ?? MySQL ??? ????.

---

1. ???? ?? ?? ?? ???

???? ?? ?? ??? ??? ? ?? ????? ???? ????? ??, ??, ???? ??? ???? ?? ??? ????? ? ?????.

• ?? ??? ??: ??? WHERE, JOIN ?? ORDER BY ?? ?? ?? ??? ?? ?? ???? ??? ? ? ????.

  CREATE INDEX idx_name_dept ON employees(name, department);

• ??? ???: ??? ????? ??? ??? ?? ?? ?????. ?, MySQL? ???? ????? ??? ????? ?? ??? ??? ? ????.

  CREATE INDEX idx_name_salary ON employees(name, salary);

• ?? ??? ??: ???? ?? ??? ???? ??(??, ????, ??) ??? ????. ???? ??? ???? ???? ?????.

• ?? ???? ?? EXPLAIN: EXPLAIN ???? ???? ?? ?? ??? ???? ?? ??? ?????.
  

  EXPLAIN SELECT * FROM employees WHERE department = 'Engineering';

2. ? ?? ??? ?? ?? ?? ??

MySQL? ?? ??? ?? ??? ??? ? ???? ??? ???? ?? ?? ??? ??? ?? ???? ??? ?? ??? ?????.

• ?? ?? ???: ?? MySQL ??(5.7 ??)??? ?? ??? ????? ?????? ??? ?? ??? ?? ??????? ??? ?? ???? ? ????.

  query_cache_type = 1
  query_cache_size = 128M

• ?? ???: ?? ???? ?????. ????? ?? ?????? ???? ?? ?? ??? ?? ??? ??? ??? ? ????.

3. ??? ?? ?? ??? ??

MySQL ????? ???? ? ???? ? ?? ???? ?? ???? ?? ? ?? ?? ?? ??? ?? ??????? ?? ?? ??? ?????.

• ?? ??: ? ??? ???? ???? ?????(?: ???? ??).

  CREATE INDEX idx_name_dept ON employees(name, department);

• ?? ??: ??? ?? ??? ???? ?????.

  CREATE INDEX idx_name_salary ON employees(name, salary);

4. ?? ???? ? ?? ??

?? ???? ? ??? ???? ?????? ??? ???? ??? ????? ??????? ?????? ?? ?? ??? ?? ??? ???? ? ????.

• ?? ????: ?? ??? ???? ??? ??? ??? ? ???? ??? ??????? ?????? ??????? ???? ?? ? ????.

  EXPLAIN SELECT * FROM employees WHERE department = 'Engineering';

• ??? ?? ??(UDF): SQL ??? ??? ? ?? ??? ?? ??? ????.

  query_cache_type = 1
  query_cache_size = 128M

5. ??? ???? ?? ?? ??

MySQL? ????? ?? ?? ?? ????? ???? ????? ??? ???? ???? ???? ? ??????.

• ACID ??: ????? Atomic, ???, ??? ? ???.

• BEGIN, COMMIT, ROLLBACK: BEGIN, COMMIT ? ROLLBACK? ???? ????? ?????.
  

  CREATE TABLE orders (
    order_id INT,
    order_date DATE,
    amount DECIMAL(10, 2)
  )
  PARTITION BY RANGE (YEAR(order_date)) (
    PARTITION p2018 VALUES LESS THAN (2019),
    PARTITION p2019 VALUES LESS THAN (2020),
    PARTITION p2020 VALUES LESS THAN (2021)
  );

• ?? ??: ??? ?? ??(READ COMMITTED, REPEATABLE READ, SERIALIZABLE ?)? ???? ???? ?? ???? ???? ?????.

6.

?? ? ?? ?? ???

MySQL? ??? ???

?? ? ?? ??? ?????, ?? ???? ??? ??? ???? ?? ?? ???? ?????.

• JOIN ???: ????? ? ???? ???? ?? ????. ?? JOIN? ??? ?? ????? ??? ?????.

  CREATE TABLE customers (
    customer_id INT,
    region VARCHAR(50)
  )
  PARTITION BY LIST (region) (
    PARTITION north_america VALUES IN ('USA', 'Canada'),
    PARTITION europe VALUES IN ('UK', 'Germany')
  );

• ?? ?? ???: ? ?? ??? ???? SELECT ?? WHERE ?? ?? ??? ???? ???. ???? JOIN? ?????.

  DELIMITER $$

  CREATE PROCEDURE getEmployeeDetails(IN emp_id INT)
  BEGIN
    SELECT name, department, salary FROM employees WHERE id = emp_id;
  END $$

  DELIMITER ;

7.

?? ??? ??? ?? ?? ??? ??

MySQL?

FULLTEXT ???? ???? ??? ??? ??? ??? ? ??? ?? ? ??? ??? ???? ??????? ?????.

• FULLTEXT ??? ??: ??? ?? ?? FULLTEXT ???? ???? ??? ?? ??? ????.

  CREATE INDEX idx_name_dept ON employees(name, department);

• MATCH AGAINST ??: ??? ???? ?? ??? ?? ??? ?? MATCH AGAINST? ???? ?? ??? ??? ?????.

  CREATE INDEX idx_name_salary ON employees(name, salary);

8. ?? ??? ?? ??

??? ???? ?? ?????? ?? ??? ???? ??? ????? ?????. MySQL? ????? ??? ???? ??? ???? ???? ????? Vitess.

? ?? ?? ??? ???? ??? ??? ? ????.

• ?? ??: ?? ID ?? ??? ?? ?? ???? ?? ??????? ???? ?????.

  EXPLAIN SELECT * FROM employees WHERE department = 'Engineering';

• Vitess: MySQL? ?? ???? ??? ??? ???? ??? ?? ??????.

9. ????? ?? ??

MySQL ??? ???? ?? ??? ?? ?????? ???? ???? ???? ???? ?? ? ????.

• ???-???? ??: ?? ???? ??? ???? ?? ????? ?? ??? ?? ????? ?? ???? ?????.

  query_cache_type = 1
  query_cache_size = 128M

• ???-??? ??: ? ?? ?? ??? ???? ?? ???? ??? ? ??? ?? ??? ?????.

10. ???? ? ?????

MySQL ??????? ??? ??? ????? ????? ??????.

• ?? ?? ??: ?? ?? ??? ????? ?? ??? ?????.

  CREATE TABLE orders (
    order_id INT,
    order_date DATE,
    amount DECIMAL(10, 2)
  )
  PARTITION BY RANGE (YEAR(order_date)) (
    PARTITION p2018 VALUES LESS THAN (2019),
    PARTITION p2019 VALUES LESS THAN (2020),
    PARTITION p2020 VALUES LESS THAN (2021)
  );

• ?? ???: MySQL? ?? ???? ?????? ??? ?????? ????? ?? ??? ??? ??? ?????.

  CREATE TABLE customers (
    customer_id INT,
    region VARCHAR(50)
  )
  PARTITION BY LIST (region) (
    PARTITION north_america VALUES IN ('USA', 'Canada'),
    PARTITION europe VALUES IN ('UK', 'Germany')
  );

---

??

?? MySQL ??? ??? ?????? ?? ??????? ??, ??? ? ???? ?? ???? ? ????. ???? ???, ?? ???, ??, ?? ? ??? ?? ??? ???? ??? ???? ????? ???? ???? ??? ? ????. ??? ??? ?? ???? ??? ?????? MySQL ??? ?? ??? ??????? ?????.

? ??? ???? ?? ?? MySQL ??: ??, ??? ? ??? ??? ?? ?????. ??? ??? PHP ??? ????? ?? ?? ??? ?????!