Shulou(Shulou.com)06/01 Report--

Let's learn about the index, transaction and view in Mysql. I believe you will benefit a lot after reading it. The text is not very good. I hope the index, transaction and view in Mysql is what you want.

The conceptual index of an index is a special file that contains reference pointers to all records in the data table. More generally, the database index is like the catalog in front of a book, which can speed up the query of the database. For example, if you need to traverse 2000 pieces of data, in the absence of an index, the database will traverse all 2000 pieces of data and select those that meet the criteria; and with the corresponding index, the database will directly look for options that meet the criteria in the index. A database index is a catalog of values in certain fields in order to improve the efficiency of table search. The function of the index (1) quickly locate, greatly speed up the query speed of the data; (2) reduce the IO (input / output) cost of the database and reduce the sorting cost of the database; (3) by creating a unique index, you can ensure the uniqueness of each row of data in the data table; (4) accelerate the relationship between the table and the table Classification of indexes (1) General index, the most basic index type, no restrictions such as uniqueness (2) unique index, all values of the index column can only appear once, that is, must be unique, there can be a null value (3) primary key index, the primary key is a unique index, but it must be specified as "PRIMARY KEY" and cannot be null. Defining a primary key for a table in the database will automatically create a primary key index. A primary key index is a special type of unique index. The index requires that each value in the primary key be unique (4) the full-text index is of type FULLTEXT, and the full-text index can create (5) multi-column indexes on columns of type CHAR,VARCHAR or TEXT. A multi-column index can distinguish between rows in which one column may have the same value. For example, if you often set query conditions for both first and last name columns in the same query, it would make sense to create indexes on both columns with more than 2000 conditional data entries. Otherwise, creating and viewing indexes is not much different from not using indexes. First enter the database school to create a data table info.

Id name score hobby

1 Zhang San 90 swimming

2 Li Si 79 read a book

2 Wang Wu 68 painting

(1) create a general index

View the index: show index from info\ G; add "\ G" after the command to display the information of the index vertically

Command format: create index index name on table name (column name)

(2) create unique index command format: create unique index index name on table (column name); for example: create unique index unique_id_index on info (id)

(3) create primary key index, there are two ways to create primary key index, one is to create primary key while creating table, the other is that the table has been created by default, no primary key is specified, and then modify the table to add primary key, the primary key index will be created automatically. Command format: alter table table name add primary key (column name)

(4) delete index command format: drop index index name on table name; for example: drop index name_index on info

Drop index unique_id_index on info

(5) delete primary key command format: alter table table name drop primary key; example: alter table info drop primary key

(6) modify the structure of the table and add a column, for example: alter table info add column age int

Delete a column from the table; alter table info drop column age

Modify the contents of the column; alter table infos change hobby hobname char (16)

(7) create a full-text index: create table infos (descript text,fulltext (descript))

(8) create a multi-column index: you only need to specify multiple columns when creating the index to create index mulit_index on info (name,score)

The concept of transaction transaction is a mechanism, an operation sequence, which contains a set of database operation commands, and submits or revokes operation requests to the system as a whole, that is, this set of database commands are either executed or none of them are executed. Transaction is an indivisible working logic unit, and transaction is the smallest control unit when performing concurrent operations on the database system. Transactions are suitable for scenarios where users operate database systems at the same time, such as banks, insurance companies and securities trading systems. Through the integrity of transactions to ensure the consistency of data. Transactions are techniques that ensure the stationarity and predictability of a set of operations. ACID characteristics of transactions A transaction has four specific characteristics: atomicity; consistency; isolation; persistence (1) atomicity: a transaction is a complete operation, and each element is inseparable, that is, atomic. All elements in the transaction must be committed or rolled back as a whole. If any element in the transaction fails, the entire transaction will fail (2) consistency: 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, the data may be in an inconsistent state; when the transaction completes successfully, the data must return to a consistent state again. (3) isolation: all concurrent transactions that modify data are isolated from each other, indicating that transactions 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. (4) persistence: the result of a transaction is permanent regardless of whether the system fails or not. Once the transaction is committed, the effect of the transaction will be permanently saved in the database using transaction commands to control the transaction begin: means to start a transaction, followed by multiple database operation statements to execute commit: to commit a transaction, corresponding to the previous begin operation, the database operation statements between them complete rollback: to roll back a transaction, between begin and commit, if an error occurs in a database operation statement Perform a rollback rollback, and the database returns to its pre-begin state, that is, the operation statements are not executed.

After reading the article on Index, transaction and View in Mysql, many readers will want to know more about it. For more industry information, you can follow our industry information section.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.