suchen
HeimDatenbankMySQL-Tutorial 结合业务特性巧用临时表

拿到某开发sql如下SELECTp.products_idFROMproductsASpJOINproducts_to_categoriesASpcUSING(products_id)JOINcategoriesAScUSING(categories_id)JOINproducts_r

拿到某开发sql如下

SELECT p.products_id FROM products AS p JOIN products_to_categories AS pc USING(products_id) JOIN categories AS c USING(categories_id) JOIN products_realtime_quantity AS prq ON prq.sku_or_poa = p.products_model WHERE products_status =1 AND categories_status =1 AND prq.msg != 'Temporary out stock.' ORDER BY p.products_date_added DESC LIMIT 10

一般看到这种sql,在where中只有status类似的字段(可选择性非常低,数据两极分化非常明显)而且需要order by的语句,我们就应该想到使用force index(order_by_column)来进行优化.

explian

+----+-------------+-------+--------+------------------------------+-------------------+---------+--------------------------------+--------+----------------------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+--------+------------------------------+-------------------+---------+--------------------------------+--------+----------------------------------------------+ | 1 | SIMPLE | pc | index | PRIMARY,categories_id | PRIMARY | 8 | NULL | 1009510 | Using index; Using temporary; Using filesort | | 1 | SIMPLE | p | eq_ref | PRIMARY,products_model | PRIMARY | 4 | banggood_work.pc.products_id | 1 | Using where | | 1 | SIMPLE | c | eq_ref | PRIMARY | PRIMARY | 4 | banggood_work.pc.categories_id | 1 | Using where | | 1 | SIMPLE | prq | ref | ix_prg_sku_or_poa,ix_prq_msg | ix_prg_sku_or_poa | 152 | banggood_work.p.products_model | 1 | Using where | +----+-------------+-------+--------+------------------------------+-------------------+---------+--------------------------------+--------+----------------------------------------------+

发现mysql优化器选择了pc表的主键,虽然使用了索引,但是进行了全索引扫描,效果还是不理想!

强制使用force index后,explain

EXPLAIN -> SELECT p.products_id FROM products AS p FORCE INDEX(products_date_added) -> JOIN products_to_categories AS pc USING(products_id) -> JOIN categories AS c USING(categories_id) -> JOIN products_realtime_quantity AS prq ON prq.sku_or_poa = p.products_model -> WHERE products_status =1 AND categories_status =1 AND prq.msg != 'Temporary out stock.' -> ORDER BY p.products_date_added DESC LIMIT 10 -> ; +----+-------------+-------+--------+------------------------------+---------------------+---------+--------------------------------+------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+--------+------------------------------+---------------------+---------+--------------------------------+------+-------------+ | 1 | SIMPLE | p | index | NULL | products_date_added | 8 | NULL | 1 | Using where | | 1 | SIMPLE | prq | ref | ix_prg_sku_or_poa,ix_prq_msg | ix_prg_sku_or_poa | 152 | banggood_work.p.products_model | 1 | Using where | | 1 | SIMPLE | pc | ref | PRIMARY,categories_id | PRIMARY | 4 | banggood_work.p.products_id | 1009 | Using index | | 1 | SIMPLE | c | eq_ref | PRIMARY | PRIMARY | 4 | banggood_work.pc.categories_id | 1 | Using where | +----+-------------+-------+--------+------------------------------+---------------------+---------+--------------------------------+------+-------------+

发现索引已经变成productsw_date_added,而执行时间,前者是2s,后者是0.003s.


这是我们一贯的优化方法,但是我们可以根据sql语句的特性和业务特性,结合临时表进行一些淫邪的优化,虽然并不通用,但是可以开阔sql优化者的思维。

我们可以看到这条语句是需要根据产品添加时间拿取符合(products_status =1 AND categories_status =1   AND prq.msg != 'Temporary out stock.')条件的10个最新上架产品.而我们知道,最新上架的产品一般状态都是不可能马上下架,而且对应的类别id也是可用,而且库存也是充足的(要不然何必上架),这个特性站到了99.9%以上.所以,我们利用这个特性,,先从产品表中找出不带任何条件的200个产品,放到临时表,然后再用临时表结果集,和拿取条件进行匹配,取出最新的10条.

(200条是一个参考值,根据各自的逻辑特性来取)

sql如下

SELECT DISTINCT p.products_id FROM (SELECT products_id,products_model,products_status,products_date_added FROM products ORDER BY products_date_added DESC LIMIT 200 ) AS p JOIN products_to_categories AS pc USING(products_id) JOIN categories AS c USING(categories_id) JOIN products_realtime_quantity AS prq ON prq.sku_or_poa = p.products_model WHERE products_status =1 AND categories_status =1 AND prq.msg != 'Temporary out stock.' ORDER BY products_date_added DESC LIMIT 10;

explain后发现

