Создание и использование простых индексов в MySQL

Правильно расставленные индексы в mysql существенно ускоряют выборку из таблиц, сохраняя и выбирая данные из таблицы в необходимом порядке с более высокой скоростью. Создание первичных, уникальных и полнотекстовых индексов рассматривать пока не будем, итак всё ясно — они должны быть там, где они должны быть короче)) Тут я опишу логику создания простых и составных индексов mysql и порядок следования колонок в составных индексах.

Если выборка из таблицы происходит лишь по 1 колонке или сортируется лишь по 1 колонке, то для неё создается простой индекс, синтаксис такой:

CREATE INDEX index_name ON table_name(col_name);
/* или */
ALTER TABLE table_name ADD INDEX index_name (col_name);

Если выборка по 1 и более колонкам и/или сортируется по 1 и более колонкам, то под запрос создаётся составной индекс для этих колонок, обычно(не факт! см. ниже) в том порядке, в котором они идут в запросе. Синтаксис:

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, определяется количеством записей для этого значения. Чем меньше записей с одинковым значением, тем выше селективность и тем раньше такая колонка должна быть указана в индексе! Другими словами теперь: чем больше записей с разным значением колонки, тем выше селективность для этой колонки и тем раньше её следует указывать в составном индексе!

Определятся селективность просто:

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 скрипт для автоматической расстановки колонок в составных индексах, пользуйтесь на здоровье:

<?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;">&times;</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">&times;</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>

Принцип работы на картинках: