MySQL Index and transaction

MySQL indexing and transaction skill targets

• Understanding the concept of indexes and mastering the creation of indexes
• Understand the these dumplings of a transaction and master the operation of a transaction

Concept and classification of Indexes 1: The concept of indexes

• An index is a special file that contains a reference pointer to all the records in a data table

2: Function of the index

• The purpose of indexing is to speed up the lookup or order of records in a table

(1) After setting the appropriate index, the database uses a variety of rapid positioning technology, can greatly speed up the query speed, which is also the main element to create the index (2) When the table is large, or when more than one table is involved, using an index can speed up the query to thousands of times (3) You can reduce the IO cost in the database and the index can also reduce the sorting cost of the database (4) by establishing a unique index, you can guarantee that the uniqueness of each row of data in a database table (5) Can speed up the table and the connection in the table (6) When using grouping and sorting clauses for data query, Can significantly reduce the number of groups and sorts in a query 3: Index classification The MySQL index is divided into the following categories:

• Normal index, which is the most basic type of index, and has no unique limitations
• Uniqueness index, which is basically the same as the previous normal index, except that all values in the indexed column can only occur once, and no null value is allowed
• Primary key index, the primary key index is a unique index, but it must specify "PRIMARY KEY".
• Full-text index, index type fulltext, full-text index can be created on a char varchar or text type column
• Single-column multicolumn index, which is an index created on a single column, or it can be an index created on multiple columns

Index details

Display Parameters	Description
Table	Name of the table
Non_unique	Uniqueness of index values, 0 for uniqueness, 1 for non-unique
Key_name	The name of the index
Seq_in_index	The serial number in the index starts from 1
column_name	Column Name

Experiment 1: Create an indexed method create a normal index command format ceate index< index name >no tablename (List of columns)

• Prepared a database (student information) inside has created inside (class student information Form)

Mysql> Show databases;+--------------------+| Database |+--------------------+| Information_schema | | Student Information | | MySQL | | Performance_schema | | SYS |+--------------------+5 rows in Set (0.00 sec) mysql> Use Academy Information;mysql> CREATE TABLE class student information (student number int, Name varchar (10), fractional int, hobby varchar (10)); Mysql> SELECT * FROM class student information, +--------+--------+--------+--------+| School Number | name | Score |      Hobby |+--------+--------+--------+--------+| 1 |     Zhang San | 88 |      1 | | 2 |     John Doe | 88 |      2 | | 3 |     Harry | 88 | 1 |+--------+--------+--------+--------+3 rows in Set (0.00 sec) mysql> CREATE index Name_index on class student information (student number); #创建普通索引实例Query OK, 0 rows affected (0.02 sec) records:0 duplicates:0 warnings:0mysql> Show index from class student information; #查看索引信息 +--------------------+------------+------------+--------------+-------------+-----------+-------------+- ---------+--------+------+------------+---------+---------------+|             Table | Non_unique | Key_name | Seq_in_index | column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |+--------------------+------------+------------+--------------+-------------+-----------+------- ------+----------+--------+------+------------+---------+---------------+|          Class Student Information | 1 |            Name_index | 1 | School Number |           A |     3 | NULL | NULL | YES |         BTREE |               | |+--------------------+------------+------------+--------------+-------------+-----------+-------------+------- ---+--------+------+------------+---------+---------------+1 row in Set (0.00 sec)

• Key_name corresponds to the index name (named Name_index), non_unique corresponding value is 1 means not a unique index

Create unique Indexed command format create unique index< index name >on tablename (List of columns)

• The index has a unique keyword more than the normal index, and the following creates a unique index on the name

Mysql> Create unique index name_unique_index on class student information (first name); Query OK, 0 rows Affected (0.00 sec) records:0 duplicates:0 warnings:0mysql> Show index from class student information; +-------------- ------+------------+-------------------+--------------+-------------+-----------+-------------+----------+----- ---+------+------------+---------+---------------+| Table | Non_unique | Key_name | Seq_in_index | column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |+--------------------+------------+-------------------+--------------+-------------+-----------+ -------------+----------+--------+------+------------+---------+---------------+|          Class Student Information | 0 |            Name_unique_index | 1 | name |           A |     3 | NULL | NULL | YES |         BTREE |               | |+--------------------+------------+-------------------+--------------+-------------+-----------+-------------+ ----------+--------+------+------------+---------+---------------+1 row in Set (0.00 sec) 

