Mysqk index details

Detailed explanation of Mysql index

What is an index?

1. Index

An index is the directory of a table. You can find the index position in the directory before finding the content, so as to quickly locate the query data. For indexes, they are saved in additional files.

**2. * * index is a data structure specially used to help users query data quickly in the database. Similar to the directory in the dictionary, when looking up the contents of the dictionary, you can find the storage location of the data according to the directory, and then obtain it directly.

The index is composed of one or more columns in the database. Its function is to improve the query speed of the data in the table
The advantage of index is that it can improve the speed of retrieving data
The disadvantage of indexes is that it takes time to create and maintain indexes
Indexing can improve query speed and slow down write speed

Index classification

1. General index
2. Unique index
3. Full text index
4. Single column index
5. Multi column index
6. Spatial index
7. Primary key index
8. Composite index

  • Normal index: only accelerate query
  • Unique index: accelerated query + unique column value (can be null)
  • Primary key index: accelerated query + unique column value + only one in the table (null is not allowed)
  • Combined index: multiple column values form an index,
    It is specially used for combined search, which is more efficient than index merging
  • Full text index: word segmentation and search for the content of the text

Index merging, using multiple single column index combination search
Overwrite the index. The data column of the select can be obtained only from the index without reading the data row. In other words, the query column should be overwritten by the built index

How to create an index? Remember a word - explain

Create indexes when creating tables

