Shulou(Shulou.com)06/01 Report--

This article mainly explains "how to create Mariadb index". The explanation content in this article is simple and clear, easy to learn and understand. Please follow the idea of Xiaobian and go deep into it slowly to study and learn "how to create Mariadb index" together.

Indexes are special files (indexes on InnoDB tables are part of a table space) that contain pointers to all records in the table.

More popularly, a database index is like a table of contents in front of a book. You can quickly locate the query data by looking up the index position in the table of contents before looking up the content.

Classification of index

Single-valued (column) index: That is, an index contains only a single column, and a table can have multiple single-column indexes.

Unique index: Index column values must be unique, but null values are allowed. -> Primary keys are special unique indexes because they do not allow null values.

Composite (composite) index: An index that contains multiple columns.

Full-text index: FULLTEXTl type index, can be created on CHAR, VARCHAR, or TEXT type columns, only MyISAM support.

Spatial index: support for spatial databases, GIS systems and whatnot... Oh, don't look at this, it looks pretty cool.

index structure

The index structure supported by each storage engine is as follows (KB from MariaDB):

BTREE is the default index structure, and HASH is the default for MEMORY storage engines.

B-TREE: Support>, >=, =, >=, operator,

R-tree: Spatial index on the data structure, do not look at the... Too hard.

What cases need to create index primary key automatically establish unique index frequently as a query condition field should create index query and other tables associated with the field, foreign key relationship to establish index frequently updated field is not suitable for establishing index, because each update not only updates the record but also updates the index WHERE condition unused field does not create index single key/composite index selection problem, who? (tend to create composite index under high concurrency) sorted fields in query, sorted fields if accessed through index will greatly improve sorting speed in query statistics or grouping fields in which cases do not create index table records too few frequently added and deleted table

Speeds up queries, but slows down table updates, such as INSERT, UPDATE, and Delete.

Because when updating tables, MySQL saves not only the data, but also the index file.

Table fields where data is duplicated and evenly distributed, so index only the most frequently queried and sorted data.

Note that indexing a data column has little practical effect if it contains many duplicates.

leftmost prefix principle

A good article was found here, excerpted from Zhihu: Understanding the leftmost matching principle of mysql index? Author: Shen Jie

The table structure is as follows: There are three fields, id,name,cid.

CREATE TABLE `student` ( `id` int(11) NOT NULL AUTO_INCREMENT, `name` varchar(255) DEFAULT NULL, `cid` int(11) DEFAULT NULL, PRIMARY KEY (`id`), KEY `name_cid_INX` (`name`,`cid`),) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=utf8

Indexing: id is the primary key, and (name,cid) is a multi-column index.

Here are two queries you have questions about:

EXPLAIN SELECT * FROM student WHERE cid=1;

Mariadb Learning Summary (VI): Index Mariadb Learning Summary (VI): Index EXPLAIN SELECT * FROM Student WHERE cid=1 AND name='Little Red';

Mariadb Learning Summary (VI): Index Mariadb Learning Summary (VI): Index

Your question is: sql query index conditions must comply with the leftmost prefix principle, why the above two queries can also use the index?

Before I talk about the above questions, let me add some knowledge first, because I think your understanding of indexes is narrow: the explanation results of your two queries above show that the types of situations in which indexes are used are different. Observe the type field in the explain result.

Your query is: \1. type: index \2. type: ref

Index: This type indicates that mysql scans the entire index. To use this type of index, there is no special requirement for this index, as long as it is an index, or part of a composite index, mysql may use the index type to scan. However, the disadvantage is that the efficiency is not high, mysql will find the last data from the first data in the index one by one, until it finds an index that meets the judgment conditions.

So: for your first sentence:

EXPLAIN SELECT * FROM student WHERE cid=1;

The judgment condition is cid=1, and cid is a part of the (name,cid) composite index. There is no problem. Index scanning of index type can be performed. explain shows that the result uses an index, which is the index type.

ref: This type indicates that mysql will quickly find a qualified index according to a specific algorithm, instead of scanning and judging each data in the index one by one, that is, the so-called index query you usually understand will fetch data faster. To achieve this kind of search, the index is required. To achieve this kind of fast search algorithm, the index must satisfy a specific data structure. Simply put, the data in the index field must be ordered in order to achieve this type of lookup and to utilize the index.

Some of you may ask, isn't an index an ordered data structure? However, the answer is not perfect enough, it is only for a single index, and some students may not understand the situation of composite index.

The following is a composite index:

Take the (name,cid) composite index of the table as an example, its internal structure is simply arranged as follows:

mysql creates a composite index by sorting the data in the leftmost, i.e., first name field of the composite index first, and then sorting the cid field of the second field after the first field. This is equivalent to implementing a sort rule like order by name cid.

So: the first name field is absolutely ordered, and the second field is out of order. Therefore, under normal circumstances, directly using the second cid field for conditional judgment is not necessary to use the index, of course, the above index type may appear. This is why mysql emphasizes the leftmost prefix principle.

