Site Tools


mysql

https://dev.mysql.com/doc/refman/5.7/en/glossary.html
https://dev.mysql.com/doc/mysql-enterprise-backup/3.7/en/glossary.html

mysqld --help #check syntax my.cnf

Обнаружение нагрузки → просмотр текущих задач

mysql> show full processlist\G;

Обнаружение нагрузки → Мониторинг долгих запросов

vi  /etc/my.cnf
log_slow_queries = /var/log/mysql/queries-slow.log
long_query_time = 1 

Проверка базы данных

http://phpclub.ru/mysql/doc/using-mysqlcheck.html
MyISAM vs InnoDB

MyISAM – для полнотекстового поиска
InnoDB – для всего остального, так как менее подвержена повреждениям, имеет транзакции…

Кэширование

show variables like '%cache%';
+------------------------------+----------------------+
| Variable_name                | Value                |
+------------------------------+----------------------+
| binlog_cache_size            | 32768                |
| binlog_stmt_cache_size       | 32768                |
| have_query_cache             | YES                  |
| key_cache_age_threshold      | 300                  |
| key_cache_block_size         | 1024                 |
| key_cache_division_limit     | 100                  |
| max_binlog_cache_size        | 18446744073709547520 |
| max_binlog_stmt_cache_size   | 18446744073709547520 |
| query_cache_limit            | 1048576              |
| query_cache_min_res_unit     | 4096                 |
| query_cache_size             | 314572800            |
| query_cache_type             | ON                   |
| query_cache_wlock_invalidate | OFF                  |
| table_definition_cache       | 400                  |
| table_open_cache             | 4                    |
| thread_cache_size            | 0                    |
+------------------------------+----------------------+
  • query_cache_size - Размер кэша
SHOW GLOBAL STATUS LIKE 'Qcache%';
+-------------------------+-----------+
| Variable_name           | Value     |
+-------------------------+-----------+
| Qcache_free_blocks      | 1         |
| Qcache_free_memory      | 212366800 |
| Qcache_hits             | 4830446   |
| Qcache_inserts          | 18678151  |
| Qcache_lowmem_prunes    | 14419894  |
| Qcache_not_cached       | 880499    |
| Qcache_queries_in_cache | 8968      |
| Qcache_total_blocks     | 18084     |
+-------------------------+-----------+
  • Qcache_free_memory - Свободно памяти для кэширования
  • Qcache_lowmem_prunes - Удалено кэша из за нехватки памяти (стремиться к 0)
  • Qcache_queries_in_cache - Запросов в кэше
  • Qcache_not_cached - не кэширующихся запросов
  • Qcache_hits - Успешных обращений к кэшу

изменение конфига

SET GLOBAL max_connections = 150;

SET NAME

Эквивалентно:

SET character_set_client = charset_name;
SET character_set_results = charset_name;
SET character_set_connection = charset_name;

Можно указать binary в качестве кодировки, чтобы перекодирования не производилось.

Устанавливаем размер кэша в 300МБ
set @@global.query_cache_size=300*1024*1024

Проверить кодировку подключения

var_dump(mysql_client_encoding());
$result = mysql_query("show variables like '%character%';", $this->lid); while($row = mysql_fetch_row($result)){var_dump($row);}

Стандартные операции в MySQL

Показать все базы

SHOW DATABASES;

Удалить базу u1234_one

DROP DATABASE `u1234_one`;

Удалить колонку в таблице

ALTER TABLE hhru DROP COLUMN email;

Добавить колонку в таблицу

ALTER TABLE calls ADD calldate datetime NOT NULL default '0000-00-00 00:00:00';

Модифицировать колонку

ALTER TABLE mytable MODIFY COLUMN mycolumn VARCHAR(100)

Показать все таблицы

SHOW TABLES;

Показать всех пользователей

SELECT * FROM mysql.user \G;

Показать права для текущего пользователя

SHOW GRANTS;

Показать версию MySQL

SELECT VERSION();

Изменил привилегии для пользователей?! Тогда!:

FLUSH PRIVILEGES; # применит твои изменения

