MySQL Beispiel Skripts für „Die Where-Klausel“

Der Gleichheitsoperator

Künstliche Schlüssel

Das folgende Skript erstellt die EMPLOYEES Tabelle mit 1000 Einträgen.

CREATE TABLE employees (
   employee_id   NUMERIC      NOT NULL,
   first_name    VARCHAR(255) NOT NULL,
   last_name     VARCHAR(255) NOT NULL,
   date_of_birth DATE                 ,
   phone_number  VARCHAR(255) NOT NULL,
   junk          CHAR(255)            ,
   CONSTRAINT employees_pk PRIMARY KEY (employee_id)
);

CREATE OR REPLACE VIEW generator_16
AS SELECT 0 n UNION ALL SELECT 1  UNION ALL SELECT 2  UNION ALL
   SELECT 3   UNION ALL SELECT 4  UNION ALL SELECT 5  UNION ALL
   SELECT 6   UNION ALL SELECT 7  UNION ALL SELECT 8  UNION ALL
   SELECT 9   UNION ALL SELECT 10 UNION ALL SELECT 11 UNION ALL
   SELECT 12  UNION ALL SELECT 13 UNION ALL SELECT 14 UNION ALL
   SELECT 15;

CREATE OR REPLACE VIEW generator_256
AS SELECT ( ( hi.n << 4 ) | lo.n ) AS n
     FROM generator_16 lo, generator_16 hi;

CREATE OR REPLACE VIEW generator_4k
AS SELECT ( ( hi.n << 8 ) | lo.n ) AS n
     FROM generator_256 lo, generator_16 hi;

CREATE OR REPLACE VIEW generator_64k
AS SELECT ( ( hi.n << 8 ) | lo.n ) AS n
     FROM generator_256 lo, generator_256 hi;

INSERT INTO employees (employee_id,  first_name,
                       last_name,    date_of_birth, 
                       phone_number, junk)
SELECT gen.n +1,
       GROUP_CONCAT(CHAR((RAND() * 25)+97) SEPARATOR ''),
       GROUP_CONCAT(CHAR((RAND() * 25)+97) SEPARATOR ''),
       SUBDATE(CURDATE(), INTERVAL (RAND()*3650 + 40*365) DAY),
       FLOOR(RAND()*9000+1000),
       'junk'
  FROM generator_4k gen, generator_16 rand
 WHERE gen.n < 1000
 GROUP BY gen.n;

UPDATE employees 
   SET first_name='MARKUS', 
       last_name='WINAND'
 WHERE employee_id=123;

ANALYZE TABLE employees;

Bemerkungen:

• Die GENERATOR_X werden im Artikel „MySQL Zeilengenerator“ erklärt.

• Die JUNK-Spalte wird verwendet, um eine realistische Zeilenlänge zu erhalten. Im Gegensatz zu VARCHAR belegt der CHAR-Datentyp immer den angegebenen Platz ‒ 1000 Byte in diesem Fall. Ohne diese Spalte wäre die Tabelle unrealistisch klein und viele Beispiele würden nicht funktionieren.

• Die Tabelle wird mit Zufallsdaten gefüllt. Danach wird mein Eintrag durch ein update geändert.

• Die Tabellen und Index Statistiken werden erstellt, sodass der Optimizer einen Überblick über den Tabelleninhalt hat.

Zusammengesetzte Schlüssel

Das nächste Skript ändert die EMPLOYEES Tabelle so, dass sie die Situation nach der Zusammenlegung mit „Sehr Große Firma“ widerspiegelt.

-- add subsidiary_id and update existing records
ALTER TABLE employees ADD subsidiary_id NUMERIC;

UPDATE      employees SET subsidiary_id = 30;

ALTER TABLE employees MODIFY subsidiary_id NUMERIC NOT NULL;

-- change the PK
ALTER TABLE employees DROP PRIMARY KEY;

ALTER TABLE employees ADD CONSTRAINT employees_pk 
      PRIMARY KEY (employee_id, subsidiary_id);

-- generate more records (Very Big Company)
INSERT INTO employees (employee_id,  first_name, 
                       last_name,    date_of_birth, 
                       phone_number, subsidiary_id, junk)
SELECT gen.n + 1
     , GROUP_CONCAT(CHAR( RAND()*25 + 97) SEPARATOR '')
     , GROUP_CONCAT(CHAR( RAND()*25 + 97) SEPARATOR '')
     , CURDATE() - INTERVAL (RAND(0)*365*10 + 40*365) DAY
     , FLOOR(RAND()*9000 + 1000)
     , FLOOR(RAND()*(gen.n/9000)*29 + 1)
     , 'junk'
  FROM generator_64k gen, generator_16 rand
 WHERE gen.n < 9000
 GROUP BY gen.n;

ANALYZE TABLE employees;

Bemerkungen:

• Der Primärschlüssel wurde um die SUBSIDIARY_ID Spalte erweitert. Die EMPLOYEE_ID Spalte bleibt an erster Stelle.

• Die neuen Einträge werden zufällig auf die Töchter 1 bis 29 aufgeteilt.

• Die Tabelle und Indizes werden neu analysiert, damit der Optimizer das neue Mengengerüst kennt.

Das nächste Skript erstellt den Index auf SUBSIDIARY_ID, um Abfragen nach allen Angestellten eines bestimmten Subunternehmens zu unterstützen:

ALTER TABLE employees ADD INDEX emp_sub_id (subsidiary_id)

Obwohl der Index gute Antwortzeiten ermöglicht, ist es besser den Primärschlüssel-Index umzubauen, sodass er diese Abfrage ebenfalls unterstützen kann:

-- use tmp index to support the PK
ALTER TABLE employees
  ADD UNIQUE INDEX tmp (employee_id, subsidiary_id);

ALTER TABLE employees
 DROP PRIMARY KEY;

ALTER TABLE employees
  ADD PRIMARY KEY (subsidiary_id, employee_id);

ALTER TABLE employees
 DROP INDEX tmp;

ALTER TABLE employees
 DROP INDEX emp_sub_id;

ANALYZE TABLE employees;

Bemerkungen:

• Ein temporärer unique Index wird angelegt, um den Primärschlüssel zu ersetzen.

• Der Primärschlüssel wird gelöscht, und mit der gänderten Spaltenreihenfolge neu angelegt.

• Der Temporärindex, und auch der Index auf SUBSIDIARY_ID, können gelöscht werden.

Funktionen

Die Default-Collation von MySQL ist case-insensitiv – in diesem Fall genügt ein regulärer Index. Darüber hinaus konnte MySQL vor Version 5.7. keine funktions-basierte Indizierung.

CREATE INDEX emp_name ON employees (last_name)

Seit Version 5.7 kann man in MySQL funktions-basierte Indizes über den Umweg einer berechneten Spalte anlegen:

Benutzerdefinierte Funktionen sind in generierten Spalten nicht erlaubt – auch nicht, wenn sie deterministisch sind und als deterministisch gekennzeichnet sind.

CREATE FUNCTION get_age(date_of_birth DATE)
RETURNS INTEGER NO SQL
RETURN TIMESTAMPDIFF(YEAR,date_of_birth,CURDATE());

ALTER TABLE employees
  ADD COLUMN last_name_up VARCHAR(255) AS (UPPER(last_name));

CREATE INDEX emp_up_name ON employees (last_name_up);