Optimieren meiner Mysql-Abfrage, um den Index für die Sortierung zu verwenden

Question

Ich habe einen zusammengesetzten Index auf der Grundlage von 3 Spalten, von denen zwei in meiner Abfrage eingeschränkt sind und die 3. ist in Reihenfolge durch Klausel noch mysql nicht Index für die Sortierung verwenden.

explain select \* from videos where public\_private='public' and approved='yes' order by number\_of\_views desc;

+----+-------------+--------+------+--------------------------------+------+---------+------+---------+-----------------------------+
| id | select\_type | table  | type | possible\_keys                  | key  | key\_len | ref  | rows    | Extra     |
+----+-------------+--------+------+--------------------------------+------+---------+------+---------+-----------------------------+
|  1 | SIMPLE      | videos | ALL  | approved,approved\_3,approved\_2 | NULL | NULL    | NULL | 1476818 | Using where; Using filesort |
+----+-------------+--------+------+--------------------------------+------+---------+------+---------+-----------------------------+

Die Struktur der Tabelle ist wie folgt:

CREATE TABLE `videos` (
  `indexer` int(9) NOT NULL auto_increment,
  `user_id` int(9) default NULL,
  `public_private` varchar(24) default NULL,
  `approved` varchar(24) default NULL,
  `number_of_views` int(9) default NULL,
  PRIMARY KEY  (`indexer`),
  KEY `approved` (`approved`,`user_id`),
  KEY `approved_3` (`approved`,`public_private`,`indexer`),
  KEY `approved_2` (`approved`,`public_private`,`number_of_views`),
) ENGINE=MyISAM AUTO_INCREMENT=1969091 DEFAULT CHARSET=utf8 |

Was sollte ich tun, um mysql zu zwingen, Index für die Sortierung der Ergebnisse zu verwenden?

Answer 1

Ich glaube, dass die Abfrage, die Sie haben, wahrscheinlich mit einem großen Prozentsatz der Daten in der Tabelle übereinstimmt. In solchen Situationen entscheidet sich der MySQL-Optimierer oft dafür, einen Tabellenscan durchzuführen und Indizes vollständig zu ignorieren, da dies schneller ist als das zusätzliche Lesen des gesamten Indexes und dessen Verwendung zum Heraussuchen der Daten. In diesem Fall vermute ich also, dass public_private='yes' and approved='yes' mit einem großen Teil Ihrer Tabelle übereinstimmt. Wenn MySQL den Index deshalb nicht verwendet, ist er auch nicht für die Sortierung verfügbar.

Wenn Sie wirklich wollen, dass ein Index verwendet wird, dann wäre die Lösung, dass Sie FORCE INDEX :

select * from videos FORCE INDEX (approved_2) where public_private='public' and approved='yes' order by number_of_views desc;

Ich würde jedoch einige Tests durchführen, um sicherzustellen, dass das, was Sie erhalten, tatsächlich schneller ist als das, was der MySQL-Optimierer ausgewählt hat. Offenbar ist der Optimierer hat einige Probleme mit der Auswahl für die Bestellung, so könnten Sie auf jeden Fall versuchen, diese und sehen, ob Sie eine verbesserte Leistung.

Answer 2

Bei zusammengesetzten Schlüsseln spielt die Reihenfolge eine Rolle. Wenn Sie nur sortieren wollen nach number_of_views unter Verwendung der approved_2 Taste, Ändern:

KEY `approved_2` (`approved`,`public_private`,`number_of_views`)

zu:

KEY `approved_2` (`number_of_views`,`approved`,`public_private`)

Zusammengesetzte Schlüssel in MySQL funktionieren von links nach rechts. Im obigen Beispiel wird der Schlüssel, der mit number_of_views , approved y public_private implicity erstellt Indizes auf:

• number_of_views
• number_of_views , approved
• number_of_views , approved , public_private

Answer 3

Das sollte funktionieren:

`select * from videos where approved='yes' and public_private='public' order by number_of_views desc;` 

Wenn dies nicht der Fall ist, erstellen Sie einfach einen separaten Index für number_of_views .

Das muss funktionieren.

(mysql folgt grundsätzlich der Links-Rechts-Reihenfolge. Also Ihr Index auf approved , public_private , number_of_views funktioniert nicht, wenn es nicht in dieser Reihenfolge verwendet wird, d.h. Sie können alle drei von links, 2 von links oder die ganz linke 1 verwenden. Aber es wird nicht funktionieren, wenn Sie die ganz linke nicht verwenden, das ist die Idee).

Die Verwendung eines separaten Indexes würde die number_of_views automatisch, dies könnte helfen bei order by -- da bin ich mir allerdings sicher.

Answer 4

Bei zusammengesetzten Schlüsseln kommt es auf die Reihenfolge an. Ich habe die Regeln vergessen, aber versuchen Sie es in einer anderen Reihenfolge.

Answer 5

Einen separaten Index auf der number_of_views Spalte und sehen Sie, ob es dann funktioniert.

Wenn ein Schlüssel eine Kombination aus 3 Spalten ist, wird dieser Schlüssel nur dann verwendet, wenn alle 3 Spalten für eine Operation verwendet werden.