MySQL techtalk

Openminds MySQL techtalk van 18 januari 2011

Transcript

1. Openminds techtalk - 18 / 01 / 2012 @openminds www.openminds.be Thursday 19 January 2012
2. Thursday 19 January 2012
3. MySQL •RDBMS •My - S - Q - L •Dual-licenced Thursday 19 January 2012
4. Historiek •94 - start •00 - 3.23 release •2008 - Sun •2010 - Oracle Thursday 19 January 2012
5. Versie-soep •MySQL - De Oracle versie •MariaDB - Monty’s versie •Percona - Performance versie •OurDelta - patchwork •Drizzle - stripped down Thursday 19 January 2012
6. Technologie •C/C++ •Unix / Solaris / Windows / ... •Lexer - yacc •API’s naar .NET, PHP, Ruby, Java, ... Thursday 19 January 2012
7. SQL standaard •Niet compatible •Triggers maar één voor en één na de query •Geen triggers op views Thursday 19 January 2012
8. Twee delen •Algemene server •Storage engine’s Thursday 19 January 2012
9. Storage •MyISAM (v9 of v10) •InnoDB •ExtraDB •Memory/Heap/Blackhole/Example/... Thursday 19 January 2012
10. Storage •Default zal goed genoeg zijn •Kan aangepast worden •Foute keuze kan dus rechtgezet worden Thursday 19 January 2012
11. Saaie theorie Thursday 19 January 2012
12. Database design •Geen dubbele data •Normalisatie •Primary keys •Tabellen lopen niet altijd gelijk met models Thursday 19 January 2012
13. Relaties •Foreign keys Thursday 19 January 2012
14. BTree Thursday 19 January 2012
15. MySQL gebruiken •command line interface •MySQL workbench •PhpMyAdmin (pma) •DBA op systeemniveau Thursday 19 January 2012
16. cli •Zeer eenvoudig •Kan alles •Dagelijkse tool •mysql -u user -p dbnaam Thursday 19 January 2012
17. mysqldump --opt databasenaam > datafile.sql mysql databasenaam < datafile.sql mysqldump --opt databasenaam | gzip > datafile.sql.gz zcat datafile.sql.gz | mysql databasenaam Thursday 19 January 2012
18. MySQL workbench Thursday 19 January 2012
19. Thursday 19 January 2012
20. PhpMyAdmin Thursday 19 January 2012
21. Queries Thursday 19 January 2012
22. META Thursday 19 January 2012
23. DATA INPUT Thursday 19 January 2012
24. SELECT Thursday 19 January 2012
25. SELECT ... FROM ... JOIN ... WHERE ... AND/OR/... GROUP BY ... HAVING ... ORDER BY ... Thursday 19 January 2012
26. SELECT * FROM posts WHERE published = 1 Thursday 19 January 2012
27. SELECT * FROM comments WHERE post_id = 265 AND approved = 1; Thursday 19 January 2012
28. Join •(Inner) join •Outer join •Left join, right join •... Thursday 19 January 2012
29. Join Thursday 19 January 2012
30. SELECT * FROM employee JOIN department ON employee.DepartmentID = department.DepartmentID Thursday 19 January 2012
31. SELECT * FROM employee JOIN department ON employee.DepartmentID = department.DepartmentID Thursday 19 January 2012
32. SELECT * FROM employee INNER JOIN department USING (DepartmentID) Thursday 19 January 2012
33. SELECT * FROM employee FULL OUTER JOIN department ON employee.DepartmentID = department.DepartmentID Thursday 19 January 2012
34. SELECT * FROM employee FULL OUTER JOIN department ON employee.DepartmentID = department.DepartmentID Thursday 19 January 2012
35. SELECT * FROM employee LEFT OUTER JOIN department ON employee.DepartmentID = department.DepartmentID Thursday 19 January 2012
36. SELECT * FROM employee LEFT OUTER JOIN department ON employee.DepartmentID = department.DepartmentID Thursday 19 January 2012
37. Thursday 19 January 2012
38. 42 Thursday 19 January 2012
39. 42 Thursday 19 January 2012
40. Thursday 19 January 2012
41. MySQL & snelheid •Dataset zo snel mogelijk doorzoeken •Dataset zo klein mogelijk houden Thursday 19 January 2012
42. Thursday 19 January 2012
43. Indexen Thursday 19 January 2012
44. Indexen •Zoeksleutels in een btree/hash Thursday 19 January 2012
45. Indexen •Zoeksleutels in een btree/hash •Resultaat blijft in de database Thursday 19 January 2012
46. SHOW INDEXES FROM comments; +----------+------------+----------+--------------+-------------+... | Table | Non_unique | Key_name | Seq_in_index | Column_name |... +----------+------------+----------+--------------+-------------+... | comments | 0 | PRIMARY | 1 | id |... +----------+------------+----------+--------------+-------------+... ...+-----------+-------------+----------+--------+------+------------+---------+ ...| Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | ...+-----------+-------------+----------+--------+------+------------+---------+ ...| A | 9294 | NULL | NULL | | BTREE | | ...+-----------+-------------+----------+--------+------+------------+---------+ Thursday 19 January 2012
47. Indexen •Herberekenen bij insert/update/... •Moeten in-mem zitten om efficiënt te zijn •Teveel is ook niet goed Thursday 19 January 2012
48. Limieten •MySQL gebruikt maximaal één index per tabel Thursday 19 January 2012
49. select * from comments where visible = 1 and post_id = 23; Thursday 19 January 2012
50. +-------------+----------+---------------+------+-------+-------------+ | select_type | table | possible_keys | key | rows | Extra | +-------------+----------+---------------+------+-------+-------------+ | SIMPLE | comments | NULL | NULL | 10180 | Using where | +-------------+----------+---------------+------+-------+-------------+ Thursday 19 January 2012
51. Thursday 19 January 2012
52. Meerdere kolommen •mysql gebruikt maar één index per tabel •index maken over meerdere kolommen Thursday 19 January 2012
53. Joins explainen •Nuttig •MySQL beslissingen onderzoeken Thursday 19 January 2012
54. select * from tags inner join posts_tags on posts_tags.tag_id = tags.id inner join posts on posts.id = posts_tags.post_id inner join comments on comments.post_id = posts.id where posts.author_id = 23 and posts.category_id in (23,5,18) and comments.visible >= 1 and posts.visible >= 1; Thursday 19 January 2012
55. select * from tags inner join posts_tags on posts_tags.tag_id = tags.id inner join posts on posts.id = posts_tags.post_id inner join comments on comments.post_id = posts.id where posts.author_id = 23 and posts.category_id in (23,5,18) and comments.visible >= 1 and posts.visible >= 1; Thursday 19 January 2012
56. select * from tags inner join posts_tags on posts_tags.tag_id = tags.id inner join posts on posts.id = posts_tags.post_id inner join comments on comments.post_id = posts.id where posts.author_id = 23 and posts.category_id in (23,5,18) and comments.visible >= 1 and posts.visible >= 1; Thursday 19 January 2012
57. select * from tags inner join posts_tags on posts_tags.tag_id = tags.id inner join posts on posts.id = posts_tags.post_id inner join comments on comments.post_id = posts.id where posts.author_id = 23 and posts.category_id in (23,5,18) and comments.visible >= 1 and posts.visible >= 1; Thursday 19 January 2012
58. String-zoeken •Titel die begint met “van” (“van%”) •Titel die eindigt met “van” (“%van”) •Titel die “van” bevat (“%van%”) Thursday 19 January 2012
59. Bewerkingen •Een bewerking op een variabel veld •Bewerking omkeren Thursday 19 January 2012
60. Cases Thursday 19 January 2012
61. select * from posts where category like ‘%,52,%’ or category like ’52,%’ or category like ‘%,52’; Thursday 19 January 2012
62. <?php $posts = mysql_query(‘select * from posts where stamp > NOW()‘); ... Thursday 19 January 2012
63. select * from posts order by rand() limit 5; Thursday 19 January 2012
64. select max(id) from posts; ... ... php-code ... select * from posts where id in (34,65,78,123,453); Thursday 19 January 2012
65. select * from posts where title like ‘%wraak%’; Thursday 19 January 2012
66. Zoeken •Fulltext index •Gespecialiseerde software •Lucene / sphinx / solr Thursday 19 January 2012
67. subselects •Op te lossen via een join •Join te optimaliseren met indexen Thursday 19 January 2012
68. charset trouble •charsets zijn overal (connectie, db, schema, ...) •export / import meeste probleem •collations (duitse SS!) Thursday 19 January 2012
69. Thursday 19 January 2012
70. databasenamen •niet langer 16 karakters •geen puntjes Thursday 19 January 2012
71. A little rant Thursday 19 January 2012
72. use DB_BLOG; select * FROM tbl_posts WHERE tbl_posts_approved = 1 Thursday 19 January 2012
73. use BLOG; select * FROM posts WHERE posts.approved = 1 Thursday 19 January 2012
74. use BLOG; select * FROM post WHERE post.approved = 1 Thursday 19 January 2012
75. Q&A Thursday 19 January 2012