среда, 19 декабря 2012 г.

InnoDB, extended secondary keys.

It's a well-know fact that InnoDB secondary keys contain both user defined columns and the primary key columns. For example, if a table has PRIMARY KEY(pk) and secondary key k1(f1), then index k1 is internally stored as k1(f1,pk).

Prior to version 5.6.9, the MySQL optimizer could only partially use these extended primary key columns: they could be used for sorting and to provide index only access. Starting from MySQL 5.6.9, the optimizer makes full use of the extended columns. This means that 'ref' access, range access, MIN/MAX optimizations, index_merge, loose index scan etc all works as if you had created the index with all primary key columns in all secondary keys. The new feature is turned on and off by optimizer switch 'use_index_extensions' and is on by default.

Consider we have following table:

CREATE TABLE t1
(
  f1 INT NOT NULL DEFAULT '0',
  f2 INT NOT NULL DEFAULT '0',
  f3 DATE DEFAULT NULL,
  PRIMARY KEY (f1, f2),
  KEY k_f3 (f3)
) ENGINE = InnoDB;

INSERT INTO t1 VALUES (1, 1, '1998-01-01'), (1, 2, '1999-01-01'),
(1, 3, '2000-01-01'), (1, 4, '2001-01-01'), (1, 5, '2002-01-01'),
(2, 1, '1998-01-01'), (2, 2, '1999-01-01'), (2, 3, '2000-01-01'),
(2, 4, '2001-01-01'), (2, 5, '2002-01-01'), (3, 1, '1998-01-01'),
(3, 2, '1999-01-01'), (3, 3, '2000-01-01'), (3, 4, '2001-01-01'),
(3, 5, '2002-01-01'), (4, 1, '1998-01-01'), (4, 2, '1999-01-01'),
(4, 3, '2000-01-01'), (4, 4, '2001-01-01'), (4, 5, '2002-01-01'),
(5, 1, '1998-01-01'), (5, 2, '1999-01-01'), (5, 3, '2000-01-01'),
(5, 4, '2001-01-01'), (5, 5, '2002-01-01');

Lets take a look at the difference in results with "use_index_extensions=off|on":


mysql> set optimizer_switch= "use_index_extensions=off";
Query OK, 0 rows affected (0.00 sec)
mysql> FLUSH STATUS;
Query OK, 0 rows affected (0.00 sec)
mysql> SELECT COUNT(*) FROM t1 WHERE f3 = '2000-01-01' AND f1 BETWEEN 1 AND 3;
+----------+
| COUNT(*) |
+----------+
|        3 |
+----------+
1 row in set (0.01 sec)
mysql> SHOW STATUS LIKE 'handler_read_next';
+-------------------+-------+
| Variable_name     | Value |
+-------------------+-------+
| Handler_read_next | 5     |
+-------------------+-------+
1 row in set (0.00 sec)


mysql> set optimizer_switch= "use_index_extensions=on";
Query OK, 0 rows affected (0.00 sec)
mysql> FLUSH STATUS;
Query OK, 0 rows affected (0.00 sec)
mysql> SELECT COUNT(*) FROM t1 WHERE f3 = '2000-01-01' AND f1 BETWEEN 1 AND 3;
+----------+
| COUNT(*) |
+----------+
|        3 |
+----------+
1 row in set (0.00 sec)
mysql> SHOW STATUS LIKE 'handler_read_next';
+-------------------+-------+
| Variable_name     | Value |
+-------------------+-------+
| Handler_read_next | 3     |
+-------------------+-------+
1 row in set (0.00 sec)

Number of handler_read_next is decreased with use_index_extensions=on. It happens because optimizer uses 'f3, f1' pair for range access. This behaviour change is indirectly can be visible is explain:


mysql> set optimizer_switch= "use_index_extensions=off";
Query OK, 0 rows affected (0.00 sec)
mysql> EXPLAIN SELECT COUNT(*) FROM t1 WHERE f3 = '2000-01-01' AND f1 BETWEEN 1 AND 3;
+----+-------------+-------+------+---------------+------+---------+-------+------+--------------------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref   | rows | Extra                    |
+----+-------------+-------+------+---------------+------+---------+-------+------+--------------------------+
|  1 | SIMPLE      | t1    | ref  | PRIMARY,k_f3  | k_f3 | 4       | const |    5 | Using where; Using index |
+----+-------------+-------+------+---------------+------+---------+-------+------+--------------------------+
1 row in set (0.00 sec)


mysql> set optimizer_switch= "use_index_extensions=on";
Query OK, 0 rows affected (0.00 sec)
mysql> EXPLAIN SELECT COUNT(*) FROM t1 WHERE f3 = '2000-01-01' AND f1 BETWEEN 1 AND 3;
+----+-------------+-------+-------+---------------+------+---------+------+------+--------------------------+
| id | select_type | table | type  | possible_keys | key  | key_len | ref  | rows | Extra                    |
+----+-------------+-------+-------+---------------+------+---------+------+------+--------------------------+
|  1 | SIMPLE      | t1    | range | PRIMARY,k_f3  | k_f3 | 8       | NULL |    3 | Using where; Using index |
+----+-------------+-------+-------+---------------+------+---------+------+------+--------------------------+

As you can see key_len field is changed (4 with "use_index_extensions=off" and 8 with "use_index_extensions=on") which means that 'f3, f1' pair is used with enabled feature.

Another example shows the improvement when using JOIN(the same table with 12800 records):


