IGNITE-10217: SQL: store unwrapped key fields in secondary indexes instead of full key when possible. Closes #5407.
Project: http://git-wip-us.apache.org/repos/asf/ignite/repo Commit: http://git-wip-us.apache.org/repos/asf/ignite/commit/a9b5c8c8 Tree: http://git-wip-us.apache.org/repos/asf/ignite/tree/a9b5c8c8 Diff: http://git-wip-us.apache.org/repos/asf/ignite/diff/a9b5c8c8 Branch: refs/heads/ignite-9720 Commit: a9b5c8c8aee9085f082fd524e288949853dd81f1 Parents: c8fd687 Author: Yuriy Gerzhedovich <ygerzhedov...@gridgain.com> Authored: Tue Nov 20 14:47:15 2018 +0300 Committer: devozerov <voze...@gridgain.com> Committed: Tue Nov 20 14:47:15 2018 +0300 ---------------------------------------------------------------------- .../IgnitePKIndexesMigrationToUnwrapPkTest.java | 6 +- .../processors/query/h2/H2TableDescriptor.java | 28 ++- .../internal/processors/query/h2/H2Utils.java | 13 + .../index/ComplexPrimaryKeyUnwrapSelfTest.java | 18 +- .../ComplexSecondaryKeyUnwrapSelfTest.java | 249 +++++++++++++++++++ .../IgniteBinaryCacheQueryTestSuite.java | 2 + 6 files changed, 300 insertions(+), 16 deletions(-) ---------------------------------------------------------------------- http://git-wip-us.apache.org/repos/asf/ignite/blob/a9b5c8c8/modules/compatibility/src/test/java/org/apache/ignite/compatibility/persistence/IgnitePKIndexesMigrationToUnwrapPkTest.java ---------------------------------------------------------------------- diff --git a/modules/compatibility/src/test/java/org/apache/ignite/compatibility/persistence/IgnitePKIndexesMigrationToUnwrapPkTest.java b/modules/compatibility/src/test/java/org/apache/ignite/compatibility/persistence/IgnitePKIndexesMigrationToUnwrapPkTest.java index 22ccae0..316e574 100644 --- a/modules/compatibility/src/test/java/org/apache/ignite/compatibility/persistence/IgnitePKIndexesMigrationToUnwrapPkTest.java +++ b/modules/compatibility/src/test/java/org/apache/ignite/compatibility/persistence/IgnitePKIndexesMigrationToUnwrapPkTest.java @@ -145,10 +145,12 @@ public class IgnitePKIndexesMigrationToUnwrapPkTest extends IgnitePersistenceCom */ @NotNull private static void initializeTable(IgniteEx igniteEx, String tblName) { executeSql(igniteEx, "CREATE TABLE " + tblName + " (id int, name varchar, age int, company varchar, city varchar, " + - "primary key (id, name, city))"); + "primary key (id, name, city)) WITH \"affinity_key=name\""); - executeSql(igniteEx, "INSERT INTO " + tblName + " (id, name, age, company, city) VALUES(1,'name',2,'company', 'city')"); + executeSql(igniteEx, "CREATE INDEX ON " + tblName + "(city, age)"); + for (int i = 0; i < 1000; i++) + executeSql(igniteEx, "INSERT INTO " + tblName + " (id, name, age, company, city) VALUES(?,'name',2,'company', 'city')", i); } /** http://git-wip-us.apache.org/repos/asf/ignite/blob/a9b5c8c8/modules/indexing/src/main/java/org/apache/ignite/internal/processors/query/h2/H2TableDescriptor.java ---------------------------------------------------------------------- diff --git a/modules/indexing/src/main/java/org/apache/ignite/internal/processors/query/h2/H2TableDescriptor.java b/modules/indexing/src/main/java/org/apache/ignite/internal/processors/query/h2/H2TableDescriptor.java index aa66939..6c20727 100644 --- a/modules/indexing/src/main/java/org/apache/ignite/internal/processors/query/h2/H2TableDescriptor.java +++ b/modules/indexing/src/main/java/org/apache/ignite/internal/processors/query/h2/H2TableDescriptor.java @@ -204,7 +204,7 @@ public class H2TableDescriptor implements GridH2SystemIndexFactory { if (affCol != null && H2Utils.equals(affCol, keyCol)) affCol = null; - List<IndexColumn> unwrappedKeyCols = extractKeyColumns(tbl, keyCol, affCol); + List<IndexColumn> unwrappedKeyAndAffinityCols = extractKeyColumns(tbl, keyCol, affCol); List<IndexColumn> wrappedKeyCols = H2Utils.treeIndexColumns(tbl.rowDescriptor(), new ArrayList<>(2), keyCol, affCol); @@ -224,7 +224,7 @@ public class H2TableDescriptor implements GridH2SystemIndexFactory { tbl, true, false, - unwrappedKeyCols, + unwrappedKeyAndAffinityCols, wrappedKeyCols, -1 ); @@ -271,14 +271,24 @@ public class H2TableDescriptor implements GridH2SystemIndexFactory { // Add explicit affinity key index if nothing alike was found. if (!affIdxFound) { - List<IndexColumn> columns = H2Utils.treeIndexColumns(tbl.rowDescriptor(), new ArrayList<>(2), affCol, keyCol); + List<IndexColumn> unwrappedKeyCols = extractKeyColumns(tbl, keyCol, null); + + ArrayList<IndexColumn> colsWithUnwrappedKey = new ArrayList<>(unwrappedKeyCols.size()); + + colsWithUnwrappedKey.add(affCol); + + //We need to reorder PK columns to have affinity key as first column, that's why we can't use simple PK columns + H2Utils.addUniqueColumns(colsWithUnwrappedKey, unwrappedKeyCols); + + List<IndexColumn> cols = H2Utils.treeIndexColumns(tbl.rowDescriptor(), new ArrayList<>(2), affCol, keyCol); + idxs.add(idx.createSortedIndex( AFFINITY_KEY_IDX_NAME, tbl, false, true, - columns, - columns, + colsWithUnwrappedKey, + cols, -1) ); } @@ -378,6 +388,12 @@ public class H2TableDescriptor implements GridH2SystemIndexFactory { GridH2RowDescriptor desc = tbl.rowDescriptor(); if (idxDesc.type() == QueryIndexType.SORTED) { + List<IndexColumn> unwrappedKeyCols = extractKeyColumns(tbl, keyCol, affCol); + + List<IndexColumn> colsWithUnwrappedKey = new ArrayList<>(cols); + + H2Utils.addUniqueColumns(colsWithUnwrappedKey, unwrappedKeyCols); + cols = H2Utils.treeIndexColumns(desc, cols, keyCol, affCol); return idx.createSortedIndex( @@ -385,7 +401,7 @@ public class H2TableDescriptor implements GridH2SystemIndexFactory { tbl, false, false, - cols, + colsWithUnwrappedKey, cols, idxDesc.inlineSize() ); http://git-wip-us.apache.org/repos/asf/ignite/blob/a9b5c8c8/modules/indexing/src/main/java/org/apache/ignite/internal/processors/query/h2/H2Utils.java ---------------------------------------------------------------------- diff --git a/modules/indexing/src/main/java/org/apache/ignite/internal/processors/query/h2/H2Utils.java b/modules/indexing/src/main/java/org/apache/ignite/internal/processors/query/h2/H2Utils.java index b9d9d8e..865754f 100644 --- a/modules/indexing/src/main/java/org/apache/ignite/internal/processors/query/h2/H2Utils.java +++ b/modules/indexing/src/main/java/org/apache/ignite/internal/processors/query/h2/H2Utils.java @@ -285,4 +285,17 @@ public class H2Utils { return resCur; } + + /** + * Add only new columns to destination list. + * + * @param dest List of index columns to add new elements from src list. + * @param src List of IndexColumns to add to dest list. + */ + public static void addUniqueColumns(List<IndexColumn> dest, List<IndexColumn> src) { + for (IndexColumn col : src) { + if (!containsColumn(dest, col)) + dest.add(col); + } + } } http://git-wip-us.apache.org/repos/asf/ignite/blob/a9b5c8c8/modules/indexing/src/test/java/org/apache/ignite/internal/processors/cache/index/ComplexPrimaryKeyUnwrapSelfTest.java ---------------------------------------------------------------------- diff --git a/modules/indexing/src/test/java/org/apache/ignite/internal/processors/cache/index/ComplexPrimaryKeyUnwrapSelfTest.java b/modules/indexing/src/test/java/org/apache/ignite/internal/processors/cache/index/ComplexPrimaryKeyUnwrapSelfTest.java index 8f4eaf0..376a0d4 100644 --- a/modules/indexing/src/test/java/org/apache/ignite/internal/processors/cache/index/ComplexPrimaryKeyUnwrapSelfTest.java +++ b/modules/indexing/src/test/java/org/apache/ignite/internal/processors/cache/index/ComplexPrimaryKeyUnwrapSelfTest.java @@ -88,18 +88,19 @@ public class ComplexPrimaryKeyUnwrapSelfTest extends GridCommonAbstractTest { put("uuid", "'1'"); } }; + for (Map.Entry<String, String> entry : types.entrySet()) { String tblName = createTableName(); String type = entry.getKey(); - String value = entry.getValue(); + String val = entry.getValue(); executeSql("CREATE TABLE " + tblName + " (id " + type + " , name varchar, age int, company varchar, city varchar," + " primary key (id))"); - checkUsingIndexes(tblName, value); + checkUsingIndexes(tblName, val); } } @@ -128,18 +129,19 @@ public class ComplexPrimaryKeyUnwrapSelfTest extends GridCommonAbstractTest { put("uuid", "'1'"); } }; + for (Map.Entry<String, String> entry : types.entrySet()) { String tblName = createTableName(); String type = entry.getKey(); - String value = entry.getValue(); + String val = entry.getValue(); executeSql("CREATE TABLE " + tblName + " (id " + type + " , name varchar, age int, company varchar, city varchar," + " primary key (id)) WITH \"affinity_key=id\""); - checkUsingIndexes(tblName, value); + checkUsingIndexes(tblName, val); } } @@ -160,18 +162,18 @@ public class ComplexPrimaryKeyUnwrapSelfTest extends GridCommonAbstractTest { * * @param tblName name of table which should be checked to using PK indexes. */ - private void checkUsingIndexes(String tblName, String idValue) { + private void checkUsingIndexes(String tblName, String idVal) { String explainSQL = "explain SELECT * FROM " + tblName + " WHERE "; - List<List<?>> results = executeSql(explainSQL + "id=" + idValue); + List<List<?>> results = executeSql(explainSQL + "id=" + idVal); assertUsingPkIndex(results); - results = executeSql(explainSQL + "id=" + idValue + " and name=''"); + results = executeSql(explainSQL + "id=" + idVal + " and name=''"); assertUsingPkIndex(results); - results = executeSql(explainSQL + "id=" + idValue + " and name='' and city='' and age=0"); + results = executeSql(explainSQL + "id=" + idVal + " and name='' and city='' and age=0"); assertUsingPkIndex(results); } http://git-wip-us.apache.org/repos/asf/ignite/blob/a9b5c8c8/modules/indexing/src/test/java/org/apache/ignite/internal/processors/cache/index/ComplexSecondaryKeyUnwrapSelfTest.java ---------------------------------------------------------------------- diff --git a/modules/indexing/src/test/java/org/apache/ignite/internal/processors/cache/index/ComplexSecondaryKeyUnwrapSelfTest.java b/modules/indexing/src/test/java/org/apache/ignite/internal/processors/cache/index/ComplexSecondaryKeyUnwrapSelfTest.java new file mode 100644 index 0000000..fe3cbfa --- /dev/null +++ b/modules/indexing/src/test/java/org/apache/ignite/internal/processors/cache/index/ComplexSecondaryKeyUnwrapSelfTest.java @@ -0,0 +1,249 @@ +/* + * Licensed to the Apache Software Foundation (ASF) under one or more + * contributor license agreements. See the NOTICE file distributed with + * this work for additional information regarding copyright ownership. + * The ASF licenses this file to You under the Apache License, Version 2.0 + * (the "License"); you may not use this file except in compliance with + * the License. You may obtain a copy of the License at + * + * http://www.apache.org/licenses/LICENSE-2.0 + * + * Unless required by applicable law or agreed to in writing, software + * distributed under the License is distributed on an "AS IS" BASIS, + * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. + * See the License for the specific language governing permissions and + * limitations under the License. + * + */ + +package org.apache.ignite.internal.processors.cache.index; + +import java.util.HashMap; +import java.util.List; +import java.util.Map; +import org.apache.ignite.cache.query.SqlFieldsQuery; +import org.apache.ignite.cache.query.annotations.QuerySqlField; +import org.apache.ignite.internal.IgniteEx; +import org.apache.ignite.testframework.junits.common.GridCommonAbstractTest; + +/** + * Test of creating and using secondary indexes for tables created through SQL. + */ +@SuppressWarnings({"unchecked", "ThrowableResultOfMethodCallIgnored"}) +public class ComplexSecondaryKeyUnwrapSelfTest extends GridCommonAbstractTest { + + /** Counter to generate unique table names. */ + private static int tblCnt = 0; + + /** {@inheritDoc} */ + @Override protected void beforeTestsStarted() throws Exception { + super.beforeTestsStarted(); + + startGrid(0); + } + + /** {@inheritDoc} */ + @Override protected void afterTestsStopped() throws Exception { + super.afterTestsStopped(); + + stopAllGrids(); + } + + /** + * Test secondary index with complex PK. Columns for secondary and PK indexes are intersect. + */ + public void testSecondaryIndexWithIntersectColumnsComplexPk() { + String tblName = createTableName(); + + executeSql("CREATE TABLE " + tblName + " (id int, name varchar, age int, company varchar, city varchar, " + + "primary key (name, city))"); + + executeSql("CREATE INDEX ON " + tblName + "(id, name, city)"); + + checkUsingIndexes(tblName, "'1'"); + } + + /** + * Test using secondary index with simple PK. + */ + public void testSecondaryIndexSimplePk() { + HashMap<String, String> types = new HashMap() { + { + put("boolean", "1"); + put("char", "'1'"); + put("varchar", "'1'"); + put("real", "1"); + put("number", "1"); + put("int", "1"); + put("long", "1"); + put("float", "1"); + put("double", "1"); + put("tinyint", "1"); + put("smallint", "1"); + put("bigint", "1"); + put("varchar_ignorecase", "'1'"); + put("time", "'11:11:11'"); + put("timestamp", "'20018-11-02 11:11:11'"); + put("uuid", "'1'"); + } + }; + + for (Map.Entry<String, String> entry : types.entrySet()) { + + String tblName = createTableName(); + + String type = entry.getKey(); + String val = entry.getValue(); + + executeSql("CREATE TABLE " + tblName + + " (id int, name " + type + ", age int, company varchar, city varchar," + + " primary key (name))"); + + executeSql("CREATE INDEX ON " + tblName + "(id, name, city)"); + + checkUsingIndexes(tblName, val); + } + } + + /** + * Check using secondary indexes for few cases. + * + * @param tblName name of table which should be checked to using secondary indexes. + * @param nameVal Value for name param. + */ + private void checkUsingIndexes(String tblName, String nameVal) { + String explainSQL = "explain SELECT * FROM " + tblName + " WHERE "; + + List<List<?>> results = executeSql(explainSQL + "id=1"); + + assertUsingSecondaryIndex(results); + + results = executeSql(explainSQL + "id=1 and name=" + nameVal); + + assertUsingSecondaryIndex(results); + + results = executeSql(explainSQL + "id=1 and name=" + nameVal + " and age=0"); + + assertUsingSecondaryIndex(results); + } + + /** + * Check that explain plan result shown using Secondary index and don't use scan. + * + * @param results result of execut explain plan query. + */ + private void assertUsingSecondaryIndex(List<List<?>> results) { + assertEquals(2, results.size()); + + String explainPlan = (String)results.get(0).get(0); + + assertTrue(explainPlan, explainPlan.contains("_idx\": ")); + + assertFalse(explainPlan, explainPlan.contains("_SCAN_")); + } + + /** + * Create unique table name. + * + * @return unique name of table. + */ + private String createTableName() { + return "TST_TABLE_" + tblCnt++; + } + + /** + * Run SQL statement on default node. + * + * @param stmt Statement to run. + * @param args arguments of statements + * @return Run result. + */ + private List<List<?>> executeSql(String stmt, Object... args) { + return executeSql(node(), stmt, args); + } + + /** + * Run SQL statement on specified node. + * + * @param node node to execute query. + * @param stmt Statement to run. + * @param args arguments of statements + * @return Run result. + */ + private List<List<?>> executeSql(IgniteEx node, String stmt, Object... args) { + return node.context().query().querySqlFields(new SqlFieldsQuery(stmt).setArgs(args), true).getAll(); + } + + /** + * @return Node to initiate operations from. + */ + private IgniteEx node() { + return grid(0); + } + + /** + * + */ + static class TestKey { + /** */ + @QuerySqlField + private int id; + + /** + * @param id ID. + */ + public TestKey(int id) { + this.id = id; + } + + /** {@inheritDoc} */ + @Override public boolean equals(Object o) { + if (this == o) + return true; + + if (o == null || getClass() != o.getClass()) + return false; + + TestKey testKey = (TestKey)o; + + return id == testKey.id; + } + + /** {@inheritDoc} */ + @Override public int hashCode() { + return id; + } + } + + /** + * + */ + static class TestValue { + /** */ + @QuerySqlField() + private String name; + /** */ + @QuerySqlField() + private String company; + /** */ + @QuerySqlField() + private String city; + /** */ + @QuerySqlField() + private int age; + + /** + * @param age Age. + * @param name Name. + * @param company Company. + * @param city City. + */ + public TestValue(int age, String name, String company, String city) { + this.age = age; + this.name = name; + this.company = company; + this.city = city; + } + } + +} http://git-wip-us.apache.org/repos/asf/ignite/blob/a9b5c8c8/modules/indexing/src/test/java/org/apache/ignite/testsuites/IgniteBinaryCacheQueryTestSuite.java ---------------------------------------------------------------------- diff --git a/modules/indexing/src/test/java/org/apache/ignite/testsuites/IgniteBinaryCacheQueryTestSuite.java b/modules/indexing/src/test/java/org/apache/ignite/testsuites/IgniteBinaryCacheQueryTestSuite.java index ddfeaef..25cb473 100644 --- a/modules/indexing/src/test/java/org/apache/ignite/testsuites/IgniteBinaryCacheQueryTestSuite.java +++ b/modules/indexing/src/test/java/org/apache/ignite/testsuites/IgniteBinaryCacheQueryTestSuite.java @@ -251,6 +251,8 @@ public class IgniteBinaryCacheQueryTestSuite extends TestSuite { suite.addTestSuite(ExplainSelfTest.class); suite.addTestSuite(RunningQueriesTest.class); + suite.addTestSuite(ComplexPrimaryKeyUnwrapSelfTest.class); + suite.addTestSuite(PartitionedSqlTest.class); suite.addTestSuite(ReplicatedSqlTest.class);