MySQL performance tuning and architecture design notes

1, MySQL basic introduction MySQL support multi-threaded high concurrency relational database;    Database storage engine InnoDB, MyISAM;    The reason for the rapid rise of MySQL is that he is open source;    Performance has always been a major feature of MySQL pride; 2, MySQL architecture composed of small perfectly formed, MySQL although simple but its internal structure is not simple; MySQL physical files composed of log files: error log errors logs logged here MySQL runtime serious warnings and errors, as well as MySQL startup and shutdown log information binary logs binary log record MySQL runtime all query and query execution time    Save As binary information query log queries logs all query including SELECT statements with large volume turned on performance has a slow query log slow the log information for slow query MySQL physical file consists of data files:    Each database will have a folder named after the database in the defined data directory, which is used to store the data information of each table in a database;    Different data storage engines have different data files; . frm extension file: Regardless of the storage engine, each table will have a file with the table name. frm, which is the definition of the table structure. MyD extension Files: The MyISAM storage engine is dedicated to storing table data, and each table will have a table name. The MyD file. Myi extension Files: The MyISAM storage engine is dedicated to storing table index data, and each table will have a table name. myi files. ibd,. ibdata files: The InnoDB storage engine is dedicated to storing table data and table indexes, except that. ibd stores each table information independently, and each table has a table name. IDB file this is the same as MyISAM,    The Ibdata file is a shared table space to store data information; MySQL Server system architecture Logic module consists of: MySQL can be seen as a two-tier architecture, the first layer is the SQL layer, the MySQL database system before processing the underlying data, all the work is done at this level, including the Authority judgment, SQL parsing, Execution plan optimization, query    Cache and other work, the second layer is the storage engine layer is the data storage implementation of the part, there are a variety of storage engine composition;    Although there are only two layers but each layer has a lot of modules, but also quite complex; The SQL Layer layer contains: (Optimizer optimizer) initialization, core API, network interaction, client &Amp Server interaction Protocol, user module, Access control module, connection management connection thread management, query parsing and conversion, query cache, query optimizer, table change management, table maintenance, System State management, table management, logging, replication, storage engine interface management MySQL comes with Tool Usage Description: MySQL provides a large number of client tool programs MySQL, Mysqladmin, mysqldump ...    3. mysql storage engine MySQL storage Engine Overview: MyISAM storage Engine is the default storage engine of MySQL;    InnoDB Storage Engine is a third-party plug-in storage engine, is the development of Innobase company, its biggest feature is to provide transaction control and other functions; Myisma Storage Engine Description: The table in the Myisma storage engine each table in the database is stored in three physical files named after the table name, respectively, the table name that holds the table structure definition. frm file, table data table name. myd file, table Index table name.    Myi file;    There are three kinds of MyISAM index types: b-tree, R-tree, Full-text, common b-tree type index;    The MyISAM storage engine has static fixed-length storage, dynamic variable-length storage and compressed storage compress.    MyISAM a table data file in the storage engine does not affect other tables or other data information after an error occurs;    InnoDB Storage Engine Introduction: MySQL In addition to the MyISAM storage engine is widely used as a InnoDB storage engine, he and MyISAM follow the Open Source license protocol;    The advantage of InnoDB is that it provides transaction control function;    InnoDB enhances the mechanism of MyISAM lock, realizes the function of row lock;    InnoDB data storage and MyISAM are not the same, although there are table structures that define information table names. frm files, table data and table indexes are stored in a file. IBD separate storage,. Ibdata shared storage;    The physical structure of InnoDB can be divided into two main categories: Data files (storing table data and table index data). IBD separate storage,. Ibdata shared storage two types;    Log file: Please do not delete all InnoDB log files This will make the database crash (crash), or the data loss;    Memory storage Engine: The memory storage engine is a kind of engine that stores the data in RAM; The memory storage engine does not store the data on disk, but only the table name. FRM structure, so if the database CRAsh or downtime can result in data loss;    Summary: The multi-storage engine is a major feature of MySQL that differs from other databases, followed by detailed descriptions of commonly used storage engines, and 4. MySQL security management is very important to the security of the data stored by the database;        Data loss of security is tantamount to losing everything;                Database system security related factors: perimeter network security: MySQL is based on the network environment, and the Internet itself there is an intrusion threat; to be prevented from the outermost layer;    Try to make MySQL exist in a protected LAN environment;        Host Defense: Perimeter network prevention has been protected, then there will be the possibility of intrusion, that is, LAN intrusion;        The main is to prevent unauthorized devices to connect to MySQL;    Intrusion database is harmful to steal data, delete data and make loopholes;                Database defense: With a second line of defense we can prevent a subset of threats, but do the devices that allow the host to log on have full permissions? Is it a trusted object?                The database defense is the Access Control authorization module of MySQL's own system, which is the last line of defense of MySQL intrusion.    Set the login user name and password and port number and set permissions; Code DEFENSE: SQL statement-related security factors, SQL injection attacks, program code-related security factors DDL, DML, DCL database definition language (CREATE ALTER DROP TRUNCATE) Database operations language (SELECT INSERT UPDATE        DELETE explan) Database Control Language (COMMIT ROLLBACK) MySQL access control implementation principle: MySQL access control actually has 2 parts: One is the user module management, one is the Access Control module management (authority);        The user module decides whether or not it can enter, and the Access Control Module (authority) Decides what can be done; For example: Client request (provide host, user name, password)--user module authentication (via Mysql.user table Authentication)--Client request query (DML, DDL)----Parse query Execute permissions--Right matching lookup (    Grant tables)--the backend continues to execute the MySQL access authorization policy: Not every user's permissions are as infinite; Each user's permission to do the smaller the better, to meet the use ofGood    First, understand the visiting host, understand the user needs, and finally classify the work, so as to ensure that the absolute necessary owner has grant (permission) permission; Note: Security no small matter, the data is an enterprise's wealth; 5, MySQL data backup and restore database backup use scenario: Data loss Scenario: Human error operation, software bug, hardware failure, security vulnerability non-data loss scenario: Data recovery in special scenarios, Development test Database Setup, database or data    Migration notes: Database data backup to solve the problem is not omnipotent; 6. Most of the factors that affect the performance of MySQL server are that the performance bottleneck of database application system is caused by database management software and database host itself;    The following is a MySQL database web scene as an example to analyze the bottleneck affecting performance;        The impact of business demand on performance: not all functions can be achieved, some unreasonable functions but finally become a burden, consumption of resources;        Unreasonable demand causes low input-output ratio of resources; The accumulation of useless functions makes the system excessively complex and affects the overall performance; (useless functions are mostly not offline, because of the risk, so the system is more and more complex, not only maintenance difficulties, system performance is getting worse) system architecture and implementation of the performance impact: A Web application is naturally inseparable from the application (Web Applic        ation) and Web application Server (Web server), Web server We control the tuning of the few are very mature products, Web application we can optimize many aspects;            The following types of data are not suitable for storage in the database: Binary multimedia data (memory consumption, CPU consumption);            pipelining Queue Data (continuous insert update Delete because each operation writes to the log file affects performance);            Ultra-large text data (waste of space resources) reasonable use of the application layer cache mechanism: the MySQL memory storage engine through the cache mechanism of successful cases many many, but the case of failure is also many;                The following is what you can use the cache mechanism: System configuration and rules of the data;                Basic information of active users (caching the user's basic information can greatly improve performance);                Statistical data of the time period;     Access trivial updates with less data;   The ability to rely too much on database SQL statements makes database operations inefficient: Try not to execute SQL multiple times in a loop, sometimes using 2 sql, so that IO and parsing resources are not consumed;            If the columns of an SQL query are not all used, split into 2 SQL, reducing the query of the non-applicable columns;            Avoid repeating the same SQL wasted resources (which may be contrary to the above two, with different logical considerations) the architecture design improperly brings performance problems and resource wasting problems: Low cache Hit rate, increased access pressure on the database, and wasted cache resource utilization;            Over-reliance on object-oriented and excessive pursuit of extensibility;            Excessive reliance on the database, some non-conforming to the database should be deposited into the file system;        Over-care user experience, such as the real-time update of the data without real-time update waste resources;            The performance impact of the query statement: When the MySQL connection thread receives the SQL from the client request, it is parsed and parsed, then the stored procedure interface is called through the execution plan, and finally the data is returned to the client display;            The execution of SQL is mainly IO consumption and CPU consumption (which can be tested by explain);        Note: There is time to test the next two tables to connect the query first and query one table information in and another table connection analysis which is good?            The impact of schema design on system performance: Database design improves performance;        Proper use of good paradigm is the greatest tuning of the design;    The impact of the hardware environment on performance: Consider the server IO and CPU processing power when concurrent access is more frequent; 7, MySQL database locking mechanism in order to ensure the integrity of the data of any kind of database has a locking mechanism;    The advantages and disadvantages of a database locking technology directly affect the database high concurrency processing and performance;            MySQL common storage engine InnoDB, MyISAM mysql locking mechanism introduction: Row-level locking: Row-level locking is the most important feature is that the particle size of the object is very small, is the most common form;        Because of locking particles under lock and lock processing more things, memory consumption, but also the most likely to occur deadlock; Table-Level locking: Table-level locking is exactly the opposite of row-level locking, which is locked MySQLThe largest particle in the storage engine;        Table locking logic is simple, fast processing, low energy consumption, not easy to deadlock;            Page-Level locking: page-level locking is a unique locking mechanism for MySQL, locking between row and table locks;        Page-level locking, like row-level locking, is easily deadlocked;            Note: Row-level locking is not MySQL's own locking mechanism, but the third-party INNODB storage engine locking mechanism;        InnoDB If a deadlock is generated by detecting a deadlock mechanism to determine the rollback of that transactional SQL, this will be judged based on the size of the affected data, so that transactional SQL that affects the data is executed successfully, the rollback affects small transactional SQL, or the deadlock mechanism expires the time to roll back the transaction SQL;            Advantages of InnoDB row-level locks: Reduce conflicts when many threads request different records;            Reduce changed data when the transaction is rolled back;        Make it possible to lock a single row of records for a long time;    Disadvantages of InnoDB: more memory is consumed than table-level locks and page-level locks;                        Optimizing the table lock optimization of Mysql:myisam by using the locking mechanism: MyISAM's table lock reduces resources compared to row lock and page lock, but it has a certain effect on concurrency performance, so the recommendation of optimizing table lock is how to improve concurrency performance.                Shorten the lock time;                The only way to make the SQL execution time as short as possible;                Large and complex SQL recommendations are divided into multiple small SQL distributed execution;            Establish efficient indexing and field-type restrictions as much as possible;            Separation of operations that can be parallel;        Make reasonable use of read-write priority;            Optimization suggestion for InnoDB row lock: Although the INNODB mechanism consumes a lot of resources than MyISAM's table lock mechanism, the high concurrency is far exceeding the latter;                InnoDB's Row locks also have a bottleneck: queries use indexes as much as possible to improve query speed;                a reasonable design index;        The scope of the query should not be too large;           System lock Condition query: Table lock condition query:     SHOW STATUS like '%table% '; Table_locks_immediate the number of times the table is locked table_locks_waited the number of times the table locks the wait table_locks_waited if the value becomes larger, the table contention ratio            More serious, need to optimize;                InnoDB row lock case query: SHOW status like '%innodb_row_lock% '; Innodb_row_lock_current_waits//is currently waiting for the number of locks innodb_row_lock_time//from System boot to now lock total length of time Inno DB_ROW_LOCK_TIME_AVG//Average time spent per wait Innodb_row_lock_time_max//wait for the longest time Innodb_row_lo Ck_waits//From the system boot up to the current number of requests above analysis: it is important to 1 3 5 These values innodb the overall performance of the storage engine is higher than the MyISAM storage engine 8, MySQL database query optimization mysql q    Uery optimizer:mysql query optimizer is a query optimizer module that provides an optimal execution plan;        Query statement optimization ideas and principles: optimization of the more needs to optimize the query;        Positioning optimization object performance bottleneck;//is IO or CPU or memory-specific optimization target;        Use show profiles more; starting from explain SQL;        Because it can show execution plan details the most likely sort in the index;        Only take out the columns you need;        Use the most effective filter conditions;    Avoid complicated joins and subqueries as much as possible; Optimize the query that needs to be optimized more: Two query per hour the number of Io executed is the same, one is to execute 10,000 times per hour consumes 20 Io, one is to execute 10 times per hour each consumes 20,000 IO so, which query should I optimize?            Answer: The first query dropped the IO from 20 to 18 and reduced by 2, then 2*10000 = 20,000 io second query if you can reduce 20,000 IO then 20000/10 = 2000 IO Then you need to reduce 2000 IO at a time        So we think that better optimization, which can reduce less IO, the first query; Executing high concurrency query is much higher than the risk of performing low concurrency, high concurrency of query is easy to let the system crash off, and so we restart the system load will be soaring near crash, so that we can not query where the problem occurs, and low concurrency of the query although also produce negative    At least within a controllable range; The principle of JOIN is ' small result set driving large result set ': A table 1000 data, b table 100,000 data SELECT a.*, b.name from a left JOIN B on a.id=b.a_id//Here is a table as the driver table Loop Connection B table Reduce the number of cycles;    b represents the driven table;        Explain SQL statement detailed analysis: note the value of Key_len, the smaller the better;    That is, often a where condition can be queried to do not use the second meaningless condition, oh, because of the consumption of memory;        Limitations of indexing in MySQL: The sum of the index key value lengths of the MyISAM storage engine cannot exceed 1000 bytes;        MySQL currently does not support function indexing;        MySQL Query condition =, <> cannot use index;    Use the like query as '%abc ' so that the index cannot be used;  JOIN principle and optimization ideas: SELECT a.* from User_group A left JOIN group_message B on a.group_id = b.group_id Left        JOIN group_message_content C on b.id = c.message_id WHERE a.user_id = 1; //Here is the User_group table as the driver table, a table through the index query group_id set as the driver table,//group_message table to query out the ID, and finally through the index message_id query the final result set;        Reduce the number of cycles of join statements as much as possible;        The connected fields must be indexed;        GROUP By/order by using the index as far as possible; 9, MySQL database schema (icon) design and tuning efficient model design: First consider the first second third paradigm;            Appropriate sink Yu Jian query to minimize join;        Example: the user table, the message table can add a author_name field;            Vertical separation of large characters;        There is a content field in the Blog table text type can be separated into blog_detail table;            The statistics are updated in real time;    Statistical data is not recommended for real-time updates, here is the impact of business demand performance, can be used in real-time updates;        Appropriate data types: Use small data types as much as possible to reduce disk space;    Numerical comparisons are performed by the appropriate data types;        Canonical object naming: Database and table naming should be as far as possible and the product description of the corresponding;        The field name should also match the description of the column information;    Index names include field names or field abbreviations as much as possible;    Note: Database performance improvements are not optimized but designed; 10, MySQL performance optimization mysql installation optimization: Install the appropriate database version;        MySQL Log performance optimization: Error log        Changelog (update log);        Binary log (binlog);        Querying log (query logs);    Slow query logs (slow query SQL);    MySQL query cache optimizer: MySQL query cache generates a flight that allows MySQL to produce a quality-of-performance leap; Other optimizations for MySQL server: The maximum number of connections allowed by the network; max_connections the number of large connections allowed by the user to handle the concurrency capability; max_user_connextions PIN connection limit for individual users;        In the transmission of network packets, the initial size of Net_buffer before transmitting the information; net_buffer_length;        The maximum value of one transmission of information in network transmission; max_allowed_packet;    Maximum number of MySQL connection waits; back_log;11, common storage engine optimizes MyISAM storage engine optimization in MySQL; MySQL InnoDB storage engine optimization; 12, MySQL can expand the design of the basic principles 13, the extensible design of MySQL replication (copy) 14, extensible design data segmentation what is data cutting: according to different tables to be divided into different databases this is the vertical        Straight (longitudinal) cutting mode;        Separate the same table into different databases according to some logical relationship. This is the horizontal cutting mode;        Note: Vertical separation is characterized by simple, low-coupling tables that can be vertically separated;    If we do a vertical separation and still do not improve performance, we also do a horizontal separation;    Vertical separation of data: the tables in the database are composed of several functional modules, the smaller the coupling degree before each function module, the easier the vertical separation;        The advantages of vertical separation: The database splitting is simple and clear, the splitting rule is explicit;        The application module is clear and easy to integrate.    Data maintenance convenient and easy to locate;        Vertical Separation Disadvantage: Partial table associations cannot be completed in the database;    Cut into a certain degree after the expansion of the lower; Data horizontal separation: The horizontal separation of data is high concurrency query table through a field of the rules bar data stored in different tables to query, so that the data collection of each table is not the previous table large, so as to prompt the query speed, the common scenario is to use the UserID to 5 modulo and then    stored in 5 tables, the display query through the UserID to 5 modulo, the remainder will know where the userid exists;    Note: Using data separation technology to separate a large data MySQL server into multiple small data MySQL server, so as to improve the query and write performance, the best way is to first vertical separation and then horizontal separation; 15. Extensibility Settings Cache and search utilization    Distributed cache caching Solution memcached;    Using search to achieve efficient full-text indexing; Note: DatabaseJust a tool for storing data, his feature is persistence, in addition to the database we have a lot of other ways of data storage; 16, MySQL cluster (cluster) MySQL cluster is a complete distributed database system based on NDB cluster storage engine; 

　　

MySQL performance tuning and architecture design notes