• Deep analysis of MySQL deadlock


    preface If our business is in a very early stage and the concurrency is relatively low, then we can not encounter a deadlock problem for several years. On the contrary, our business has a very high degree of concurrency, so we must be very upset by the deadlock problems from time to time. However, when […]

  • Explain the correct posture of if function in MySQL


    In order to write today’s content, run the program for nearly seven hours, stored 10 million pieces of data in the database. —— What I want to talk about today is an example of if() function of MySQL database. The specific scenarios are as follows, Let’s look at the table structure CREATE TABLE `message` ( […]

  • MySQL 8.0.18 hash join does not support left / right join connection


    In MySQL 8.0.18, a new function of hash join has been added, which is applicable to the fields that have not been indexed to do equivalent Association query. In previous versions, if the joined fields were not indexed, the query speed would be very slow, and the optimizer would use BNL (block nesting) algorithm. Hash […]

  • Usage analysis of MySQL database show processlist instruction


    In the actual project development, if we have a lot of pressure on the database, such as a large number of SQL queries or inserts, especially in the case of multithreading, we can kill some of the slow SQL, and a common command is show processlist 1. What is show processlist Show processlist: by looking […]

  • Analysis of dynamic SQL statement in mybatis


    This article mainly introduces mybatis in the dynamic SQL statement parsing, the article through the example code introduction is very detailed, to everybody’s study or the work has the certain reference study value, needs the friend may refer to There are two ways to configure SQL in mybatis, one is to use XML to configure, […]

  • Installing MySQL 5.7 in window10


    1. Download and install MySQL 1) Official website: MySQL Community (GPL) Downloads »     2) Select the system MySQL installer for windows   3) Select the download version. 5.7 is downloaded here. Enter archives and click download                     4) Double click to install after […]

  • Analysis of SQL integrity constraint statement in database


    integrity constraint Integrity constraints are for the correctness of table data! If the data is incorrect, it cannot be added to the table in the first place. 1 primary key When a primary key constraint is added to a column, the data in that column cannot be repeated. In this way, the value of the […]

  • MySQL 8.0.18 installation and configuration method graphic tutorial (Linux)


    This paper records the Linux MySQL 8.0.18 installation and configuration tutorial, for your reference, the specific content is as follows 1. Download address of installation package on official website 2. I upload the installation package to the Linux server through xftp 3. Decompression Decompression command:tar -xvf mysql-8.0.18-linux-glibc2.12-x86_64.tar Continue to unzip the one you need Command:tar […]

  • MySQL master-slave replication read-write separation, this is enough!


    Introduction: what is master-slave replication, how to achieve separation of reading and writing, you will understand this article! Mind map This article has been included in my GitHub collection. Welcome to star : preface In many projects, especially Internet projects, the master-slave replication and read-write separation architecture will be adopted when using mysql. Why should we […]

  • Summary of MySQL lock knowledge


    The concept of lock ① Lock, in real life, is a tool that we want to hide from the outside world. ② In a computer, it is a mechanism to coordinate multiple processes or threads to access a resource concurrently. ③ In the database, in addition to the traditional computing resources (CPU, ram, I / […]

  • Troubleshooting MySQL deadlock


    Deadlock query mode: 1) Check whether there is lock information in the current transaction: select trx_id,trx_state,trx_started,trx_requested_lock_id,trx_weight from innodb_trx; 2) View lock information (table lock or row lock, the table of lock) select lock_id,lock_trx_id,lock_mode,lock_type,lock_table,lock_index from innodb_locks; 3) See which transactions the lock is waiting for select * from innodb_lock_waits;   How to avoid deadlock 1. Reduce […]

  • Step by step teaching you MySQL query optimization analysis tutorial


    preface MySQL is a kind of relational database, which has strong query function, high data consistency, high data security, and supports secondary index. However, the performance is slightly worse than that of non relational databases, especially for data above one million level, it is easy to slow down the query. At this time, we need […]