锟斤拷站锟斤拷锟斤拷

锟斤拷锟斤拷位锟矫ｏ拷锟斤拷页 > 锟斤拷锟斤拷锟教筹拷 > 锟教筹拷 > count(*)锟斤拷count(1)锟侥革拷锟斤拷锟届？锟斤拷锟皆憋拷锟绞ｏ拷通锟斤拷锟斤拷锟斤拷锟斤拷十锟斤拷锟斤拷锟斤拷MySQL锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷

count(*)锟斤拷count(1)锟侥革拷锟斤拷锟届？锟斤拷锟皆憋拷锟绞ｏ拷通锟斤拷锟斤拷锟斤拷锟斤拷十锟斤拷锟斤拷锟斤拷MySQL锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷

锟斤拷源锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷|锟斤拷时锟戒：2024-10-21 09:45:56 |锟斤拷锟侥讹拷锟斤拷129锟斤拷|锟斤拷 锟斤拷签锟斤拷 T 锟斤拷 S C 锟斤拷锟斤拷 锟斤拷锟斤拷 锟斤拷 |锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷

一锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷锟紺ount(*)锟斤拷Count(1)锟侥ｏ拷 锟斤拷锟斤拷锟斤拷锟斤拷没锟斤拷锟斤拷锟金，诧拷要锟斤拷锟斤拷 count() 锟斤拷锟斤拷全锟斤拷锟街段ｏ拷锟斤拷 count(1) 锟斤拷锟结。锟斤拷锟斤拷 count() 锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷 MySQL 锟斤拷锟竭伙拷锟斤拷么锟斤拷锟斤拷 锟斤拷锟皆猴拷锟斤拷确锟截革拷锟斤拷锟斤拷锟斤拷 count() 锟斤拷 count(1) 锟斤拷一锟斤拷锟侥ｏ拷锟斤拷锟斤拷确锟斤拷锟斤拷锟斤拷锟斤拷锟�

一锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷锟紺ount(*)锟斤拷Count(1)锟侥ｏ拷

锟斤拷锟斤拷锟斤拷锟斤拷没锟斤拷锟斤拷锟金，诧拷要锟斤拷锟斤拷 count( ) 锟斤拷锟斤拷全锟斤拷锟街段ｏ拷锟斤拷 count(1) 锟斤拷锟结。锟斤拷锟斤拷 count( ) 锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷 MySQL 锟斤拷锟竭伙拷锟斤拷么锟斤拷锟斤拷

锟斤拷锟皆猴拷锟斤拷确锟截革拷锟斤拷锟斤拷锟斤拷 count( ) 锟斤拷 count(1) 锟斤拷一锟斤拷锟侥ｏ拷锟斤拷锟斤拷确锟斤拷锟斤拷锟斤拷锟斤拷锟� count(锟街讹拷)锟斤拷锟斤拷锟斤拷锟� count() 锟斤拷锟角撅拷锟斤拷锟斤拷侄危锟斤拷锟矫� MySQL 锟斤拷锟叫讹拷某锟叫硷拷录锟叫讹拷应锟街讹拷锟角凤拷为 null锟斤拷锟斤拷锟轿� null 锟酵诧拷锟斤拷锟斤拷锟酵筹拷锟斤拷恕锟斤拷锟斤拷 count(锟街讹拷) 锟侥斤拷锟斤拷锟斤拷芑锟叫★拷锟� count( ) 锟斤拷 count(1)锟斤拷

锟斤拷锟解，直锟斤拷执锟斤拷 select (*) from t1; 时锟斤拷也锟斤拷锟斤拷锟斤拷锟矫碉拷锟斤拷锟斤拷锟侥ｏ拷锟斤拷锟斤拷一锟斤拷锟斤拷全锟斤拷扫锟借，也锟斤拷锟斤拷扫锟斤拷某锟斤拷锟斤拷锟斤拷 B+ 锟斤拷锟斤拷叶锟接节点，锟接讹拷锟矫碉拷锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷为锟斤拷锟斤拷锟斤拷什么锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷锟角革拷锟斤拷锟斤拷锟斤拷锟斤拷实锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷叶锟接节碉拷锟斤拷锟斤拷锟斤拷锟揭伙拷锟斤拷模锟街伙拷锟斤拷锟斤拷侄锟斤拷锟斤拷锟揭伙拷锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷锟饺拷锟斤拷侄危锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷锟街伙拷锟斤拷硕锟斤拷锟斤拷锟斤拷锟斤拷锟街讹拷 + 锟斤拷锟斤拷锟街段ｏ拷锟斤拷锟斤拷通锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷锟角革拷占锟矫空硷拷模锟斤拷锟剿憋拷锟斤拷锟斤拷锟斤拷也锟斤拷锟斤拷欤拷锟斤拷羌锟铰硷拷锟斤拷锟斤拷锟揭伙拷锟斤拷摹锟�

锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷锟角白涸拷锟侥ｏ拷

锟斤拷锟皆拷锟斤拷锟斤拷锟斤拷只锟斤拷锟节革拷锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷嗖匡拷值锟斤拷锟斤拷希锟斤拷锟斤拷锟斤拷锟斤拷鼙锟斤拷呕锟斤拷锟斤拷锟斤拷浠八碉拷锟斤拷锟绞癸拷酶锟斤拷锟斤拷锟斤拷锟绞憋拷锟斤拷锟窖拷锟斤拷锟斤拷锟接︼拷么锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷锟叫匡拷始锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷锟�

锟斤拷锟角达拷锟斤拷一锟斤拷锟津单碉拷示锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷为 employees 锟斤拷锟斤拷锟斤拷锟斤拷锟较达拷锟斤拷一锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷锟结构锟斤拷锟铰ｏ拷 

CREATE TABLE employees (
    id INT AUTO_INCREMENT PRIMARY KEY,
    first_name VARCHAR(50),
    last_name VARCHAR(50),
    age INT,
    department VARCHAR(50),
    INDEX idx_name_age (last_name, first_name, age)
);

锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷锟角诧拷锟斤拷一些实锟斤拷锟斤拷锟斤拷 

INSERT INTO employees (first_name, last_name, age, department) VALUES
('John', 'Doe', 30, 'HR'),
('Jane', 'Doe', 25, 'IT'),
('Mary', 'Smith', 35, 'Finance'),
('Michael', 'Johnson', 40, 'IT'),
('Emily', 'Davis', 29, 'HR');

锟斤拷锟斤拷锟斤拷锟斤拷前缀原锟斤拷牟锟窖疭QL 

SELECT * FROM employees WHERE last_name = 'Doe' AND first_name = 'Jane' AND age=25;
SELECT * FROM employees WHERE  first_name = 'Jane' AND last_name = 'Doe' AND age=30;
SELECT * FROM employees WHERE  age=30 AND last_name = 'Doe' AND first_name = 'Jane';

锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷些锟斤拷询锟斤拷MySQL锟斤拷使锟斤拷idx_name_age锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷锟杰癸拷锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷SQL锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷Where锟斤拷锟斤拷顺锟斤拷没锟叫癸拷系 

+----+-------------+-----------+-------+---------------+---------+---------+------+-------+-------------+
| id | select_type | table     | type  | possible_keys | key     | key_len | ref  | rows  | Extra       |
+----+-------------+-----------+-------+---------------+---------+---------+------+-------+-------------+
|  1 | SIMPLE      | employees | range | idx_name_age  | idx_name_age | 100     | NULL |     2 | Using where |
+----+-------------+-----------+-------+---------------+---------+---------+------+-------+-------------+

锟斤拷锟斤拷锟街达拷屑苹锟斤拷校锟斤拷锟斤拷强锟斤拷锟絫ype锟斤拷range锟斤拷说锟斤拷MySQL锟斤拷锟斤拷使锟斤拷idx_name_age锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷只锟斤拷锟斤拷舜锟皆�2锟斤拷锟斤拷锟捷★拷

锟斤拷锟斤拷锟斤拷锟絣ast_name去锟斤拷锟截ｏ拷

锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷前缀原锟斤拷牟锟窖� 

SELECT * FROM employees WHERE first_name = 'Jane' AND age = 25;

锟斤拷锟斤拷锟斤拷锟斤拷锟窖拷锟組ySQL锟斤拷锟斤拷使锟矫革拷锟斤拷锟斤拷锟斤拷idx_name_age锟斤拷锟斤拷为锟斤拷没锟叫达拷锟斤拷锟斤拷锟斤拷锟斤拷last_name锟斤拷始锟斤拷

通锟斤拷Explain执锟叫计伙拷锟斤拷锟介看锟斤拷锟斤拷执锟斤拷锟斤拷锟� 

+----+-------------+-----------+-------+---------------+------+---------+------+-------+-------------+
| id | select_type | table     | type  | possible_keys | key  | key_len | ref  | rows  | Extra       |
+----+-------------+-----------+-------+---------------+------+---------+------+-------+-------------+
|  1 | SIMPLE      | employees | ALL   | NULL          | NULL | NULL    | NULL |     5 | Using where |
+----+-------------+-----------+-------+---------------+------+---------+------+-------+-------------+

锟斤拷锟斤拷锟街达拷屑苹锟斤拷校锟斤拷锟斤拷强锟斤拷锟絫ype锟斤拷ALL锟斤拷锟斤拷锟斤拷味锟斤拷MySQL没锟斤拷使锟斤拷锟轿猴拷锟斤拷锟斤拷锟斤拷锟斤拷锟角斤拷锟斤拷锟斤拷全锟斤拷扫锟借，锟斤拷锟斤拷效锟绞较低★拷

锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷一锟捷帮拷锟斤拷锟斤拷锟叫达拷锟剿拷锟绞记ｏ拷一锟斤拷锟接达拷通锟斤拷锟揭碉拷锟轿讹拷锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷原锟斤拷没锟斤拷么锟窖★拷锟斤拷锟角达拷锟斤拷写锟侥ｏ拷 7701页锟斤拷BAT锟斤拷锟斤拷写锟斤拷刷锟斤拷始牵锟斤拷锟斤拷锟給ffer锟矫碉拷锟斤拷锟斤拷

锟杰斤拷

锟斤拷锟斤拷锟斤拷钥锟斤拷锟斤拷锟斤拷锟轿斤拷锟斤拷锟斤拷锟角白涸拷锟侥★拷锟斤拷锟襟”ｏ拷锟斤拷锟斤拷锟斤拷指where锟斤拷锟斤拷锟叫碉拷last_name一锟斤拷要锟斤拷锟斤拷锟斤拷撸锟斤拷锟斤拷锟街竪here锟斤拷锟斤拷锟斤拷一锟斤拷要锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷侄危锟斤拷锟斤拷锟斤拷锟斤拷嵌锟斤拷濉發ast_name, first_name, age锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷SQL为锟斤拷 

INDEX idx_name_age (last_name, first_name, age)

锟斤拷锟斤拷last_name锟街讹拷锟斤拷锟斤拷锟斤拷叩锟斤拷侄危锟斤拷锟斤拷锟斤拷锟斤拷锟揭拷锟絠dx_name_age锟斤拷锟斤拷锟斤拷锟斤拷么SQL一锟斤拷要锟斤拷锟斤拷last_name锟街段碉拷锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷恰锟斤拷锟斤拷蟆钡锟斤拷锟剿硷拷锟�

锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷锟紾AP锟斤拷锟斤拷锟劫斤拷锟斤拷锟侥ｏ拷

1锟斤拷锟斤拷锟斤拷

锟斤拷锟斤拷锟角对撅拷锟斤拷锟斤拷锟斤拷锟叫碉拷锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷癫⒎锟斤拷锟斤拷锟斤拷锟酵拷校锟街伙拷锟斤拷锟酵伙拷锟斤拷辖锟斤拷锟叫达拷锟绞憋拷呕锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷锟�

锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷锟铰憋拷锟结构锟斤拷锟斤拷锟捷ｏ拷 

CREATE TABLE employees (
    id INT AUTO_INCREMENT PRIMARY KEY,
    first_name VARCHAR(50),
    last_name VARCHAR(50),
    age INT,
    department VARCHAR(50)
);

锟斤拷锟斤拷锟斤拷锟紸锟斤拷锟斤拷锟斤拷某锟斤拷锟截讹拷员锟斤拷锟斤拷锟斤拷息锟斤拷 

