Database Tuning tutorial (5) Roles of indexes and types of indexes, tuning Indexes

Database Tuning tutorial (5) Roles of indexes and types of indexes, tuning Indexes
Iii. Index

In the previous chapter, we learned how to use explain to generate a query execution plan (QEP), so as to discover problems with the statement. Three results returned by explain are related to the index (possible key, key, and extra). It can be seen that the index plays a significant role in improving the query efficiency.

In this chapter, we will explain how to use indexes to optimize our database.

1. Functions of Indexes

The indexing principle is to use special search algorithms (such as Binary Tree algorithms) to limit the number of accessed rows and improve access efficiency.

In addition, indexes can efficiently establish connections between tables and sort results efficiently.

2. Basic index types and Creation

Indexes can be divided into primary key indexes, common indexes, unique indexes, and full-text indexes.

1) Primary Key Index

When a table sets a column as the primary key, the column is the primary key index.

create table aaa(id int unsigned primary key auto_increment ,name varchar(32) not null defaul ‘’);

This is the id column or the primary key index.

If you do not specify a primary key index when creating a table, you can also add

Alter table name add primary key (column name );

2) Common Index

Generally, to create a common index, you must first create a table and then create a common index.

Create index name on table (column name 1, column name 2 );

When there are more than one column in the brackets, we call it a multi-column index. Under certain circumstances, the multi-column index can evolve to a covered index (which will be introduced later ).

Here we can see the power of indexes by creating common indexes in the emp table.

Before creating an index

Run the following statement:

select count(1) from emp where ename like 'yu%';

Then we create an index.

create index index_ename on emp(ename);

Execute again

select count(1) from emp where ename like 'yu%';

Incredible!

The power of indexes is evident!

Of course, indexing is costly.

This is the size of the data/bigtable/emp. ibd file under the mysql installation directory when no index is created. For innodb Engine tables, ibd files store table data and indexes.

After I create an index

Obviously, indexes occupy the host space, which is also called the index overhead.

3) unique index

When a column in a table is specified as a unique constraint, this column is a unique index.

create table ddd(id int primary key auto_increment , name varchar(32) unique);

The name column is a unique index.

Create a unique index after creating a table

Create unique index name on table name (list ..);

The unique field can be NULL and can have multiple NULL values, that is, Null! = Null. However, if the content is specific, it cannot be repeated.

4) full-text index

Full-text indexing is only useful for the MyISAM engine. Mainly for file and text retrieval, such as articles or paragraphs ,.

It generates a list of all the words in a data column of a data table.

Full-text index creation

Create a table

CREATE TABLE articles (       id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,       title VARCHAR(200),       body TEXT,       FULLTEXT (title,body)     )engine=myisam charset utf8;

Or add

ALTER TABLE articles ADD FULLTEXT (title,body);

The correct full-text index usage is:

Select * from articles where match (title, body) against ('word to search ');

In addition, the fulltext provided by mysql takes effect in English and needs to be used to search for Chinese characters.Sphinx full-text search engineOr use enhanced fuzzy search.

Note:

Full-text indexes are not exactly the same as fuzzy queries.

For example, if the title field has such data 'abc20088ccaa ', you can use the fuzzy query select * from articleswhere title like' % 100' to find it, and use the full-text search select * from articleswhere match (title) against ('20140901'); cannot be retrieved because 2008 is not a word!

The end of this lecture is to explain the operations related to indexes in the next lecture.