Изменить кодировку в таблице

ALTER TABLE my_table CONVERT TO CHARACTER SET utf8;

Очистка таблицы

TRUNCATE TABLE yourTable;
echo show tables | mysql -uuser -ppass | grep -i "cach" | awk '{print "TRUNCATE TABLE " $1";"}' | mysql -uuser -ppass #очищаем все таблицы в имене которых "cach"

Копирование таблицы на основе уже созданной

CREATE TABLE recipes_new LIKE production.recipes;
INSERT recipes_new SELECT * FROM production.recipes;

Ограничить количество строк вывода

SELECT * FROM table LIMIT 10

Сортировка прямая и обратная

SELECT id FROM result ORDER BY ID DESC LIMIT 1;
SELECT id FROM result ORDER BY ID ASC LIMIT 1;

Вставить строку

INSERT INTO emails (email, uri, fio) VALUES ("some@inbox.ru", "test", "Constantin Conovaloff");

Вставить несколько строк одновременно

INSERT INTO Table ( Column1, Column2 ) VALUES ( Value1, Value2 ), ( Value1, Value2 )

Показать все столбцы в таблице

SHOW FULL COLUMNS FROM your_table;

Показать параметры с которыми создается таблица

SHOW CREATE TABLE t\G 

Cоздать таблицу

create table emails ( id int auto_increment not null,
                      email varchar(255) not null,
                      uri varchar(255),
                      senddone int not null DEFAULT 0,
                      primary key (id), unique (email));

Таблица с датой

create table times ( calldate DATETIME NOT NULL default '0000-00-00 00:00:00',
                      curdate TIMESTAMP NOT NULL default CURRENT_TIMESTAMP);

Использование и совмещение нескольких таблиц

                                   SELECT
                                      q.id        AS question_id,
                                      q.name      AS question_name,
                                      q.answer    AS answer_text,
                                      w.name      AS question_author
                                   FROM
                                      questions AS q
                                   LEFT JOIN
                                      workers AS w
                                   ON
                                      w.id = q.author;
                         В данном случае, q.uthor получается из таблицы questions, а далее идет запрос к таблице workers

Удалить все таблицы

user=u; pass=p; host=localhost; database=${user};mysqldump -u${user} -p${pass} -h ${host} --add-drop-table --no-data ${database} | grep ^DROP | mysql -u${user} -p${pass} -h ${host} ${database}

Получить размер баз данных

SELECT table_schema                                        "DB Name", 
   Round(Sum(data_length + index_length) / 1024 / 1024, 1) "DB Size in MB" 
FROM   information_schema.tables 
GROUP  BY table_schema;

За интервал времени DATETIME

WHERE date_create >= DATE_FORMAT('$date', '%Y-%m') and date_create <= DATE_FORMAT('$fdate', '%Y-%m')
Меняем root доступ
/etc/init.d/mysql stop
mysqld_safe --skip-grant-tables &
mysql -u root mysql
update user set Password=PASSWORD('ThisMyPassword') where User='root';
flush privileges;
/etc/init.d/mysql restart
mysqldump

Дампим одну базу

mysqldump -uroot -h82.82.82.82 -p database > database.sql

Дампим все базы данных в один файл

mysqldump -uroot -h82.82.82.82 -p -A > all-databases.sql

Дампим выборочное несколько баз в один файл

mysqldump -uroot -h82.82.82.82 -p -B database1 database2 database3 > databases.sql

Дампим все базы данных по отдельности

for db_name in `echo 'SHOW DATABASES' | mysql -BN -uroot -pQWERTY`; do echo $db_name; mysqldump -aeQ -f --add-locks --add-drop-table -uroot -pQWERTY $db_name | gzip > $db_name.gz; done 
  • –default-character-set=charset_name # Указываем кодировку подключения. По умолчанию utf-8 или в более ранних latin1
  • character-sets-dir=/usr/local/mysql/share/mysql/charsets/
  • default-character-set=cp1251

mysqlcheck

Table is marked as crashed and should be repaired

  • Без параметров - только проверка базы на ошибки.
  • -A - Все базы данных
  • -r - чинить
  • -o - оптимизировать #не до конца понятен способ оптимизации
