Mysql-Index + transaction

Article contents 1, index: (1) database index: (2) function of index: (3) index classification: (4) principle of index creation: (5) method of creating index: (2) transaction: (1) transaction concept: (2) transaction four characteristics: (3) transaction operation: three, two table join query: first, index: (1) database index: 1, in the database The index enables the data program to find the data it needs without scanning the entire table. 2. An index in a database is a collection of one or more column values in a table, and a list of logical pointers to the data pages that physically identify these values. (2) the function of the index: (1) the database can greatly speed up the query rate; (2) reduce the IO cost of the database, and the index can also reduce the sorting cost of the database; (3) to ensure the uniqueness of the data table by creating a unique index; (4) to speed up the connection between tables. 5. When grouping and sorting, the time of grouping and sorting can be greatly reduced. (3) Index classification: 1. General index: the most basic type of index, and there are no restrictions such as uniqueness; 2. Unique index: basically similar to ordinary index, the difference is that all values of index columns can only appear once, that is, they must be unique. 3. Primary key: the primary key is a unique index and must be specified as "primary key". 4. Full-text index: mysql supports full-text index and full-text search since version 3.23.23. In mysql, the index type of full-text index is fulltext, and full-text index can be created on columns of varchar or text type. 5. Single-column index and multi-column index: the index can be an index created on a single column or an index created on multiple columns. (4) the principle of index creation: the primary key and foreign key of a table must have an index; a table with more than 300 rows of data should have an index; a table that is often joined with other tables should establish an index on the join field; fields with poor uniqueness are not suitable for indexing; fields that are updated too frequently are not suitable for indexing; fields that often appear in the where clause, especially those in large tables, should be indexed. The index should be based on highly selective fields; the index should be based on small fields, and do not index large text fields or even ultra-long fields. (5) methods to create an index: 1. Create a general index: # # create a general index create index to define the index name on table name (column name 1, column name 2.); # # View index show index from info;## delete index drop index index_age on info

For example, let's create an index for the age column for the following table:

2. Create a unique index: create unique index on tablename (list of columns); # # another way: change the table construction method: alter table table name add index index name (column name); # # third way: it can be defined directly when the table is created

3. Create a primary key index: # # creata table tablename ([..], primary key (column list) when creating a new table; # # set an alter table tablename add primary key (column list) on an existing table 4. Create full-text index: create fulltext index index name on table name (list of columns); 5. Create combined index: create index index name on table name (list 1 of columns, list 2 of columns); 6. View index: show index from tablename; show keys from tablename Transaction: (1) transaction concept: to put it simply, all orders are submitted or revoked as a whole, either executed together or not executed together. (2) the four characteristics of transactions: 1, atomicity: all elements must be committed or rolled back as a whole, and if any element fails, the whole transaction will fail; for example, we usually download software, etc., if the installation fails due to a failure, it will return to the state before the installation of the software. 2. Consistency: before the transaction starts, the data is in a unified and consistent state; after the transaction is completed, it returns to the consistent state again. 3. Isolation: all concurrent transactions are independent, irrelevant and influence each other. 4. Persistence: once the transaction is committed, it is permanently retained in the database. (3) transaction operation: by default, all transactions in mysql commit automatically, and when the sql statement commits, the transaction commits automatically; the method of manually controlling the transaction (transaction command control or using set command control): 1. Transaction command control transaction: begin: start a transaction; commit: commit a transaction; rollback: roll back a transaction

Next, we start to roll back to which node we want to go back to, just go back to that archive point:

To roll back to the original state, use the rollback command directly:

2. Use set command to control: set autocommit=0: disable autocommit; set autocommit=1: enable autocommit.

3. A query connected by two tables: for example, there are two tables below. The first is named suer; and the second is called hob. Because these two tables are associated with some information, how to do it:

Enter the following command to associate the two tables: 1. Direct association: select * from user inner join hob on user.hobby=hob.id

2. You can also associate by condition: select user.name,hob.name from user inner join hob on user.hobby=hob.id