MySQL 必知必会

MySQL 性能

MySQL 索引概览_wallace_www的博客-CSDN博客

MySQL索引原理及慢查询优化

为什么 MySQL 使用 B+ 树 - 面向信仰编程

Database Index: A Re-visit to B+ Tree

MySQL实战45讲

UPDATE

How to update multiple rows at once in MySQL?

UPDATE students SET math = 5, english = 8 WHERE id = 1;
UPDATE students SET math = 10, english = 8 WHERE id = 2;
UPDATE students SET math = 8, english = 3 WHERE id = 3;

INSERT … ON DUPLICATE KEY UPDATE

INSERT INTO students (id, score1, score2)
VALUES
(1, 5, 8),
(2, 10, 8),
(3, 8, 3),
(4, 10, 7)
ON DUPLICATE KEY UPDATE
	score1 = VALUES(score1),
  score2 = VALUES(score2);

CASE WHEN

UPDATE categories
	SET order = CASE id
		WHEN 1 THEN 3
		WHEN 2 THEN 4
		WHEN 3 THEN 5
	END,
	title = CASE id
		WHEN 1 THEN 'New Title 1'
		WHEN 2 THEN 'New Title 2'
		WHEN 3 THEN 'New Title 3'
	END
WHERE id IN (1,2,3);

JOIN

UPDATE students s
JOIN (
    SELECT 1 as id, 5 as new_math, 8 as new_english
    UNION ALL
    SELECT 2, 10, 8
    UNION ALL
    SELECT 3, 8, 3
    UNION ALL
    SELECT 4, 10, 7
) vals ON s.id = vals.id
SET math = new_math, english = new_english;

replace into vs. Insert into on duplicate key update

REPLACE internally performs a delete and then an insert. This can cause problems if you have a foreign key constraint pointing at that row. In this situation the REPLACE could fail or worse: if your foreign key is set to cascade delete, the REPLACE will cause rows from other tables to be deleted. This can happen even though the constraint was satisfied both before and after the REPLACE operation. Using INSERT ... ON DUPLICATE KEY UPDATE avoids this problem and is therefore preferred.