For databases, methods to optimize queries
1. Use indexes
When using indexes, avoid full table scans and first consider creating indexes on the columns involved in where and order BY, group BY.
2. Optimize SQL statements
1) Query statement analysis: Through the analysis of the query statement, the execution of the query statement can be understood, the bottleneck of the query statement execution can be found, so as to optimize the query statement.
Explain (query optimizer) is used to view the results of SQL statements to help select better indexes and optimize queries, and write better optimizations.
For example, explain select * from news;
Select * from t; select * from t; select * from t;
3) Do not perform operations or use functions on index columns.
4) The query uses limit as far as possible to reduce the number of rows returned, reduce data transmission time and bandwidth waste.
3. Optimize database objects
1) Optimize the data type of the table
The table is analyzed using the Procedure analyse() function, which makes recommendations for optimizing the data types of the columns in the table. The first principle of table data types is to use the shortest type that can represent and store data correctly. This reduces the use of disk space, memory, and CPU cache.
Select * from table name procedure analyse();
2) Split the table
Splitting a table can improve the efficiency of table access. There are two ways to split:
A. Vertical split (by functional module)
Divide the tables into functional modules and relationships and deploy them to different libraries. For example, we will establish the definition database workDB, commodity database payDB, user database userDB, etc., respectively used to store the project data definition table, commodity definition table, user data table, etc.
Put the primary key and some columns in one table, and then put the primary key and other columns in a zero table. Vertical splitting can be used if some columns in a table are commonly used and others are not.
B. Split horizontally (divide storage according to rules **) **
When the amount of data in a table is too large, we can divide the table data according to some rules, such as userID hash, and then store it in multiple tables with the same structure and different libraries.
Place rows into two separate tables based on the values of one or more columns of data.
3) Use intermediate tables to improve query speed
Create an intermediate table with the same structure as the source table, transfer the data to the intermediate table, and then perform statistics on the intermediate table to get the desired results.
4. Hardware optimization
1) CPU optimization
Choose a CPU with multiple cores and high frequency.
2) Memory optimization
Use more memory. Allocate as much memory as possible to MySQL for caching.
3) Disk I/O optimization
A. Use a disk array
RAID 0 has no data redundancy and no disk display for data verification. RAID 0 requires at least two hard disks. The two hard disks are combined into one disk and data is continuously divided on each disk.
RAID 1 is a RAID array consisting of two hard disks. The capacity of one hard disk is equal to that of the other hard disk, because the other hard disk is used as data mirroring.
Use a RAID-0+1 disk array. RAID 0+1 is a combination of RAID 0 and RAID 1. It provides the same data security as RAID 1, and the same storage performance as RAID 1.
B. Adjust the disk scheduling algorithm
Selecting a proper disk scheduling algorithm can reduce the disk seek time.
5. Optimize MySQL itself
The optimization of MySQL itself is mainly to optimize and adjust the parameters in its configuration file my.cnf. For example, specify the size of the MySQL query buffer and specify the maximum number of connections allowed by MySQL.
6. Application optimization
1) Use the database connection pool
2) Practical query cache
Its purpose is to store the text of the SELECT query and its corresponding results. If an identical query is subsequently received, the server gets the query results directly from the query cache. The query cache is used for tables that are updated infrequently. When the data in the table changes, the query cache clears the relevant entries.
JDK resource collection
- 【JDK5】 jdk1.5×64-bit Windows version.zip
- Download.csdn.net/download/we…
- 【JDK6】 JDk-6u45-Windows-x64 jdk1.6 64-Bit Windows version
- Download.csdn.net/download/we…
- 【JDK7】 jdk-7u72-windows-i586-32-bit
- Download.csdn.net/download/we…
- JDK – 8 JDK8 】 【 u131 – Linux – x64. Tar. Gz
- Download.csdn.net/download/we…
- JDK – 8 JDK8 】 【 u131 – Linux – x64. Tar. Gz
- Download.csdn.net/download/we…
MySql Database Resources
- Mysql 5.7 64-bit installation package for Windows
- Download.csdn.net/download/we…
- Mysql5.7 64-bit installation package for Linux
- Download.csdn.net/download/we…
Oracle database patch collection
- OPatch patch tool 20.0+
- Download.csdn.net/download/we…
Oracle client tool
- Oracle-instantclient19.6-basic-19.6.0.0.0-1.x86_64 RPM package
- Download.csdn.net/download/we…
- Oracle Client X32-bit Windows version.zip
- Download.csdn.net/download/we…
Oracle Database Collection [Linux+Windows]
- Oracle10g database Windows 32-bit +Linux 32-bit collection
- Download.csdn.net/download/we…
- Oracle database 10Gx 32-bit installation package Linux + Client Client
- Download.csdn.net/download/we…
- Oracle database 11gx 64-bit installation package for Linux
- Download.csdn.net/download/we…
- Oracle database 11Gx64-bit +Windows installation package +Oracle client +Plsql tool
- Download.csdn.net/download/we…
- Oracle 11G 11.2.0.3 client for Windows 64-bit
- Download.csdn.net/download/we…
- Oracle 11g linux64-bit installation package
- Download.csdn.net/download/we…
- Oracle 11g linux64-bit installation package
- Download.csdn.net/download/we…
- Oracle11g x32-bit database installation package for Linux
- Download.csdn.net/download/we…
- Spotlight_for_oracle_rac. 5.0.1.1022. Zip
- Download.csdn.net/download/we…
- Linux_Oracle All RPM packages of the Linux_Oracle client
- Download.csdn.net/download/we…
- Oracle12c client +plsql12
- Download.csdn.net/download/we…