Чиним базу данных user_base
mysqlcheck -uUSER -pPASSWORD --repair --extended user_base
Чиним все доступные базы данных
mysqlcheck -uUSER -pPASSWORD  --repair --extended -A
Чиним табличку TABLE в базе user_base

SQL запросом:

USE user_base REPAIR TABLE TABLE;
кодировка подключения в PHP к MySQL

После mysql_connect:

mysql_query('SET NAMES cp1251');

Или для всего сервера в my.cnf:

default-character-set=cp1251
character-set-server=cp1251
collation-server=cp1251_general_ci
init-connect= SET NAMES cp1251;
skip-character-set-client-handshake
Импорт с указанием кодировки:   mysql --default-character-set=utf8

Экспорт по линии ( построчный )

--extended-insert=false

Примеры

INSERT INTO result(
    worker, 
    questid,
    answerid,
    ticketid,
    dateAnswer)
VALUE(
    43,
    10,
    11,
    (SELECT                               #В инсерте создаем отдельный SELECT запрос
            @ticketID := r.ticketid + 1   #В селекте задаем переменную ticketID для дальнейшего использования.  + инкрементация полученного значения
        FROM result AS r                  #FROM result выдаст ошибку, так как к result уже есть обращение и следует использовать AS some
        ORDER BY ID DESC LIMIT 1),        
    (SELECT @nowTime := NOW())            #Создаем переменную @nowTime для одинакового значения во всем инсерте и меньших количествах вызовов функции.
    ), 
    
    
    (43,                                  #Множественный инсерт
    10,
    11,
    @ticketID,                            #Использование рание созданных переменных
    @nowTime),
    
    (43,
    10,
    11,
    @ticketID,
    @nowTime);

Добавление индексов

ALTER TABLE `TEST` ADD INDEX (`CF_ID`);

Пример:

mysql> explain select * from TEST where CF_ID=8;
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra       |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
|  1 | SIMPLE      | TEST  | ALL  | NULL          | NULL | NULL    | NULL |   30 | Using where |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
1 row in set (0.01 sec)


mysql> ALTER TABLE `TEST` ADD INDEX (`CF_ID`);


mysql> explain select * from TEST where CF_ID=8;
+----+-------------+-------+------+---------------+-------+---------+-------+------+-------+
| id | select_type | table | type | possible_keys | key   | key_len | ref   | rows | Extra |
+----+-------------+-------+------+---------------+-------+---------+-------+------+-------+
|  1 | SIMPLE      | TEST  | ref  | CF_ID         | CF_ID | 4       | const |    1 |       |
+----+-------------+-------+------+---------------+-------+---------+-------+------+-------+
1 row in set (7.91 sec)

Убрать индексы:

 ALTER TABLE `TEST` DROP INDEX `CF_ID`;
  • UNIQUE и PRIMARY KEY также является индексами по умолчанию.

Возможный ошибки:

ERROR 1170 (42000): BLOB/TEXT column 'callid' used in key specification without a key length

Сообщает, что BLOB или TEXT не могут быть индексами, так как имеют рандомную длину.
В этом случае следует использовать ADD FULLTEXT INDEX
Если колонка имеет большой текст, то можно хранить и делать уникальным md5 колонки.
Если колонка имеет все значения похожими (к примеру 1383312588.9068, телефон или т.д.) то колонку стоит сделать varchar(255)

Debug

Ошибки

/usr/libexec/mysqld: Sort aborted

закомментируем в /etc/my.cnf строку
sort_buffer_size = 512K

или увеличиваем значение до 2мб:
sort_buffer_size = 2M

Логи
  • log=/var/log/mysql-queries.log - лог работы
  • log-error=/var/log/mysql-errors.log - лог ошибок
  • log-slow-queries=/var/log/mysql-slow-queries.log - лог медленных запросов

Other

Хранимые процедуры

ссылки:
http://ruseller.com/lessons.php?rub=28&id=1189 - простые примеры создания процедуры

