MySQLの接頭辞インデックスの使用方法をインスタンスで認識
4386 ワード
今日、テスト環境にインデックスを追加したときに警告が見つかりました.
show create table articleでインデックスとテーブル構造の情報を表示します.
次に、異なる文字セットでテストされませんでした.
�Cエラー�C
成功した
�Cエラー�C
成功した
�Cエラー�C
MySQLのvarcharインデックスは768バイトまたは768/2=384バイトまたは768/3=256バイトのフィールドのみをサポートし、GBKは2バイト、UTF-8は3バイトです.では、私の文字セットはGBKが2バイトでurlが512文字、1024バイトなので、文字列インデックスの制限を超えて警告が出て、mysqlはデフォルトで383(766バイト)の長さのプレフィックスインデックスを作成しました.小さなインデックスサイズは、スペースストレージ、メモリの低下、パフォーマンスの向上に重要な役割を果たすだけでなく、接頭辞インデックスの長さを計算する際に、賢明な選択が必要であることを知っています.どのように賢明ですか.フルインデックス列の選択性:
様々な長さの接頭辞列の選択性を計算します.
85の長さを選択すると、この接頭辞列の選択性は全列の選択性に相当する:alter table article add index ind_article_url(url(85))は、383バイトを接頭辞として選択する必要はありません.しかし、接頭辞インデックスには、クエリ文でorder byとgroup byが接頭辞インデックスに使用できない点が不足しています.
root@test 07:57:52>alter table article drop index ind_article_url;
Query OK, 144384 rows affected (16.29 sec)
Records: 144384 Duplicates: 0 Warnings: 0
root@test 07:58:40>alter table article add index ind_article_url(url);
Query OK, 144384 rows affected, 1 warning (19.52 sec)
Records: 144384 Duplicates: 0 Warnings: 0
root@test 07:59:23>show warnings;
+―――+――+―――――――――――――――――――+
| Level | Code | Message |
+―――+――+―――――――――――――――――――+
| Warning | 1071 | Specified key was too long; max key length is 767 bytes |
+―――+――+―――――――――――――――――――+
1 row in set (0.00 sec)
show create table articleでインデックスとテーブル構造の情報を表示します.
`URL` varchar(512) default NULL COMMENT ‘ url',
……
KEY `ind_article_url` (`URL`(383))
…..
DEFAULT CHARSET=gbk
……
drop table test;
create table test(test varchar(767) primary key)charset=latin5;
次に、異なる文字セットでテストされませんでした.
drop table test;
create table test(test varchar(768) primary key)charset=latin5;
�Cエラー�C
ERROR 1071 (42000): Specified key was too long; max key length is 767 bytes
drop table test;
create table test(test varchar(383) primary key)charset=GBK;
成功した
drop table test;
create table test(test varchar(384) primary key)charset=GBK;
�Cエラー�C
ERROR 1071 (42000): Specified key was too long; max key length is 767 bytes
drop table test;
create table test(test varchar(255) primary key)charset=UTF8;
成功した
drop table test;
create table test(test varchar(256) primary key)charset=UTF8;
�Cエラー�C
ERROR 1071 (42000): Specified key was too long; max key length is 767 bytes
MySQLのvarcharインデックスは768バイトまたは768/2=384バイトまたは768/3=256バイトのフィールドのみをサポートし、GBKは2バイト、UTF-8は3バイトです.では、私の文字セットはGBKが2バイトでurlが512文字、1024バイトなので、文字列インデックスの制限を超えて警告が出て、mysqlはデフォルトで383(766バイト)の長さのプレフィックスインデックスを作成しました.小さなインデックスサイズは、スペースストレージ、メモリの低下、パフォーマンスの向上に重要な役割を果たすだけでなく、接頭辞インデックスの長さを計算する際に、賢明な選択が必要であることを知っています.どのように賢明ですか.フルインデックス列の選択性:
root@test 08:10:35>select count(distinct(url))/count(*) from article;
+――――――――――-+
| count(distinct(url))/count(*) |
+――――――――――-+
| 0.0750 |
+――――――――――-+
様々な長さの接頭辞列の選択性を計算します.
root@test 08:16:41>select count(distinct left(url,76))/count(*) url_76,
-> count(distinct left(url,77))/count(*) url_77,
-> count(distinct left(url,78))/count(*) url_78,
-> count(distinct left(url,79))/count(*) url_79,
-> count(distinct left(url,80))/count(*) url_80,
-> count(distinct left(url,81))/count(*) url_81,
-> count(distinct left(url,82))/count(*) url_82,
-> count(distinct left(url,83))/count(*) url_83,
-> count(distinct left(url,84))/count(*) url_84,
-> count(distinct left(url,85))/count(*) url_85
-> from article;
+――�C+――�C+――�C+――�C+――�C+――�C+――�C+――�C+――�C+――�C+
| url_76 | url_77 | url_78 | url_79 | url_80 | url_81 | url_82 | url_83 | url_84 | url_85 |
+――�C+――�C+――�C+――�C+――�C+――�C+――�C+――�C+――�C+――�C+
| 0.0747 | 0.0748 | 0.0749 | 0.0749 | 0.0749 | 0.0749 | 0.0749 | 0.0749 | 0.0749 | 0.0750 |
+――�C+――�C+――�C+――�C+――�C+――�C+――�C+――�C+――�C+――�C+
1 row in set (1.82 sec)
85の長さを選択すると、この接頭辞列の選択性は全列の選択性に相当する:alter table article add index ind_article_url(url(85))は、383バイトを接頭辞として選択する必要はありません.しかし、接頭辞インデックスには、クエリ文でorder byとgroup byが接頭辞インデックスに使用できない点が不足しています.
root@test 08:49:24>explain select id,url,deleted from article group by url;
+―-+――――-+――――-+――+―――――+――+―――+――+――�C+―――――――――――+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+―-+――――-+――――-+――+―――――+――+―――+――+――�C+―――――――――――+
| 1 | SIMPLE | article | ALL | NULL | NULL | NULL | NULL | 139844 | Using temporary; Using filesort |
+―-+――――-+――――-+――+―――――+――+―――+――+――�C+―――――――――――+
1 row in set (0.00 sec);