MySQL techtalk

Openminds - 13 februari 2014

Transcript

1. MySQL Openminds techtalk - 13 februari 2014 Friday 14 February 14
2. MySQL • RDBMS • Open Source • 2de meest gebruikte OS RDBMS • SQL compliant (ANSI SQL 99 & more) Friday 14 February 14
3. Historiek Friday 14 February 14
4. ’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
5. Sun-era • 2008 - Sun microsystems koopt MySQL AB • 5.1.x releases begonnen in ’08 Friday 14 February 14
6. Oracle • 27 januari 2010: Oracle koopt Sun • Anti-concurrentie • Tot 2015: dual licenced Friday 14 February 14
7. Meer oracle • Niet alle changes komen nog beschikbaar • Code-reshuffle tussen 5.5 en 5.6 • InnoDB gegijzeld Friday 14 February 14
8. Fork me • Percona • MariaDB Friday 14 February 14
9. Storage engines Friday 14 February 14
10. MyISAM • Read • Geen transacties • Table level locking • Portable Friday 14 February 14
11. Aria • Fragmented key cache • Crash-safe • todo: transacties Friday 14 February 14
12. InnoDB • Transacties • References / foreign keys • Row level locking • Inserts/updates Friday 14 February 14
13. Queries Friday 14 February 14
14. Meta • create/drop/rename/... table • alter table create/drop/rename... column • Friday 14 February 14
15. Data manipuleren • Insert into ... • Update ... • Delete from ... • ... from infile ... • Truncate Friday 14 February 14
16. Data ophalen • select from ... • ... join ... • ... where ... • ... group by ... • ... having ... • ... order ... Friday 14 February 14
17. A little rant Friday 14 February 14
18. use DB_BLOG; select * FROM tbl_posts WHERE tbl_posts_approved = 1 Friday 14 February 14
19. Zoeken • select ... where ... Friday 14 February 14
20. Datastructuren • array (ordered) • hash (bucket list) • trees (btree, b+tree) Friday 14 February 14
21. Friday 14 February 14
22. Friday 14 February 14
23. Indexen Friday 14 February 14
24. Nut • Zoekopdrachten • Sorteeropdrachten • Data ophalen Friday 14 February 14
25. idx_a: where... • a = const • a < const, a > const, a <= const, a >= const • a between const and const2 Friday 14 February 14
26. Friday 14 February 14
27. Friday 14 February 14
28. Friday 14 February 14
29. Friday 14 February 14
30. Friday 14 February 14
31. Friday 14 February 14
32. Friday 14 February 14
33. Friday 14 February 14
34. idx_a_b • gecombineerde index • eerst a (prefix), dan b • where a = const and b = const • where a = const and b < const • where a between and b = const Friday 14 February 14
35. Cardinaliteit • Mate van variatie • Mate van sterkte van de filter • Hoger is beter Friday 14 February 14
36. Explain Friday 14 February 14
37. 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
38. 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
39. 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 ...<snip>... *************************** 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
40. 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
41. 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
42. 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
43. 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
44. 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
45. 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
46. 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
47. Joins Friday 14 February 14
48. • (Inner) join • Outer join • Left join, right join • ... Friday 14 February 14
49. Friday 14 February 14
50. SELECT * FROM employee JOIN department ON employee.DepartmentID = department.DepartmentID Friday 14 February 14
51. SELECT * FROM employee JOIN department ON employee.DepartmentID = department.DepartmentID Friday 14 February 14
52. SELECT * FROM employee FULL OUTER JOIN department ON employee.DepartmentID = department.DepartmentID Friday 14 February 14
53. SELECT * FROM employee FULL OUTER JOIN department ON employee.DepartmentID = department.DepartmentID Friday 14 February 14
54. SELECT * FROM employee LEFT OUTER JOIN department ON employee.DepartmentID = department.DepartmentID Friday 14 February 14
55. SELECT * FROM employee LEFT OUTER JOIN department ON employee.DepartmentID = department.DepartmentID Friday 14 February 14
56. Friday 14 February 14
57. Indexen • Indexen worden gebruikt • Join clause is eigenlijk deel van where Friday 14 February 14
58. Charsets Friday 14 February 14
59. Charsets • latin1 was lang default • utf8 • SET NAMES utf8; Friday 14 February 14
60. Friday 14 February 14
61. Tools • mysql • mysqldump • MySQL Workbench Friday 14 February 14
62. Cases Friday 14 February 14
63. select * from posts where category like ‘%,52,%’ or category like ’52,%’ or category like ‘%,52’; Friday 14 February 14
64. <?php $posts = mysql_query(‘select * from posts where stamp > NOW()‘); ... Friday 14 February 14
65. select * from posts order by rand() limit 5; Friday 14 February 14
66. select max(id) from posts; ... ... php-code ... select * from posts where id in (34,65,78,123,453); Friday 14 February 14
67. Input sanitation Friday 14 February 14
68. Friday 14 February 14