So when can I use it? Of course, the cid field index data is also ordered under the circumstances can be used, when is it ordered? Observed, cid is ordered, of course, only if the name field is an equivalent match. No. Observe whether the cid fields with name c are ordered. Four and five from top to bottom. This is why mysql indexing rules require composite indexes to use the first index before using the second index. (And the first index must be an equivalent match.)

So for your SQL query:

EXPLAIN SELECT * FROM student WHERE cid=1 AND name='Little Red';

There is no mistake, and the two index fields in the composite index can be well utilized! Because the leftmost name field in the statement matches equivalently, cid is ordered and can also be used to index.

You might ask: My index is (name,cid). And my query sentence is cid=1 AND name='Xiaohong'; I query cid first, then query name, not first from the left?

Well, let me explain the problem again: first of all, it is certain that the conditional judgment is reversed to such a name='small red' and cid=1; finally, the result of the query is the same. So what's the problem? Since the results are the same, in what order is the best way to query? So, at this point that's when our mysql query optimizer comes on stage, and the mysql query optimizer determines what order to correct the sql statement in the most efficient way, and finally generates the real execution plan.

So, of course, we can make the most of the query order when we get to the index, so the mysql query optimizer will eventually execute queries in this order.

Praise a ~ Please do not rotate, thank the original author Shen Jie, if there is infringement please inform.

Syntax for creating an index

Create indexes when creating tables

CREATE TABLE tbl_name(Column_def1,Column_def2,Columndef3,..... index_def);index_def: {INDEX|KEY} [index_name] [index_type] (index_col_name,...) [index_option] ... | {FULLTEXT|SPATIAL} [INDEX|KEY] [index_name] (index_col_name,...) [index_option] ... | [CONSTRAINT [symbol]] PRIMARY KEY [index_type] (index_col_name,...) [index_option] ... | [CONSTRAINT [symbol]] UNIQUE [INDEX|KEY] [index_name] [index_type] (index_col_name,...) [index_option] ... | [CONSTRAINT [symbol]] FOREIGN KEY [index_name] (index_col_name,...) reference_definition

Example: Create a table with three fields: id, name, gid Create an index for name:

MariaDB [mydb]> CREATE TABLE test( -> id int(10) PRIMARY KEY AUTO_INCREMENT, -> name VARCHAR(10) NOT NULL, -> gid int(3), -> INDEX name_idx (name(5)) -> );

Name(5) is an index created for the name column, and only the first 5 characters are indexed.

For more SQL syntax when creating tables, see mariadb.com/kb/en/library/create-table/#indexes

Create indexes for existing tables

CREATE INDEX syntax is as follows:

CREATE [OR REPLACE] [ONLINE|OFFLINE] [UNIQUE|FULLTEXT|SPATIAL] INDEX [IF NOT EXISTS] index_name [index_type] ON tbl_name (index_col_name,...) [WAIT n | NOWAIT] [index_option] [algorithm_option | lock_option] ... index_col_name: col_name [(length)] [ASC | DESC]index_type: USING {BTREE | HASH | RTREE}index_option: KEY_BLOCK_SIZE [=] value | index_type | WITH PARSER parser_name | COMMENT 'string'algorithm_option: ALGORITHM [=] {DEFAULT|INPLACE|COPY}lock_option: LOCK [=] {DEFAULT|NONE|SHARED|EXCLUSIVE}

Let's look at the index created above:

MariaDB [mydb]> SHOW INDEX FROM test\G;*************************** 1. row *************************** //primary key creates an index Table: test Non_unique: 0 Key_name: PRIMARYSeq_in_index: 1 Column_name: id Collation: A Cardinality: 0 Sub_part: NULL Packed: NULL Null: Index_type: BTREE Comment:Index_comment:*************************** 2. row ****************************** //This is an index we created ourselves Table: test Non_unique: 1 Key_name: name_idxSeq_in_index: 1 Column_name: name Collation: A Cardinality: 0 Sub_part: 5 Packed: NULL Null: Index_type: BTREE Comment:Index_comment:2 rows in set (0.00 sec)

Now we delete the index we created earlier and create a multi-column index (name,gid)

DROP INDEX name_idx ON test;//Delete index CREATE INDEX name_gid_idx ON test (name(5),gid DESC); //Create a multi-column index with name in the first 5 and gid in reverse CREATE OR REPLACE INDEX name_gid_idx ON test(name(5),gid); //Modify the index created above

unique index

Creating a unique constraint automatically creates a unique index, but creating a unique index does not create a unique constraint, and a unique index can do the same as a unique constraint.

Thank you for reading, the above is "Mariadb index how to create" content, after the study of this article, I believe we have a deeper understanding of Mariadb index how to create this problem, the specific use of the situation still needs to be verified by practice. Here is, Xiaobian will push more articles related to knowledge points for everyone, welcome to pay attention!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.