|
Задавайте вопросы, мы ответим
Вы не зашли.
Страниц: 1
Здравствуйте.
Данный вопрос является достаточно сложным для меня, поэтому прощу помощи у опытных программистов.
Сут в следующем:
Есть такие запросы (встречаются они на разных старницах):
SELECT * FROM payment WHERE wait = 1 and status != 3 SELECT * FROM payment WHERE id_partner = $id and summa != 0 SELECT * FROM payment WHERE summa != 0 SELECT * FROM payment WHERE status = 0 SELECT count(*) FROM payment WHERE wait = 2 and status != 0
1) Как в этом случае использовать индексы?
Можно ли так:
Это решит проблему оптимизации, если таблица будет модержать несколько десятков тысяч записей?
KEY multi (wait, status) поле статус используется и здесь KEY new (status) и здесь. Это допустимо? KEY multi2 (id_partner, summa) поле сумма здесь KEY new (summa) и здесь, это нормально?
2) Какого придела стоит придерживаться по количеству SELECT запросов на странице?
У меня обячно бывает 2 -а разных запроса, например такие:
SELECT * FROM partner WHERE login = '$login' AND password = '$password' SELECT * FROM message WHERE status = 1
3) Поля login и password имеют текстовый тип данных (TINYTEXT). Как в этом случае поступить с индексами?
SELECT * FROM partner WHERE login = '$login' AND password = '$password'
Так будет правильно : KEY multi3 (login(10), password(10) )
4) На странице встречается такой запрос:
SELECT * FROM partner WHERE text != '-'
(поле text имеет тип данных TEXT, здесь храниться сообщение от администратора, по умолчанию в это поле заноситься знак прочерка '-')
Индекс для поля можно добавить такой: KEY text (text(10))?
А если база данных огромная, то стоит ли ввести ещё одно дополнительное поле (числовое), например hide TINYINT, куда записывать значение либо 0 (сообщение от админа отсутствует) либо 1 (есть сообщение от админа), т.е., таким образом, осуществлять поиск не по текстовому полю text, а уже по числовому и создать для него индекс, а не для поля text, так ведь будет быстрее?
Пожалуйста, подскажите, мне решение проблемы.
Я очень надеюсь на Вашу помощь.
Заранее большое Вам спасибо
Неактивен
1. Если status!=3 отвечает значительно доле записей, то включать status в индекс не имеет смысла, оптимизатор не будет его использовать, а выполнит перебор. То же относится к другим индексам, созданным ради !=. Посмотрите тему http://sqlinfo.ru/forum/viewtopic.php?id=151
Смотрите EXPLAIN ваш_запрос, увидите в каких случаях MySQL использует созданные индексы и какова длина использованного индекса
2. Предел определяется Вашей задачей. MySQL тут ничего не навязывает. Все еще зависит от того, насколько быстрые запросы.
3. username уже уникален, поэтому достаточно KEY(username). Добавив password Вы не увеличили cardinality индекса (число различных элементов), но увеличили его размер.
4. Если сообщения админа никогда не начинаются с прочерка, то можно создать ключ длиной 1 байт KEY(text(1))
Более корректно их хранить в отдельной таблице вместе с временем, именем админа и.т.д.
Неактивен
СПАСИБО ВАМ большое за ответ.
Я ещё новичок и ВАШ форум меня просто выручает.
Я хотел бы уточнить некоторые моменты:
rgbeast написал:
1. Если status!=3 отвечает значительно доле записей, то включать status в индекс не имеет смысла, оптимизатор не будет его использовать, а выполнит перебор. То же относится к другим индексам, созданным ради !=. Посмотрите тему http://sqlinfo.ru/forum/viewtopic.php?id=151
Я внимательно прочитал статью, вроде всё понятно. Но относительно моего случая, как я читал в литературе стоит создавать индекс и желательно составной, если после WHERE содержит несколько условий! Как я понял в моём случае нужно создавать только ключ по полю wait? А как в этом случае будет с производительностью? Почему оптимизатор не будет использовать status (доля записей значительная)?
2. Предел определяется Вашей задачей. MySQL тут ничего не навязывает. Все еще зависит от того, насколько быстрые запросы.
Здесь всё понятно.
3. username уже уникален, поэтому достаточно KEY(username). Добавив password Вы не увеличили cardinality индекса (число различных элементов), но увеличили его размер.
Нужно задавать индекс именно так KEY(username) или так KEY(username (10) ), ведь поле текстовое?
4. Если сообщения админа никогда не начинаются с прочерка, то можно создать ключ длиной 1 байт KEY(text(1))
Более корректно их хранить в отдельной таблице вместе с временем, именем админа и.т.д.
Прочерк я записываю по умолчанию, я читал, что при больших таблицах поля, которые содержат пустоту (NULL) обрабатываются медленнее, исходя из этого я помещаю знак прочерка, а как было бы правильнее?
Если хранить в отдельной таблице, то придётся использовать многотабличный запрос, для того, чтобы уведомить пользователя о наличии сообщения от админа. А ведь в случае использования многотабличных запросов больших баз данных это уменьшает скорость. Ведь одиночный запрос обрабатывается быстрее.
Скажите, пожалуйста, использование в запросах конструкций != как-то влияет на производительность.
Или стоит придерживаться строго конструкции =
P/S Скажите, пожалуйста, Вы не пишите книги, я бы купил у Вас. Может быть Вы посоветуете что-нибудь.
Отредактированно wizard (05.04.2008 11:34:04)
Неактивен
Я внимательно прочитал статью, вроде всё понятно. Но относительно моего случая, как я читал в литературе стоит создавать индекс и желательно составной, если после WHERE содержит несколько условий! Как я понял в моём случае нужно создавать только ключ по полю wait? А как в этом случае будет с производительностью? Почему оптимизатор не будет использовать status (доля записей значительная)?
Рассмотрим для начала случай одиночного ключа. Пусть есть поле status, принимающее значения 1,2,3,4 приблизительно равномерно. Есть запрос SELECT * FROM tbl WHERE status=2
В таблице есть ключ KEY(status)
У оптимизатора 2 пути
1. прочитать всю таблицу, проверяя status (называется full table scan)
2. пойти по ключу, выбрать записи со status=2, затем считать эти записи
Во втором случае обращение к меньшему количеству записей, но это обращение непоследовательное по дисковому чтению. Часто, считать всю таблицу проще, чем прочитать часть таблицу, но отрывками, перемещая головку по диску.
mysql> CREATE TABLE tbl (id INT auto_increment PRIMARY KEY, status INT, key(staus)); Query OK, 0 rows affected (0.07 sec) mysql> insert into tbl (status) VALUES (1),(2),(3),(4); Query OK, 4 rows affected (0.00 sec) mysql> insert into tbl (status) SELECT t1.status from tbl t1, tbl t2; Query OK, 16 rows affected (0.00 sec) Records: 16 Duplicates: 0 Warnings: 0 mysql> insert into tbl (status) SELECT t1.status from tbl t1, tbl t2; Query OK, 400 rows affected (0.01 sec) Records: 400 Duplicates: 0 Warnings: 0 mysql> insert into tbl (status) SELECT t1.status from tbl t1, tbl t2; Query OK, 176400 rows affected (6.61 sec) Records: 176400 Duplicates: 0 Warnings: 0 mysql> explain SELECT * FROM tbl WHERE status=3; +----+-------------+-------+------+---------------+--------+---------+-------+-------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+------+---------------+--------+---------+-------+-------+-------------+ | 1 | SIMPLE | tbl | ref | status | status | 5 | const | 56358 | Using where | +----+-------------+-------+------+---------------+--------+---------+-------+-------+-------------+ 1 row in set (0.01 sec) mysql> explain SELECT * FROM tbl WHERE status in (3,4); +----+-------------+-------+------+---------------+------+---------+------+--------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+------+---------------+------+---------+------+--------+-------------+ | 1 | SIMPLE | tbl | ALL | status | NULL | NULL | NULL | 176820 | Using where | +----+-------------+-------+------+---------------+------+---------+------+--------+-------------+ 1 row in set (0.00 sec) mysql> explain SELECT * FROM tbl WHERE status!=3; +----+-------------+-------+------+---------------+------+---------+------+--------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+------+---------------+------+---------+------+--------+-------------+ | 1 | SIMPLE | tbl | ALL | status | NULL | NULL | NULL | 176820 | Using where | +----+-------------+-------+------+---------------+------+---------+------+--------+-------------+ 1 row in set (0.00 sec)
В примере видно, что четверть таблицы оптимизатор соглашается обойти по ключу, а большую часть - нет (предпочитает полный перебор)
username можете сделать varchar(50), тогда и ключ можно прямо на username создавать. Для уменьшения объема ключа, можно создать его на первые 10 символов username, как Вы и предложили. varchar() работает быстрее чем текст, так как хранится там же, где и другие данные таблицы (text - хранится в отдельной области)
!= или = определяется Вашей задачей, а не MySQL
P.S. написать книгу большой труд, из известных мне наиболее полезная MySQL 5.0 Certification Guide, доступная только на английском языке. Если английским владеете, поищите ее в инете.
Неактивен
Спасибо огромное за пример, это для меня очень поучительно.
Спасибо за книгу.
Вы рассмотрели случай одиночного ключа KEY(status), но всё же как быть с условием
SELECT * FROM payment WHERE wait = 1 and status != 3
и подобным этому.
Нужен тут составной ключ или нет? У меня мало опыта, а Вы профессионал. Разъясните мне, пожалуйста.
И ещё один момент:
у меня поле username типа TINYTEXT, а поле varchar(50) в данном случае использовать лучше?
Я всегда стараюсь использовать тип TINYTEXT и если очень нужно тип TEXT
Как мне известно, для больших таблиц следует избегать использования таких типов как varchar, text, blob, т.к. работа со столбцами фиксированной длины осуществляется много быстрее, чем со столбцами переменной длины. Это верно?
А как насчёт использования пустых текстовых полей (NULL) при большой базе данных? Стоит ли писать что-то в эти поля по умолчанию?
Неактивен
Вы рассмотрели случай одиночного ключа KEY(status), но всё же как быть с условием
SELECT * FROM payment WHERE wait = 1 and status != 3
То же самое. Сначала делается выборка по первой части ключа wait=1, затем для того, что осталось оптимизатор решает применять ли ключ. Просто проверьте, создайте составной ключ, сделайте EXPLAIN запросу и посмотрите key_len (длину используемого ключа), увидите использует или нет.
TINYTEXT - это по сути BLOB. Фиксированное поле это char(100), но оно не всегда быстрее. varchar - оптимальный выбор по-умолчанию
NULL - просто дополнительный бит, хранящийся в таблице, так что если и замедляет, то немножко. Не надо использовать, если не нужен, а если нужен по логике, то наоборот будет удобно.
Неактивен
Отлично, спасибо, многое прояснили.
Вот хотел последнее уточнить у Вас.
Если ключ не используется то это значение NULL, в этом случае использование составного ключа не приносит должного эффекта, т.е. создавать одиночный ключ?
Отредактированно wizard (05.04.2008 12:55:52)
Неактивен
Можете создавать составной, оптимизатор вправе использовать его первую часть.
Неактивен
Простите, сразу не сообразил, ещё такой вопрос....
При создании больших баз данных, как стоит относиться к количеству полей в таблице?
т.е. лучше по-возможности разбивать на несколько таблиц и работать потом с многотабличными запросами или всё же пусть таблица будет иметь 30 полей? Я понимаю, что это, зависит от задачи, но всё же посоветуйте как поступать лучше.
Огромное Вам спасибо за помощь. Вы настоящий профессионал и полностью ответили на все мои вопросы. Ваш форум является уникальным, спасибо Вам.
Неактивен
Если обращаетесь ко всем полям равномерно, то разбивать нет смысла. Если самые частые запросы только к нескольким полям, то можно разбить (но делать это только после того, как убедитесь, что действительно есть проблема). Преждевременная оптимизация - корень всех зол.
Неактивен
Зависит от конкретной ситуации. Например, если у Вас большое количество повторяющихся полей, то возможно имеет смысл провести нормализацию. Посмотрите тему http://sqlinfo.ru/forum/viewtopic.php?id=209
Неактивен
rgbeast написал:
key_len (длину используемого ключа), увидите использует или нет.
не могли бы вы по подробнее...
Чем значение в этом поле больше тем хуже?
Неактивен
Тем лучше 
Неактивен
paulus написал:
Тем лучше
так что именно означает это поле? скажите где почитать
Неактивен
Представьте себе составной ключ из двух полей. Пусть для простоты будут целыми
числами (4 байта). Максимальная длина строки в индексе, стало быть, 8 байт.
Если MySQL не может использовать весь индекс целиком, он может попробовать
использовать левую часть индекса (например, 4 байта — первое поле). Соответственно,
если Вы видите число меньшее длины строки индекса, — это говорит, что в конкретном
запросе индекс используется не так эффективно, как мог бы.
Неактивен
Страниц: 1
