#backendmeetup

Качаем ловкость

MySQL

Sergey WhiteVEZETVSEM

MySQL vs PL/SQL ORACLE in me...● Отладчик● Использование hints● Шикарный оптимизатор SQL, PL/SQL кода● Использование JOB’S● Восстановление данных из временных логических таблиц● Сама архитектура Oracle● Миллион и маленькая тележка функций, процедур● Привычный ООП подход к написанию кода.

VEZETVSEM

О чём поговорим:

● Индексы MySQL● Совет по написанию SQL кода в MySQL.● SQL Tricks or how to make index● Как используются индексы в 90% случаях

VEZETVSEM

Индексы

● BTREE● RTREE● Hash Index● FullText MyISAM, InnoDB from 5.6

VEZETVSEM

MyISAM vs InnoDB

● Кластер: Нет● Блокировка на уровне таблицы WTF?!?!● Полнотекстовый поиск: ДА● Внешние ключи: НЕТ● Все индексы одинаковы

● Кластер: ДА● Блокировка на уровне row● Полнотекстовый поиск: НЕТ● Внешние ключи: ДА● Pimary key - особенный

MyISAM InnoDB

VEZETVSEM

Добби InnoDB for BTREE indexes● Find all rows with key = 5(point lookup)● Find all rows with key > 5(open range)● Find all rows with 5 < key < 10(close range)● Not find rows with last digit of the key is Zero

VEZETVSEM

Strings

● “AAAA” < “AAAB”

● LIKE “ABC%” =

ABC[low] < key < ABC[high]

● LIKE “%ABC”

VEZETVSEM

Для чего MySQL использует index● Поиск● Сортировка● Избежание повторного чтения данных

VEZETVSEM

Search

● Select * from EMPLOYES WHERE LAST_NAME = “IVANOV”

key(LAST_NAME)

● Select * from EMPLOYES WHERE LAST_NAME = “IVANOV” AND

DEPT = “DEV”

key(LAST_NAME,DEPT)

VEZETVSEM

Multiple Index tricks● Index(A,B,C) порядок имеет значение● Полное использование индекса

○ A = 5○ A = 5 AND B > 5○ A = 5 AND B =6 AND C = 7○ A = 5 AND B in (3,4) AND C > 7

● Не будет использоваться индекс○ B > 6○ B = 6 AND C > 7

● Используется только часть индекса○ A > 5 AND B = 6○ A = 5 AND B > 6 AND C = 7

VEZETVSEM

Основное правило1. Уважай первую колонку2. Остерегайся range…..

VEZETVSEM

Сортировка1. SELECT * FROM PLAYERS ORDER BY SCORES DESC LIMIT 10

Индексов нет, получим (filesort)

2. SELECT * FROM PLAYERS WHERE COUNTRY=”RUS” ORDER BY

SCORE DESC

key(COUNTRY, SCORE)

VEZETVSEM

Заставим делать сортировку в index’e● key(A,B)● Index работает:

○ ORDER BY A○ A = 5 ORDER BY B○ ORDER BY A DESC, ORDER BY B DESC○ A > 5 ORDER BY A

● Index не работает○ ORDER BY B○ A > 5 ORDER BY B○ A in (1,2) ORDER BY B○ ORDER BY A ASC, B DESC (Trick ORDER BY A ASC, -B ASC)

VEZETVSEM

Коротко о сортировке

VEZETVSEM

Читать данные с диска не эффективноSELECT STATUS FROM ORDERS WHERE CUSTOMER_ID = 123

index(CUSTOMER_ID,STATUS)

VEZETVSEM

GROUP BY● SELECT MAX(ID) FROM table● SELECT DEPT_ID, MAX(SALARY) FROM employee GROUP BY DEPT_ID

Необходим индекс(DEPT_ID,SALARY)

Основная радость Using Index for group by

VEZETVSEM

Index and JOINS

GOOD

SELECT * FROM USERS u

JOIN USER_PROFILE up

ON u.user_profile_id = up.id

WHERE u.role_id = 3

BAD

SELECT * FROM USERS u

JOIN USER_PROFILE up

ON u.id = up.user_id

WHERE u.role_id = 3

Нужен key(user_id)

VEZETVSEM

MERGE_INDEX● Select * from tabl1 WHERE A= 1 AND B = 3 key(a), key(b).

● Select * from tabl1 WHERE A =1 OR B = 3 (коварный fullscan)

VEZETVSEM

Explain● Средствами IDE, некоторые замечательно строят граф● EXPLAIN● EXPLAIN JSON● https://dev.mysql.com/doc/refman/5.7/en/explain-output.html

VEZETVSEM

Стратегия выбора Index’ов● Data and MUST NOW 100 SQL STATEMENTS ● Where● Exceptions(Hints)

VEZETVSEM

Пример стратегии индексирования.

● Select * from table where a > 5 and b = 3 ● Select * from table where a = 5 and b = 5

VEZETVSEM

Trick 1Есть ключ (A,B)SELECT * FROM table1 WHERE a BETWEEN 1 AND 4 AND b = 3

что можем сделать

SELECT * FROM table1 WHERE a in (1,2,3,4) AND b = 3

VEZETVSEM

Trick 2key(CITY,GENDER)

SELECT * FROM PEOPLE WHERE CITY = “ROSTOV”

Индекс будет использоваться, но не максимально эффективно. Для того чтобы выжать максимум из индекса, что стоит сделать:

SELECT * FROM PEOPLE WHERE CITY = “ROSTOV” AND GENDER IN (“M”,”F”)

VEZETVSEM

Trick 3...ужастный Filesortkey(A,B)

SELECT * FROM TABLE1 WHERE a in (1,2) ORDER BY b LIMIT 10;

SELECT * FROM (SELECT * FROM TABLE1 WHERE a =1 limit 10UNION ALL SELECT * FROM TABLE1 WHERE a =2 limit 10

) ORDER BY B.

VEZETVSEM

Доступен

● https://www.facebook.com/sergey.white.77● sergey.white@hotmail.com● s.ivanenko@vezetvsem.ru

VEZETVSEM