suchen
HeimDatenbankMySQL-TutorialSQL优化,百万级2张表关联,从40分钟到3秒的历程_MySQL

bitsCN.com

SQL优化,百万级2张表关联,从40分钟到3秒的历程

 

[sql] 

表结构如下:  

[sql] 

CREATE TABLE `deviceback` (  

  `id` int(11) NOT NULL AUTO_INCREMENT,  

  `imei` varchar(100) NOT NULL COMMENT '手机唯一标识',  

  `mid` varchar(50) DEFAULT NULL,  

  `mac` varchar(100) DEFAULT NULL,  

  `APNType` varchar(100) DEFAULT NULL,  

  `status` int(11) DEFAULT '0',  

  `ip` varchar(100) DEFAULT NULL,  

  `sn` varchar(100) DEFAULT NULL COMMENT '系列号',  

  `oem` varchar(100) DEFAULT NULL COMMENT '厂商',  

  `product` varchar(100) DEFAULT NULL COMMENT '产品',  

  `region` varchar(100) DEFAULT NULL COMMENT '区域',  

  `operator` varchar(100) DEFAULT NULL COMMENT '运营商',  

  `sim` varchar(100) DEFAULT NULL COMMENT 'sim卡号',  

  `push_time` timestamp NULL DEFAULT NULL COMMENT '第一次登陆时间',  

  `origin_version` varchar(100) DEFAULT NULL COMMENT '原始版本',  

  `province` varchar(100) DEFAULT NULL COMMENT '省份',  

  `provinceCode` varchar(100) DEFAULT NULL COMMENT '省份code',  

  `city` varchar(50) DEFAULT NULL COMMENT '城市',  

  `cityCode` varchar(50) DEFAULT NULL COMMENT '城市code',  

  `brands` varchar(50) DEFAULT '0',  

  `version` varchar(100) DEFAULT '0' COMMENT '客户端版本号',  

  `last_checktime` timestamp NULL DEFAULT NULL COMMENT '最后一次登录时间',  

  PRIMARY KEY (`id`),  

  FULLTEXT KEY `NewIndex1` (`imei`),  

  FULLTEXT KEY `NewIndex2` (`mid`),  

  FULLTEXT KEY `NewIndex3` (`product`),  

  FULLTEXT KEY `NewIndex4` (`brands`)  

) ENGINE=MyISAM AUTO_INCREMENT=6832460 DEFAULT CHARSET=utf8;  

[sql] 

CREATE TABLE `20130602_AppLog` (  

  `id` int(11) NOT NULL AUTO_INCREMENT,  

  `imei` varchar(100) DEFAULT NULL,  

  `mid` varchar(50) NOT NULL DEFAULT '',  

  `status` char(1) NOT NULL DEFAULT '0',  

  `mac` varchar(100) DEFAULT 'NULL',  

  `sn` varchar(100) DEFAULT NULL,  

  `sim` varchar(100) DEFAULT NULL,  

  `coperator` varchar(100) DEFAULT NULL,  

  `version` varchar(100) DEFAULT NULL,  

  `logintime` datetime DEFAULT NULL,  

  `ip` varchar(20) DEFAULT NULL,  

  `origin_version` varchar(100) DEFAULT NULL,  

  `now_version` varchar(100) DEFAULT NULL,  

  `APNType` varchar(20) DEFAULT NULL,  

  `oem` varchar(20) DEFAULT NULL,  

  `product` varchar(100) DEFAULT NULL,  

  `region` varchar(100) DEFAULT NULL,  

  `operator` varchar(100) DEFAULT NULL,  

  PRIMARY KEY (`id`),  

  FULLTEXT KEY `NewIndex1` (`imei`),  

  FULLTEXT KEY `NewIndex2` (`mid`)  

) ENGINE=MyISAM AUTO_INCREMENT=3123866 DEFAULT CHARSET=utf8;  

 

SQL如下:

 

 

[sql] 

SELECT *  

FROM   deviceback d,20130602_AppLog g  

