mysql7-Performance Optimization

First, build table 1, ID: Each table to set a meaningless ID, preferably an int (unsigned), much better than varchar performance, set to self-increment better. Exceptions to foreign key conditions. 2. Field type (1) try not to use NULL, especially as a comparison column (where/distinct/...). However, the main requirement is that the column is sometimes set to not null, and when using ORM for insertion, you must ensure that each field is not NULL, otherwise it will be an exception; it is useless to specify a default value in the database. (2) The simpler and smaller the data type, the better. If possible, use Integer, enum, date type instead of string (enum internal implementation is tinyint), use fixed-length string instead of variable-length string. (3) IP addresses use unsigned int instead of strings, unsigned int is small, fixed-length, and easy to compare, using Inet_aton () and Inet_ntoa () for easy conversion. (4) Fixed-length table: If there are no varchar, blob, and text fields in the table, the table is fixed-length and the operation will be quick.

3, use index: See "Key and Index", note the following. (1) Not only can optimize where, but also can optimize distinct, join on, order by, group by and so on (2) Index field type should meet the above requirements. (3) Btree index left match, hash index integer value match and only equivalent comparison (=/in, etc.). (4) The index is not the more the better, too many indexes will lead to insert/update inefficient. 4, using the partition/Sub-table, see "Partition and sub-table" 5, Vertical segmentation: Divide the table into several columns according to the column. (1) For example, there are many fields in a table, some of which are relatively independent and not used, such as home address information in users, and a single table can be listed. (2) For example, the user table has a last_login field is often modified, the other few changes, you can last_login out, so that the query cache is frequently cleaned. (3) For example, a table can be varchar/blob/text to a fixed-length table by splitting the infrequently used ones. (4) However, if the table and the original table to be a frequent join operation, then do not split, not worth the candle. 6, reasonable selection of storage engine, the most commonly used is the following two InnoDB: Support transactions, using row locks, update and other statements faster than MyISAM; the query is relatively slow; full-text search is not supported MyISAM: transactions are not supported, table locks are used, update statements are slow; Application for a large number of queries; Support full-text search second, operation data (especially query) 1, avoid select *, explicitly specify the field. 2, for the index field, try not to use the calculation expression, because the index will be full table scan. SELECT COUNT(*) FROM ta_info_cad_andr WHERE ctime > ‘2016-10-01‘ AND ctime < ‘2016-10-31‘;#7s SELECT COUNT(*) FROM ta_info_cad_andr WHERE DAY(ctime) = ‘10‘;#34s3. For indexed fields, try not to use! = or <>, MySQL will scan the index table for full table. 4, if you know the number of query results, or only need to return a specified number, use limit, so that when the server finds the specified number, it returns immediately. The most typical example is the limit 1. 5, when using join, in addition to the above two tables in the Join field to be indexed, but also to ensure that two fields of the same type, if it is a string, also ensure that the string character set is the same. 6, make good use of the query cache: Most of the MySQL server has turned on the query cache, but when the query statement in the Curdate (), now (), such as the return variable variable function, the server does not open the query cache, you can use variables instead. However, it should be noted that the use of parameters may affect the use of indexes, which can affect query speed. SELECT * FROM ta_project WHERE DATE(create_date) = DATE(NOW()) //优化 SET @today = ‘2016-12-14‘; SELECT * FROM ta_project WHERE DATE(create_date) = @today;7. Return a random line: Do not use ORDER by rand (), even with limit 1, the server executes many times rand (), but also records and sorts. select username from user order by rand() limit 1; //优化 select count(*) into @count from user; set @rand = rand(0,@count - 1); select username from user limit @rand,1;8, WHERE clause, if the use of parameters on the index value, will affect the query speed, the specific reason is unclear; with explain analysis, it is found that both methods use indexes, and rows are the same. SELECT COUNT(*) FROM ta_info_cad_andr WHERE ctime > ‘2016-10-01‘ AND ctime < ‘2016-10-31‘;#7s SET @startTime = ‘2016-10-01‘; SET @endTime = ‘2016-10-31‘; SELECT COUNT(*) FROM ta_info_cad_andr WHERE ctime > @startTime AND ctime < @endTime;#15sOnline use of forced indexing can improve the speed of the query, after testing is not. SELECT COUNT(*) FROM ta_info_cad_andr FORCE INDEX(ctime) WHERE ctime > @startTime AND ctime < @endTime;#15s9, split the large delete/update statement: Because these two operations will lock the table, may cause other threads to be inaccessible, even the MySQL server crashes. 10, query partition data, if the data are in a partition, you can display the partition statement plus. As shown below, statement 1 is time consuming 7s and statement 2 takes 25s. SELECT SQL_NO_CACHE COUNT(DISTINCT deviceid) FROM ta_info_cad_andr_test2 PARTITION(p201610); SELECT SQL_NO_CACHE COUNT(DISTINCT deviceid) FROM ta_info_cad_andr_test2 WHERE ctime > ‘2016-10-01 00:00:00‘ AND ctime < ‘2016-11-01 00:00:00‘;Iii. Tools and Techniques 1, explain 2, from procedure analyse () Get advice: These recommendations are more likely to be effective when there are actual data in the table (preferably more), recommendations are for data types, suggestions are recommendations, and decisions are made on their own. SELECT COUNT(DISTINCT deviceid) FROM ta_info_cad_ios WHERE ctime > ‘2016-11-01‘ PROCEDURE analyse(); SELECT DISTINCT deviceid FROM ta_info_cad_ios WHERE ctime > ‘2016-11-01‘ PROCEDURE analyse();The recommendations are as follows: These suggestions are also not very reliable. 3, slow query log 4, regular analyze table and optimize table: Because both will be locked on the table, use with caution. 5, Other: Prepared statements, no buffer query 6, in order to prevent the server cache on the impact of evaluation of the query, query statements can be added Sql_no_query. SELECT SQL_NO_CACHE COUNT(DISTINCT deviceid) FROM ta_info_cad_andr_test2 WHERE ctime > ‘2016-10-01 00:00:00‘ AND ctime < ‘2016-11-01 00:00:00‘; PARTITION(p201610);Reference: http://www.cnblogs.com/daxian2012/articles/2767989.html**************************************************** "Article-operation and maintenance view on MySQL database optimization" It's a good story. What if I asked how fast the database is running? I think the most intuitive and direct way is to view the slow query log, you can use Mysqldumpslow, pt-query-digest or direct view. Io, network, CPU, memory can be the bottleneck that restricts the speed of database, in which IO is a bottleneck.

