MySQL Architektur - Überblick
Nachfolgendes betrachtet MySQL 5.0.x mit dem Schwerpunkt auf der InnoDB Storage Engine!
Inhaltsverzeichnis - Blättern mit den Cursortasten oder mit Mausklick! (manche Bullets lassen sich anklicken)
[Hilfe siehe unten]
show engines; +------------+---------+-------------------------------------------------------- | Engine | Support | Comment | +------------+---------+-------------------------------------------------------- | MyISAM | DEFAULT | Default engine as of MySQL 3.23 with great performance | | MEMORY | YES | Hash based, stored in memory, useful for temporary tables | | InnoDB | YES | Supports transactions, row-level locking, and foreign keys | | BerkeleyDB | NO | Supports transactions and page-level locking | | BLACKHOLE | YES | /dev/null storage engine (anything you write to it disappears) | | EXAMPLE | NO | Example storage engine | |.....
CREATE TABLE my_inno ( ... ) ENGINE = innodb;
->->-> [Query Cache
->-> Storage Cache/Buffer]
-> Festplatte
SELECT page_content WHERE page_id = 12;
SELECT SQL_CACHE * FROM avzmedien a LIMIT 10;
SELECT SQL_NO_CACHE * FROM avzmedien a LIMIT 10;
SHOW VARIABLES LIKE 'query_cache%'; Konfiguriert in der my.conf (Aufgabe des DBAs): # query cache query_cache_type = 1 // eingeschaltet query_cache_size = 25301231 // 25 MB Cache reserviert für den qc query_cache_limit = 253123 // max. 250 kb Resultsetgröße im Cache
show global status like 'qcache_%'; "Variable_name","Value" "Qcache_free_blocks","1" // freie Speicherblöcke im QC "Qcache_free_memory","722480" // freier Speicher für QS "Qcache_hits","201" // Treffer "Qcache_inserts","10" // Querys die in den QC hinzugefügt wurden "Qcache_lowmem_prunes","0" // Querys die Aufgrund des Speicherplatzes gelöscht wurden "Qcache_not_cached","5" // nicht gecachte oder cachebare Abfragen "Qcache_queries_in_cache","11" // gesamt Anzahl der Queries im QC "Qcache_total_blocks","121" // Anzahl der belegten Blöcke im QC
show status like 'innodb_buffer_pool%'; |Innodb... | .| |Innodb_buffer_pool_read_requests |349| |Innodb_buffer_pool_reads | 13|
SHOW STATUS LIKE 'Created_tmp%';liefert die Counter zu den Temporären Tabellen
CREATE INDEX idx_name (name(12));
SELECT id, desc FROM tab WHERE name LIKE = 'Thomas Walter%';nicht mehr komplett aus dem Index bedient werden
SELECT * FROM t1 FORCE INDEX(goodkey1,goodkey2) WHERE (goodkey1 < 10 OR goodkey2 < 20) AND badkey < 30;
MySQL> EXPLAIN SELECT * FROM avzmedien a WHERE title ='MySQL'\G;
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: a
type: ALL
possible_keys:
key:
key_len:
ref:
rows: 234511
Extra: Using where
1 row in set (0.00 sec)
- hier wird ein Full Table Scan ausgeführt (type: ALL).
MySQL> EXPLAIN SELECT * FROM avzmedien a WHERE title ='MySQL'\G;
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: a
type: ref
possible_keys: avz_title
key: avz_title
key_len: 14
ref: const
rows: 1
Extra: Using where
1 row in set (0.00 sec)
SELECT AVG(AGE) FROM USER GROUP BY CITYwird durch einen Index auf (city,age) schneller werden - allein schon durch die kürzeren Daten im Index die gescannt werden.
# in Python: import MySQLdb import MySQLdb.cursors con = MySQLdb.connect(...cursorclass=MySQLdb.cursors.SSDictCursor) # Hint: S
# in Python: import MySQLdb import MySQLdb.cursors con = MySQLdb.connect(...cursorclass = MySQLdb.cursors.CSDictCursor) # Hint: C
# User@Host: root[root] @ localhost [] # Query_time: 0.000652 Lock_time: 0.000109 Rows_sent: 1 Rows_examined: 1 SELECT id FROM avzmedien WHERE name='MySQL'; # User@Host: root[root] @ localhost [] # Query_time: 0.001943 Lock_time: 0.000145 Rows_sent: 0 Rows_examined: 0 INSERT INTO avzmedien (id, title,)VALUES(12,'MySQL');
Explain SELECT val,pad
FROM fact f
LEFT JOIN dim d ON f.id=d.id
WHERE f.val<100000 LIMIT 500000,10
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: f
type: range
possible_keys: Index_val
key: Index_val
key_len: 4
ref: NULL
rows: 1426930
Extra: Using where; Using index
*************************** 2. row ***************************
id: 1
select_type: SIMPLE
table: d
type: eq_ref
possible_keys: PRIMARY
key: PRIMARY
key_len: 4
ref: test.f.id
rows: 1
Extra:
besser:
SELECT i,pad
FROM (SELECT i,val
FROM fact
WHERE i<10000 LIMIT 500000,10) res
LEFT JOIN dim ON val=id;
*************************** 1. row ***************************
id: 1
select_type: PRIMARY
table:
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 10
Extra:
*************************** 2. row ***************************
id: 1
select_type: PRIMARY
table: d
type: eq_ref
possible_keys: PRIMARY
key: PRIMARY
key_len: 4
ref: fres.id
rows: 1
Extra:
*************************** 3. row ***************************
id: 2
select_type: DERIVED
table: f
type: range
possible_keys: Index_val
key: Index_val
key_len: 4
ref: NULL
rows: 1426930
Extra: Using where; Using index
Trotz das der zweite Ausführungsplan eine weiter Datenbankoperation benötigt ist das Statement um Faktor 10 schneller.
CREATE TEMPORARY TABLE t AS SELECT ... ALTER TABLE t ADD INDEX(...) SELECT ... FROM x JOIN t ON ... DROP TABLE t
SELECT * FROM a WHERE a.id in (SELECT id FROM b)dann als Subquerie umgeschrieben:
SELECT * FROM a, (SELECT id FROM b) as c WHERE a.id = c.id
SELECT id FROM cdcol.cds c UNION SELECT id FROM cdcol.cdrent so;
INSERT INTO tab (a,b) VALUES (1,23),(2,34),(4,33);
BEGIN; SELECT id FROM avzmedien WHERE title ='MySQL' LOCK IN SHARE MODE; INSERT INTO exemplare (..., avzmed_id) VALUES (....); COMMIT;
SELECT id FROM avzmedien FOR UPDATE WHERE title ='MySQL'; UPDATE avzmedien .... COMMIT;
LOCK TABLE WRITE avzmedien; INSERT / UPDATE irgendwas UNLOCK TABLE avzmedien;