MySQL Operation specification Summary

Source: quiet so far √ Tuan

User Rights Management create user command:' username ' @ ' host ' identified by ' password ';
Description: User name created by username
host specifies on which host the user can log on, if localhost is available to the local user, you can use the wildcard% if you want the user to be able to log on from any remote host.
Password The user's login password, the password can be empty, if it is empty, the user can not require a password to log on to the server. For example: CREATE user ' pig ' @ ' 192.168.1.101_ ' idendified by ' 123456 ';
Note: When you create a user, you can also add users to the user table by adding them.
Permission Assignment mysql Privilege principle: permission control is mainly for security reasons, so we need to follow a few experience principles:

1. Grant only the minimum permissions that are needed to prevent users from exceeding their authority. If the user just needs to query, then give only select permission, do not give the user update, insert or delete permission.
2. When creating a user, restrict the user's login host, which is usually restricted to the designated IP or intranet IP segment.
3. Delete the user without the password when initializing the database. Some users are automatically created when the database is installed, and these users do not have a password by default.
4. Set a password that satisfies the complexity of the password for each user.
5. Regular cleanup of unwanted users. Reclaim permissions or delete users.

MySQL permissions at a glance:
How MySQL's permissions are distributed is what permissions are set on the table, what permissions are set on the column, and so on, which can be explained in a table in the official documentation:
MySQL Permissions control1. The grant command instructions: Create a superuser fog that only allows log on locally, and allow permissions to be assigned to other users, with the password: test123
Mysql> Grant all privileges on * * to [e-mail protected] ' localhost ' identified by ' test123 ' with GRANT option;
Description of the GRANT command:
All privileges is a representation of all permissions, and you can also use the Select, Update, and other permissions.
On to specify which libraries and tables the permissions are for.
The * in front of * * is used to specify the database name, followed by the * number to specify the table name.
To indicates that the permission is assigned to a user.
[email protected] ' localhost ' means the fog user, at the end of the limited host, can be IP, IP segment, domain name, and%,% represent anywhere. Note: There are some versions of this is not included locally, previously encountered to a user set the% allowed to log anywhere, but not on the local login, this and version has a relationship with this issue and add a localhost user.
Identified by specifies the user's login password.
With GRANT option This option means that the user can delegate the permissions they own to others.
Note: It is often not specified with the WITH GRANT option when creating an operation user that the user cannot use the grant command to create a user or authorize another user.
2, refresh the permission to use this command to take effect, especially if you have to those permissions table user, DB, host and so on update or delete updates. If you have previously encountered permissions that have not been updated with grant, use the flush privileges command to refresh the permissions whenever you make changes to the permissions.
mysql> flush Privileges;
3. View Permissions view current user's permissions:mysql> show grants;+---------------------------------------------------------------------+| Grants for [email protected] |+------------------------------------------------- --------------------+| GRANT all privileges on * * to ' root ' @ ' localhost ' with GRANT OPTION | | Grant PROXY on "@" to "root" @ ' localhost ' with GRANT OPTION |+---------------------------------------------------- -----------------+
View the permissions of a user:mysql> show grants for ' fog ' @ '% ';
4. Reclaim Permissions mysql> Revoke Delete on * * from ' fog ' @ ' localhost ';
5. Delete User mysql> drop users ' fog ' @ ' localhost '; Query OK, 0 rows affected (0.01 sec)
6. Rename the account mysql> rename user ' fog ' @ '% ' to ' jim '% ';
7. Change the password
1, with Set PASSWORD command mysql> set PASSWORD for ' root ' @ ' localhost ' = PASSWORD (' 123456 ');
2, with Mysqladmin[[email protected] ~]# mysqladmin-uroot-p123456 password 1234ABCD format: mysqladmin-u user name-p old code password new password
3. Edit user table directly with update mysql> use mysqlmysql> update user Set PASSWORD = PASSWORD (' 1234ABCD ') where user = ' root '; MYSQL&G T Flush privileges;
SQL Development Recommendationsabout naming1, the library name, table name, field names must use lowercase letters, and underlined by the split.
2, library name, table name, field name up to 64 characters, try not to exceed 32 characters.
3, library name, table name, field name prohibit the use of MySQL reserved word. When attributes such as library name, table name, field name, and so on contain reserved words, the SQL statement must refer to the property name in inverted quotation marks, which makes it very complex to write SQL statements, escape variables in shell scripts, and so on.
4, try not to use the partition table. Partitioned tables have strict requirements for partitioning keys, and partitioned tables become more difficult to perform DDL, sharding, and single-table restores after the table becomes larger. It is therefore forbidden to use partitioned tables, and it is recommended to manually sharding the business side.
about building a table1. Use the InnoDB storage engine. InnoDB engine is the default after the MySQL5.5 version of the breaking, support transactions, row-level locks, better data recovery capability, better concurrency performance, while the multi-core, large memory, SSD and other hardware support better, support data hot backup, etc., so innodb compared to MyISAM has a clear advantage.
2. We recommend using unsigned to store non-negative values. The same number of bytes, the value of the non-negative storage range is larger. If the tinyint has a symbol of-128-127, unsigned is 0-255.
3, it is recommended to use the int unsigned storage IPV4. The unsinged int storage IP address occupies 4 bytes, and CHAR (15) occupies 15 bytes. In addition, the computer handles integer types faster than string types. Use the int unsigned instead of char (15) to store the IPV4 address and convert it through the MySQL function Inet_ntoa and Inet_aton. The IPV6 address currently has no conversion function and requires a decimal or two bigint to store it. For example: SELECT Inet_aton (' 209.207.224.40 '); 3520061480SELECT Inet_ntoa (3520061480); 209.207.224.40
4. It is strongly recommended to use tinyint instead of enum type. The enum type requires an online DDL when the enumeration value needs to be modified or added, and the cost is greater; The enum column value, if it contains a numeric type, can cause a default value to be confused.
5. Use varbinary to store case-sensitive variable-length strings or binary content. varbinary is case-sensitive by default, with no character set concept and fast speed.
6, the int type is fixed occupies 4 byte storage, for example int (4) only represents the display character width is 4 bits, does not represent the storage length.
Numeric type the number after the parentheses is only the width and not the storage range, for example, int (3) shows 3 bits by default, the space is padded, the normal display is exceeded, Python, Java client and so on do not have this function.
7, distinguish between using datetime and timestamp. The year type is used for storage years. The date type is used for storing dates. Storage time (accurate to seconds) it is recommended to use the timestamp type.
Both datetime and timestamp are accurate to seconds, with preference for timestamp, because timestamp has only 4 bytes, and datetime has 8 bytes. At the same time, timestamp has automatic assignment and auto-Update feature.
Note: In versions 5.5 and earlier, if there are multiple timestamp columns in a table, only one column can have the Automatic Update feature.
8, the large-scale segment, low-frequency fields are split into separate tables to store, separating hot and cold data.
Facilitates efficient use of cache, prevents read-in of useless cold data, and less disk IO, while maintaining thermal data resident memory to improve cache hit ratio.
9. Prohibit storing plaintext passwords in database tables. Use the encrypted string to store the password, and ensure that the password is not decrypted, while using a random string to add salt to ensure password security. Prevent database data by the company's internal personnel or hackers, using a dictionary attack and other means of brute force to crack user passwords.
The table must have a primary key, and it is recommended to use the unsigned self-increment column as the primary key.
The table does not have a primary key, and InnoDB defaults to the hidden primary key columns; tables without a primary key are very difficult to locate data rows and reduce the efficiency of row-based replication.
10, the table character set uses UTF8, if necessary, can apply to use the UTF8MB4 character set.

