Prowadzę EXPLAIN:

mysql> explain select last_name from employees order by last_name;
+----+-------------+-----------+------+---------------+------+---------+------+-------+----------------+  
| id | select_type | table     | type | possible_keys | key  | key_len | ref  | rows  | Extra          |
+----+-------------+-----------+------+---------------+------+---------+------+-------+----------------+  
|  1 | SIMPLE      | employees | ALL  | NULL          | NULL | NULL    | NULL | 10031 | Using filesort |
+----+-------------+-----------+------+---------------+------+---------+------+-------+----------------+  
1 row in set (0.00 sec)  

Indeksy w mojej tabeli:

mysql> show index from employees;  
+-----------+------------+---------------+--------------+---------------+-----------+-------------+----------+--------+------+------------+---------+---------------+  
| Table     | Non_unique | Key_name      | Seq_in_index | Column_name   | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |  
+-----------+------------+---------------+--------------+---------------+-----------+-------------+----------+--------+------+------------+---------+---------------+  
| employees |          0 | PRIMARY       |            1 | subsidiary_id | A         |           6 |     NULL | NULL   |      | BTREE      |         |               |  
| employees |          0 | PRIMARY       |            2 | employee_id   | A         |       10031 |     NULL | NULL   |      | BTREE      |         |               |  
| employees |          1 | idx_last_name |            1 | last_name     | A         |       10031 |      700 | NULL   |      | BTREE      |         |               |  
| employees |          1 | date_of_birth |            1 | date_of_birth | A         |       10031 |     NULL | NULL   | YES  | BTREE      |         |               |  
| employees |          1 | date_of_birth |            2 | subsidiary_id | A         |       10031 |     NULL | NULL   |      | BTREE      |         |               |  
+-----------+------------+---------------+--------------+---------------+-----------+-------------+----------+--------+------+------------+---------+---------------+  
5 rows in set (0.02 sec)  

Nazwisko zawiera indeks, ale optymalizator go nie używa.
Ja również:

mysql> explain select last_name from employees force index(idx_last_name) order by last_name;  
+----+-------------+-----------+------+---------------+------+---------+------+-------+----------------+  
| id | select_type | table     | type | possible_keys | key  | key_len | ref  | rows  | Extra          |  
+----+-------------+-----------+------+---------------+------+---------+------+-------+----------------+  
|  1 | SIMPLE      | employees | ALL  | NULL          | NULL | NULL    | NULL | 10031 | Using filesort |  
+----+-------------+-----------+------+---------------+------+---------+------+-------+----------------+  
1 row in set (0.00 sec)  

Ale nadal indeks nie jest używany! Co robię tutaj źle?
Czy ma to związek z faktem, że indeks jest NON_UNIQUE? BTW ostatnią nazwą jestVARCHAR(1000)

Aktualizacja zażądana przez @RolandoMySQLDBA

mysql> SELECT COUNT(DISTINCT last_name) DistinctCount FROM employees;  
+---------------+  
| DistinctCount |  
+---------------+  
|         10000 |  
+---------------+  
1 row in set (0.05 sec)  


mysql> SELECT COUNT(1) FROM (SELECT COUNT(1) Count500,last_name FROM employees GROUP BY last_name HAVING COUNT(1) > 500) A;  
+----------+  
| COUNT(1) |  
+----------+  
|        0 |  
+----------+  
1 row in set (0.15 sec)  

PROBLEM # 1

Spójrz na zapytanie

select last_name from employees order by last_name;

Nie widzę sensownej klauzuli WHERE, podobnie jak MySQL Query Optimizer. Nie ma zachęty do korzystania z indeksu.

PROBLEM # 2

Spójrz na zapytanie

select last_name from employees force index(idx_last_name) order by last_name; 

Dałeś mu indeks, ale przejęła go Query Opitmizer. Widziałem to wcześniej ( Jak zmusić JOIN do używania określonego indeksu w MySQL? )

Dlaczego tak się dzieje?

Bez WHEREklauzuli Optymalizator zapytań mówi do siebie:

• To jest tabela InnoDB
• To indeksowana kolumna
• Indeks ma identyfikator wiersza indeksu gen_clust_index (aka Clustered Index)
• Dlaczego powinienem patrzeć na indeks, kiedy
  • nie ma WHEREklauzuli?
  • Zawsze musiałbym wracać do stołu?
• Ponieważ wszystkie wiersze w tabeli InnoDB znajdują się w tych samych blokach 16 KB co gen_clust_index, zamiast tego wykonam pełne skanowanie tabeli.

