catalogue
1, Introduction to mysql index
2. Basic principles of index creation
3. Index creation and classification
4. Combined index (single column index and multi column index)
2, Introduction to mysql transaction
2. ACID characteristics of transactions
3. Isolation level of mysql transactions
4. Transaction control statement
5. Use set to set control transactions
1, Introduction to mysql index
1. The concept of index
Index of database
An index is a sorted list in which the index value and the physical address of the row containing the data containing the value are stored
After using the index, you can not scan the whole table to locate the data of a row, but first find the corresponding physical address of the row data through the index table, and then access the corresponding data, so it can speed up the query of the database.
An index is a method of sorting the values of one or more columns in a table.
The purpose of indexing is to speed up the search or sorting of records in the table
Indexing requires additional disk space
Function of index
The database can greatly speed up the query rate by using various fast positioning technologies.
When the table is large or the query involves multiple tables, the query speed can be improved thousands of times.
It can reduce the IO cost of the database and the sorting cost of the database.
Ensure the uniqueness of data in the data table by creating a unique index.
You can speed up the connection between tables.
When grouping and sorting are used, the time of grouping and sorting can be greatly reduced.
Side effects of indexing
Indexing requires additional disk space.
For MyISAM engine, index file and data file are separated, and index file is used to save the address of data record.
The table data file of InnoDB engine itself is an index file.
It takes more time to insert and modify data because the index changes with it.
2. Basic principles of index creation
Although the index can improve the speed of database query, it is not suitable to create an index in any case. Because the index itself will consume system resources, when there is an index, the database will first query the index and then locate the specific data row. If the index is not used properly, it will increase the burden on the database.
● the primary key and foreign key of the table must have an index. Because the primary key is unique, the foreign key is associated with the primary key of the sub table, which can be quickly located during query
● tables with more than 300 rows of records should have indexes. If there is no index, the table needs to be traversed, which will seriously affect the performance of the database.
● for tables that are often connected with other tables, an index should be established on the connection field.
● fields with poor uniqueness are not suitable for indexing.
● fields that are updated too frequently are not suitable for index creation.
● the fields that often appear in the where clause, especially the fields of large tables, should be indexed.
● the index should be built on highly selective fields.
● the index should be built on small fields. For large text fields or even super long fields, do not build an index.
3. Index creation and classification
Create a data table for the environment
[root@localhost ~]# mysql -uroot -p123123 #Login database (root@localhost) [(none)]> show databases; #view the database +--------------------+ | Database | +--------------------+ | information_schema | | class | | hellodb | | mysql | | performance_schema | | school | | sys | +--------------------+ 7 rows in set (0.00 sec) (root@localhost) [(none)]> creat database test; #Create Library (root@localhost) [(none)]> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | class | | hellodb | | mysql | | performance_schema | | school | | sys | | test | +--------------------+ 8 rows in set (0.00 sec) (root@localhost) [(none)]> use test; #Warehousing Database changed (root@localhost) [test]> create table info (id int,name char(40),cardid varchar(50),address varchar(50),remark text); #Create table (root@localhost) [test]> desc info; #View table +---------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +---------+-------------+------+-----+---------+-------+ | id | int(11) | YES | | NULL | | | name | char(40) | YES | | NULL | | | cardid | varchar(50) | YES | | NULL | | | address | varchar(50) | YES | | NULL | | | remark | text | YES | | NULL | | +---------+-------------+------+-----+---------+-------+ 5 rows in set (0.00 sec)
Add data
(root@localhost) [test]> insert into info values(1,'zhangsi',123123,'shangghai','vip'); Query OK, 1 row affected (0.00 sec) #Continue adding data
(root@localhost) [test]> select *from info; #View table +------+----------+--------+----------+--------+ | id | name | cardid | address | remark | +------+----------+--------+----------+--------+ | 1 | zhangsan | 123456 | beijing | vip | | 2 | zhangsi | 123123 | shanghai | vip | | 3 | liwu | 123321 | shanghai | vvip | | 4 | liliu | 654321 | hangzhou | vip | | 5 | liqi | 123654 | shenzhen | vvip | +------+----------+--------+----------+--------+ 5 rows in set (0.00 sec)
1. General index
The most basic index type has no restrictions such as uniqueness.
● create index directly
CREATE INDEX index name ON table name (column name [(length)]);
#(column name (length)): length is optional. If the value of length is ignored, the value of the entire column is used as the index.
If you specify to use the length characters before the column to create the index, it is helpful to reduce the size of the index file.
#It is recommended that index names end with "_index".
Example: create index directly
(root@localhost) [test]> create index name_index on info(name); #Add normal index Query OK, 0 rows affected (0.01 sec) Records: 0 Duplicates: 0 Warnings: 0
(root@localhost) [test]> show create table info; View table +-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Table | Create Table | +-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | info | CREATE TABLE `info` ( `id` int(11) DEFAULT NULL, `name` char(40) DEFAULT NULL, `cardid` varchar(50) DEFAULT NULL, `address` varchar(50) DEFAULT NULL, `remark` text, KEY `name_index` (`name`) #Added successfully ) ENGINE=InnoDB DEFAULT CHARSET=latin1 | +-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.00 sec)
(root@localhost) [test]> explain select name from info; +----+-------------+-------+------------+-------+---------------+------------+---------+------+------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+-------+---------------+------------+---------+------+------+----------+-------------+ | 1 | SIMPLE | info | NULL | index | NULL | name_index | 41 | NULL | 5 | 100.00 | Using index | +----+-------------+-------+------------+-------+---------------+------------+---------+------+------+----------+-------------+ 1 row in set, 1 warning (0.00 sec) #When viewing name, type is the index, and the key is name_index
Create index by modifying table mode
ALTER TABLE table name ADD INDEX index index name (column name);
(root@localhost) [test]> alter table info add index id_index(id); #Modify table to create index Query OK, 0 rows affected (0.00 sec) Records: 0 Duplicates: 0 Warnings: 0 (root@localhost) [test]> explain select id from info; +----+-------------+-------+------------+-------+---------------+----------+---------+------+------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+-------+---------------+----------+---------+------+------+----------+-------------+ | 1 | SIMPLE | info | NULL | index | NULL | id_index | 5 | NULL | 6 | 100.00 | Using index | +----+-------------+-------+------------+-------+---------------+----------+---------+------+------+----------+-------------+ 1 row in set, 1 warning (0.00 sec) #When looking for an ID, it is an index. When looking for an ID, it is an id_index
Specify the index when creating the table
CREATE TABLE table name (field 1 data type, field 2 data type [,...], INDEX index name (column name));
(root@localhost) [test]> create table info1 (id int,name char(20)not null, cardid varrchar(40)not null,index id_index(id)); #Specify the index id_index when creating the table Query OK, 0 rows affected (0.01 sec) (root@localhost) [test]> show create table info1; +-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Table | Create Table | +-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | info1 | CREATE TABLE `info1` ( `id` int(11) DEFAULT NULL, `name` char(20) NOT NULL, `cardid` varchar(40) NOT NULL, KEY `id_index` (`id`) #Created successfully ) ENGINE=InnoDB DEFAULT CHARSET=latin1 | +-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.00 sec) (root@localhost) [test]> explain select id from info1; +----+-------------+-------+------------+-------+---------------+----------+---------+------+------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+-------+---------------+----------+---------+------+------+----------+-------------+ | 1 | SIMPLE | info1 | NULL | index | NULL | id_index | 5 | NULL | 1 | 100.00 | Using index | +----+-------------+-------+------------+-------+---------------+----------+---------+------+------+----------+-------------+ 1 row in set, 1 warning (0.00 sec) Index lookup when viewing id_index
2. Unique index
It is similar to a normal index, but the difference is that each value of a unique index column is unique.
A unique index allows null values (note that it is different from the primary key). If it is created with a combined index, the combination of column values must be unique. Adding a unique key will automatically create a unique index.
Example:
Create unique index directly
CREATE UNIQUE INDEX index name ON table name (column name);
(root@localhost) [test]> select *from info; +------+----------+--------+----------+--------+ | id | name | cardid | address | remark | +------+----------+--------+----------+--------+ | 1 | zhangsan | 123456 | beijing | vip | | 2 | zhangsi | 123123 | shanghai | vip | | 3 | liwu | 123321 | shanghai | vvip | | 4 | liliu | 654321 | hangzhou | vip | | 5 | liqi | 123654 | shenzhen | vvip | | 3 | liwu | 123321 | shanghai | vvip | +------+----------+--------+----------+--------+ (root@localhost) [test]> create unique index name_index on info(name); ERROR 1062 (23000): Duplicate entry 'liwu' for key 'name_index' #Unique key each data needs to be different
(root@localhost) [test]> create unique index name_index on info(name); #Create unique key index Query OK, 0 rows affected (0.01 sec) Records: 0 Duplicates: 0 Warnings: 0 (root@localhost) [test]> show create table info; +-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Table | Create Table | +-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | info | CREATE TABLE `info` ( `id` int(11) DEFAULT NULL, `name` char(40) DEFAULT NULL, `cardid` varchar(50) DEFAULT NULL, `address` varchar(50) DEFAULT NULL, `remark` text, UNIQUE KEY `name_index` (`name`), #Created successfully KEY `id_index` (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 | +-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.00 sec)
Create by modifying the table
ALTER TABLE table name ADD UNIQUE index name (column name);
(root@localhost) [test]> alter table info add unique index cardid_index(cardid); Query OK, 0 rows affected (0.01 sec) #Create unique index by modifying table mode Records: 0 Duplicates: 0 Warnings: 0 (root@localhost) [test]> show create table info; +-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Table | Create Table | +-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | info | CREATE TABLE `info` ( `id` int(11) DEFAULT NULL, `name` char(40) DEFAULT NULL, `cardid` varchar(50) DEFAULT NULL, `address` varchar(50) DEFAULT NULL, `remark` text, UNIQUE KEY `name_index` (`name`), UNIQUE KEY `cardid_index` (`cardid`), #Created successfully KEY `id_index` (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 | +-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.00 sec)
Delete index
(root@localhost) [test]> drop index name_index on info; #Delete index name_index Query OK, 0 rows affected (0.01 sec) Records: 0 Duplicates: 0 Warnings: 0 (root@localhost) [test]> drop index cardid_index on info; #Delete index card_index Query OK, 0 rows affected (0.00 sec) Records: 0 Duplicates: 0 Warnings: 0 (root@localhost) [test]> drop index id_index on info; Delete index id_index Query OK, 0 rows affected (0.00 sec) Records: 0 Duplicates: 0 Warnings: 0 (root@localhost) [test]> show create table info; #see +-------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Table | Create Table | +-------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | info | CREATE TABLE `info` ( `id` int(11) DEFAULT NULL, `name` char(40) DEFAULT NULL, `cardid` varchar(50) DEFAULT NULL, `address` varchar(50) DEFAULT NULL, `remark` text #No index after deletion ) ENGINE=InnoDB DEFAULT CHARSET=latin1 | +-------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.00 sec)
Specify when creating a table
CREATE TABLE table name (field 1 data type, field 2 data type [,...], UNIQUE index name (column name));
(root@localhost) [test]> create table info2 (id int,name char(20)not null, cardid varrchar(40)not null,unique id_index(id)); #Create a table with a unique index Query OK, 0 rows affected (0.00 sec) (root@localhost) [test]> show create table info2; #View table info2 +-------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Table | Create Table | +-------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | info2 | CREATE TABLE `info2` ( `id` int(11) DEFAULT NULL, `name` char(20) NOT NULL, `cardid` varchar(40) NOT NULL, UNIQUE KEY `id_index` (`id`) #Successfully created unique key ) ENGINE=InnoDB DEFAULT CHARSET=latin1 | +-------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.00 sec)
3. Primary key index
Is a special unique index and must be specified as "PRIMARY KEY".
A table can only have one primary key, and null values are not allowed. Adding a primary key will automatically create a primary key index.
Example:
Create when creating a table
CREATE TABLE table name ([...], PRIMARY KEY (column name));
(root@localhost) [test]> create table info3 (id int primary key,name char(20)not null,cardid varchar(40)not null); #Specify the primary key when creating a table Query OK, 0 rows affected (0.00 sec) (root@localhost) [test]> show create table info3; +-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Table | Create Table | +-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | info3 | CREATE TABLE `info3` ( `id` int(11) NOT NULL, `name` char(20) NOT NULL, `cardid` varchar(40) NOT NULL, PRIMARY KEY (`id`) #Primary key created successfully ) ENGINE=InnoDB DEFAULT CHARSET=latin1 | +-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.00 sec)
Create by modifying the table
ALTER TABLE table name ADD PRIMARY KEY (column name);
(root@localhost) [test]> alter table info add primary key (id); #Modify table method Query OK, 0 rows affected (0.03 sec) Records: 0 Duplicates: 0 Warnings: 0 (root@localhost) [test]> show create table info; +-------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Table | Create Table | +-------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | info | CREATE TABLE `info` ( `id` int(11) NOT NULL, `name` char(40) DEFAULT NULL, `cardid` varchar(50) DEFAULT NULL, `address` varchar(50) DEFAULT NULL, `remark` text, PRIMARY KEY (`id`) #Primary key created successfully ) ENGINE=InnoDB DEFAULT CHARSET=latin1 | +-------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.00 sec)
4. Combined index (single column index and multi column index)
It can be an index created on a single column or an index created on multiple columns. It needs to meet the leftmost principle, because the where condition of the select statement is executed from left to right, so when using the select statement to query, the field order used by the where condition must be consistent with the sorting in the composite index, otherwise the index will not take effect.
CREATE TABLE table name (column name 1 data type, column name 2 data type, column name 3 data type, INDEX index name (column name 1, column name 2, column name 3));
The order of fields created by a composite index is the order in which it triggers the query of the index
select * from table name where column name 1='...' AND column name 2='...' AND column name 3 = '...';
(root@localhost) [test]> create table info4 (id int primary key,name char(20)not nulll,cardid varchar(40)not null,index inc_index(id,name,cardid)); Query OK, 0 rows affected (0.01 sec) #Create composite index (root@localhost) [test]> show create table info4; +-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Table | Create Table | +-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | info4 | CREATE TABLE `info4` ( `id` int(11) NOT NULL, `name` char(20) NOT NULL, `cardid` varchar(40) NOT NULL, PRIMARY KEY (`id`), KEY `inc_index` (`id`,`name`,`cardid`) #Composite index ) ENGINE=InnoDB DEFAULT CHARSET=latin1 | +-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.00 sec)
select id,name,cardid from info4; #The composite index is triggered select cardid,name,id from info4; #Composite indexes are not triggered
5. Full text index (FULLTEXT)
It is suitable for fuzzy query and can be used to retrieve text information in an article.
Before MySQL version 5.6, FULLTEXT index can only be used for MyISAM engine. After version 5.6, innodb engine also supports FULLTEXT index. Full TEXT indexes can be created on columns of type CHAR, VARCHAR, or TEXT. Only one full-TEXT index is allowed per table.
Example:
Create index directly
CREATE FULLTEXT INDEX name ON table name (column name);
(root@localhost) [test]> create fulltext index name_index on info(name); Query OK, 0 rows affected, 1 warning (0.07 sec) #Create full-text index directly Records: 0 Duplicates: 0 Warnings: 1 (root@localhost) [test]> show create table info; #View index +-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Table | Create Table | +-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | info | CREATE TABLE `info` ( `id` int(11) NOT NULL, `name` char(40) DEFAULT NULL, `cardid` varchar(50) DEFAULT NULL, `address` varchar(50) DEFAULT NULL, `remark` text, PRIMARY KEY (`id`), FULLTEXT KEY `name_index` (`name`) #Full text index created successfully ) ENGINE=InnoDB DEFAULT CHARSET=latin1 | +-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.00 sec)
Modify table creation
ALTER TABLE table name ADD FULLTEXT index name (column name);
(root@localhost) [test]> alter table info add fulltext cardid_index(cardid); Query OK, 0 rows affected (0.22 sec) #Modify table to create full-text index Records: 0 Duplicates: 0 Warnings: 0 (root@localhost) [test]> show create table info; #View index +-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Table | Create Table | +-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | info | CREATE TABLE `info` ( `id` int(11) NOT NULL, `name` char(40) DEFAULT NULL, `cardid` varchar(50) DEFAULT NULL, `address` varchar(50) DEFAULT NULL, `remark` text, PRIMARY KEY (`id`), FULLTEXT KEY `name_index` (`name`), FULLTEXT KEY `cardid_index` (`cardid`) #Full text index created successfully ) ENGINE=InnoDB DEFAULT CHARSET=latin1 | +-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.00 sec)
Specify the index when creating the table
CREATE TABLE table name (field 1 data type [,...], FULLTEXT index name (column name));
The data type can be CHAR, VARCHAR or TEXT
(root@localhost) [test]> create table info5 (id int ,name char(20)not null, cardid varchar(40)not null,fulltext cardid_index(cardid)); #Create a full-text index when creating a table Query OK, 0 rows affected (0.01 sec) (root@localhost) [test]> show create table info5; #View index +-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Table | Create Table | +-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | info5 | CREATE TABLE `info5` ( `id` int(11) DEFAULT NULL, `name` char(20) NOT NULL, `cardid` varchar(40) NOT NULL, FULLTEXT KEY `cardid_index` (`cardid`) #Full text index created successfully ) ENGINE=InnoDB DEFAULT CHARSET=latin1 | +-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.00 sec)
Using full-text index queries
SELECT * FROM table name where match (column name) AGAINST('query content ');
(root@localhost) [test]> select *from info5; +------+----------+--------+ | id | name | cardid | +------+----------+--------+ | 1 | zhangsan | 123 | | 2 | zhangsi | 1234 | | 3 | zhangwu | 12345 | | 4 | zhangliu | 123456 | | 5 | zhangqi | 123456 | +------+----------+--------+ 5 rows in set (0.00 sec) (root@localhost) [test]> select *from info5 where match(cardid) against(123456); +------+----------+--------+ | id | name | cardid | +------+----------+--------+ | 4 | zhangliu | 123456 | | 5 | zhangqi | 123456 | +------+----------+--------+ 2 rows in set (0.00 sec) (root@localhost) [test]> select *from info5 where cardid=123456; +------+----------+--------+ | id | name | cardid | +------+----------+--------+ | 4 | zhangliu | 123456 | | 5 | zhangqi | 123456 | +------+----------+--------+ 2 rows in set (0.00 sec)
4. View index
show index from table name;
show index from table name \ G; Vertical display of table index information
(root@localhost) [test]> show index from info5; +-------+------------+--------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | +-------+------------+--------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ | info5 | 1 | cardid_index | 1 | cardid | NULL | 5 | NULL | NULL | | FULLTEXT | | | +-------+------------+--------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ 1 row in set (0.00 sec)
show keys from table name;
show keys from table name \ G;
(root@localhost) [test]> show keys from info5; +-------+------------+--------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | +-------+------------+--------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ | info5 | 1 | cardid_index | 1 | cardid | NULL | 5 | NULL | NULL | | FULLTEXT | | | +-------+------------+--------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ 1 row in set (0.00 sec)
The meaning of each field is as follows:
Table Name of the table
Non_unique 0 if the index content is unique; 1 if it can not be unique.
Key_name The name of the index.
Seq_in_index Column ordinal in index, starting from 1. limit 2,3
Column_name Column name.
Collation How columns are stored in the index. In MySQL, there are values' A '(ascending) or NULL (no classification).
Cardinality An estimate of the number of unique values in the index.
Sub_part If the column is only partially indexed, the number of characters indexed (zhangsan). NULL if the entire column is indexed.
Packed Indicates how keywords are compressed. NULL if not compressed.
NULL If the column contains NULL, it contains YES. If not, the column contains NO.
Index_type Used indexing methods (BTREE, FULLTEXT, HASH, RTREE).
Comment remarks.
5. Delete index
DROP INDEX index name ON table name;
(root@localhost) [test]> show create table info; +-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Table | Create Table | +-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | info | CREATE TABLE `info` ( `id` int(11) NOT NULL, `name` char(40) DEFAULT NULL, `cardid` varchar(50) DEFAULT NULL, `address` varchar(50) DEFAULT NULL, `remark` text, PRIMARY KEY (`id`), FULLTEXT KEY `name_index` (`name`), FULLTEXT KEY `cardid_index` (`cardid`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 | +-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.00 sec)
(root@localhost) [test]> drop index name_index on info; #Delete index Query OK, 0 rows affected (0.01 sec) Records: 0 Duplicates: 0 Warnings: 0 (root@localhost) [test]> show create table info; +-------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Table | Create Table | +-------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | info | CREATE TABLE `info` ( `id` int(11) NOT NULL, `name` char(40) DEFAULT NULL, `cardid` varchar(50) DEFAULT NULL, `address` varchar(50) DEFAULT NULL, `remark` text, PRIMARY KEY (`id`), FULLTEXT KEY `cardid_index` (`cardid`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 | +-------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.00 sec)
Delete index by modifying table mode
ALTER TABLE table name DROP INDEX index name;
(root@localhost) [test]> alter table info drop index cardid_index; Query OK, 0 rows affected (0.00 sec) #Delete index by modifying table mode Records: 0 Duplicates: 0 Warnings: 0 (root@localhost) [test]> show create table info; +-------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Table | Create Table | +-------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | info | CREATE TABLE `info` ( `id` int(11) NOT NULL, `name` char(40) DEFAULT NULL, `cardid` varchar(50) DEFAULT NULL, `address` varchar(50) DEFAULT NULL, `remark` text, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 | +-------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.00 sec)
Delete primary key index
ALTER TABLE table name DROP PRIMARY KEY;
(root@localhost) [test]> alter table info drop primary key; #Delete primary key Query OK, 4 rows affected (0.01 sec) Records: 4 Duplicates: 0 Warnings: 0 (root@localhost) [test]> show create table info; +-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Table | Create Table | +-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | info | CREATE TABLE `info` ( `id` int(11) NOT NULL, `name` char(40) DEFAULT NULL, `cardid` varchar(50) DEFAULT NULL, `address` varchar(50) DEFAULT NULL, `remark` text ) ENGINE=InnoDB DEFAULT CHARSET=latin1 | +-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.00 sec)
2, Introduction to mysql transaction
1. Concept of transaction
MySQL transactions are mainly used to process data with large amount of operations and high complexity. For example, in the personnel management system, to delete a person, you need to delete the basic data of the person,
You also need to delete the information related to the person, such as mailbox, article, etc. In this way, these database operation statements constitute a transaction!
● transaction is a mechanism and a sequence of operations, including a set of database operation commands,
And all commands are submitted or revoked to the system as a whole, that is, this group of database commands are either executed or not executed.
● transaction is an inseparable work logic unit. When performing concurrent operations on the database system, transaction is the smallest control unit.
● transactions are applicable to the scenario of database systems operated by multiple users at the same time, such as banks, insurance companies, securities trading systems, etc.
● transaction is to ensure data consistency through transaction integrity.
The so-called transaction is a sequence of operations. These operations are either executed or not executed. It is an inseparable work unit.
2. ACID characteristics of transactions
ACID refers to the four characteristics that a transaction should have in a reliable database management system (DBMS):
Atomicity
Consistency
Isolation
Durability.
Atomicity: refers to that a transaction is an indivisible work unit, and all operations in the transaction either occur or do not occur.
A transaction is a complete operation, and the elements of a transaction are inseparable.
All elements in a transaction must be committed or rolled back as a whole.
If any element in the transaction fails, the entire transaction fails.
Case:
When A transfers 100 yuan to B, A only executes the deduction statement and submits it. At this time, if there is A sudden power failure, account A has been deducted, but account B has not received the increase,
Disputes will arise in life. In this case, transaction atomicity is required to ensure that transactions are either executed or not executed.
Consistency: refers to that the integrity constraints of the database are not destroyed before and after the transaction.
When the transaction completes, the data must be in a consistent state.
Before the transaction starts, the data stored in the database is in a consistent state.
In an ongoing transaction, data may be in an inconsistent state.
When the transaction completes successfully, the data must return to the known consistent state again.
Case:
For bank transfer transactions, whether the transaction succeeds or fails, it should be ensured that the total deposits of A and B in the table after the transaction is completed are consistent with those before the transaction is executed.
Isolation: in a concurrent environment, when different transactions manipulate the same data at the same time, each transaction has its own complete data space.
All concurrent transactions that modify data are isolated from each other, indicating that the transaction must be independent and should not depend on or affect other transactions in any way. A transaction that modifies data can access the data before another transaction that uses the same data starts, or after another transaction that uses the same data ends. The execution of one transaction cannot be disturbed by other transactions
The interaction between transactions can be divided into several types:
1. Dirty reading (reading uncommitted data): dirty reading refers to reading uncommitted data from other transactions. Uncommitted means that these data may be rolled back,
That is, it may not be stored in the database, that is, non-existent data. The data that is read and must eventually exist is dirty reading
2. Non repeatable reading (read several times before and after, and the data content is inconsistent): two identical queries in a transaction return different data. This is caused by the submission of other transaction modifications in the system during query.
3. Phantom reading (multiple times before and after reading, the total amount of data is inconsistent): a transaction modifies the data in a table, which involves all data rows in the table. At the same time, another transaction also modifies the data in the table. This modification is to insert a new row of data into the table. Then, the user who operates the previous transaction will find that there are still unmodified data rows in the table, as if there was an illusion.
4. Lost update: two transactions read the same record at the same time. A modifies the record first, and B also modifies the record (B does not know that a has modified it). After B submits the data, B's modification result overwrites a's modification result.
Persistence: after the transaction is completed, the changes made by the transaction to the database will be permanently saved in the database and will not be rolled back.
It means that the result of transaction processing is permanent regardless of whether the system fails or not.
Once the transaction is committed, the effect of the transaction will be permanently retained in the database.
3. Isolation level of mysql transactions
(1) Read uncommitted: read uncommitted data: dirty reads are not resolved
Dirty reading is allowed. As long as other transactions modify the data, even if it is not committed, this transaction can see the modified data value. That is, the number of uncommitted transaction modifications in other sessions may be read.
(2) Read committed: read committed data: dirty reads can be resolved
Only submitted data can be read. Most databases such as Oracle are at this level by default (no repeated reading)
(3) repeatable read: it can solve dirty reads and non repeatable reads - mysql default
Repeatable. No matter whether other transactions modify and commit data, the data values seen in this transaction are always unaffected by other transactions
(4) serializable: serialization: it can solve dirty reads, non repeatable reads and virtual reads - equivalent to locking tables
For fully serialized reads, table level shared locks need to be obtained for each read, and reads and writes will block each other.
The default transaction level of mysql is repeatable read, while Oracle and SQL Server are read committed.
The scope of transaction isolation level is divided into two types:
Global level: valid for all sessions
Session level: | only valid for the current session
1. Query global transaction management
show global variables like '%isolation%';
SELECT @@global.tx_isolation;
(root@localhost) [test]> show global variables like '%isolation%'; +-----------------------+-----------------+ | Variable_name | Value | +-----------------------+-----------------+ | transaction_isolation | REPEATABLE-READ | | tx_isolation | REPEATABLE-READ | +-----------------------+-----------------+ 2 rows in set (0.00 sec)
(root@localhost) [test]> SELECT @@global.tx_isolation; +-----------------------+ | @@global.tx_isolation | +-----------------------+ | REPEATABLE-READ | +-----------------------+ 1 row in set, 1 warning (0.00 sec)
2. Query session transaction isolation level
show session variables like '%isolation%';
SELECT @@session.tx_isolation;
SELECT @@tx_isolation;
(root@localhost) [test]> show session variables like '%isolation%'; +-----------------------+-----------------+ | Variable_name | Value | +-----------------------+-----------------+ | transaction_isolation | REPEATABLE-READ | | tx_isolation | REPEATABLE-READ | +-----------------------+-----------------+ 2 rows in set (0.00 sec) (root@localhost) [test]> SELECT @@session.tx_isolation; +------------------------+ | @@session.tx_isolation | +------------------------+ | REPEATABLE-READ | +------------------------+ 1 row in set, 1 warning (0.00 sec) (root@localhost) [test]> SELECT @@tx_isolation; +-----------------+ | @@tx_isolation | +-----------------+ | REPEATABLE-READ | +-----------------+ 1 row in set, 1 warning (0.00 sec)
3. Set global transaction isolation level
set global transaction isolation level read committed;
(root@localhost) [test]> set global transaction isolation level read committed; Query OK, 0 rows affected (0.00 sec) (root@localhost) [test]> SELECT @@global.tx_isolation; +-----------------------+ | @@global.tx_isolation | +-----------------------+ | READ-COMMITTED | +-----------------------+ 1 row in set, 1 warning (0.00 sec)
(root@localhost) [test]> set global transaction isolation level repeatable read; Query OK, 0 rows affected (0.00 sec) (root@localhost) [test]> SELECT @@global.tx_isolation; +-----------------------+ | @@global.tx_isolation | +-----------------------+ | REPEATABLE-READ | +-----------------------+ 1 row in set, 1 warning (0.00 sec) #Change back to the default settings
4. Set session transaction isolation level
set session transaction isolation level read committed;
(root@localhost) [test]> set session transaction isolation level read committed; Query OK, 0 rows affected (0.00 sec) (root@localhost) [test]> SELECT @@tx_isolation; +----------------+ | @@tx_isolation | +----------------+ | READ-COMMITTED | +----------------+ 1 row in set, 1 warning (0.00 sec)
Change back to the default settings
(root@localhost) [test]> set session transaction isolation level repeatable read; Query OK, 0 rows affected (0.00 sec) (root@localhost) [test]> SELECT @@tx_isolation; +-----------------+ | @@tx_isolation | +-----------------+ | REPEATABLE-READ | +-----------------+ 1 row in set, 1 warning (0.00 sec)
4. Transaction control statement
BEGIN or START TRANSACTION: explicitly start a transaction.
COMMIT or COMMIT WORK: COMMIT the transaction and make all changes made to the database permanent.
ROLLBACK or ROLLBACK WORK: ROLLBACK will end the user's transaction and undo all uncommitted changes in progress.
SAVEPOINT S1: using SAVEPOINT allows you to create a rollback point in a transaction. There can be multiple savepoints in a transaction; "S1" stands for the rollback point name.
ROLLBACK TO [SAVEPOINT] S1: rollback the transaction to the marked point.
Test:
Create a database
(root@localhost) [test]> create database bank; Query OK, 1 row affected (0.00 sec) (root@localhost) [test]> use bank; Database changed (root@localhost) [bank]> create table test(id int primary key,name char(20),money vaarchar(20)); Query OK, 0 rows affected (0.01 sec) (root@localhost) [bank]> insert into test values(1,'A',100); Query OK, 1 row affected (0.00 sec) (root@localhost) [bank]> insert into test values(2,'B',100); Query OK, 1 row affected (0.00 sec) (root@localhost) [bank]> select *from test; +----+------+-------+ | id | name | money | +----+------+-------+ | 1 | A | 100 | | 2 | B | 100 | +----+------+-------+ 2 rows in set (0.00 sec)
1. Test commit transaction
(root@localhost) [bank]> begin; #Open transaction Query OK, 0 rows affected (0.00 sec) (root@localhost) [bank]> update test set money=money-10 where name='A'; #operation Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0 (root@localhost) [bank]> select *from test; #View data +----+------+-------+ | id | name | money | +----+------+-------+ | 1 | A | 90 | | 2 | B | 100 | +----+------+-------+ 2 rows in set (0.00 sec) (root@localhost) [bank]> commit; #Commit transaction Query OK, 0 rows affected (0.00 sec) (root@localhost) [bank]> quit #Exit mysql Bye [root@localhost ~]# mysql -uroot -p123123 #Login to mysql mysql: [Warning] Using a password on the command line interface can be insecure. Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 5 Server version: 5.7.36 MySQL Community Server (GPL) Copyright (c) 2000, 2021, Oracle and/or its affiliates. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. (root@localhost) [(none)]> use bank Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Database changed (root@localhost) [bank]> select *from test; #View data +----+------+-------+ | id | name | money | +----+------+-------+ | 1 | A | 90 | | 2 | B | 100 | +----+------+-------+ 2 rows in set (0.00 sec)
2. Test rollback transaction
(root@localhost) [bank]> begin; #Open transaction Query OK, 0 rows affected (0.00 sec) (root@localhost) [bank]> update test set money=money+10 where name='A'; #operation Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0 (root@localhost) [bank]> select *from test; #see +----+------+-------+ | id | name | money | +----+------+-------+ | 1 | A | 100 | | 2 | B | 100 | +----+------+-------+ 2 rows in set (0.00 sec) (root@localhost) [bank]> rollback; #Rollback uncommitted transactions Query OK, 0 rows affected (0.00 sec) (root@localhost) [bank]> quit #Exit mysql Bye [root@localhost ~]# mysql -uroot -p123123 #Login to mysql mysql: [Warning] Using a password on the command line interface can be insecure. Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 6 Server version: 5.7.36 MySQL Community Server (GPL) Copyright (c) 2000, 2021, Oracle and/or its affiliates. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. (root@localhost) [(none)]> use bank; #Warehousing Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Database changed (root@localhost) [bank]> select *from test; #View the data table (A's money is rolled back before the operation) +----+------+-------+ | id | name | money | +----+------+-------+ | 1 | A | 90 | | 2 | B | 100 | +----+------+-------+ 2 rows in set (0.00 sec)
3. Test multipoint rollback
(root@localhost) [bank]> select *from test; #View original data +----+------+-------+ | id | name | money | +----+------+-------+ | 1 | A | 90 | | 2 | B | 100 | +----+------+-------+ 2 rows in set (0.00 sec) (root@localhost) [bank]> begin; Query OK, 0 rows affected (0.00 sec) (root@localhost) [bank]> update test set money=money-20 where name='A'; #operation Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0 (root@localhost) [bank]> savepoint s1; #Set rollback point s1 Query OK, 0 rows affected (0.00 sec) (root@localhost) [bank]> select *from test; #see +----+------+-------+ | id | name | money | +----+------+-------+ | 1 | A | 70 | | 2 | B | 100 | +----+------+-------+ 2 rows in set (0.00 sec) (root@localhost) [bank]> update test set money=money+20 where name='B'; #operation Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0 (root@localhost) [bank]> savepoint s2; #Set rollback point s2 Query OK, 0 rows affected (0.00 sec) (root@localhost) [bank]> select *from test; #View data +----+------+-------+ | id | name | money | +----+------+-------+ | 1 | A | 70 | | 2 | B | 120 | +----+------+-------+ 2 rows in set (0.00 sec) (root@localhost) [bank]> insert into test values(3,'C',200); #Add data Query OK, 1 row affected (0.00 sec) (root@localhost) [bank]> select *from test; #View data +----+------+-------+ | id | name | money | +----+------+-------+ | 1 | A | 70 | | 2 | B | 120 | | 3 | C | 200 | +----+------+-------+ 3 rows in set (0.00 sec) (root@localhost) [bank]> rollback to s1; #Rollback to node s1 Query OK, 0 rows affected (0.00 sec) (root@localhost) [bank]> select *from test; #see +----+------+-------+ | id | name | money | +----+------+-------+ | 1 | A | 70 | | 2 | B | 100 | +----+------+-------+ 2 rows in set (0.00 sec)
5. Use set to set control transactions
SET AUTOCOMMIT=0; # Disable automatic submission
SET AUTOCOMMIT=1; # Enable automatic submission. Mysql defaults to 1
SHOW VARIABLES LIKE 'AUTOCOMMIT'； # View the autocommit value in Mysql
If Auto commit is not enabled, all mysql operations connected to the current session will be treated as a transaction until you enter rollback commit; The current transaction is over.
Before the end of the current transaction, the operation results of any current session cannot be read when a new mysql connection is made.
If Auto commit is enabled, mysql will treat each sql statement as a transaction, and then automatically commit.
Of course, whether it is turned on or not, begin; commit|rollback; Are independent affairs.
Example:
(root@localhost) [bank]> select *from test; #Original data +----+------+-------+ | id | name | money | +----+------+-------+ | 1 | A | 70 | | 2 | B | 100 | +----+------+-------+ 2 rows in set (0.00 sec) (root@localhost) [bank]> set autocommit=0; #Turn off auto commit transactions Query OK, 0 rows affected (0.00 sec) (root@localhost) [bank]> show variables like 'autocommit'; #View transaction commit status +---------------+-------+ | Variable_name | Value | +---------------+-------+ | autocommit | OFF | +---------------+-------+ 1 row in set (0.00 sec) (root@localhost) [bank]> update test set money=money-50 where name='A'; #operation Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0 (root@localhost) [bank]> select *from test; #View data +----+------+-------+ | id | name | money | +----+------+-------+ | 1 | A | 20 | | 2 | B | 100 | +----+------+-------+ 2 rows in set (0.00 sec) (root@localhost) [bank]> quit #sign out Bye [root@localhost ~]# mysql -uroot -p123123 mysql: [Warning] Using a password on the command line interface can be insecure. Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 12 Server version: 5.7.36 MySQL Community Server (GPL) Copyright (c) 2000, 2021, Oracle and/or its affiliates. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. (root@localhost) [(none)]> use bank; Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Database changed (root@localhost) [bank]> select *from test; #View data +----+------+-------+ | id | name | money | +----+------+-------+ | 1 | A | 70 | | 2 | B | 100 | +----+------+-------+ 2 rows in set (0.00 sec) #Because the automatic commit transaction is turned off, the operation needs to be committed manually, but the operation just now did not commit the transaction. Therefore, the operation done to exit the login database is not saved, only the original data.