• An index named Name_unique_index has been created, and his non_unique is 0, indicating a unique index

There are two ways to create a primary key index (1): Create a primary key while creating a table, 2): Do not specify a primary key in a table that has already been created, and then modify the table to join the primary key, and the primary key index automatically creates the command format that creates the primary key index 1): Specify the primary key when creating table TableName ...],primary key (List of columns));

Mysql> Show tables; #在学员信息里面有三个表现在我重新创建一个新表在创建新编的同时定义主键 +------------------------+| Tables_in_ Student Information |+------------------------+| Student Hobby | | Hobbies | | Class Student Information |+------------------------+3 rows in Set (0.00 sec) mysql> CREATE TABLE student interest (number int (4) primary key, hobby varchar (10));               Query OK, 0 rows affected (0.01 sec) mysql> desc Student interest; #创建了主键的表看一下两者区别创建主键的表Key里面多一个PRI +--------+-------------+------+-----+---------+-------+| Field | Type | Null | Key | Default | Extra |+--------+-------------+------+-----+---------+-------+| Numbering | Int (4) | NO | PRI |       NULL | || Hobbies | varchar (10) |     YES | |       NULL |                |+--------+-------------+------+-----+---------+-------+2 rows in Set (0.00 sec) mysql> desc Hobby; #原有的表没有创建主键的表没有创建主键的表Key里面是空的 +--------+-------------+------+-----+---------+-------+| Field | Type | Null | Key | Default | Extra |+--------+-------------+------+-----+---------+-------+| Numbering | Int (4) |     YES | |       NULL | || Hobbies | varchar (10) |     YES | |       NULL | |+--------+-------------+------+-----+---------+-------+2 rows in Set (0.00 sec)

2): Create a table After creating a primary key after modifying the table, adding the primary key format primary key index is automatically created: ALTER TABLE tablename add primary key (List of columns);

 mysql> CREATE TABLE Student Hobby (number int (4), hobby varchar (10)); Query OK, 0 rows affected (0.01 sec) mysql> desc student Hobby, +--------+-------------+------+-----+---------+-------+| Field | Type | Null | Key | Default | Extra |+--------+-------------+------+-----+---------+-------+| Numbering | Int (4) | YES | | NULL | || Hobbies | varchar (10) | YES | | NULL | |+--------+-------------+------+-----+---------+-------+2 rows in Set (0.00 sec) mysql> ALTER TABLE student Hobby add Constraint pk_id primary key student Hobby (number); Query OK, 0 rows affected (0.02 sec) records:0 duplicates:0 warnings:0mysql> desc student hobby; +--------+-------------+---- --+-----+---------+-------+| Field | Type | Null | Key | Default | Extra |+--------+-------------+------+-----+---------+-------+| Numbering | Int (4) | NO | PRI | NULL | || Hobbies | varchar (10) | YES | | NULL | |+--------+-------------+------+-----+---------+-------+2 rows in Set (0.00 sec) 

Creating a multi-column index only requires multiple columns at the time the index is created. The command format is create index Duolie_index on class student information (student number, name);

Mysql> CREATE index Duolie_index on class student information (student number, name); Query OK, 0 rows affected (0.01 sec) records:0 duplicates:0 warnings:0mysql> Show index from class student information; +-------------- ------+------------+--------------+--------------+-------------+-----------+-------------+----------+--------+- -----+------------+---------+---------------+| Table | Non_unique | Key_name | Seq_in_index | column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |+--------------------+------------+--------------+--------------+-------------+-----------+----- --------+----------+--------+------+------------+---------+---------------+|          Class Student Information | 1 |            Duolie_index | 1 | School Number |           A |     3 | NULL | NULL | YES |         BTREE |               | ||          Class Student Information | 1 |            Duolie_index | 2 | name |           A |     3 | NULL | NULL | YES |         BTREE |               | |+--------------------+------------+--------------+--------------+-------------+-----------+-------------+------- ---+--------+------+------------+---------+---------------+2 rows in Set (0.00 sec) mysql> ALTER TABLE class student information drop Column study number; #删除多列索引中其中一个索引Query OK, 0 rows affected (0.04 sec) records:0 duplicates:0 warnings:0

