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

Definition:

The index is used to quickly find a row in a column that has a specific value.

Without using an index, MySql must read the entire table from the first record until the relevant rows are found. The larger the table, the more time it takes to query the data.

If the query column in the table has an index, Mysql can quickly get to a location to search for data files without having to view all the data.

Brief introduction:

An index is a structure that sorts the values of one or more columns in a database table. Using an index can improve the query speed of specific data in the database.

An index is a separate, disk-stored database structure that contains reference pointers to all records in the data table.

Using indexes is used to quickly find rows with a specific value in one or more columns. All Mysql column types can be indexed. Using indexes on related columns is the best way to speed up query operations.

For example, there are 20,000 records in the database, and now you want to execute a query like this:

Select * from table where num=10000

If there is no index, you must traverse the entire table until the row where num equals 10000 is found.

If you create an index on a num column, MySql does not need any scans, just find 10000 in the index to know the location of the row.

Indexes are implemented by storage engines, so the indexes of each storage engine are not necessarily the same, and each storage engine does not necessarily support all index types.

Define the maximum number of indexes and the maximum index length for each table according to the storage engine. All storage engines support at least 16 indexes per table, with a total index length of at least 256 bytes. Most storage engines have higher limits.

There are two storage types of indexes in MySql: BTREE and HASH, which are related to the table storage engine; both MyISAM and InnoDB storage engines support BTREE indexes; and MEMORY/HEAP storage engines can support BTREE and HASH indexes.

Index advantages:

1. By creating a unique index, the uniqueness of each row of data in the database table can be guaranteed.

2. It can greatly accelerate the query speed of the data, which is also the main reason for creating the index.

3. In order to realize the reference integrity of the data, the link between the table and the table can be accelerated.

4. When using grouping and sorting clauses for data query, you can also significantly reduce the time of grouping and sorting in the query.

Disadvantages of the index:

1. It takes time to create and maintain an index, and the time spent increases as the amount of data increases.

2, the index needs to occupy the disk space, in addition to the data table occupies the data space, each index also occupies a certain physical space, if there are a large number of indexes, the index file may reach the maximum file size faster than the data file.

3. When the data in the table is added, deleted and modified, the index should also be maintained dynamically, which reduces the speed of data maintenance.

Classification of MySql indexes:

1. General index and unique index

Generic index: the most basic index type in Mysql that allows duplicate and null values to be inserted in the column that defines the index.

Unique index: the value of the index column must be unique, but null values are allowed.

If it is a combined index, the column values and combinations must be unique. The primary key index is a special unique index, and null values are not allowed.

2. Single-column index and combined index

Single-column index: an index contains only one column, and a table can have multiple single-column indexes.

Composite index: an index created on a combination of fields in a table, and the index is used only if the left field of these fields is used in the query condition. Follow the leftmost prefix collection when using a combined index.

3. Full-text index

The full-text index type is FULLTEXT, and support on the columns that define the index is worthy of full-text search, allowing duplicate and null values to be inserted in these index columns. Full-text indexes can be created on CHAR,VARCHAR or TEXT types.

Only the MyISAM storage engine supports full-text indexing.

4. Spatial index

Spatial index is to index fields of spatial data type. There are 4 spatial data in Mysql: GEOMETRY, POINT, LINESTRING and POLYGON. Mysl is extended with the SPATIAL keyword to make it possible to create regular indexes in a syntax similar to spatial indexes.

The column that creates the spatial index must be declared as NOT NULL, and the spatial index can only be created in the storage engine MyISAM.

Design principles of index tables:

1. The more index tables, the better. If there are a large number of indexes in a table, it not only takes up hard disk space, but also affects the performance of statements such as additions, deletions, modifications and queries, because when the data in the table changes, the index will also be adjusted and updated.

2. Avoid having too many indexes in frequently updated tables and have as few columns in the index table as possible. Fields that are queried frequently should be indexed, but avoid adding unnecessary fields.

3. It is best not to use indexes for tables with small amounts of data. When the traversal time is short and the index is short, it will not produce optimization effect.

4. index on columns with more different values that are often used in conditional expressions, and do not build indexes on columns with fewer values. Like gender.

5. When uniqueness is a characteristic of some data itself, specify a unique index. Using a unique index needs to ensure the integrity of the data of the defined columns to improve the query speed of the data.

6. Index the columns that are sorted or grouped frequently (that is, group by or order by operations). If there are multiple columns to be sorted, you can build a combined index on these columns.

Create syntax:

CREATE TABLE specifies index column ALTER TABLE to create an index on the existing table CREATE INDEX add index CREATE TABLE table_name [col_name data_type] [UNIQUE | FULLTEXT | SPATIAL] [INDEX | KEY] [index_name] (col_name [length]) [ASC | DESC] UNIQUE: unique index FULLTEXT: full-text index SPATIAL: spatial index [INDEX | KEY]: create index col_name: field columns that need to be indexed, columns that must be defined in the table index_name: specify an index name If you do not specify the Mysql default col_name as the index value. Length: index length. Only string length can be specified. [ASC | DESC]: ascending and descending order

1. Create a general index:

Mysql > create database lxqdb;mysql > use lxqdb;mysql > create table book-> (- > book_id int not null,-> book_name varchar (255not null),-> authors varchar (255not null),-> info varchar (255null),-> comment varchar (255null),-> year_publication year not null,-> INDEX (year_publication)->)

View:

Mysql > show create table book\ GKEY `year_ publication` (`index publication`) # the index is successfully established, and mysql automatically adds the index name: year_publication

Check to see if the index is used correctly:

Mysql > explain select * from book where year_publication=2018\ gateway * 1. Row * * id: 1 select_type: SIMPLE # indicates a simple select, and subqueries cannot be used. Other values: PRIMARY | UNION | SUBQUERY table: book # line specifies the data table name, and they are arranged in the order in which they are read: type: ref # line specifies the association between this data table and other data tables. Other values: system | const | eq_ref | ref | range | index | ALL possible_keys: year_publication # optional index key: year_publication # actual selected index key_len: 1 # Index length in bytes The smaller the value, the faster the ref: const # associates the column name in another data table rows: 1 # the number of data rows expected to be read from this data table when the query is executed Extra: Using index condition # associated operation information 1 row in set (0.00 sec)

2. Create a unique index:

In large data tables, in order to reduce the time of index column operation. The only difference from a normal index is that the value of the index column must be unique, but null values are allowed. If it is a combined index, the combination of column values must be unique.

Create the tb1 table, and the id field uses UNIQUE to create a unique index.

Mysql > create table tb1-> (- > id int not null,-> name char (20) not null,-> UNIQUE INDEX uidx (id)->)

View:

A unique index named uidx is created on the mysql > show create table tb1\ GUNIQUE KEY `uidx` (`id`) # id field.

3. Create a single column index:

A single-column index is an index created on a field in a data table, and multiple single-column indexes can be created in a table.

The indexes created in the above two examples are single-column indexes.

Create a tb2 table and create a single-column index on the name field in the table.

Mysql > create table tb2-> (- > id int not null,-> name char (20) not null,-> INDEX Single_idx (name (10))->)

View:

Create a single column index named Single_idx with length 10 on the mysql > show create table tb2\ GKEY `Single_ idx` (`name` (10)) # name field.

4. Create a composite index:

Create an index on multiple fields.

Create a tb3 table with id,name,age as the index

Mysql > create table tb3-> (- > id int not null,-> name char (20) not null,-> age int not null,-> info varchar (255),-> INDEX Multi_idx (id,name,age)->)

View:

Mysql > show create table tb3\ GKEY `Multi_ idx` (`id`, `name`, `age`) # set up a combined index Note: the combined index can act as several indexes, but the index can not be used to query any field. Instead, it follows the leftmost prefix of "leftmost prefix": match the leftmost column set in the index, and the index is stored in the order of id\ name\ age. The index can search for the following field combinations: (id,name,age), (id,name), id. If the column does not constitute the leftmost prefix of the index, mysql cannot use a local index, such as an age or name,age combination. Query the index of the id and name fields: mysql > explain select * from tb3 where id=1 and name='lxq'\ Gkey: Multi_idx # you can see the index using MultiIdx. Query the index of the name and age fields: mysql > explain select * from tb3 where name='lxq' and age=23\ Gkey: NULL # indicates that the index query is not used.

5. Create full-text index:

Only the myisam engine supports and only char,varchar,text columns. The index is always on the whole column and does not support local indexes.

Create the table tb4 and create a full-text index on the info field:

Mysql > create table tb4-> (- > id int not null,-> name char (20) not null,-> age int not null,-> info varchar (255),-> FULLTEXT INDEX Fulltext_idx (info)->) engine=myisam

View:

Mysql > show create table tb4\ GFULLTEXT KEY `Fulltext_ idx` (`info`) # FULLTEXT named Fulltext_idx. Suitable for large data, do not use in small data.

