MySQL learning notes

Posted by ashutosh.titan on Tue, 04 Jan 2022 14:29:17 +0100

MySQL learning notes (IV)

MySQL foreign key constraints

By establishing foreign keys, you can set constraints between tables and restrict data entry.

Main table (class)

Serial numberDepartment nameDepartment address
1marketBeijing
2personnel mattersShanghai
3engineeringNanjing

Sub table (employee table)

Employee numberEmployee nameDepartment name
1Zhang Sanmarket
2Li Sipersonnel matters
3Wang Wuengineering
# Prepare data
mysql> create table `Department table` (
    -> `Department number` int primary key auto_increment,
    -> `Department name` varchar(10),
    -> `Department address` varchar(10));
Query OK, 0 rows affected (0.01 sec)

mysql> create table `Employee table` ( `Employee number` int primary key auto_increment, `Employee name` varchar(10), `Department name` varchar(10));
Query OK, 0 rows affected (0.00 sec)

mysql> DESC `Department table`;
+--------------+-------------+------+-----+---------+----------------+
| Field        | Type        | Null | Key | Default | Extra          |
+--------------+-------------+------+-----+---------+----------------+
| Department number       | int(11)     | NO   | PRI | NULL    | auto_increment |
| Department name     | varchar(10) | YES  |     | NULL    |                |
| Department address     | varchar(10) | YES  |     | NULL    |                |
+--------------+-------------+------+-----+---------+----------------+
3 rows in set (0.00 sec)

mysql> DESC `Employee table`;
+--------------+-------------+------+-----+---------+----------------+
| Field        | Type        | Null | Key | Default | Extra          |
+--------------+-------------+------+-----+---------+----------------+
| Employee number       | int(11)     | NO   | PRI | NULL    | auto_increment |
| Employee name     | varchar(10) | YES  |     | NULL    |                |
| Department name     | varchar(10) | YES  |     | NULL    |                |
+--------------+-------------+------+-----+---------+----------------+
3 rows in set (0.00 sec)

mysql> insert into `Department table` values(1,"market","Beijing"),(2,"personnel matters","Shanghai"),(3,"engineering","Nanjing");
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> select * from `Department table`;
+-----------+--------------+--------------+
| Department number    | Department name     | Department address     |
+-----------+--------------+--------------+
|         1 | market         | Beijing         |
|         2 | personnel matters         | Shanghai         |
|         3 | engineering         | Nanjing         |
+-----------+--------------+--------------+
3 rows in set (0.00 sec)

mysql> insert into `Employee table` values(1,"Zhang San","market"),(2,"Li Si","personnel matters"),(3,"Wang Wu","engineering");
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> select * from `Employee table`;
+-----------+--------------+--------------+
| Employee number    | Employee name     | Department name     |
+-----------+--------------+--------------+
|         1 | Zhang San         | market         |
|         2 | Li Si         | personnel matters         |
|         3 | Wang Wu         | engineering         |
+-----------+--------------+--------------+
3 rows in set (0.00 sec)

# Add foreign key constraint
mysql> create table `Employee table` ( `Employee number` int primary key auto_increment, `Employee name` varchar(10), `Department number` int , foreign key(`Department number`) references `Department table`(`Department number`));
Query OK, 0 rows affected (0.00 sec)

mysql> DESC `Employee table`;
+--------------+-------------+------+-----+---------+----------------+
| Field        | Type        | Null | Key | Default | Extra          |
+--------------+-------------+------+-----+---------+----------------+
| Employee number       | int(11)     | NO   | PRI | NULL    | auto_increment |
| Employee name     | varchar(10) | YES  |     | NULL    |                |
| Department number       | int(11)     | YES  | MUL | NULL    |                |
+--------------+-------------+------+-----+---------+----------------+
3 rows in set (0.00 sec)

