Правильно расставленные индексы в mysql существенно ускоряют выборку из таблиц, сохраняя и выбирая данные из таблицы в необходимом порядке с более высокой скоростью. Создание первичных, уникальных и полнотекстовых индексов рассматривать пока не будем, итак всё ясно — они должны быть там, где они должны быть короче)) Тут я опишу логику создания простых и составных индексов mysql и порядок следования колонок в составных индексах.
Если выборка из таблицы происходит лишь по 1 колонке или сортируется лишь по 1 колонке, то для неё создается простой индекс, синтаксис такой:
1 2 3 | CREATE INDEX index_name ON table_name(col_name); /* или */ ALTER TABLE table_name ADD INDEX index_name (col_name); |
Если выборка по 1 и более колонкам и/или сортируется по 1 и более колонкам, то под запрос создаётся составной индекс для этих колонок, обычно(не факт! см. ниже) в том порядке, в котором они идут в запросе. Синтаксис:
1 2 3 | CREATE INDEX index_name ON table_name(col_name_1, col_name_2, col_name_N); /* или */ ALTER TABLE table_name ADD INDEX index_name (col_name_1, col_name_2, col_name_N); |
Однако, чтобы использовать возможности индексирования на полную катушку, не всегда следует создавать индексы в порядке следования их в запросе.
Селективность колонок, которые указываем в порядке первыми в составных индексах mysql, определяется количеством записей для этого значения. Чем меньше записей с одинковым значением, тем выше селективность и тем раньше такая колонка должна быть указана в индексе! Другими словами теперь: чем больше записей с разным значением колонки, тем выше селективность для этой колонки и тем раньше её следует указывать в составном индексе!
Определятся селективность просто:
1 2 3 | SELECT COUNT(*) as `selectivity` FROM (SELECT * FROM `table_name` GROUP BY `col_name_1`) as `sel`; SELECT COUNT(*) as `selectivity` FROM (SELECT * FROM `table_name` GROUP BY `col_name_2`) as `sel`; SELECT COUNT(*) as `selectivity` FROM (SELECT * FROM `table_name` GROUP BY `col_name_N`) as `sel`; |
Чем выше результаты этих запросов для каждой колонки, тем раньше её указываем в составном индексе. Вот и всё. Логика проста как мычание. Не надо заниматься глупостями и считать байты по колонкам. И вообще, это не сложно, сперва прочекать на порядок следования, а затем добавить составные индексы в нужном порядке. Проверка запроса на использование индексации выполняется командой EXPLAIN , а чтобы настроить логгирование медленных запросов и не использующих индексации обязательно почитайте про mysql_slow.log, настройте кеш запросов в mysql и ваша бд станет резкой как понос.
А чтобы не чекать всё ручками моя лень написала замечательный php скрипт для автоматической расстановки колонок в составных индексах, пользуйтесь на здоровье:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 181 182 183 184 185 186 187 188 189 190 191 192 193 194 195 196 197 198 199 200 201 202 203 204 205 206 207 208 209 210 211 212 213 214 215 216 217 218 219 220 221 222 223 224 225 226 227 228 229 230 231 232 233 234 235 236 237 238 239 240 241 242 243 244 245 246 247 248 249 250 251 252 253 254 255 256 257 258 259 260 261 262 263 264 265 266 267 268 269 270 271 272 273 274 275 276 277 278 279 280 281 282 283 284 285 286 287 288 289 290 291 292 293 294 295 296 297 298 299 300 | <?php ob_start(); session_start(); header("Cache-Control: no-cache"); header("Expires: ".gmdate("D, d M Y H:i:s", time()-86400)." GMT"); class DB { protected $connection; public $insert_id; public $affected_rows; public function __construct($host, $username, $password, $db_name) { $this->connection = @new mysqli($host, $username, $password, $db_name); if (!$this->connection) { throw new Exception('Could not connect to DB '); } if ($this->connection->connect_error) { die('Ошибка подключения (' . $this->connection->connect_errno . ') ' . $this->connection->connect_error); } $this->connection->set_charset("utf8"); $this->connection->query('SET time_zone="' . date('P') . '"'); } public function query($sql) { if (!$this->connection) { return false; } $result = $this->connection->query($sql); if (mysqli_error($this->connection)) { throw new Exception(mysqli_error($this->connection)); } $this->insert_id = $this->connection->insert_id; $this->affected_rows = $this->connection->affected_rows; return $result; } public function esc($str) { $str = strtr($str, array( chr(0) => '', chr(1) => '', chr(2) => '', chr(3) => '', chr(4) => '', chr(5) => '', chr(6) => '', chr(7) => '', chr(8) => '', chr(9) => '', chr(11) => '', chr(12) => '', chr(14) => '', chr(15) => '', chr(16) => '', chr(17) => '', chr(18) => '', chr(19) => '', chr(20) => '', chr(21) => '', chr(22) => '', chr(23) => '', chr(24) => '', chr(25) => '', chr(26) => '', chr(27) => '', chr(28) => '', chr(29) => '', chr(30) => '', chr(31) => '' )); return mysqli_escape_string($this->connection, $str); } } if (isset($_POST['db_host'])) { $_SESSION = $_POST; $db = new DB($_POST['db_host'], $_POST['db_user'], $_POST['db_pass'], $_POST['db_name']); if (isset($_POST['new_connect'])) { $q = $db->query("SHOW TABLES"); echo '<div class="form-group mt-2"> <select onchange="selectTable(this.value)" name="db_table" class="custom-select custom-select-sm"> <option value="" selected="">Выберете таблицу</option>'; while ($table = $q->fetch_array()) { echo '<option value="'.$table[0].'">'.$table[0].'</option>'; } exit('</select></div><div id="columns"></div>'); } elseif (isset($_POST['show_columns'])) { $q = $db->query("SHOW INDEXES FROM `".$db->esc($_POST['show_columns'])."`"); echo '<table class="table table-striped"><thead><tr>'; $names = $q->fetch_assoc(); foreach ($names as $key => $value) { echo '<th>'.$key.'</th>'; } $q->data_seek(0); echo '</tr></thead><tbody>'; while ($row = $q->fetch_assoc()) { echo '<tr>'; foreach ($row as $key => $value) { echo '<td>'.$value.($key == 'Key_name' ? '<button onclick=" $(\'#delKey .modal-body\').html(\'ALTER TABLE `'.$db->esc($_POST['show_columns']).'` DROP INDEX `'.$value.'`\'); $(\'#delKey\').modal(); " type="button" class="close" style="font-size:0.9rem;">×</button>' : '').'</td>'; } echo '</tr>'; } echo '</tbody></table>'; $q = $db->query("SHOW COLUMNS FROM `".$db->esc($_POST['show_columns'])."`"); echo '<form id="cols"><table class="table table-striped"><thead><tr>'; $names = $q->fetch_assoc(); foreach ($names as $key => $value) { echo '<th>'.$key.'</th>'; } echo '<th>Selectivity</th>'; $q->data_seek(0); echo '</tr></thead><tbody>'; while ($row = $q->fetch_assoc()) { echo '<tr>'; $i = 0; $sel = 0; foreach ($row as $value) { if ($i == 0) { $sel = $db->query("SELECT COUNT(*) as `selectivity` FROM (SELECT * FROM `".$db->esc($_POST['show_columns'])."` GROUP BY `".$value."`) as `sel`")->fetch_object()->selectivity; echo '<td><div class="custom-control custom-checkbox"> <input onchange="changeCols()" type="checkbox" class="custom-control-input" id="'.$value.'" name="cols['.$value.']"> <label class="custom-control-label" for="'.$value.'">'.$value.'</label> </div></td>'; } else { echo '<td>'.$value.'</td>'; } $i++; } echo '<td>'.$sel.'</td></tr>'; } exit('</tbody></table></form><div id="selectivity"></div>'); } elseif (isset($_POST['cols'])) { $cols = array(); foreach ($_POST['cols'] as $key => $value) { $s = $db->query("SELECT COUNT(*) as `selectivity` FROM (SELECT * FROM `".$db->esc($_POST['db_table'])."` GROUP BY `".$db->esc($key)."`) as `sel`")->fetch_object()->selectivity; $cols[$s.$key] = $key; } krsort($cols, SORT_NUMERIC); echo '<form id="addindex"> <div class="input-group input-group-sm mb-5"> <div class="input-group-prepend"> <span class="input-group-text">SQL</span> </div> <input type="text" class="form-control" name="sql" value="ALTER TABLE `' . $db->esc($_POST['db_table']) . '` ADD INDEX `'.implode('_', array_unique(explode('_', implode('_', $cols)))).'` (`' . implode('`, `',$cols) . '`);"> <div class="input-group-append"> <button onclick="addIndex()" type="button" class="btn btn-primary">+</button> </div> </div> </form>'; exit; } elseif (isset($_POST['sql'])) { $db->query($_POST['sql']); exit; } exit; } ?><!DOCTYPE html> <html lang="ru"> <head> <meta charset="UTF-8"> <meta name="viewport" content="width=device-width, initial-scale=1.0"> <meta http-equiv="X-UA-Compatible" content="ie=edge"> <title>index-creater</title> <link rel="stylesheet" href="https://stackpath.bootstrapcdn.com/bootstrap/4.3.1/css/bootstrap.min.css"> <style> .preloader-body{display:none;position:fixed;left:0px;top:0px;right:0px;z-index:2147483647;width:100%;height:100%;background:rgba(0, 0, 0, 0.1);} .cssload-loader{position:absolute;left:calc(50% - 100px);top:calc(50% - 100px);width:200px;height:200px;border-radius:50%;} .cssload-inner{position:absolute;width:100%;height:100%;box-sizing:border-box;border-radius:50%}.cssload-inner.cssload-one{left:0;top:0;animation:cssload-rotate-one 1.15s linear infinite;border-bottom:3px solid #000}@keyframes cssload-rotate-one{0%{transform:rotateX(35deg) rotateY(-45deg) rotateZ(0)}100%{transform:rotateX(35deg) rotateY(-45deg) rotateZ(360deg)}}.cssload-inner.cssload-two{right:0;top:0;animation:cssload-rotate-two 1.15s linear infinite;border-right:3px solid #000}@keyframes cssload-rotate-two{0%{transform:rotateX(50deg) rotateY(10deg) rotateZ(0)}100%{transform:rotateX(50deg) rotateY(10deg) rotateZ(360deg)}}.cssload-inner.cssload-three{right:0;bottom:0;animation:cssload-rotate-three 1.15s linear infinite;border-top:3px solid #000}@keyframes cssload-rotate-three{0%{transform:rotateX(35deg) rotateY(55deg) rotateZ(0)}100%{transform:rotateX(35deg) rotateY(55deg) rotateZ(360deg)}}.hide{display:none;} .table td, .table th{padding:2px;border:1px solid #dee2e6;font-size:12px;} .custom-control-label::before,.custom-control-label::after{top:0;cursor:pointer;} .custom-control-label{cursor:pointer;} .custom-control{min-height:0;} </style> <script src="https://code.jquery.com/jquery-3.4.1.min.js"></script> </head> <body> <div class="preloader-body"> <div class="cssload-loader"> <div class="cssload-inner cssload-one"></div> <div class="cssload-inner cssload-two"></div> <div class="cssload-inner cssload-three"></div> </div> </div> <div class="container-fluid"> <form id="connect" class="mt-2"> <div class="input-group input-group-sm"> <div class="input-group-prepend"> <span class="input-group-text">БД</span> </div> <input type="text" class="form-control" placeholder="Хост" name="db_host" value="<?=(isset($_SESSION['db_host']) ? $_SESSION['db_host'] : 'localhost')?>"> <input type="text" class="form-control" placeholder="Пользователь" name="db_user" value="<?=(isset($_SESSION['db_user']) ? $_SESSION['db_user'] : '')?>"> <input type="password" class="form-control" placeholder="Пароль" name="db_pass" value="<?=(isset($_SESSION['db_pass']) ? $_SESSION['db_pass'] : '')?>"> <input type="text" class="form-control" placeholder="Имя базы" name="db_name" value="<?=(isset($_SESSION['db_name']) ? $_SESSION['db_name'] : '')?>"> <div class="input-group-append"> <button type="button" class="btn btn-primary">Connect</button> </div> </div> </form> <div id="results"></div> </div> <div class="modal fade" id="delKey"> <div class="modal-dialog modal-dialog-centered modal-sm"> <div class="modal-content"> <div class="modal-header"> <h4 class="modal-title">Подтвердите</h4> <button type="button" class="close" data-dismiss="modal">×</button> </div> <div class="modal-body"></div> <div class="modal-footer"> <button onclick="rmIndex()" type="button" class="btn btn-danger" data-dismiss="modal">Да</button> <button type="button" class="btn btn-secondary" data-dismiss="modal">Нет</button> </div> </div> </div> </div> <script src="https://code.jquery.com/jquery-3.4.1.min.js"></script> <script src="https://cdnjs.cloudflare.com/ajax/libs/popper.js/1.14.7/umd/popper.min.js"></script> <script src="https://stackpath.bootstrapcdn.com/bootstrap/4.3.1/js/bootstrap.min.js"></script> <script> $("#connect button").click(() => { $(".preloader-body").show(); $.post({ url: "?", data: $("#connect").serialize() + "&new_connect", dataType: "html", cache: false }) .always(res => { $(".preloader-body").hide(); $("#results").html(res); }) }); function selectTable (val) { if (!val) { return $("#columns").empty(); } $(".preloader-body").show(); $.post({ url: "?", data: $("#connect").serialize() + "&show_columns=" + val, dataType: "html", cache: false }) .always(res => { $(".preloader-body").hide(); $("#columns").html(res); }) } function changeCols (val) { $(".preloader-body").show(); $.post({ url: "?", data: $("#connect").serialize() + "&" + $("#cols").serialize() + "&db_table=" + $("#results select[name=db_table]").val(), dataType: "html", cache: false }) .always(res => { $(".preloader-body").hide(); $("#selectivity").html(res); }) } function addIndex () { $("#addindex button").hide(); $(".preloader-body").show(); $.post({ url: "?", data: $("#connect").serialize() + "&" + $("#addindex").serialize(), dataType: "html", cache: false }) .always(res => { $("#results select[name=db_table]").trigger("change"); $("#addindex button").show(); }) } function rmIndex() { $(".preloader-body").show(); $.post({ url: "?", data: $("#connect").serialize() + "&sql=" + $("#delKey .modal-body").html(), dataType: "html", cache: false }) .always(res => { $("#results select[name=db_table]").trigger("change"); }) } </script> </body> </html> |
Принцип работы на картинках:

