Mysql database optimization technology (1)

Mysql optimization is a comprehensive technology, mainly including: a. Table Design rationalization (in line with 3NF); B. Adding an appropriate index (index) [four]: general Index, primary key index, unique index (unique), full-text index; c. Table sharding Technology (horizontal and vertical); d. read/write splitting; e. Stored Procedure (

Mysql optimization is a comprehensive technology, mainly including: a. Table Design rationalization (in line with 3NF); B. Adding an appropriate index (index) [four]: general Index, primary key index, unique index (unique), full-text index; c. Table sharding Technology (horizontal and vertical); d. read/write splitting; e. Stored Procedure (

Mysql optimization is a comprehensive technology, mainly including:

A. Table Design rationalization (in line with 3NF );

B. Add an appropriate index [Four Types]: normal index, primary key index, unique index (unique), and full-text index;

C. Table sharding Technology (horizontal and vertical );

D. read/write splitting;

E. Stored Procedures (modular programming can improve the speed );

F. Optimize mysql configuration: configure the maximum number of concurrent connections (my. ini file: max_connections maximum number of concurrent connections. Generally, the website should be set to around 1000, and the memory will not be able to stand if it is too large) and adjust the cache size;

G. Upgrade of mysql server hardware;

H. regularly clear unwanted data and perform fragment at regular intervals (especially the MyISAM storage engine ).

? Database layer-3 structure: PHP program → dbms (Database Management System, which we usually say is actually this database) → database (that is, file ).

? The PHP program sends SQL statements. After the dbms is compiled, it executes the statements and caches the data returned from the database. Therefore, the second SQL request speed increases. However, using SQL statements for compilation can take a long time. We can encode some frequently used code in the database to form a binary code and then call it directly. This process is a stored procedure.

? Tables that conform to the 3NF (paradigm): The table's paradigm must first comply with 1NF to meet 2NF and further meet 3NF.

? 1NF: indicates that columns in a table are atomic and cannot be decomposed, that is, column information. As long as the database is a relational database (mysql, Oracle, db2, SQL server, informix, sysbase), it automatically meets 1NF.

? 2NF: The table records are unique and meet 2NF requirements. We usually design a primary key. (Primary Key: Generally, it does not include the business logic and is generally auto-incrementing. Because the primary key does not include the business logic, the data is relatively stable)

? 3NF: No redundant data exists in the table. That is to say, if the table information can be derived, a separate field should not be designed for storage. 3NF not met:

? Anti-3NF: (the number of views in the album table is the sum of the views in the photo to table. To increase the response speed, the number of views added to the album table. Although the browsing times of the album can be derived from the photo table, if there are too many images, the query speed of the two tables will be slow. You can solve this problem by designing the field views, therefore, necessary data redundancy is also allowed)

? SQL statement optimization: How to quickly locate slow statements (locate slow queries) in a large project )?

? Common statement: show status like 'uptime' to view how long MySQL is enabled; show [session | global] status like 'com _ select' show status like 'com _ Update' (the default parameter is session, indicating the execution of the current window, global fetch the number of times since mysql was started) to check the number of times the corresponding statement was executed (the storage engine selection is more inclined to refer to which operation is executed ); show status like 'connections' to view the number of times the mysql server is connected; show status like 'low _ queries '(show slow query times ),

? Slow query (by default, mysql considers 10 seconds as a slow query) Optimization: locate slow query (create a large table-> stored procedure; Modify mysql slow query: show slow query value show variables like 'long _ query_time '. set long_query_time = 1)

? Record SQL statements of slow queries to one of our logs (mysql does not record slow queries by default, so you must specify slow queries when mysql starts ). If the slow query log is enabled, this file is stored in my by default. INI file record location, such as: datadir = d:/wamp/bin/mysql/mysql5.6.12/data (this address should not be easily modified)

? The database can have multiple data objects: tables, stored procedures, views, functions, and triggers.

? Dual sub-table, that is, an empty table. Select rand_string (6) from dual;

Optimization Problems:

? The explain statement can be used to analyze how mysql executes SQL statements.

? Create an appropriate index:

? 1. Add an index:

? Primary Key Index addition: When a table sets a column as the primary key, this column is the primary key index. After creating the table, add the index: alter table name add primary key (column name ). Creating an index is costly. It cannot be empty or repeated.

? Why is it faster to create a primary key index: Before an index is created, dbms searches for the index one by one based on the given conditions (such as id = 2. After creating an index, you can use the binary algorithm (or hash algorithm) to create an index file. Binary Tree (BTREE) Efficiency log2N

? Add a common index: Create a table before creating a common index. Create table aaa..., create index name on table (column)

? Add full-text indexes: Full-text indexes are mainly used for Text Retrieval. Full-text indexes are only valid for MyISAM. Currently, they are only valid for English (sphek (coreseek) technology to process Chinese characters ), no index is created for deprecated words.

Create table articles (

Id int unsigned AUTO_INCREMENT not null primary key,

Title VARCHAR (200 ),

Body TEXT,

FULLTEXT (title, body)

) Engine = myisam charset utf8;

? Full-text indexing: Incorrect usage: select * from articles where body like '% mysql %'; (full-text indexing is not used); correct usage: select * from articles where match (title, body) against ('mysql ')

? Add a unique index: When a column in a table is specified as a unique constraint, this column is a unique index (also using the Binary Tree Algorithm ). Unique indexes cannot be repeated, but can be NULL (NULL can have multiple, ''can have only one empty string). After creating a table, add a unique index: create unique index name on table name (column name)

? Composite Index: The index applies to multiple columns. Alter table name add index name (column name 1, column name 2 ...)

? Explain: Understand the execution of SQL statements

? 2. query index: desc table name (the disadvantage of this method is that the index name cannot be displayed), show index (es) from Table Name (\ G), show keys from Table Name (\ G)

? D: \ wamp \ bin \ mysql \ mysql5.6.12 \ data \ you can see that there is a folder in a database. Tables created using InnoDB are composed of three files:

Generally, tables created using the MyASIN engine only have. frm files, and the data is stored in the directory and directory.

. Frm indicates the table structure,. MYD indicates the table data, and MYI indicates the table index. After the index is created, the. MYI file will become larger.

? 3. delete an index: alter table Name drop index name. If you delete a primary key index, you can also: alter table Name drop primary key;

? 4. Modify the index: Generally, the index is deleted first and then re-created.

? Notes for using indexes: disk usage; efficiency impact on dml (update delete insert) Statements, slowing down: Because the index file is updated during addition, deletion, and modification (such as deleting a record, the corresponding binary tree should also delete the corresponding records), even if the index is used in this way, the advantage is greater than the disadvantage, most website queries are more than the addition, deletion, and modification.

? Which columns are suitable for indexing: it must be used frequently in where; the content of this field is not the only value (sex); the content of the field is not frequently changed.

? Notes for using indexes:

? 1. For the created multi-column index, the index is generally used as long as the leftmost column is used for the query condition.

Alter table dept add index myind (dna m, loc) // DNA m is the column on the left, AND loc is the column on the right.

? 2. For queries using like, if '% aaa' (% aaa %, _ aaa) does not use an index, 'aaa %' uses an index (that is, when you like a query, the first character of the keyword is definite, and % or _ cannot be used. If there is a change in the front, the full-text index is considered)

? 3. If there is or in the condition, all the fields used must be indexed (the columns on the right of the composite index are also required). We recommend that you avoid using or

? 4. If the column type is a string, you must reference the value in single quotes in the condition. Otherwise, no index is used. (If the value is a string and no single quotation marks are used, an error is directly reported. If the value is a number, no error is returned if no single quotation marks are used, because it is automatically converted to a string but cannot be indexed)

? 5. mysql estimates that full table scan is faster than indexing, so no index is used.

? View the index usage: show status like 'handler _ read %'

? If you want to import data from one table to another, we recommend that you disable indexes first. Otherwise, indexes will be created (not important) When importing data)

? When querying by group, the default group will be automatically sorted (filesort), which may reduce the speed. Add order by null after group by to prevent sorting.

? In some cases, you can use a connection instead of a subquery. Because join is used, MySQL does not need to create a temporary table in memory.

Simple connection query: select * from dept, emp where dept. deptno = emp. deptno;

Left Outer join: select * from dept left join emp on dept. deptno = emp. deptno;

? How to select the MySQL storage engine:

? Myisam: If the table does not have high transaction requirements and is dominated by queries and additions, we will consider using myisam. For example, the posting and reply tables in bbs.

? InnoDB: it has high transaction requirements and stores important data. InnoDB is recommended. For example, order table and account table.

? Memory: for example, if our data changes frequently, we do not need to store the data in the database, and frequently query and modify the data, consider using it.

Myiasm Data Query and addition is faster than InnoDB, because myisam is directly inserted at the end of the table, while InnoDB needs to perform Transaction Security Validation and sort the data properly.

In PHP development, foreign keys are usually not set, and data consistency is usually ensured in the program.

? If the database storage engine is myisam, it is necessary to regularly fragment (otherwise the deleted data will never be deleted): optimize table tablename