How indexes work in relational databases

D98ee74ffe0fafbdc83b23907dda3665?s=47 doublemarket
February 10, 2017

How indexes work in relational databases

Talk at Tokyo Rubyist Meetup Feb. 10th, 2017

D98ee74ffe0fafbdc83b23907dda3665?s=128

doublemarket

February 10, 2017
Tweet

Transcript

  1. How indexes work in relational databases Tokyo Rubyist Meetup Feb.

    10th, 2017
  2. Who am I doublemarket • Technical support for a RoR

    based package software • Worked as MySQL DBA • Hobbies ◦ Translation - Yakst https://yakst.com/ja ◦ Creating twitter bots • Twitter : dblmkt
  3. SQL Performance Explained • How indexes work ◦ Multiple databases

    : Oracle, MySQL, PostgreSQL, etc. ◦ Multiple languages : PHP, Java, etc. • Written by Markus Winand • Available in EN, JP, GE, FR, SP ◦ Japanese ver. translator = me • Buy at http://sql-performance-explained.com/ ◦ JP : http://sql-performance-explained.jp/ • Free Web Edition: http://use-the-index-luke.com/
  4. What I’m going to talk • Why you need indexs

    (B-tree index) • How indexes work in general • Pros and Cons of indexes
  5. What I’m NOT going to talk about • How to

    write SQL queries (w/ Ruby on Rails) ◦ Buy and read “SQL Performance Explanined” ;-) ◦ Read AR manual • How to tune SQL queries ◦ Depends on a DBMS you’re using
  6. What is good points of SQL?

  7. What you want How you get

  8. What you want How you get When writing a SQL

    query
  9. What you want How you get When getting a result

    quickly or efficiently
  10. What you want How you get

  11. Developers need to know how to get data from storage

    ≒ how to use indexes
  12. What is index?

  13. Indexes in a relational database Same as an index of

    a book • To get data quickly and efficiently • Redundant, just pointing a piece of data stored somewhere • Sorted by a specific order Different from an index of a book • Updated frequently
  14. Ice cream Hand Nose Girl X’mas Dog Man Rain Fish

    Cat Orange Pen Egg Boy Van Water Sugar Apple Queen Tree Yellow Kite Lamp Jet Zoo Umbrella 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 Data
  15. Why don’t you scan all records everytime? • No, Inefficient

    ◦ Especially data is not on memory • Time to find a record will be longer when data is getting bigger
  16. A F D G C J B I H E

  17. Why don’t you sort records like a dictionary? • No,

    it’s inefficient.
  18. A B C E F G H I J D

  19. A B C E F G H I D J

  20. A B C E F G H D J I

  21. A B C E F G D J I H

  22. A B C E F D J I H G

  23. A B C E D J I H G F

  24. A B C D J I H G F E

  25. A B C D J I H G F E

  26. Use the index, Luke!

  27. Index ≒ B-tree index (in this talk)

  28. How the (B-tree) index works B-tree index • The most

    popular index structure for relational databases • Balanced tree, not binary tree Doubly linked list • Can change an order by changing pointers • Can connects leaf nodes
  29. I R Z I J K L M N O

    P Q R S T E F G H A B C D U V W X Y Z C F U X L R O I Z Ice cream Hand Nose Girl X’mas Dog Man Rain Fish Cat Orange Pen Egg Boy Van Water Sugar Apple Queen Tree Yellow Kite Lamp Jet Zoo Umbrella 18 14 10 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 6 13 9 4 2 1 24 22 23 7 3 11 12 19 8 17 20 26 15 16 5 21 25 (B Tree) Index Data … … … … … … … … … … … … … … … … … … … … … … … … … … …
  30. I R Z I J K L M N O

    P Q R S T E F G H A B C D U V W X Y Z C F U X L R O I Z 18 14 10 6 13 9 4 2 1 24 22 23 7 3 11 12 19 8 17 20 26 15 16 5 21 25 Root node Branch nodes Leaf nodes Ice cream Hand Nose Girl X’mas Dog Man Rain Fish Cat Orange Pen Egg Boy Van Water Sugar Apple Queen Tree Yellow Kite Lamp Jet Zoo Umbrella 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 … … … … … … … … … … … … … … … … … … … … … … … … … … …
  31. Ice cream Hand Nose Girl X’mas Dog Man Rain Fish

    Cat Orange Pen Egg Boy Van Water Sugar Apple Queen Tree Yellow Kite Lamp Jet Zoo Umbrella 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 … … … … … … … … … … … … … … … … … … … … … … … … … … … I R Z I J K L M N O P Q R S T E F G H A B C D U V W X Y Z C F U X L R O I Z 18 14 10 6 13 9 4 2 1 24 22 23 7 3 11 12 19 8 17 20 26 15 16 5 21 25 (B Tree) Index Data
  32. Pros of using the (B-tree) index The index • Reduces

    a number of disk accesses • Reduces the amount of disk accesses • Finds records in a same time when data is getting bigger • Is smaller than data itself = can fit in memory
  33. Cons of using the index The index • Consumes storage

    • Increases a cost when inserting/deleting/updating records Don’t create too many indices
  34. Summary • Learn how indexes work on relational databases •

    When writing a query ◦ Think about how indexes work ◦ Think about how the query works when data is getting bigger • When creating/updating/deleting an index ◦ Think about the balance ▪ How much the index makes queries fast ▪ How much the cost of modifying the index
  35. Learn how indexes work and make your queries fast

  36. http://sql-performance-explained.com http://sql-performance-explained.jp