Principles of database operation and maintenance

I. GENERAL principles of database operation and maintenance

1, can not give the database to do things do not give the database, the database only to do data containers.

2, for the database changes must have records, you can roll back.

Ii. authority-related

General principles to control permissions at the lowest granularity.

Select permissions: All developers can have table permissions within their own business scope.

Insert/update/delete permissions: All project managers can have table permissions within their own business scope.

Structure permissions: A database administrator can have.

Administration permissions: System administrators and database administrators can have.

Program Access: Establishes a user name based on the IP and system name and only has access to the required table.

All rights: Technical Director in charge, MySQL system table MySQL table permissions in addition to technical director no one shall have.

Permission granularity: Developer permissions granularity to table level, database administrator and system Administrator permissions granularity to library level.

Access restrictions: In addition to the DBA and system administrator need 24 hours to ensure that the database can operate, the other person needs to bind IP.

Password strength: must be based on the target/source/user information to set the password, to ensure that users in different host from different sources of the password is not the same, each time need to focus on replacement password.

Third, the change table related

General principles, record data on demand with minimal overhead, and record all changes.

More Wonderful content: http://www.bianceng.cnhttp://www.bianceng.cn/database/basis/

Table Structure Modification Process: Developer submits the build/change/delete table SQL (must have comments) to the DBA, and describes the reason for the build/change/delete list, DBA review, Exchange corrections with the developer, execute SQL operations by the DBA, and record.

Table structure Changes Note: (Higher precedence)

Create fields on demand without using a type that is much larger than the requirement.

Use unsigned data types as much as possible.

Try to adopt a fixed length type.

Try to avoid text types.

Try to use numbers instead of characters.

You can enumerate a few characters with an enum.

If you must use the text type, if there is no special case, you must split the table to hold the text column separately.

There are columns that must be evaluated by function to make a condition, a new one to store the function calculation structure, an index to the new column, and a trigger to update automatically.

Record modification time as much as possible with time stamp, do not take the time type column and then manually set.

Separate the common and infrequently used data in the table and store it separately.

Historical data to be separated in time, according to a certain rule name table or storage to the Data warehouse.

Similar tag field design should be reasonable, try to avoid appearing in SQL <>, try to use >/</= to complete the condition lookup.

Type selection rules refer to the type you have selected to select the document.

Iv. Index-related

The general principle, with high concurrency SQL as the core index, as far as possible reuse index.

A separate index is established for this field unless a field is frequently used as a query field and does not act as the first column of another combined index.

Build indexes as much as possible, combining the considerations involved with the SQL, and following the principle of reuse.

The index of high concurrency SQL is treated preferentially, and SQL that is rarely used or is used only for idle use can not be indexed.

Small tables can not be indexed (if the participating associations need to be indexed for the associated fields).

The result set after indexing is still large to increase the Sort field index and delete if the execution plan is not used.

The associated field needs to be indexed except for special cases.

Adjust the index in a timely manner based on SQL usage.

V. Architecture-related

The general principle, as much as possible in memory to do most of the operation.

Enough memory to select INNODB (high concurrency data is equivalent to memory size), not enough memory for MyISAM, archiving with archive, transaction-related with InnoDB, temporary high concurrency data with memory.

Master-slave architecture, suitable for clearly read greater than write.

Master-master architecture, which makes two master produce different primary keys, such as an odd primary key, an even-numbered primary key,

Single-machine transaction with INNODB, cluster business with LVS+NDB, must not be directly used NDB, inefficient.

Distributed transactions are avoided as much as possible, if necessary, with XA transactions, sublevel submissions, or directly using the NDB cluster engine.

In principle, historical data and production data should not be placed in the same database, such as the historical data are rarely used, then set up a new library to keep the historical data in the table name unchanged, as long as the use of change database can query historical data, more commonly used historical data using table name + date to store the corresponding Make historical inquiries by means of the date stitching.

The main library to write pressure when the Cascade replication, read the library more pressure when the use of a one-to-many replication, to balance the pressure as the goal.

Historical data splits are split time by the DBA and the developer, each time the DBA and the developer confirms the deletion or transfer to the Data Warehouse.

Six, SQL related

General principle, minimize the query result set, do not do unnecessary operation, disperse the pressure to peacetime.

Try not to do a full table query, as needed to select the required fields.

Conditions as detailed as possible, do not let the database scan unnecessary rows.

The selection criteria appear in the same order as possible, maximizing the use of the index.

Try to split the large result set operation into multiple executions to avoid the database being locked for a long time.

Vii. Work Flow

View running status through cacti and Mysqlreport scripts

Monitoring real-time status with Mystat scripts

Through the Mysqlsla script analysis yesterday slow check, can be scheduled as a daily scheduled tasks, clear old slow check

Tuning Daily Slow Check

Checking for invalid indexes with MYSQLIDXCHX script

Build a table to ask which fields are used for querying and connecting, indexing

Change the field to ask the type and scope, set up the appropriate data type

Changes to the table structure must be commented, each field should have a comment, except for the ID

Deleting data from a database must be backed up before it is deleted.

The end of the month to perform historical data cleanup, through Mysqldump–where to export the specified range of data, insert the Data Warehouse.

Useless tables and fields to clean up in time

Author: 51cto Blog Oracle Little Bastard