Optymalizator zapytań wybrał ścieżkę najmniejszego oporu.

Będziesz miał trochę szoku, ale oto i on: czy wiesz, że Optymalizator zapytań będzie obsługiwał MyISAM zupełnie inaczej?

Prawdopodobnie mówisz HUH ???? W JAKI SPOSÓB ????

MyISAM przechowuje dane w .MYDpliku i wszystkie indeksy w .MYIpliku.

To samo zapytanie wygeneruje inny plan EXPLAIN, ponieważ indeks znajduje się w innym pliku niż dane. Dlaczego ? Oto dlaczego:

• Potrzebne dane ( last_namekolumna) są już uporządkowane w.MYI
• W najgorszym przypadku będziesz miał pełny skan indeksu
• Dostęp do kolumny uzyskasz tylko last_namez indeksu
• Nie musisz przesiewać niepożądanych
• Nie uruchomisz tworzenia plików tymczasowych do sortowania

Jak możesz być tego taki pewien? Przetestowałem tę działającą teorię na temat tego, w jaki sposób użycie innego magazynu wygeneruje inny plan WYJAŚNIENIA (czasem lepszy): Czy indeks musi obejmować wszystkie wybrane kolumny, aby można go było zastosować w ORDER BY?

Problem polega na tym, że wygląda to jak indeks przedrostka. Nie widzę definicji tabeli w pytaniu, ale sub_part= 700? Nie zaindeksowałeś całej kolumny, więc indeksu nie można użyć do sortowania i nie jest on również przydatny jako indeks zakrywający. Można go użyć tylko do znalezienia wierszy, które „mogą” pasować do a, WHEREa warstwa serwera (powyżej silnika pamięci masowej) musiałaby dalej filtrować dopasowane wiersze. Czy naprawdę potrzebujesz 1000 znaków na nazwisko?

aktualizacja w celu zilustrowania: Mam tabelę testową tabeli z małym ponad 500 wierszami, każdy z nazwą domeny witryny internetowej w kolumnie domain_name VARCHAR(254) NOT NULLi bez indeksów.

mysql> alter table keydemo add key(domain_name);
Query OK, 0 rows affected (0.17 sec)
Records: 0  Duplicates: 0  Warnings: 0

Po zaindeksowaniu pełnej kolumny zapytanie korzysta z indeksu:

mysql> explain select domain_name from keydemo order by domain_name;
+----+-------------+---------+-------+---------------+-------------+---------+------+------+-------------+
| id | select_type | table   | type  | possible_keys | key         | key_len | ref  | rows | Extra       |
+----+-------------+---------+-------+---------------+-------------+---------+------+------+-------------+
|  1 | SIMPLE      | keydemo | index | NULL          | domain_name | 764     | NULL |  541 | Using index |
+----+-------------+---------+-------+---------------+-------------+---------+------+------+-------------+
1 row in set (0.01 sec)

Więc teraz upuszczę ten indeks i po prostu zindeksuję pierwsze 200 znaków nazwy_domeny.

