MariaDB(MySQL)에서의 PRIMARY KEY와 INDEX KEY의 속도비교

아래와 같은 모양의 테이블이 있다

bXlzcWw+IHNob3cgY3JlYXRlIHRhYmxlIHRlc3Q7DQorLS0tLS0tLSstLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tDQp8IFRhYmxlIHwgQ3JlYXRlIFRhYmxlDQorLS0tLS0tLSstLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tDQp8IHRlc3QgIHwgQ1JFQVRFIFRBQkxFIGB0ZXN0YCAoDQogIGBpZGAgdmFyY2hhcig1KSBDT0xMQVRFIHV0ZjhfdW5pY29kZV9jaSBOT1QgTlVMTCwNCiAgYGcxYCB2YXJjaGFyKDUpIENPTExBVEUgdXRmOF91bmljb2RlX2NpIE5PVCBOVUxMLA0KICBgZzJgIHZhcmNoYXIoNSkgQ09MTEFURSB1dGY4X3VuaWNvZGVfY2kgTk9UIE5VTEwsDQogIFBSSU1BUlkgS0VZIChgaWRgKSwNCiAgS0VZIGBnMWAgKGBnMWApLA0KICBLRVkgYGcyYCAoYGcyYCkNCikgRU5HSU5FPUlubm9EQiBERUZBVUxUIENIQVJTRVQ9dXRmOCBDT0xMQVRFPXV0ZjhfdW5pY29kZV9jaSB8DQorLS0tLS0tLSstLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tDQoxIHJvdyBpbiBzZXQgKDAuMDAgc2VjKQ==


    mysql> show create table test;
    +-------+----------------------------------------------------------
    | Table | Create Table
    +-------+----------------------------------------------------------
    | test | CREATE TABLE `test` (
    `id` varchar(5) COLLATE utf8_unicode_ci NOT NULL,
    `g1` varchar(5) COLLATE utf8_unicode_ci NOT NULL,
    `g2` varchar(5) COLLATE utf8_unicode_ci NOT NULL,
    PRIMARY KEY (`id`),
    KEY `g1` (`g1`),
    KEY `g2` (`g2`)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci |
    +-------+----------------------------------------------------------
    1 row in set (0.00 sec)

테이블 안에 내용은 아래와 같다

bXlzcWw+IHNlbGVjdCAqIGZyb20gdGVzdCBsaW1pdCAxMDsNCistLS0tLS0tKy0tLS0tLS0rLS0tLS0tLSsNCnwgaWQgICAgfCBnMSAgICB8IGcyICAgIHwNCistLS0tLS0tKy0tLS0tLS0rLS0tLS0tLSsNCnwgMDAwMmIgfCAzQndSQiB8IGo4TWVZIHwNCnwgMDAwNGggfCBBazVhRSB8IDN2Wm9PIHwNCnwgMDAwNjcgfCBrNWZ2YiB8IG5VZXFIIHwNCnwgMDAwOXkgfCBIaWE4WiB8IEhHazV3IHwNCnwgMDAwRTUgfCBmWFhrWCB8IHBjSTZ6IHwNCnwgMDAwZnEgfCAwTzRHYiB8IHVXNTBsIHwNCnwgMDAwZ1EgfCBEMU5wdCB8IGlOcHBWIHwNCnwgMDAwa1IgfCBXbXZYZyB8IGtlbHhwIHwNCnwgMDAwbVggfCByeVBOYyB8IGtDcTdFIHwNCnwgMDAwU0ogfCBsT2dzNSB8IE9wSkFmIHwNCistLS0tLS0tKy0tLS0tLS0rLS0tLS0tLSsNCjEwIHJvd3MgaW4gc2V0ICgwLjAwIHNlYyk=


    mysql> select * from test limit 10;
    +-------+-------+-------+
    | id | g1 | g2 |
    +-------+-------+-------+
    | 0002b | 3BwRB | j8MeY |
    | 0004h | Ak5aE | 3vZoO |
    | 00067 | k5fvb | nUeqH |
    | 0009y | Hia8Z | HGk5w |
    | 000E5 | fXXkX | pcI6z |
    | 000fq | 0O4Gb | uW50l |
    | 000gQ | D1Npt | iNppV |
    | 000kR | WmvXg | kelxp |
    | 000mX | ryPNc | kCq7E |
    | 000SJ | lOgs5 | OpJAf |
    +-------+-------+-------+
    10 rows in set (0.00 sec)

자료의 양은 316만 9천건,InnoDB의 경우 count(*) 는 자료수가 많을수록 수행속도가 느려진다..

bXlzcWw+IHNlbGVjdCBjb3VudCgqKSBmcm9tIHRlc3Q7DQorLS0tLS0tLS0tLSsNCnwgY291bnQoKikgfA0KKy0tLS0tLS0tLS0rDQp8ICAzMTY5MzA1IHwNCistLS0tLS0tLS0tKw0KMSByb3cgaW4gc2V0ICgxLjkxIHNlYyk=


    mysql> select count(*) from test;
    +----------+
    | count(*) |
    +----------+
    | 3169305 |
    +----------+
    1 row in set (1.91 sec)

해보고자 하는건 다음 두 쿼리이다. 두 쿼리의 속도차이가 있을까 빠를까느릴까 하는것에 대해 확인해보고자 한다

bXlzcWw+IHNlbGVjdCAqIGZyb20gdGVzdCB3aGVyZSBpZCA9ICcwMDBTSic7DQpteXNxbD4gc2VsZWN0ICogZnJvbSB0ZXN0IHdoZXJlIGcyID0gJ2lOcHBWJzs=


    mysql> select * from test where id = '000SJ';
    mysql> select * from test where g2 = 'iNppV';

결과는 다음과 같다

bXlzcWw+IHNlbGVjdCAqIGZyb20gdGVzdCB3aGVyZSBpZCA9ICcwMDBTSic7DQorLS0tLS0tLSstLS0tLS0tKy0tLS0tLS0rDQp8IGlkICAgIHwgZzEgICAgfCBnMiAgICB8DQorLS0tLS0tLSstLS0tLS0tKy0tLS0tLS0rDQp8IDAwMFNKIHwgbE9nczUgfCBPcEpBZiB8DQorLS0tLS0tLSstLS0tLS0tKy0tLS0tLS0rDQoxIHJvdyBpbiBzZXQgKDAuMDAgc2VjKQ0KDQpteXNxbD4gc2VsZWN0ICogZnJvbSB0ZXN0IHdoZXJlIGcyID0gJ2lOcHBWJzsNCistLS0tLS0tKy0tLS0tLS0rLS0tLS0tLSsNCnwgaWQgICAgfCBnMSAgICB8IGcyICAgIHwNCistLS0tLS0tKy0tLS0tLS0rLS0tLS0tLSsNCnwgMDAwZ1EgfCBEMU5wdCB8IGlOcHBWIHwNCistLS0tLS0tKy0tLS0tLS0rLS0tLS0tLSsNCjEgcm93IGluIHNldCAoMC4wMCBzZWMp


    mysql> select * from test where id = '000SJ';
    +-------+-------+-------+
    | id | g1 | g2 |
    +-------+-------+-------+
    | 000SJ | lOgs5 | OpJAf |
    +-------+-------+-------+
    1 row in set (0.00 sec)

    mysql> select * from test where g2 = 'iNppV';
    +-------+-------+-------+
    | id | g1 | g2 |
    +-------+-------+-------+
    | 000gQ | D1Npt | iNppV |
    +-------+-------+-------+
    1 row in set (0.00 sec)

id like 로도 검색해보았다

bXlzcWw+IHNlbGVjdCAqIGZyb20gdGVzdCB3aGVyZSBpZCBsaWtlICd4YWJjSSc7DQorLS0tLS0tLSstLS0tLS0tKy0tLS0tLS0rDQp8IGlkICAgIHwgZzEgICAgfCBnMiAgICB8DQorLS0tLS0tLSstLS0tLS0tKy0tLS0tLS0rDQp8IHhhYmNJIHwgRGdKWTcgfCBFbjZyWCB8DQorLS0tLS0tLSstLS0tLS0tKy0tLS0tLS0rDQoxIHJvdyBpbiBzZXQgKDAuMDAgc2VjKQ0KDQpteXNxbD4gc2VsZWN0ICogZnJvbSB0ZXN0IHdoZXJlIGlkIGxpa2UgJ3hhYmNJJSc7DQorLS0tLS0tLSstLS0tLS0tKy0tLS0tLS0rDQp8IGlkICAgIHwgZzEgICAgfCBnMiAgICB8DQorLS0tLS0tLSstLS0tLS0tKy0tLS0tLS0rDQp8IHhhYmNJIHwgRGdKWTcgfCBFbjZyWCB8DQorLS0tLS0tLSstLS0tLS0tKy0tLS0tLS0rDQoxIHJvdyBpbiBzZXQgKDAuMDAgc2VjKQ0KDQpteXNxbD4gc2VsZWN0ICogZnJvbSB0ZXN0IHdoZXJlIGlkIGxpa2UgJyV4YWJjSSc7DQorLS0tLS0tLSstLS0tLS0tKy0tLS0tLS0rDQp8IGlkICAgIHwgZzEgICAgfCBnMiAgICB8DQorLS0tLS0tLSstLS0tLS0tKy0tLS0tLS0rDQp8IHhhYmNJIHwgRGdKWTcgfCBFbjZyWCB8DQorLS0tLS0tLSstLS0tLS0tKy0tLS0tLS0rDQoxIHJvdyBpbiBzZXQgKDMuNjEgc2VjKQ0KDQpteXNxbD4gc2VsZWN0ICogZnJvbSB0ZXN0IHdoZXJlIGlkIGxpa2UgJ3hhYmMlJzsNCistLS0tLS0tKy0tLS0tLS0rLS0tLS0tLSsNCnwgaWQgICAgfCBnMSAgICB8IGcyICAgIHwNCistLS0tLS0tKy0tLS0tLS0rLS0tLS0tLSsNCnwgeGFiYzUgfCBoSEdmaSB8IGNHY1F5IHwNCnwgWGFCQzkgfCBjMGg3aSB8IHZGWTVIIHwNCnwgeGFiY0kgfCBEZ0pZNyB8IEVuNnJYIHwNCnwgeGFiY2wgfCB3SGppOSB8IFJxQlRDIHwNCnwgeGFiY1YgfCB4OENDbCB8IHNIZ3BxIHwNCnwgeGFiY3cgfCBZNmZLSSB8IFJtMmZ3IHwNCistLS0tLS0tKy0tLS0tLS0rLS0tLS0tLSsNCjYgcm93cyBpbiBzZXQgKDAuMDAgc2VjKQ0KDQpteXNxbD4=


    mysql> select * from test where id like 'xabcI';
    +-------+-------+-------+
    | id | g1 | g2 |
    +-------+-------+-------+
    | xabcI | DgJY7 | En6rX |
    +-------+-------+-------+
    1 row in set (0.00 sec)

    mysql> select * from test where id like 'xabcI%';
    +-------+-------+-------+
    | id | g1 | g2 |
    +-------+-------+-------+
    | xabcI | DgJY7 | En6rX |
    +-------+-------+-------+
    1 row in set (0.00 sec)

    mysql> select * from test where id like '%xabcI';
    +-------+-------+-------+
    | id | g1 | g2 |
    +-------+-------+-------+
    | xabcI | DgJY7 | En6rX |
    +-------+-------+-------+
    1 row in set (3.61 sec)

    mysql> select * from test where id like 'xabc%';
    +-------+-------+-------+
    | id | g1 | g2 |
    +-------+-------+-------+
    | xabc5 | hHGfi | cGcQy |
    | XaBC9 | c0h7i | vFY5H |
    | xabcI | DgJY7 | En6rX |
    | xabcl | wHji9 | RqBTC |
    | xabcV | x8CCl | sHgpq |
    | xabcw | Y6fKI | Rm2fw |
    +-------+-------+-------+
    6 rows in set (0.00 sec)

    mysql>

g2 like 로도 검색해보았다

bXlzcWw+IHNlbGVjdCAqIGZyb20gdGVzdCB3aGVyZSBnMiBsaWtlICdjR2NReSc7DQorLS0tLS0tLSstLS0tLS0tKy0tLS0tLS0rDQp8IGlkICAgIHwgZzEgICAgfCBnMiAgICB8DQorLS0tLS0tLSstLS0tLS0tKy0tLS0tLS0rDQp8IHhhYmM1IHwgaEhHZmkgfCBjR2NReSB8DQorLS0tLS0tLSstLS0tLS0tKy0tLS0tLS0rDQoxIHJvdyBpbiBzZXQgKDAuMDAgc2VjKQ0KDQpteXNxbD4gc2VsZWN0ICogZnJvbSB0ZXN0IHdoZXJlIGcyIGxpa2UgJ2NHY1F5JSc7DQorLS0tLS0tLSstLS0tLS0tKy0tLS0tLS0rDQp8IGlkICAgIHwgZzEgICAgfCBnMiAgICB8DQorLS0tLS0tLSstLS0tLS0tKy0tLS0tLS0rDQp8IHhhYmM1IHwgaEhHZmkgfCBjR2NReSB8DQorLS0tLS0tLSstLS0tLS0tKy0tLS0tLS0rDQoxIHJvdyBpbiBzZXQgKDAuMDAgc2VjKQ0KDQpteXNxbD4gc2VsZWN0ICogZnJvbSB0ZXN0IHdoZXJlIGcyIGxpa2UgJyVjR2NReSc7DQorLS0tLS0tLSstLS0tLS0tKy0tLS0tLS0rDQp8IGlkICAgIHwgZzEgICAgfCBnMiAgICB8DQorLS0tLS0tLSstLS0tLS0tKy0tLS0tLS0rDQp8IHhhYmM1IHwgaEhHZmkgfCBjR2NReSB8DQorLS0tLS0tLSstLS0tLS0tKy0tLS0tLS0rDQoxIHJvdyBpbiBzZXQgKDMuNzcgc2VjKQ0KDQpteXNxbD4gc2VsZWN0ICogZnJvbSB0ZXN0IHdoZXJlIGcyIGxpa2UgJ2NHY1ElJzsNCistLS0tLS0tKy0tLS0tLS0rLS0tLS0tLSsNCnwgaWQgICAgfCBnMSAgICB8IGcyICAgIHwNCistLS0tLS0tKy0tLS0tLS0rLS0tLS0tLSsNCnwgaEtZM2wgfCBWbVBERCB8IENHY1FpIHwNCnwgdnFhOTAgfCBaUWlwcCB8IGNHQ3FKIHwNCnwgWFZDa2IgfCBxelNCUCB8IENnQ3F0IHwNCnwgeGFiYzUgfCBoSEdmaSB8IGNHY1F5IHwNCnwgZGNCRmQgfCBIZThyZSB8IENHQ1FaIHwNCistLS0tLS0tKy0tLS0tLS0rLS0tLS0tLSsNCjUgcm93cyBpbiBzZXQgKDAuMDAgc2VjKQ0KDQpteXNxbD4=


    mysql> select * from test where g2 like 'cGcQy';
    +-------+-------+-------+
    | id | g1 | g2 |
    +-------+-------+-------+
    | xabc5 | hHGfi | cGcQy |
    +-------+-------+-------+
    1 row in set (0.00 sec)

    mysql> select * from test where g2 like 'cGcQy%';
    +-------+-------+-------+
    | id | g1 | g2 |
    +-------+-------+-------+
    | xabc5 | hHGfi | cGcQy |
    +-------+-------+-------+
    1 row in set (0.00 sec)

    mysql> select * from test where g2 like '%cGcQy';
    +-------+-------+-------+
    | id | g1 | g2 |
    +-------+-------+-------+
    | xabc5 | hHGfi | cGcQy |
    +-------+-------+-------+
    1 row in set (3.77 sec)

    mysql> select * from test where g2 like 'cGcQ%';
    +-------+-------+-------+
    | id | g1 | g2 |
    +-------+-------+-------+
    | hKY3l | VmPDD | CGcQi |
    | vqa90 | ZQipp | cGCqJ |
    | XVCkb | qzSBP | CgCqt |
    | xabc5 | hHGfi | cGcQy |
    | dcBFd | He8re | CGCQZ |
    +-------+-------+-------+
    5 rows in set (0.00 sec)

    mysql>