• MySQL optimization 2 (internal optimizer and explain)


    MySQL performance optimization Introduction of internal optimizer of MySQL MySQL has an optimizer module which is specially responsible for optimizing the select statement. Its main function is to calculate and analyze the statistical information collected in the system, and provide the query requested by the client with the best execution plan in his opinion (he […]

  • Notes on MySQL (11): stored procedures and functions


    This article is updated on June 23, 2019, using MySQL 5.7 and operating system deepin 15.4. catalog SQL statement Creating stored procedures or functions Modifying stored procedures or functions Deleting a stored procedure or function Calling a stored procedure or function Variables, conditions, processing, cursors variable condition handle cursor Process control IF CASE LEAVE ITERATE […]

  • Installing redis and MySQL in CentOS


    1|0MySQL(MariaDB) 1. Explanation MariaDB database management system is a branch of MySQL, which is mainly maintained by the open source community and licensed by GPL. One of the reasons for the development of this branch is: after Oracle acquired mysql, there is a potential risk of closing the source of MySQL, so the community adopts […]

  • Summary of MySQL indexes that will not be used


    The type of index in MySQL Generally, it can be divided into four categories Common index: the most common index Unique index: the value of index column must be unique, but null value is allowed Primary key index: a special unique index that does not allow null values Joint index: the index column has multiple […]

  • Meet SQL (1)


    SQL self study notes Introduction of SQL Drawing demonstration of SQL classification     DDL Operation database 1.0 query and creation       2.0 modify, delete and use   Operation table 1.0 query e 2. Create       3. Delete 4. Modification       DML 1.0 add data   2.0 delete   […]

  • MySQL InnoDB MRR Optimization Guide


    preface MRR is the abbreviation of multi range read. The purpose of MRR is to reduce disk random access and convert random access into more sequential access. For range / ref / EQ_ Query of type Ref. Implementation principle: 1. After searching the secondary index, find out the needed data according to the primary key […]

  • Generating MySQL data dictionary with Navicat


    First, select the database to generate the data dictionary. Here, take the backend library as an example, and click new query. Insert SQL statement: select TABLE_SCHEMA,TABLE_NAME,COLUMN_NAME,COLUMN_TYPE,COLUMN_DEFAULT,COLUMN_COMMENT from information_schema.columns where TABLE_SCHEMA=’backend’; Run SQL to get all the tables and columns in the current database.Click “export result” in the upper right corner to export to excel. mysql […]

  • MySQL learning notes (14): partition


    This article is updated on June 30, 2019, using MySQL 5.7 and operating system deepin 15.4. catalog Partition type Range partition List partition Columns partition Hash partition Key partition Subarea Partition management Partition management of range partition and list partition Partition management of hash partition and key partition Partition type have access toSHOW PLUGINSCheck to […]

  • System error 1067 of MySQL 5.6 decompressed version service unable to start


    Let’s record the bug that was trapped all afternoon today Let’s start half a month ago At that time …………………………………………………………………………………………………………… Then, I unloaded mysql5.5 installed on my local computer Go to the official website under a decompression version of 5.6 Then unzip it Configure the environment variable and configure it to bin. Then add a […]

  • List is not in group by clause and contains nonaggre


    Causes of the problem: MySQL 5.7.5 and above functions rely on the detection function. If only is enabled_ FULL_ GROUP_ By SQL mode (by default), MySQL will reject queries that select lists, having conditions, or order by lists, refer to non collection columns that are neither named nor functionally dependent on them in the group […]

  • MySQL logs suddenly soared


    1. Phenomenon Today, when helping other students to check the problem, we found that the database error log file has more than 9g. Open the content to see as follows: ===================================== 2020-07-08 13:47:43 0x7fe3723ff700 INNODB MONITOR OUTPUT ===================================== Per second averages calculated from the last 1 seconds —————– BACKGROUND THREAD —————– srv_master_thread loops: 28112548 srv_active, […]

  • How to query data with DQL command in MySQL


    This chapter will take you to learn the commonly used data query language in MySQL. DQL (data query language) Select syntax SELECT [ALL | DISTINCT] { * | table.* | [table.field1 [as alias1][,table.field2] [as alias2][,…]]} FROM table_name [as table_alias] [left | out | inner join table_ Name2] # union query [where…] # specify the conditions […]