Query optimization, index optimization, library table structure optimization, should go hand in hand, one does not fall. "These are all at the database usage level; the hardware configuration, the database resource usage configuration is also the optimization direction"

QPS, tps:questions per second and transactions per second
*************************************************************************************************************** View related status/parameters for slow (query) logsShow variables like ' slow% 'Slow_query_log:on/offslow_query_log_file: Slow log file name Slow_launch_time: Note that this time threshold is the threshold for new thread consumption time, not a slow query time thresholdShow variables like '%long_query_time% 'Long_query_time: Time threshold for slow logging, per second; Note that this time threshold is the threshold for query process consumption time, the default is 10s, generally 2s, but for Web applications, 2s is too long, you can consider 1s. The 5.6 version is a threshold precision that can be set to the MS level. Note that if there are only the following lines in the log, there is no slow query "The following line is a table header for a slow query, and is incremented once per restart of the server (verified)" C:\Program Files\MySQL\MySQL Server 5.6\bin\mysqld.exe, Version: 5.6.26-log (MySQL Community Server (GPL)). started with: TCP Port: 3306, Named Pipe: (null) Time Id Command Argument"Slow logging does provide a lot of useful information, but it doesn't necessarily mean that the queries that appear are always slow." If the same query appears in the slow log more than once, it does need to optimize, but if only occasionally one or two times, there may be other objective reasons, such as some locks, I/O disk physical problems, network problems and so on "" to view slow log mainly two ways: 1, directly open File View 2, Using Mysqldumpslow, this is a script for MySQL, the suffix is. pl, you need to install PL to invoke "*************************************************************** What did MySQL do when it sent a request to MySQL? 1, the client sends a query to server 2, the server checks the query cache first, if the cache is hit, immediately returns the results stored in the cache; otherwise go to the next Stage 3, the server generates the execution plan, this process mainly includes parsing SQL, preprocessing and optimizing SQL execution Plan 4, MySQL calls the storage engine's API to execute query 5 according to the optimizer-generated execution plan, returns the result to the client show (full) Processlist: View the status of the connection/thread can display the domain name of the database server, the database name , user name and other basic information, and the following are the most important information time: From start execution to now how many times, the unit is sinfo: execution information, general representative of what command executed command: Current statusSleep: The thread is waiting for the client to send a new request query: The thread is executing the query or is sending the result to the client locked: at the MySQL server layer, the thread is waiting for the table lock. Locks implemented at the storage engine level, such as InnoDB row locks, are not reflected in the thread state. Analyzing and Statistics: threads are collecting statistics about the storage engine and generating execution plans for the query. Coping to TMP table [on disk]: The thread is performing a query operation and copies the result set to a temporary table, which is typically either a group by operation, a file sort operation, or a union operation. If you have on disk, you are placing a temporary memory table on disk. Sorting result: The thread is sorting the results set sending data: Indicates that a thread may pass data (?) between multiple states, or generate a result set, or return data to the client ************************** Explain The order of execution of the ID:SQL statement is not important in the detail of select: Table name Possible_keys: Which index key may be used: Which indexes are used Key_ Len: Index length used ref: shows which column or constant is used together with key to select rows from a table row: The number of rows executing a query is the main indicator for viewing performance extra: the details of the query, more than one, and can appear more than a single, such as using index: by index can be found, No need to scan data rows select_type:simple: Simple query, no union and subquery primary: The outer query of the subquery, or the first query in union union:union the second or subsequent query in a query dependent Union:union Query The result of the second or subsequent query to the Union result:union query ... type from best to worst: Top 5 is the preferred way system: Only one row, is a special case of const Const: (1) primary KEY or Unique key (2) Full query, that is, not just the leftmost (3) =, cannot make <> (4) can only be a table Eq_ref:const (4) unsatisfied, i.e. two tables primary key/unique key equal use ref when Ref:const (1) or (2) is not satisfied, because neither the normal key nor the leftmost search can guarantee the unique results of the query . ref_or_null: You can search for rows that contain null valuesIndex_mergeunique_subqueryindex_subqueryrange: When there is an index (B-tree can, hash index not), use <>between, in (also to pay special attention), range. If there is no index, it is all. Index: Scans the full table-index table, such as using the leftmost data in the index. All: Scan all tables-the original table, if no index is used, or if the condition is not = when using a hash index (not verified). Two points to note: (1) should be aware of the type when querying, if a table ID is a normal index, the type is varchar, if you use id= ' 1000 ', the type is ref; If you use id=1000, the type is all; remember! (2) For more frequent queries of the column, can be used primary/unique The normal index, can be set to NOT NULL, do not allow null (because allow NULL for the performance of the index has a large impact).  ********************************************************************************************************* Long connections and short connections: What is a long connection? In fact, a long connection is relative to the usual short connection, that is, long time to maintain the client and the server connection state. The usual short connection operation steps are: Connect-"data transfer-" Close the connection, and the long connection is usually: connect-"Data transmission-" Stay connected-"data transmission-" Stay connected-"...-" Close the connection, this requires a long connection in the absence of data communication, the timing of sending packets to maintain the connection state, Short connections can be closed directly when there is no data transfer   when to use long connections, short connections? Long connections are primarily used for frequent communication between a small number of clients and the server, because at this time the socket error occurs frequently with short connections, and the frequent creation of socket connections is a waste of resources. However, for the service side, long connections also consume a certain amount of resources, requiring specialized threads (which can be managed under Unix) to maintain the connection state. In summary, the choice of long connections and short connections is subject to availability.   How do I set up long connections and short connections? It is not clear that the connections in the  hibernate connection pool are long connections. ***************************************************************************************************"MySQL must know" some suggestions for improving performance hardware 1, attention to hardware requirements; In general, critical production DBMS should run on dedicated servers. 2. mysql is pre-set with a set of default settings; After a while, you may need to adjust memory allocations, buffer sizes, and so on. Use show variables and show status to view.  3, MySQL is multi-user multi-threaded, often perform multiple tasks, if one task is slow, it will cause all tasks to perform slowly. If you experience significant performance degradation, you can use show processlist to display all active processes and end a process with the kill command.  4, there is always more than one way to write the same SELECT statement, you should test the junction, and sub-query, and so on to find the best method. 5. Use the explain statement to see how to execute a SELECT statement. 6. In general, the stored procedure is faster than executing the MySQL statement in one of the strips. 7. The correct data type should always be used. 8. Never retrieve more data than you need, use SELECT * carefully. 9. Some operations (including insert) support the Delayed keyword, and if it is used, the control is immediately returned to the calling program, and the operation is actually performed once it is possible. 10. When importing data, auto-commit should be turned off. You may also want to delete the indexes (including the Fulltext index) and then rebuild them after the import is complete. 11. Using an index can improve the performance of the search. At the same time, the performance of insertions, deletions, and updates is compromised, and if you have some tables that collect data and do not search frequently, we recommend that you do not use indexes. 12, if there are a series of complex or conditions in the SELECT statement, it is recommended to use multiple SELECT statements and the union statement that joins them, which can greatly improve performance. 13, like very slow, generally best use fulltext rather than like. &NBSP;14, the database is a constantly changing entity; Due to the use of tables and changes in content, ideal optimizations and configurations will change. 15. The most important rule is that each rule will be defeated under certain conditions.  ********************************************************************************************************* Practice experience 1, the disadvantage of joint primary key sometimes in a table A, two fields C1 and C2, you can uniquely determine a record; In principle, you can use C1 and C2 as a joint primary key, although less redundant than using a separate primary key,However, you may have the following problems: If Table A is referenced in other tables, then it is necessary to use both C1 and C2 as foreign keys, not only redundant, cumbersome, but also more efficient if indexed.

mysql7-Performance Optimization