mysql index what does it mean

In mysql, index refers to "index", which is a special database structure composed of one or more columns in the data table. It can be used to quickly query a specific column in the data table. value record. Through the index, when querying data, you do not need to read all the information of the record, but only query the index column; otherwise, the database system will read all the information of each record for matching. Therefore, using indexes can greatly improve the query speed of the database and effectively improve the performance of the database system.

The operating environment of this tutorial: windows7 system, mysql8 version, Dell G3 computer.

In mysql, index refers to "index", which is a special database structure composed of one or more columns in the data table. It can be used to quickly query a specific column in the data table. value record.

Through the index, when querying data, you do not need to read all the information in the record, but only query the index column. Otherwise, the database system will read all information of each record for matching.

The index can be compared to the phonetic sequence of the Xinhua Dictionary. For example, if you want to look up the word "ku", if you don't use phonetic sequence, you need to find it page by page in the 400 pages of the dictionary. However, if you extract the pinyin to form a phonetic sequence, you only need to look it up directly from the phonetic table of more than 10 pages. This can save a lot of time.

Therefore, using indexes can greatly improve the query speed of the database and effectively improve the performance of the database system.

Why use indexes

The index is the relationship between the column values ​​and the record rows established in a certain order based on one or several columns in the table. The correspondence table is essentially an ordered table describing the one-to-one correspondence between the column values ​​of the index columns and the record rows in the original table.

Index is a very important database object in MySQL and is the basis of database performance tuning technology. It is often used to achieve fast retrieval of data.

In MySQL, there are usually two ways to access row data of a database table:

1) Sequential access

Sequential access is to perform a full table scan on the table. Traverse row by row from beginning to end until you find target data that meets the conditions in the unordered row data.

Sequential access is relatively simple to implement, but when there is a large amount of data in the table, the efficiency is very low. For example, when searching for a small amount of data among tens of millions of data, using sequential access will traverse all the data, which will take a lot of time and will obviously affect the processing performance of the database.

2) Index access

Index access is a way to directly access record rows in the table by traversing the index.

The premise of using this method is to create an index on the table. After creating the index on the column, when searching for data, you can directly find the location of the corresponding record row based on the index on the column, so as to quickly find the data. The index stores pointers to the data values ​​of the specified columns, sorting these pointers according to the specified sort order.

For example, in the student basic information table tb_students, if an index is established based on student_id, the system will create a mapping table from the index column to the actual record. When the user needs to find the data with student_id 12022, the system first finds the record on the student_id index, then directly finds the data row through the mapping table, and returns the row of data. Because the speed of scanning indexes is generally much greater than the speed of scanning actual data rows, using indexes can greatly improve the efficiency of the database.

In short, without using an index, MySQL must read the entire table starting 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 queried column in the table has an index, MySQL can quickly get to a location to search the data file without having to look at all the data, which will save a lot of time.

Advantages and Disadvantages of Index

Index has its obvious advantages and its inevitable disadvantages.

Advantages

The advantages of index are as follows:

• By creating a unique index, each row of data in the database table can be guaranteed to be unique. sex.

• You can set indexes for all MySQL column types.

• can greatly speed up data query, which is the main reason for using indexes.

• It can speed up the connection between tables in terms of achieving referential integrity of data.

• When using grouping and sorting clauses for data query, it can also significantly reduce the time of grouping and sorting in the query

Disadvantages

Increasing indexes also has many disadvantages, mainly as follows:

• It takes time to create and maintain index groups, and as the amount of data increases, it takes The time will also increase.

• Indexes need to occupy disk space. In addition to the data space occupied by the data table, each index also occupies a certain amount of physical space. If you have a large number of indexes, the index files may reach their maximum file size faster than the data files.

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

When using indexes, you need to consider the advantages and disadvantages of indexes.

Indexes can increase query speed, but will affect the speed of inserting records. Because when inserting records into an indexed table, the database system will sort according to the index, which reduces the speed of inserting records. The speed impact will be more obvious when inserting a large number of records. In this case, the best way is to delete the index in the table first, then insert the data, and then create the index after the insertion is completed.

Index design principles

1) It should be noted that indexes also occupy disk space, so the more the better.

2) Too many indexes will affect the execution efficiency of INSERT, DELETE, UPDATE and other statements.

3) It is not recommended to create an index if there is too little data.

4) For unique constraints, the corresponding unique index should be used.

5) Try not to index fields with low discrimination. For example: the gender field of the enumeration type only has men and women, which will not achieve optimization effects.

6) It is recommended to create an index when creating the table. If there are a large number of records in the table, all records in the table will be modified after the column is created as an index.

7) It is not recommended to create indexes for fields that are not frequently queried.

Index type

Type	Function
Normal index	Basic indexing method, no special instructions
Unique index	The data is unique, null is allowed
Primary key index	There is only one primary key in the table, the data is unique, null is not allowed
Compound index	An index method that can contain two or more columns

Just select the corresponding index type according to different environments.

Create index syntax

Take an ordinary index as an example, create an index when creating a table

create table 表名(
 字段定义…
 index 索引名称(字段));

Take an ordinary index as an example, modify the table For index

alter table 表名 add index 索引名称(字段);

, you can use key instead of index, and the index name can be omitted. If no index name is added, the field name will be used as the index name by default.

Normal index

Nothing to pay attention to, the most basic indexing method.

Create a table and create a normal index. The index column is id

mysql> create table test1(id int,name varchar(5),age tinyint,index index_id(id));

View the table structure. MUL under the key represents the normal index

