Tabela MySQL zawierająca 100 000 rekordów jest często sprawdzana

Mam jedną bazę danych zawierającą około 100 tabel do przechowywania różnego rodzaju informacji.

Najważniejszą tabelą jest nasza tabela zamówień, która jest używana do przechowywania zamówień klientów i zawiera obecnie ponad 100 000 rekordów.

Ta tabela jest najczęściej wyszukiwaną tabelą w naszej bazie danych, zawierającą różne informacje potrzebne z pulpitów nawigacyjnych zamówień w czasie rzeczywistym, statystyk, analiz itp.

Regularnie monitoruję bazę danych i włączam powolne zapytania w bazie danych w celu śledzenia problemów.

Używam skryptów takich jak mysqltuner do codziennego wypluwania zapytania.

Używam również mysqlsla do zbierania informacji o 10 najwolniejszych zapytaniach w naszej bazie danych.

sample stat
Count         : 11.48k  (30.66%)
Time          : 19.623758 s total, 1.709 ms avg, 239 µs to 2.475017 s max  (18.64%)
  95% of Time : 5.246833 s total, 481 µs avg, 239 µs to 1.095 ms max
Lock Time (s) : 14.460071 s total, 1.259 ms avg, 53 µs to 2.462555 s max  (41.38%)
  95% of Lock : 806.43 ms total, 74 µs avg, 53 µs to 137 µs max
Rows sent     : 1 avg, 0 to 9 max  (0.99%)
Rows examined : 6 avg, 1 to 28 max  (0.15%)

Większość najwolniejszych zapytań dotyczy tabeli zamówień wspomnianej powyżej. Używam MyISAM jako mojego silnika pamięci, więc możliwe problemy to:

1. Blokowanie stołu
2. Problemy z indeksowaniem

Jak mogłem poprawić te statystyki, mam indeksowanie tych tabel i ciągle je poprawiam, aby poprawić zapytania dotyczące odczytu.

Schemat tabeli

`orderid` int(11) NOT NULL AUTO_INCREMENT,
`cityid` tinyint(3) unsigned NOT NULL DEFAULT '1',
 `model_type` tinyint(1) unsigned DEFAULT '1',
`userid` int(11) DEFAULT NULL,
`usertype` char(1) DEFAULT NULL,
`time` time DEFAULT NULL,
`ordercode` char(8) DEFAULT NULL,
`restid` smallint(3) unsigned NOT NULL,
`areaid` smallint(3) unsigned DEFAULT NULL,
`restname` varchar(50) DEFAULT NULL,
`date` date NOT NULL,
`del_time` time NOT NULL,
`status` tinyint(3) unsigned NOT NULL,
`amount` float NOT NULL,
`deliverycharge` smallint(4) unsigned DEFAULT '0',
`tax` float NOT NULL,
`total` float NOT NULL,
`extras` varchar(255) DEFAULT NULL,
`requests` varchar(255) DEFAULT NULL,
`discount` float DEFAULT NULL,
`rdiscount` float DEFAULT NULL,
`reason` varchar(255) DEFAULT NULL,
`rest_order` tinyint(1) unsigned DEFAULT NULL,
`admin_user` varchar(25) DEFAULT NULL,
`mode` char(1) NOT NULL,
`priority_order` tinyint(1) unsigned DEFAULT '0',
`payment_mode` tinyint(1) unsigned DEFAULT '0',
`km` tinyint(3) unsigned DEFAULT NULL,
`order_type` tinyint(1) NOT NULL DEFAULT '1',
`coupon_discount` smallint(3) DEFAULT '0',
`pickup_time` time NOT NULL,
PRIMARY KEY (`orderid`),
KEY `cityid` (`cityid`),
KEY `date_3` (`date`,`status`,`mode`),
KEY `orderid` (`orderid`),
KEY `time` (`time`),
KEY `userid` (`userid`,`usertype`),
KEY `restid` (`restid`,`date`,`status`)

powolne zapytanie dziennika

SELECT `a`.`orderid`, `a`.`date`, `a`.`status`, `a`.`restname`, `a`.`admin_user`, `a`.`model_type`, `b`.`name` as cityname
FROM `tk_order_queue` AS a
INNER JOIN `tk_cities` AS b ON `a`.`cityid` = `b`.`id`
WHERE `a`.`date` =  '2012-06-30'
AND `a`.`status` =  0
AND `a`.`mode` =  1
ORDER BY `a`.`orderid` desc;

Będziesz musiał porównać klauzule WHERE oraz instrukcje GROUP BY i ORDER BY wszystkich swoich zapytań, aby upewnić się, że bieżące indeksy mogą je obsługiwać w planach EXPLAIN.

Wczoraj odpowiedziałem na to pytanie: InnoDB vs MyISAM z wieloma indeksami

W tym pytaniu zasugerowałem zrobienie czegoś ze stołem MyISAM, co również możesz zrobić

ALTER TABLE orders ROW_FORMAT=Fixed;

To potraktuje wszystkie VARCHAR jako CHAR. Każdy rząd będzie miał dokładnie tę samą długość. Zwiększy to miejsce na dysku o 80% -100%. Tabela nadyma się do maksymalnego rozmiaru dla układu wierszy razy liczba wierszy. Twój stół może mieć rozmiar podwójny lub potrójny.

Gdzie jest korzyść? Twoja tabela MyISAM zostanie następnie odczytana / zapisana w dowolnym miejscu od 20% - 30% szybciej bez zmiany czegokolwiek innego.

Nauczyłem się tego na stronach 72, 73 z MySQL Database Design and Tuning .

Pisałem o tym w przeszłości:

• Który jest szybszy, InnoDB lub MyISAM? (3 maja 2012 r.)
• Który silnik pamięci masowej mysql wybrać? (02 maja 2012)
• Best of MyISAM i InnoDB (20 września 2011 r.)
• Który DBMS jest dobry do superszybkich odczytów i prostej struktury danych? (12 sierpnia 2011 r.)
• Jaki wpływ na wydajność ma użycie CHAR vs VARCHAR na polu o stałym rozmiarze? (10 maja 2011 r.)