インデックス最適化(シーンの最適化、失効シーンの最適化、手法の最適化)

10210 ワード
MySQLアーキテクチャ
インデックス最適化可能なシーン
次の図は、この記事の例題で使用されるテーブルの構造と合計行数です.
  
mysql> desc usertb;
+-------------+---------------------+------+-----+---------+----------------+
| Field       | Type                | Null | Key | Default | Extra          |
+-------------+---------------------+------+-----+---------+----------------+
| id          | bigint(20) unsigned | NO   | PRI | NULL    | auto_increment |
| uname       | varchar(20)         | YES  |     | NULL    |                |
| ucreatetime | datetime            | YES  |     | NULL    |                |
| age         | int(11)             | YES  |     | NULL    |                |
+-------------+---------------------+------+-----+---------+----------------+

mysql> select max(id) from usertb;
+----------+
| max(id)  |
+----------+
| 10000000 |
+----------+

1、インデックスはwhere条件を最適化することができ、where条件を使用して5%~20%のデータを取得する場合、実行計画でrowをできるだけ小さくしたい
mysql> explain select * from usertb where id=100;
+----+-------------+--------+------------+-------+---------------+------+---------+-------+------+----------+-------+
| id | select_type | table  | partitions | type  | possible_keys | key  | key_len | ref   | rows | filtered | Extra |
+----+-------------+--------+------------+-------+---------------+------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | usertb | NULL       | const | id            | id   | 8       | const |    1 |   100.00 | NULL  |
+----+-------------+--------+------------+-------+---------------+------+---------+-------+------+----------+-------+

2、インデックスはテーブル接続を最適化することができ、接続列にインデックスがある場合、オプティマイザは駆動テーブルと被駆動テーブルと接続順序を選択することができる
3、インデックスはorder byを最適化することができ、特にorder by limitページングクエリの場合.where条件フィルタの行数が少なく、大量のデータをソートする必要がある場合、接続列にインデックスを作成すると、インデックス列とプライマリ・キー列が取り出され、インデックス列に基づいてソートされるため、order byインデックス列ではパフォーマンスが良い
//uname        

mysql> explain select * from usertb order by uname limit 1,10;
+----+-------------+--------+------------+------+---------------+------+---------+------+---------+----------+----------------+
| id | select_type | table  | partitions | type | possible_keys | key  | key_len | ref  | rows    | filtered | Extra          |
+----+-------------+--------+------------+------+---------------+------+---------+------+---------+----------+----------------+
|  1 | SIMPLE      | usertb | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 9726850 |   100.00 | Using filesort |
+----+-------------+--------+------------+------+---------------+------+---------+------+---------+----------+----------------+


//uname      
mysql> explain select * from usertb order by uname limit 1,10;
+----+-------------+--------+------------+-------+---------------+---------+---------+------+------+----------+-------+
| id | select_type | table  | partitions | type  | possible_keys | key     | key_len | ref  | rows | filtered | Extra |
+----+-------------+--------+------------+-------+---------------+---------+---------+------+------+----------+-------+
|  1 | SIMPLE      | usertb | NULL       | index | NULL          | i_umane | 63      | NULL |   11 |   100.00 | NULL  |
+----+-------------+--------+------------+-------+---------------+---------+---------+------+------+----------+-------+

質問:ユーザーが次のページをブラシしている間に、テーブル全体のスキャンが発生する可能性があります.この場合、テーブル接続で書き換えることができます.
//uname    
mysql> explain select * from usertb order by uname limit 1000000,10;
+----+-------------+--------+------------+------+---------------+------+---------+------+---------+----------+----------------+
| id | select_type | table  | partitions | type | possible_keys | key  | key_len | ref  | rows    | filtered | Extra          |
+----+-------------+--------+------------+------+---------------+------+---------+------+---------+----------+----------------+
|  1 | SIMPLE      | usertb | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 9726850 |   100.00 | Using filesort |
+----+-------------+--------+------------+------+---------------+------+---------+------+---------+----------+----------------+

//         
mysql> explain select * from usertb b
    -> join (select id from usertb order by uname limit 1000000,10) a
    -> on a.id=b.id;
+----+-------------+------------+------------+--------+---------------+---------+---------+------+---------+----------+-------------+
| id | select_type | table      | partitions | type   | possible_keys | key     | key_len | ref  | rows    | filtered | Extra       |
+----+-------------+------------+------------+--------+---------------+---------+---------+------+---------+----------+-------------+
|  1 | PRIMARY     |  | NULL       | ALL    | NULL          | NULL    | NULL    | NULL | 1000010 |   100.00 | NULL        |
|  1 | PRIMARY     | b          | NULL       | eq_ref | id            | id      | 8       | a.id |       1 |   100.00 | NULL        |
|  2 | DERIVED     | usertb     | NULL       | index  | NULL          | i_uname | 63      | NULL | 1000010 |   100.00 | Using index |
+----+-------------+------------+------------+--------+---------------+---------+---------+------+---------+----------+-------------+