# Fill in data for employee table. When the department number of employee table exists in department table, it can be added normally:
mysql> insert into `Employee table` values(1,"zhangsan",1);
Query OK, 1 row affected (0.00 sec)

mysql> select * from `Employee table`;
+-----------+--------------+-----------+
| Employee number    | Employee name     | Department number    |
+-----------+--------------+-----------+
|         1 | zhangsan     |         1 |
+-----------+--------------+-----------+
1 row in set (0.00 sec)
# If the Department no. of the employee table does not exist in the Department table, an error will be reported:
mysql> insert into `Employee table` values(2,"Li Si",4);
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`test01`.`Employee table`, CONSTRAINT `Employee table_ibfk_1` FOREIGN KEY (`Department number`) REFERENCES `Department table` (`Department number`))

Foreign key settings on update cascade and on delete cascade
This is an optional option of database foreign key definition. It is used to set the transformation rules of the response field in the foreign key table when the data of the referenced column in the primary key table changes.
update updates the value of the referenced field in the primary key table. delete deletes a record in the primary key table:
on update and on delete can be followed by four words
no action ， set null ， set default ，cascade
No action means no action,
set null indicates that the corresponding field is set to null in the foreign key table
set default means set as the default value
Cascade means cascade operation, that is, if the referenced fields in the primary key table are updated, the foreign key table is also updated, the records in the primary key table are deleted, and the changed rows in the foreign key table are deleted accordingly.
Cascade update is based on the previously matched data. After the master table updates the value of the associated foreign key field, the system automatically updates the value of the corresponding foreign key field in the slave table instead of other fields that are not set to be associated with the master foreign key. Fields that are not associated with the master foreign key are not affected.

# on update cascade
# on delete cascade
mysql> create table `Employee table 1` ( `Employee number` int primary key auto_increment, `Employee name` varchar(10), `Department number` int , foreign key(`Department number`) references `Department table`(`Department number`) on update cascade on delete cascade);
Query OK, 0 rows affected (0.01 sec)
mysql> insert into `Employee table 1` values(1,"zhangsan",1);
Query OK, 1 row affected (0.00 sec)

mysql> select * from `Employee table 1`;
+-----------+--------------+-----------+
| Employee number    | Employee name     | Department number    |
+-----------+--------------+-----------+
|         1 | zhangsan     |         1 |
+-----------+--------------+-----------+
1 row in set (0.00 sec)
# The "department number" of the marketing department that modifies the "department table" is adjusted from 1 to 4:
mysql> update `Department table` set `Department number`=4 where `Department name`="market";
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0
# After modification, you can see that the data of department table and employee table are changed at the same time:
mysql> select * from `Department table`;
+-----------+--------------+--------------+
| Department number    | Department name     | Department address     |
+-----------+--------------+--------------+
|         2 | personnel matters         | Shanghai         |
|         3 | engineering         | Nanjing         |
|         4 | market         | Beijing         |
+-----------+--------------+--------------+
3 rows in set (0.00 sec)

mysql> select * from `Employee table 1`;
+-----------+--------------+-----------+
| Employee number    | Employee name     | Department number    |
+-----------+--------------+-----------+
|         1 | zhangsan     |         4 |
+-----------+--------------+-----------+
1 row in set (0.00 sec)

MySQL multi table query

Classification of multi table query:

  • Merge query Union, union all
    • Merge the result set to merge the query results of two select statements together (equivalent to Union);
    • The number of columns and the order of columns should be consistent between the two merged results;
  • Connection query:
    • inner join / join
      • inner join is a comparison operator that returns only qualified rows;
    • External connection:
      • Left outer join: left join (represents that the query result is: all rows in the left table, and NULL is returned if there is no data in the right table);
      • Right outer join: right join is opposite to left join;