-- 锟斤拷锟斤拷A
START TRANSACTION;
UPDATE employees SET age = 31 WHERE last_name = 'Doe';

锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷校锟斤拷锟斤拷锟斤拷岜伙拷锟斤拷锟絣ast_name = 'Doe'锟斤拷锟斤拷应锟斤拷锟斤拷锟较ｏ拷锟斤拷John锟斤拷Jane锟斤拷锟斤拷锟斤拷锟斤拷锟绞憋拷锟斤拷锟紹锟斤拷锟皆革拷锟斤拷同一锟叫ｏ拷 

-- 锟斤拷锟斤拷B
START TRANSACTION;
UPDATE employees SET age = 29 WHERE last_name = 'Doe';

锟斤拷锟斤拷B锟结被锟斤拷锟斤拷锟斤拷直锟斤拷锟斤拷锟斤拷A锟结交锟斤拷毓锟�,锟斤拷为锟斤拷锟斤拷A锟斤拷锟斤拷锟斤拷B锟接的讹拷锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷也锟叫憋拷锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷确锟斤拷锟斤拷锟斤拷锟捷碉拷一锟斤拷锟皆★拷

GAP锟斤拷

锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷某一锟叫ｏ拷锟斤拷GAP锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷前锟斤拷募锟较讹拷锟阶拷锟街伙拷锟斤拷锟角帮拷锟侥硷拷隙锟斤拷锟斤拷锟斤拷芑锟斤拷锟斤拷潜锟斤拷锟斤拷锟斤拷一锟斤拷前锟斤拷锟叫硷拷隙锟斤拷锟斤拷锟斤拷锟揭伙拷泻锟斤拷锟侥硷拷隙锟斤拷锟斤拷锟斤拷

锟斤拷然锟斤拷锟斤拷锟剿ｏ拷只锟斤拷锟斤拷锟斤拷锟角斤拷锟斤拷锟斤拷一锟斤拷锟斤拷锟斤拷PAGE_NEW_SUPREMUM锟侥硷拷录锟斤拷说锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷锟轿狿AGE_NEW_SUPREMUM锟斤拷录锟斤拷InnoDB默锟较的ｏ拷锟斤拷锟教讹拷锟斤拷为锟斤拷锟揭伙拷锟斤拷锟铰硷拷锟斤拷锟斤拷只要锟斤拷住PAGE_NEW_SUPREMUM前锟斤拷募锟较讹拷锟斤拷锟斤拷嗟憋拷锟斤拷锟阶★拷锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷锟揭伙拷泻锟斤拷锟侥硷拷隙锟斤拷

count(*)、count(1)哪个更快？面试必问：通宵整理的十道经典MySQL必问面试题

锟劫斤拷锟斤拷锟斤拷Next-Key Lock锟斤拷

锟劫斤拷锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷GAP锟斤拷锟侥斤拷希锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷锟皆硷拷锟斤拷之锟斤拷目锟较讹拷锟斤拷锟斤拷锟斤拷锟饺凤拷锟斤拷锟揭伙拷锟斤拷锟轿э拷诘牟锟窖拷校锟斤拷锟斤拷锟斤拷锟街癸拷嘶枚锟斤拷锟斤拷锟斤拷鼙锟斤拷锟斤拷锟斤拷锟斤拷锟�

锟斤拷锟斤拷使锟斤拷之前锟斤拷锟斤拷锟接ｏ拷锟斤拷锟斤拷锟斤拷锟斤拷执锟斤拷锟斤拷锟斤拷锟铰诧拷锟斤拷: 

-- 锟斤拷锟斤拷D
START TRANSACTION;
SELECT * FROM employees WHERE last_name >= 'D' FOR UPDATE;

锟节此诧拷询锟叫ｏ拷MySQL锟斤拷锟斤拷锟斤拷锟絣ast_name为'D'锟斤拷锟斤拷锟斤拷锟叫硷拷锟斤拷锟斤拷锟斤拷锟斤拷同时锟斤拷'D'之前锟侥匡拷隙锟斤拷锟斤拷GAP锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷锟皆凤拷止锟节该凤拷围锟节诧拷锟斤拷锟铰碉拷锟叫★拷

锟侥★拷锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷锟組VCC锟侥ｏ拷

OCR识锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷一些锟斤拷锟斤拷突锟斤拷遥锟斤拷锟斤拷锟斤拷锟斤拷莶锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷锟饺皇讹拷锟斤拷呛锟斤拷锟斤拷耄拷锟斤拷医锟斤拷锟斤拷锟斤拷业锟斤拷锟斤拷锟斤拷锟斤拷锟街讹拷锟斤拷锟斤拷锟斤拷锟酵计拷锟斤拷艽锟斤拷锟斤拷锟斤拷锟捷★拷

锟斤拷锟斤拷锟斤拷锟組VCC锟斤拷

锟斤拷谓MVCC锟斤拷锟角讹拷姹撅拷锟斤拷锟斤拷锟斤拷疲锟組ySQL为锟斤拷实锟街匡拷锟截革拷锟斤拷锟斤拷锟斤拷锟斤拷爰讹拷穑锟斤拷锟轿拷瞬锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷锟绞碉拷挚锟斤拷馗锟斤拷锟斤拷锟斤拷锟斤拷圆锟斤拷锟組VCC锟斤拷锟斤拷锟斤拷实锟街★拷

锟斤拷要锟斤拷锟斤拷

  1. ReadView锟斤拷锟斤拷取锟斤拷图锟斤拷 锟斤拷

    • 锟斤拷一锟斤拷锟斤拷锟斤拷始时锟斤拷MVCC锟结创锟斤拷一锟斤拷 ReadView 锟斤拷锟斤拷锟斤拷图锟斤拷录锟剿碉拷前锟缴硷拷锟斤拷锟斤拷锟叫版本锟斤拷锟斤拷锟斤拷锟斤拷些锟斤拷锟斤拷锟角伙拷跃锟侥ｏ拷锟斤拷些锟斤拷锟斤拷锟窖撅拷锟结交锟斤拷

  2. 锟斤拷锟斤拷ID 锟斤拷

    • 每锟斤拷锟斤拷锟斤拷锟斤拷一锟斤拷唯一锟斤拷锟斤拷锟斤拷ID锟斤拷锟斤拷 ReadView 锟叫ｏ拷锟斤拷锟铰硷拷锟角帮拷锟斤拷锟斤拷ID锟斤拷锟斤拷小锟斤拷锟斤拷ID锟斤拷锟斤拷锟斤拷锟斤拷锟絀D锟斤拷

  3. 锟缴硷拷锟皆癸拷锟斤拷 锟斤拷

    • 锟斤拷锟揭伙拷锟斤拷锟斤拷锟斤拷ID 锟斤拷
      1. 锟斤拷锟斤拷 ReadView 锟叫碉拷锟斤拷锟斤拷锟斤拷锟絀D锟斤拷锟斤拷锟斤拷锟斤拷锟侥革拷锟侥对碉拷前锟斤拷锟今不可硷拷锟斤拷
      2. 锟斤拷锟斤拷 ReadView 锟叫的伙拷跃锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷锟侥革拷锟侥诧拷锟缴硷拷锟斤拷锟斤拷为锟斤拷锟斤拷锟斤拷锟节斤拷锟斤拷锟叫★拷
      3. 小锟斤拷 ReadView 锟叫碉拷锟斤拷小锟斤拷锟斤拷ID锟斤拷锟斤拷锟斤拷锟斤拷母锟斤拷锟斤拷强杉锟斤拷模锟斤拷锟轿拷锟斤拷丫锟斤拷峤伙拷锟�

MVCC锟侥癸拷锟斤拷锟斤拷锟斤拷

  • 锟斤拷锟斤拷 ReadView 锟斤拷

    • 锟斤拷锟斤拷锟斤拷始时锟斤拷MVCC锟斤拷锟斤拷锟斤拷一锟斤拷 ReadView 锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷锟角帮拷锟斤拷锟斤拷ID锟斤拷锟斤拷跃锟斤拷锟斤拷锟絀D锟皆硷拷锟斤拷锟斤拷锟斤拷小锟斤拷锟斤拷ID锟斤拷

  • 锟斤拷取锟斤拷锟斤拷 锟斤拷

    • 锟斤拷锟斤拷锟斤拷锟饺★拷锟斤拷锟绞憋拷锟斤拷锟斤拷锟斤拷慰锟� ReadView 锟叫碉拷锟斤拷息锟斤拷锟斤拷确锟斤拷锟斤拷些锟芥本锟斤拷锟斤拷锟斤拷锟角可硷拷锟侥★拷

  • 锟斤拷锟斤拷锟斤拷锟斤拷 锟斤拷

    • 锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷锟绞憋拷锟組VCC锟斤拷锟斤拷直锟接革拷锟斤拷原锟叫碉拷锟斤拷锟捷ｏ拷锟斤拷锟角达拷锟斤拷一锟斤拷锟铰的版本锟斤拷只锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷锟矫革拷锟斤拷锟捷碉拷锟斤拷锟斤拷锟斤拷珊螅呕锟斤拷锟斤拷锟斤拷砂姹撅拷锟�

count(*)、count(1)哪个更快？面试必问：通宵整理的十道经典MySQL必问面试题

锟杰斤拷

MVCC锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷诓锟斤拷锟斤拷疟舜说锟斤拷锟斤拷锟斤拷同时锟斤拷锟叫诧拷锟斤拷锟斤拷锟解极锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷锟捷匡拷牟锟斤拷锟斤拷锟斤拷堋锟酵拷锟轿拷锟斤拷锟斤拷莸亩锟斤拷锟芥本锟斤拷MVCC锟斤拷证锟斤拷锟斤拷锟捷碉拷一锟斤拷锟皆和革拷锟斤拷锟皆ｏ拷同时锟斤拷锟斤拷锟斤拷锟斤拷锟侥撅拷锟斤拷锟斤拷

锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷一锟捷帮拷锟斤拷锟斤拷锟叫达拷锟剿拷锟绞记ｏ拷一锟斤拷锟接达拷通锟斤拷锟揭碉拷锟轿讹拷锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷原锟斤拷没锟斤拷么锟窖★拷锟斤拷锟角达拷锟斤拷写锟侥ｏ拷 7701页锟斤拷BAT锟斤拷锟斤拷写锟斤拷刷锟斤拷始牵锟斤拷锟斤拷锟給ffer锟矫碉拷锟斤拷锟斤拷

锟藉、锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷锟絆nline DDL锟侥ｏ拷

Online DDL 锟斤拷指锟节诧拷影锟斤拷锟斤拷锟捷匡拷锟斤拷锟斤拷锟斤拷锟斤拷拢锟斤拷薷锟斤拷锟斤拷菘锟斤拷锟侥结构锟斤拷通锟阶碉拷说锟斤拷锟斤拷锟斤拷锟斤拷锟角匡拷锟斤拷锟斤拷锟斤拷锟捷匡拷锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷同时锟斤拷锟皆憋拷锟斤拷锟叫碉拷锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷锟叫★拷锟睫革拷锟街讹拷锟斤拷锟酵★拷锟斤拷锟斤拷锟斤拷锟斤拷锟饺ｏ拷锟斤拷锟斤拷锟斤拷要停锟斤拷维锟斤拷锟斤拷

锟斤拷锟紻DL锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷一锟斤拷锟街段ｏ拷锟斤拷锟斤拷锟斤拷锟铰硷拷锟斤拷锟斤拷锟斤拷

1. 锟斤拷锟斤拷锟斤拷锟斤拷

MySQL 锟斤拷锟饺伙拷锟� DDL 锟斤拷锟斤拷锟叫斤拷锟斤拷锟斤拷确锟斤拷锟斤法锟斤拷确锟斤拷锟斤拷锟界： 

ALTER TABLE users ADD COLUMN age INT;

MySQL 锟斤拷锟斤拷锟� users 锟角凤拷锟斤拷冢锟斤拷锟斤拷锟斤拷锟� age 锟街讹拷锟角凤拷锟斤拷锟斤拷锟叫碉拷锟街段筹拷突锟斤拷锟斤拷锟斤拷锟街讹拷锟斤拷锟截革拷锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷锟角凤拷支锟街等★拷

