Upgrade to Pro — share decks privately, control downloads, hide ads and more …

How indexes work in relational databases

doublemarket
February 10, 2017

How indexes work in relational databases

Talk at Tokyo Rubyist Meetup Feb. 10th, 2017

doublemarket

February 10, 2017
Tweet

More Decks by doublemarket

Other Decks in Technology

Transcript

  1. How indexes work in
    relational databases
    Tokyo Rubyist Meetup
    Feb. 10th, 2017

    View Slide

  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

    View Slide

  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/

    View Slide

  4. What I’m going to talk
    ● Why you need indexs (B-tree index)
    ● How indexes work in general
    ● Pros and Cons of indexes

    View Slide

  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

    View Slide

  6. What is good points of SQL?

    View Slide

  7. What you want
    How you get

    View Slide

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

    View Slide

  9. What you want
    How you get
    When getting a result quickly or efficiently

    View Slide

  10. What you want
    How you get

    View Slide

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

    how to use indexes

    View Slide

  12. What is index?

    View Slide

  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

    View Slide

  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

    View Slide

  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

    View Slide

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

    View Slide

  17. Why don’t you sort records like a dictionary?
    ● No, it’s inefficient.

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

  26. Use the index, Luke!

    View Slide

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

    View Slide

  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

    View Slide

  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



























    View Slide

  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



























    View Slide

  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

    View Slide

  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

    View Slide

  33. Cons of using the index
    The index
    ● Consumes storage
    ● Increases a cost when inserting/deleting/updating records
    Don’t create too many indices

    View Slide

  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

    View Slide

  35. Learn how indexes work
    and make your queries fast

    View Slide

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

    View Slide