MySQL Architecture

  • Client :
    • Common toolset for connecting to MySQL server
  • Server :
    • MySQL instance is a MySQL server process that really provides data storage and data processing functions
  • mysqld:
    • MySQL server daemon, running in the background. It manages client requests. mysqld is a multi-threaded process that allows multiple sessions to connect, port listening for connections, and manage MySQL instances
  • MySQL memory allocation:
    • MySQL requires dynamic memory space, such as innodb_buffer_pool_size (from 5.7.5), key_buffer_size. Each session has a unique execution plan. We can only share data sets in the same session domain.
  • SESSION:
    • Assign a session to each client connection, dynamically allocate and recycle. For query processing, each session has a buffer at the same time. Each session is executed as a thread.
  • Parser:
    • Detect SQL statement syntax and generate SQL for each SQL statement_ ID, user authentication also occurs at this stage.
  • Optimizer:
    • Create an efficient execution plan (according to the specific storage engine). It will rewrite the query statements. For example, InnoDB has a shared buffer, so the optimizer will first extract from the pre cached data. Using table statistics optimizer will generate an execution plan for SQL queries. User permission checking also occurs at this stage.
  • Metadata cache:
    • Cache object meta information and statistics
  • Query cache:
    • Share identical query statements in memory. If the same query hits the cache, the MySQL server will retrieve the results directly from the cache. The cache is shared between sessions, so the result set generated for one customer can also be used by another customer. Query cache based on SQL_ID. Writing a SELECT statement to a view is the best example of query caching.
  • key cache:
    • Cache table index. MySQL keys is an index. If the amount of index data is small, it will cache the index structure and leaf nodes (store index data). If the index is large, it will only cache the index structure, which is usually used by MyISAM storage engine

MySQL storage engine

MyISAM engine:

MyISAM storage engine features:

  • Transaction not supported
  • Table level locking
  • Reading and writing are mutually blocked. Writing cannot be read and writing cannot be written during reading
  • Cache indexes only
  • Foreign key constraints are not supported
  • Clustered indexes are not supported
  • Read data faster and occupy less resources
  • MVCC (multi version concurrency control mechanism) high concurrency is not supported
  • Poor crash recovery
  • MySQL5. Default database engine before 5.5

MyISAM storage engine applicable scenarios:

  • Read only (or write less)
  • The table is small (it can be repaired for a long time)

MyISAM engine file:

  • tbl_name.frm table format definition
  • tbl_name.MYD data file
  • tbl_name.MYI index file

InnoDB engine:

InnoDB engine features:

  • Row level lock
  • Support transactions, suitable for handling a large number of short-term transactions
  • Read / write blocking is related to the transaction isolation level
  • Cacheable data and indexes
  • Support clustered index
  • Better crash recovery
  • Support MVCC high concurrency
  • From mysql5 Full text indexing is supported after 5
  • From mysql5 5.5 start as the default database engine

InnoDB database file:

  • The data and indexes of all InnoDB tables are placed in the same tablespace
    • Data files: ibdata1, ibdata2, stored in the directory defined by datadir
    • Table format definition: tb_name.frm is stored in the directory corresponding to each database defined by datadir
  • Each form uses a unique table space to store the data and indexes of the table
    • The two types of files are placed in a separate directory corresponding to each database
    • Data files (storing data and indexes): tb_name.ibd
    • Table format definition: tb_name.frm