Delete commands when indexes are not required (uppercase is fixed and case-insensitive in MySQL)

• DROP INDEX index_name on table_name; #大写为固定格式index_name为索引名称table_name为删除哪张表格中的索引
• ALTER TABLE table_name DROP INDEX index_name #删除那张表格的哪一个索引
• ALTER TABLE table_name Dorp PRIMARY KEY; #删除主键

View Index Drop method

• SHOW KEY from table_name
• Show index from table_name #在后面加上 \g vertical display of indexing information is: Show index from TABLE_NAME\G

Primary key and foreign key (1) Primary key

• Each row of records in a datasheet must be unique, not identical records, and the uniqueness of the record (entity) can be guaranteed by defining the primary key
• Key, which is the keyword, is a very important element in the relational model
• If a property can have a unique identity and no extra attributes, then this property set is called a candidate key.
• The primary key from the table is the foreign key in the primary table and the value unique cannot be empty

(2) Foreign key

• A relational database usually contains multiple tables, which can be closed by foreign keys.
• The foreign key in the primary table is the primary key from the table and the value unique cannot be null

There are two tables under the experiment. The main table number is the primary key hobby is the foreign key below one is from the table number is the primary key

mysql> select * from 六年级二班学生数据表;+--------+--------+--------+--------+| 编号   | 姓名   | 学分   | 爱好   |+--------+--------+--------+--------+|      1 | 张三   |     80 |      1 ||      2 | 李四   |     81 |      2 ||      3 | 王五   |     81 |      3 ||      4 | 赵六   |     99 |      2 |+--------+--------+--------+--------+4 rows in set (0.00 sec)mysql> select * from 六年级二班兴趣爱好表;+--------+-----------+| 编号   | 爱好      |+--------+-----------+|      1 | 看书      ||      2 | 玩游戏    ||      3 | 游玩      ||      4 | 打球      |+--------+-----------+4 rows in set (0.00 sec)

The above two tables associated with the query number, name, credit, hobbies, in a form to show the actual table does not exist

mysql> select i.编号,i.姓名,i.学分,h.爱好 from 六年级二班学生数据表 i join 六年级二班兴趣爱好表 h on i.爱好=+--------+--------+--------+-----------+| 编号   | 姓名   | 学分   | 爱好      |+--------+--------+--------+-----------+|      1 | 张三   |     80 | 看书      ||      2 | 李四   |     81 | 玩游戏    ||      4 | 赵六   |     99 | 玩游戏    ||      3 | 王五   |     81 | 游玩      |+--------+--------+--------+-----------+4 rows in set (0.00 sec)

MySQL database Transaction 2: The concept and characteristics of things a transaction is a mechanism, a sequence of operations that contains a set of database operations commands, and submits or revokes an operation request to the system as a whole, that is, the set of commands is either executed or revoked. A transaction is an indivisible logical unit that is a small control unit when a concurrency operation is performed on a database system. ACID Properties of transactions

• Atomicity: A transaction is a complete operation, and the elements are non-divided, that is, the atomic
• Consistency: A transaction must be in the same state when the transaction completes
• Isolation: All concurrent transactions that modify things are isolated from each other, and everything is separate
• Persistence: means that the result of a transaction is permanent regardless of whether the system has failed

Using transaction commands to control transactions

• Begin represents the start of a transaction, followed by a number of data action statements executed
• Commit represents the commit transaction, corresponding to the preceding begin action
• Rollback indicates that a transaction is rolled back, between Begin and commit if an error occurs in a database operation statement, the execution of rollback rolls back the data back to the state before begin

The experiment uses begin to add another person to the school class six grade two student data sheet