6. Create a spatial index:

Only the myisam engine supports and only not null columns.

Create the tb5 table and create a spatial index on the GEOMETRY field:

Mysql > create table tb5-> (- > gmt geometry not null,-> SPATIAL INDEX Spat_idx (gmt)->) engine=myisam

View:

Mysql > show create table T5\ G

SPATIAL KEY `Spat_ idx` (`gmt`) # GEOMETRY named Spat_idx

-

I am Segmentation Line 1

-

Create an index in an existing table:

1. Build a general index of bookname_idx on the bookname field in the book table:

Mysql > alter table book add index bookname_idx (book_name (30))

View:

Mysql > show index from book\ gateway * 2. Row * * Table: book Non_unique: 1 # index is not unique. 1 represents a non-unique index. 0 represents the location in the only Key_name: bookname_idx # index name Seq_in_index: 1 # index, and 1 is a single column The combined index is the order of each field in the index definition Column_name: book_name # column fields of the index Collation: a Cardinality: 0 Sub_part: 30 # Index length Packed: NULL Null: # whether the field is empty Index_type: BTREE # Index Type Comment: Index_comment: Table: book

2. Create a unique index named UniqidIdx on the bookid field:

Mysql > alter table book add UNIQUE INDEX Uniqid_idx (book_id)

View:

Mysql > show index from book\ G * * 1. Row * * Table: book Non_unique: 0 # index unique, 1 for non-unique index, 0 for unique index Key_name: Uniqid_idx Seq_in_index: 1

3. Create a single column index on comment:

Mysql > alter table book add INDEX coment_idx (comment (50))

View:

Mysql > show index from book\ GSub_part: 50 # only retrieve the first 50 characters

4. Build a combined index on authors and info of book:

Mysql > alter table book add INDEX Au_Info_idx (authors (20), info (50))

View:

Mysql > show index from book\ G; Key_name: Au_Info_idx Seq_in_index: 1 # Index sequence 1 Column_name: authors** Key_name: Au_Info_idx Seq_in_index: 2 # Index sequence 2 Column_name: info

5. Create a spatial index in the gmt field of the tb6 table:

Build a table:

Mysql > create table tb6 (gmt geometry not null) engine=myisam

Add spatial index:

Mysql > alter table tb6 add SPATIAL INDEX spat_idx (gmt)

View:

Mysql > show index from tb6\ G

-

I am Segmentation Line 2

-

View the index:

Mysql > show create table book\ G UNIQUE KEY `Uniqid_ idx` (`book_ id`), KEY `bookname_ publication` (`year_ publication`), KEY `bookname_ idx` (`book_ name` (30)), KEY `coment_ idx` (`comment` (50)), KEY `Au_Info_ idx` (`authors` (20), `info` (50)) or mysql > show index from book\ G

Delete the index:

Mysql > alter table book drop index Uniqid_idx; # deletes the index named Uniqid_idx.

Note:

A unique index that adds an AUTO_INCREMENT constraint field cannot be deleted.

Or

Mysql > drop index coment_idx on book; # deletes the index named coment_idx in the book table.

-

I am Segmentation Line 3

-

Summary:

1. The index is so important to the database, how should it be used?

Choosing the correct index for the database is a complex task.

If there are fewer index columns, less disk space and maintenance overhead are required.

If multiple composite indexes are created on a large table, the index file expands very quickly.

More indexes on the other side can cover more queries.

Deleting the creation of an index does not affect the application or the database architecture, so you should try multiple different indexes to establish the optimal index.

2. Try to use short indexes.

Index fields of string type and specify a prefix length if possible.

For example, if there is a char column, if most of the values are unique within the first 10 or 30 characters, there is no need to index the entire column.

The short index can not only improve the query speed, but also save disk space and reduce the Icano operation.

3. Is it better to build as many indexes as possible?

Reasonable indexes can improve query speed, but not as many indexes as possible. When the insert statement is executed, mysql indexes the newly inserted records, so too many indexes can cause the insert operation to become very slow.

In principle, the index is created only in the fields used for the query.

4. Why doesn't the index in the query statement work?

In some cases, fields with indexes are used in query statements. However, the index field does not work.

For example, if the like keyword of the where condition matches a string that starts with "%", it will not work.

The or keyword is used in the where condition to link the condition, and if one field does not use an index, then the other indexes will not work.

If you use a multi-column index, but do not use the first field in a multi-column index, then the multi-column index will not work.

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.