Other storage engines:

  • Performance_Schema:
    • Performance_Schema database usage
  • Memory :
    • Store all data in RAM for quick access in environments that need to quickly find references and other similar data. Suitable for storing temporary data. The engine was formerly known as the HEAP engine
  • MRG_MyISAM:
    • Enables MySQL DBA s or developers to logically group a series of the same MyISAM tables and reference them as an object. It is applicable to VLDB(Very Large Data Base) environment, such as data warehouse
  • Archive :
    • In order to store and retrieve a large number of archive or security audit information with little reference, only SELECT and INSERT operations are supported; Row level locks and private caches are supported
  • Federated Federation:
    • An agent used to access other remote MySQL servers. It creates a client connection to the remote MySQL server, transmits queries to the remote server for execution, and then completes data access. It provides the ability to link individual MySQL servers to create a logical database from multiple physical servers. Ideal for distributed or data mart environments
  • BDB:
    • It can replace the transaction engine of InnoDB and support COMMIT, ROLLBACK and other transaction features
  • Cluster/NDB:
    • MySQL's clustered database engine is especially suitable for applications with high-performance search requirements. Such search requirements also require the highest normal working time and availability
  • CSV:
    • The CSV storage engine stores data in a text file using a comma separated value format. You can use the CSV engine to import and export data exchange between other software and applications in CSV format
  • BLACKHOLE :
    -The black hole storage engine accepts but does not store data, and the retrieval always returns an empty set. This function can be used for distributed database design, data automatic replication, but not local storage
  • example:
    • "stub" engine, it does nothing. You can use this engine to create tables, but you cannot store or retrieve data from them. The purpose is to illustrate how to start writing a new storage engine as an example

Manage MySQL storage engine

View the storage engines supported by mysql

# View the storage engines supported by mysql
mysql> show engines;
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| Engine             | Support | Comment                                                        | Transactions | XA   | Savepoints |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| InnoDB             | DEFAULT | Supports transactions, row-level locking, and foreign keys     | YES          | YES  | YES        |
| MRG_MYISAM         | YES     | Collection of identical MyISAM tables                          | NO           | NO   | NO         |
| MEMORY             | YES     | Hash based, stored in memory, useful for temporary tables      | NO           | NO   | NO         |
| BLACKHOLE          | YES     | /dev/null storage engine (anything you write to it disappears) | NO           | NO   | NO         |
| MyISAM             | YES     | MyISAM storage engine                                          | NO           | NO   | NO         |
| CSV                | YES     | CSV storage engine                                             | NO           | NO   | NO         |
| ARCHIVE            | YES     | Archive storage engine                                         | NO           | NO   | NO         |
| PERFORMANCE_SCHEMA | YES     | Performance Schema                                             | NO           | NO   | NO         |
| FEDERATED          | NO      | Federated MySQL storage engine                                 | NULL         | NULL | NULL       |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
9 rows in set (0.00 sec)

View the current default storage engine

# View the current default storage engine
mysql> show variables like '%storage_engine%';
+----------------------------------+--------+
| Variable_name                    | Value  |
+----------------------------------+--------+
| default_storage_engine           | InnoDB |
| default_tmp_storage_engine       | InnoDB |
| disabled_storage_engines         |        |
| internal_tmp_disk_storage_engine | InnoDB |
+----------------------------------+--------+
4 rows in set (0.01 sec)

Set default storage engine

# Set the default storage engine:

[root@localhost ~]# vim /etc/my.cnf
  1 # For advice on how to change settings please see
  2 # http://dev.mysql.com/doc/refman/5.7/en/server-configuration-defaults.html
  3 
  4 [mysqld]
  5 #

 33 # Set default storage engine
 34 default_storage_engine= InnoDB
# Restart the MySQL service after modifying the configuration file 
[root@localhost ~]# systemctl restart mysqld

View the storage engine used by all tables in the database

# View the storage engine used by all tables in the database
mysql> show table status from test01;
+------------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-----------------+----------+----------------+---------+
| Name       | Engine | Version | Row_format | Rows | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Create_time         | Update_time | Check_time | Collation       | Checksum | Create_options | Comment |
+------------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-----------------+----------+----------------+---------+
| Employee table 1    | InnoDB |      10 | Dynamic    |    1 |          16384 |       16384 |               0 |        16384 |         0 |              2 | 2021-08-10 23:29:12 | NULL        | NULL       | utf8_general_ci |     NULL |                |         |
| Department table     | InnoDB |      10 | Dynamic    |    3 |           5461 |       16384 |               0 |            0 |         0 |              5 | 2021-08-10 22:51:04 | NULL        | NULL       | utf8_general_ci |     NULL |                |         |
+------------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-----------------+----------+----------------+---------+
2 rows in set (0.05 sec)