mysql> CREATE database data; Query OK, 1 row Affected (0.00 sec) mysql> use data; Database changedmysql> use college class information; Reading table information for completion of table and column namesyou can turn off this feature to get a quicker startup W Ith-adatabase changedmysql> begin; Query OK, 0 rows Affected (0.00 sec) Mysql>insert to into six grade student data sheet values (5, ' Tianqi ', 2, 1); #在表中插入这段信息Query OK, 1 row Affected (0.00 sec) mysql> Select, * from six Grade II student data sheet, +--------+--------+--------+----- ---+| Numbering | name | Credits |      Hobby |+--------+--------+--------+--------+| 1 |     Zhang San |      80 |      1 | | 2 |     John Doe |      81 |      2 | | 3 |     Harry |      81 |      3 | | 4 |     Zhao Liu |      99 |      2 | | 5 |      Tianqi |      2 | 1 |+--------+--------+--------+--------+5 rows in Set (0.00 sec) #查看表中数据有田七这个人员信息 # We'll open another terminal. Log in to the database and view the table information mysql> Select * From six grade Two class student data sheet, +--------+--------+--------+--------+| Numbering | name | Credits |      Hobby |+--------+--------+--------+--------+| 1 |  Zhang San |   80 |      1 | | 2 |     John Doe |      81 |      2 | | 3 |     Harry |      81 |      3 | | 4 |     Zhao Liu |      99 | 2 |+--------+--------+--------+--------+4 rows in Set (0.00 sec) #从另一个终端的数据库看不到田七数据因为begin只是开始一个事务并没有提交 # Commit a transaction with commit if you're afraid to make a mistake, you can leave a file here mysql> savepoint S1; Query OK, 0 rows Affected (0.00 sec) mysql> INSERT into six grade second class student data sheet values (6, ' Black Eight ', 77,4); Query OK, 1 row Affected (0.00 sec) mysql> savepoint S2; Query OK, 0 rows Affected (0.00 sec) mysql> select * from six Grade II student data sheet, +--------+--------+--------+--------+| Numbering | name | Credits |      Hobby |+--------+--------+--------+--------+| 1 |     Zhang San |      80 |      1 | | 2 |     John Doe |      81 |      2 | | 3 |     Harry |      81 |      3 | | 4 |     Zhao Liu |      99 |      2 | | 5 |      Tianqi |      2 |      1 | | 6 |     Black Eight |      77 | 4 |+--------+--------+--------+--------+6 rows in Set (0.00 sec) #如果再第二个留档中出错了用rollback to SavePoint s1;mysql> Rollback to savepoint S1; Query OK, 0 rows Affected (0.00 sec) mysql> SELECT *From six grade two class student data sheet, +--------+--------+--------+--------+| Numbering | name | Credits |      Hobby |+--------+--------+--------+--------+| 1 |     Zhang San |      80 |      1 | | 2 |     John Doe |      81 |      2 | | 3 |     Harry |      81 |      3 | | 4 |     Zhao Liu |      99 |      2 | | 5 |      Tianqi |      2 | 1 |+--------+--------+--------+--------+5 rows in Set (0.00 sec) #此时留档二中的黑八就叫已经不存在了, NOTE: Rollback to the location of the previous S1 the transaction of all operations from the bottom of the S1 does not exist.

Turn off auto-commit with Set sutocommit=0

mysql> set autocommit=0;Query OK, 0 rows affected (0.00 sec)mysql> insert into 六年级二班学生数据表 values (6,‘黑八‘,77,4);Query OK, 1 row affected (0.00 sec)mysql> select * from 六年级二班学生数据表;+--------+--------+--------+--------+| 编号   | 姓名   | 学分   | 爱好   |+--------+--------+--------+--------+|      1 | 张三   |     80 |      1 ||      2 | 李四   |     81 |      2 ||      3 | 王五   |     81 |      3 ||      4 | 赵六   |     99 |      2 ||      6 | 黑八   |     77 |      4 |+--------+--------+--------+--------+5 rows in set (0.00 sec)#上表关闭了自动提交功能然后操作事务不会自动提交相当于执行了begin同样在另一个终端登录的时候不会显示出来#如果set autocommit=1开启自动提交那么以上正在进行的事务自动提交相当于执行了begin语句中的commit

This experiment summarizes

• Database indexes are: Normal index, unique index, primary key index, full-text index, multi-column index
• Database indexes can help you quickly query data in a table, but not all fields are indexed (data at least 1000 rows to see the effect)
• ACID Properties of database things
• The MySQL transaction command has a BEGIN, rollback, Commit, savrpoint.

MySQL Index and transaction