Slide 1

Slide 1 text

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

Slide 2

Slide 2 text

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

Slide 3

Slide 3 text

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/

Slide 4

Slide 4 text

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

Slide 5

Slide 5 text

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

Slide 6

Slide 6 text

What is good points of SQL?

Slide 7

Slide 7 text

What you want How you get

Slide 8

Slide 8 text

What you want How you get When writing a SQL query

Slide 9

Slide 9 text

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

Slide 10

Slide 10 text

What you want How you get

Slide 11

Slide 11 text

Developers need to know how to get data from storage ≒ how to use indexes

Slide 12

Slide 12 text

What is index?

Slide 13

Slide 13 text

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

Slide 14

Slide 14 text

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

Slide 15

Slide 15 text

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

Slide 16

Slide 16 text

A F D G C J B I H E

Slide 17

Slide 17 text

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

Slide 18

Slide 18 text

A B C E F G H I J D

Slide 19

Slide 19 text

A B C E F G H I D J

Slide 20

Slide 20 text

A B C E F G H D J I

Slide 21

Slide 21 text

A B C E F G D J I H

Slide 22

Slide 22 text

A B C E F D J I H G

Slide 23

Slide 23 text

A B C E D J I H G F

Slide 24

Slide 24 text

A B C D J I H G F E

Slide 25

Slide 25 text

A B C D J I H G F E

Slide 26

Slide 26 text

Use the index, Luke!

Slide 27

Slide 27 text

Index ≒ B-tree index (in this talk)

Slide 28

Slide 28 text

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

Slide 29

Slide 29 text

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 … … … … … … … … … … … … … … … … … … … … … … … … … … …

Slide 30

Slide 30 text

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 … … … … … … … … … … … … … … … … … … … … … … … … … … …

Slide 31

Slide 31 text

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

Slide 32

Slide 32 text

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

Slide 33

Slide 33 text

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

Slide 34

Slide 34 text

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

Slide 35

Slide 35 text

Learn how indexes work and make your queries fast

Slide 36

Slide 36 text

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