MySQL techtalk - Speaker Deck

Slide 1

Slide 1 text

MySQL Openminds techtalk - 13 februari 2014 Friday 14 February 14

Slide 2

Slide 2 text

MySQL • RDBMS • Open Source • 2de meest gebruikte OS RDBMS • SQL compliant (ANSI SQL 99 & more) Friday 14 February 14

Slide 3

Slide 3 text

Historiek Friday 14 February 14

Slide 4

Slide 4 text

’94 tot ’08 • begin ’94: MySQL AB • 23 mei ’95: eerste release • v3.19 (eind ’96) tot v3.23 (eind ‘01) • v4.x (’02 tot ’04) • v5.0.x (’05 tot ...) Friday 14 February 14

Slide 5

Slide 5 text

Sun-era • 2008 - Sun microsystems koopt MySQL AB • 5.1.x releases begonnen in ’08 Friday 14 February 14

Slide 6

Slide 6 text

Oracle • 27 januari 2010: Oracle koopt Sun • Anti-concurrentie • Tot 2015: dual licenced Friday 14 February 14

Slide 7

Slide 7 text

Meer oracle • Niet alle changes komen nog beschikbaar • Code-reshuﬄe tussen 5.5 en 5.6 • InnoDB gegijzeld Friday 14 February 14

Slide 8

Slide 8 text

Fork me • Percona • MariaDB Friday 14 February 14

Slide 9

Slide 9 text

Storage engines Friday 14 February 14

Slide 10

Slide 10 text

MyISAM • Read • Geen transacties • Table level locking • Portable Friday 14 February 14

Slide 11

Slide 11 text

Aria • Fragmented key cache • Crash-safe • todo: transacties Friday 14 February 14

Slide 12

Slide 12 text

InnoDB • Transacties • References / foreign keys • Row level locking • Inserts/updates Friday 14 February 14

Slide 13

Slide 13 text

Queries Friday 14 February 14

Slide 14

Slide 14 text

Meta • create/drop/rename/... table • alter table create/drop/rename... column • Friday 14 February 14

Slide 15

Slide 15 text

Data manipuleren • Insert into ... • Update ... • Delete from ... • ... from inﬁle ... • Truncate Friday 14 February 14

Slide 16

Slide 16 text

Data ophalen • select from ... • ... join ... • ... where ... • ... group by ... • ... having ... • ... order ... Friday 14 February 14

Slide 17

Slide 17 text

A little rant Friday 14 February 14

Slide 18

Slide 18 text

use DB_BLOG; select * FROM tbl_posts WHERE tbl_posts_approved = 1 Friday 14 February 14

Slide 19

Slide 19 text

Zoeken • select ... where ... Friday 14 February 14

Slide 20

Slide 20 text

Datastructuren • array (ordered) • hash (bucket list) • trees (btree, b+tree) Friday 14 February 14

Slide 21

Slide 21 text

Friday 14 February 14

Slide 22

Slide 22 text

Friday 14 February 14

Slide 23

Slide 23 text

Indexen Friday 14 February 14

Slide 24

Slide 24 text

Nut • Zoekopdrachten • Sorteeropdrachten • Data ophalen Friday 14 February 14

Slide 25

Slide 25 text

idx_a: where... • a = const • a < const, a > const, a <= const, a >= const • a between const and const2 Friday 14 February 14

Slide 26

Slide 26 text

Friday 14 February 14

Slide 27

Slide 27 text

Friday 14 February 14

Slide 28

Slide 28 text

Friday 14 February 14

Slide 29

Slide 29 text

Friday 14 February 14

Slide 30

Slide 30 text

Friday 14 February 14

Slide 31

Slide 31 text

Friday 14 February 14

Slide 32

Slide 32 text

Friday 14 February 14

Slide 33

Slide 33 text

Friday 14 February 14

Slide 34

Slide 34 text

idx_a_b • gecombineerde index • eerst a (preﬁx), dan b • where a = const and b = const • where a = const and b < const • where a between and b = const Friday 14 February 14

Slide 35

Slide 35 text

Cardinaliteit • Mate van variatie • Mate van sterkte van de ﬁlter • Hoger is beter Friday 14 February 14

Slide 36

Slide 36 text

Explain Friday 14 February 14

Slide 37

Slide 37 text

mysql> desc City; +-------------+----------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +-------------+----------+------+-----+---------+----------------+ | ID | int(11) | NO | PRI | NULL | auto_increment | | Name | char(35) | NO | | | | | CountryCode | char(3) | NO | MUL | | | | District | char(20) | NO | | | | | Population | int(11) | NO | | 0 | | +-------------+----------+------+-----+---------+----------------+ 5 rows in set (0.00 sec) Friday 14 February 14

Slide 38

Slide 38 text

mysql> explain select * from City where Population < 27000\G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: City type: ALL possible_keys: NULL key: NULL key_len: NULL ref: NULL rows: 4188 Extra: Using where 1 row in set (0.00 sec) Friday 14 February 14

Slide 39

Slide 39 text