mysql> alter table keydemo drop key domain_name;
Query OK, 0 rows affected (0.11 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> alter table keydemo add key(domain_name(200));
Query OK, 0 rows affected (0.08 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> explain select domain_name from keydemo order by domain_name;
+----+-------------+---------+------+---------------+------+---------+------+------+----------------+
| id | select_type | table   | type | possible_keys | key  | key_len | ref  | rows | Extra          |
+----+-------------+---------+------+---------------+------+---------+------+------+----------------+
|  1 | SIMPLE      | keydemo | ALL  | NULL          | NULL | NULL    | NULL |  541 | Using filesort |
+----+-------------+---------+------+---------------+------+---------+------+------+----------------+
1 row in set (0.00 sec)

mysql>

Voila

Zauważ też, że indeks, zawierający 200 znaków, jest dłuższy niż najdłuższa wartość w kolumnie ...

mysql> select max(length(domain_name)) from keydemo;
+--------------------------+
| max(length(domain_name)) |
+--------------------------+
|                       43 |
+--------------------------+
1 row in set (0.04 sec)

... ale to nie robi różnicy. Indeks zadeklarowany z długością prefiksu może być używany tylko do wyszukiwania, a nie do sortowania, a nie jako indeks pokrywający, ponieważ z definicji nie zawiera pełnej wartości kolumny.

Ponadto powyższe zapytania zostały uruchomione w tabeli InnoDB, ale uruchomienie ich w tabeli MyISAM daje praktycznie identyczne wyniki. Tylko różnicą w tym przypadku jest to, że liczyć InnoDB dla rowsnieco poza (541), podczas gdy pokazuje MyISAM dokładna liczba rzędów (563), który jest normalne zachowanie, ponieważ dwa silniki magazynowania uchwyt nurkowania indeksu w bardzo różny sposób.

Nadal twierdziłbym, że kolumna last_name jest prawdopodobnie większa niż potrzeba, ale nadal można indeksować całą kolumnę, jeśli używasz InnoDB i korzystasz z MySQL 5.5 lub 5.6:

Domyślnie klucz indeksu dla indeksu jednokolumnowego może mieć do 767 bajtów. Ten sam limit długości dotyczy każdego prefiksu klucza indeksu. Patrz sekcja 13.1.13, „ CREATE INDEXSkładnia”. Na przykład możesz przekroczyć ten limit z indeksem prefiksu kolumny dłuższym niż 255 znaków w kolumnie TEXTlub VARCHAR, zakładając, że UTF-8zestaw znaków i maksymalnie 3 bajty na każdy znak. Gdy innodb_large_prefixopcja konfiguracji jest włączona, przy czym granica ta długość wzrasta do 3072 bajtów, na InnoDBstołach, które używają DYNAMICi COMPRESSEDwierszy formaty.

^{- http://dev.mysql.com/doc/refman/5.5/en/innodb-restrictions.html}

Odpowiedziałem, ponieważ komentarz nie będzie obsługiwał formatowania, a program RolandoMySQL DBA mówił o gen_clust_index i innodb. Jest to bardzo ważne w przypadku tabeli opartej na innodb. To wykracza poza zwykłą wiedzę DBA, ponieważ musisz być w stanie analizować kod C.

ZAWSZE powinieneś ZAWSZE tworzyć KLUCZ PODSTAWOWY lub UNIKALNY KLUCZ, jeśli używasz Innodb. Jeśli nie użyjesz innodb, użyje wygenerowanego przez siebie ROW_ID, który może wyrządzić ci więcej szkody niż pożytku.

Spróbuję to wyjaśnić łatwo, ponieważ dowód jest oparty na kodzie C.

/**********************************************************************//**
Returns a new row id.
@return the new id */
UNIV_INLINE
row_id_t
dict_sys_get_new_row_id(void)
/*=========================*/
{
    row_id_t    id;

    mutex_enter(&(dict_sys->mutex));

    id = dict_sys->row_id;

    if (0 == (id % DICT_HDR_ROW_ID_WRITE_MARGIN)) {
          dict_hdr_flush_row_id();
    }

    dict_sys->row_id++;
    mutex_exit(&(dict_sys->mutex));
    return(id);
}

Pierwszy problem

mutex_enter (& (dict_sys-> mutex));

Ta linia zapewnia, że ​​tylko jeden wątek może uzyskać dostęp do dict_sys-> mutex w tym samym czasie. Co, jeśli już wartość została muteksowana ... tak, wątek musi poczekać, więc otrzymasz coś w rodzaju ładnej losowej funkcji, takiej jak blokowanie wątku, lub jeśli masz więcej tabel bez własnego KLUCZA PODSTAWOWEGO lub UNIKALNEGO KLUCZA, to masz fajną funkcję z Innodb „ blokowanie tabeli ” nie jest powodem, dla którego MyISAM został zastąpiony przez InnoDB, ponieważ nie jest to przyjemna funkcja zwana blokowaniem na podstawie rekordów / wierszy.

Drugi problem

(0 == (id% DICT_HDR_ROW_ID_WRITE_MARGIN))

obliczenia modulo (%) są powolne, niezbyt dobre, jeśli wstawiasz wsadowo, ponieważ za każdym razem trzeba je ponownie obliczać ... oraz ponieważ DICT_HDR_ROW_ID_WRITE_MARGIN (wartość 256) jest potęgą dwóch, można to zrobić znacznie szybciej.

(0 == (id & (DICT_HDR_ROW_ID_WRITE_MARGIN - 1)))

Uwaga dodatkowa: jeśli kompilator C został skonfigurowany do optymalizacji i jest dobrym optymalizatorem, optymalizator C naprawi „ciężki” kod do lżejszej wersji

motto tej historii zawsze stwórz własny KLUCZ PODSTAWOWY lub upewnij się, że masz indeks UNIKALNY podczas tworzenia tabeli od samego początku