programmera.net -> mysql -> normal     för utskrift      info@programmera.net

Index

1. Vad är ett index?
2. INDEX / KEY
3. SHOW INDEX
4. CREATE INDEX
5. CREATE INDEX: Exempel
6. DROP INDEX
7. Multipelindex
8. När använder MySQL index?
9. När MySQL inte använder index
10. EXPLAIN
11. Tipsa SQL-motorn om index

1. Vad är ett index?

Index är en speciell tabell som gör att en vanlig tabell kan genomsökas snabbare.

  • Fördel=Snabba sökningar: Indexet är sorterat efter en eller flera kolumner. När MySQL använder ett index söker MySQL igenom indexet istället för i tabellen. Att söka i ett (sorterat) index är snabbare än att söka i en vanlig (osorterad) tabell. Korrekt indexering är den viktigaste faktorn för hög prestanda på databasen.
  • Nackdel=Långsamma uppdateringar: När man har indexerat en tabell måste indexet ändras varje gång man gör en INSERT eller DELETE på tabellen. Detta tar extra tid, och därför ska man inte skapa onödiga index.
I nästan samtliga fall får man bättre prestanda om man skapar index så att alla SQL-satser använder sig av ett index. Men undvik att skapa index som aldrig används. Nedan illustreras ett index:

2. INDEX / KEY

I vanliga fall skapar man ett index samtidigt som man skapar själva tabellen. Titta på följande kod från sidan  Tabeller : 
[olle@dev1]$ /usr/local/mysql/bin/mysql -u olle -p
Enter password: star
mysql> use olles_db;
mysql> CREATE TABLE animal( 
    -> id INT NOT NULL AUTO_INCREMENT,
    -> name VARCHAR(50),
    -> weight INT,
    -> birth DATE,
    -> timestamp TIMESTAMP,
    -> PRIMARY KEY (id)
    -> );
Raden PRIMARY KEY (id) skapar ett index som indexerar kolumnen id (som är tabellens primära nyckel). Använder man nu den primära nyckeln i sin SQL-sats kommer satsen att utföras snabbare.

3. SHOW INDEX

För att se vilka index som finns skapade för en tabell, använd: 
mysql> SHOW INDEX FROM animal;
+--------+------------+----------+--------------+-------------+-----------+-------------+
| Table  | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | 
+--------+------------+----------+--------------+-------------+-----------+-------------+
| animal |          0 | PRIMARY  |            1 | id          | A         |           0 |
+--------+------------+----------+--------------+-------------+-----------+-------------+
(fortsättning..)
+----------+--------+------+------------+---------+
| Sub_part | Packed | Null | Index_type | Comment |
+----------+--------+------+------------+---------+
|     NULL | NULL   |      | BTREE      |         |
+----------+--------+------+------------+---------+
Du tolkar utskriften på detta sätt:

Rubrik Default Beskrivning
Table  -  Tabellens namn.
Non_unique  -  0 om den indexerade kolumnen bara innehåller unika värden.
Key_name  -  Indexets namn.
Seq_in_index  -  Kolumnens sekvensnummer, börjar med ett.
Column_name  -  Kolumnens namn.
Collation  A  Hur kolumnen är sorterad i indexet. A=Stigande (Ascending), NULL=icke sorterad.
Cardinality  -  Antalet unika värden i indexet. Detta kan uppdateras med isamchk -a.
Sub_part  NULL  Antal indexerade tecken om kolumnen är delvis indexerad. NULL om hela nyckeln är indexerad.
Packed  NULL  ?
Null  -  YES om kolumnen kan innehålla NULL.
Index_type  BTREE  Vilken indexeringsmetod som används.
Comment  -  Programmerarens kommentarer.

eller alternativt: 
mysql> SHOW CREATE TABLE animal;

4. CREATE INDEX

Om tabellen redan finns kan man lägga till ett index med kommandot CREATE INDEX. En mer allmän struktur på CREATE INDEX visas nedan: 
CREATE [UNIQUE|FULLTEXT] INDEX index_name
       ON tbl_name (index_col_name,...)

index_col_name:
        col_name [(length)] [ASC | DESC]
Om du vet att du kommer använda olika kolumner i dina JOIN ska du skapa ett index för varje kolumn (för maximal prestanda).

  • FULLTEXT: Kan endast användas på CHAR, VARCHAR och TEXT. Innebär att man kan göra sökningar med funktionen MATCH() som letar efter en sträng i texten. Det tar lång tid att göra insert på tabeller som har index av denna typ.
  • UNIQUE:

5. CREATE INDEX: Exempel

I exemplet nedan indexerar vi kolumnen birth i tabellen animal: 
mysql> CREATE INDEX my_index_birth ON animal (birth);
Query OK, 0 rows affected (0.03 sec)
Records: 0  Duplicates: 0  Warnings: 0
Tittar vi nu på tabellen med SHOW CREATE TABLE ser vi följande: 
mysql> SHOW CREATE TABLE
CREATE TABLE `animal` (
  `id` int(11) NOT NULL auto_increment,
  `name` varchar(50) default NULL,
  `weight` int(11) default NULL,
  `birth` date default NULL,
  `timestamp` timestamp(14) NOT NULL,
  PRIMARY KEY  (`id`),
  KEY `my_index_birth` (`birth`)
) TYPE=MyISAM
Vi ser att en ny rad har lagts till: KEY `my_index_birth` (`birth`). Om vi listar alla index med SHOW INDEX ser vi nu: 
mysql> SHOW INDEX FROM animal;
+--------+------------+----------------+--------------+-------------+-----------+-------------+
| Table  | Non_unique | Key_name       | Seq_in_index | Column_name | Collation | Cardinality |
+--------+------------+----------------+--------------+-------------+-----------+-------------+
| animal |          0 | PRIMARY        |            1 | id          | A         |           0 |
| animal |          1 | my_index_birth |            1 | birth       | A         |        NULL |
+--------+------------+----------------+--------------+-------------+-----------+-------------+
(fortsättning ..)
+----------+--------+------+------------+---------+
| Sub_part | Packed | Null | Index_type | Comment |
+----------+--------+------+------------+---------+
|     NULL | NULL   |      | BTREE      |         |
|     NULL | NULL   | YES  | BTREE      |         |
+----------+--------+------+------------+---------+
Vi ser att det nya indexet skiljer sig från det första på följande sätt:

  • Non_unique=1: Det nya indexet kan innehålla dubletter.
  • Null=YES: Det nya indexets nyckel kan vara NULL.