mysql> desc test1;
+-------+------------+------+-----+---------+-------+
| Field | Type       | Null | Key | Default | Extra |
+-------+------------+------+-----+---------+-------+
| id    | int(11)    | YES  | MUL | NULL    |       |
| name  | varchar(5) | YES  |     | NULL    |       |
| age   | tinyint(4) | YES  |     | NULL    |       |
+-------+------------+------+-----+---------+-------+

View index information

mysql> show index from test1\G
*************************** 1. row ***************************
        Table: test1
   Non_unique: 1
     Key_name: index_id		
 Seq_in_index: 1
  Column_name: id
    Collation: A
  Cardinality: 0
     Sub_part: NULL
       Packed: NULL
         Null: YES
   Index_type: BTREE
      Comment:
Index_comment:
      Visible: YES
   Expression: NULL
1 row in set (0.00 sec)

Add some data to the table for testing

mysql> insert into test1 values(1,'张三',21),(2,'李四',22),(3,'王五',23),(4,'赵六',24);

Simulate the execution of the sql query statement through explain, without going through the index first, query the record named Zhao Liu .

mysql> explain select * from test1 where name='赵六'\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: test1
   partitions: NULL
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 4
     filtered: 25.00
        Extra: Using where
1 row in set, 1 warning (0.00 sec)

type:ALL item indicates full table scan, rows: 4 indicates the number of rows. Because Zhao Liu is the last record in the table, the query traverses the entire table.

Next, query the field named Zhao Liu through the index column id.

mysql> explain select * from test1 where id=4\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: test1
   partitions: NULL
         type: ref
possible_keys: index_id
          key: index_id
      key_len: 5
          ref: const
         rows: 1
     filtered: 100.00
        Extra: NULL
1 row in set, 1 warning (0.00 sec)

rows: 1 means that only one row was retrieved and the record was found. The verdict is clear.

Unique index

In the unique index, the data in the index column is unique and no duplicate values ​​can appear. It is used to constrain the content and allows null value.

Syntax:

create table 表名（
 字段定义…
 unique key 索引名(字段名)）;

Unique indexes are commonly used in fields whose values ​​cannot be repeated, ID numbers, mobile phone numbers, etc.

Create a table and create a unique index. The index column is the mobile phone number.

mysql> create table test2(id int,name varchar(5),phone int,unique key unique_key_phone(phone));

View the table structure, UNI under the key is represented as a unique index

mysql> desc test2;
+-------+------------+------+-----+---------+-------+
| Field | Type       | Null | Key | Default | Extra |
+-------+------------+------+-----+---------+-------+
| id    | int(11)    | YES  |     | NULL    |       |
| name  | varchar(5) | YES  |     | NULL    |       |
| phone | int(11)    | YES  | UNI | NULL    |       |
+-------+------------+------+-----+---------+-------+

Insert data and test the features

mysql> insert into test2 values(1,'张三',1111111111);
mysql> insert into test2 values(2,'李四',null);	#可以为null
mysql> insert into test2 values(3,'王五',1111111111);	#值必须唯一
ERROR 1062 (23000): Duplicate entry '1111111111' for key 'unique_key_phone'

View table data

mysql> select * from test2;
+------+--------+------------+
| id   | name   | phone      |
+------+--------+------------+
|    2 | 李四   |       NULL |
|    1 | 张三   | 1111111111 |
+------+--------+------------+

Primary key index

The primary key index is to set the primary key. Each table can only have one primary key at most. Primary key column values ​​must be unique and null values ​​are not allowed.

Syntax:
create table table name (field primary key);

or

create table table name (
field definition …
primary key index name (field));

Create a table and set it as the primary key index. The index column is id

mysql> create table test3(id int primary key,name varchar(5),age tinyint);

View the table structure. The key is PRI. Primary key index column

mysql> desc test3;
+-------+------------+------+-----+---------+-------+
| Field | Type       | Null | Key | Default | Extra |
+-------+------------+------+-----+---------+-------+
| id    | int(11)    | NO   | PRI | NULL    |       |
| name  | varchar(5) | YES  |     | NULL    |       |
| age   | tinyint(4) | YES  |     | NULL    |       |
+-------+------------+------+-----+---------+-------+

Insert data and test features

mysql> insert into test3 values(1,'张三',23);
mysql> insert into test3 values(null,'张三',23);	#不能为null
ERROR 1048 (23000): Column 'id' cannot be null
mysql> insert into test3 values(1,'张三',23);	#值不能重复
ERROR 1062 (23000): Duplicate entry '1' for key 'PRIMARY'

View table data

mysql> select * from test3;
+----+--------+------+
| id | name   | age  |
+----+--------+------+
|  1 | 张三   |   23 |
+----+--------+------+

Compound index

A composite index can contain two or more columns.

There is no specific syntax, you can create dual indexes for the table

Create a table and set it as a composite primary key. The index column is id, name

mysql> create table test4 (id int,name varchar(5),age tinyint,primary key(id,name));

Composite primary key characteristics and primary key There are some differences, only one field needs to be non-repeating.

mysql> insert into test4 values(1,'张三',21);
mysql> insert into test4 values(1,'李四',21);
mysql> insert into test4 values(1,'张三',21);	#主键列全重复
ERROR 1062 (23000): Duplicate entry '1-张三' for key 'PRIMARY'

View table data

mysql> select * from test4;
+----+--------+------+
| id | name   | age  |
+----+--------+------+
|  1 | 张三   |   21 |
|  1 | 李四   |   21 |
+----+--------+------+

[Related recommendations: mysql video tutorial]

The above is the detailed content of mysql index what does it mean. For more information, please follow other related articles on the PHP Chinese website!