Сам код хранятся в таблице proc базы mysql
Информация о именах хранится в information_schema.ROUTINES
Экспорт средствами mysqldump

thx http://baron.su/archives/135-Hranimye_procedury_i_mysqldump.html

Для тех, кто обладает памятью, лучшей, чем у меня: экспорт хранимых процедур и функций включается параметром --routines, например:
mysqldump --routines --extended-insert berserk > outputfile.sql
Если же база уже перенесена, и не хватает лишь переноса хранимых функций и процедур, можно воспользоваться командой:
mysqldump --routines --no-create-info --no-data --no-create-db --skip-opt berserk > outputfile.sql
Все тоже самое, но без экспорта триггеров.
mysqldump --skip-triggers --routines --no-create-info --no-data --no-create-db --skip-opt berserk > outputfile.sql
Название базы (berserk) выделено курсивом, вероятно, вам потребуется его заменить. Импорт в базу на новом сервере можно осуществить так:
mysql newberserk < outputfile.sql

Тестовый скрипт проверки хранимых процедур

Импортируем test.sql

-- MySQL dump 10.13  Distrib 5.1.70, for debian-linux-gnu (x86_64)
-- ------------------------------------------------------
-- Server version       5.1.70-beget-log
/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
/*!40103 SET TIME_ZONE='+00:00' */;
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;
/*!50003 SET @saved_cs_client      = @@character_set_client */ ;
/*!50003 SET @saved_cs_results     = @@character_set_results */ ;
/*!50003 SET @saved_col_connection = @@collation_connection */ ;
/*!50003 SET character_set_client  = utf8 */ ;
/*!50003 SET character_set_results = utf8 */ ;
/*!50003 SET collation_connection  = utf8_general_ci */ ;
/*!50003 SET @saved_sql_mode       = @@sql_mode */ ;
/*!50003 SET sql_mode              = 'NO_AUTO_VALUE_ON_ZERO' */ ;
DELIMITER ;;
/*!50003 CREATE*/ /*!50020 DEFINER=`constaj0_dle`@`localhost`*/ /*!50003 PROCEDURE `testProc`()
BEGIN
    SELECT * FROM dle_users LIMIT 1;
END */;;

Где constaj0_dle заменяется на корректную базу данных.

Проверяем что процедура загружена:

mysql> select * from information_schema.ROUTINES \G;

Вызываем процедуры

call constaj0_dle.testProc;

Удаляем процедуру

DROP PROCEDURE constaj0_dle.testProc;

MySQL server has gone away

при импорте дампа - В строке, может содержаться более 1 мегабайта. В этом случе, необходимо увеличить значение max_allowed_packet
при работе сайта - mysql_query('set SESSION wait_timeout = 600');
SET SESSION wait_timeout = 3600                   #секунты
SET SESSION max_allowed_packet = 16777216         #байты (16 мегабайт)

Создание пользователя и разграничение доступа

Создаем локального и нелокального пользователя

CREATE USER 'newuser'@'localhost' IDENTIFIED BY 'password';
CREATE USER 'myuser'@'%' IDENTIFIED BY PASSWORD '*HASH';

Смотрим права доступа для пользователей

select * from INFORMATION_SCHEMA.SCHEMA_PRIVILEGES;

Даем права execute для процедуры или функции # еще не тестил

GRANT execute ON PROCEDURE proc TO username;
GRANT execute ON FUNCTION func TO username;

Даем права на все, для процедуры:

grant all on procedure routine.res to user;

Даем этим пользователя права к базам

GRANT ALL ON mydb.* TO 'myuser'@'%';
GRANT ALL ON mydb TO 'myuser'@'%';
GRANT CREATE ON mydb TO 'myuser'@'%';
FLUSH PRIVILEGES;

http://dev.mysql.com/doc/refman/5.1/en/grant.html
http://stackoverflow.com/questions/16287559/mysql-adding-user-for-remote-access

not secure user

CREATE USER 'main'@'%' ;
GRANT ALL PRIVILEGES ON *.* TO 'main'@'%';
FLUSH PRIVILEGES;

Подключение

