[테스트 환경]
OS : CentOS7
DB : MariaDB 10.1.12
-- 테이블 및 인덱스 정보 MariaDB [northwind]> desc Products; +-----------------+---------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +-----------------+---------------+------+-----+---------+----------------+ | ProductID | int(11) | NO | PRI | NULL | auto_increment | | ProductName | varchar(40) | NO | MUL | NULL | | | SupplierID | int(11) | YES | MUL | NULL | | | CategoryID | int(11) | YES | MUL | NULL | | | QuantityPerUnit | varchar(20) | YES | | NULL | | | UnitPrice | decimal(10,4) | YES | | 0.0000 | | | UnitsInStock | smallint(2) | YES | | 0 | | | UnitsOnOrder | smallint(2) | YES | | 0 | | | ReorderLevel | smallint(2) | YES | | 0 | | | Discontinued | bit(1) | NO | | b'0' | | +-----------------+---------------+------+-----+---------+----------------+ 10 rows in set (0.00 sec) MariaDB [northwind]> show index from Products; +----------+------------+------------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | +----------+------------+------------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ | Products | 0 | PRIMARY | 1 | ProductID | A | 77 | NULL | NULL | | BTREE | | | | Products | 1 | ProductName | 1 | ProductName | A | 77 | NULL | NULL | | BTREE | | | | Products | 1 | FK_Products_Categories | 1 | CategoryID | A | 19 | NULL | NULL | YES | BTREE | | | | Products | 1 | FK_Products_Suppliers | 1 | SupplierID | A | 77 | NULL | NULL | YES | BTREE | | | +----------+------------+------------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ 4 rows in set (0.00 sec) MariaDB [northwind]> desc Suppliers; +--------------+-------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +--------------+-------------+------+-----+---------+----------------+ | SupplierID | int(11) | NO | PRI | NULL | auto_increment | | CompanyName | varchar(40) | NO | MUL | NULL | | | ContactName | varchar(30) | YES | | NULL | | | ContactTitle | varchar(30) | YES | | NULL | | | Address | varchar(60) | YES | | NULL | | | City | varchar(15) | YES | | NULL | | | Region | varchar(15) | YES | | NULL | | | PostalCode | varchar(10) | YES | MUL | NULL | | | Country | varchar(15) | YES | | NULL | | | Phone | varchar(24) | YES | | NULL | | | Fax | varchar(24) | YES | | NULL | | | HomePage | mediumtext | YES | | NULL | | +--------------+-------------+------+-----+---------+----------------+ 12 rows in set (0.00 sec) MariaDB [northwind]> show index from Suppliers; +-----------+------------+-------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | +-----------+------------+-------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ | Suppliers | 0 | PRIMARY | 1 | SupplierID | A | 29 | NULL | NULL | | BTREE | | | | Suppliers | 1 | CompanyName | 1 | CompanyName | A | 29 | NULL | NULL | | BTREE | | | | Suppliers | 1 | PostalCode | 1 | PostalCode | A | 29 | NULL | NULL | YES | BTREE | | | +-----------+------------+-------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ 3 rows in set (0.00 sec)
-- Driving 테이블 컬럼에 대해서 order by
MariaDB [northwind]> analyze
-> select a.ProductName
-> ,b.CompanyName
-> from Products a
-> straight_join Suppliers b on(b.SupplierID = a.SupplierID)
-> order by a.ProductID;
+------+-------------+-------+--------+-----------------------+---------+---------+------------------------+------+--------+----------+------------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | r_rows | filtered | r_filtered | Extra |
+------+-------------+-------+--------+-----------------------+---------+---------+------------------------+------+--------+----------+------------+-------------+
| 1 | SIMPLE | a | index | FK_Products_Suppliers | PRIMARY | 4 | NULL | 77 | 77.00 | 100.00 | 100.00 | Using where |
| 1 | SIMPLE | b | eq_ref | PRIMARY | PRIMARY | 4 | northwind.a.SupplierID | 1 | 1.00 | 100.00 | 100.00 | |
+------+-------------+-------+--------+-----------------------+---------+---------+------------------------+------+--------+----------+------------+-------------+
2 rows in set (0.00 sec)
-- 조건절이 존재하지 않는데 Using where가 나타나는 이유
MariaDB [northwind]> analyze format=json
-> select a.ProductName
-> ,b.CompanyName
-> from Products a
-> straight_join Suppliers b on(b.SupplierID = a.SupplierID)
-> order by a.ProductID;
-- "attached_condition": "(a.SupplierID is not null)" 조건절이 추가됨 -> 스토리지 엔진이 테이블의 데이터를 MariaDB 엔진으로 넘겨주면, MariaDB 엔진이 해당 조건에 맞는 데이터만 필터링 함.
+-----------------------------------------------------------+
| ANALYZE |
+-----------------------------------------------------------+
| {
"query_block": {
"select_id": 1,
"r_loops": 1,
"r_total_time_ms": 0.4606,
"table": {
"table_name": "a",
"access_type": "index",
"possible_keys": ["FK_Products_Suppliers"],
"key": "PRIMARY",
"key_length": "4",
"used_key_parts": ["ProductID"],
"r_loops": 1,
"rows": 77,
"r_rows": 77,
"r_total_time_ms": 0.1284,
"filtered": 100,
"r_filtered": 100,
"attached_condition": "(a.SupplierID is not null)"
},
"table": {
"table_name": "b",
"access_type": "eq_ref",
"possible_keys": ["PRIMARY"],
"key": "PRIMARY",
"key_length": "4",
"used_key_parts": ["SupplierID"],
"ref": ["northwind.a.SupplierID"],
"r_loops": 77,
"rows": 1,
"r_rows": 1,
"r_total_time_ms": 0.2228,
"filtered": 100,
"r_filtered": 100
}
}
} |
+-----------------------------------------------------------+
1 row in set (0.01 sec)
-- Driven 테이블 컬럼에 대해서 order by
MariaDB [northwind]> analyze
-> select a.ProductName
-> ,b.CompanyName
-> from Products a
-> straight_join Suppliers b on(b.SupplierID = a.SupplierID)
-> order by b.CompanyName;
+------+-------------+-------+--------+-----------------------+---------+---------+------------------------+------+--------+----------+------------+----------------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | r_rows | filtered | r_filtered | Extra |
+------+-------------+-------+--------+-----------------------+---------+---------+------------------------+------+--------+----------+------------+----------------------------------------------+
| 1 | SIMPLE | a | ALL | FK_Products_Suppliers | NULL | NULL | NULL | 77 | 77.00 | 100.00 | 100.00 | Using where; Using temporary; Using filesort |
| 1 | SIMPLE | b | eq_ref | PRIMARY | PRIMARY | 4 | northwind.a.SupplierID | 1 | 1.00 | 100.00 | 100.00 | |
+------+-------------+-------+--------+-----------------------+---------+---------+------------------------+------+--------+----------+------------+----------------------------------------------+
2 rows in set (0.00 sec)
-- [번외] MariaDB 인덱스는 인덱스 컬럼 + PK 컬럼 데이터를 저장하고 해당 쿼리에서 인덱스 컬럼과 PK컬럼만 사용했을 때 using index가 나오지 않아서 강제로 인덱스 적용
MariaDB [northwind]> analyze
-> select a.ProductName
-> ,b.CompanyName
-> from Products a
-> straight_join Suppliers b USE INDEX(CompanyName) on(b.SupplierID = a.SupplierID)
-> order by b.CompanyName;
+------+-------------+-------+-------+-----------------------+-------------+---------+------+------+--------+----------+------------+--------------------------------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | r_rows | filtered | r_filtered | Extra |
+------+-------------+-------+-------+-----------------------+-------------+---------+------+------+--------+----------+------------+--------------------------------------------------------------+
| 1 | SIMPLE | a | ALL | FK_Products_Suppliers | NULL | NULL | NULL | 77 | 77.00 | 100.00 | 100.00 | Using temporary; Using filesort |
| 1 | SIMPLE | b | index | NULL | CompanyName | 122 | NULL | 29 | 29.00 | 100.00 | 3.45 | Using where; Using index; Using join buffer (flat, BNL join) |
+------+-------------+-------+-------+-----------------------+-------------+---------+------+------+--------+----------+------------+--------------------------------------------------------------+
2 rows in set (0.00 sec)
MariaDB [northwind]> analyze format=json
-> select a.ProductName
-> ,b.CompanyName
-> from Products a
-> straight_join Suppliers b USE INDEX(CompanyName) on(b.SupplierID = a.SupplierID)
-> order by b.CompanyName;
+-----------------------------------------------------------+
| ANALYZE |
+-----------------------------------------------------------+
| {
"query_block": {
"select_id": 1,
"r_loops": 1,
"r_total_time_ms": 1.4263,
"filesort": {
"r_loops": 1,
"r_total_time_ms": 0.1296,
"r_used_priority_queue": false,
"r_output_rows": 77,
"r_buffer_size": "8Kb",
"temporary_table": {
"table": {
"table_name": "a",
"access_type": "ALL",
"possible_keys": ["FK_Products_Suppliers"],
"r_loops": 1,
"rows": 77,
"r_rows": 77,
"r_total_time_ms": 0.1289,
"filtered": 100,
"r_filtered": 100
},
"block-nl-join": {
"table": {
"table_name": "b",
"access_type": "index",
"key": "CompanyName",
"key_length": "122",
"used_key_parts": ["CompanyName"],
"r_loops": 1,
"rows": 29,
"r_rows": 29,
"r_total_time_ms": 0.0636,
"filtered": 100,
"r_filtered": 100,
"using_index": true
},
"buffer_type": "flat",
"buffer_size": "2048Kb",
"join_type": "BNL",
"attached_condition": "(b.SupplierID = a.SupplierID)",
"r_filtered": 3.4483
}
}
}
}
} |
+-----------------------------------------------------------+
1 row in set (0.00 sec)
-- 결론: order by 절에는 Driving 테이블에서 인덱스로 order by 작업을 대체할 수 있도록 쿼리를 작성하는 것이 좋다.
MariaDB [northwind]> analyze
-> select a.ProductName
-> ,b.CompanyName
-> from Suppliers b USE INDEX(CompanyName)
-> straight_join Products a on(b.SupplierID = a.SupplierID)
-> order by b.CompanyName;
+------+-------------+-------+-------+-----------------------+-----------------------+---------+------------------------+------+--------+----------+------------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | r_rows | filtered | r_filtered | Extra |
+------+-------------+-------+-------+-----------------------+-----------------------+---------+------------------------+------+--------+----------+------------+-------------+
| 1 | SIMPLE | b | index | NULL | CompanyName | 122 | NULL | 29 | 29.00 | 100.00 | 100.00 | Using index |
| 1 | SIMPLE | a | ref | FK_Products_Suppliers | FK_Products_Suppliers | 5 | northwind.b.SupplierID | 1 | 2.66 | 100.00 | 100.00 | |
+------+-------------+-------+-------+-----------------------+-----------------------+---------+------------------------+------+--------+----------+------------+-------------+
2 rows in set (0.00 sec)
'MariaDB > SQL Tuning' 카테고리의 다른 글
| [MariaDB][성능] 스칼라 서브쿼리 실행계획2 (0) | 2016.08.17 |
|---|---|
| [MariaDB][성능]인라인뷰 처리 방식 분석 (0) | 2016.08.17 |
| [MariaDB][성능] 스칼라 서브쿼리 실행계획 (0) | 2016.08.15 |
| [MariaDB][성능] 실행계획 분석2 (0) | 2016.08.08 |
| [MariaDB][성능] 실행계획 분석[정리필요] (0) | 2016.08.07 |