mysql> SELECT COUNT(*) FROM t1;
+----------+
| COUNT(*) |
+----------+
|    12800 |
+----------+
1 row in set (0.07 sec)

mysql> set optimizer_switch= "use_index_extensions=off";
Query OK, 0 rows affected (0.00 sec)
mysql> SELECT count(*) FROM t1 AS t1 JOIN t1 AS t2 ON t2.f1 = t1.f1
WHERE t1.f3 = '2000-01-01' AND t2.f3 = '2002-01-01';
+----------+
| count(*) |
+----------+
|    10240 |
+----------+
1 row in set (0.47 sec)

set optimizer_switch= "use_index_extensions=on";
Query OK, 0 rows affected (0.00 sec)
mysql> SELECT count(*) FROM t1 AS t1 JOIN t1 AS t2 ON t2.f1 = t1.f1
WHERE t1.f3 = '2000-01-01' AND t2.f3 = '2002-01-01';
+----------+
| count(*) |
+----------+
|    10240 |
+----------+
1 row in set (0.18 sec)


As you can see execution of the query is roughly 2.5 better with enabled "use_index_extensions" switch.

3 комментария:

  1. Заношу ваш проект в закладки Head Hunter

    ОтветитьУдалить
  2. О замене SELECT COUNT для highload можете почитать в моем блоге: http://plutov.by/post/mongodb_counters

    ОтветитьУдалить
  3. OnlineAttestationPakistan.com Providing The Best Attestation Services In Pakistan. Educational Documents Attestation Services In Pakistan, Degree Attestation Services From Ministry Of Foreign Affairs In Pakistan, Certificate Attestation Services From HEC , IBCC Online In Pakistan. Online Attestation Services In Pakistan Offering :

    HEC Degree Attestation From Pakistan

    Degree Attestation Procedure In Pakistan

    Documents Attestation Requirements In Pakistan

    Certificate Attestation Process In Pakistan

    IBCC Attestation Procedure Or IBCC Equivalence Certificate Issuance For O and A Level`s In Pakistan.

    British Council Attestation From Pakistan

    Degree Attestation Service For Higher Education HEC In Pakistan

    Transcript / Marksheets Attestation Services From University Of Karachi, Lahore, Islamabad Pakistan.

    Duplicate Certificate / Marksheet / Degree Issuance From Pakistan

    Diploma Attestation Services From Pakistan Such as Sindh Technical Board Or Punjab Technical Board Attestation

    WES IQAS ICES ICAS CES Canada Immigration Consultation & Attestation Services For Equivalence Report ECA Services .

    Attestation Services Of Marksheets / Degree Copies Or Original In Sealed Envelope And Courier Through FBISE (Federal Board) , IBCC Board Or HEC Sealed Envelope

    Saudi Cultural And Saudi Embassy Attestation Services From Pakistan

    UAE Embassy Or Dubai Embassy Attestation Services From Pakistan

    MOFA Pakistan Attestation Services Or Attestation Agents In Pakistan

    Kuwait, Oman , Qatar, China Embassy Attestation Services In Pakistan

    PEC or Experience Letter Attestation Services From Pakistan

    Translation Services Of Nikkah Nama Into English or Arabic Certified Translations

    Notarization And Attestation Services From Notary Public In Pakistan

    Marriage Certificate Or Birth Certificate Attestation Services From Saudi Embassy Or UAE Embassy Or Ministry Of Foreign Affairs Office In Pakistan

    School Leaving Certificate Attestation Services From Pakistan

    Lost Degree / Marksheet / Certificate Issuance From University In Karachi , Lahore, Islamabad Pakistan

    Nadra Birth Certificate, Marriage Certificate , Divorce Or Death Certificate Issuance From Pakistan. No Entry or No Birth Record Or Non Availibility Of Birth Certificate, Unmarried Certificate, Single Certificate, Bachelor Certificate, Issuance From Nadra Pakistan.

    Attestation Services For Job, Visa , Immigration, Sponsorship Or Travel From Karachi, Lahore, Islamabad In Pakistan.

    Name Change , Correction Of Date Or Complete Syllabus From University Or College Or Date Of Birth / Marriage / Unmarried Certificate Solutions From Pakistan.

    Dont Just Rely On TCS, Fedex, Leapord Courier, DHL Courier Services. Ensure Your Documents Safety and Return With Our Trusted Expertise And most trust worthy reliable services. Now Don`t worry about on Confusions in Google Search Like :

    How To Get Birth Certificate From Pakistan ?

    How To Attest Degree From Hec In Pakistan ?

    Who Issues Birth Or Marriage Certificate In Pakistan ?

    Where To Apply For IBCC Attestation In Pakistan ?

    Answer : we got all solutions for all your answers right here

    Online Attestation Pakistan Payoneer In Providing Services For More Then 15 Years In Pakistan. We Provide Complete Procedure & Details Such as Procedure, Requirements, Process, Answers, Days, Office, Results, Fees, Apply, Details , Information reliable Authentic Genuine & Urgent Service For All Type Of Documentation & Attestation Services From Pakistan.

    Serving Overseas and Domestic Pakistani`s In Various Cities And Country Around The Globe.

    Served Over 6600 Clients In More Then 50 States Such As

    England, London , United Kingdom UK

    So Dont Wait And Click On http://onlineattestationpakistan.com, Get Contacted By Our Team Instantly.

    OnlineAttestationPakistan : Accelerating Solutions For You

    ОтветитьУдалить