Im vorherigen Artikel „Übung (1) – MySQL-Leistungsoptimierung“ haben wir über einige Prinzipien des Datenbanktabellendesigns, eine Einführung in das Explain-Tool und Best Practices zur Optimierung von Indizes für SQL-Anweisungen gesprochen Der Artikel wird fortgesetzt. Lassen Sie uns darüber sprechen, wie Sie den geeigneten Index in MySQL auswählen.
MySQL-Trace-Tool
Ob MySQL am Ende einen Index verwendet oder eine Tabelle mehrere Indizes umfasst und wie man am Ende einen Index auswählt, können Sie mit dem Trace-Tool herausfinden Beeinträchtigt die Leistung von MySQL und kann daher nur vorübergehend analysiert werden. Verwenden Sie SQL und schließen Sie es sofort nach der Verwendung.
Fallanalyse
Bevor wir über das Trace-Tool sprechen, schauen wir uns zunächst einen Fall an:
# 示例表CREATE TABLE`employees`(`id` int(11) NOT NULL AUTO_INCREMENT,`name` varchar(24) NOT NULL DEFAULT '' COMMENT '姓名',`age` int(11) NOT NULL DEFAULT '0' COMMENT '年龄',`position` varchar(20) NOT NULL DEFAULT '' COMMENT '职位',`hire_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '入职时间',
PRIMARY KEY (`id`), KEY `idx_name_age_position` (`name`,`age`,`position`) USING BTREE
)ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8 COMMENT='员工记录表';
INSERT INTO employees(name,age,position,hire_time)VALUES('ZhangSan',23,'Manager',NOW());INSERT INTO employees(name,age,position,hire_time)VALUES('HanMeimei', 23,'dev',NOW());INSERT INTO employees(name,age,position,hire_time) VALUES('Lucy',23,'dev',NOW());复制代码
So wählen Sie einen geeigneten Index für MySQL aus
EXPLAIN select * from employees where name > 'a';复制代码
Wenn Sie den Namensindex verwenden, müssen Sie ihn durchlaufen Der gemeinsame Indexbaum des Namensfelds, und dann müssen Sie den durchquerten Primärschlüsselwert in den Primärschlüsselindexbaum einfügen. Die Kosten sind höher als bei einem vollständigen Tabellenscan. Sie können den abdeckenden Index verwenden. Sie müssen also nur den gemeinsamen Indexbaum des Namensfelds durchlaufen, um alle Ergebnisse wie folgt zu erhalten:
EXPLAIN select name,age,position from employees where name > 'a' ;复制代码
EXPLAIN select * from employees where name > 'zzz' ;复制代码
Für die beiden oben genannten Ausführungsergebnisse von name>'a' code> und <code>name>'zzz', ob MySQL sich letztendlich für die Verwendung des Index entscheidet. Oder eine Tabelle umfasst mehrere Indizes. Wie kann MySQL letztendlich den Index auswählen? Das Einschalten des Trace-Tools wirkt sich auf die Leistung von MySQL aus, sodass wir die SQL-Nutzung nur vorübergehend analysieren und nach der Verwendung sofort schließen können. name>'a' 和 name>'zzz' 的执行结果，mysql最终是否选择走索引或者一张表涉及多个索引，mysql最终如何选择索引，我们可以用trace工具来一查究竟，开启trace工具会影响mysql性能，所以只能临时分析sql使用，用完之后立即关闭。
EXPLAIN select * from employees where name = 'ZhangSan' and age = 18 order by position,age复制代码
Fall 1
Führen Sie diese beiden SQL-Sätze aus
EXPLAIN select * from employees where name = 'ZhangSan' order by age asc, position desc;复制代码
🎜, um den Trace-Wert zu erhalten. Weitere Informationen finden Sie in den Hinweisen🎜
EXPLAIN select * from employees where name in ('ZhangSan', 'hjh') order by age, position;复制代码
🎜🎜Fazit: Die Kosten für den vollständigen Tabellenscan sind niedriger als für den Indexscan, also MySQL endlich wählt den vollständigen Tabellenscan. 🎜
Fall 2
EXPLAIN select * from employees where name > 'a' order by name;复制代码
🎜🎜Fazit🎜: Wenn wir uns das Trace-Feld ansehen, können wir sehen, dass die Kosten für den Index-Scan niedriger sind als die für den vollständigen Tabellen-Scan MySQL wählt schließlich den Index-Scan. 🎜
Allgemeine SQL-Tiefenoptimierung 🎜
Sortieren nach und Gruppieren nach Optimierung 🎜
Fall 1
EXPLAIN select name,age,position from employees where name > 'a' order by name;复制代码
🎜🎜🎜🎜🎜🎜Analyse: 🎜🎜🎜Verwendung der 🎜Präfixregel ganz links 🎜: mittleres Feld Es kann nicht gebrochen werden, daher verwendet die Abfrage name index Aus key_len = 74 können wir auch erkennen, dass die Altersindexspalte im 🎜sorting🎜-Prozess verwendet wird, da es keinen filesort verwenden im Extra-Feld. 🎜<h3 data-id="heading-10">Fall 2</h3><pre class="brush:php;toolbar:false;">EXPLAIN select * from employees where name = &#39;ZhangSan&#39; order by position;复制代码</pre>🎜<img alt="Übung (2) – MySQL-Leistungsoptimierung" class="lazyload" src="https://img.php.cn/upload/%20Article/000/000/052/eb3551193382561721acbe6f7a17ece5-3.png" data- style="max-width:90%" data- style="max-width:90%">🎜🎜🎜🎜🎜Analyse: 🎜🎜🎜Aus den Ausführungsergebnissen von EXPLAIN: key_len = 74 . Da die Position zum Sortieren verwendet wird, wird das Alter übersprungen und <code>Using filesort angezeigt. 🎜
Fall 3
set session optimizer_trace="enabled=on",end_markers_in_json=on; #开启traceselect * from employees where name = 'ZhangSan' order by position;select * from information_schema.OPTIMIZER_TRACE;复制代码
🎜🎜🎜🎜🎜🎜Analyse: 🎜🎜🎜Abfragen verwenden nur Indexname, Alter und Position werden zum Sortieren verwendet, ohne <code>Filesort verwenden. 🎜
🎜🎜🎜🎜🎜🎜Analyse: 🎜🎜🎜Das Ausführungsergebnis ist das gleiche wie im Fall erläutert 3, aber Using filesort erschien, weil die Indexerstellungsreihenfolge Name, Alter, Position ist, aber beim Sortieren, Alter und Position 🎜die Positionen umkehren🎜. 🎜
案例5
EXPLAIN select * from employees where name = 'ZhangSan' and age = 18 order by position,age复制代码
分析：
与案例4对比，在Extra中并未出现** Using filesort **，因为 age 为常量，在排序中被优化，所以索引未颠倒，不会出现 Using filesort 。
案例6
EXPLAIN select * from employees where name = 'ZhangSan' order by age asc, position desc;复制代码
分析：
虽然排序的字段列与索引顺序一样，且 order by 默认升序，这里 position desc 变成列降序，导致与索引的排序方式不同，从而产生 Using filesort 。MySQL8 以上版本有降序索引可以支持该种查询方式。
案例7
EXPLAIN select * from employees where name in ('ZhangSan', 'hjh') order by age, position;复制代码
分析：
对于排序来说，多个相等条件也是范围查询。
案例8
EXPLAIN select * from employees where name > 'a' order by name;复制代码
可以用覆盖索引优化
EXPLAIN select name,age,position from employees where name > 'a' order by name;复制代码
优化总结
MySQL支持两种方式的排序 filesort 和 index。Using index 是指MySQL 扫描索引本身完成排序。index 效率高，filesort 效率低。
order by 满足两种情况会使用 Using index.
order by 语句使用索引最左前例。
使用 where 子句与 order by 子句条件列组合满足索引最左前例。
尽量在索引列上完成排序，遵循索引建立（索引创建的顺序）时的最左前缀法则。
如果 order by 的条件不在索引列上，就会产生 Using filesort。
能用覆盖索引尽量用覆盖索引。
group by 和 order by 很类似，其实质是先排序后分组，遵循索引创建顺序的最左前缀法则。对于 group by 的优化如果不需要排序的可以加上 order by null 禁止排序。注意：where 高于 having，能写在 where 中的限定条件就不要去 having 限定了。
EXPLAIN select * from employees where name = 'ZhangSan' order by position;复制代码
查看下这条sql对应trace结果如下(只展示排序部分):
set session optimizer_trace="enabled=on",end_markers_in_json=on; #开启traceselect * from employees where name = 'ZhangSan' order by position;select * from information_schema.OPTIMIZER_TRACE;复制代码
set max_length_for_sort_data = 10;
select * from employees where name = 'ZhangSan' order by position;select * from information_schema.OPTIMIZER_TRACE;复制代码
drop procedure if exists insert_emp;
delimiter ;;
create procedure insert_emp()begin
declare i int;
set i=1;
while(i<=100000) do
insert into employees(name,age,position) values(CONCAT('hjh',i),i,'dev'); set i=i+1;
end while;end;;
delimiter ;
call insert_emp();复制代码
另外如果原SQL是order by 非主键的字段，按照上面说饿的方法改写会导致两条SQL的结果不一致。所以这种改写得满足以下两个条件：
主键自增且连续
结果是按照主键排序的
案例2: 根据非主键字段排序的分页查询
再看一个根据非主键字段排序的分页查询，SQL 如下:
select * from employees ORDER BY name limit 9000,5;复制代码
EXPLAIN select * from employees ORDER BY name limit 90000,5;复制代码
发现并没有使用 name 字段的索引(key 字段对应的值为 null)，具体原因上前面讲过 : 扫描整个索引并查找到没索引的行(可能要遍历多个索引树)的成本比扫描全表的成本更高，所以优化器放弃使用索引。 知道不走索引的原因，那么怎么优化呢? 其实关键是让排序时返回的字段尽可能少，所以可以让排序和分页操作先查出主键，然后根据主键查到对应的记录，SQL 改写如下：
select * from employees e inner join (select id from employees order by name limit 90000,5) ed on e.id = ed.id;复制代码
需要的结果与原 SQL 一致，执行时间减少了一半以上，我们再对比优化前后sql的执行计划:
EXPLAIN select * from employees e inner join (select id from employees order by name limit 90000,5) ed on e.id = ed.id;复制代码
原 SQL 使用的是 filesort 排序，而优化后的 SQL 使用的是索引排序。
Join关联查询优化
#示例表CREATE TABLE `t1` ( `id` INT (11) NOT NULL AUTO_INCREMENT, `a` INT (11) DEFAULT NULL, `b` INT (11) DEFAULT NULL,
PRIMARY KEY (`id`), KEY `idx_a` (`a`)
) ENGINE = INNODB AUTO_INCREMENT = 10001 DEFAULT CHARSET = utf8;CREATE TABLE t2 LIKE t1;复制代码
往t1表插入1万行记录，往t2表插入100行记录
#t1 1万条记录drop procedure if exists insert_emp_t1;
delimiter ;;
create procedure insert_emp_t1()begin
declare i int;
set i=1;
while(i<=10000) do
insert into t1(a,b) values(i,i); set i=i+1;
end while;end;;
delimiter ;
call insert_emp_t1();
#t2 100条记录drop procedure if exists insert_emp_t2;
delimiter ;;
create procedure insert_emp_t2()begin
declare i int;
set i=1;
while(i<=100) do
insert into t2(a,b) values(i,i); set i=i+1;
end while;end;;
delimiter ;
call insert_emp_t2();复制代码
set global query_cache_size=0;set global query_cache_type=0;复制代码
EXPLAIN select count(1) from employees;
EXPLAIN select count(id) from employees;EXPLAIN select count(name) from employees;
EXPLAIN select count(*) from employees;复制代码
Fragen Sie die Gesamtzahl der von MySQL verwalteten Zeilen ab.
Anzeigen des Tabellenstatus
Für
myisam Die Tabelle der Speicher-Engine
hat eine sehr hohe Leistung für Zählabfragen ohne Where-Bedingung, da die Gesamtzahl der Zeilen der Tabelle der Myisam-Speicher-Engine von MySQL auf der Festplatte gespeichert wird und die Abfrage dies tut muss nicht berechnet werden.
Für die Tabelle MySQL der
innodb-Speicher-Engine wird nicht die Gesamtzahl der Datensatzzeilen in der Tabelle gespeichert, und die Abfrageanzahl muss in Echtzeit berechnet werden. Tabellenstatus anzeigenWenn Sie nur die Schätzung der Gesamtzahl der Zeilen in der Tabelle kennen müssen, können Sie die folgende SQL-Abfrage verwenden. Die Leistung ist sehr hoch.
Behalten Sie die Gesamtzahl bei Redis
Behalten Sie beim Einfügen oder Löschen von Tabellendatenzeilen den Zählwert der Gesamtzahl der Tabellenzeilenschlüssel in Redis bei (verwenden Sie den Befehl incr oder decr). Diese Methode ist jedoch möglicherweise ungenau und es ist schwierig, die Transaktionskonsistenz zwischen Tabellenoperationen sicherzustellen und Redis-Operationen. Zähltabelle hinzufügenBehalten Sie beim Einfügen oder Löschen von Tabellendatenzeilen gleichzeitig die Zähltabelle bei, damit sie in derselben Transaktion ausgeführt werden können.
Wenn Sie mehr über das Erlernen des Programmierens erfahren möchten, achten Sie bitte auf die Spalte „PHP-Schulung“!
Das obige ist der detaillierte Inhalt vonÜbung (2) – MySQL-Leistungsoptimierung. Für weitere Informationen folgen Sie bitte anderen verwandten Artikeln auf der PHP chinesischen Website!
Stellungnahme：
Dieser Artikel ist reproduziert unter:juejin.im. Bei Verstößen wenden Sie sich bitte an admin@php.cn löschen
🎜🎜🎜🎜🎜Analyse: 🎜🎜🎜Verwendung der 🎜Präfixregel ganz links 🎜: mittleres Feld Es kann nicht gebrochen werden, daher verwendet die Abfrage 
🎜🎜🎜🎜🎜Analyse: 🎜🎜🎜Abfragen verwenden nur 
🎜🎜🎜🎜🎜Analyse: 🎜🎜🎜Das Ausführungsergebnis ist das gleiche wie im Fall erläutert 3, aber 