WHERE d.mid=g.mid  

      AND d.imei=g.imei  

      AND d.mac=g.mac  

      AND d.brands=0  

      AND g.coperator '' limit 20;  

 

explain

结果:

 

SQL优化,百万级2张表关联,从40分钟到3秒的历程_MySQL

 

从这里可以看到ALL以及key为NULL,就是全表扫描,没有走索引,索引失效了!

 

1 然后我建议建上加上联合索引,试试看效果如何:

 

SQL优化,百万级2张表关联,从40分钟到3秒的历程_MySQL

 

2 效果还是比较慢的,看来得换种办法了,检查所有关联字段,将为null的数据改成''。还是没有效果。

 

3  不查*了,直接count(*)  看看结果集大小,结果还是卡住了,短时间内没有出查询结果。

 

4,到这里我猜测估计是数据的构成问题,那就一个个条件去掉去尝试了。

SELECT *

FROM   deviceback d,20130602_AppLog g

WHERE d.mid=g.mid

      AND d.imei=g.imei

      AND d.mac=g.mac

      AND d.brands=0 limit 20;

去掉 条件试试看,God,结果是37秒就出来了,好,大概问题找到了,出在这条判断语句里面,也就是

AND g.coperator '' 这个影响还蛮大的。

 

那就先在20130602_AppLog 表的coperator字段单独建索引试试看,然后查下 20130602_AppLog里面 g.coperator ''的有多少?

结果是比较令人欣慰的,单独查询,一秒不到出来结果了,6W多条纪录。

 SELECT COUNT(1) 

       FROM 20130602_AppLog gg

       WHERE gg.coperator ''

63987

 

然后再试试整个sql,看需要多长时间。

先看下explain结果:

 

SQL优化,百万级2张表关联,从40分钟到3秒的历程_MySQL

 

SELECT *

FROM   deviceback d,20130602_AppLog g

WHERE d.mid=g.mid

      AND d.imei=g.imei

      AND d.mac=g.mac

      AND d.brands=0

      AND g.coperator '' limit 20;

Great,不到3秒就出来结果了。

 

5,limit 20是OK了,我还想试试不限制limit的话,要多久可以查询出来结果。

直接执行

SELECT *

FROM   deviceback d,20130602_AppLog g

WHERE d.mid=g.mid

      AND d.imei=g.imei

      AND d.mac=g.mac

      AND d.brands=0

      AND g.coperator '' ;

卡住了,很久都没有出来结果,在想是否是数据的问题?切换下where条件后面的字段顺序试试看。

 

SELECT d.mid,g.mac,

FROM   deviceback d,20130602_AppLog g

WHERE 

       d.imei=g.imei

      AND d.mac=g.mac

      AND d.mid=g.mid

      AND d.brands=0

      AND g.coperator '' ;

 OK,27秒出来了,AND d.mid=g.mid这个之前在第一条,现在放在后面,原因是手机唯一标示这个字段有空值。

God,最讨厌关键业务字段null值了,给我们的优化工作带来巨大的烦恼。

 

切记:大家以后千万要记住关键业务字段不能允许录入null值。

bitsCN.com
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 wirkt sich die MySQL -Kardinalität auf die Abfrageleistung aus?Wie wirkt sich die MySQL -Kardinalität auf die Abfrageleistung aus?Apr 14, 2025 am 12:18 AM

Die MySQL -Idium -Kardinalität hat einen signifikanten Einfluss auf die Abfrageleistung: 1. Hoher Kardinalitätsindex kann den Datenbereich effektiver einschränken und die Effizienz der Abfrage verbessern. 2. Niedriger Kardinalitätsindex kann zu einem vollständigen Tischscannen führen und die Abfrageleistung verringern. 3. Im gemeinsamen Index sollten hohe Kardinalitätssequenzen vorne platziert werden, um die Abfrage zu optimieren.

MySQL: Ressourcen und Tutorials für neue BenutzerMySQL: Ressourcen und Tutorials für neue BenutzerApr 14, 2025 am 12:16 AM