6. DROP INDEX

För att kasta indexexet my_index_birth skriver du: 
mysql> DROP INDEX my_index_birth ON  animal;
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0
Nu har indexet raderats.

7. Multipelindex

Multipelindex är ett index som indexerar flera kolumnnamn samtidigt. Multipelindex är bra om du vet att du kommer göra SQL-satser som matchar flera kolumner samtidigt. Vi tänker oss att vi vill söka efter alla husdjur med namnet Pelle som väger mellan 2014 och 2825 gram. SELECT-satsen blir som följer: 
mysql> SELECT * 
	FROM animal 
	WHERE name="Pelle" 
	AND  weight > 2014
	AND  weight < 2825;
För att exemplet ska bli intressant förutsätter vi att det finns ett mycket stort antal husdjur som har smeknamnet Pelle i tabellen. Den första idén man får är att göra ett index på kolumnen name. Nu kan vi snabbt välja ut alla rader där name=Pelle. Så långt allt väl, men när vi ska välja ut alla husdjur med weight > 2014 och weight < 2825 måste vi gå igenom alla de utvalda raderna, eftersom indexet inte bryr sig om vikt. Se bilden:



Ett snabbare sätt är att göra ett multipelindex som indexerar båda kolumnerna name och weight. Nu kan alla SELECT-satsens JOINs utföras i indexet, d.v.s. vi hittar alla raderna vi vill ha genom att bara titta i indexet. Bilden nedan illustrerar detta:



Vi skapar detta multipelindex med: 
mysql> CREATE INDEX my_index_name_weight ON animal (name, weight);
Query OK, 0 rows affected (0.03 sec)
Records: 0  Duplicates: 0  Warnings: 0

8. När använder MySQL index?

Allmänt så gäller att index används om man använder någon av dessa operationer opererar på en nyckel: 
 >, >=, =, <, <=, IF NULL och BETWEEN
Nedan ges några exempel på när MySQL använder index: 
mysql> SELECT * FROM table_name WHERE key_part1=1 and key_part2 > 5;
mysql> SELECT * FROM table_name WHERE key_part1 IS NULL;
När man använder LIKE om det inte startar med wildcard: 
mysql> SELECT * FROM table_name WHERE key_part1 LIKE 'jani%'
JOIN med andra tabeller: 
mysql> SELECT * from t1,t2 where t1.col=t2.key_part
När man vill hitta MAX() eller MIN() för ett index: 
mysql> SELECT MIN(key_part2),MAX(key_part2) FROM table_name where key_part1=10
Vid sortering (ORDER BY eller GROUP BY) av en nyckel: 
mysql> SELECT * FROM foo ORDER BY key_part1,key_part2,key_part3

9. När MySQL inte använder index

Om MySQL tror att det går snabbare att läsa in hela tabellen så kommer indexet att ignoreras. I exemplet nedan är key_part1 en variabel mellan 1 till 100 som har värden på ett oordnat sätt: 
mysql> SELECT * FROM table_name where key_part1 > 1 and key_part1 < 90
Om du använder LIKE som startar med wildcard: 
mysql> SELECT * FROM table_name WHERE key_part1 LIKE '%jani%'
Om du söker på ett index men sorterar på ett annat: 
mysql> SELECT * from table_name WHERE key_part1 = # ORDER BY key2

10. EXPLAIN

Med kommandot EXPLAIN kan du kontrollera om en fråga använder ett index: 
mysql> EXPLAIN SELECT * FROM animal WHERE name="Hasse" AND weight > 5;
+--------+-------+----------------------+----------------------+---------+------+------+-------------+
| table  | type  | possible_keys        | key                  | key_len | ref  | rows | Extra       |
+--------+-------+----------------------+----------------------+---------+------+------+-------------+
| animal | range | my_index_name_weight | my_index_name_weight |      56 | NULL |    1 | Using where |
+--------+-------+----------------------+----------------------+---------+------+------+-------------+
1 row in set (0.00 sec)
I exemplet ovan ser vi att frågan använder indexet my_index_name_weight. Tabellen ska innehålla minst en rad för att EXPLAIN ska fungera.

11. Tipsa SQL-motorn om index

Ibland använder inte MySQL de index man vill. Då måste man ge SQL-motorn tips för rätt index ska användas. Såhär tipsar man om att ett index ska användas: 
SELECT *
	FROM table_name [USE INDEX (index_list) | IGNORE INDEX (index_list)], table_name2
Tipsen bör läggas inom kommentarer för att satserna ska fungera på andra system: 
mysql> SELECT * 
	FROM animal /*! USE INDEX (my_index_name_weight) */
	WHERE name="Pelle" 
	AND  weight > 2014
	AND  weight < 2825;