Rechercher dans le manuel MySQL
8.6.1 Optimizing MyISAM Queries
Some general tips for speeding up queries on
MyISAM
tables:
To help MySQL better optimize queries, use
ANALYZE TABLE
or run myisamchk --analyze on a table after it has been loaded with data. This updates a value for each index part that indicates the average number of rows that have the same value. (For unique indexes, this is always 1.) MySQL uses this to decide which index to choose when you join two tables based on a nonconstant expression. You can check the result from the table analysis by usingSHOW INDEX FROM
and examining thetbl_name
Cardinality
value. myisamchk --description --verbose shows index distribution information.To sort an index and data according to an index, use myisamchk --sort-index --sort-records=1 (assuming that you want to sort on index 1). This is a good way to make queries faster if you have a unique index from which you want to read all rows in order according to the index. The first time you sort a large table this way, it may take a long time.
Try to avoid complex
SELECT
queries onMyISAM
tables that are updated frequently, to avoid problems with table locking that occur due to contention between readers and writers.MyISAM
supports concurrent inserts: If a table has no free blocks in the middle of the data file, you canINSERT
new rows into it at the same time that other threads are reading from the table. If it is important to be able to do this, consider using the table in ways that avoid deleting rows. Another possibility is to runOPTIMIZE TABLE
to defragment the table after you have deleted a lot of rows from it. This behavior is altered by setting theconcurrent_insert
variable. You can force new rows to be appended (and therefore permit concurrent inserts), even in tables that have deleted rows. See Section 8.11.3, “Concurrent Inserts”.For
MyISAM
tables that change frequently, try to avoid all variable-length columns (VARCHAR
,BLOB
, andTEXT
). The table uses dynamic row format if it includes even a single variable-length column. See Chapter 16, Alternative Storage Engines.It is normally not useful to split a table into different tables just because the rows become large. In accessing a row, the biggest performance hit is the disk seek needed to find the first byte of the row. After finding the data, most modern disks can read the entire row fast enough for most applications. The only cases where splitting up a table makes an appreciable difference is if it is a
MyISAM
table using dynamic row format that you can change to a fixed row size, or if you very often need to scan the table but do not need most of the columns. See Chapter 16, Alternative Storage Engines.Use
ALTER TABLE ... ORDER BY
if you usually retrieve rows inexpr1
,expr2
, ...
order. By using this option after extensive changes to the table, you may be able to get higher performance.expr1
,expr2
, ...If you often need to calculate results such as counts based on information from a lot of rows, it may be preferable to introduce a new table and update the counter in real time. An update of the following form is very fast:
This is very important when you use MySQL storage engines such as
MyISAM
that has only table-level locking (multiple readers with single writers). This also gives better performance with most database systems, because the row locking manager in this case has less to do.Use
OPTIMIZE TABLE
periodically to avoid fragmentation with dynamic-formatMyISAM
tables. See Section 16.2.3, “MyISAM Table Storage Formats”.Declaring a
MyISAM
table with theDELAY_KEY_WRITE=1
table option makes index updates faster because they are not flushed to disk until the table is closed. The downside is that if something kills the server while such a table is open, you must ensure that the table is okay by running the server with the--myisam-recover-options
option, or by running myisamchk before restarting the server. (However, even in this case, you should not lose anything by usingDELAY_KEY_WRITE
, because the key information can always be generated from the data rows.)Strings are automatically prefix- and end-space compressed in
MyISAM
indexes. See Section 13.1.15, “CREATE INDEX Syntax”.You can increase performance by caching queries or answers in your application and then executing many inserts or updates together. Locking the table during this operation ensures that the index cache is only flushed once after all updates.
Deutsche Übersetzung
Sie haben gebeten, diese Seite auf Deutsch zu besuchen. Momentan ist nur die Oberfläche übersetzt, aber noch nicht der gesamte Inhalt.Wenn Sie mir bei Übersetzungen helfen wollen, ist Ihr Beitrag willkommen. Alles, was Sie tun müssen, ist, sich auf der Website zu registrieren und mir eine Nachricht zu schicken, in der Sie gebeten werden, Sie der Gruppe der Übersetzer hinzuzufügen, die Ihnen die Möglichkeit gibt, die gewünschten Seiten zu übersetzen. Ein Link am Ende jeder übersetzten Seite zeigt an, dass Sie der Übersetzer sind und einen Link zu Ihrem Profil haben.
Vielen Dank im Voraus.
Dokument erstellt 26/06/2006, zuletzt geändert 26/10/2018
Quelle des gedruckten Dokuments:https://www.gaudry.be/de/mysql-rf-optimizing-queries-myisam.html
Die Infobro ist eine persönliche Seite, deren Inhalt in meiner alleinigen Verantwortung liegt. Der Text ist unter der CreativeCommons-Lizenz (BY-NC-SA) verfügbar. Weitere Informationen auf die Nutzungsbedingungen und dem Autor.
Referenzen
Diese Verweise und Links verweisen auf Dokumente, die während des Schreibens dieser Seite konsultiert wurden, oder die zusätzliche Informationen liefern können, aber die Autoren dieser Quellen können nicht für den Inhalt dieser Seite verantwortlich gemacht werden.
Der Autor Diese Website ist allein dafür verantwortlich, wie die verschiedenen Konzepte und Freiheiten, die mit den Nachschlagewerken gemacht werden, hier dargestellt werden. Denken Sie daran, dass Sie mehrere Quellinformationen austauschen müssen, um das Risiko von Fehlern zu reduzieren.