Der MySQL -Lernpfad umfasst Grundkenntnisse, Kernkonzepte, Verwendungsbeispiele und Optimierungstechniken. 1) Verstehen Sie grundlegende Konzepte wie Tabellen, Zeilen, Spalten und SQL -Abfragen. 2) Lernen Sie die Definition, die Arbeitsprinzipien und die Vorteile von MySQL kennen. 3) Master grundlegende CRUD -Operationen und fortgeschrittene Nutzung wie Indizes und gespeicherte Verfahren. 4) KON -Debugging- und Leistungsoptimierungsvorschläge, wie z. B. rationale Verwendung von Indizes und Optimierungsabfragen. In diesen Schritten haben Sie einen vollen Verständnis für die Verwendung und Optimierung von MySQL.

Reale MySQL: Beispiele und AnwendungsfälleReale MySQL: Beispiele und AnwendungsfälleApr 14, 2025 am 12:15 AM

Die realen Anwendungen von MySQL umfassen grundlegende Datenbankdesign und komplexe Abfrageoptimierung. 1) Grundnutzung: Wird zum Speichern und Verwalten von Benutzerdaten verwendet, z. B. das Einfügen, Abfragen, Aktualisieren und Löschen von Benutzerinformationen. 2) Fortgeschrittene Nutzung: Verwandte komplexe Geschäftslogik wie Auftrags- und Bestandsverwaltung von E-Commerce-Plattformen. 3) Leistungsoptimierung: Verbesserung der Leistung durch rationale Verwendung von Indizes, Partitionstabellen und Abfrage -Caches.

SQL -Befehle in MySQL: Praktische BeispieleSQL -Befehle in MySQL: Praktische BeispieleApr 14, 2025 am 12:09 AM

SQL -Befehle in MySQL können in Kategorien wie DDL, DML, DQL und DCL unterteilt werden und werden verwendet, um Datenbanken und Tabellen zu erstellen, zu ändern, zu löschen, Daten einfügen, aktualisieren, Daten löschen und komplexe Abfragebetriebe durchführen. 1. Die grundlegende Verwendung umfasst die Erstellungstabelle erstellbar, InsertInto -Daten einfügen und Abfragedaten auswählen. 2. Die erweiterte Verwendung umfasst die Zusammenarbeit mit Tabellenverbindungen, Unterabfragen und GroupBy für die Datenaggregation. 3.. Häufige Fehler wie Syntaxfehler, Datentyp -Nichtübereinstimmung und Berechtigungsprobleme können durch Syntaxprüfung, Datentypkonvertierung und Berechtigungsmanagement debuggen. 4. Vorschläge zur Leistungsoptimierung umfassen die Verwendung von Indizes, die Vermeidung vollständiger Tabellenscanning, Optimierung von Join -Operationen und Verwendung von Transaktionen, um die Datenkonsistenz sicherzustellen.

Wie geht InnoDB mit der Einhaltung der Säure um?Wie geht InnoDB mit der Einhaltung der Säure um?Apr 14, 2025 am 12:03 AM

InnoDB erreicht Atomizität durch Ungewöhnung, Konsistenz und Isolation durch Verriegelungsmechanismus und MVCC sowie Persistenz durch Redolog. 1) Atomizität: Verwenden Sie Unolog, um die Originaldaten aufzuzeichnen, um sicherzustellen, dass die Transaktion zurückgerollt werden kann. 2) Konsistenz: Stellen Sie die Datenkonsistenz durch Verriegelung auf Zeilenebene und MVCC sicher. 3) Isolierung: Unterstützt mehrere Isolationsniveaus und wird standardmäßig WiederholungSead verwendet. 4) Persistenz: Verwenden Sie Redolog, um Modifikationen aufzuzeichnen, um sicherzustellen, dass die Daten für lange Zeit gespeichert werden.

Mysqls Platz: Datenbanken und ProgrammierungMysqls Platz: Datenbanken und ProgrammierungApr 13, 2025 am 12:18 AM

