Lidt info om forskellige features i MySQL 4.0, 4.1 og 5.0: 1) MySQL 4.0, querycache 2) MySQL 4.0, UNION 3) MySQL 4.0, UPDATE og DELETE over flere tabeller 4) MySQL 4.1, subqueries 5) MySQL 4.1, GROUP_CONCAT 6) MySQL 5.0, Opgradering 7) MySQL 5.0, Triggers === 1) MySQL 4.0, querycache === Queries og deres tilsvarende resultater bliver cache'd, hvis MySQL- serveradministratoren har enabled querycache'n (hvilket er default), *og* har angivet en cache-størrelse (der default er på 0 bytes, hvilket ikke giver mulighed for at cache noget). Her et eksempel på en ineffektiv query, afviklet to gange i træk. mysql> SELECT COUNT(*) FROM bog s1, bog s2 WHERE s1.description LIKE '%e%' AND s2.description LIKE '%a%'; +----------+ | COUNT(*) | +----------+ | 2227484 | +----------+ 1 row in set (2.56 sec) mysql> SELECT COUNT(*) FROM bog s1, bog s2 WHERE s1.description LIKE '%e%' AND s2.description LIKE '%a%'; +----------+ | COUNT(*) | +----------+ | 2227484 | +----------+ 1 row in set (0.00 sec) - Bemærk svartiden på query'en, anden gang, vi kører den. Specielt for serverside-genererede websider, der som oftest laver præcis de samme SQL-forespørgsler for hvert eneste request, vil drage fordel af querycache'n. Der er ingen grund til at MySQL for hvert request skal ud og lave database-opslag og kigge i datafiler for at finde de 10 nyeste nyheder, de 10 nyeste artikler, de 5 næste begivenheder i kalenderen, de 3 næste fødselsdage, etc. - når den trods alt har lavet de samme requests for få sekunder siden, da der var en tidligere besøgende. Blot få MB allokeret til querycache'n kan gøre underværker. Tilføj fx query_cache_size = 8MB til serverens my.cnf (/etc/mysql/my.cnf på en debian) under [mysqld] for at allokere 8MB. Der skal ikke ændres noget som helst i ens eksisterende queries; funktionaliteten er helt transparent for klienten. Serveren sørger selvfølgelig for ikke at cache funktioner med varierende resultat, fx NOW(), RAND() og CURDATE(). Derfor kan det nogle gange betale sig at få sin applikation til at indsætte fx dags dato, i stedet for at bruge CURDATE(). Relevante queries, når man tester: SHOW VARIABLES LIKE '%query%'; SHOW STATUS LIKE 'Qcache%'; === 2) MySQL 4.0, UNION === UNION hjælper os med at udføre flere SELECTs og give os ét sammensat resultat af de forskellige SELECTs. UNION fjerner dubletter i det endelige resultat-sæt, og UNION ALL bevarer dem. Eksempel 1: Optimering af opslag med OR på to (indekserede) felter i en stor tabel: Før: mysql> SELECT id, code FROM storage WHERE id = 10329320 OR code = "ABF7-CJE-9" Her skal hele datafilen kigges igennem, selvom der er et index både på id-feltet og på code-feltet. Med et par millioner rækker kan det let blive en tung forespørgsel. Med UNION kan vi bare lave to uafhængige forespørgsler: Nu: mysql> SELECT id, code FROM storage WHERE id = 10329320 UNION SELECT id, code FROM storage WHERE code = "ABF7-CJE-9" UNION fjerner selv dubletter fra de to forespørgsler, såfremt id og code henviser til den samme entry i tabellen. Problemstillingen opstår kun, når vi bruger OR til at kigge på to *forskellige* felter i tabellen. Ved AND kan vi nøjes med at hive data ud, der matchede det ene index, og så bare matche denne meget lille delmængde med resten af WHERE-delen, og på den måde finde fællesmængden. Ved OR er det foreningsmængden, vi skal finde, og kan således ikke bare nøjes med at arbejde videre med at reducere resultatet ved opslag på det første index. Hvis det derimod var tale om ét felt, man ville finde rækker fra, ud fra to værdier, så ville der ikke være nogen problemer i første omgang, idet der så netop kun skal kigges i ét index. (eksempel: ".. id = 10329320 OR id = 159384302 ..", hvilket også kan samskrives til ".. id IN (10329320,159384302)" - de to forespørgsler er identiske, og optimeres på samme måde internt af mysql-serveren) [MySQL 5.0 understøtter index_merge-opslag, så til den tid vil der ikke være noget problem ved bare at bruge OR i det setup] Eksempel 2: Firmaet holder fest, og vil gerne invitere både VIP-kunder og vigtige medarbejdere: mysql> (SELECT navn FROM kunder WHERE status = 'VIP') UNION ALL (SELECT navn FROM ansatte WHERE jobtype = 'leder') Ved "UNION" bliver dubletter fra de to tabeller fjernet - fx hvis der både optræder en "Ib Hansen" i kunder-tabellen og i ansatte-tabellen. Ved "UNION ALL", som vi bruger her, bevares evt. dubletter. Eksempel 2.1: Vi skal nu finde fem tilfældige vindere til vores fest. Medarbejdere må også gerne deltage: mysql> (SELECT navn FROM kunder WHERE status = 'VIP') UNION ALL (SELECT navn FROM ansatte WHERE jobtype = 'leder') ORDER BY RAND() LIMIT 5; Eksempel 2.2: Vi skal i stedet finde 4 vindere blandt kunderne, og 1 vinder blandt medarbejderne: mysql> (SELECT navn FROM kunder WHERE status = 'VIP' ORDER BY RAND() LIMIT 4) UNION ALL (SELECT navn FROM ansatte WHERE jobtype = 'leder' ORDER BY RAND() LIMIT 1) === 3) MySQL 4.0, UPDATE og DELETE over flere tabeller === En UPDATE-forespørgsel kan nu henvise til flere tabeller. Det er muligt at ændre data i flere tabeller på én gang, eller evt. blot opdatere en tabel med data fra en anden: Her er det blot feltet "price" i items-tabellen, der bliver ændret: mysql> UPDATE items, month SET items.price = month.price WHERE items.id = month.id; Her ændrer vi felter i to forskellige tabeller, ud fra en tredje tabel. mysql> UPDATE tabel1, tabel2, tabel3 SET tabel2.fornavn = tabel1.fornavn, tabel3.efternavn = tabel1.efternavn WHERE tabel1.id = tabel2.id AND tabel1.id = tabel3.id En DELETE kan ligeledes henvise til flere tabeller. Her angiver man dog først hvilke tabeller, der skal slettes rækker fra, og dernæst fra hvilke tabeller, opslaget skal laves fra: mysql> DELETE t1, t2 FROM t1,t2,t3 WHERE t1.id = t2.id AND t2.id = t3.id; Her slettes der rækker fra tabellerne t1 og t2. === 4) MySQL 4.1, subqueries === Eksempel 1: Hvor meget tjener hver medarbejder? mysql> SELECT name, salary FROM employee; +-------------------+----------+ | name | salary | +-------------------+----------+ | John Smith | 15000.00 | | Jack Smith | 16000.00 | | Peter Johnson | 15500.00 | | Bartholomew White | 28000.00 | | Michael Friday | 18000.00 | | Lisa Craft | 30000.00 | +-------------------+----------+ 6 rows in set (0.00 sec) Godt, hvilke personer tjener mere end gennemsnitslønnen? Det kan vi finde ud af ved at have en subselect i vores query: mysql> SELECT name, salary FROM employee WHERE salary > (SELECT AVG(salary) FROM employee) ; +-------------------+----------+ | name | salary | +-------------------+----------+ | Bartholomew White | 28000.00 | | Lisa Craft | 30000.00 | +-------------------+----------+ 2 rows in set (0.01 sec) Eksempel 2: Hvor mange bøger har hver forfatter skrevet? mysql> SELECT forfatter_id, COUNT(*) AS boeger FROM forfatter_bog_relation GROUP BY forfatter_id; +--------------+--------+ | forfatter_id | boeger | +--------------+--------+ | 1 | 6 | | 2 | 18 | | 3 | 17 | | 4 | 8 | | 5 | 11 | ... Godt, hvor mange har så skrevet 18 bøger? Hvor mange har skrevet 17 bøger? etc... Vi skal nu lave endnu en GROUP BY på ovenstående resultatsæt, så denne query bliver lagt ind som en derived table: mysql> SELECT COUNT(*) AS forfattere, boeger FROM (SELECT forfatter_id, COUNT(*) AS boeger FROM forfatter_bog_relation GROUP BY forfatter_id) s1 GROUP BY s1.boeger ORDER BY boeger DESC; +------------+--------+ | forfattere | boeger | +------------+--------+ | 1 | 20 | | 1 | 19 | | 2 | 18 | | 4 | 17 | | 3 | 16 | | 3 | 15 | | 4 | 14 | | 6 | 13 | | 6 | 12 | | 9 | 11 | | 4 | 10 | | 10 | 9 | | 19 | 8 | | 16 | 7 | | 31 | 6 | | 35 | 5 | | 48 | 4 | | 77 | 3 | | 176 | 2 | | 454 | 1 | +------------+--------+ 20 rows in set (0.01 sec) === 5) MySQL 4.1, GROUP_CONCAT === Normalt "mister" man tilgængelig data, når man laver en GROUP BY. For talfelter kan man dog stadigvæk bruge aggregeringsfunktioner som fx MIN(), MAX(), AVG(), SUM(), etc., men for tekstfelter står man normalt blot tilbage med ét "tilfældigt" felt fra den nu grupperede data. Med GROUP_CONCAT kan man sammensætte/concatenate alle felter, der er grupperet sammen. Eksempel 1: Først en liste over folk i forskellige afdelinger: mysql> SELECT employee.department_id, employee.name FROM employee ORDER BY department_id; +---------------+-------------------+ | department_id | name | +---------------+-------------------+ | 1 | Peter Johnson | | 2 | John Smith | | 2 | Jack Smith | | 2 | Michael Friday | | 3 | Bartholomew White | | 3 | Lisa Craft | +---------------+-------------------+ 6 rows in set (0.01 sec) Nu ville det være rart med én række pr. afdeling, og så bare en liste over folk i afdelingen: mysql> SELECT department_id, GROUP_CONCAT(name) FROM employee GROUP BY department_id; +---------------+--------------------------------------+ | department_id | GROUP_CONCAT(name) | +---------------+--------------------------------------+ | 1 | Peter Johnson | | 2 | John Smith,Jack Smith,Michael Friday | | 3 | Bartholomew White,Lisa Craft | +---------------+--------------------------------------+ 3 rows in set (0.01 sec) Eksempel 2: Her med JOINs over flere tabeller, samt et mere kompliceret indhold i GROUP_CONCAT. Vi vil for hver bog finde alle forfattere, og præsentere dem i ét felt: mysql> SELECT bog.id, bog.title, GROUP_CONCAT(forfatter.firstname,' ',forfatter.surname SEPARATOR ', ') AS forfattere FROM bog LEFT JOIN forfatter_bog_relation ON forfatter_bog_relation.bog_id = bog.id LEFT JOIN forfatter ON forfatter.id = forfatter_bog_relation.forfatter_id GROUP BY bog.id LIMIT 20; +----+----------------------+----------------------------------------------+ | id | title | forfattere | +----+----------------------+----------------------------------------------+ | 1 | Vogterens Arving | Michael Erik Næsby | | 2 | Tidens Ritual | Lars Kaos Andresen | | 3 | Dr. Hoffmanns børn | Morten Juul | | 4 | Jisei | Kristoffer Apollo | | 5 | Monogami | Morten Trøst Jaeger | | 6 | Oculus Tertius | Jacob Schmidt-Madsen | | 7 | Jagten | Ask Agger | | 8 | Jagten Går Ind | Sebastian Flamant | | 9 | Bleeker Street | Adam Bindslev | | 10 | Tropical Zombies | Merlin P. Mann, Henrik Sylow | | 11 | Arken | Alex Uth | | 12 | Påskemorgen Røde | Paul Hartvigson | | 13 | Roser er Røde | Martha Cecilia Lassen, Jens Hougaard Nielsen | | 14 | Et Langt Liv | Nikolaj Lemche | | 15 | Paladins Lampe | Kristoffer Apollo, Christian Savioli | | 16 | Elysian Fields | Sanne Harder | | 17 | Messe for en Galning | Michael Erik Næsby | | 18 | Junglefeber | Nikolaj Lemche | | 19 | Majgækken | Sanne Harder | | 20 | Kongens By | Lone Gram Larsen, Flemming Lindblad Johansen | +----+----------------------+----------------------------------------------+ 20 rows in set (0.00 sec) === 6) MySQL 5.0, Opgradering === Jeg opgraderede fra MySQL 4.1 til MySQL 5.0 under Linux Debian, med pakker fra www.dotdeb.org. Denne sektion omhandler primært problemer ved at opgradere en eksisterende installation. Første problem var at jeg ikke kunne logge ind som nogen af brugerne efter opgraderingen, heller ikke root-brugeren. Eneste undtagelse var "debian-sys-maint"-brugeren (kig i /etc/mysql/debian.cnf), som jeg blev nødt til at logge ind med og få verden til at virke igen. I mysql.user-tabellen var Host og User blevet konverteret fra CHAR til VARBINARY. Det betød blandt andet, at alle hostnavne og brugernavne nu havde padding spaces. Det kan observeres med: mysql> SELECT Host, LENGTH(Host), User, LENGTH(User) FROM mysql.user; Hvis LENGTH er den samme (fx 16) stort set hele vejen ned, måske med undtagelse af den nyoprettede debian-sys-maint-bruger, så betyder det at en bruger, fx "hest" nu skal logge ind som "hest ". Men da Host tilsvarende er påvirket, kan "%" nu være ændret til "% ". Og så kan brugeren stadigvæk ikke logge ind. Jeg brugte følgende løsning: mysql> UPDATE mysql.user SET Host = TRIM(Host), User = TRIM(User) WHERE User != 'debian-sys-maint'; .. og derefter: mysql> FLUSH PRIVILEGES; Derefter kunne alle mine brugere logge ind igen uden videre. Jeg ved ikke om alle oplever problemet, eller om det kun var dotdeb-opgraderingen, der var pudsig i den henseende. Husk, at hvis du slet ikke kan komme ind på din mysql-server med nogen brugere, så kan du starte serveren op med --skip-grant-table og derefter logge ind som root uden password. Husk at reloade serveren igen bagefter eller kør FLUSH PRIVILEGES for at få genetableret password-tjek. Det er i øvrigt også en god idé at køre scriptet mysql_fix_privilege_tables fra kommandolinje. Det tilføjer relevante felter til bl.a. mysql.user- tabellen for at kunne give view-rettigheder m.m. Enkelte forespørgsler gav problemer efter installationen af MySQL 5.0. Pludselig virkede nogle LEFT JOINs ikke. Følgende vil fx ikke virke mere: mysql> SELECT a.foo FROM a, b LEFT JOIN c ON a.c_id = c.id .. ERROR 1054 (42S22): Unknown column 'a.c_id' in 'on clause' MySQL er dog her fuldt i overensstemmelse med SQL:2003, og der er tale om en bevidst ikke-bagudkompatibel ændring. Forklaringen er, at det bliver fortolket som: mysql> SELECT a.foo FROM a, (b LEFT JOIN c ON a.c_id = c.id) .. En workaround er at flytte a sidst i listen, eller blot at bruge parenteser om listen af tabeller, så de er adskilt fra ens LEFT JOIN-udtryk: mysql> SELECT a.foo FROM (a, b) LEFT JOIN c ON a.c_id = c.id .. Yderligere detaljer kan læses i MySQL bug #13551: http://bugs.mysql.com/bug.php?id=13551 Bladr ned til indlægget af Sergei Golubchik. === 7) MySQL 5.0, Triggers === Triggers kan benyttes til at lave ekstra operationer, når der foretages en SELECT, INSERT eller DELETE. Det kan for eksempel være at indsætte nogle ekstra rækker eller slette rækker i en anden tabel såfremt nogle omstændigheder er opfyldt. Man kan vælge om de hændelser skal foretages før eller efter den aktuelle operation. Triggers kræver SUPER-privilegiet for mysql-brugeren. Eksempel 1: Fjernelse af orphans (ON DELETE CASCADE-emulering) En mulighed er at lave en fattigmands-løsning for at fjerne orphans i en referencetabel, når der slettes i hovedtabellen. Dette løses normalt med foreign key-referencer, men da MyISAM ikke giver denne mulighed, må vi gøre det selv. For følgende konstruktion: mysql> CREATE TABLE departments (id int, department tinytext); mysql> INSERT INTO departments VALUES (1,'Finance'),(2,'Support'),(3,'Marketing'),(4,'Sales'),(5,'Tech'); mysql> SELECT * FROM departments; +------+------------+ | id | department | +------+------------+ | 1 | Finance | | 2 | Support | | 3 | Marketing | | 4 | Sales | | 5 | Tech | +------+------------+ mysql> CREATE TABLE employees (department_id int, name tinytext); mysql> INSERT INTO employees VALUES (1,'Anders'),(1,'Ole'),(2,'Hans'),(2,'Peter'),(3,'Jens'),(4,'Ib'), (4,'Mogens'),(4,'Erik'),(5,'Niels'),(5,'Thomas'),(5,'Lars'); mysql> SELECT * FROM employees; +---------------+--------+ | department_id | name | +---------------+--------+ | 1 | Anders | | 1 | Ole | | 2 | Hans | | 2 | Peter | | 3 | Jens | | 4 | Ib | | 4 | Mogens | | 4 | Erik | | 5 | Niels | | 5 | Thomas | | 5 | Lars | +---------------+--------+ .. vil vil gerne sørge for at når vi sletter en afdeling i departments- tabellen, så bliver medarbejdere, der tilhører den afdeling i den afdeling (departments.id = employees.department_id) også tilsvarende slettet. Vi laver nu en trigger, der aktiveres på en DELETE i departments-tabellen: mysql> CREATE TRIGGER layoff BEFORE DELETE ON departments FOR EACH ROW DELETE FROM employees WHERE department_id = OLD.id ; Lad os så lukke et par unødvendige afdelinger: mysql> DELETE FROM departments WHERE department IN('Marketing','Sales'); Query OK, 2 rows affected (0.32 sec) mysql> SELECT * FROM employees; +---------------+--------+ | department_id | name | +---------------+--------+ | 1 | Anders | | 1 | Ole | | 2 | Hans | | 2 | Peter | | 5 | Niels | | 5 | Thomas | | 5 | Lars | +---------------+--------+ Medarbejderne fra afdeling 3 og 4 ('Marketing' og 'Sales') er nu fjernet fra employees-tabellen. Bemærk, at dette langt fra er en fuldstændig løsning. Man kan stadigvæk insert'e (eller update) folk påny ind i de ikke-eksisterende department_id's og i øvrigt skabe orphans. Så det skal blot ses som en tilføjelse til en enkelt forespørgsel end en samlet løsning. Bemærk også, at en TRUNCATE (i stedet for en DELETE) af departments ikke udløser triggeren. ==== - Peter Brodersen