mysql> create index idx_1 on City(Population); Query OK, 0 rows affected (0.03 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> show indexes from City\G ...... *************************** 3. row *************************** Table: city Non_unique: 1 Key_name: idx_1 Seq_in_index: 1 Column_name: Population Collation: A Cardinality: 4188 Sub_part: NULL Packed: NULL Null: Index_type: BTREE Comment: Index_comment: 3 rows in set (0.00 sec) Friday 14 February 14

Slide 40

Slide 40 text

mysql> explain select * from City where Population < 27000\G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: City type: range possible_keys: idx_1 key: idx_1 key_len: 4 ref: NULL rows: 64 Extra: Using index condition 1 row in set (0.00 sec) Friday 14 February 14

Slide 41

Slide 41 text

mysql> explain select * from City where Name = "Gent"\G *************************** 1. row *************************** rows: 4188 Extra: Using where 1 row in set (0.00 sec) mysql> create index Name on City (Name); Query OK, 0 rows affected (0.04 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> explain select * from City where Name = "Gent"\G *************************** 1. row *************************** table: City type: ref possible_keys: Name key: Name key_len: 35 ref: const rows: 1 Extra: Using index condition 1 row in set (0.00 sec) Friday 14 February 14

Slide 42

Slide 42 text

mysql> create index Population on City (Population); Query OK, 0 rows affected (0.04 sec) Records: 0 Duplicates: 0 Warnings: 0 Friday 14 February 14

Slide 43

Slide 43 text

mysql> explain select * from City where Name = "Gent" and Population < 27000\G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: City type: ref possible_keys: Name,Population key: Name key_len: 35 ref: const rows: 1 Extra: Using index condition; Using where 1 row in set (0.00 sec) Friday 14 February 14

Slide 44

Slide 44 text

mysql> explain select * from City where Name like "G%" and Population < 27000\G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: City type: range possible_keys: Name,Population key: Population key_len: 4 ref: NULL rows: 64 Extra: Using index condition; Using where 1 row in set (0.00 sec) Friday 14 February 14

Slide 45

Slide 45 text

mysql> create index NameAndPopulation on City(Name,Population); Query OK, 0 rows affected (0.05 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> explain select * from City where Name like "G%" and Population < 27000\G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: City type: range possible_keys: Name,Population,NameAndPopulation key: Population key_len: 4 ref: NULL rows: 64 Extra: Using index condition; Using where 1 row in set (0.00 sec) Friday 14 February 14

Slide 46

Slide 46 text

mysql> explain select * from City where Name like "Ge%" and Population < 27000\G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: City type: range possible_keys: Name,Population,NameAndPopulation key: NameAndPopulation key_len: 39 ref: NULL rows: 16 Extra: Using index condition 1 row in set (0.00 sec) Friday 14 February 14

Slide 47

Slide 47 text

Joins Friday 14 February 14

Slide 48

Slide 48 text

• (Inner) join • Outer join • Left join, right join • ... Friday 14 February 14

Slide 49

Slide 49 text

Friday 14 February 14

Slide 50

Slide 50 text

SELECT * FROM employee JOIN department ON employee.DepartmentID = department.DepartmentID Friday 14 February 14

Slide 51

Slide 51 text

SELECT * FROM employee JOIN department ON employee.DepartmentID = department.DepartmentID Friday 14 February 14

Slide 52

Slide 52 text

SELECT * FROM employee FULL OUTER JOIN department ON employee.DepartmentID = department.DepartmentID Friday 14 February 14

Slide 53

Slide 53 text

SELECT * FROM employee FULL OUTER JOIN department ON employee.DepartmentID = department.DepartmentID Friday 14 February 14

Slide 54

Slide 54 text

SELECT * FROM employee LEFT OUTER JOIN department ON employee.DepartmentID = department.DepartmentID Friday 14 February 14

Slide 55

Slide 55 text

SELECT * FROM employee LEFT OUTER JOIN department ON employee.DepartmentID = department.DepartmentID Friday 14 February 14

Slide 56

Slide 56 text

Friday 14 February 14

Slide 57

Slide 57 text

Indexen • Indexen worden gebruikt • Join clause is eigenlijk deel van where Friday 14 February 14

Slide 58

Slide 58 text

Charsets Friday 14 February 14

Slide 59

Slide 59 text

Charsets • latin1 was lang default • utf8 • SET NAMES utf8; Friday 14 February 14

Slide 60

Slide 60 text

Friday 14 February 14

Slide 61

Slide 61 text

Tools • mysql • mysqldump • MySQL Workbench Friday 14 February 14

Slide 62

Slide 62 text

Cases Friday 14 February 14

Slide 63

Slide 63 text

select * from posts where category like ‘%,52,%’ or category like ’52,%’ or category like ‘%,52’; Friday 14 February 14

Slide 64

Slide 64 text

NOW()‘); ... Friday 14 February 14

Slide 65

Slide 65 text

select * from posts order by rand() limit 5; Friday 14 February 14

Slide 66

Slide 66 text

select max(id) from posts; ... ... php-code ... select * from posts where id in (34,65,78,123,453); Friday 14 February 14