MySQL performance optimization (1), MySQL performance optimization (

MySQL performance optimization (1), MySQL performance optimization (

<Pre name = "code" class = "SQL"> Performance Optimization involves six points: 1. basic MySQL operations 2. common SQL skills 3. SQL statement optimization 4. mySQL database optimization 5. myisam Table lock 6. mySQL Server optimization will be completed later. mySQL basic operation 1: MySQL basic operation 1: MySQL table copy Copy table structure + copy table data create table t3 like t1; -- create a table the same as t1, use like (the same table structure) insert into t3 select * from t1; -- take all the data in t1. Note that only select * is performed when the table structure is consistent *, otherwise, select the corresponding field column to insert the create table t1 (id int unsigned not null auto_increment primary key, name varchar (30); 2: MySQL index (create not If you can create a primary key index, you must use alter. We recommend that you use alter to create all indexes. * if you set a primary key, the default value is primary key index 1. alter table is used to create a common index, unique index or primary key index common index: alter table t1 add index in_name (name) -- add index name in_name unique index in table t1: alter table t1 add unique (name) -- no name is given. The default value is the field name alter table t1 add unique un_name (name) primary key index (Auto increment makes sense): alter table t1 add primary key (id) if the primary key index is not auto-incrementing, remember to change it to auto-incrementing: alter table t1 modify id int unsigned not null auto_increment; view: show index from t1; Delete: al Ter table t1 drop index in_name; @ * Delete primary key index separately * 2: alter table table_name drop index index_namealter index: alter table t1 drop index in_name; @ * Delete Primary Key Index *: Note: When deleting a primary key index, if your primary key index is auto-incrementing (for example, id) if you really want to delete the primary key, the auto_increment of the primary key will be removed from alter table t1 modify id int unsigned not null; then the primary key will be deleted: alter table t1 drop primary key; change to auto-increment: alter table t1 modify id int unsigned not null auto_increment; 3: create index (not commonly used in learning, you cannot operate on the primary key index, only Can operate on common and unique indexes) * create a common index: create index in_name on t1 (name); -- add the field in Table t1 as a common index to view the index: show index from t1; delete index: drop index in_name on t1; -- delete the in_name index in Table t1-> the field cannot have duplicate values before the unique index is created. Otherwise, the index cannot be created. * create a unique index: create unique index un_name on t1 (name); 4. Delete the index created by create: drop indexdrop index in_name on t1; 3. MySQL view definition: The view is a virtual table, the content is defined by the query, it is a table created based on the data obtained by the SQL statement for creating a view stored in a table-view * Create a view based on the data obtained from the table: create view v_t1 as select * from T1 where id> 4 and id <11; function: If a record of data in table t1 is deleted, the corresponding data in table v_t1 is also deleted, similar to the Master/Slave (the master table does not exist, but the slave table does not exist). Therefore, the view can also act as an intermediate table. When querying data, you can not check the master t1 to check the visual chart v_t1, when creating the SQL table t_name, if the table t_name is damaged (Deleted), the corresponding view will have an error. You cannot use view: show tables; Delete view: drop view v_t1; view help information :? View; 4: MySQL built-in function string function: selectconcat ("hello", "word"); Link string-> hello worldlcase ("MYSQL ") convert to lowercase ucase ("mysql") to uppercase length ("leyangjun") string length ltrim ("userName") Remove front-end spaces rtrim ("userName ") remove backend space repeat ("linux", count) and repeat count (select repeat ('D', 2); repeat output twice dd) replace (str, search_str, replace_str) in str, replace_str is used to replace search_strsubstring (str, position [length]). Starting from the position of str, take the length string-> substring and substr are the same as sele. Ct substr ("leyangjun",); obtain five strings from the first space (count) to generate count (number) space mathematical function bin (decimal_number) decimal to binary (select bin (120);) ceiling (number2) rounded up (select ceiling (10.10); ---> 11) floor (number2) round-down (select ceiling (10.10); ---> 10) Max (column) Maximum MIN (column) Minimum sqrt (number2) Square rand () returns the date function of the random value within 0-1: curdate (); returns the current date curtime (); returns the current time now (); returns the current date and time unix_timestamp (date) returns the unix timestamp from_unixtime () of date. returns the unix timestamp date value week (date Returns the week year (date) of the year from date to the year. returns the year datediff (expr, expr2) of the date) select datediff ("", ""); 5: MySQL preprocessing Statement 1: Set A preprocessing statement: prepare stmt1 from 'select * from t1 where id>? '; 2: set a variable: set @ = i1; 3: execute stmt1 preprocessing: execute stmt1 using @ I; set @ I = 5 set @ I = 5; execute stmt1 using @ I; Delete preprocessing: drop prepare stmt1; application scenarios: for example, if you are the boss, I want to see 1, 2, 3, 4, 5, 6, and the number of new employees in March * the SQL statement can be pre-processed, in this way, you do not need to request MySQL to directly pass a value (MySQL stores the pre-processed SQL and runs it directly when using the pre-processed SQL statement, you do not need to connect to MySQL every time.) 6: MySQL Transaction Processing (after adding, deleting, modifying, and querying, as long as there is no commit, it can be rolled back) * The myisam engine does not support transactions, innodb (supports foreign keys and transactions) supports modifying the table engine for transactions. Method: alter table t1 engine = innodb 1: transaction operation to check whether automatic commit: select @ autocommit; Disable Automatic commit set Autocommit = 0; delete from t1 where id> 5; data is only deleted temporarily. If commit is used, the delete statement rollback is executed. If no commit is used, the deleted data commit is restored. 2: restore origin: insert into t1 values ("user4"); savepoint p1; insert into t1 values ("user5"); savepoint p2; insert into t1 values ("user6"); savepoint p3; -- three pieces of data have been inserted. If so, do you think user6 is not required, you can find the Restore Point savepoint p2 to rollback to p2; -- restore to P2 user6 without commit; 7: MySQL storage (which can be considered as a custom function) to create a storage: \ d // create procedure p1 () beginset @ I = 0; w Hile @ I <10 doinsert into t2 (name) values (concat ("user", @ I); -- add, delete, modify, and query data here... Set @ I = @ I + 1; end while; end; // execute a storage: \ d; call p1 (); view storage: show procedure status; show create procedure p1 \ G -- view the basic information stored in p1 8: MySQL trigger (automatically executed) * No trigger is required for query!: Select * from t1 union select * from t2; 1: add the trigger \ d // create a trigger named tg1. when data is inserted into the table, insert a data record to create trigger tg1 before insert on t1 for each rowbegininsert into t2 (id) values (new. id); -- new. id. id = 4 directly copy to this end // prepare Table t1 and table t1 to insert multiple data entries to table t1 to view: show triggers; Delete trigger: drop trigger t2; 2: delete a trigger (Note: The values of the two tables must be symmetric when the trigger is deleted, for example, t1:, 3 t2: 11,12, and 13 cannot be deleted and an error is returned, it must be symmetric. For example, table t1 and table t2 both have user1, so that deletion is OK) \ d // create trigger tg2 before delete on t1 for each rowbegin delete from t2 where id = old. id; -- this value is not included in the insert operation. It is new. the value of id is old. idend // 3: Change trigger: create trigger tg3 before update on t1 for each rowbegin update t2 set id = new. id where old. id; -- (update t1 set name = "leyangjun"-new. id where name = "zhangfei"-old. id) end // 9: resets the auto_increment value. How can I restore the Automatically increasing ID of the MySQL database? delete from tableName is not allowed when clearing the table. Instead, truncate tableName is used; in this way, the auto_increment is restored to 1, or the table is directly modified using the alter command after the content is cleared: alter table tableName auto_increment = 1; Scenario: 1: The id fields in table t1 correspond to 1, 2, records of 3, 4, 5, 6, 7, 8, and 9. 2: delete the records after the deletion is enabled, and insert data later. You will find that the data is inserted from 10, 11, 12, 13 ..... start rather than 13: when clearing the file, we can execute this archive as 1: alter table tableName auto_increment = 1; 4: It starts from 1 when it is inserted.

Mysql performance can be optimized. Welcome

The information you provided is basically useless. You did not mention the application type of your database and your operating environment.

The configuration file does not need to be changed. The configuration file will not play a significant role in database optimization due to specific minor adjustments.

The optimization is generally divided into several points:
1. Storage engine selection. High concurrency, update, and insert. innodb is applicable for transaction processing. select is used for myisam. Generally, all tables use the same engine for maintenance.
2. You can select table sharding, database sharding, and Master/Slave load based on the data volume, cpu, memory, and disk I/o overhead.
3. The most important thing is SQL and index. with appropriate indexes, writing efficient SQL statements is the basis of optimization.

Optimization is a debugging process, and there is no general mode. You should make a reasonable choice from the actual situation.

What skills can be used to optimize MySQL database performance?

1. Storage engine selection if the data table requires transaction processing, InnoDB should be considered because it fully complies with the ACID feature. If no transaction is needed, it is wise to use the default storage engine MyISAM. And do not try to use both storage engines. Think about it: in a transaction, some data tables use InnoDB, while others use MyISAM. What about the results? The entire subject will be canceled, and only those in the transaction processing will be taken back to the original state, and the rest of the committed data will be transferred to the memory, which will lead to conflicts throughout the database. However, there is a simple method to take advantage of the advantages of both storage engines. Currently, most MySQL suites include InnoDB, compiler, and linked list. If you choose MyISAM, you can still download InnoDB separately and use it as a plug-in. It's a simple method, isn't it?
2. Counting if the data table uses a storage engine that supports transaction processing (such as InnoDB), you should not use COUNT (*) to calculate the number of rows in the data table. This is because the product database uses COUNT (*) and returns an approximate value at most, because at a specific time, some transactions are always running. If you use COUNT (*), it will obviously produce a bug, and this error result will appear.
3. The most difficult problem of repeatedly testing the query is that no matter how careful the query is, errors will always occur and bugs will occur. On the contrary, the problem is that when a bug occurs in most cases, the application or database is online. There is indeed no practical solution to this issue unless you run the test sample on an application or database. Any database query can only be recognized after a large number of sample tests with thousands of records.
4. Avoid full table scan. Generally, full table scan is used if MySQL (or other relational database models) needs to search for or scan any specific records in the data table. In addition, the simplest method is to use an index table to solve the problem of low efficiency caused by full table scan. However, as we can see in subsequent problems, this has an error section.
5. Use "EXPLAIN" for query. When debugging is required, EXPLAIN is a good command. The following describes EXPLAIN in depth.
Reference: hi.baidu.com/...3.html