I. general principles of database O & M
1. What can be done for a database? Do not give it to a database. The database only serves as a data container.
2. Database changes must be recorded and can be rolled back.
Ii. Permission-related
The general principle is to control permissions at the minimum granularity.
Select permission: All developers can have table permissions within their business scope.
Insert, update, and delete permissions: All project managers can have table permissions within their business scope.
Structure permission: this permission is available to the database administrator.
Administration permissions: available to system administrators and database administrators.
ProgramAccess permission: create a user name based on the IP address and system name, and only have the permission to access the required table.
All permissions: the technical director is in charge, and the MySQL table is not owned by anyone except the technical director.
Permission granularity: The developer permissions are collected to the table level, and the database administrator and system administrator permissions are collected to the database level.
Access restriction: Except for DBA and system administrator, it takes 24 hours to operate the database. Other users must bind an IP address.
Password strength: You must set a password based on the target/source/user information to ensure that you do not need to change the password from different sources on different hosts.
Iii. Table Modification
The general principle is to record data with minimal overhead on demand. All changes must be recorded.
- Table Structure Modification Process: The developer submits the SQL statements for table creation, modification, and deletion (which must be annotated) to the DBA, and explains the reasons for table creation, modification, and deletion. the DBA reviews the statements, communicate with developers for corrections. The DBA executes SQL operations and records the changes.
Note: the higher the table structure, the higher the priority)
Create fields as needed. Do not use a type that is larger than the requirement.
Use the unsigned data type whenever possible.
Use a fixed length type whenever possible.
Avoid text type.
Use numbers instead of characters.
You can use Enum to enumerate a few characters.
If the text type is required, the text column must be separately stored in the split table unless otherwise specified.
There are columns that must use function compute to create conditions. Create a new column that stores the function compute structure, index the new column, and use the trigger to automatically update the column.
Record modification time should adopt the timestamp whenever possible. Do not use the time type column and then manually set it.
Separated tables are commonly used and infrequently used data and stored separately.
Historical data should be separated in a timely manner, and tables should be named or stored in the data warehouse according to certain rules.
Similar markup fields should be reasonably designed, so as to avoid the appearance of <> in SQL, as far as possible >/</= to complete the condition search.
For details about how to select a type, see the document on how to select a type.
Iv. Index-related
- The general principle is to create indexes with high-concurrency SQL as the core and reuse the indexes as much as possible.
A separate index is created for this field unless a field is often used as a query field and is not the first column of other composite indexes.
Create a composite index as much as possible, take into account the SQL statements involved, and follow the principle of reusing as much as possible to create an index.
The indexing of highly concurrent SQL statements is preferred. SQL statements that are rarely used or SQL statements that are used only in idle time can not be indexed.
Small tables do not need to be indexed (if the join operation requires an indexed field ).
After the index, the sorting field can be added to the index when the result set is large. If the execution plan is not used, the sorting field is deleted.
Indexes must be created for associated fields except in special cases.
- The index should be adjusted according to the SQL usage in a timely manner.
5. Architecture
- The general principle is to do most operations in the memory as much as possible.
If the memory is sufficient, choose InnoDB (high concurrency data is equivalent to the memory size). If the memory is insufficient, use MyISAM, archive, InnoDB for transaction, and memory for temporary high concurrency data.
The master-slave architecture is suitable for situations where the read speed is significantly higher than the write speed.
The Master-master architecture makes two masters generate different primary keys. For example, one generates an odd primary key and the other generates an even primary key,
InnoDB is used for standalone transactions, and LVS + NDB is used for cluster transactions. NDB cannot be used directly, resulting in extremely low efficiency.
Avoid distributed transactions as much as possible. If necessary, XA transactions, multipart commit, or NDB cluster engine are used.
In principle, the historical data and production data should not be stored in the same database. If the historical data is rarely used, the new database is created to keep the historical data name unchanged, and the historical data can be queried as long as the use changes the database, commonly used historical data uses the table name + date to store the corresponding historical data, and uses the table and date concatenation for Historical query.
When the writing pressure on the master database is large, cascade replication is adopted. When the reading database is under more pressure, one-to-multiple replication is adopted to balance the pressure.
- The dBA and the developer discuss the time for splitting historical data. The time for splitting is confirmed by the DBA and the developer, and is deleted or transferred to the data warehouse.
Vi. SQL Problems
The general principle is to minimize the query result set without unnecessary operations and distribute the pressure to ordinary times.
Do not perform full table query as much as possible. Select the rows required for the fields as needed.
Do not let the Database Scan unnecessary rows.
The selection conditions appear in the same order as possible to maximize the use of indexes.
- Try to split the large result set operation into multiple executions to prevent the database from being locked for a long time.
VII. Workflow
- View the running status through cacti and mysqlreport scripts
Monitor real-time status using mystat script
Analyze the slow query by using the mysqlsla script yesterday, which can be scheduled to run on a daily basis and clear the old slow query.
Optimize the daily slow Query
Check invalid indexes using mysq1_xchx script
To create a table, you need to ask which fields will be used for query and connection, and create an index.
To change a field, you need to check the type and range and set up an appropriate data type.
The table structure must be annotated. Each field must be annotated, except for the ID
To delete data from a database, you must back up the data before deleting it.
At the end of each month, you need to clear historical data. You can use mysqldump-Where to export data within the specified range and insert it into the data warehouse.
- Useless tables and fields should be cleared in time