[](javascript:void(0)😉

CREATE TABLE tbl_name(
    Field name field type [Integrity constraints]，
    ，，，,
    [UNIQUE|FULLTEXT|SPATIAL] INDEX|KEY[Index name](Field name[(length)])
    [ASC|DESC]
);

[](javascript:void(0)😉

Create index on existing table:

1.CREATE [UNIQUE|FULLTEXT|SPATIAL] INDEX Index name ON Table name{Field name[(length)] [ASC|DESC]}
2.ALTER TABLE tbl_name ADD [UNIQUE|FULLTEXT|SPATIAL] INDEX Index name(Field name[(length)][ASC|DESC]);

How to delete an index?

DROP INDEX Index name ON tbl_name
<strong>1,General index</strong><br><br>Ordinary index has only one function: accelerating query

Create table + index

[](javascript:void(0)😉

create table in1(
    nid int not null auto_increment primary key,
    name varchar(32) not null,
    email varchar(64) not null,
    extra text,
    index ix_name (name)
)

[](javascript:void(0)😉

Create index

create index index_name on table_name(column_name)
Delete index
drop index_name on table_name;
View index
show index from table_name;
Note: for index creation, if yes BLOB and TEXT Type, must be specified length. 
create index ix_extra on in1(extra(32));
<strong>2,unique index</strong>

Unique index has two functions: accelerating query and unique constraint (which can contain null)

Create table + unique index

[](javascript:void(0)😉

create table in1(
    nid int not null auto_increment primary key,
    name varchar(32) not null,
    email varchar(64) not null,
    extra text,
    unique ix_name (name)
)

[](javascript:void(0)😉

Create unique index

create unique index Index name on Table name(Listing)
Delete unique index
drop unique index Index name on Table name
<strong>3,primary key </strong>

The primary key has two functions: accelerating query and unique constraint (non null able)

Create table + create primary key

[](javascript:void(0)😉

create table in1(
    nid int not null auto_increment primary key,
    name varchar(32) not null,
    email varchar(64) not null,
    extra text,
    index ix_name (name)
)

[](javascript:void(0)😉

[](javascript:void(0)😉

create table in1(
    nid int not null auto_increment,
    name varchar(32) not null,
    email varchar(64) not null,
    extra text,
    primary key(ni1),
    index ix_name (name)
)

[](javascript:void(0)😉

Create primary key

alter table Table name add primary key(Listing);
Delete primary key
alter table Table name drop primary key;
alter table Table name  modify  Listing int, drop primary key;
<strong>4,Composite index</strong>

Combined index is to combine n columns into one index

Its application scenario is: frequently use n columns to query at the same time, such as where n1 = 'alex' and n2 = 666.

Table creation

[](javascript:void(0)😉

create table in3(
    nid int not null auto_increment primary key,
    name varchar(32) not null,
    email varchar(64) not null,
    extra text
)

[](javascript:void(0)😉

Create composite index

create index ix_name_email on in3(name,email);
After creating the composite index as above, query:
  • name and email – use index
  • name - use index
  • email - do not use index

Note: when searching n conditions at the same time, the performance of combined index is better than that of multiple single index combination.

Related commands

- View table structure
desc Table name

- View the of the generated table SQL
show create table Table name

- View index
show index from  Table name
- View execution time
set profiling = 1;
    SQL...
    show profiles;

Use index and not use index

Because the index is designed to speed up the search, the query efficiency will fly quickly after adding the index.` `# Indexed

[](javascript:void(0)😉

mysql> select * from tb1 where name = 'wupeiqi-888';
+-----+-------------+---------------------+----------------------------------+---------------------+
| nid | name        | email               | radom                            | ctime               |
+-----+-------------+---------------------+----------------------------------+---------------------+
| 889 | wupeiqi-888 | wupeiqi888@live.com | 5312269e76a16a90b8a8301d5314204b | 2016-08-03 09:33:35 |
+-----+-------------+---------------------+----------------------------------+---------------------+
1 row in set (0.00 sec)

[](javascript:void(0)😉

# No index

[](javascript:void(0)😉

mysql> select * from tb1 where email = 'wupeiqi888@live.com';
+-----+-------------+---------------------+----------------------------------+---------------------+
| nid | name        | email               | radom                            | ctime               |
+-----+-------------+---------------------+----------------------------------+---------------------+
| 889 | wupeiqi-888 | wupeiqi888@live.com | 5312269e76a16a90b8a8301d5314204b | 2016-08-03 09:33:35 |
+-----+-------------+---------------------+----------------------------------+---------------------+
1 row in set (1.23 sec)

[](javascript:void(0)😉

Use index correctly

Adding an index to a database table does make the query speed take off, but the premise must be to use the index correctly. If it is used in the wrong way, even establishing the index will not work.

Even if the index is established, the index will not take effect:

- like '%xx'
    select * from tb1 where name like '%cn';
- Use function
    select * from tb1 where reverse(name) = 'wupeiqi';
- or
    select * from tb1 where nid = 1 or email = 'seven@live.com';
    Special: when or The condition is invalid only if there are columns that have not been indexed. The following will be indexed
            select * from tb1 where nid = 1 or name = 'seven';
            select * from tb1 where nid = 1 or email = 'seven@live.com' and name = 'alex'
- Inconsistent type
    If the column is of string type, the incoming condition must be enclosed in quotation marks, otherwise...
    select * from tb1 where name = 999;
- !=
    select * from tb1 where name != 'alex'
    Special: if it is a primary key, the index will still be used
        select * from tb1 where nid != 123
- >
    select * from tb1 where name > 'alex'
    Special: if the primary key or index is of integer type, the index will still be used
        select * from tb1 where nid > 123
        select * from tb1 where num > 123
- order by
    select email from tb1 order by name desc;
    When sorting by index, if the selected mapping is not an index, the index will not be used
    Special: if the primary key is sorted, the index is still used:
        select * from tb1 order by nid desc;

- Leftmost prefix of composite index
    If the combined index is:(name,email)
    name and email       -- Use index
    name                 -- Use index
    email                -- Do not use index

Other precautions

- Avoid using``select` `*``- ``count``(1)or``count``(column) replace ``count``(*)``- When creating tables ``char` `replace ``varchar``- The field order of the table is fixed, and the field with fixed length takes precedence``- Composite index replaces multiple single column indexes (when multiple conditional queries are often used)``- Try to use short indexes``- Use connection(``JOIN``)Instead of subquery(Sub-Queries)``- When connecting tables, pay attention to the consistency of condition types``- Index hash value (less repetition) is not suitable for index building. For example, gender is not suitable
      -- Use index
    email                -- Do not use index

Other precautions

- Avoid using``select` `*``- ``count``(1)or``count``(column) replace ``count``(*)``- When creating tables ``char` `replace ``varchar``- Priority fields of fixed length table``- Composite index replaces multiple single column indexes (when multiple conditional queries are often used)``- Try to use short indexes``- Use connection(``JOIN``)Instead of subquery(Sub-Queries)``- When connecting tables, pay attention to the consistency of condition types``- Index hash value (less repetition) is not suitable for index building. For example, gender is not suitable

Keywords: Database MySQL

Added by techbinge on Fri, 04 Feb 2022 04:35:30 +0200

Popular Keywords

©2024 Programming VIP