2. 锟斤拷锟斤拷元锟斤拷锟斤拷锟斤拷锟斤拷metadata lock锟斤拷

锟节斤拷锟斤拷锟轿何憋拷锟结构锟斤拷锟街帮拷锟組ySQL 锟斤拷员锟斤拷锟揭伙拷锟皆拷锟斤拷锟斤拷锟斤拷锟組DL锟斤拷锟斤拷元锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷锟角凤拷止锟节憋拷锟斤拷峁癸拷锟酵憋拷锟斤拷锟斤拷锟� DDL 锟斤拷锟斤拷锟皆憋拷锟斤拷锟斤拷锟睫改ｏ拷锟斤拷证锟斤拷锟结构一锟斤拷锟皆★拷

锟斤拷锟� 锟斤拷元锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷锟节筹拷锟斤拷锟斤拷装锟斤拷锟斤拷时锟斤拷锟斤拷止锟斤拷锟斤拷锟斤拷也锟斤拷同时锟斤拷锟侥伙拷锟斤拷位锟矫ｏ拷锟斤拷锟斤拷锟斤拷摇锟�

3. 锟斤拷锟斤拷锟斤拷时锟斤拷

锟斤拷锟斤拷锟斤拷执锟斤拷 ALTER TABLE 锟斤拷锟绞憋拷锟組ySQL 锟结创锟斤拷一锟斤拷 锟斤拷时锟斤拷 锟斤拷锟斤拷锟斤拷锟绞憋拷锟斤拷锟斤拷锟斤拷斜锟斤拷锟揭伙拷锟斤拷锟斤拷锟狡凤拷锟斤拷锟斤拷一岚达拷锟斤拷锟斤拷堑锟揭拷锟斤拷锟斤拷锟斤拷碌锟斤拷侄巍锟�

锟斤拷时锟斤拷锟侥诧拷锟借：

  1. 锟斤拷锟斤拷原锟斤拷锟结构 锟斤拷MySQL 锟结复锟斤拷原锟斤拷锟侥结构锟斤拷一锟斤拷锟斤拷时锟斤拷锟叫ｏ拷锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷锟街段ｏ拷锟斤拷锟斤拷 age 锟斤拷
  2. 锟斤拷锟斤拷锟斤拷锟斤拷 锟斤拷MySQL 锟斤拷原锟斤拷锟叫碉拷锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷锟叫革拷锟狡碉拷锟斤拷时锟斤拷锟叫ｏ拷同时为每一锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷侄蔚锟侥拷锟街碉拷锟斤拷锟斤拷锟叫ｏ拷锟斤拷

锟斤拷锟� 锟斤拷锟斤拷锟斤拷锟斤拷谢锟斤拷锟斤拷锟斤拷锟绞憋拷锟斤拷锟斤拷诓挚锟斤拷锟筋建一锟斤拷锟铰的伙拷锟斤拷模锟酵ｏ拷锟斤拷锟斤拷锟斤拷同锟斤拷锟斤拷品锟斤拷同时锟斤拷锟斤拷锟铰碉拷锟斤拷品锟斤拷锟斤拷锟斤拷锟�

4. 锟叫伙拷锟斤拷

锟斤拷 MySQL 锟斤拷锟斤拷锟斤拷锟斤拷莞锟斤拷坪锟斤拷锟斤拷峤拷锟斤拷锟斤拷锟绞憋拷锟斤拷锟斤拷锟斤拷婊伙拷锟斤拷锟绞憋拷锟斤拷锟绞憋拷锟斤拷锟斤拷锟斤拷锟斤拷式锟侥憋拷锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷锟街段★拷

  • 锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷校锟斤拷锟斤拷械锟� DML锟斤拷锟斤拷删锟侥查）锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷时锟斤拷锟斤拷锟斤拷直锟斤拷锟芥换锟斤拷伞锟斤拷锟斤拷时锟斤拷芏锟斤拷荩锟斤拷苑锟斤拷锟斤拷影锟斤拷浅锟叫★拷锟�

锟斤拷锟� 锟斤拷锟斤拷锟斤拷挚锟斤拷锟斤拷锟铰伙拷锟杰搭建锟矫后，帮拷锟斤拷锟斤拷锟斤拷锟斤拷校锟酵憋拷婊伙拷锟斤拷苫锟斤拷堋锟斤拷丝图锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷锟教★拷

5. 删锟斤拷锟缴憋拷

原始锟侥憋拷锟斤拷锟铰憋拷锟芥换锟斤拷MySQL 锟斤拷锟皆讹拷删锟斤拷锟缴憋拷锟斤拷元锟斤拷锟捷ｏ拷锟酵放空间。锟斤拷一锟斤拷锟节猴拷台锟斤拷桑锟斤拷锟接帮拷锟斤拷锟斤拷菘锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷

6. 锟酵凤拷锟斤拷

锟斤拷锟斤拷锟叫诧拷锟斤拷锟斤拷珊锟組ySQL 锟斤拷锟酵凤拷元锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷 DDL 锟斤拷 DML 锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷锟叫★拷

锟杰结：

  1. 锟斤拷锟斤拷锟斤拷洳拷锟斤拷戏锟斤拷浴锟�
  2. 锟皆憋拷锟斤拷元锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷止锟斤拷突锟侥结构锟斤拷锟斤拷锟斤拷锟�
  3. 锟斤拷锟斤拷锟斤拷时锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷锟捷从旧憋拷锟斤拷锟狡碉拷锟斤拷时锟斤拷锟斤拷
  4. 锟芥换锟缴憋拷锟斤拷删锟斤拷锟缴憋拷锟斤拷元锟斤拷锟捷★拷
  5. 锟酵凤拷锟斤拷锟斤拷

注锟斤拷 锟斤拷锟斤拷锟街凤拷锟斤拷锟斤拷 锟斤拷使锟斤拷 Online DDL 锟斤拷锟斤拷锟斤拷拢锟斤拷锟斤拷艿锟斤拷麓锟斤拷锟斤拷锟斤拷锟斤拷莞锟斤拷撇锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷锟接帮拷欤拷锟斤拷锟斤拷潜锟斤拷锟斤拷锟斤拷锟斤拷洗锟绞憋拷锟�

锟斤拷锟斤拷锟斤拷知锟斤拷锟斤拷些锟斤拷锟斤拷禄岬硷拷锟斤拷锟斤拷锟绞�

锟斤拷 MySQL 锟叫ｏ拷锟斤拷锟斤拷锟斤拷锟斤拷卟锟窖э拷实墓丶锟斤拷锟斤拷撸锟斤拷锟斤拷锟绞憋拷锟斤拷芑锟斤拷锟斤拷锟斤拷锟斤拷锟� 失效 锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷虏锟窖拷锟斤拷艽锟斤拷锟铰斤拷锟斤拷锟斤拷锟斤拷锟斤拷锟酵拷锟斤拷锟斤拷询锟斤拷锟斤拷写锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷锟酵碉拷选锟斤拷锟皆硷拷锟斤拷锟捷匡拷锟斤拷呕锟斤拷锟斤拷锟斤拷泄亍锟斤拷锟斤拷锟斤拷羌锟斤拷殖锟斤拷锟斤拷岬硷拷锟斤拷锟斤拷锟绞э拷某锟斤拷锟斤拷锟�

1. 使锟斤拷 LIKE 时通锟斤拷锟斤拷锟斤拷锟角帮拷锟�

锟斤拷锟斤拷锟� LIKE 锟斤拷锟斤拷校锟酵拷锟斤拷 % 锟斤拷锟斤拷锟街凤拷锟斤拷锟侥匡拷头锟斤拷锟结导锟斤拷锟斤拷锟斤拷失效锟斤拷锟斤拷为锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷拢锟組ySQL 锟睫凤拷通锟斤拷锟斤拷锟斤拷锟斤拷锟劫讹拷位锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷锟侥硷拷录锟斤拷锟斤拷锟斤拷要扫锟斤拷锟斤拷锟叫硷拷录锟斤拷

示锟斤拷锟斤拷 

SELECT * FROM users WHERE name LIKE '%abc';  -- 锟斤拷锟斤拷失效

锟斤拷锟斤拷写锟斤拷锟斤拷使锟斤拷 MySQL 扫锟斤拷全锟斤拷锟斤拷锟斤拷锟斤拷锟叫达拷锟� LIKE 'abc%' 锟斤拷锟斤拷锟斤拷锟斤拷然锟斤拷效锟斤拷

锟斤拷锟� 锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷锟揭伙拷锟斤拷锟侥硷拷锟叫诧拷锟斤拷锟斤拷锟斤拷锟斤拷"abc"锟斤拷头锟斤拷锟侥硷拷锟斤拷锟斤拷锟斤拷锟斤拷锟街憋拷锟斤拷业锟斤拷锟接︼拷牟锟斤拷郑锟斤拷锟斤拷锟斤拷锟角诧拷锟斤拷锟斤拷锟街帮拷锟斤拷"abc"锟斤拷锟侥硷拷锟斤拷锟斤拷偷每锟矫匡拷锟斤拷募锟斤拷锟斤拷锟�

2. 锟斤拷锟斤拷锟斤拷锟酵诧拷一锟斤拷

锟斤拷锟斤拷询锟斤拷锟斤拷锟叫碉拷锟街讹拷锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷锟街段碉拷锟斤拷锟酵诧拷一锟斤拷时锟斤拷MySQL 锟斤拷锟杰诧拷锟斤拷使锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷锟饺讹拷锟斤拷锟捷斤拷锟斤拷锟斤拷锟斤拷转锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷转锟斤拷锟结导锟斤拷锟睫凤拷锟斤拷效锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷

示锟斤拷锟斤拷

锟斤拷锟斤拷 id 锟斤拷一锟斤拷锟斤拷锟斤拷锟街段ｏ拷 

SELECT * FROM users WHERE id = '123';  -- 锟斤拷锟斤拷失效

锟斤拷锟斤拷锟� '123' 锟斤拷锟街凤拷锟斤拷锟斤拷MySQL 锟斤拷锟斤拷锟斤拷锟绞阶拷锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷失效锟斤拷

3. 锟斤拷锟斤拷锟斤拷锟街讹拷使锟矫猴拷锟斤拷

锟斤拷锟斤拷诓锟窖拷卸锟斤拷锟斤拷锟斤拷侄锟绞癸拷锟斤拷撕锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷MySQL 锟斤拷锟斤拷通锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷询锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷失效锟斤拷

示锟斤拷锟斤拷 

SELECT * FROM users WHERE YEAR(created_at) = 2023;  -- 锟斤拷锟斤拷失效

锟斤拷锟斤拷 YEAR(created_at) 锟角讹拷 created_at 锟街段斤拷锟斤拷锟剿猴拷锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷锟� MySQL 锟睫凤拷直锟斤拷使锟斤拷锟斤拷锟斤拷锟斤拷锟叫诧拷锟揭★拷

锟斤拷锟� 锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷氚茨筹拷止锟斤拷锟斤拷锟斤拷械锟斤拷斜锟斤拷胁锟斤拷锟斤拷锟斤拷荩锟斤拷锟斤拷锟斤拷锟揭拷雀谋锟斤拷锟斤拷锟斤拷锟绞斤拷锟斤拷锟斤拷业锟斤拷锟斤拷锟斤拷锟叫э拷锟斤拷陆锟斤拷锟�

4. 使锟斤拷 OR 锟截硷拷锟斤拷

锟斤拷 OR 锟斤拷锟斤拷锟叫碉拷某一锟斤拷没锟斤拷锟斤拷锟斤拷时锟斤拷锟斤拷锟斤拷锟斤拷询锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷失效锟斤拷

示锟斤拷锟斤拷 

SELECT * FROM users WHERE id = 1 OR name = 'Alice';  -- 锟斤拷锟斤拷失效

锟斤拷锟斤拷 id 锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷 name 锟斤拷没锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷么锟斤拷锟斤拷锟窖拷筒锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷锟組ySQL 锟斤拷要锟斤拷锟斤拷全锟斤拷扫锟借。

锟脚伙拷锟斤拷式 锟斤拷为 name 锟街段碉拷锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷锟竭革拷写锟斤拷询锟竭硷拷锟斤拷锟斤拷锟斤拷 OR 锟斤拷锟铰碉拷锟斤拷锟斤拷失效锟斤拷