View the storage engine for the specified table in the library

# View the storage engine for the specified table in the library
mysql> show create table `Department table`;
+-----------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table     | Create Table                                                                                                                                                                                                                                      |
+-----------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Department table    | CREATE TABLE `Department table` (
  `Department number` int(11) NOT NULL AUTO_INCREMENT,
  `Department name` varchar(10) DEFAULT NULL,
  `Department address` varchar(10) DEFAULT NULL,
  PRIMARY KEY (`Department number`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8                  |
+-----------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

Set the storage engine for the table:

# Set the storage engine for the table:
mysql> create table test01 (
    -> id int primary key auto_increment,
    -> name varchar(10))engine=myisam default charset=utf8;
Query OK, 0 rows affected (0.00 sec)

mysql> show create table test01;
+--------+--------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table  | Create Table                                                                                                                                                 |
+--------+--------------------------------------------------------------------------------------------------------------------------------------------------------------+
| test01 | CREATE TABLE `test01` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(10) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 |
+--------+--------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

System database in MySQL

The core database of mysql is similar to the master database in Sql Server. It is mainly responsible for storing the control and management information that mysql needs to use, such as database users, permission settings, keywords, etc

  • performance_schema database:
    • The newly added databases in MySQL 5.5 are mainly used to collect database server performance parameters. The storage engines of the database tables are PERFORMANCE_SCHEMA, user cannot create storage engine as PERFORMANCE_SCHEMA table
  • information_schema database:
    • A virtual database generated after MySQL 5.0 does not have information physically_ Schema database is similar to "data dictionary", which provides a way to access database metadata, that is, the data of data. For example, database name or table name, column type and access permission (more detailed access method)
  • sys database:
    • MySQL5. For the newly added database after 7, all data sources in the database come from performance_schema. The goal is to put performance_schema reduces the complexity, so that DBAs can better read the contents of the library. Let DBAs know the operation of DB faster

MySQL lock mechanism

Reference document: MySQL locking mechanism and locking principle https://blog.csdn.net/qq_38238296/article/details/88362999

A lock is a mechanism by which a computer coordinates multiple processes or threads to access a resource concurrently.

  • In the database, In addition to traditional computing resources In addition to the contention (such as CPU, RAM, I/O, etc.), data is also a resource shared by many users. How to ensure the consistency and effectiveness of data concurrent access is a problem that all databases must solve, and lock conflict is also an important factor affecting the performance of database concurrent access. From this point of view, locks are particularly important and more complex for databases Miscellaneous.
  • Mysql uses many such locking mechanisms, such as row lock, table lock, read lock and write lock, which are locked before operation. These locks are collectively referred to as pessimistic locks.

MySQL lock overview

Compared with other databases, MySQL's locking mechanism is relatively simple. Its most remarkable feature is that different storage engines support different locking mechanisms. For example, MyISAM and MEMORY storage engines adopt table level locking; BDB storage engine adopts page level locking, but also supports table level locking; InnoDB storage engine supports both row level locking and table level locking, but row level locking is adopted by default.

  • Table lock:
    • Low cost and fast locking; No deadlock; The locking granularity is large, the probability of lock conflict is the highest, and the concurrency is the lowest.
  • Row level lock:
    • High overhead and slow locking; Deadlock will occur; The locking granularity is the smallest, the probability of lock conflict is the lowest, and the concurrency is the highest.
  • Page lock:
    • The overhead and locking time are bounded between table lock and row lock; Deadlock will occur; The locking granularity is between table lock and row lock, and the concurrency is general

Only from the perspective of locking: table level locking is more suitable for applications that mainly focus on query and only update data according to index conditions, such as Web applications; Row level locks are more suitable for applications with a large number of concurrent updates of a small amount of different data according to index conditions and concurrent queries, such as some online transaction processing (OLTP) systems.

  • Read lock:
    • Shared locks, also known as s locks, are read-only and not writable (including the current transaction), and multiple reads are not blocked from each other
  • Write lock:
    • Exclusive locks and exclusive locks are also called X locks. Write locks block the reading and writing of other transactions (excluding the current transaction)

Deadlock:

  • Deadlock: refers to the execution of two or more processes,
    A phenomenon of waiting for each other caused by competition for resources. If there is no external force, they will not be able to move forward
  • At this time, the system is said to be in a deadlock state or the system has a deadlock. These processes that are always waiting for each other are called deadlock processes
  • Table level locks do not deadlock Therefore, deadlock resolution is mainly aimed at the most commonly used InnoDB

The key to deadlock is that the locking order of two (or more) sessions is inconsistent.

The key to solving the deadlock problem is to lock different session s in order

Common deadlock cases

Demand: divide the investment money into several parts and distribute them to the borrower at random.
At first, the idea of business procedure was as follows:

  • After investment, the investor randomly divides the amount into several copies, then randomly selects several from the borrower's table, and then updates the balance in the borrower's table through select for update one by one.

Abstracted from a session, there are several statements through the for loop:

  • Select * from xxx where id = "random ID" for update

At this time, if two users invest at the same time, the amount of user A is randomly divided into two parts and distributed to the borrower 1 and 2
B. the user amount is randomly divided into two parts, which are distributed to borrowers 2 and 1. Because the locking order is different, of course, the deadlock will appear soon.

Prepare data

mysql> create table test02 ( id int primary key auto_increment, name varchar(10))engine=innodb default charset=utf8;
Query OK, 0 rows affected (0.00 sec)
mysql> insert into test02 values(1,"zhangsan"),(2,"lisi"),(3,"wangwu");
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> select * from test02;
+----+----------+
| id | name     |
+----+----------+
|  1 | zhangsan |
|  2 | lisi     |
|  3 | wangwu   |
+----+----------+
3 rows in set (0.00 sec)

Test lock

# Add read lock for test02 table
mysql> lock table test02 read;
Query OK, 0 rows affected (0.00 sec)
# Test the read lock, and you can query normally
mysql> select * from test02;
+----+----------+
| id | name     |
+----+----------+
|  1 | zhangsan |
|  2 | lisi     |
|  3 | wangwu   |
+----+----------+
3 rows in set (0.00 sec)
# Test read lock, unable to insert new data
mysql> insert into test02 values(4,"zhangwuji");
ERROR 1099 (HY000): Table 'test02' was locked with a READ lock and can't be updated

# Test write lock:
mysql> lock table test02 write;
Query OK, 0 rows affected (0.00 sec)
# Open a new terminal to connect to MySQL and query the test02 table. At this time, the query cannot be normal, and the terminal is stuck
mysql> use test01;
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
mysql> select * from test02;
# Through the "show processlist;" command, you can see that the current status of the query with ID 4 is "Waiting for table metadata lock | select * from test02"“
mysql> show processlist;
+----+------+-----------+--------+---------+------+---------------------------------+----------------------+
| Id | User | Host      | db     | Command | Time | State                           | Info                 |
+----+------+-----------+--------+---------+------+---------------------------------+----------------------+
|  3 | root | localhost | test01 | Query   |    0 | starting                        | show processlist     |
|  4 | root | localhost | test01 | Query   |  155 | Waiting for table metadata lock | select * from test02 |
+----+------+-----------+--------+---------+------+---------------------------------+----------------------+
2 rows in set (0.00 sec)
# Pass "kill 4"; "The command killed the process with ID 4
mysql> kill 4;
Query OK, 0 rows affected (0.00 sec)
# Unlock:
mysql> unlock tables;
Query OK, 0 rows affected (0.00 sec)

Hot Topics

©2025 Programmer Think