質問:合計ページ数を求めるにはどうすればいいですか?
  id      ，       10 ，  
mysql> select (max(id))/10 from usertb;
+--------------+
| (max(id))/10 |
+--------------+
| 1000000.0000 |
+--------------+

4、インデックスはgroupbyを最適化できる
5、インデックスを上書きすることで、テーブルに戻ってデータを取ることを避けることができ、大量のデータにアクセスしても、テーブル全体のスキャンよりずっと良い
6、インデックスはgapロックを最適化することができ、insertにとって、テーブルにプライマリ・キーが必要で、そうでないとgapロックが発生します.delete、updateの場合、where条件には効率的なインデックスが必要です.
7、プライマリ・キー・インデックスの場合、20%を超えても80%を超えても、プライマリ・キー・インデックスの効果は良好である
 
インデックスが無効なシーン
1、SQL文にwhere条件がない、またはwhere条件列にインデックスがない
//     
mysql> explain select * from usertb1 where uname='  1';
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table   | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | usertb1 | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 9973 |    10.00 | Using where |
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------------+

2、SQL文にはwhere条件があり、where条件列にはインデックスがあるが、取ったデータが30%を超えると、全テーブルをスキャンする
mysql> explain select * from usertb where uname like '  1';
+----+-------------+--------+------------+------+---------------+------+---------+------+---------+----------+-------------+
| id | select_type | table  | partitions | type | possible_keys | key  | key_len | ref  | rows    | filtered | Extra       |
+----+-------------+--------+------------+------+---------------+------+---------+------+---------+----------+-------------+
|  1 | SIMPLE      | usertb | NULL       | ALL  | i_uname       | NULL | NULL    | NULL | 9726850 |    20.63 | Using where |
+----+-------------+--------+------------+------+---------------+------+---------+------+---------+----------+-------------+

3、小表にとって、オプティマイザは全表スキャンを行ったほうがいいと思う
4、複数のカラムインデックスの場合、where条件では先頭カラムが表示されないか、カラム間の接続条件はor
5、<>、!=インデックスは無効になります.=番号付けのデータは少ないと考えられます.
//uname    
mysql> explain select * from usertb where uname <> '  1';
+----+-------------+--------+------------+------+---------------+------+---------+------+---------+----------+-------------+
| id | select_type | table  | partitions | type | possible_keys | key  | key_len | ref  | rows    | filtered | Extra       |
+----+-------------+--------+------------+------+---------------+------+---------+------+---------+----------+-------------+
|  1 | SIMPLE      | usertb | NULL       | ALL  | i_uname       | NULL | NULL    | NULL | 9726850 |    70.63 | Using where |
+----+-------------+--------+------------+------+---------------+------+---------+------+---------+----------+-------------+

6、where like '%abc';一般的には全表スキャンを行います
mysql> explain select * from usertb where id like '%1';
+----+-------------+--------+------------+------+---------------+------+---------+------+---------+----------+-------------+
| id | select_type | table  | partitions | type | possible_keys | key  | key_len | ref  | rows    | filtered | Extra       |
+----+-------------+--------+------------+------+---------------+------+---------+------+---------+----------+-------------+
|  1 | SIMPLE      | usertb | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 9726850 |    11.11 | Using where |
+----+-------------+--------+------------+------+---------------+------+---------+------+---------+----------+-------------+

7、is nullは一般的にインデックスを歩きます.テーブルにデータが多くても、これはデータベースのバグです.
mysql> explain select * from usertb where uname is null;
+----+-------------+--------+------------+------+---------------+---------+---------+-------+------+----------+-----------------------+
| id | select_type | table  | partitions | type | possible_keys | key     | key_len | ref   | rows | filtered | Extra                 |
+----+-------------+--------+------------+------+---------------+---------+---------+-------+------+----------+-----------------------+
|  1 | SIMPLE      | usertb | NULL       | ref  | i_uname       | i_uname | 63      | const |    1 |   100.00 | Using index condition |
+----+-------------+--------+------------+------+---------------+---------+---------+-------+------+----------+-----------------------+

インデックス最適化手法
1、explain文は実行計画を見て、type列タイプがall行全表スキャンであるかどうか、表接続である場合、駆動によって返される行数が多すぎるかどうか、被駆動表に主キーがないか、効率的なインデックスがないかどうかを見て、アクセスのコストは全表スキャンである
2、SQLに対して構造分析を行い、主にwhere条件、join接続条件を見る
3、show table status like ‘tbl_name’G、時計の行数を見て、大きい時計なら必ず全表掃面を歩かなければなりません
4、show index from tbl_name;列のインデックスの状況と列の選択性を見て、主に先頭列を見ます
5、select count(distinct tbl_name) from (select * from tbl_name limit 19000,10);列の選択性を見る
『深入浅出mysql』読書ノート
nginx入門から入門へ