5. 锟斤拷锟斤拷锟斤拷锟斤拷 ( != 锟斤拷 <> )

使锟矫诧拷锟斤拷锟节诧拷锟斤拷锟斤拷锟斤拷 != 锟斤拷 <> 锟斤拷时锟斤拷MySQL 锟斤拷锟斤拷锟斤拷效使锟斤拷锟斤拷锟斤拷锟斤拷锟结导锟斤拷全锟斤拷扫锟借。

示锟斤拷锟斤拷 

SELECT * FROM users WHERE age != 30;  -- 锟斤拷锟斤拷失效

锟斤拷锟斤拷锟窖拷锟斤拷岬硷拷锟斤拷锟斤拷锟绞э拷锟斤拷锟轿� MySQL 锟睫凤拷通锟斤拷锟斤拷锟斤拷锟斤拷位锟斤拷锟斤拷锟斤拷锟斤拷 != 锟侥硷拷录锟斤拷

6. 锟斤拷围锟斤拷询 ( > , < , BETWEEN ) 锟斤拷锟斤拷锟�

锟节革拷锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷锟叫ｏ拷锟斤拷锟斤拷一锟斤拷锟街讹拷使锟斤拷锟剿凤拷围锟斤拷询时锟斤拷锟斤拷锟斤拷锟斤拷锟街段碉拷锟斤拷锟斤拷锟斤拷锟杰伙拷失效锟斤拷

示锟斤拷锟斤拷

锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷一锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷 (age, name) 锟斤拷锟斤拷锟铰诧拷询锟斤拷 

SELECT * FROM users WHERE age > 30 AND name = 'Alice';  -- `name` 锟斤拷锟斤拷失效

锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷拢锟斤拷锟斤拷锟� age 使锟斤拷锟剿凤拷围锟斤拷询锟斤拷 name 锟叫碉拷锟斤拷锟斤拷锟斤拷失效锟斤拷MySQL 锟睫凤拷通锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷直锟接诧拷锟揭★拷

7. 锟斤拷锟斤拷锟斤拷前锟斤拷锟斤拷 IS NULL 锟斤拷 IS NOT NULL

锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷使锟斤拷 IS NULL 锟斤拷 IS NOT NULL 锟斤拷锟斤拷时 MySQL 锟斤拷锟杰诧拷锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷锟节达拷锟斤拷锟斤拷锟捷达拷锟斤拷 NULL 值锟斤拷锟斤拷锟斤拷拢锟組ySQL 锟斤拷锟斤拷为锟斤拷锟斤拷锟斤拷使锟矫诧拷锟斤拷锟姐。

示锟斤拷锟斤拷 

SELECT * FROM users WHERE name IS NOT NULL;  -- 锟斤拷锟斤拷锟斤拷锟斤拷失效

8. 锟斤拷询锟斤拷锟斤拷锟斤拷使锟斤拷 NOT IN 锟斤拷 NOT EXISTS

使锟斤拷 NOT IN 锟斤拷 NOT EXISTS 也锟斤拷锟杰会导锟斤拷 MySQL 锟斤拷使锟斤拷锟斤拷锟斤拷锟斤拷锟接讹拷锟斤拷锟斤拷全锟斤拷扫锟借。

示锟斤拷锟斤拷 

SELECT * FROM users WHERE id NOT IN (1, 2, 3);  -- 锟斤拷锟斤拷失效

9. 锟斤拷锟叫碉拷锟斤拷锟斤拷锟斤拷锟斤拷小

锟斤拷锟斤拷锟叫碉拷锟斤拷锟斤拷锟斤拷锟斤拷小锟斤拷MySQL 锟斤拷锟斤拷锟斤拷为全锟斤拷扫锟斤拷锟绞癸拷锟斤拷锟斤拷锟斤拷锟斤拷锟叫э拷锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷锟铰ｏ拷MySQL 锟斤拷选锟斤拷直锟斤拷扫锟斤拷锟斤拷锟斤拷锟酵拷锟斤拷锟斤拷锟斤拷锟斤拷摇锟�

锟斤拷锟� 锟斤拷锟斤拷锟斤拷锟街伙拷屑锟斤拷锟斤拷募锟斤拷锟揭拷锟斤拷遥锟斤拷锟绞憋拷锟斤拷却锟斤拷锟侥柯硷拷锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷悖憋拷锟缴拷锟饺拷锟斤拷募锟斤拷锟斤拷臁�

10. MySQL 锟脚伙拷锟斤拷选锟斤拷使锟斤拷锟斤拷锟斤拷

锟斤拷时锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷锟矫ｏ拷MySQL 锟侥诧拷询锟脚伙拷锟斤拷锟斤拷锟杰伙拷锟斤拷荼锟斤拷锟酵筹拷锟斤拷锟较拷统杀锟斤拷锟斤拷悖拷锟轿拷锟缴拷锟斤拷使锟斤拷锟斤拷锟斤拷锟斤拷锟届，锟接讹拷锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷

锟杰结：

锟斤拷锟斤拷失效通锟斤拷锟斤拷锟窖拷锟斤拷锟叫达拷锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷汀锟斤拷锟斤拷锟绞癸拷谩锟斤拷约锟� MySQL 锟斤拷询锟脚伙拷锟斤拷锟侥撅拷锟斤拷锟叫关★拷为锟剿憋拷锟斤拷锟斤拷锟斤拷失效锟斤拷锟斤拷要锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷纾�

  • 锟斤拷 LIKE 锟斤拷询锟叫憋拷锟斤拷通锟斤拷锟� % 锟斤拷头
  • 锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷一锟斤拷
  • 锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷使锟矫猴拷锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷锟�
  • 锟斤拷锟斤拷锟芥划锟斤拷锟斤拷锟斤拷锟斤拷锟叫的诧拷询顺锟斤拷

锟竭★拷锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷锟組ySQL锟斤拷filesort锟侥ｏ拷

通锟阶碉拷锟斤拷锟解，锟斤拷锟皆帮拷 filesort 锟斤拷锟斤拷为锟斤拷锟捷匡拷摹锟斤拷锟斤拷锟斤拷锟斤拷锟绞斤拷锟斤拷锟斤拷锟斤拷锟窖拷械锟� ORDER BY 锟斤拷锟斤拷薹锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷械锟斤拷锟斤拷锟剿筹拷锟绞憋拷锟組ySQL 锟酵伙拷锟斤拷锟斤拷 filesort 锟斤拷锟街讹拷锟斤拷锟斤拷锟斤拷锟斤拷

什么时锟斤拷岽ワ拷锟� filesort 锟斤拷

MySQL 锟斤拷锟斤拷某些锟斤拷锟斤拷锟绞癸拷锟� filesort 锟斤拷锟斤拷锟界：

  1. 没锟叫猴拷锟绞碉拷锟斤拷锟斤拷 锟斤拷
    锟斤拷锟斤拷询锟叫碉拷 ORDER BY 锟街讹拷没锟斤拷锟斤拷锟斤拷锟斤拷MySQL 锟睫凤拷锟斤拷锟斤拷锟斤拷锟斤拷顺锟斤拷只锟杰斤拷锟斤拷 filesort 锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷

    示锟斤拷锟斤拷 

    SELECT * FROM users ORDER BY age;

    锟斤拷锟斤拷 users 锟斤拷锟斤拷没锟斤拷 age 锟街段碉拷锟斤拷锟斤拷锟斤拷锟斤拷时锟斤拷 MySQL 锟斤拷锟斤拷锟� filesort 锟斤拷

  2. 锟斤拷锟斤拷锟斤拷锟津，碉拷锟斤拷锟斤拷锟斤拷匹锟斤拷 锟斤拷
    锟斤拷锟斤拷锟角对讹拷锟斤拷薪锟斤拷锟斤拷锟斤拷颍锟斤拷锟叫╋拷锟矫伙拷斜锟斤拷锟斤拷锟斤拷锟斤拷腔锟斤拷锟斤拷锟剿筹拷锟斤拷锟斤拷锟斤拷锟揭拷蟛环锟绞憋拷锟� filesort 也锟结被锟斤拷锟斤拷锟斤拷

    示锟斤拷锟斤拷 

    SELECT * FROM users ORDER BY age, name;

    锟斤拷锟斤拷锟斤拷锟街伙拷锟� age 锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷没锟斤拷 (age, name) 锟侥革拷锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷么 MySQL 锟斤拷使锟斤拷 filesort 锟斤拷

  3. 锟斤拷喜锟窖拷锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷锟绞� 锟斤拷
    锟斤拷锟斤拷询锟叫讹拷锟街段斤拷锟叫硷拷锟斤拷锟斤拷锟斤拷锟斤拷锟绞憋拷锟斤拷锟斤拷锟斤拷锟叫╋拷侄锟斤拷锟斤拷锟斤拷锟斤拷锟揭诧拷薹锟街憋拷锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷锟�

    示锟斤拷锟斤拷 

    SELECT * FROM users ORDER BY LENGTH(name);

    LENGTH(name) 锟斤拷一锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷MySQL 锟斤拷要锟街讹拷锟斤拷锟斤拷锟斤拷嘶锟绞癸拷锟� filesort 锟斤拷

filesort 锟侥癸拷锟斤拷锟斤拷式锟斤拷

filesort 实锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷实锟街凤拷式锟斤拷取锟斤拷锟斤拷 MySQL 锟侥版本锟斤拷锟斤拷锟矫ｏ拷

  1. 锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷 锟斤拷Older Versions锟斤拷锟斤拷MySQL 锟斤拷巡锟窖拷锟斤拷锟斤拷锟斤拷锟斤拷锟叫讹拷锟斤拷锟斤拷一锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷然锟斤拷锟斤拷锟� ORDER BY 锟街讹拷锟斤拷锟叫比较诧拷锟斤拷锟斤拷锟斤拷锟街凤拷式效锟斤拷锟斤拷越系停锟斤拷锟轿拷锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷艽锟�

  2. 锟斤拷锟斤拷扫锟斤拷锟斤拷锟斤拷 锟斤拷Optimized Versions锟斤拷锟斤拷锟节斤拷锟铰碉拷 MySQL 锟芥本锟叫ｏ拷 filesort 锟斤拷锟斤拷锟斤拷呕锟斤拷锟街伙拷锟斤拷诘锟揭伙拷锟缴拷锟绞憋拷占锟斤拷锟揭拷锟斤拷锟斤拷锟街段猴拷 ROW_ID 锟斤拷然锟斤拷通锟斤拷锟斤拷锟斤拷锟斤拷 ROW_ID 锟斤拷去锟斤拷取锟斤拷锟斤拷锟斤拷锟捷★拷锟斤拷锟街凤拷式锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷锟杰★拷

filesort 锟斤拷锟杰碉拷影锟届：

filesort 锟斤拷锟斤拷锟斤拷说每锟轿讹拷锟斤拷锟芥及锟斤拷锟教诧拷锟斤拷锟斤拷锟斤拷锟叫匡拷锟斤拷锟斤拷锟节达拷锟斤拷锟斤拷桑锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷洗锟绞憋拷锟斤拷诖娌伙拷锟斤拷锟斤拷锟斤拷锟斤拷锟津，就匡拷锟杰斤拷锟斤拷锟斤拷写锟斤拷锟斤拷探锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷锟接帮拷锟斤拷锟斤拷堋锟�

MySQL 锟斤拷锟斤拷锟斤拷锟斤拷要锟侥诧拷锟斤拷锟斤拷锟斤拷 filesort 锟斤拷为锟斤拷

  1. sort_buffer_size 锟斤拷锟斤拷锟斤拷 MySQL 锟斤拷锟斤拷锟斤拷锟节达拷锟斤拷锟斤拷锟斤拷幕锟斤拷锟斤拷锟斤拷锟叫★拷锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷芊沤锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷突锟斤拷锟斤拷诖锟斤拷锟斤拷锟缴ｏ拷锟斤拷锟斤拷MySQL 锟结将锟斤拷锟斤拷锟斤拷锟斤拷写锟斤拷锟斤拷蹋锟斤拷佣锟接帮拷锟斤拷锟斤拷堋锟�

  2. max_length_for_sort_data 锟斤拷锟斤拷锟斤拷 MySQL 锟斤拷锟斤拷锟斤拷锟斤拷 filesort 锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷锟缴拷锟斤拷锟斤拷颍锟斤拷锟斤拷诮隙痰锟斤拷锟斤拷荩锟組ySQL 锟斤拷锟斤拷锟斤拷选锟斤拷效锟绞较高碉拷锟斤拷锟斤拷扫锟斤拷锟斤拷锟斤拷式锟斤拷

锟斤拷伪锟斤拷锟斤拷锟脚伙拷 filesort 锟斤拷

  1. 使锟矫猴拷锟绞碉拷锟斤拷锟斤拷 锟斤拷
    锟斤拷直锟接的办法锟斤拷锟斤拷为锟斤拷询锟叫碉拷锟斤拷锟斤拷锟街段达拷锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷 ORDER BY 锟接撅拷时锟斤拷确锟斤拷锟斤拷锟斤拷锟剿革拷锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷效锟斤拷锟斤拷 filesort 锟斤拷

    示锟斤拷锟斤拷 

    CREATE INDEX idx_age_name ON users (age, name);

  2. 锟斤拷锟斤拷 sort_buffer_size 锟斤拷
    锟斤拷锟斤拷薹锟斤拷锟斤拷锟� filesort 锟斤拷锟斤拷锟斤拷通锟斤拷锟斤拷锟斤拷 sort_buffer_size 锟侥达拷小锟斤拷确锟斤拷锟斤拷锟斤拷锟斤拷锟捷匡拷锟斤拷锟斤拷锟节达拷锟斤拷锟斤拷锟津，硷拷锟劫达拷锟斤拷 I/O锟斤拷

  3. 锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷锟� 锟斤拷
    使锟斤拷 LIMIT 锟斤拷锟斤拷锟狡诧拷询锟斤拷锟斤拷锟斤拷拇锟叫★拷锟斤拷锟斤拷约锟斤拷锟斤拷锟揭拷锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷锟接讹拷锟斤拷小 filesort 锟侥匡拷锟斤拷锟斤拷

    示锟斤拷锟斤拷 

    SELECT * FROM users ORDER BY age LIMIT 100;

  4. 锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷侄锟绞癸拷煤锟斤拷锟� 锟斤拷
    锟斤拷 ORDER BY 锟叫ｏ拷锟斤拷锟斤拷锟斤拷要锟斤拷锟斤拷锟斤拷锟街段斤拷锟叫猴拷锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷式锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷 MySQL 使锟斤拷锟斤拷锟斤拷锟侥匡拷锟斤拷锟皆★拷

锟杰结：

filesort 锟斤拷 MySQL 锟叫碉拷一锟斤拷锟斤拷锟斤拷锟斤拷疲锟斤拷锟斤拷锟窖拷锟斤拷锟睫凤拷通锟斤拷锟斤拷锟斤拷顺锟斤拷锟斤拷锟斤拷时锟斤拷MySQL 锟酵伙拷锟斤拷锟斤拷 filesort 锟斤拷锟斤拷锟街讹拷锟斤拷锟斤拷锟斤拷然锟斤拷锟斤拷锟斤拷锟叫★拷file锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷未锟斤拷一锟斤拷锟芥及锟斤拷锟教诧拷锟斤拷锟斤拷锟节达拷锟叫碉拷锟斤拷锟斤拷也锟角筹拷锟斤拷锟侥★拷 filesort 锟斤拷 MySQL 锟侥憋拷锟斤拷锟斤拷锟斤拷式锟斤拷锟斤拷锟斤拷锟斤拷时锟斤拷锟缴憋拷锟解，锟斤拷锟斤拷锟角匡拷锟斤拷通锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷小锟饺凤拷式锟斤拷锟脚伙拷锟斤拷锟斤拷锟斤拷锟杰★拷

锟剿★拷锟斤拷知锟斤拷锟斤拷些锟斤拷锟斤拷禄锟斤拷锟斤拷锟斤拷锟�

一锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷锟�

1. DDL 锟斤拷锟斤拷锟斤拷锟斤拷锟捷讹拷锟斤拷锟斤拷锟皆ｏ拷

锟斤拷执锟斤拷一些锟结构锟皆憋拷锟斤拷牟锟斤拷锟斤拷锟斤拷锟斤拷锟� ALTER TABLE 锟斤拷 CREATE TABLE 锟斤拷 DROP TABLE 锟斤拷时锟斤拷MySQL 锟斤拷锟斤拷住锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷止锟斤拷锟斤拷锟竭程对该憋拷锟斤拷锟叫诧拷锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷 元锟斤拷锟斤拷锟斤拷 锟斤拷Metadata Lock锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷证锟斤拷锟结构锟侥憋拷锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷锟酵伙拷锟�

示锟斤拷锟斤拷 

ALTER TABLE users ADD COLUMN age INT;

锟剿诧拷锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷 users 锟斤拷锟侥诧拷锟斤拷锟结被锟斤拷锟斤拷锟斤拷直锟斤拷锟斤拷锟斤拷锟缴★拷

2. 全锟斤拷扫锟斤拷锟� UPDATE 锟斤拷 DELETE 锟斤拷锟斤拷

锟斤拷锟斤拷执锟斤拷一锟斤拷 UPDATE 锟斤拷 DELETE 锟斤拷锟斤拷锟斤拷未使锟斤拷锟斤拷锟斤拷时锟斤拷MySQL 锟斤拷锟杰伙拷锟斤拷住锟斤拷锟斤拷锟斤拷锟斤拷锟叫革拷锟铰伙拷删锟斤拷锟斤拷锟斤拷为锟斤拷锟斤拷锟斤拷扫锟斤拷锟斤拷锟斤拷锟叫★拷

示锟斤拷锟斤拷 

UPDATE users SET age = 30 WHERE name LIKE '%John%';  -- 锟斤拷锟矫伙拷锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷锟�

锟斤拷锟斤拷 LIKE '%John%' 锟睫凤拷锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷MySQL 锟斤拷要全锟斤拷扫锟借，锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷

3. 锟斤拷锟斤拷锟叫碉拷写锟斤拷锟斤拷锟斤拷 INSERT 锟斤拷 UPDATE 锟斤拷 DELETE 锟斤拷

锟斤拷 InnoDB 锟芥储锟斤拷锟斤拷锟叫ｏ拷写锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷屑锟斤拷锟� 锟斤拷锟斤拷 锟斤拷Row Lock锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷某些锟斤拷锟斤拷拢锟斤拷锟矫伙拷锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷锟铰ｏ拷锟斤拷MySQL 锟斤拷锟杰伙拷锟剿伙拷为 锟斤拷锟斤拷 锟斤拷Table Lock锟斤拷锟斤拷锟斤拷使锟斤拷锟斤拷锟斤拷锟斤拷锟节筹拷锟斤拷锟斤拷未锟结交锟斤拷毓锟斤拷锟斤拷锟斤拷锟铰ｏ拷也锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷锟今，从讹拷锟斤拷锟斤拷锟斤拷拥锟斤拷锟斤拷锟斤拷锟斤拷锟�

示锟斤拷锟斤拷 

BEGIN;
UPDATE orders SET status = 'shipped' WHERE order_id = 1234;  -- 锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷
COMMIT;

锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷撕艹锟绞憋拷洌拷锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷锟揭诧拷锟揭拷锟斤拷锟� orders 锟斤拷锟叫的硷拷录锟斤拷锟斤拷锟杰会导锟铰等达拷锟斤拷

4. LOCK TABLES 锟斤拷式锟斤拷锟斤拷

MySQL 支锟斤拷使锟斤拷 LOCK TABLES 锟斤拷锟斤拷锟斤拷式锟截对憋拷锟斤拷锟斤拷锟斤拷锟斤拷为 锟斤拷锟斤拷锟斤拷READ LOCK锟斤拷 锟斤拷 写锟斤拷锟斤拷WRITE LOCK锟斤拷 锟斤拷锟斤拷写锟斤拷锟节间，锟斤拷锟斤拷锟竭程诧拷锟杰对该憋拷锟斤拷锟斤拷锟轿何讹拷锟斤拷写锟斤拷锟斤拷锟斤拷锟节讹拷锟斤拷锟节间，锟斤拷锟斤拷锟竭筹拷只锟杰讹拷锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷写锟斤拷锟斤拷

示锟斤拷锟斤拷 

LOCK TABLES users WRITE;  -- 锟斤拷 `users` 锟斤拷锟斤拷写锟斤拷

锟斤拷时锟斤拷锟斤拷锟斤拷锟竭程讹拷 users 锟斤拷锟斤拷锟轿何讹拷写锟斤拷锟斤拷锟斤拷锟结被锟斤拷锟斤拷锟斤拷直锟斤拷锟斤拷锟斤拷锟酵放★拷

5. 锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷

锟斤拷锟斤拷使锟斤拷某些锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷洌拷锟� INSERT INTO ... SELECT ... 锟斤拷 INSERT IGNORE 锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷锟绞憋拷锟斤拷锟斤拷没锟斤拷恰锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷MySQL 锟斤拷锟杰伙拷锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷 MyISAM 锟芥储锟斤拷锟斤拷锟叫★拷

示锟斤拷锟斤拷 

INSERT INTO new_users SELECT * FROM users WHERE created_at > '2023-01-01';  -- 锟斤拷锟斤拷锟斤拷锟斤拷

锟斤拷锟� created_at 没锟斤拷锟斤拷锟斤拷锟斤拷MySQL 锟斤拷要锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷

6. 锟斤拷锟皆硷拷锟斤拷锟斤拷

锟斤拷 InnoDB 锟叫ｏ拷锟斤拷锟斤拷锟斤拷锟缴撅拷锟斤拷婕帮拷锟斤拷锟斤拷约锟斤拷锟斤拷锟斤拷锟斤拷时锟斤拷MySQL 锟斤拷锟杰伙拷锟斤拷住锟斤拷锟斤拷锟斤拷锟接憋拷锟斤拷确锟斤拷锟斤拷锟捷碉拷锟斤拷锟斤拷锟皆★拷锟斤拷然 InnoDB 锟斤拷锟斤拷锟斤拷锟铰伙拷使锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷某些锟斤拷锟接碉拷锟斤拷锟斤拷拢锟斤拷锟斤拷锟矫伙拷泻锟斤拷实锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷芑岬硷拷卤锟斤拷锟斤拷锟�

示锟斤拷锟斤拷 

DELETE FROM orders WHERE order_id = 100;  -- 锟斤拷锟斤拷锟斤拷锟皆硷拷锟斤拷锟介，锟斤拷锟斤拷锟斤拷住 `customers` 锟斤拷

锟斤拷锟� orders 锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷锟� customers 锟斤拷锟斤拷锟斤拷没锟叫猴拷锟绞碉拷锟斤拷锟斤拷锟斤拷锟斤拷锟杰伙拷锟斤拷住 customers 锟斤拷锟斤拷

7. MyISAM 锟芥储锟斤拷锟斤拷亩锟叫达拷锟斤拷锟�

锟斤拷 MyISAM 锟芥储锟斤拷锟斤拷锟叫ｏ拷写锟斤拷锟斤拷锟斤拷锟斤拷住锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷使只锟睫革拷锟斤拷一锟叫★拷锟斤拷锟斤拷锟斤拷之锟戒不锟结互锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷写锟斤拷锟斤拷之锟斤拷岱拷锟斤拷锟斤拷锟斤拷锟斤拷锟剿ｏ拷MyISAM 锟斤拷锟节达拷锟斤拷锟竭诧拷锟斤拷写锟斤拷锟斤拷时锟斤拷锟杰伙拷频锟斤拷锟斤拷锟斤拷锟斤拷

示锟斤拷锟斤拷 

INSERT INTO myisam_table (name, age) VALUES ('John', 30);  -- 写锟斤拷锟斤拷住锟斤拷锟斤拷锟斤拷

锟斤拷锟斤拷写锟斤拷锟斤拷锟叫达拷锟斤拷锟斤拷锟斤拷锟斤拷锟狡碉拷锟斤拷锟斤拷锟阶★拷锟接帮拷觳拷锟斤拷锟斤拷堋锟�

锟斤拷锟斤拷锟斤拷伪锟斤拷锟斤拷锟斤拷锟斤拷锟�

锟斤拷锟斤拷锟斤拷影锟斤拷锟斤拷锟捷匡拷牟锟斤拷锟斤拷院锟斤拷锟斤拷埽锟斤拷锟斤拷锟斤拷锟斤拷通锟斤拷锟斤拷要锟斤拷锟斤拷锟斤拷锟解。锟斤拷锟斤拷锟斤拷一些锟斤拷锟斤拷锟斤拷锟皆硷拷锟斤拷锟斤拷锟斤拷锟侥凤拷锟斤拷锟斤拷

1. 使锟矫猴拷锟绞的存储锟斤拷锟斤拷

锟斤拷锟斤拷使锟斤拷 InnoDB 锟芥储锟斤拷锟芥，锟斤拷支锟斤拷 锟叫硷拷锟斤拷 锟斤拷锟斤拷锟斤拷锟节撅拷锟斤拷锟斤拷锟斤拷锟斤拷锟铰憋拷锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷锟街拷拢锟� MyISAM 使锟矫碉拷锟斤拷 锟斤拷锟斤拷锟斤拷 锟斤拷锟节诧拷锟斤拷锟斤拷写锟斤拷锟斤拷锟斤拷锟斤拷锟杰较差。

2. 锟斤拷锟斤拷锟斤拷锟绞碉拷锟斤拷锟斤拷

通锟斤拷为锟斤拷询锟斤拷锟斤拷锟叫碉拷锟叫达拷锟斤拷锟绞碉拷锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷全锟斤拷扫锟借。锟斤拷锟界，锟斤拷锟斤拷憔拷锟斤拷锟斤拷锟� name 锟街段斤拷锟叫诧拷询锟酵革拷锟铰ｏ拷应锟斤拷为 name 锟街段达拷锟斤拷锟斤拷锟斤拷锟斤拷 

CREATE INDEX idx_name ON users (name);

锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷效锟斤拷锟斤拷锟斤拷锟斤拷锟侥匡拷锟斤拷锟皆★拷

3. 锟斤拷锟劫筹拷锟斤拷锟斤拷

锟斤拷时锟斤拷未锟结交锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷锟接讹拷锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷询锟斤拷锟斤拷耍锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷执锟斤拷时锟戒，确锟斤拷锟斤拷锟斤拷锟斤拷锟叫撅拷锟斤拷锟斤拷刹锟斤拷锟斤拷锟斤拷峤伙拷锟�

4. 使锟斤拷 OPTIMIZE 锟斤拷 ANALYZE 锟斤拷锟斤拷

锟斤拷些锟斤拷锟斤拷锟斤拷锟阶★拷锟斤拷锟皆拷锟斤拷荩锟斤拷锟街癸拷锟斤拷锟斤拷亩锟叫达拷锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷锟叫╋拷锟斤拷锟绞庇︼拷锟斤拷锟竭诧拷锟斤拷时锟斤拷巍锟�

5. 锟斤拷锟斤拷锟轿诧拷锟斤拷

锟斤拷锟斤拷锟揭达拷写锟斤拷锟斤拷锟� UPDATE 锟斤拷 DELETE 锟斤拷锟斤拷锟皆斤拷锟斤拷锟斤拷锟斤拷锟斤拷执锟叫ｏ拷锟皆硷拷锟斤拷每锟轿诧拷锟斤拷锟芥及锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷锟解长时锟斤拷锟斤拷锟斤拷锟斤拷

示锟斤拷锟斤拷 

DELETE FROM users WHERE created_at < '2022-01-01' LIMIT 1000;  -- 锟斤拷锟斤拷删锟斤拷

6. 锟斤拷锟斤拷锟斤拷式锟斤拷锟斤拷

锟斤拷锟斤拷锟斤拷锟斤拷使锟斤拷 LOCK TABLES 锟斤拷锟斤拷锟斤拷式锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷锟节高诧拷锟斤拷锟斤拷锟斤拷锟铰★拷InnoDB 锟斤拷锟斤拷锟斤拷锟斤拷坪锟斤拷屑锟斤拷锟斤拷丫锟斤拷愎挥︼拷源锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷锟解。

锟脚★拷锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷锟組ySQL锟叫碉拷锟斤拷锟斤拷锟斤拷锟狡的ｏ拷

锟斤拷 MySQL 锟叫ｏ拷 锟斤拷锟斤拷 锟斤拷Deadlock锟斤拷锟斤拷指锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷却锟斤拷苑锟斤拷锟斤拷械锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷嵌锟斤拷薹锟斤拷锟斤拷锟街达拷小锟斤拷锟斤拷锟揭伙拷殖锟斤拷锟斤拷牟锟斤拷锟斤拷锟斤拷猓拷锟斤拷锟斤拷锟斤拷诟卟锟斤拷锟斤拷锟斤拷锟铰ｏ拷锟斤拷锟斤拷锟节凤拷锟斤拷锟斤拷同锟斤拷锟斤拷锟斤拷锟斤拷源时锟斤拷锟阶诧拷锟斤拷锟斤拷锟斤拷锟斤拷

通锟斤拷锟斤拷锟解：

锟斤拷锟皆帮拷锟斤拷锟斤拷锟斤拷锟轿拷锟斤拷锟斤拷锟斤拷锟斤拷锟揭伙拷锟秸凤拷希锟斤拷锟斤拷嵌锟斤拷锟揭拷苑锟斤拷锟铰凤拷锟斤拷芗锟斤拷锟角帮拷锟斤拷锟紸 锟斤拷住锟斤拷 B 锟斤拷路锟斤拷B 锟街碉拷住锟斤拷 A 锟斤拷路锟斤拷谁也锟斤拷锟斤拷锟斤拷锟矫ｏ拷锟斤拷锟斤拷锟斤拷锟斤拷硕锟斤拷锟阶★拷恕锟斤拷锟斤拷锟斤拷锟斤拷菘锟斤拷斜锟斤拷锟轿拷锟斤拷锟� A 锟饺达拷锟斤拷锟斤拷 B 锟酵凤拷锟斤拷源锟斤拷锟斤拷锟斤拷锟斤拷 B 同时也锟节等达拷锟斤拷锟斤拷 A 锟酵凤拷锟斤拷源锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷锟睫凤拷锟斤拷锟斤拷锟斤拷

锟斤拷锟斤拷锟侥诧拷锟斤拷锟斤拷锟教ｏ拷

  1. 锟斤拷锟斤拷 A 锟斤拷取锟斤拷源 X 锟斤拷锟斤拷 锟斤拷
  2. 锟斤拷锟斤拷 B 锟斤拷取锟斤拷源 Y 锟斤拷锟斤拷 锟斤拷
  3. 锟斤拷锟斤拷 A 锟斤拷锟皆伙拷取锟斤拷源 Y 锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷源 Y 锟斤拷锟斤拷锟斤拷 B 锟斤拷住锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷 A 锟斤拷锟斤拷却锟阶刺拷锟�
  4. 锟斤拷锟斤拷 B 锟斤拷锟皆伙拷取锟斤拷源 X 锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷源 X 锟窖撅拷锟斤拷锟斤拷锟斤拷 A 锟斤拷住锟斤拷锟斤拷锟斤拷 B 也锟斤拷锟斤拷却锟阶刺拷锟�
  5. 锟斤拷锟斤拷牵锟斤拷锟斤拷锟� A 锟饺达拷 B锟斤拷锟斤拷锟斤拷 B 锟饺达拷 A锟斤拷锟轿筹拷一锟斤拷循锟斤拷锟饺达拷锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷

MySQL 锟叫碉拷锟斤拷锟斤拷锟斤拷锟狡ｏ拷

MySQL 使锟矫的存储锟斤拷锟斤拷 InnoDB 锟结供锟斤拷锟叫硷拷锟斤拷锟斤拷锟斤拷锟斤拷然锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷突锟侥革拷锟绞ｏ拷锟斤拷也锟斤拷锟斤拷锟阶碉拷锟斤拷锟斤拷锟斤拷锟斤拷InnoDB 锟斤拷锟斤拷锟斤拷锟斤拷时锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷獠拷锟斤拷锟斤拷锟斤拷锟斤拷猓拷锟斤拷毓锟斤拷锟斤拷锟揭伙拷锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷平锟斤拷帧锟�

锟斤拷锟斤拷锟侥达拷锟斤拷锟斤拷式锟斤拷

锟斤拷 MySQL 锟叫ｏ拷锟斤拷 InnoDB 锟斤拷獾斤拷锟斤拷锟绞憋拷锟斤拷锟斤拷锟窖★拷锟截癸拷 锟斤拷锟斤拷锟斤拷小锟斤拷锟斤拷锟斤拷 锟斤拷通锟斤拷锟角回癸拷锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷源锟斤拷锟斤拷锟斤拷然锟斤拷锟斤拷锟斤拷锟斤拷突锟斤拷朔锟斤拷锟揭伙拷锟斤拷锟斤拷锟斤拷锟较拷锟斤拷锟斤拷锟斤拷冢锟� 

ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction

锟斤拷一锟斤拷锟斤拷锟今被回癸拷锟斤拷锟斤拷一锟斤拷锟斤拷锟斤拷锟斤拷约锟斤拷锟街达拷校锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷锟解。

锟斤拷伪锟斤拷锟斤拷锟斤拷锟斤拷锟�

锟斤拷锟斤拷 MySQL 锟斤拷锟皆讹拷锟斤拷獠拷锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷锟狡碉拷锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷锟接帮拷锟较低筹拷锟斤拷埽锟斤拷锟剿撅拷锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷锟角猴拷锟叫憋拷要锟侥★拷锟斤拷锟斤拷锟斤拷一些锟斤拷锟斤拷锟侥憋拷锟斤拷锟斤拷锟斤拷锟侥凤拷锟斤拷锟斤拷

1. 锟教讹拷锟斤拷锟斤拷顺锟斤拷

确锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷锟节凤拷锟绞讹拷锟脚憋拷锟斤拷锟斤拷锟斤拷锟铰际憋拷锟斤拷锟窖拷锟酵拷锟剿筹拷锟斤拷锟斤拷锟斤拷锟皆达拷锟斤拷锟斤拷锟斤拷锟斤拷员锟斤拷獠煌拷锟斤拷锟斤拷锟斤拷纸锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷目锟斤拷锟斤拷浴锟�

示锟斤拷锟斤拷
锟斤拷锟斤拷锟斤拷锟斤拷锟节革拷锟斤拷 users 锟斤拷锟斤拷 orders 锟斤拷时锟斤拷锟斤拷锟斤拷锟斤拷住 users 锟斤拷锟斤拷锟斤拷锟斤拷住 orders 锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷

2. 锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷围

锟斤拷锟斤拷锟斤拷锟斤拷每锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷锟侥凤拷围锟斤拷时锟戒，锟斤拷锟解长时锟斤拷占锟斤拷锟斤拷锟斤拷锟斤拷锟界，锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷锟街达拷锟绞憋拷洌拷锟斤拷俨锟斤拷锟揭拷牟锟窖拷锟�

示锟斤拷锟斤拷 

BEGIN;
UPDATE users SET age = age + 1 WHERE id = 1;
COMMIT;

锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷一锟斤拷锟斤拷锟斤拷锟节斤拷锟叫癸拷锟斤拷牟锟斤拷锟斤拷锟饺达拷锟矫伙拷锟斤拷锟诫。

3. 使锟矫猴拷锟绞碉拷锟斤拷锟斤拷

锟节诧拷询时锟斤拷锟斤拷使锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷锟截憋拷锟斤拷锟斤拷 UPDATE 锟斤拷 DELETE 锟斤拷锟斤拷时锟斤拷锟斤拷锟绞碉拷锟斤拷锟斤拷锟斤拷锟皆硷拷锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷锟接讹拷锟斤拷锟斤拷锟斤拷锟斤拷锟侥凤拷锟秸★拷

示锟斤拷锟斤拷
为 user_id 锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷 

CREATE INDEX idx_user_id ON orders (user_id);

4. 锟斤拷锟劫诧拷锟斤拷锟斤拷

锟斤拷锟斤拷锟斤拷锟捷匡拷牟锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷锟杰的伙拷锟斤拷锟斤拷锟斤拷锟节高诧拷锟斤拷锟斤拷锟斤拷陆锟斤拷写锟斤拷锟斤拷锟斤拷锟斤拷莶锟斤拷锟斤拷锟斤拷卟锟斤拷锟斤拷锟斤拷驶锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷母锟斤拷省锟�

5. 锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷爰讹拷锟�

使锟矫猴拷锟绞碉拷锟斤拷锟斤拷锟斤拷爰讹拷锟斤拷锟皆硷拷锟斤拷锟斤拷锟斤拷锟斤拷突锟斤拷InnoDB 支锟街讹拷锟斤拷锟斤拷锟斤拷锟斤拷爰讹拷锟斤拷畛ｏ拷锟斤拷锟斤拷锟� REPEATABLE READ 锟斤拷 READ COMMITTED 锟斤拷锟斤拷锟叫ｏ拷 READ COMMITTED 锟斤拷锟诫级锟斤拷锟斤拷约锟斤拷锟斤拷锟斤拷锟斤拷玫锟斤拷锟斤拷锟斤拷锟接讹拷锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷锟侥革拷锟绞★拷

示锟斤拷锟斤拷 

SET TRANSACTION ISOLATION LEVEL READ COMMITTED;

6. 通锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷时锟斤拷

锟斤拷锟斤拷锟揭拷源锟斤拷锟斤拷锟斤拷萁锟斤拷懈锟斤拷禄锟缴撅拷锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷钥锟斤拷欠锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷锟矫匡拷锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷佣锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷铡锟�

示锟斤拷锟斤拷 

DELETE FROM orders WHERE order_date < '2023-01-01' LIMIT 1000;

使锟斤拷 LIMIT 锟斤拷锟斤拷删锟斤拷锟斤拷锟斤拷锟捷★拷

锟斤拷渭锟斤拷锟斤拷锟斤拷锟斤拷

锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷时锟斤拷InnoDB 锟斤拷锟节达拷锟斤拷锟斤拷志锟叫硷拷录锟斤拷锟斤拷锟斤拷锟斤拷息锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷锟较拷约锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷锟酵诧拷询锟斤拷锟斤拷锟斤拷通锟斤拷锟斤拷锟斤拷 SQL 锟斤拷锟斤拷取锟斤拷锟斤拷锟斤拷息锟斤拷 

SHOW ENGINE INNODB STATUS;

锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷锟绞� InnoDB 锟斤拷状态锟斤拷息锟斤拷锟斤拷锟叫帮拷锟斤拷锟斤拷锟揭伙拷锟斤拷锟斤拷锟斤拷锟斤拷锟较革拷锟较拷锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷锟侥等达拷锟斤拷锟斤拷锟�

十锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷呕锟斤拷锟斤拷锟窖拷模锟�

锟斤拷锟斤拷询锟斤拷锟脚伙拷锟斤拷锟斤拷头锟斤拷锟斤拷锟�

1. 锟斤拷锟斤拷锟斤拷锟斤拷询锟斤拷志

锟斤拷锟饺ｏ拷确锟斤拷 锟斤拷锟斤拷询锟斤拷志 锟斤拷Slow Query Log锟斤拷锟窖匡拷锟斤拷锟斤拷锟斤拷锟斤拷 MySQL 锟斤拷锟斤拷锟斤拷录执锟斤拷时锟戒超锟斤拷指锟斤拷锟斤拷值锟侥诧拷询锟斤拷锟斤拷锟斤拷锟酵拷锟斤拷锟斤拷锟斤拷锟叫╋拷锟街撅拷锟斤拷页锟较低筹拷泻锟绞憋拷畛わ拷牟锟窖拷锟�

锟斤拷锟斤拷锟斤拷锟斤拷询锟斤拷志锟斤拷 

SET GLOBAL slow_query_log = 'ON';
SET GLOBAL long_query_time = 1;  -- 锟斤拷锟斤拷锟斤拷锟斤拷询锟斤拷值为 1 锟斤拷

之锟斤拷锟斤拷锟斤拷圆榭达拷锟斤拷锟窖拷锟街撅拷锟斤拷私锟斤拷锟叫╋拷锟窖达拷锟绞憋拷锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷些锟斤拷询锟斤拷锟斤拷锟街斤拷锟斤拷锟脚伙拷锟斤拷

2. 使锟斤拷 EXPLAIN 锟斤拷锟斤拷锟斤拷询执锟叫计伙拷

使锟斤拷 EXPLAIN 锟斤拷锟斤拷锟斤拷园锟斤拷锟斤拷锟斤拷私锟� MySQL 锟斤拷锟街达拷胁锟窖拷锟斤拷锟斤拷锟斤拷峁╋拷锟较拷纾猴拷锟窖拷欠锟绞癸拷锟斤拷锟斤拷锟斤拷锟斤拷锟缴拷锟斤拷硕锟斤拷锟斤拷小锟斤拷锟斤拷锟绞斤拷取锟斤拷锟斤拷锟斤拷通锟斤拷锟介看执锟叫计伙拷锟斤拷锟斤拷锟街诧拷询锟叫碉拷锟斤拷锟斤拷瓶锟斤拷锟斤拷

示锟斤拷锟斤拷 

EXPLAIN SELECT * FROM users WHERE name = 'John';

执锟叫斤拷锟斤拷锟斤拷锟绞撅拷锟窖拷锟斤拷锟斤拷停锟斤拷锟� ALL 锟斤拷 INDEX 锟斤拷 RANGE 锟饺ｏ拷锟斤拷锟斤拷锟斤拷 MySQL 锟角凤拷使锟斤拷锟斤拷全锟斤拷扫锟借（ ALL 锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷 INDEX 锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷锟� ALL 锟斤拷示全锟斤拷扫锟借，锟斤拷通锟斤拷锟斤拷锟斤拷要锟脚伙拷锟斤拷锟脚号★拷

锟斤拷谓锟斤拷一些锟斤拷锟斤拷锟侥斤拷锟斤拷锟�

  • type : ALL 锟斤拷示全锟斤拷扫锟借，锟斤拷要锟脚伙拷锟斤拷 range 锟斤拷 ref 锟斤拷 const 锟斤拷示使锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷锟杰较好★拷
  • rows : 锟斤拷示 MySQL 预锟斤拷扫锟斤拷锟斤拷锟斤拷锟斤拷锟皆斤拷锟皆斤拷谩锟缴拷锟斤拷锟斤拷锟斤拷越锟洁，锟斤拷询锟侥匡拷锟斤拷越锟斤拷
  • key : 锟斤拷示 MySQL 使锟斤拷锟斤拷锟侥革拷锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷锟绞疚� NULL 锟斤拷锟斤拷示没锟斤拷使锟斤拷锟斤拷锟斤拷锟斤拷

3. 锟斤拷锟斤拷锟斤拷锟脚伙拷锟斤拷锟斤拷

锟斤拷锟斤拷锟斤拷 MySQL 锟脚伙拷锟斤拷锟斤拷询锟斤拷锟筋常锟斤拷锟街讹拷之一锟斤拷锟绞碉拷锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷锟劫诧拷询锟斤拷扫锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷询锟劫度★拷

锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷锟脚伙拷锟斤拷锟皆ｏ拷

  • 锟斤拷锟斤拷锟斤拷锟斤拷 锟斤拷锟皆诧拷询锟叫筹拷锟矫的癸拷锟斤拷锟斤拷锟斤拷锟斤拷 WHERE 锟接撅拷锟叫碉拷锟街段达拷锟斤拷锟斤拷锟斤拷锟斤拷
  • 锟斤拷锟斤拷锟斤拷锟斤拷 锟斤拷锟斤拷锟芥及锟斤拷锟斤拷锟斤拷锟斤拷牟锟窖拷锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷纾� SELECT * FROM users WHERE age = 30 AND status = 'active'; 锟斤拷锟斤拷锟斤拷为 (age, status) 锟斤拷锟斤拷一锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷

示锟斤拷锟斤拷 

CREATE INDEX idx_name ON users (name);
CREATE INDEX idx_age_status ON users (age, status);
  • 锟斤拷锟斤拷锟斤拷锟斤拷 锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷锟窖拷锟斤拷锟斤拷锟斤拷锟斤拷锟街段ｏ拷MySQL 锟斤拷锟斤拷直锟接达拷锟斤拷锟斤拷锟叫讹拷取锟斤拷锟捷ｏ拷锟斤拷锟斤拷锟斤拷锟斤拷时锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷芗锟斤拷锟� I/O 锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷锟窖э拷省锟�

示锟斤拷锟斤拷 

SELECT name FROM users WHERE age = 30;  -- 锟斤拷锟� name 锟斤拷 age 锟斤拷锟斤拷锟斤拷锟斤拷锟叫ｏ拷MySQL 锟斤拷锟斤拷只锟斤拷锟斤拷锟斤拷
  • 锟斤拷锟斤拷锟斤拷锟斤拷失效 锟斤拷确锟斤拷锟斤拷询锟斤拷锟斤拷锟杰癸拷锟斤拷确锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷锟界：
    • 锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷侄锟绞癸拷煤锟斤拷锟斤拷锟斤拷锟斤拷式锟斤拷锟斤拷 WHERE UPPER(name) = 'JOHN' 锟结导锟斤拷锟斤拷锟斤拷失效锟斤拷
    • 使锟矫撅拷确匹锟戒，锟斤拷锟斤拷锟斤拷锟斤拷 LIKE '%abc' 锟斤拷锟斤拷通锟斤拷锟角白猴拷牟锟窖拷锟�

4. 锟脚伙拷锟斤拷询锟斤拷锟�

锟侥斤拷锟斤拷询锟斤拷锟斤拷写锟斤拷锟斤拷锟皆达拷锟斤拷锟斤拷锟斤拷锟斤拷堋锟斤拷锟斤拷锟斤拷羌锟斤拷殖锟斤拷锟斤拷锟斤拷呕锟斤拷锟斤拷椋�

  • 选锟斤拷锟斤拷实锟斤拷锟斤拷锟斤拷锟斤拷锟� 锟斤拷锟斤拷锟斤拷使锟矫猴拷锟绞碉拷锟斤拷锟斤拷锟斤拷锟酵ｏ拷锟斤拷锟斤拷使锟矫癸拷锟斤拷锟斤拷侄纬锟斤拷取锟斤拷锟斤拷纾拷锟� INT 锟芥储锟斤拷锟戒，锟斤拷锟斤拷锟斤拷锟斤拷 VARCHAR 锟斤拷
  • 锟斤拷锟劫诧拷询锟侥凤拷锟截斤拷锟� 锟斤拷锟斤拷锟斤拷 SELECT * 锟斤拷只锟斤拷询锟斤拷要锟斤拷锟街段★拷锟斤拷锟截碉拷锟斤拷锟斤拷越锟劫ｏ拷锟斤拷询锟劫讹拷越锟届。

示锟斤拷锟斤拷 

SELECT id, name FROM users WHERE age = 30;  -- 锟斤拷锟斤拷 SELECT *锟斤拷只取锟斤拷锟斤拷锟斤拷侄锟�

  • 锟街解复锟接诧拷询 锟斤拷锟斤拷锟斤拷锟接的诧拷询锟斤拷锟轿拷锟斤拷小锟斤拷询锟斤拷锟斤拷时锟斤拷锟斤拷锟斤拷锟斤拷锟杰ｏ拷锟斤拷锟斤拷锟斤拷锟斤拷锟芥及锟斤拷锟斤拷锟斤拷锟斤拷锟绞憋拷锟斤拷锟斤拷纾拷锟揭伙拷锟斤拷锟斤拷锟斤拷锟斤拷 JOIN 锟侥革拷锟接诧拷询锟斤拷锟斤拷殖啥锟轿诧拷询锟斤拷锟斤拷锟叫硷拷锟斤拷锟斤拷

  • 使锟斤拷 LIMIT 锟脚伙拷锟斤拷页 锟斤拷锟节达拷锟斤拷姆锟揭筹拷锟窖拷校锟斤拷锟斤拷锟缴拷锟斤拷锟斤拷锟斤拷锟捷★拷锟斤拷锟斤拷通锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷锟� LIMIT 锟脚伙拷锟斤拷

示锟斤拷锟斤拷 

SELECT * FROM users WHERE id > 1000 LIMIT 10;  -- 锟斤拷锟斤拷锟斤拷锟斤拷锟侥凤拷页

5. 锟脚伙拷 JOIN 锟斤拷锟斤拷

