MDEV-6929: Port Facebook Prefix Index Queries Optimization

Merge Facebook commit 154c579b828a60722a7d9477fc61868c07453d08
and e8f0052f9b112dc786bf9b957ed5b16a5749f7fd authored
by Steaphan Greene from https://github.com/facebook/mysql-5.6

Optimize prefix index queries to skip cluster index lookup when possible.

Currently InnoDB will always fetch the clustered index (primary key
index) for all prefix columns in an index, even when the value of a
particular record is smaller than the prefix length. This change
optimizes that case to use the record from the secondary index and avoid
the extra lookup.

Also adds two status vars that track how effective this is:

innodb_secondary_index_triggered_cluster_reads:
Times secondary index lookup triggered cluster lookup.

innodb_secondary_index_triggered_cluster_reads_avoided:
Times prefix optimization avoided triggering cluster lookup.

1 files changed, 70 insertions, 3 deletions

diff --git a/storage/innobase/row/row0sel.cc b/storage/innobase/row/row0sel.cc
index e5a7694cb93..d888c1ef645 100644
--- a/storage/innobase/row/row0sel.cc
+++ b/storage/innobase/row/row0sel.cc
@@ -56,6 +56,7 @@ Created 12/19/1997 Heikki Tuuri
 #include "row0mysql.h"
 #include "read0read.h"
 #include "buf0lru.h"
+#include "srv0srv.h"
 #include "ha_prototypes.h"
 #include "m_string.h" /* for my_sys.h */
 #include "my_sys.h" /* DEBUG_SYNC_C */
@@ -2936,9 +2937,14 @@ row_sel_store_mysql_rec(
 			: templ->rec_field_no;
 		/* We should never deliver column prefixes to MySQL,
 		except for evaluating innobase_index_cond(). */
+		/* ...actually, we do want to do this in order to
+		support the prefix query optimization.
+
 		ut_ad(dict_index_get_nth_field(index, field_no)->prefix_len
 		      == 0);
 
+		...so we disable this assert. */
+
 		if (!row_sel_store_mysql_field(mysql_rec, prebuilt,
 					       rec, index, offsets,
 					       field_no, templ)) {
@@ -3031,6 +3037,8 @@ row_sel_get_clust_rec_for_mysql(
 	dberr_t		err;
 	trx_t*		trx;
 
+	srv_stats.n_sec_rec_cluster_reads.inc();
+
 	*out_rec = NULL;
 	trx = thr_get_trx(thr);
 
@@ -3686,6 +3694,7 @@ row_search_for_mysql(
 	ulint*		offsets				= offsets_;
 	ibool		table_lock_waited		= FALSE;
 	byte*		next_buf			= 0;
+	ibool		use_clustered_index		= FALSE;
 
 	rec_offs_init(offsets_);
 
@@ -4709,10 +4718,68 @@ locks_ok:
 	}
 
 	/* Get the clustered index record if needed, if we did not do the
-	search using the clustered index. */
-
-	if (index != clust_index && prebuilt->need_to_access_clustered) {
+	search using the clustered index... */
+
+	use_clustered_index =
+		(index != clust_index && prebuilt->need_to_access_clustered);
+
+	if (use_clustered_index && srv_prefix_index_cluster_optimization
+	    && prebuilt->n_template <= index->n_fields) {
+		/* ...but, perhaps avoid the clustered index lookup if
+		all of the following are true:
+		1) all columns are in the secondary index
+		2) all values for columns that are prefix-only
+		   indexes are shorter than the prefix size
+		This optimization can avoid many IOs for certain schemas.
+		*/
+		ibool row_contains_all_values = TRUE;
+		int i;
+		for (i = 0; i < prebuilt->n_template; i++) {
+			/* Condition (1) from above: is the field in the
+			index (prefix or not)? */
+			mysql_row_templ_t* templ =
+				prebuilt->mysql_template + i;
+			ulint secondary_index_field_no =
+				templ->rec_prefix_field_no;
+			if (secondary_index_field_no == ULINT_UNDEFINED) {
+				row_contains_all_values = FALSE;
+				break;
+			}
+			/* Condition (2) from above: if this is a
+			prefix, is this row's value size shorter
+			than the prefix? */
+			if (templ->rec_field_is_prefix) {
+				ulint record_size = rec_offs_nth_size(
+					offsets,
+					secondary_index_field_no);
+				const dict_field_t *field =
+					dict_index_get_nth_field(
+						index,
+						secondary_index_field_no);
+				ut_a(field->prefix_len > 0);
+				if (record_size >= field->prefix_len) {
+					row_contains_all_values = FALSE;
+					break;
+				}
+			}
+		}
+		/* If (1) and (2) were true for all columns above, use
+		rec_prefix_field_no instead of rec_field_no, and skip
+		the clustered lookup below. */
+		if (row_contains_all_values) {
+			for (i = 0; i < prebuilt->n_template; i++) {
+				mysql_row_templ_t* templ =
+					prebuilt->mysql_template + i;
+				templ->rec_field_no =
+					templ->rec_prefix_field_no;
+				ut_a(templ->rec_field_no != ULINT_UNDEFINED);
+			}
+			use_clustered_index = FALSE;
+			srv_stats.n_sec_rec_cluster_reads_avoided.inc();
+		}
+	}
 
+	if (use_clustered_index) {
 requires_clust_rec:
 		ut_ad(index != clust_index);
 		/* We use a 'goto' to the preceding label if a consistent