"SET NAMES cp1251"
"SET CHARACTER SET cp1251"
"SET NAMES utf8"
"SET CHARACTER SET utf8"

mysql_query("SET CHARACTER SET cp1251");
mysql_query("SET NAMES cp1251");

mysql_query("SET CHARACTER SET utf8");
mysql_query("SET NAMES utf8");


--default-character-set=utf8
--default-character-set=cp1251

Индексы

Обычный индекс

Создает еще одну колонку отсортированное в бинарное дерево с указателями на место обращения к основной колонки.

добавление
ALTER TABLE table_name ADD INDEX (`column_name`);
удаление
ALTER TABLE table_name DROP INDEX column_name

Составные индексы

Тоже что и обычный, но B-дерево принимает на вход несколько колонок. Тем при селекте по этим столбцам скорость несколько возврастает.

  1. Индекс по левой колонке\индексу. (col1, col2) подходит для col1 AND col2, col1 но не подходит для col2
  2. Не подходит для OR. Только для AND
добавление
ALTER TABLE table_name ADD INDEX anyIndexName (`column_name`, `col2`);
удаление
ALTER TABLE table_name DROP INDEX anyIndexName

FULLTEXT

Подходит только для полнотекстового поиска. В остальном не увеличивает скорость. Кажется не работает с LIKE

TMP

SET SESSION FOREIGN_KEY_CHECKS = 0; #при возникновение ошибок FOREIGN_KEY
Подзапрос в одной таблице
SELECT login FROM overloads WHERE id IN (select id from overloads WHERE server = "nostromo");
SELECT * FROM overloads WHERE id IN (select id from overloads WHERE server = "nostromo") AND login LIKE "%cav%";
SET @n:=0;
UPDATE table1 AS scr1
SET scr1.rate = (@n:=@n+1)
WHERE scr1.id_tour = '29'
  AND scr.b_id IN (SELECT b_id FROM table_users AS tt)
ORDER BY scr1.money DESC

Посмотреть размер

баз данных

SELECT table_schema                                        "DB Name", 
 Round(Sum(data_length + index_length) / 1024 / 1024, 1) "DB Size in MB" 
FROM   information_schema.tables 
GROUP  BY table_schema; 

таблиц

Discussion

Constantin Conovaloff, 2014/10/21 16:25

Запись абсолютно всех запросов к mysql.

log   = /path/to/query.log  #works for mysql < 5.1.29
general_log_file=/path/to/query.log

SET general_log = 1;

http://stackoverflow.com/questions/6479107/how-to-enable-mysql-query-log
Constantin Conovaloff, 2014/10/31 13:56

Процедура

Так же известны под именем Routines, подпрограммы
Процедура относится сразу ко всем базам данных?
Ограничивается ее использование только пользователем который ее вызывает?

DELIMITER ;;
CREATE DEFINER=`root`@`localhost` FUNCTION `function_name`(cust_id INT(10)) RETURNS float(8,2) <--- Это процедура. Можно смотреть по ключевому слову Function. cust_id - входной парамет. функция возвращает float
    READS SQL DATA
    DETERMINISTIC
BEGIN
...
END ;;

Тригеры

Вьюхи

Бэкапятся

Ничего дополнительного. Поподают в mysqldump без аргументов

Если нужно бэкапить только их, то: экспорт:

database=my_db_name ; mysql INFORMATION_SCHEMA --skip-column-names --batch -e "select table_name from tables where table_type = 'VIEW' and table_schema = '$database'" | xargs mysqldump $database >> /home/dev/konovalov/view.sql

импорт:

mysql database_name < /home/dev/konovalov/view.sql

Присутствие

на уровне базы в виде подобия таблицы. Выглядят как обычные таблицы.

Просмотр

Присутствуют и неотличимы в show tables, но сразу палятся в show FULL tables;

Посмотреть на вьюху можно через

SHOW CREATE VIEW view_name;
Constantin Conovaloff, 2014/11/10 14:34

Есть процедуры (PROCEDURE) а есть функции (FUNCTION) Процедуры вызываем через

call procedure_name(args)

