Shulou(Shulou.com)06/02 Report--

This article mainly introduces the database optimization in what content related knowledge, content detailed and easy to understand, simple and fast operation, has a certain reference value, I believe that we read this database optimization in what content articles will be harvested, let's take a look at it.

I. Problem analysis

Examiner is mainly on the assessment of database optimization, general database optimization is divided into performance and application aspects, such as you understand sql optimization; how to optimize millions of data, etc.

II. Explanation of core answers

1. Configure mysql performance optimization parameters according to service level;

2. Enhance mysql performance from the system level, optimize data table structure, field types, field indexes, sub-tables, sub-libraries, read-write separation, etc.

3. Enhance performance from the database level, optimize SQL statements, and use field indexes reasonably.

4. Enhance performance from the code level, use caching and NoSQL database storage, such as MongoDB/Memcached/Redis to relieve the pressure of database queries under high concurrency.

Reduce the number of database operations and try to use database access-driven batch processing methods.

6, infrequently used data migration backup, avoid each time in the mass data to retrieve.

Upgrade the database server hardware configuration, or build a database cluster.

8. Programming means to prevent SQL injection, use JDBC PreparedStatement bitwise insertion or query; regular expression filtering (illegal string filtering);

III. Problem Extension

1. Sql optimization

1) Try to avoid using!= in where clauses Or operator, otherwise the engine will abandon the use of index and full table scan;

2) Try to avoid making null value judgment on fields in where clause, otherwise it will cause the engine to abandon using index and scan the whole table. For example, select id from t where num is null can set default value 0 on num to ensure that num column in the table has no null value, and then query like this:

select id from t where num=0;

3) many times using exists instead of in is a good choice;

4) Replace the HAVING clause with the Where clause because HAVING filters the result set only after retrieving all records;

5) select count(*) from table; such a count without any conditions will cause a full table scan, and there is no business significance, it must be eliminated;

2. Index

1) Index concept, a structure for sorting the values of one or more columns in a database table, using an index to quickly access specific information in a database table. If we think of the contents of a table as a dictionary, the index is equivalent to the dictionary's table of contents.

2) Index type:

Oracle:

Logically: Single column Single column index

Concatenated multirow index

Unique Unique index

NonUnique index

Function-based index

Domain domain index

Physically:

partitioned index

Nonpartitioned index

B-tree ：

Normal B-tree

Rever Key B-tree

Bitmap Bitmap index

MySQL index is divided into ordinary index, unique index, primary key index, composite index and full-text index.

3) When to use the index

① Primary key, unique field;

② The fields connected with other tables need to be indexed;

3. Use>, ≥,=,<, ≤, is null and between in where

fields such as;

(4) Use like without starting with wildcard, where A like 'China%';

5. Fields in aggregation function MIN(), MAX();

order by and group by fields;

4) When the index expires

① Combined index does not use the leftmost prefix, for example, combined index (A, B), where B=b will not use the index;

2 like does not use the leftmost prefix, where A like '% China';

Search one index and do order by on another index, whereA=a order by B, only use the index on A, because the query only uses one index;

4 or will invalidate the index. An index can also be used if the query fields are the same. For example, where A=a1 or A=a2 (effective), where A=a or B =b (invalid)

5 If the column type is string, use quotation marks. For example, whereA='China', otherwise the index is invalid (type conversion will be performed);

④ Operations on index columns, functions (upper(), etc.), or,!= (), not in, etc.;

IV. Use in combination projects

1. index fields that are frequently used but not frequently modified (such as commodity names in commodity tables) to achieve the goal of faster retrieval and higher user experience

2. Using mycat to divide databases and tables

Vertical splitting is based on "columns" in the database. If there are many fields in a table, you can create a new extended table to split the fields that are not often used or have large field lengths into the extended table. For example, in the case of a user table with many fields (for example, a large table has more than 100 fields), it is easier to develop and maintain by "splitting large tables into small tables" and avoiding cross-page problems.

horizontal subscale

Horizontal split is divided into intra-database sub-tables and sub-database sub-tables. According to the inherent logical relationship of the data in the table, the same table is dispersed into multiple databases or multiple tables according to different conditions. Each table only contains a part of data, so that the data volume of a single table becomes smaller and the distributed effect is achieved (such as order table).

The content of this article on "What are the contents of database optimization" is introduced here, thank you for reading! I believe everyone has a certain understanding of the knowledge of "what is in database optimization". If you still want to learn more knowledge, please pay attention to the industry information channel.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.