Heim >Datenbank >MySQL-Tutorial >Ein Artikel, der Ihnen hilft, die zugrunde liegenden Prinzipien von MYSQL zu verstehen
In der Spalte „MySQL-Video-Tutorial“ werden die zugrunde liegenden Prinzipien vorgestellt.
Erster Blick auf eine SQL-Abfrage
Eine aktualisierte SQL-Ausführung
client=> ··· => Der Vorgang ist derselbe. Um den <code>UPDATE
-Prozess zu verstehen, werfen wir zunächst einen Blick auf das Architekturmodell von Innodb. 客户端 => ··· => 执行引擎
是一样的流程,都要先查到这条数据,然后再去更新。要想理解 UPDATE
流程我们先来看看,Innodb的架构模型。上一张 MYSQL 官方InnoDB架构图:
连接器(JDBC、ODBC等) =>
[MYSQL 内部
[Connection Pool] (授权、线程复用、连接限制、内存检测等) => [SQL Interface] (DML、DDL、Views等) [Parser] (Query Translation、Object privilege) [Optimizer] (Access Paths、 统计分析) [Caches & Buffers] => [Pluggable Storage Engines]复制代码
]
=> [File]
这里有个关键点,当我们去查询数据时候会先 拿着我们当前查询的 page
去 buffer pool
中查询 当前page
是否在缓冲池
中。如果在,则直接获取。
而如果是update操作
时,则会直接修改 Buffer
中的值。这个时候,buffer pool
中的数据就和我们磁盘中实际存储的数据不一致
了,称为脏页
。每隔一段时间,Innodb存储引擎就会把脏页数据
刷入磁盘。一般来说当更新一条数据,我们需要将数据给读取到buffer
中修改,然后写回磁盘,完成一次 落盘IO
操作。
为了提高update
的操作性能,Mysql在内存中做了优化,可以看到,在架构图的缓冲池
中有一块区域叫做:change buffer
。顾名思义,给change后的数据,做buffer的
,当更新一个没有 unique index
的数据时,直接将修改的数据放到 change buffer
,然后通过 merge
操作完成更新,从而减少了那一次 落盘的IO
操作。
没有唯一索引的数据更新时
,为什么必须要没有唯一索引的数据更新时
才能直接放入change buffer
呢?如果是有唯一约束的字段
,我们在更新数据后,可能更新的数据和已经存在的数据有重复,所以只能从磁盘中把所有数据读出来比对
才能确定唯一性。写多读少
的时候,就可以通过 增加 innodb_change_buffer_max_size
来调整 change buffer
在buffer pool
中所占的比例,默认25(即:25%)有四种情况:
redo log
写满的时候,merge到磁盘谈到redo,就要谈到innodb的 crash safe
,使用 WAL 的方式实现(write Ahead Logging,在写之前先记录日志)
这样就可以在,当数据库崩溃的后,直接从 redo log
中恢复数据,保证数据的正确性
redo log 默认存储在两个文件中 ib_logfile0
ib_logfile1
,这两个文件都是固定大小的
。为什么需要固定大小?
这是因为redo log
的 顺序读取
rrreee]
=> [File]
Hier gibt es einen wichtigen Punkt: Wenn wir Daten abfragen, nehmen wir zuerst die
Seite
, die wir gerade abfragen, und gehen zum Pufferpool
, um die aktuelle Seite abzufragen. code> Ob es sich im <code>Pufferpool
befindet. Wenn ja, holen Sie es sich direkt. Aktualisierungsvorgang
handelt, wird der Wert im Puffer
direkt geändert. Zu diesem Zeitpunkt sind die Daten im Pufferpool
inkonsistent
mit den tatsächlich auf unserer Festplatte gespeicherten Daten, was als schmutzige Seite
bezeichnet wird. Hin und wieder löscht die Innodb-Speicher-Engine verschmutzte Seitendaten
auf die Festplatte. Im Allgemeinen müssen wir beim Aktualisieren eines Datenelements die Daten zur Änderung in den Puffer
einlesen und sie dann zurück auf die Festplatte schreiben, um einen disk IO
-Vorgang abzuschließen. update
zu verbessern, wurde MySQL im Speicher optimiert. Sie können sehen, dass es im Pufferpool von Architekturdiagramm
einen Bereich mit dem Namen gibt: Puffer ändern
. Wie der Name schon sagt, erstellt einen Puffer für die geänderten Daten
. Beim Aktualisieren von Daten ohne eindeutigen Index
werden die geänderten Daten direkt im Änderungspuffer abgelegt. code> code> und schließen Sie dann die Aktualisierung durch den <code>merge
-Vorgang ab, wodurch der E/A-Vorgang des Disk-Drops reduziert wird.
Wenn die Daten ohne eindeutigen Index aktualisiert werden
, warum muss das so sein? Wenn die Daten ohne eindeutigen Index aktualisiert werden
bevor es direkt in eingefügt werden kann. Was ist mit dem Änderungspuffer
? Wenn es sich um ein Feld mit eindeutigen Einschränkungen
handelt, werden die aktualisierten Daten nach der Aktualisierung der Daten möglicherweise mit den vorhandenen Daten dupliziert, sodass wir nur alle Daten von der Festplatte lesen und vergleichen können um die Einzigartigkeit zu bestimmen.
Wenn unsere Daten also mehr geschrieben und weniger gelesen
sind, können wir den Änderungspuffer
im Puffer anpassen, indem wir den <code>innodb_change_buffer_max_size
-Anteil erhöhen des Pools, der Standardwert ist 25 (also: 25%)
Redo-Protokoll
schreiben Wenn es voll ist, auf Festplatte zusammenführen🎜🎜absturzsicheren
von innodb sprechen WAL-Implementierung (Ahead-Protokollierung schreiben, Protokoll vor dem Schreiben aufzeichnen)🎜🎜Auf diese Weise können die Daten bei einem Datenbankabsturz direkt aus Redo-Log
wiederhergestellt werden, um die Richtigkeit der Daten sicherzustellen.🎜 🎜Redo-Log wird standardmäßig in zwei Dateien gespeichert: ib_logfile0
ib_logfile1
, beide Dateien haben eine feste Größe
. Warum benötigen Sie eine feste Größe? 🎜🎜Dies ist auf die Funktion sequentielles Lesen
des Redo-Protokolls
zurückzuführen, bei der es sich um einen kontinuierlichen Speicherplatz handeln muss🎜🎜2. Zufälliges Lesen und Schreiben sowie sequentielles Lesen und Schreiben🎜🎜 Siehe Bild 🎜🎜🎜 Im Allgemeinen sind unsere Daten auf der Festplatte verstreut: 🎜🎜Die Lese- und Schreibsequenz der mechanischen Festplatte ist: 🎜🎜🎜Suchen Sie die Spur🎜🎜Warten Sie darauf, in den entsprechenden Sektor zu wechseln🎜🎜Beginnen Sie mit dem Lesen und Schreiben 🎜🎜🎜 Festkörperlesen und -schreiben: 🎜Tatsächlich verwenden wir beim Speichern, egal ob mechanisch oder Solid-State, den Dateisystem
und die Festplatte Sie interagieren miteinander, und es gibt zwei Möglichkeiten, mit ihnen umzugehen. Zufälliges Lesen und Schreiben
und Sequentielles Lesen und Schreiben
文件系统
与磁盘打交道的,而他们打交道的方式就有两个。随机读写
和顺序读写
块
(默认 1block=8扇区=4K)一串连续的块
中,这样读取速度就大大提升了看到buffer pool
中的Log Buffer
,其就是用来写 redo log 之前存在的缓冲区
在这里,redo log具体的执行策略有三种:
Log Buffer
,只需要每秒写redo log 磁盘数据一次,性能高,但会造成数据 1s 内的一致性问题。适用于强实时性
,弱一致性
,比如评论区评论
Log Buffer
,同时写入磁盘,性能最差,一致性最高。 适用于弱实时性
,强一致性
,比如支付场景
Log Buffer
,同时写到os buffer
(其会每秒调用 fsync
将数据刷入磁盘),性能好,安全性也高。这个是实时性适中
一致性适中
的,比如订单类
。我们通过innodb_flush_log_at_trx_commit
就可以设置执行策略。默认为 1
自适应Hash索引
主要用于加快查询 页
。在查询时,Innodb通过监视索引搜索的机制来判断当前查询是否能走Hash索引
。比如LIKE运算符和% 通配符就不能走。存储在一个叫ibdata1
的文件中,其中包含:
Buffer Pool
写入数据页时,不是直接写入到文件,而是先写入到这个区域。这样做的好处的是,一但操作系统,文件系统或者mysql挂掉,可以直接从这个Buffer
中获取数据。每一张表都有一张 .ibd
的文件,存储数据和索引。
每表文件表空间
可以使得 ALTER TABLE
与 TRUNCATE TABLE
性能得到很好的提升。比如 ALTER TABLE
,相较于对驻留在共享表空间中的表,在修改表时,会进行表复制操作
,这可能会增加表空间占用的磁盘空间量
。此类操作可能需要与表中的数据以及索引一样多的额外空间。该空间不会像每表文件表空间
Blöcken
verteilt (Standard 1block=). 8 Sektoren =4K)einer Reihe aufeinanderfolgender Blöcke
verteilt, sodass die Lesegeschwindigkeit erheblich verbessert wirdProtokollpuffer
im Pufferpool
, und es wird verwendet, um den Puffer zu schreiben, der vor dem Redo-Log vorhanden war🎜🎜Hier gibt es drei spezifische Ausführungsstrategien für das Redo-Log:🎜Log Buffer zu schreiben Code>, nur jede Die Redo-Log-Festplattendaten werden einmal pro Sekunde geschrieben, was eine hohe Leistung bietet, aber innerhalb von 1 Sekunde zu Datenkonsistenzproblemen führt. Anwendbar auf <code>starke Echtzeit
, schwache Konsistenz
, wie z. B. Kommentare im Kommentarbereich
🎜🎜schreiben Sie Protokollpuffer
und gleichzeitig schreiben Auf die Festplatte ist die Leistung am schlechtesten und die Konsistenz am höchsten. Anwendbar auf Schwache Echtzeit
, Starke Konsistenz
, wie z. B. Zahlungsszenario
🎜🎜Schreiben Sie Protokollpuffer
und schreiben Sie darauf Der OS-Puffer
(der jede Sekunde fsync
aufruft, um Daten auf die Festplatte zu übertragen) bietet eine gute Leistung und hohe Sicherheit. Dies ist moderate Echtzeit
moderate Konsistenz
, wie z. B. Auftragstyp
. 🎜🎜🎜Wir können die Ausführungsrichtlinie über innodb_flush_log_at_trx_commit
festlegen. Der Standardwert ist 1
🎜Adaptive Hash Index
wird hauptsächlich verwendet, um die Abfrage von page . Bei der Abfrage bestimmt Innodb, ob die aktuelle Abfrage den <code>Hash-Index
durchlaufen kann, indem es den Indexsuchmechanismus überwacht. Beispielsweise können der LIKE-Operator und das Platzhalterzeichen % nicht verwendet werden. 🎜🎜ibdata1-Datei, die Folgendes enthält: 🎜🎜🎜InnoDB Data Dictionary, das Metadaten wie Tabellenstrukturinformationen, Indizes usw. speichert. 🎜🎜Doublewrite Buffer Wenn <code>Buffer Pool
die Datenseite schreibt , es wird nicht direkt in die Datei geschrieben, sondern zuerst in diesen Bereich geschrieben. Dies hat den Vorteil, dass die Daten direkt aus diesem Puffer
abgerufen werden können, sobald das Betriebssystem, das Dateisystem oder MySQL hängt. 🎜🎜Puffer ändern Wenn MySQL heruntergefahren wird, werden die Änderungen auf der Festplatte gespeichert. 🎜🎜Rückgängig-Protokolle zeichnen Transaktionsänderungsvorgänge auf >🎜Jede Tabelle verfügt über eine .ibd
-Datei zum Speichern von Daten und Indizes. 🎜🎜🎜Mit Datei-pro-Tabelle-Tablespace
kann die Leistung von ALTER TABLE
und TRUNCATE TABLE
erheblich verbessert werden. Beispielsweise führt ALTER TABLE
im Vergleich zu einer Tabelle in einem gemeinsam genutzten Tabellenbereich beim Ändern der Tabelle einen Tabellenkopiervorgang
aus, der den Tabellenbereich vergrößern kann belegt. Die Größe des Speicherplatzes
. Solche Vorgänge erfordern möglicherweise genauso viel zusätzlichen Speicherplatz wie die Daten in der Tabelle und den Indizes. Dieser Speicherplatz wird nicht wie Datei-pro-Tabelle-Tablespace
an das Betriebssystem zurückgegeben. 🎜🎜Tablespace-Datendateien pro Tabelle können auf separaten Speichergeräten zur E/A-Optimierung, Speicherplatzverwaltung oder Sicherung erstellt werden. Dies bedeutet, dass Tabellendaten und -strukturen problemlos zwischen verschiedenen Datenbanken migriert werden können. 🎜🎜Wenn eine Datenbeschädigung auftritt, Sicherungen oder Binärprotokolle nicht verfügbar sind oder eine MySQL-Serverinstanz nicht neu gestartet werden kann, spart das Speichern von Tabellen in einer einzigen Tablespace-Datendatei Zeit und erhöht die Chance auf eine erfolgreiche Wiederherstellung. 🎜🎜🎜Natürlich gibt es Vor- und Nachteile:🎜Tabelle löschen
(es sei denn, Sie verwalten die Fragmentierung selbst)Drop table
的时候会影响性能(除非你自己管理了碎片)fsync
一次性刷入数据到文件中文件句柄
, 以提供维持对文件的持续访问共享表空间
,他可以存储多个表
的数据每表表空间
小
存储在一个叫 ibtmp1
的文件中。正常情况下Mysql启动的时候会创建临时表空间,停止的时候会删除临时表空间。并且它能够自动扩容。
原子性
,即当修改到一半,出现异常,可以通过Undo 日志回滚。系统表空间``撤销表空间``临时表空间
中,如架构图所示。前面已经介绍过
origin
,返给执行器modification
modification
刷入内存,Buffer Pool
的 Change Buffer
说了 Undo
、Redo
也顺便说一下Bin log
.
innodb
引擎没有多大关系,我们前面说的那两种日志,都在是innodb引擎层的。而Bin log
是处于服务层
的。所以他能被各个引擎所通用Bin log
是以事件的形式,记录了各个 DDL DML
语句,它是一种逻辑意义上的日志。主从复制
, 从
服务器拿到主
服务器的bin log
日志,然后执行。数据恢复
,拿到某个时间段的日志,重新执行一遍。索引
试试华丽的分割线
要想彻底弄明白InnoDB中的索引
是个什么东西,就必须要了解它的文件存储级别
Pages, Extents, Segments, and Tablespaces
它们的关系是:
extent
大小为 1M
即 64
个 16KB
的Page
。平常我们文件系统所说的页大小是 4KB
,包含 8
个 512Byte
的扇区。所以有时候,我们被要求主键为什么要是有序的原因就是,如果我们在一个有序的字段上,建立索引,然后插入数据。
在存储的时候,innodb就会按着顺序一个个存储到 页
上,存满一个页再去申请新的页,然后接着存。
但如果我们的字段是无序的,存储的位置就会在不同的页上。当我们的数据存储到一个已经被 存满
的页
上时,就会造成页分裂
,从而形成碎片
Da jede Tabelle in ihre eigene unterteilt ist Tabellendatei, das Betriebssystem kann nicht gleichzeitig fsync
einmaliges Flushen von Daten in die Datei durchführen
Dateihandle
von bei Jede Tabellendatei bietet kontinuierlichen Zugriff auf Dateien
mehreren Tabellen
gespeichert werden. 🎜Wenn die gleiche Anzahl von Tabellen gespeichert wird, ist der verbrauchte Speicher kleiner als tablespace pro Tabelle
🎜Die Unterstützung für die Platzierung von Tabellenpartitionen in regulären Tablespaces ist in MySQL 5.7.24 veraltet und wird in einer zukünftigen MySQL-Version nicht mehr unterstützt. werden in einer Datei namens ibtmp1
gespeichert. Unter normalen Umständen erstellt MySQL beim Start einen temporären Tabellenbereich und löscht den temporären Tabellenbereich beim Stoppen. Und es kann automatisch erweitert werden. 🎜
Atomizität
von Änderungsvorgängen, d. Sie können es über Rollback rückgängig machen protokollieren. 🎜Es speichert die Originaldaten vor Beginn der Transaktion und diesem Änderungsvorgang. 🎜Das Rückgängig-Protokoll existiert im Rollback-Segment und das Rollback-Segment existiert im Systemtabellenbereich „Rückgängig-Tabellenbereich“ temporärer Tabellenbereich
, wie im Architekturdiagramm gezeigt. Wurde bereits eingeführt🎜
origin
nennen, und an den Executor zurückgegeben🎜Im Executor Das Ändern von Daten wird als Änderung
🎜Flash Änderung
in den Speicher, Puffer ändern
des Pufferpools
🎜Engine-Schicht: Rückgängig-Protokoll aufzeichnen (um Transaktionsatomizität zu erreichen) 🎜Engine-Schicht: Redo-Protokoll aufzeichnen (wird für die Wiederherstellung nach einem Absturz verwendet) 🎜Service-Schicht: Bin-Protokoll aufzeichnen (DDL aufzeichnen) li>🎜 li>🎜Das Erfolgsergebnis der Aktualisierung zurückgeben🎜Die Daten warten darauf, vom Arbeitsthread auf die Festplatte geleert zu werden🎜
Ich habe Undo
und Redo
erwähnt und übrigens, Bin log
.🎜 🎜🎜Dieses Protokoll hat wenig mit der innodb
-Engine zu tun. Die beiden zuvor erwähnten Protokolle befinden sich beide auf der Ebene der innodb-Engine. Und Bin log
befindet sich in der Serviceschicht
. Es kann also von allen Motoren verwendet werden🎜Was ist seine Hauptfunktion? Zunächst zeichnet Bin log
jede DDL DML
-Anweisung in Form von Ereignissen auf. Es handelt sich im logischen Sinne um ein Protokoll. 🎜Es kann eine Master-Slave-Replikation
realisieren und das <code>bin log
-Protokoll des Master
-Servers vom server. 🎜Führen Sie eine Datenwiederherstellung
durch, rufen Sie die Protokolle eines bestimmten Zeitraums ab und führen Sie sie erneut aus.
Index
hinzu >Probieren Sie es ausWunderschöne Trennlinie🎜
Wenn Sie es gründlich verstehen wollenWas ist ein Index in InnoDB
? Sie müssen seineDateispeicherebene
verstehen. 🎜
Seiten, Extents, Segmente und Tablespaces🎜
Ihre Beziehung ist:🎜
🎜
1M
, also 64
16KB
Seite
. Die Seitengröße, auf die sich unser Dateisystem normalerweise bezieht, beträgt 4 KB
und enthält 8
Sektoren von 512 Byte
. 🎜
Manchmal werden wir gefragt, warum der Primärschlüssel geordnet sein muss, weil wir einen Index für ein geordnetes Feld erstellen und dann Daten einfügen.
Beim Speichern speichert innodb sie nacheinander auf Seiten
. Wenn eine Seite voll ist, wird sie für eine neue Seite angewendet und dann mit dem Speichern fortgefahren. 🎜
Wenn unsere Felder jedoch ungeordnet sind, befinden sich die Speicherorte auf verschiedenen Seiten. Wenn unsere Daten auf einer Seite
gespeichert werden, die bereits voll
ist, kommt es zu einer Seitenteilung
, wodurch Fragmente
entstehen . 🎜
B+tree
-Diagramm oben gezeigt, werden Zeilendaten
auf den untergeordneten Knoten gespeichert und der des Index Wenn die Sortierreihenfolge
mit der Indexschlüsselwertreihenfolge
übereinstimmt, handelt es sich um einen Clustered-Index
. Der Primärschlüsselindex ist ein Clustered-Index, alle anderen sind Hilfsindizes
B+树
图所示,子节点上存储行数据
,并且索引的排列的顺序
和索引键值顺序
一致的话就是 聚簇索引
。主键索引就是聚簇索引,除了主键索引,其他所以都是辅助索引
辅助索引
,它的叶子节点上只存储自己的值
和主键索引的值
。这就意味着,如果我们通过辅助索引查询所有数据,就会先去查找辅助索引
中的主键键值
,然后再去主键索引
里面,查到相关数据
。这个过程称为回表
rowid
如果没有主键索引
怎么办呢?聚簇索引
。rowid
的东西,根据这个id来创建 聚簇索引
搞清楚什么是索引,结构是什么之后。 我们来看看,什么时候我们要用到索引,理解了这些能更好的帮助我们创建正确高效的索引
离散度低不建索引,也就是数据之间相差不大的就没必要建立索引。(因为建立索引,在查询的时候,innodb大多数据都是相同的,我走索引 和全表没什么差别就会直接全表查询
)。比如 性别字段。这样反而浪费了大量的存储空间。
联合字段索引,比如 idx(name, class_name)
select * from stu where class_name = xx and name = lzw
查询时,也能走 idx
这个索引的,因为优化器将SQL优化为了 name = lzw and class_name = xx
select ··· where name = lzw
的时候,不需要创建一个单独的 name
索引,会直接走 idx
这个索引覆盖索引
。如果我们此次查询的所有数据
全都包含在索引里面了,就不需要再 回表
去查询了。比如:select class_name from stu where name =lzw
索引条件下推(index_condition_pushdown)
select * from stu where name = lzw and class_name like '%xx'
索引条件下推
,因为后面是 like '%xx'
的查询条件,所以这里首先根据 name
走 idx联合索引
查询到几条数据后,再回表
查询到全量row数据
,然后在server层
进行 like 过滤找到数据引擎层
对like也进行过滤了,相当于把server层
这个过滤操作下推到引擎层
了。如图所示:页分裂
,索引按顺序存储,如果存储页满了,再去插入就会造成页分裂)函数
的时候不会使用索引,所以没必要额外建select count(distinct left(name, 10))/count(*)
来看离散度,决定到底提取前几位)优化器决定的
。比如你使用了 Cost Base Optimizer
Hilfsindex
erstellen, ist sein Blatt Knoten speichern nur eigenen Wert
und Wert des Primärschlüsselindex
. Das heißt, wenn wir alle Daten über den Hilfsindex abfragen, suchen wir zuerst den Primärschlüsselwert
im Hilfsindex
und gehen dann zum Primärindex SchlüsselindexIm Inneren finden Sie relevante <code>Daten
. Dieser Vorgang heißt table return
rowid
Was tun, wenn kein Primärschlüsselindex
vorhanden ist? Es gibt keinen Primärschlüssel, aber einen eindeutigen Schlüssel, der nicht null ist. Dann wird ein Clustered-Index
basierend auf diesem Schlüssel erstellt.
rowid
und erstellt darauf basierend einen Clustered Index
die gesamte Tabelle abfragen
). Zum Beispiel das Feld „Geschlecht“. Dadurch wird viel Speicherplatz verschwendet. idx(name, class_name)
🎜🎜🎜Bei der Ausführung von select * from stu where class_name = xx and name = lzw
auch Abfrage Der Index idx
kann verwendet werden, da der Optimierer SQL auf name = lzw und class_name = xx
optimiert. 🎜🎜Wenn Sie benötigen, wählen Sie ··· wobei name = ist lzw
, es ist nicht erforderlich, einen separaten name
-Index zu erstellen, der idx
-Index wird direkt verwendet🎜🎜abdeckender Index
. Wenn alle Daten
, die wir dieses Mal abfragen, alle im Index enthalten sind, besteht keine Notwendigkeit, zur Abfrage zur Tabelle zurückzukehren
. Zum Beispiel: select class_name from stu where name =lzw
🎜🎜🎜🎜index_condition_pushdown)🎜🎜🎜Es gibt so ein SQL, select * from stu where name = lzw und class_name wie '%xx'
🎜🎜Wenn es keinen Indexbedingungs-Pushdown
gibt, weil darauf die Abfragebedingung von wie '%xx'
folgt, also hier ist der erste Verwenden Sie gemäß name
den idx-Joint-Index
, um mehrere Daten abzufragen, und kehren Sie dann zur Tabelle zurück
, um Vollständige Zeilendaten
. Führen Sie dann eine ähnliche Filterung auf der Serverebene
durch, um die Daten zu finden. 🎜🎜Wenn vorhanden, filtern Sie diese direkt auf der Engine-Ebene
, was dem Filtern der Serverebene
code> entspricht. Dieser Filtervorgang wird auf die Engine-Ebene verschoben
. Wie im Bild gezeigt: 🎜🎜🎜🎜Funktionen
verwendet werden, sodass keine zusätzlichen Funktionen erstellt werden müssen 🎜🎜 Wenn eine implizite Konvertierung erfolgt, z. B. die Konvertierung von Zeichenfolgen in int, sind keine Indizes erforderlich 🎜🎜Ein außergewöhnlich langes Feld, Sie können die ersten paar Ziffern abfangen, um einen Index zu erstellen (Sie können select count(distinct left(name, 10) verwenden ))/count(*)
, um die Streuung zu sehen und zu entscheiden, wie die ersten paar Ziffern extrahiert werden) 🎜Optimierer
entschieden. Wenn Sie beispielsweise den kostenbasierten Optimierer Cost Base Optimizer
verwenden, verwenden Sie die Optimierung mit den niedrigsten Kosten. 🎜🎜🎜Nachdem wir den Index verstanden haben, können wir die Kopie von Lock Chapter öffnen. 🎜🎜Eine weitere wunderschöne Trennlinie.🎜🎜🎜Lock Chapter : 🎜Prämisse in einer Transaktion:
SQL92-Standardbestimmungen: (Die Parallelität nimmt von links nach rechts ab)
RR
. Der Anfangswert der Rollback-ID in der Abbildung sollte nicht 0, sondern NULL sein. Der Einfachheit halber wird er als 0RCs MVCC-Implementierung erstellt eine Version für mehrere Lesevorgänge derselben Transaktion
und RR erstellt eine Version für alle Lesevorgänge derselben Transaktion
RR 的 MVCC实现
,图中 回滚id 初始值不应该为0而是NULL,这里为了方便写成0
RC的MVCC实现是对 同一个事务的多个读 创建一个版本
而 RR 是 同一个事务任何一条都创建一个版本
通过MVCC
与LBCC
的结合,InnoDB能解决对于不加锁
条件下的 幻读的情况。而不必像 Serializable
一样,必须让事务串行
进行,无任何并发
。
下面我们来深入研究一下InnoDB锁
是如何实现 RR
事务隔离级别的
表级别
的 共享和排它锁 => (IS、IX)上面这四把锁
是最基本锁的类型
这三把锁,理解成对于上面四把锁
实现的三种算法方式,我们这里暂且把它们称为:高阶锁
上面三把是额外扩展的锁
lock in share mode
。排它锁默认 Insert、Update、Delete
会使用。显示使用在语句后加for update
。打一个标记
,记录这个表是否被锁住了) => 如果没有这个锁,别的事务想锁住这张表的时候,就要去全表扫描是否有锁,效率太低。所以才会有意向锁的存在。锁的是索引,那么这个时候可能有人要问了:那如果我不创建索引呢?
索引的存在,我们上面讲过了,这里再回顾一下,有下面几种情况
完整的数据
)聚簇索引
rowid
的东西,根据这个id来创建 聚簇索引
所以一个表里面,必然会存在一个索引,所以锁当然总有索引拿来锁住了。
当要给一张你没有显示创建索引
的表,进行加锁查询
时,数据库其实是不知道到底要查哪些数据的,整张表可能都会用到。所以索性就锁整张表
LBCC
kann InnoDB das Problem des Phantomlesens unter keine Sperrung
-Bedingungen lösen. Anstatt wie Serialisierbar
zu sein, muss die Transaktion seriell
sein und ohne jegliche Parallelität
durchgeführt werden. 🎜🎜Lassen Sie uns eine detaillierte Untersuchung darüber durchführen, wie InnoDB lock
die Transaktionsisolationsstufe RR
implementiert. 🎜Tabellenebene
=> (IS, IX) handelt. 🎜🎜🎜Die oben genannten vier Sperren
sind Die grundlegendsten Arten von Sperren
🎜Four Locks
werden hier vorübergehend genannt: High-order locks
🎜im Freigabemodus sperren
nach der Anweisung. Standardmäßig werden exklusive Sperren Einfügen, Aktualisieren, Löschen
verwendet. Anzeige mit for update
nach der Anweisung. 🎜🎜Absichtssperren werden von der Datenbank selbst verwaltet. (Die Hauptfunktion besteht darin, die Tabelle
zu markieren und aufzuzeichnen, ob die Tabelle gesperrt ist.) => Wenn keine solche Sperre vorhanden ist und andere Transaktionen die Tabelle sperren möchten, müssen sie alle Tabellenscans durchführen Zu sehen, ob eine Sperre vorhanden ist, ist zu ineffizient. Aus diesem Grund gibt es Absichtssperren. 🎜🎜vollständige Daten
speichert). >) 🎜🎜Es gibt keinen Primärschlüssel, aber einen eindeutigen Schlüssel. Wenn keiner von beiden null ist, wird ein gruppierter Index
basierend auf diesem Schlüssel erstellt. 🎜🎜Wenn keiner der oben genannten Schlüssel vorhanden ist, Keine Sorge, innodb verwaltet es selbst. Es gibt etwas namens rowid
und ein clustered index
wird basierend auf dieser ID erstellt 🎜🎜🎜Es muss also einen Index geben in einer Tabelle, also gibt es natürlich immer einen Index, der die Sperre übernimmt. Kommen Sie und sperren Sie sie. 🎜🎜Wenn Sie eine gesperrte Abfrage
für eine Tabelle durchführen möchten, für die Sie nicht explizit einen Index
erstellt haben, weiß die Datenbank tatsächlich nicht, welche Daten überprüft werden sollen Tabelle Kann verwendet werden. Also einfach die gesamte Tabelle sperren
. 🎜Hilfsindex
hinzufügen, wählen Sie * aus, wobei name = 'xxx' für die Aktualisierung
und schließlich kehren Sie zur Tabelle zurück code>, um die Primärschlüsselinformationen zu überprüfen. Daher sperren wir zu diesem Zeitpunkt zusätzlich zum Sperren des <code>Hilfsindex
auch den Primärschlüsselindex
辅助索引
加写锁,比如select * from where name = ’xxx‘ for update
最后要回表
查主键上的信息,所以这个时候除了锁辅助索引
还要锁主键索引
首先上三个概念,有这么一组数据:主键是 1,3,6,9 在存储时候有如下:x 1 x 3 x x 6 x x x 9 x···
记录锁,锁的是每个记录,也就是 1,3,6,9
间隙锁,锁的是记录间隙,每个 x
,(-∞,1), (1,3), (3,6), (6,9), (9,+∞)
临锁,锁的是 (-∞,1], (1,3], (3,6], (6,9], (9,+∞] 左开右闭的区间
首先这三种锁都是 排它锁
, 并且 临键锁 = 记录锁 + 间隙锁
select * from xxx where id = 3 for update
时,产生记录锁select * from xxx where id = 5 for update
时,产生间隙锁 => 锁住了(3,6),这里要格外注意一点:间隙锁之间是不冲突的。select * from xxx where id = 5 for update
时,产生临键锁 => 锁住了(3,6], mysql默认使用临键锁,如果不满足 1 ,2 情况 则他的行锁的都是临键锁Record Lock 行锁
防止别的事务修改或删除,Gap Lock 间隙锁
防止别的事务新增,Gap Lock 和 Record Lock
结合形成的Next-Key锁
共同解决RR级别
在写数据时的幻读问题。show status like 'innodb_row_lock_%'
select * from information_schema.INNODB_TRX
能查看到当前正在运行和被锁住的事务show full processlist
= select * from information_schema.processlist
能查询出是 哪个用户
在哪台机器host的哪个端口上
连接哪个数据库
执行什么指令
的 状态与时间
编码层 -- 实现 AbstracRoutingDataSource => 框架层 -- 实现 Mybatis Plugin => 驱动层 -- Sharding-JDBC(配置多个数据源,根据自定义实现的策略对数据进行分库分表存储)核心流程,SQL解析=>执行优化=>SQL数据库路由=>SQL改变(比如分表,改表名)=>SQL执行=>结果归并) => 代理层 -- Mycat(将所有与数据库的连接独立出来。全部由Mycat连接,其他服务访问Mycat获取数据) => 服务层 -- 特殊的SQL版本
说到底我们学习这么多知识都是为了能更好使用MYSQL,那就让我们来实操一下,建立一个完整的优化体系
要想获得更好的查询性能,可以从这张查询执行过程
入手
添加连接池,避免每次都新建、销毁连接
Lassen Sie uns zunächst über drei Konzepte sprechen. Es gibt einen solchen Datensatz: Der Primärschlüssel ist 1 , 3, 6, 9
Beim Speichern ist es wie folgt: x 1 x 3 x x 6 x x x 9 x...
Lückensperre, sperrt die Datensatzlücke, jedes x
, (-∞,1), (1,3), (3,6), (6,9), (9,+∞ )
Beim Sperren sind die Sperren (-∞,1], (1,3], (3,6], (6,9], (9,+∞], die linksoffenen und rechtsgeschlossenen Intervalle
Zuallererst handelt es sich bei diesen drei Sperren um exklusive Sperren
und temporäre Tastensperren = Datensatzsperren + Lückensperren
* aus xxx mit ID = 3 für die Aktualisierung ausgewählt wird
, eine Datensatzsperre wird generiert🎜Wenn wähle * aus xxx, wobei die ID = 5 für die Aktualisierung ist
, wird eine Lückensperre generiert => (3,6) ist gesperrt Besondere Aufmerksamkeit hier: Die Lückensperre Es gibt keinen Konflikt zwischen 🎜Wenn wähle * aus xxx, wobei die ID = 5 für die Aktualisierung ist
, wird eine temporäre Tastensperre => , das standardmäßig von MySQL verwendet wird und die Bedingungen 1 und 2 nicht erfüllt sind, sind alle Zeilensperren temporäre Tastensperren Datensatzsperre Zeilensperre
Verhindert das Ändern oder Löschen anderer Transaktionen, Gap Lock Lückensperre
verhindert das Hinzufügen anderer Transaktionen, Gap Lock und Record Lock
werden zu einem Formular kombiniert a Next-Key lock
Arbeiten Sie zusammen, um das Problem des Phantomlesens beim Schreiben von Daten auf RR-Ebene
zu lösen select * from information_schema.INNODB_TRX
kann die aktuell laufenden und gesperrten Transaktionen anzeigen 🎜vollständige Prozessliste anzeigen
= select * from information_schema.processlist
kann abfragen, welcher Benutzer
sich an welchem Port auf welcher Maschine befindet host
Welche Datenbank soll verbunden werden
Welcher Befehl ausgeführt werden soll
s Status- und Zeit
Abfrageausführungsprozess
Erste Schritte folgen🎜1. Client-Verbindungspool🎜Fügen Sie einen Verbindungspool hinzu, um zu vermeiden, dass jedes Mal Verbindungen erstellt und zerstört werden
. Dann ist unser Verbindungspool nicht mehr vorhanden desto besser?
Interessierte Freunde können diesen Artikel lesen: Über die Poolgröße🎜🎜Ich fasse es grob zusammen:🎜CPU
tatsächlich Threads
ausführen kann. Da das Betriebssystem die Time-Slicing
-Technologie verwendet, gehen wir davon aus, dass ein CPU-Kern
mehrere Threads
ausführt. CPU
才能真正去执行线程
。而操作系统因为用时间分片
的技术,让我们以为一个CPU内核
执行了多个线程
。CPU
在某个时间段
只能执行一个线程
,所以无论我们怎么增加并发,CPU
还是只能在这个时间段里处理这么多数据。CPU
处理不了这么多数据,又怎么会变慢?因为时间分片
,当多个线程看起来在"同时执行"
,其实他们之间的上下文切换
十分耗时I/O
操作,这个时候,CPU
就可以把时间,分片给其他线程
,以提升处理效率和速度I/O
等待时间非常短,所以我们就不能添加过多连接数线程数 = ((核心数 * 2) + 有效磁盘数)
。比如一台 i7 4core 1hard disk
的机器,就是 4 * 2 + 1 = 9很多CPU计算和I/O的场景
比如:设置最大线程数等如果并发非常大,就不能让他们全打到数据库上,在客户端连接数据库查询时,添加如Redis
这种三方缓存
既然我们一个数据库承受不了巨大的并发,那为什么不多添加几台机器呢? 主从复制原理图
从图中我们不难看出、Mysql主从复制 读写分离
异步复制
的特性。
Binary Log
写入relay log
之后,slave
都会把最新读取到的Binary Log Position
记录到master info
上,下一次就直接从这个位置去取。上面这种异步
的主从复制,很明显的一个问题就是,更新不及时的问题。当写入一个数据后,马上有用户读取,读取的还是之前的数据,也就是存在着延时。
要解决延时的问题,就需要引入 事务
failover
动作,即主节点挂掉,选举从节点后,能快速自动避免数据丢失。对数据进行分类划分,分成不同表,减少对单一表造成过多锁操作
Aber tatsächlich kann die vorherige CPU
nur einen Thread
in einem bestimmten Zeitraum
ausführen, also egal, wie wir uns steigern Parallelität, CPU kann in diesem Zeitraum immer noch nur eine bestimmte Menge Daten verarbeiten.
CPU
dann langsamer werden, selbst wenn sie nicht so viele Daten verarbeiten kann? Aufgrund der Zeitaufteilung
ist der Kontextwechsel
zwischen ihnen tatsächlich sehr zeitaufwändig, wenn mehrere Threads scheinbar „gleichzeitig ausgeführt“
werden. code> li>🎜Sobald also die Anzahl der Threads die Anzahl der CPU-Kerne übersteigt, wird das System durch Erhöhen der Anzahl der Threads nur langsamer und nicht schneller. 🎜Natürlich ist dies nur der Hauptgrund. Die Festplatte wirkt sich auch auf die Geschwindigkeit aus und wirkt sich auch auf die Konfiguration unserer Verbindungsnummer aus. 🎜🎜Bei der von uns verwendeten mechanischen Festplatte müssen wir sie beispielsweise drehen, an einen bestimmten Ort adressieren und dann den I/O
-Vorgang ausführen CPU kann Zeit sparen und auf andere Threads
aufteilen, um die Verarbeitungseffizienz und -geschwindigkeit zu verbessern🎜Wenn Sie also eine mechanische Festplatte verwenden, können wir normalerweise weitere Verbindungen hinzufügen um eine hohe Parallelität aufrechtzuerhalten🎜Aber wenn Sie SSD verwenden, können wir nicht zu viele Verbindungen hinzufügen, da die I/O
-Wartezeit sehr kurz ist li>🎜Bestanden Im Allgemeinen müssen Sie dieser Formel folgen: Anzahl der Threads = ((Anzahl der Kerne * 2) + Anzahl der effektiven Festplatten)
. Zum Beispiel ist eine i7 4core 1hard disk
Maschine 4 * 2 + 1 = 9🎜Ich frage mich, ob Ihnen diese Formel bekannt ist. Sie gilt nicht nur für Datenbankverbindungen, sondern auch Anwendbar auf jedes Szenario mit viel CPU-Rechenleistung und E/A
. Zum Beispiel: Festlegen der maximalen Anzahl von Threads usw.Wenn die Parallelität sehr groß ist, können wir nicht zulassen, dass alle davon betroffen sind Wenn der Client zur Abfrage eine Verbindung zur Datenbank herstellt, fügen Sie einen Drittanbieter-Cache hinzu, z. B. Redis
🎜
Da eine unserer Datenbanken einer großen Parallelität nicht standhalten kann, warum nicht noch ein paar weitere Maschinen hinzufügen? Master-Slave-Replikationsschema🎜
🎜
Auf dem Bild können wir leicht erkennen, dass die MySQL-Master-Slave-Replikation Lese-Schreib-Trennung
ist > Asynchrone Replikation-Funktion. 🎜
Binary Log
in Relay Log
geschrieben wurde, schreibt Slave
die zuletzt gelesene Binary Log Position code> wird in <code>master info
aufgezeichnet und beim nächsten Mal direkt von dieser Position abgerufen.
Die obige asynchrone
Master-Slave-Replikation ist sehr Ein offensichtliches Problem besteht darin, dass Updates nicht rechtzeitig erfolgen. Wenn ein Datenelement geschrieben und sofort von einem Benutzer gelesen wird, handelt es sich bei den gelesenen Daten immer noch um die vorherigen Daten, was bedeutet, dass es zu einer Verzögerung kommt.
Um das Verzögerungsproblem zu lösen, ist es notwendig, eine vollständig synchrone Replikation einzuführen, die zuerst im Transaktionsmodus ausgeführt wird und dann alle Slave-Knoten schreiben Beim Schreiben der Daten wird ein erfolgreicher Schreibvorgang zurückgegeben, was sich stark auf die Schreibleistung auswirkt.🎜Halbsynchrone Replikation gilt als erfolgreich, solange eine Salbe zum Schreiben von Daten vorhanden ist. (Wenn eine halbsynchrone Replikation erforderlich ist, müssen sowohl der Master- als auch der Slave-Knoten die Plug-Ins semisync_mater.so und semisync_slave.so installieren.) 🎜GTID-Replikation (Global Transaction Identities), wenn die Master-Bibliothek parallel repliziert Die Slave-Bibliothek repliziert auch parallel und löst so das Problem des Masters. Durch die synchrone Replikationsverzögerung wird eine automatische Failover
-Aktion implementiert, d. h. wenn der Master-Knoten auflegt und der Slave-Knoten ausgewählt wird, werden Daten übertragen Verluste können schnell und automatisch vermieden werden.
Kategorisiert und unterteilt Daten in verschiedene Tabellen, um übermäßige Sperrvorgänge
zu reduzieren, die sich auf die Leistung einer einzelnen Tabelle auswirken🎜
mysqldumpslow /var/lib/mysql/mysql-slow.log
verwenden. Es gibt viele Plug-Ins, die eine elegantere Analyse ermöglichen, daher werde ich hier nicht auf Details eingehen. explain
seinleft/right join
导致性能低下left/right join
会直接指定驱动表,在MYSQL中,默认使用Nest loop join
进行表关联(即通过驱动表
的结果集作为循环基础数据,然后通过此集合中的每一条数据筛选下一个关联表的数据,最后合并结果,得出我们常说的临时表
)。驱动表
的数据是 百万千万
级别的,可想而知这联表查询得有多慢。但是反过来,如果以小表
作为驱动表
,借助千万级表
的索引
查询就能变得很快。驱动表
,那么请交给优化器来决定,比如:select xxx from table1, table2, table3 where ···
,优化器会将查询记录行数少的表作为驱动表。驱动表
,那么请拿好Explain
武器,在Explain
的结果中,第一个就是基础驱动表
表
排序也是有很大的性能差异,我们尽量对驱动表
进行排序,而不要对临时表,也就是合并后的结果集
进行排序。即执行计划中出现了 using temporary
,就需要进行优化。普通查询
和复杂查询
(联合查询、子查询等)SIMPLE
,查询不包含子查询或者UNIONPRIMARY
,如果查询包含复杂查询
的子结构,那么就需要用到主键查询SUBQUERY
,在select
或者where
中包含 子查询
DERIVED
,在from
中包含子查询UNION RESULT
,从union
表查询子查询越来越快
const或者system
常量级别的扫描,查询表最快的一种,system是const的一种特殊情况(表中只有一条数据)eq_ref
唯一性索引扫描ref
非唯一性索引扫描range
索引的范围扫描,比如 between、等范围查询index
(index full)扫描全部索引树ALL
扫描全表NULL
,不需要访问表或者索引不一定使用
哪一个索引被真正使用
到了。如果没有则为NULL索引(key)
一起被使用only index
信息只需要从索引中查出,可能用到了覆盖索引,查询非常快using where
如果查询没有使用索引,这里会在server
层过滤再使用 where
来过滤结果集impossible where
啥也没查出来using filesort
,只要没有通过索引来排序,而是使用了其他排序的方式就是 filesortusing temporary
(需要通过临时表来对结果集进行暂时存储,然后再进行计算。)一般来说这种情况都是进行了DISTINCT、排序、分组
using index condition
索引下推,上文讲过,就是把server层
这个过滤操作下推到引擎层
插入与查询
比较多的时候,可以使用MyISAM
存储引擎memory
插入、更新、查询
等并发数很多时,可以使用InnoDB
Antwort MYSQL-Optimierung auf fünf Ebenen, von oben nach unten
Darüber hinaus ist die Datenabfrage langsam, daher sollten wir die Datenbank nicht einfach blind „optimieren“, sondern sie auf der Ebene der Geschäftsanwendung analysieren. Zum Beispiel Daten zwischenspeichern, Anfragen einschränken usw.
Bis zum nächsten Artikel
Verwandte kostenlose Lernempfehlungen: MySQL-Video-Tutorial
Das obige ist der detaillierte Inhalt vonEin Artikel, der Ihnen hilft, die zugrunde liegenden Prinzipien von MYSQL zu verstehen. Für weitere Informationen folgen Sie bitte anderen verwandten Artikeln auf der PHP chinesischen Website!