функцию вызываем через

select function_name(args)

функции назначаются на определенные таблицы, которые указаны в information_schema.ROUTINES WHERE ROUTINE_SCHEMA = 'table_name'

Constantin Conovaloff, 2014/11/25 19:09

фитчи

Online DDL – как минимум, умеет проставлять индексы в таблицы не блокируя ее. Вероятно, много чего еще.

Constantin Conovaloff, 2014/11/25 19:20

Посмотреть размер

Баз данных

SELECT table_schema "DB Name", Round(Sum(data_length + index_length) / 1024 / 1024, 1) "DB Size in MB" FROM information_schema.tables GROUP BY table_schema;

таблиц (поменять database_name):

SELECT table_name , concat( round( data_length / ( 1024 *1024 ) , 2 ) , 'M' ) data_length, concat( round( index_length / ( 1024 *1024 ) , 2 ) , 'M' ) index_length, concat( round( round( data_length + index_length ) / ( 1024 *1024 ) , 2 ) , 'M') total_size FROM information_schema.TABLES WHERE TABLE_SCHEMA = 'database_name'
Constantin Conovaloff, 2014/12/23 17:21

установка 5.6

Constantin Conovaloff, 2014/12/23 18:30

Быстрый импорт

Если при импорте, CPU не нагружается, то можно посмотреть в iotop и если наибольшую нагрузку создает [jbd2/sda3-8] вместе с mysql, то можно отключить коммит изменений после каждого импорта и некоторые чеки:

mysql> set autocommit=0; set unique_checks=0; set foreign_key_checks=0;
mysql> source /path/to/dump.sql
mysql> commit; set unique_checks=1; set foreign_key_checks=1;
Constantin Conovaloff, 2015/01/21 14:44

групировка по дням

GROUP by YEAR(tm), MONTH(tm), DAY(tm)
Constantin Conovaloff, 2015/01/30 18:41

http://sqlize.com/0fZI6J0Oi5 – jsfiddle для mysql

Constantin Conovaloff, 2015/01/31 18:09

USE INDEX

После FROM:

SELECT * FROM table USE INDEX (login) WHERE some > 10;
Constantin Conovaloff, 2015/01/31 18:47
root@mysql> SELECT COUNT(id) FROM statistic WHERE `date` > '2015-01-25';
+-----------+
| COUNT(id) |
+-----------+
|    227192 |
+-----------+
1 row in set (0.00 sec)

root@mysql> RESET QUERY CACHE;
Query OK, 0 rows affected (0.00 sec)

root@mysql> SELECT COUNT(id) FROM statistic WHERE `date` > '2015-01-25';
+-----------+
| COUNT(id) |
+-----------+
|    227192 |
+-----------+
1 row in set (3.53 sec)

Но вместо этого, можно указывать в запросе “не использовать” кэш

root@mysql> SELECT SQL_NO_CACHE COUNT(id) FROM statistic WHERE `date` > '2015-01-25';
Constantin Conovaloff, 2015/02/01 16:31

Новая фитча

EXPLAIN FORMAT = JSON SELECT ...
Constantin Conovaloff, 2015/02/02 14:30

скрыть вывод на время дебага

> pager > /dev/null
PAGER set to '> /dev/null'

> select 1;
1 row in set (0.00 sec)

> pager;
Default pager wasn't set, using stdout.

> select 1;
+---+
| 1 |
+---+
| 1 |
+---+
1 row in set (0.00 sec)
Constantin Conovaloff, 2015/04/07 12:10

Использвоание переменных в запросе

mysql> select (@user:=email) AS email, CONCAT(@user, 'wow') from emails LIMIT 1;
+--------------------------+-----------------------------+
| email                    | CONCAT(@user, 'wow')        |
+--------------------------+-----------------------------+
| some@yandex.ru |  some@yandex.ruwow |
+--------------------------+-----------------------------+
Получаем переменную с логинами, которые следует обойти:
SELECT (@user:=cm1.cust_login) AS cust_login FROM customers_moved AS cm1 GROUP BY cm1.cust_login LIMIT 10


