MySQL techtalk

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