Die Position von MySQL in Datenbanken und Programmierung ist sehr wichtig. Es handelt sich um ein Open -Source -Verwaltungssystem für relationale Datenbankverwaltung, das in verschiedenen Anwendungsszenarien häufig verwendet wird. 1) MySQL bietet effiziente Datenspeicher-, Organisations- und Abruffunktionen und unterstützt Systeme für Web-, Mobil- und Unternehmensebene. 2) Es verwendet eine Client-Server-Architektur, unterstützt mehrere Speichermotoren und Indexoptimierung. 3) Zu den grundlegenden Verwendungen gehören das Erstellen von Tabellen und das Einfügen von Daten, und erweiterte Verwendungen beinhalten Multi-Table-Verknüpfungen und komplexe Abfragen. 4) Häufig gestellte Fragen wie SQL -Syntaxfehler und Leistungsprobleme können durch den Befehl erklären und langsam abfragen. 5) Die Leistungsoptimierungsmethoden umfassen die rationale Verwendung von Indizes, eine optimierte Abfrage und die Verwendung von Caches. Zu den Best Practices gehört die Verwendung von Transaktionen und vorbereiteten Staten

MySQL: Von kleinen Unternehmen bis zu großen UnternehmenMySQL: Von kleinen Unternehmen bis zu großen UnternehmenApr 13, 2025 am 12:17 AM

MySQL ist für kleine und große Unternehmen geeignet. 1) Kleinunternehmen können MySQL für das grundlegende Datenmanagement verwenden, z. B. das Speichern von Kundeninformationen. 2) Große Unternehmen können MySQL verwenden, um massive Daten und komplexe Geschäftslogik zu verarbeiten, um die Abfrageleistung und die Transaktionsverarbeitung zu optimieren.

Was liest Phantom und wie verhindert InnoDB sie (Sperren des nächsten Schlägers)?Was liest Phantom und wie verhindert InnoDB sie (Sperren des nächsten Schlägers)?Apr 13, 2025 am 12:16 AM

InnoDB verhindert effektiv das Phantom-Lesen durch den Mechanismus für den nächsten Kleien. 1) Nächstschlüsselmesser kombiniert Zeilensperr- und Gap-Sperre, um Datensätze und deren Lücken zu sperren, um zu verhindern, dass neue Datensätze eingefügt werden. 2) In praktischen Anwendungen kann durch Optimierung der Abfragen und Anpassung der Isolationsstufen die Verringerungswettbewerb reduziert und die Gleichzeitleistung verbessert werden.

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

AI Hentai Generator

AI Hentai Generator

Erstellen Sie kostenlos Ai Hentai.

Heißer Artikel

R.E.P.O. Energiekristalle erklärten und was sie tun (gelber Kristall)
3 Wochen vorBy尊渡假赌尊渡假赌尊渡假赌
R.E.P.O. Beste grafische Einstellungen
3 Wochen vorBy尊渡假赌尊渡假赌尊渡假赌
R.E.P.O. So reparieren Sie Audio, wenn Sie niemanden hören können
3 Wochen vorBy尊渡假赌尊渡假赌尊渡假赌
WWE 2K25: Wie man alles in Myrise freischaltet
4 Wochen vorBy尊渡假赌尊渡假赌尊渡假赌

Heiße Werkzeuge

PHPStorm Mac-Version

PHPStorm Mac-Version

Das neueste (2018.2.1) professionelle, integrierte PHP-Entwicklungstool

Senden Sie Studio 13.0.1

Senden Sie Studio 13.0.1

Leistungsstarke integrierte PHP-Entwicklungsumgebung

SAP NetWeaver Server-Adapter für Eclipse

SAP NetWeaver Server-Adapter für Eclipse

Integrieren Sie Eclipse mit dem SAP NetWeaver-Anwendungsserver.

SublimeText3 Mac-Version

SublimeText3 Mac-Version

Codebearbeitungssoftware auf Gottesniveau (SublimeText3)

VSCode Windows 64-Bit-Download

VSCode Windows 64-Bit-Download

Ein kostenloser und leistungsstarker IDE-Editor von Microsoft