---
# System prepended metadata

title: MySQL 查詢優化

---

# MySQL Index

## B+Tree

* binary tree

    [Binary tree](https://zh.wikipedia.org/zh-tw/%E4%BA%8C%E5%8F%89%E6%A0%91)

    [demo](https://www.cs.usfca.edu/~galles/visualization/BST.html)
    [AVL demo](https://www.cs.usfca.edu/~galles/visualization/AVLtree.html)

* B tree

    [B tree](https://zh.wikipedia.org/zh-tw/B%E6%A0%91)
    [demo](https://www.cs.usfca.edu/~galles/visualization/BTree.html)

* B+ tree

    [B+ tree](https://zh.wikipedia.org/zh-tw/B+%E6%A0%91)
    [demo](https://www.cs.usfca.edu/~galles/visualization/BPlusTree.html)

    在以上的 __demo__ 增加 __next button__

    ```javascript
    var controls = document.querySelector('#AlgorithmSpecificControls');
    var input = controls.querySelector('input');
    var insert = controls.querySelector('input[type=Button]');
    var next = document.createElement('button');
    var nextTd = document.createElement('td');
    nextTd.appendChild(next);
    var count = 0;
    next.innerText = 'Next';
    next.onclick = function () {
        input.value = ++count;
        insert.click();
    };
    next.disabled = input.disabled;
    insert.parentElement.insertBefore(nextTd, insert)

    var mutationObserver = new MutationObserver((mutations) => {
        mutations.forEach((mutation) => {
            if (mutation) {
                if (mutation.type == 'attributes' && mutation.attributeName == 'disabled') {
                    next.disabled = mutation.target.disabled;
                }
            }
        });
    });

    mutationObserver.observe(input, {
        attributes: true
        , childList: false
        , characterData: false
        , subtree: false
    });
    ```

## Indexs
![image](https://hackmd.io/_uploads/S1L1EYcpR.png)

[indexes](https://dev.mysql.com/doc/refman/5.7/en/innodb-indexes.html)

    
* 主索引 [clustered index](https://dev.mysql.com/doc/refman/5.7/en/glossary.html#glos_clustered_index)

    葉末存放資料

* 索引 Secondary(Non-Clustered) Index

    葉末存放 __Primary Key__，再回主索引查詢資料，所謂的回查。


* [covering index](https://dev.mysql.com/doc/refman/5.7/en/glossary.html#glos_covering_index)
    
    使用的 __Index__ 涵蓋所有查詢結果需要的欄位，所以不用回查。

## Index 設計技巧

* 太多會降低寫入效能
    * 實測，不要亂來，一定是利大於弊。
    * 
* 欄位大小不能太大
* 盡量避免使用常異動的欄位
* 時間盡量以 bigint 格式儲存，效能好，可讀性差
* 隨時間增長的資料表，第一個欄位縮小範圍，第二個欄位為時間欄位
    
    * index(time,group)
        where time between 01 and 05
        and group = 1
        ![image](https://hackmd.io/_uploads/HJhUY8caC.png)

    * index(group,time)
    
        ![image](https://hackmd.io/_uploads/rk-IAIqTR.png)

    
* 百萬筆以下的表，隨意建
* 



## Backward index scan

效能較差
[別人回答的](https://dba.stackexchange.com/questions/199551/why-scanning-an-index-backwards-is-slower)

MySQL 8 有 descending indexes

[descending indexes](https://dev.mysql.com/doc/refman/8.0/en/descending-indexes.html)

[indexs](https://xiaoyu72.com/articles/2d9260c5/)