| 1 | PRIMARY | | ALL | NULL | NULL | NULL | NULL | 200 | Using where; Using temporary; Using filesort | | 1 | PRIMARY | prq | ref | ix_prg_sku_or_poa,ix_prq_msg | ix_prg_sku_or_poa | 152 | p.products_model | 1 | Using where; Distinct | | 1 | PRIMARY | pc | ref | PRIMARY,categories_id | PRIMARY | 4 | p.products_id | 1009 | Using index; Distinct | | 1 | PRIMARY | c | eq_ref | PRIMARY | PRIMARY | 4 | banggood_work.pc.categories_id | 1 | Using where; Distinct | | 2 | DERIVED | products | index | NULL | products_date_added | 8 | NULL | 200 | |

我们可以看到,已经利用products表中的products_date_added字段排序取出200条,整个sql语句变成一个只有200行的临时表驱动查询了,性能相对于原来的语句,提高上百倍!

执行时间大约是0.02s(可能比force index略慢)。

本文出自 “原下” 博客,请务必保留此出处

Stellungnahme
Der Inhalt dieses Artikels wird freiwillig von Internetnutzern beigesteuert und das Urheberrecht liegt beim ursprünglichen Autor. Diese Website übernimmt keine entsprechende rechtliche Verantwortung. Wenn Sie Inhalte finden, bei denen der Verdacht eines Plagiats oder einer Rechtsverletzung besteht, wenden Sie sich bitte an admin@php.cn
Wie geht MySQL mit der Datenreplikation um?Wie geht MySQL mit der Datenreplikation um?Apr 28, 2025 am 12:25 AM

MySQL verarbeitet die Datenreplikation durch drei Modi: Asynchron, halbsynchron und Gruppenreplikation. 1) Die asynchrone Replikationsleistung ist hoch, die Daten können jedoch verloren gehen. 2) Die halbsynchrone Replikation verbessert die Datensicherheit, erhöht jedoch die Latenz. 3) Die Gruppenreplikation unterstützt die Replikation und das Failover mit mehreren Master, die für Anforderungen an hoher Verfügbarkeit geeignet sind.

Wie können Sie die Erklärungserklärung verwenden, um die Abfrageleistung zu analysieren?Wie können Sie die Erklärungserklärung verwenden, um die Abfrageleistung zu analysieren?Apr 28, 2025 am 12:24 AM

Die Erklärungserklärung kann verwendet werden, um die SQL -Abfrageleistung zu analysieren und zu verbessern. 1. Führen Sie die Erklärung zur Erklärung aus, um den Abfrageplan anzuzeigen. 2. Analysieren Sie die Ausgabeergebnisse, achten Sie auf den Zugriffstyp, die Indexverwendung und den Verbindung der Reihenfolge. 3. Erstellen oder passen Sie die Indizes anhand der Analyseergebnisse an, optimieren Sie die Join -Operationen und vermeiden Sie die volle Tabellen -Scan, um die Effizienz der Abfrage zu verbessern.

Wie können Sie eine MySQL -Datenbank wiederherstellen und wiederherstellen?Wie können Sie eine MySQL -Datenbank wiederherstellen und wiederherstellen?Apr 28, 2025 am 12:23 AM

Die Verwendung von MySQldump für logische Sicherungen und MySQLenterPriseBackups für Hot Backup ist effektive Möglichkeiten, um MySQL -Datenbanken zu sichern. 1. Verwenden Sie MySQldump, um die Datenbank zu sichern: mysqldump-uroot-pmydatabase> mydatabase_backup.sql. 2. Verwenden Sie MySQLenterPriseBackup für Hot Backup: Mysqlbackup-User = Root-Password = Passwort-Backup-Dir =/path/to/backupbackup. Verwenden Sie bei der Wiederherstellung das entsprechende Leben

Was sind einige häufige Ursachen für langsame Abfragen in MySQL?Was sind einige häufige Ursachen für langsame Abfragen in MySQL?Apr 28, 2025 am 12:18 AM

Die Hauptgründe für die langsame MySQL -Abfrage sind fehlende oder unsachgemäße Verwendung von Indizes, Komplexität der Abfrage, übermäßiges Datenvolumen und unzureichende Hardware -Ressourcen. Zu den Optimierungsvorschlägen gehören: 1. Erstellen Sie geeignete Indizes; 2. Optimieren Sie Abfrageanweisungen; 3.. Verwenden Sie die Tabellenpartitionierungstechnologie; 4.. Angemessene Hardware upgraden.

Was sind Ansichten in MySQL?Was sind Ansichten in MySQL?Apr 28, 2025 am 12:04 AM

MySQL View ist eine virtuelle Tabelle, die auf SQL -Abfrageergebnissen basiert und keine Daten speichert. 1) Ansichten vereinfachen komplexe Abfragen, 2) die Datensicherheit verbessern und 3) die Datenkonsistenz beibehalten. Ansichten sind Abfragen in Datenbanken, die wie Tabellen verwendet werden können, aber Daten werden dynamisch generiert.

Was sind die Unterschiede in der Syntax zwischen MySQL und anderen SQL -Dialekten?Was sind die Unterschiede in der Syntax zwischen MySQL und anderen SQL -Dialekten?Apr 27, 2025 am 12:26 AM

MySQLDIFFERSFROMOTHERQLDIALCTSINSYNTAXFORLIMIT, Auto-Increment, StringComparison, Unterabfragen und Performanceanalyse.1) Mysqluse Slimit, whileqlServerusSestopandorakelSrownum.2) Mysql'Sauto_incrementContrastswithpostgresql'Sserialandoracle'ssequencandt

Was ist MySQL Partitioning?Was ist MySQL Partitioning?Apr 27, 2025 am 12:23 AM

Die MySQL -Partitionierung verbessert die Leistung und vereinfacht die Wartung. 1) Teilen Sie große Tabellen nach bestimmten Kriterien (z. B. Datumsbereichen) in kleine Stücke, 2) Daten in unabhängige Dateien physikalisch unterteilen, 3) MySQL kann sich auf verwandte Partitionen konzentrieren, wenn Sie abfragen, 4) Abfragoptimierer kann nicht verwandte Partitionen überspringen, 5) Die Auswahl der richtigen Partitionsstrategie und die regelmäßige Wartung des Schlüssels ist der Schlüssel.

Wie gewähren und widerrufen Sie Privilegien in MySQL?Wie gewähren und widerrufen Sie Privilegien in MySQL?Apr 27, 2025 am 12:21 AM

Wie kann man Berechtigungen in MySQL erteilen und widerrufen? 1. Verwenden Sie die Stipendienerklärung, um Berechtigungen wie GrantAllPrivileGesAndatabase_Name.to'username'@'host '; 2. Verwenden Sie die Revoke -Erklärung, um Berechtigungen wie RevokeAllPrivileGeDatabase_Name.From'username'@'host 'zu widerrufen, um eine rechtzeitige Kommunikation von Genehmigungsänderungen zu gewährleisten.

See all articles

Heiße KI -Werkzeuge

Undresser.AI Undress

Undresser.AI Undress

KI-gestützte App zum Erstellen realistischer Aktfotos

AI Clothes Remover

AI Clothes Remover

Online-KI-Tool zum Entfernen von Kleidung aus Fotos.

Undress AI Tool

Undress AI Tool

Ausziehbilder kostenlos

Clothoff.io

Clothoff.io

KI-Kleiderentferner

Video Face Swap

Video Face Swap

Tauschen Sie Gesichter in jedem Video mühelos mit unserem völlig kostenlosen KI-Gesichtstausch-Tool aus!

Heiße Werkzeuge

SublimeText3 Mac-Version

SublimeText3 Mac-Version

Codebearbeitungssoftware auf Gottesniveau (SublimeText3)

mPDF

mPDF

mPDF ist eine PHP-Bibliothek, die PDF-Dateien aus UTF-8-codiertem HTML generieren kann. Der ursprüngliche Autor, Ian Back, hat mPDF geschrieben, um PDF-Dateien „on the fly“ von seiner Website auszugeben und verschiedene Sprachen zu verarbeiten. Es ist langsamer und erzeugt bei der Verwendung von Unicode-Schriftarten größere Dateien als Originalskripte wie HTML2FPDF, unterstützt aber CSS-Stile usw. und verfügt über viele Verbesserungen. Unterstützt fast alle Sprachen, einschließlich RTL (Arabisch und Hebräisch) und CJK (Chinesisch, Japanisch und Koreanisch). Unterstützt verschachtelte Elemente auf Blockebene (wie P, DIV),

DVWA

DVWA

Damn Vulnerable Web App (DVWA) ist eine PHP/MySQL-Webanwendung, die sehr anfällig ist. Seine Hauptziele bestehen darin, Sicherheitsexperten dabei zu helfen, ihre Fähigkeiten und Tools in einem rechtlichen Umfeld zu testen, Webentwicklern dabei zu helfen, den Prozess der Sicherung von Webanwendungen besser zu verstehen, und Lehrern/Schülern dabei zu helfen, in einer Unterrichtsumgebung Webanwendungen zu lehren/lernen Sicherheit. Das Ziel von DVWA besteht darin, einige der häufigsten Web-Schwachstellen über eine einfache und unkomplizierte Benutzeroberfläche mit unterschiedlichen Schwierigkeitsgraden zu üben. Bitte beachten Sie, dass diese Software

SAP NetWeaver Server-Adapter für Eclipse

SAP NetWeaver Server-Adapter für Eclipse

Integrieren Sie Eclipse mit dem SAP NetWeaver-Anwendungsserver.

VSCode Windows 64-Bit-Download

VSCode Windows 64-Bit-Download

Ein kostenloser und leistungsstarker IDE-Editor von Microsoft