The prefix index of MySQL is similar to that of Oracle.

The prefix index of MySQL is similar to that of Oracle.

MySQL has an interesting index type called prefix index. It can index the front part of a text field separately to reduce the index size.

In fact,Oracle has similar implementations. For text, it can use the substr function index to implement the same or more functions.. In addition,After exploration, we found that the original number and time fields can implement similar functions in Oracle.

MySQL prefix Index

MySQL prefix index refers to the index created for the first few digits of the specified column.

Altertable Table_Name add key (column_name (prefix_len ));

Or

Createindex index_name on Table_Name (column_name (prefix_len ));

After a prefix index is created, it can be directly used as a normal index for filtering.

Select .. from table_name where column_name = '... ';

The biggest benefit of prefix indexes is to reduce the index size. In addition, because the index length of a single column in InnoDB cannot exceed 767 bytes, if it is a text or blob field, an error may be reported if the index is directly created, and the prefix index can bypass this restriction.

 

Let's take a test.

Delimiter ;;

DropFUNCTION if exists random_str ;;

CREATEFUNCTION random_str (n int) RETURNS varchar (30000)

Begin

Declarereturn_str varchar (30000) default "";

Declare iint default 0;

Whilelength (return_str) <n do

Setreturn_str = concat (return_str, md5 (rand ()));

Endwhile;

Returnsubstring (return_str, 1, n );

End ;;

First, create a function to generate a random string of more than 1000 characters.

Create test table

CREATETABLE TEST_PREFIX_IND (

Id int (10) primary key AUTO_INCREMENT,

NORMAL_STR VARCHAR (20 ),

LONG_STR VARCHAR (1000 ),

TEXT_STR TEXT,

BLOB_STR BLOB

);

Insert 10000 rows of records:

Drop procedure if exists init_test_prefix_ind ;;

Createprocedure init_test_prefix_ind (n int)

Begin

Declare iint default 0;

While I <n do

Insertinto test_prefix_ind (NORMAL_STR, long_str, TEXT_STR, BLOB_STR)

Values (random_str (20), random_str (rand () * 1000 + 1), random_str (rand () * 1000 + 1), random_str (rand () * 300 + 1 ));

Seti = I + 1;

Endwhile;

End ;;

Callinit_test_prefix_ind (10000 );;

Try to create an index in LONG_STR of Type varchar (1000)

Altertable test_prefix_ind add key (LONG_STR );;

Succeeded, but Sub_part is displayed as 767, indicating that the system automatically creates a 767 prefix index;

Check the size:8992 k

Try to create an index directly on the column of TEXT and BLOB

Mysql> alter table test_prefix_ind add key (text_str );

ERROR 1170 (42000): BLOB/TEXT column 'text _ str' used in key specification without a key length

Mysql> alter table test_prefix_ind add key (blob_str );;

ERROR 1170 (42000): BLOB/TEXT column 'blob _ str 'used in key specification without a key length

You must specify the prefix length to create an index on the TEXT and BLOB fields.

Alter table test_prefix_ind add key (text_str (30 ));;

Look at the size,528 k(9520-8992), far smaller than LONG_STR8992 k.

Alter table test_prefix_ind add key (blob_str (30 ));;

Look at the size,544 k(10064-9520 ).

The prefix length and size of several tables. The prefix length significantly reduces the index size.

Check whether the query is normal:

You can use the upper index.

Prefix index length selection

For a column that may be quite long, how can we determine the appropriate prefix index?

Simple practice:

Select count (distinct substr (long_str, 1, 5)/count (*) from test_prefix_ind;

With some tips, you can traverse multiple values in the same SQL statement and view the selection of multiple values at the same time.

Select R, count (distinct substr (long_str, 1, R)/count (*)

From

(SELECT @ rownum: = ceil (@ rownum * 1.4) AS R

FROM (SELECT @ rownum: = 1) r, test_prefix_ind limit 1, 10

) R, test_prefix_ind T

Group by R ;;

Because the data in this table is random, the first five digits are good enough.

Create a prefix index with a prefix length of 5.

Alter table test_prefix_ind add key (long_str (5 ));

Look at the size, just258 k(10320-10064), far lower than the oldest8992 k.

Test the performance. When there is a prefix index:

After the index is deleted, the performance gap is obvious:

Similar Implementation of Oracle

From the previous practice, we can find that the prefix index essentially uses the first N digits of the column as the index, which looks like the Oracle function index. Similar:

Create index index_name on table_name (substr (column_name, 1, <length> ));

A deep impression on Oracle's function indexes is that the where condition must be consistent with the expression in the function index before using the function index. However, since MySQL can use a prefix index, it seems that it can also be implemented as an Oracle predecessor.

Let's see if the same functions can be implemented in Oracle.

Create a table:

Create table test_substr

Select object_id, object_name | dbms_random.string ('x', dbms_random.value (1,1000) as object_name, created from all_objects,

(Select * from dual connect by level <100)

Where rownum: <10000;

Create a function index for substr:

Create index test_substr_detail on test_substr (substr (object_name, 1, 5 ));

Look at the execution plan:

What's amazing is that it does take an index. Oracle also supports prefix indexes ~~

We can see that there is a new entry in the original statement in the search predicate:

Bind the variable to see:

As you can see, the predicate becomes:

Why is this thing missing? Because, logically speaking:

Select * from test_substr where object_name =:;

And

Select * from test_substr where object_name =: a and substr (object_name,) = substr (: );

Is equivalent. Oracle automatically performs semantic optimization.

If you are interested, make a 10053. The actual execution plan parsing in Oracle is such an SQL.

SELECT * FROM TEST_SUBSTR WHERE OBJECT_NAME =: a and substr (OBJECT_NAME,) = SUBSTR (: );

Let's see how much space is occupied if a common index is created.

Create index test_substr_inx2 on test_substr (object_name );

The sizes are7 MAnd256 K.

But Does Oracle only stop here? Let's try another SQL statement. This time, we also use substr on the condition, but the length is not 5.

Sure enough. Because logically speaking

Select * from test_substr

Where substr (object_name, 1, <N>) =:;

When N> = 5,

Select * from test_substr

Where substr (object_name, 1, <N>) =: a and substr (object_name,) = substr (: );

It is equivalent. Therefore, the optimizer can still add a predicate.

Of course, if you replace substr in the where condition with a value smaller than 5, the index will no longer be used. Because it cannot be directly converted into an equivalent statement with substr (object_name, 1, 5.

Prefix indexes on Oracle time and number

Just like this? Besides the character type, are numeric and time supported?

Let's take a look.

Create a trunc function index for the time type based on the table just now.

Create index test_trunc_date_prop on test_substr (trunc (created ));

Look at the execution plan:

It's okay.

Create a trunc function index on the numeric type:

Create index test_trunc_number on TEST_SUBSTR (trunc (object_id ));

In fact, the key to the problem lies in the equivalent and the internal rewriting of the optimizer.

Let's look at another example.

Create another table with a column length of up to 5.

Create table test_scale (object_name varchar2 (5 ));

Insert into test_scale select substr (object_name, 1, 5) from all_objects;

Create index test_scale_str_scale in test_scale (object_name );

Let's see the execution result of this statement.

Select * from test_scale where object_name = 'dba _ TABLES ';

The magic thing happened again. db block gets/consistent gets in autotrace are all 0, which means that the database did not access the table at all.

The reason is simple. The length of 'dba _ TABLES 'is greater than 5, which exceeds the value of varchar2 (5) in the table definition. Object_name = 'dba _ TABLES 'is equivalent to a constant condition. This cannot be found in 10053, but it does exist.

Follow this public account and reply to: prelection. You can find the relevant video document in this article.

Related reading:

In-depth analysis-structure of Oracle index branch blocks

Uses composite indexes to effectively reduce system IO

Covering indexes of MySQL SQL Optimization

The impact of more than 20 indexes on remote database tables

Download Resources

Public Account: Data and cloud (OraNews) reply keyword acquisition

'Dedicated DTC', Dig DTC Conference PPT

'Dbalife', "DBA's Day" Poster

'Dba04', DBA Manual 4, classic chapter ebook

'Racv1 ',RAC series course video and ppt

'122arch', Oracle 12.2 architecture Diagram

'Drawing oow', Oracle OpenWorld documents

'Prection', Lecture hall Course Materials