• The UTF8 character set stores Chinese characters in 3 bytes, and stores English characters in a single byte.
• UTF8 Unified and Universal, there will be no transcoding garbled risk.
• If you encounter a storage requirement for emoji such as emoj, you can request to use the UTF8MB4 character set.

11, the use of appropriate sub-database sub-table strategy. such as the Thousand library ten tables, 10 library hundred meters and so on.
The use of appropriate sub-database sub-table strategy is conducive to rapid business development in the late stages of horizontal split, while the sub-Library can effectively utilize the MySQL multi-threaded replication features.
About Indexes1, prohibit redundant index. Indexes are double-edged swords that increase maintenance burdens and increase IO pressure. (A,b,c), (A, b), which is a redundant index. The prefix index can be used to achieve acceleration and reduce maintenance burden.
2. Prohibit duplicate indexing. Primary key A;uniq index A; Duplicate indexes increase maintenance burdens and disk space, without any benefit.
3. Do not create indexes on low cardinality columns, such as "gender". In most scenarios, an exact lookup of indexes on low cardinality columns has no advantage over non-indexed full-table scans and increases the IO burden.
4, reasonable use of coverage index to reduce IO, avoid sorting. The overlay index can get all the required fields from the index, thus avoiding two lookups of the back table and saving IO. In the InnoDB storage Engine, secondary index (non-primary key index, also known as secondary Index, level two index) does not store the row address directly, but instead stores the primary key value. If a user needs to query a data column that is not contained in secondary index, it needs to be queried two times by secondary index to find the primary key value and then querying to the other data column through the primary key. An overlay index can obtain all the required data in an index, and is therefore more efficient. The primary key query is a natural overwrite index. For example select Email,uid from User_email WHERE uid=xx, if the UID is not a primary key, it is appropriate to add the index as index (UID,EMAIL) for performance gains.
about SQL1. Use in instead of OR. The in SQL statement should contain no more than 1000 values.
In is a range lookup, MySQL internally sorts the list values of in to find, which is more efficient than or.
2. Use UNION ALL instead of union. UNION all does not require any further sorting of the result set.
3, try not to use ORDER by rand (). Order BY Rand () adds a pseudo-column to the table, calculates the rand () value for each row of data with the rand () function, and then sorts it based on that row, which usually generates a temporary table on the disk and is therefore very inefficient. It is recommended that you first use the rand () function to obtain a random primary key value and then obtain the data from the primary key.
4, it is recommended to use reasonable paging method to improve paging efficiency.
5. Select only gets the necessary fields, use SELECT * sparingly.
6. Avoid the function of uncertain results such as now (), Rand (), Sysdate (), Current_User () in SQL.
In a statement-level replication scenario, a function that causes the master-slave data to be inconsistent, and an indeterminate value, produces an SQL statement that cannot take advantage of query CACHE.
7, reduce the number of interactions with the database, try to use bulk SQL statements.
Use the following statement to reduce the number of interactions with DB: a) INSERT ... On DUPLICATE KEY updateb) REPLACE intoc) insert ignored) insert into VALUES ()
8. Split complex SQL into multiple small SQL to avoid large transactions. Simple SQL is easy to use to MySQL's query CACHE; Reduce lock table time especially MyISAM; you can use multicore CPUs.
9. Multiple alter operations on the same table must be combined into a single operation.
10. Use as few stored procedures, triggers, views, custom functions, etc. as possible.
These advanced features have performance issues and many unknown bugs. Putting the business logic into the database makes the database DDL, scale out, sharding, and so on more difficult.
other1. The application account that controls super privileges exists.
2, the submission of online table modification requirements, you must specify all the SQL statements involved (including INSERT, DELETE, UPDATE), easy for DBAs to review and optimize.
The index is not the only SELECT statement that needs to be used. UPDATE, delete all need to navigate to the data before the change can be executed. Therefore it is necessary for the business to provide all the SQL statements to facilitate DBA review.
3. Do not store the business logic in the MySQL database.

A database is a stateful service, and changes are complex and slow, and if you put the business logic into a database, it will limit the rapid growth of your business. It is recommended to advance the business logic to the front-end or intermediate logic layer, and to use the database as the storage layer to realize the separation of logic and storage.

MySQL Operation specification Summary