Добавляем в нее информацию, что обходить нужно только тех, у кого не все впорядке с переносом:
SELECT (@user:=cm1.cust_login) AS cust_login FROM customers_moved AS cm1 WHERE cm1.move_user = 'N' OR cm1.move_file = 'N' OR cm1.move_mysql = 'N' OR cm1.move_base = 'N' OR cm1.update_ip = 'N' GROUP BY cm1.cust_login LIMIT 10

====================

Берем последнюю запись пользователя:
SELECT * FROM (SELECT * FROM `customers_moved` WHERE cust_login = @user ORDER BY tm DESC LIMIT 1) AS last;

Если пользователь в последней записи перенесся, то возвращаем логин:
SELECT * FROM (SELECT * FROM `customers_moved` WHERE cust_login = @user ORDER BY tm DESC LIMIT 1) AS last WHERE last.move_user = 'Y' AND last.move_file = 'Y' AND last.move_mysql = 'Y' AND last.move_base = 'Y' AND last.update_ip = 'Y';

====================

Теперь высыпаем это все в сковородку и перемешываем:

SELECT (@user:=cm1.cust_login) AS cust_login FROM customers_moved AS cm1 WHERE cm1.move_user = 'N' OR cm1.move_file = 'N' OR cm1.move_mysql = 'N' OR cm1.move_base = 'N' OR cm1.update_ip = 'N' AND cm1.cust_login = (SELECT last.cust_login FROM (SELECT * FROM `customers_moved` WHERE cust_login = @user ORDER BY tm DESC LIMIT 1) AS last WHERE last.move_user = 'Y' AND last.move_file = 'Y' AND last.move_mysql = 'Y' AND last.move_base = 'Y' AND last.update_ip = 'Y') LIMIT 10
Constantin Conovaloff, 2015/09/21 17:15

Join

Есть несколько видов Джойнов:

  1. LEFT JOIN – берет целевую таблицу (указанную в FROM) и добавляет к ней значения из второстепенной таблице. Через WHERE another_table.status == error, можно отсечь значения из целевой таблицы
  2. RIGHT JOIN – Тоже самое что и LEFT JOIN, но берет второстепенную таблицу указанную в RIGHT JOIN и добавляет к ней значения из целевой таблицы (указанной в FROM)
  3. INNER JOIN – Берет значение из обоих таблиц, только там, где они точно соединены с друг другом
  4. FULL OUTER JOIN – Берет значения из обоих таблиц, даже если они не соединены с друг другом
Constantin Conovaloff, 2018/05/29 19:11

профилирование

profile.sql
mysql> RESET QUERY CACHE;
Query OK, 0 ROWS affected, 1 warning (0.00 sec)
 
mysql> SET profiling = 1;                                                                                                                                                                                                                                                      Query OK, 0 ROWS affected, 1 warning (0.00 sec)
 
mysql> SELECT places.* FROM `places` LEFT JOIN `partners` ON `places`.`partner_id` = `partners`.`id` WHERE `places`.`is_active` = 1 AND `places`.`latitude` BETWEEN 59.838721066274 AND 59.868945733654 AND `places`.`longitude` BETWEEN 30.220369606632 AND 30.263284950871 AND `places`.`deleted_at` IS NULL;
1 ROW IN SET (0.01 sec)
 
 
mysql> SHOW PROFILES;
+----------+------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Query_ID | Duration   | Query                                                                                                                                                                                                                                                                                                   |
+----------+------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
|        1 | 0.00468425 | SELECT places.* FROM `places` LEFT JOIN `partners` ON `places`.`partner_id` = `partners`.`id` WHERE `places`.`is_active` = 1 AND `places`.`latitude` BETWEEN 59.838721066274 AND 59.868945733654 AND `places`.`longitude` BETWEEN 30.220369606632 AND 30.263284950871 AND `places`.`deleted_at` IS NULL |
+----------+------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

https://dev.mysql.com/doc/refman/5.5/en/show-profile.html

You could leave a comment if you were logged in.
mysql.txt · Last modified: 2018/03/21 13:03 by conovaloff

Page Tools