JOIN 锟斤拷锟斤拷锟节讹拷锟斤拷锟窖拷谐锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷椎锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷猓拷乇锟斤拷堑锟斤拷锟斤拷艽锟绞憋拷锟斤拷呕锟� JOIN 时锟斤拷注锟斤拷锟斤拷锟筋：

  • 确锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷锟街讹拷锟斤拷锟斤拷锟斤拷 锟斤拷锟斤拷锟斤拷 JOIN 锟斤拷使锟矫碉拷锟街段ｏ拷确锟斤拷锟斤拷锟斤拷锟叫猴拷锟绞碉拷锟斤拷锟斤拷锟斤拷

示锟斤拷锟斤拷 

SELECT * FROM orders o JOIN users u ON o.user_id = u.id WHERE u.status = 'active';

锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷拢锟� user_id 锟斤拷 id 应锟矫分憋拷锟斤拷 orders 锟斤拷 users 锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷

  • 锟斤拷锟斤拷锟斤拷锟劫癸拷锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷 锟斤拷通锟斤拷锟斤拷筛选锟斤拷锟斤拷要锟斤拷锟斤拷锟捷ｏ拷锟劫斤拷锟斤拷 JOIN 锟斤拷锟斤拷锟斤拷锟斤拷锟界，锟斤拷筛选锟斤拷锟斤拷锟斤拷锟斤拷锟接诧拷询锟叫ｏ拷锟斤拷锟斤拷锟斤拷要锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷

示锟斤拷锟斤拷 

SELECT * FROM (SELECT id FROM users WHERE status = 'active') u JOIN orders o ON o.user_id = u.id;

6. 锟斤拷锟斤拷 MySQL 锟斤拷锟矫诧拷锟斤拷

MySQL 锟斤拷一些锟斤拷锟矫诧拷锟斤拷直锟斤拷影锟斤拷锟窖拷锟斤拷埽锟斤拷乇锟斤拷锟斤拷诟卟锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷隆锟斤拷锟斤拷锟斤拷锟揭恍╋拷锟斤拷锟斤拷锟斤拷呕锟斤拷锟斤拷锟斤拷锟�

  • innodb_buffer_pool_size 锟斤拷锟斤拷锟斤拷 InnoDB 锟侥伙拷锟斤拷卮锟叫★拷锟斤拷锟斤拷锟斤拷锟� MySQL 锟斤拷锟斤拷锟矫讹拷锟斤拷锟节达拷锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷页锟斤拷锟斤拷锟街低拷锟斤拷锟斤拷锟轿低筹拷诖锟斤拷 70%-80%锟斤拷锟皆便尽锟斤拷锟杰硷拷锟劫达拷锟斤拷 I/O锟斤拷

  • query_cache_size 锟斤拷锟斤拷锟较低筹拷写锟斤拷锟斤拷牟锟窖拷锟斤拷锟斤拷锟斤拷同锟侥ｏ拷锟斤拷锟斤拷锟斤拷锟矫诧拷询锟斤拷锟芥，锟皆硷拷锟斤拷锟截革拷锟斤拷询锟侥匡拷锟斤拷锟斤拷锟斤拷要注锟斤拷锟斤拷牵锟組ySQL 8.0 锟叫诧拷询锟斤拷锟芥被锟斤拷锟矫ｏ拷锟斤拷为锟斤拷锟皆高诧拷锟斤拷锟斤拷锟斤拷锟斤拷锟杰达拷锟斤拷锟斤拷锟斤拷瓶锟斤拷锟斤拷

  • tmp_table_size 锟斤拷 max_heap_table_size 锟斤拷锟斤拷些锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷时锟斤拷锟斤拷锟斤拷锟斤拷锟节达拷锟斤拷使锟矫碉拷锟斤拷锟斤拷小锟斤拷锟斤拷锟斤拷锟斤拷些锟斤拷锟斤拷锟斤拷值锟斤拷锟斤拷锟皆憋拷锟斤拷频锟斤拷锟斤拷锟斤拷时锟斤拷写锟斤拷锟斤拷蹋锟斤拷佣锟斤拷锟斤拷锟斤拷锟斤拷锟� GROUP BY 锟斤拷询锟斤拷效锟绞★拷

7. 使锟矫凤拷锟斤拷锟斤拷

锟斤拷锟斤拷锟侥憋拷锟角筹拷锟襟，匡拷锟皆匡拷锟斤拷使锟斤拷 锟斤拷锟斤拷锟斤拷 锟斤拷锟脚伙拷锟斤拷询锟斤拷锟杰★拷锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷锟捷分成讹拷锟斤拷锟叫★拷锟斤拷锟斤拷锟斤拷颖锟斤拷锟組ySQL 锟斤拷锟皆革拷锟捷诧拷询锟斤拷锟斤拷直锟接讹拷位锟斤拷某锟斤拷锟斤拷锟斤拷锟斤拷锟接讹拷锟斤拷锟斤拷扫锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷锟�

示锟斤拷锟斤拷 

CREATE TABLE orders (
    order_id INT,
    order_date DATE,
    user_id INT,
    ...
) PARTITION BY RANGE (YEAR(order_date)) (
    PARTITION p0 VALUES LESS THAN (2010),
    PARTITION p1 VALUES LESS THAN (2015),
    PARTITION p2 VALUES LESS THAN (2020),
    PARTITION p3 VALUES LESS THAN MAXVALUE
);

8. 锟斤拷锟斤拷锟斤拷锟斤拷锟酵筹拷锟斤拷锟斤拷

锟斤拷锟侥筹拷锟斤拷锟斤拷锟窖拷锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷突锟斤拷锟斤拷锟斤拷锟斤拷傻模锟接︼拷镁锟斤拷锟斤拷锟斤拷獬わ拷锟斤拷锟斤拷频锟斤拷锟斤拷锟斤拷锟斤拷通锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷围锟斤拷使锟矫猴拷锟绞的革拷锟诫级锟金、憋拷锟斤拷锟斤拷锟斤拷锟叫达拷锟斤拷锟斤拷确锟绞斤拷锟斤拷锟斤拷锟斤拷锟斤拷却锟斤拷锟斤拷锟斤拷锟斤拷姆锟斤拷锟斤拷锟斤拷佣锟斤拷涌锟斤拷询锟劫度★拷

9. 锟斤拷锟斤拷

锟斤拷锟斤拷使锟斤拷 MySQL 锟斤拷锟斤拷锟侥诧拷询锟斤拷锟芥，锟姐还锟斤拷锟斤拷使锟斤拷应锟矫诧拷幕锟斤拷锟斤拷锟狡ｏ拷锟斤拷 Redis锟斤拷Memcached锟斤拷锟斤拷锟斤拷频锟斤拷锟斤拷锟绞碉拷锟斤拷锟捷伙拷锟芥到锟节达拷锟叫ｏ拷锟斤拷锟斤拷锟斤拷锟捷匡拷姆锟斤拷锟狡碉拷省锟�

示锟斤拷锟斤拷 

# 锟斤拷应锟矫层缓锟斤拷 MySQL 锟斤拷询锟斤拷锟�
cache.set('users:active', active_users, timeout=60*5)  # 锟斤拷锟斤拷 5 锟斤拷锟斤拷

锟杰结：

  • 锟斤拷锟斤拷锟斤拷锟斤拷询锟斤拷志 锟斤拷使锟斤拷 EXPLAIN 锟剿斤拷执锟叫计伙拷锟斤拷锟脚伙拷锟斤拷锟斤拷询锟侥碉拷一锟斤拷锟斤拷
  • 锟斤拷锟斤拷锟斤拷锟绞碉拷锟斤拷锟斤拷 锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷卟锟窖拷俣龋锟斤拷锟斤拷锟斤拷锟斤拷诖锟斤拷锟叫★拷
  • 锟脚伙拷锟斤拷询锟斤拷锟斤拷 JOIN 锟斤拷锟斤拷 锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷扫锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷锟绞癸拷锟斤拷锟斤拷锟斤拷锟斤拷遣锟窖拷锟�
  • 通锟斤拷锟斤拷锟斤拷 MySQL 锟斤拷锟矫诧拷锟斤拷 锟斤拷锟斤拷锟斤拷系统锟斤拷锟节达拷锟斤拷锟皆达拷锟斤拷锟斤拷锟斤拷省锟�
  • 锟节达拷锟斤拷锟绞癸拷梅锟斤拷锟斤拷锟斤拷锟斤拷锟酵拷锟斤拷锟斤拷锟斤拷锟劫讹拷锟斤拷锟捷匡拷锟窖癸拷锟�

锟斤拷锟剿狄伙拷锟�(锟斤拷锟阶拷锟斤拷锟斤拷蓿锟斤拷锟斤拷锟斤拷锟斤拷)

锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷一锟捷帮拷锟斤拷锟斤拷锟叫达拷锟剿拷锟绞记ｏ拷一锟斤拷锟接达拷通锟斤拷锟揭碉拷锟轿讹拷锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷原锟斤拷没锟斤拷么锟窖★拷
锟斤拷锟角达拷锟斤拷写锟侥ｏ拷 7701页锟斤拷BAT锟斤拷锟斤拷写锟斤拷刷锟斤拷始牵锟斤拷锟斤拷锟給ffer锟矫碉拷锟斤拷锟斤拷

锟斤拷锟侥ｏ拷锟斤拷锟斤拷录锟节ｏ拷锟揭的硷拷锟斤拷锟斤拷站 cxykk.com锟斤拷锟斤拷锟斤拷员锟斤拷锟斤拷锟斤拷锟秸� 锟斤拷锟叫达拷锟斤拷锟斤拷锟芥经锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷锟杰癸拷师锟缴筹拷之路锟斤拷锟饺撅拷锟斤拷锟斤拷锟�

锟斤拷一锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷锟睫★拷锟斤拷锟斤拷锟斤拷锟秸诧拷

锟斤拷锟睫讹拷锟斤拷锟斤拷姆浅锟斤拷锟揭拷锟斤拷锟斤拷锟斤拷锟斤拷蓿锟斤拷痈锟斤拷锟阶拷一锟角筹拷锟叫硷拷锟斤拷

小锟斤拷锟狡硷拷锟侥讹拷

锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷锟侥斤拷为锟斤拷锟斤拷锟斤拷息锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷同锟斤拷锟睫观碉拷锟街な碉拷锟斤拷锟斤拷锟斤拷锟�

锟斤拷锟斤拷锟狡碉拷锟斤拷锟�

锟斤拷锟斤拷

同锟斤拷锟斤拷锟斤拷

锟斤拷锟斤拷

锟斤拷锟斤拷锟斤拷讯

锟斤拷锟斤拷

  • 锟斤拷戏锟狡硷拷
  • 锟斤拷匕姹�

锟斤拷锟斤拷
  • 锟斤拷讯
  • 锟斤拷频

锟斤拷锟斤拷
  • 锟斤拷卓锟饺帮拷
  • 苹锟斤拷锟饺帮拷

锟斤拷锟节猴拷锟斤拷锟斤拷锟斤拷系锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷锟�锟斤拷锟斤拷站锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷锟狡硷拷锟斤拷锟斤拷

频锟斤拷 锟斤拷锟斤拷频锟斤拷锟斤拷锟斤拷锟斤拷专锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷卓锟斤拷锟斤拷锟�锟斤拷app锟斤拷锟斤拷

锟狡硷拷 锟斤拷原锟斤拷锟斤拷锟斤拷锟斤拷锟铰帮拷锟斤拷锟斤拷锟斤拷锟杰匡拷锟斤拷锟斤拷锟截诧拷锟斤拷

锟斤拷锟斤拷 锟斤拷锟斤拷色锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷 锟斤拷锟斤拷锟�锟斤拷锟斤拷战锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷

扫锟斤拷维锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷锟街伙拷锟芥本锟斤拷

扫锟斤拷维锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷微锟脚癸拷锟节号ｏ拷

锟斤拷站锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷锟较达拷锟斤拷锟斤拷锟斤拷锟街凤拷锟斤拷陌锟饺拷锟斤拷敕拷始锟�[email protected]

锟斤拷ICP锟斤拷2022002427锟斤拷-10 锟芥公锟斤拷锟斤拷锟斤拷锟斤拷43070202000427锟斤拷© 2013~2025 haote.com 锟斤拷锟斤拷锟斤拷