SQL с нуля (лекция 4)

1.

SQL с нуля
Лекция 4

2.

Index
Индексы - это специальные структуры в базах данных, которые
позволяют ускорить поиск и сортировку по определенному
полю или набору полей в таблице, а также используются для
обеспечения уникальности данных.

3.

Поиск данных в MySQL
SELECT * FROM users WHERE age = 29

4.

Сортировка данных

5.

Выбор индексов в MySQL

6.

Синтаксис
CREATE INDEX index_name
ON Table_name (column 1, …)
CREATE INDEX age ON users(age);
DROP INDEX index_name ON Table_name;

7.

Внутренности хранения индексов
ID
name
age
age index
1
Den
29
12
2
Alyona
15
15
3
Vasya
89
29
4
Retr
12
89
связь с записями
age index
4
12
2
15
1
29
3
89

8.

Уникальные индексы
SELECT * FROM users WHERE email = '[email protected]';
CREATE UNIQUE INDEX email ON users(email)

9.

Составные индексы
SELECT * FROM users WHERE age = 29 AND gender = 'male‘
CREATE INDEX age_gender ON users(age, gender);

10.

Explain

11.

Что влияет на
производительность
запросов?

12.

Железо
Индексы
Операторы
Кардинальность
Одно-/многопоточность
Типы данных и размерности
Последовательность операторов

13.

Типы таблиц aka Движок
*
InnoDB
*
MyISAM

14.

MyISAM
InnoDB
Не поддерживает транзакционность =>
занимает меньше места
Полностью поддерживает
транзакционность, команды отката и
коммита транзакций
Не поддерживает внешние ключи
Поддержка внешних ключей
Блокировка на уровне таблицы
Блокировка на уровне строки
Отсутствие самовосстановления
данных при сбоях
Самовосстановление по логам БД
Таблица хранятся в виде двух файлов
данные + индексы. Являются
платформенно независимыми
Таблицы хранятся в одном файле
Высокая скорость при
последовательных просмотрах данных
Хорошая работа с многопоточностью
Низкая скорость insert\update\delete

15.

Slow
Query Logs
# Time: 140520 15:25:26
# User@Host: root[root] @ localhost [127.0.0.1] Id: 1
# Query_time: 0.171875 Lock_time: 0.093750 Rows_sent: 10400
Rows_examined: 10400
use study;
SET timestamp=1400588726;
select * from employees;

16.

Удаленный доступ к БД
Чтобы открыть удаленный доступ к БД необходимо выполнить следующие команды:
mysql -uroot -p
use mysql
SELECT Host,User,Password FROM user;
Host
User
Password
localhost
root
425678901585ACDB80C72475B1E70451ADB1263
Это означает, что в MySQL есть всего один пользователь root и у него установлен пароль. И подключиться он может
только с localhost.
Чтобы разрешить пользователю root подключаться с любого компьютера необходимо выполнить следующие команды:
UPDATE user SET Host='%' WHERE User='root' AND Host='localhost';
FLUSH PRIVILEGES;
Внимание: Убедитесь что у вас установлен пароль для root, т.к. если его нет любой пользователь с легкостью
изменит ваши данные
UPDATE user SET Password=PASSWORD('новый пароль') WHERE User='root' AND
Host='%';

17.

Для того чтобы дать доступ к конкретной базе данных, конкретному пользователю, вопервых должна быть создана БД, во-вторых пользователь.
Создание БД на mysql сервере:
mysql -u root -p
CREATE DATABASE mydb;
Создание пользователя и выдача ему прав на пользование БД:
CREATE USER 'user_name'@'ip_address' IDENTIFIED BY 'user_password';
GRANT ALL PRIVILEGES ON mydb.* TO 'user_name'@'ip_address';
где
user_name - имя пользователя базы данных
ip_address - ip адрес компьютера, с которого будет осуществляться подключение к базе
user_password - пароль пользователя user_name в базе MySQL
Запрос можно запустить как через phpmyadmin, так и напрямую и из консоли MySQL.

18.

Определяем host и port на котором располагается mysql сервер с БД
Первым делом смотрим что прослушивает наш MySQL:
netstat -nap | grep mysql
tcp
0
0 0.0.0.0:3306
0.0.0.0:*
LISTEN
1666/mysqld
Обратим внимание на 0 0.0.0.0:3306 т.е. из состояния видно, что сервер "слушает" подключения локально, дабы
разрешить удаленные подключения необходимо закомментировать параметр skip-networking и добавить параметр bindaddress в раздел [mysqld] файла my.cnf:
nano /etc/my.cnf
[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
user=mysql
symbolic-links=0
bind-address=xxx.xxx.xx.x
где bind-address, адрес сервера на котором установлен MySQL. Сохраняем файл, перезапускаем сервис mysql:
service mysqld restart
и проверяем состояние:
netstat -nap | grep mysql
tcp
0
0 xxx.xxx.xx.x:3306
0.0.0.0:*
LISTEN
745/mysqld
Проверяем прослушку порта 3306 на сервере БД:
telnet xxx.xxx.xx.x 3306
Примечание: перед проверкой не забываем проверить настройки файервола на БД сервере

19.

Примеры инструментов для удаленного
подключения к БД:
MySQL: Пример удаленного подключения к базе данных на сервере «server90.hosting.reg.ru» под
пользователем «u0015955_default»:
PuTTY: Используется для безопасного подключения с использованием SSH-туннелирования