Causes and optimization of excessive offset affecting performance in mysql query
1 problem status
At first, we used the simplest offset+limit paging query method. The pseudo code is as follows:
List<Student> students = new ArrayList<>();
int limit = n;
int offset = 0;
do {
students = SELECT * FROM [table] WHERE status = [status] AND gradeId = [id] LIMIT [limit] OFFSET [offset];
push(pos);
update [table] ...
Added by river001 on Tue, 15 Feb 2022 14:12:03 +0200
Rapid deployment of Centos/MySQL/Python environment
Centos7 installs MySQL 8 database
(yum, the default interpreter is python2.7, don't change Python soft connection)
mkdir /data
mkdir /data/mysql
cd /data/mysql
wget https://dev.mysql.com/get/mysql80-community-release-el7-2.noarch.rpm
yum -y install mysql80-community-release-el7-2.noarch.rpm
yum -y install mysql-community-server
If it is ...
Added by dave007 on Tue, 15 Feb 2022 07:42:16 +0200
mysql database management
catalogue
1, Common data types
2, View database structure
1. Enter database
2. View database information
3. View information about tables in the database
4. Display the structure (field) of the data table
III. overview of SQL statements
1. SQL language
2. SQL classification
III. DDL
1. Create databases and tables
2. Delete the speci ...
Added by rhathid on Mon, 14 Feb 2022 18:40:57 +0200
Mysql database log management, backup and recovery
catalogue
I Log management of MySQL
(1) Log classification of MySQL
(2)MySQL log on
(3) enter the database to check whether the corresponding log is enabled
II MySQL full backup and recovery
(1) Importance of data backup
(2) Causes of data loss
(3) Classification of database backup
(4) Common backup methods
① Physical cold standby
② ...
Added by morpheus.100 on Mon, 14 Feb 2022 10:25:10 +0200
Technology sharing | MySQL parallel DDL
Author: Li PengboAs a member of aikesheng DBA team, he will be transformed and is mainly responsible for MySQL fault handling and SQL audit optimization. Be dedicated to technology and responsible for customers.Source: original contribution*It is produced by aikesheng open source community. The original content cannot be used without authorizat ...
Added by RJP1 on Mon, 14 Feb 2022 09:22:01 +0200
MySQL Learning Notes 2
Functions for MySQL
Common Functions
-- Mathematical operations
SELECT ABS(-8) -- absolute value
SELECT CEILING(9.4) -- ceil
SELECT FLOOR(9.4) -- Rounding Down
SELECT RAND() -- Return 0-1 random number
SELECT SIGN(-10) -- Judging the sign 0 of a number-0 Negative Return-1 Positive number returns 1
-- String function
SELECT CHAR_LENGTH( ...
Added by jaronblake on Sun, 13 Feb 2022 19:08:39 +0200
JDBC and MYSQL database
Load MYSQL database driver
The development tool used by the author is IntelliJ IDEA, and other integrated environments have similar principles.
Download MYSQL database driver
JDBC is java data base connectivity. In order to access the database on the MySQL database server, the application must ensure that the corresponding JDBC-MYSQL driver ...
Added by AbraCadaver on Sun, 13 Feb 2022 14:58:28 +0200
Summary of operation methods in database
Methods in the database:
DDL: operation database and table
1. Operation database: CRUD
1. C(Create):establish
* Create database:
* create database Database name;
* Create a database, judge that it does not exist, and then create:
* create database if not exists Database name;
* Create a database and specify a character set
...
Added by yelvington on Sun, 13 Feb 2022 07:41:54 +0200
Multi table relational design paradigm of MySQL database and its backup and restore
Database design
1, Relationship between multiple tables
1. Classification:
One to one (understanding):
Such as: person and ID cardAnalysis: a person has only one ID card, and one ID card can only correspond to one person One to many (many to one):
For example: departments and employeesAnalysis: a department has multiple employees, and ...
Added by idnoble on Sat, 12 Feb 2022 16:38:55 +0200
MySQL optimization lesson 2: Explain and index practice
Explain use and explanation
Explain tool introduction
Use the EXPLAIN keyword to simulate the database optimizer to execute SQL statements and analyze the performance bottlenecks of your query statements or structures Add the explain keyword before the select statement. MySQL will set a flag on the query. Executing the query will return the i ...
Added by lesmckeown on Sat, 12 Feb 2022 12:42:36 +0200