1. Preface
In the previous chapter, we explained the manual compilation and installation process of MySQL and related database operation commands (sql statements). This article will introduce the concept and principle of MySQL index and transaction in detail, and initially understand the concept of MySQL database view, and briefly describe the stored procedures of the database.
2. Index
2.1 Index concept - what is an index?
Generally speaking, a paper, or a book, has its own contents, while a catalog is generally an overview of all chapters, or the core of the essentials, and the concept of index is similar.
Indexes, as the name implies, are a guide for users to search for the resources they need, but in databases, indexes are generally considered a special kind of file, especially in Linux systems ("Everything Files").Explaining its meaning in technical terms is "a reference pointer representing a record".
The Role of 2.2 Indexes
- Speed up the query speed and improve work efficiency;
- Reduce i/o costs and provide basic sorting;
- Accelerate the connection between multiple tables;
- Supports uniqueness indexes (what is a uniqueness index is described below) to ensure the uniqueness of data in a data table;
Classification of 2.3 Index
2.3.1 General Index
Is a general index, just to distinguish it from other special indexes
2.3.2 Uniqueness Index
The difference is that all the values in the columns of the uniqueness index are different, that is, unique.
For a simple example, the student data table, age can be a normal index, but not a unique index, but the detailed address can be.
2.3.3 Primary Key Index
Essentially, it is also a unique index, but it must be specified as "primary key", which requires each value in the primary key to be unique.In the previous article, we mentioned the concept of primary key, which is also characterized by "not empty and unique".
2.3.4 Full-text Index
The index type is FULLTEXT, and full-text indexes can be created on columns of type char, vachar, or text.
2.3.5 Single-column and Multi-column Indexes
You can create an index on one or more columns.A multicolumn index is generally used to distinguish rows where one column may have the same value.
2.4 Principles for creating indexes: Table-based (indexes depend on tables)
Indexes can speed up database queries, but not all tables need to be indexed.Since the index itself also requires system resources, this may not be a prominent problem in general, because the server's resources are still supported to some extent, but if the index file is too large, it may be as large as the maximum file limit allowed by the operating system.
Also, improper use of indexes can cause a burden on the database.Therefore, database indexing has its own principles.
2.4.1 Guidelines for Index Creation
- Primary and foreign keys of a table must have indexes;
- Tables up to a certain amount (300 rows of records) should be indexed;
- Indexing should be done on joined fields between tables;
- Fields with poor uniqueness cannot be used as indexes;
- Fields that are frequently updated are also not suitable for indexing;
- Small fields are suitable for indexing, while long fields are not.
Advantages and disadvantages of 2.5 index
Advantages: Quick query of required resources
Disadvantages: space and resources
2.6 Create and view indexes
First, we need to make sure that there are tables and data in the database.
mysql> select * from fruit_info; +----+-------+---------+ | id | price | newtype | +----+-------+---------+ | 1 | 2.50 | banana | | 2 | 5.50 | apple | | 3 | 6.00 | peach | +----+-------+---------+ 3 rows in set (0.00 sec)
2.6.1 Creating a Normal Index
Command format: create index <index name> on data table (column name);
Example:
mysql> create index id_index on fruit_info(id); Query OK, 0 rows affected (0.00 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> show index from fruit_info; #Viewing the index statement can also change index to "keys" +------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | +------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ | fruit_info | 1 | id_index | 1 | id | A | 3 | NULL | NULL | | BTREE | | | +------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ 1 row in set (0.00 sec)
Non_unique1 means not a unique index; Key_name corresponds to the index name, where is id_index;
2.6.2 Creating a Unique Index
Command format: create unique index <index name> on data table (column name);
Example:
mysql> create unique index type_index on fruit_info(newtype); Query OK, 0 rows affected (0.01 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> show index from fruit_info; +------------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | +------------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ | fruit_info | 0 | type_index | 1 | newtype | A | 3 | NULL | NULL | YES | BTREE | | | | fruit_info | 1 | id_index | 1 | id | A | 3 | NULL | NULL | | BTREE | | | +------------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ 2 rows in set (0.00 sec)
The index name here is type_index, with a value of 0 for the corresponding Non_unique, indicating that it is a unique index.And the uniqueness index values are different.
So let's consider the question: can the fields of a table in a database be either ordinary indexes or unique indexes?
Let's verify:
mysql> create unique index id_index_new on fruit_info(id); Query OK, 0 rows affected (0.03 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> show index from fruit_info; +------------+------------+--------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | +------------+------------+--------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ | fruit_info | 0 | id_index_new | 1 | id | A | 3 | NULL | NULL | | BTREE | | | | fruit_info | 0 | type_index | 1 | newtype | A | 3 | NULL | NULL | YES | BTREE | | | | fruit_info | 1 | id_index | 1 | id | A | 3 | NULL | NULL | | BTREE | | | +------------+------------+--------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ 3 rows in set (0.00 sec)
It turns out to be possible, but we need to know that the difference between a unique index and a normal index is in "uniqueness".If you create a unique index, you need to be aware of the uniqueness of field matching when inserting data records.
2.6.3 Create Primary Key Index
Command format: (1) Create primary key when creating table: create table table table name ([...], primary key (list of columns));
(2) Modify the table structure to add the primary key: alter table table name add primary key;
Example:
mysql> create table student (id int not null,sex char(2),age int not null,hobby varchar(20),primary key(id,hobby)); Query OK, 0 rows affected (0.01 sec) mysql> show index from student; +---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | +---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ | student | 0 | PRIMARY | 1 | id | A | 0 | NULL | NULL | | BTREE | | | | student | 0 | PRIMARY | 2 | hobby | A | 0 | NULL | NULL | | BTREE | | | +---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ 2 rows in set (0.01 sec)
There can only be one primary key in a table, but a primary key can consist of multiple fields.
mysql> alter table fruit_info add primary key(id); Query OK, 0 rows affected (0.04 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> show index from fruit_info; +------------+------------+--------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | +------------+------------+--------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ | fruit_info | 0 | PRIMARY | 1 | id | A | 4 | NULL | NULL | | BTREE | | | | fruit_info | 0 | id_index_new | 1 | id | A | 4 | NULL | NULL | | BTREE | | | | fruit_info | 0 | type_index | 1 | newtype | A | 4 | NULL | NULL | YES | BTREE | | | | fruit_info | 1 | id_index | 1 | id | A | 4 | NULL | NULL | | BTREE | | | +------------+------------+--------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ 4 rows in set (0.00 sec)
The primary key index is created automatically after the primary key is set. The system automatically generates the name "primary" without specifying a name.The difference between primary key index and uniqueness index is that the uniqueness index can be null, while the primary key index is not null, so it can be easily understood by a formula: primary index = not null + unique index;
2.6.4 Full-text Index
The types of fields that a full-text index can create are mentioned earlier, and the command format is as follows:
1. create table table table name (column name text, FULLTEXT) engine=MyISAM;
2. alter table table table name add FULLTEXT (column name);
Example:
Look at the type of storage engine for your database: (Storage engine we'll cover in the next article)
mysql> show table status from fruit where name='student'\G
*************************** 1. row ***************************
Name: student
Engine: InnoDB
Version: 10
Row_format: Dynamic
Rows: 0
Avg_row_length: 0
Data_length: 16384
Max_data_length: 0
Index_length: 16384
Data_free: 0
Auto_increment: NULL
Create_time: 2020-01-06 19:12:24
Update_time: NULL
Check_time: NULL
Collation: utf8_general_ci
Checksum: NULL
Create_options:
Comment:
1 row in set (0.00 sec)
mysql> alter table student add fulltext(hobby); Query OK, 0 rows affected (0.02 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> mysql> show keys from student; +---------+------------+-------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | +---------+------------+-------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ | student | 0 | PRIMARY | 1 | id | A | 0 | NULL | NULL | | BTREE | | | | student | 0 | PRIMARY | 2 | hobby | A | 0 | NULL | NULL | | BTREE | | | | student | 1 | hobby | 1 | hobby | NULL | 0 | NULL | NULL | | FULLTEXT | | | +---------+------------+-------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ 3 rows in set (0.00 sec)
2.6.5 Multi-column Index
Specify multiple columns when creating an index
Command format: create index name on table name (field 1, field 2);
Example:
mysql> create index mo_index on student(id,hobby)
-> ;
Query OK, 0 rows affected (0.00 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> show index from student;
+---------+------------+-------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+---------+------------+-------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| student | 0 | PRIMARY | 1 | id | A | 0 | NULL | NULL | | BTREE | | |
| student | 0 | PRIMARY | 2 | hobby | A | 0 | NULL | NULL | | BTREE | | |
| student | 1 | mo_index | 1 | id | A | 0 | NULL | NULL | | BTREE | | |
| student | 1 | mo_index | 2 | hobby | A | 0 | NULL | NULL | | BTREE | | |
| student | 1 | hobby | 1 | hobby | NULL | 0 | NULL | NULL | | FULLTEXT | | |
+---------+------------+-------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
6 rows in set (0.00 sec)
2.7 Delete Index
Command format:
2.7.1 Delete Index Directly
Format: drop index index name on table name;
Example:
mysql> drop index mo_index on student; Query OK, 0 rows affected (0.01 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> show index from student; +---------+------------+-------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | +---------+------------+-------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ | student | 0 | PRIMARY | 1 | id | A | 0 | NULL | NULL | | BTREE | | | | student | 0 | PRIMARY | 2 | hobby | A | 0 | NULL | NULL | | BTREE | | | | student | 1 | hobby | 1 | hobby | NULL | 0 | NULL | NULL | | FULLTEXT | | | +---------+------------+-------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ 4 rows in set (0.00 sec)
2.7.2 Modifying a table is deleting an index
Format: alter table table table name drop index name;
Example:
mysql> alter table student drop index hobby; Query OK, 0 rows affected (0.01 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> show index from student; +---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | +---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ | student | 0 | PRIMARY | 1 | id | A | 0 | NULL | NULL | | BTREE | | | | student | 0 | PRIMARY | 2 | hobby | A | 0 | NULL | NULL | | BTREE | | | +---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ 2 rows in set (0.00 sec)
2.7.3 Delete Primary Key Index
Format: alter table table name drop primary key;
Example:
mysql> alter table student drop primary key; Query OK, 0 rows affected (0.01 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> show index from student; Empty set (0.00 sec)
Of course, if you delete a column that contains an index when you modify the table structure, the corresponding index will also be deleted.
3. Transactions
3.1 Transaction concept
Execute multiple commands as a whole, either successfully or not, such as bank transfers;
If the first part succeeds and the last part fails when the transaction is executed, a "rollback" mechanism is applied, which returns the execution to the starting point of the transaction without any consequential changes in the data.
ACID features of 3.2 transactions (4)
3.2.1 Atomicity
Transactions are an inseparable logical unit of work. Transactions can contain multiple sql statements, but the entire transaction is a complete operation and inseparable; (e.g. transfer, once an error occurs in the transfer process, it rolls back to its original state, and their assets do not change)
3.2.2 Consistency
Data must be consistent before and after the transaction is executed, but the execution process is dynamic; (e.g. transfer, the sum of assets of both parties before and after the transfer is constant (regardless of other charges such as handling fees)
3.2.3 Isolation
Concurrent transactions are isolated from each other and must be independent of each other (e.g. phone calls, A and B calls will not affect C and D calls)
3.2.4 Persistence
Transaction outcomes are permanent and irreversible (for example, the result of a transfer, if a dispute is resolved again, it is another transaction.)
Operations of 3.3 Transactions
3.3.1 Autosubmit
By default, it is automatically committed, that is, it automatically commits to execute the command when an sql statement is entered, but generally this is not safe;
3.3.2 Manual Submission
In production environments, this mode is used more because manual submissions can be cached and the results of in-memory data display can be checked for errors or rollbacks (rollbacks are typically set).
3.3.3 Use Transaction Commands to Control Transactions (4)
begin: indicates the start of a transaction followed by multiple sql statements; 0
Commit: means commit a transaction, corresponding to begin
Rollback: means to roll back a transaction, between begin and rollback, when an error occurs.
savepoint: Sets the rollback point for use with the rollback command.
Example:
First we have a table like this:
mysql> desc fruit_info; #Table structure +---------+--------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +---------+--------------+------+-----+---------+-------+ | id | int(4) | NO | PRI | NULL | | | price | decimal(3,2) | NO | | NULL | | | newtype | varchar(6) | YES | UNI | NULL | | +---------+--------------+------+-----+---------+-------+ 3 rows in set (0.00 sec) mysql> select * from fruit_info; #table data +----+-------+---------+ | id | price | newtype | +----+-------+---------+ | 1 | 2.50 | banana | | 2 | 5.50 | apple | | 3 | 6.00 | peach | | 4 | 6.00 | orange | +----+-------+---------+ 4 rows in set (0.00 sec) mysql> begin; #Flag to start a transaction Query OK, 0 rows affected (0.00 sec) mysql> insert into fruit_info values(5,4,'pear'); #Insert a record Query OK, 1 row affected (0.00 sec) mysql> select * from fruit_info; #At this point, just put it in the cache and use rollback to return to its original state; +----+-------+---------+ | id | price | newtype | +----+-------+---------+ | 1 | 2.50 | banana | | 2 | 5.50 | apple | | 3 | 6.00 | peach | | 4 | 6.00 | orange | | 5 | 4.00 | pear | +----+-------+---------+ 5 rows in set (0.00 sec) mysql> rollback; Query OK, 0 rows affected (0.00 sec) mysql> select * from fruit_info; +----+-------+---------+ | id | price | newtype | +----+-------+---------+ | 1 | 2.50 | banana | | 2 | 5.50 | apple | | 3 | 6.00 | peach | | 4 | 6.00 | orange | +----+-------+---------+ 4 rows in set (0.00 sec) mysql> insert into fruit_info values(5,4,'pear'); Query OK, 1 row affected (0.00 sec) mysql> commit; #rollback cannot be used to return to its original state after submission; Query OK, 0 rows affected (0.00 sec) mysql> select * from fruit_info; +----+-------+---------+ | id | price | newtype | +----+-------+---------+ | 1 | 2.50 | banana | | 2 | 5.50 | apple | | 3 | 6.00 | peach | | 4 | 6.00 | orange | | 5 | 4.00 | pear | +----+-------+---------+ 5 rows in set (0.00 sec) mysql> rollback; Query OK, 0 rows affected (0.00 sec) mysql> select * from fruit_info; +----+-------+---------+ | id | price | newtype | +----+-------+---------+ | 1 | 2.50 | banana | | 2 | 5.50 | apple | | 3 | 6.00 | peach | | 4 | 6.00 | orange | | 5 | 4.00 | pear | +----+-------+---------+ 5 rows in set (0.01 sec)
Set Breakpoint - Rollback Point
mysql> begin; Query OK, 0 rows affected (0.00 sec) mysql> insert into fruit_info values(5,4,'pear'); ERROR 1062 (23000): Duplicate entry '5' for key 'PRIMARY' mysql> insert into fruit_info values(6,4,'grape'); Query OK, 1 row affected (0.00 sec) mysql> savepoint s1; Query OK, 0 rows affected (0.00 sec) mysql> insert into fruit_info values(7,4,'cherry'); Query OK, 1 row affected (0.00 sec) mysql> savepoint s2; Query OK, 0 rows affected (0.00 sec) mysql> select * from fruit_info; +----+-------+---------+ | id | price | newtype | +----+-------+---------+ | 1 | 2.50 | banana | | 2 | 5.50 | apple | | 3 | 6.00 | peach | | 4 | 6.00 | orange | | 5 | 4.00 | pear | | 6 | 4.00 | grape | | 7 | 4.00 | cherry | +----+-------+---------+ 7 rows in set (0.00 sec) mysql> rollback to savepoint s2; Query OK, 0 rows affected (0.00 sec) mysql> select * from fruit_info; +----+-------+---------+ | id | price | newtype | +----+-------+---------+ | 1 | 2.50 | banana | | 2 | 5.50 | apple | | 3 | 6.00 | peach | | 4 | 6.00 | orange | | 5 | 4.00 | pear | | 6 | 4.00 | grape | | 7 | 4.00 | cherry | +----+-------+---------+ 7 rows in set (0.00 sec) mysql> rollback to savepoint s1; Query OK, 0 rows affected (0.00 sec) mysql> select * from fruit_info; +----+-------+---------+ | id | price | newtype | +----+-------+---------+ | 1 | 2.50 | banana | | 2 | 5.50 | apple | | 3 | 6.00 | peach | | 4 | 6.00 | orange | | 5 | 4.00 | pear | | 6 | 4.00 | grape | +----+-------+---------+ 6 rows in set (0.00 sec)
mysql> rollback; Query OK, 0 rows affected (0.00 sec) mysql> select * from fruit_info; +----+-------+---------+ | id | price | newtype | +----+-------+---------+ | 1 | 2.50 | banana | | 2 | 5.50 | apple | | 3 | 6.00 | peach | | 4 | 6.00 | orange | | 5 | 4.00 | pear | +----+-------+---------+ 5 rows in set (0.00 sec) mysql> rollback to savepoint s1; ERROR 1305 (42000): SAVEPOINT s1 does not exist
Based on the above results, we can draw the following conclusions:
Rollback mechanisms can be implemented by using transaction commands to control transactions.
When using savepoint in conjunction with the rollback command, the location of the rollback is the final location based on the command you executed.
If you use the rollback command directly, you go back directly to the original state and cannot go back to other rollback nodes.
3.3.4set command
Set autocommit=0: No autocommit - equivalent to begin;
set autocommit-=1: turn on autocommit
4. Summary
This paper mainly introduces the concepts of index and transaction in MySQL database. The concepts and principles here and the corresponding usage scenarios need to be understood in detail with examples.The classification of indexes, the difference between primary key index and uniqueness index, the four main characteristics of transactions and the rollback mechanism of transactions.
The operation of this article is not difficult, but the theoretical understanding of the details is rather trivial and requires a real understanding. This is very important during the interview. Thank you for your reading!