innodbのインデックス構造解析から、インデックスのkey長が長すぎないのはなぜですか?

keyが長すぎると、1つのページに格納できるkeyの数が少なくなり、間接的にインデックスツリーのページ数が多くなり、インデックス階層が増加し、全体的なクエリー変更の効率に影響します.
一、myisamストレージエンジン
1 . テストされたテーブル構造情報

mysql> show create table tb2
Table: tb2
Create Table: CREATE TABLE `tb2` (
`a1` varchar(255) DEFAULT NULL,
`b1` varchar(255) DEFAULT NULL,
`c1` varchar(255) DEFAULT NULL,
`d1` varchar(1000) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

テストインデックス付き

(1)単列インデックスを追加することで成功(warningを報告)を追加できるが,実際には接頭辞インデックスを追加する.

mysql> alter table tb2 add index idx1 (d1);
Query OK, 0 rows affected, 2 warnings (0.00 sec)
Records: 0 Duplicates: 0 Warnings: 0

mysql> show warnings;
+---------+------+----------------------------------------------------------+
| Level | Code | Message |
+---------+------+----------------------------------------------------------+
| Warning | 1071 | Specified key was too long; max key length is 1000 bytes |
| Warning | 1071 | Specified key was too long; max key length is 1000 bytes |
+---------+------+----------------------------------------------------------+
2 rows in set (0.00 sec)

テーブル構造情報:

mysql> show create table tb2
Table: tb2
Create Table: CREATE TABLE `tb2` (
`a1` varchar(255) DEFAULT NULL,
`b1` varchar(255) DEFAULT NULL,
`c1` varchar(255) DEFAULT NULL,
`d1` varchar(1000) DEFAULT NULL,
KEY `idx1` (`d1`(333))
) ENGINE=MyISAM DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

(2)コンビネーションインデックスの追加に失敗します.

mysql> alter table tb2 add index idx1 (a1,b1);
ERROR 1071 (42000): Specified key was too long; max key length is 1000 bytes

分析

myisamストレージエンジンは、インデックスを作成する際に、インデックスキーの長さには厳格な長さ制限があり、すべてのインデックスキーの最大長の合計は1000を超えず、実際のデータ長の合計ではなく、インデックスキーフィールド定義の長さの合計です.
プライマリ文字セットの計算方法:latin 1=1 byte=1 character uft 8=3 byte=1 character gbk=2 byte=1 character
二、innodbストレージエンジン

表構造情報:

mysql> create table tb1 (a1 varchar(255), b1 varchar(255), c1 varchar(255), d1 varchar(1000));
Query OK, 0 rows affected (0.01 sec)

2.コンビネーションインデックスを追加し、waringを報告し、実際にある単列に接頭辞インデックスを追加する

mysql> alter table tb1 add index idx1(a1,b1,c1,d1);

*Query OK, 0 rows affected, 2 warnings (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0*

mysql> show warnings;
+---------+------+---------------------------------------------------------+
| Level | Code | Message |
+---------+------+---------------------------------------------------------+
| Warning | 1071 | Specified key was too long; max key length is 767 bytes |
| Warning | 1071 | Specified key was too long; max key length is 767 bytes |
+---------+------+---------------------------------------------------------+
2 rows in set (0.00 sec)

3.単列索引を追加し、waringを報告し、実際に接頭辞索引を追加する

mysql> alter table tb1 add index idx2(d1);
Query OK, 0 rows affected, 2 warnings (0.02 sec)
Records: 0 Duplicates: 0 Warnings: 0

mysql> show warnings;
+---------+------+---------------------------------------------------------+
| Level | Code | Message |
+---------+------+---------------------------------------------------------+
| Warning | 1071 | Specified key was too long; max key length is 767 bytes |
| Warning | 1071 | Specified key was too long; max key length is 767 bytes |
+---------+------+---------------------------------------------------------+
2 rows in set (0.00 sec)

mysql> show create table tb1
Table: tb1
Create Table: CREATE TABLE `tb1` (
`a1` varchar(255) DEFAULT NULL,
`b1` varchar(255) DEFAULT NULL,
`c1` varchar(255) DEFAULT NULL,
`d1` varchar(1000) DEFAULT NULL,
KEY `idx1` (`a1`,`b1`,`c1`,`d1`(255)),
KEY `idx2` (`d1`(255))
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

分析:

デフォルトでは、InnoDBエンジンの単一フィールドインデックスの長さは最大767バイトで、接頭辞インデックスにも同様の制限があります.UTF-8文字セットを使用すると、各文字が3バイトで格納され、TEXTまたはVARCHARタイプのフィールドに255文字以上のプレフィックスインデックスが作成されると問題が発生します.サーバオプションinnodb_large_prefixを有効にすると、この制限は3072バイトに増加し、テーブルのrow_formatはcompressedまたはdynamicを使用する必要があります.
三、接頭辞索引の使用によるリスク:
INNODBのインデックスは、個々のKeyの最大長が767バイトであることを制限し、この長さを超えると767バイト以下のプレフィックスインデックスを確立する必要があります.
また、BLOBとTEXTタイプのカラムでは、プレフィックスインデックスしか作成できません.
接頭辞インデックスは、インデックスの作成速度と取得速度を向上させることができますが、接頭辞インデックスは使用できません.

インデックス上書きスキャン

インデックスのソート(order by,groupby)

または上記のテストテーブルにあります.

mysql> explain select * from tb1 order by d1;
+----+-------------+-------+------+---------------+------+---------+------+------+----------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+---------------+------+---------+------+------+----------------+
| 1 | SIMPLE | tb1 | ALL | NULL | NULL | NULL | NULL | 5 | Using filesort |
+----+-------------+-------+------+---------------+------+---------+------+------+----------------+
1 row in set (0.00 sec)

mysql> explain select * from tb1 group by d1;
+----+-------------+-------+------+---------------+------+---------+------+------+---------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+---------------+------+---------+------+------+---------------------------------+
| 1 | SIMPLE | tb1 | ALL | NULL | NULL | NULL | NULL | 5 | Using temporary; Using filesort |
+----+-------------+-------+------+---------------+------+---------+------+------+---------------------------------+
1 row in set (0.00 sec)

参照リンク:
https://www.jianshu.com/p/bea78290722c
https://github.com/debitCrossBlockchain/interview__reference/blob/master/01.%E9%98%BF%E9%87%8C%E7%AF%87/1.1.6%20%E4%BB%8Einnodb%E7%9A%84%E7%B4%A2%E5%BC%95%E7%BB%93%E6%9E%84%E5%88%86%E6%9E%90%EF%BC%8C%E4%B8%BA%E4%BB%80%E4%B9%88%E7%B4%A2%E5%BC%95%E7%9A%84%20key%20%E9%95%BF%E5%BA%A6%E4%B8%8D%E8%83%BD%E5%A4%AA%E9%95%BF.md