Shulou(Shulou.com)06/01 Report--

This article mainly introduces MySQL optimization method, the article is very detailed, has a certain reference value, interested friends must read!

SQL Optimization

SQL Optimization Analysis

index tuning

Optimize database objects

Optimize the data types of tables

Table split (horizontal, vertical)

inverse normal form

Use intermediate tables

Optimizing MySQL Server

mysql memory management optimization

log mechanism and optimization

Adjust mysql concurrent parameters

application optimization

database connection pool

Use cache to reduce stress

Load Balancer Build Cluster

Master-master synchronization, master-slave replication

Mysql optimization problem analysis location analysis SQL execution frequency show status For example: analysis read as main, or write as main location inefficient execution SQl slow query log location-log-slow-queries = xxx (specified file name) SHOW PROCESSLIST View the current thread in progress, including thread status, lock table analysis SQL execution plan explain "your sql"desc "your sql"-partial parameter analysis select_type: SIMPLE simple table, without table join or subquery PRIMARY main query, i.e. outer query UNION SUBQUER first selecttype of subquery: ALL full table scan index full scan range index scan ref prefix scan eq_ref similar to ref index used is unique index const/system single table has at most one matching row NULL do not access table or index Get results directly show profile analysis SQLselect @@have_profiling Whether select @@profiling is supported Whether to enable execution of "your sql"show profiles show profile block io for QUERY 17 Index optimization index storage classification B-TREE index: common, most support HASH index: only memory engine supports R-TREE index: Spatial index is a special index type of MyISAM, mainly used for geospatial data type full-text index: Full-text indexing, a special index type for MyISAM, innodb Since 5.6 Support index creation and deletion Add index ALTER Table `table_name` ADD PRIMARY KEY （`column`）ALTER Table `table_name` ADD UNIQUE （`column`）ALTER Table `table_name` ADD INDEX （`column`）ALTER Table `table_name` ADD FULLTEXT (`column`) Delete ALTER Table `table_name` drop index_nameMysql Cases where an index can be used Match full value Match value range Query Match leftmost prefix Query index only (overwrite query) Match column prefix (add prefix index) Partially exact + Partially range Scenarios where an index cannot be used % switch like query data type appears implicit conversion composite index query condition does not contain the leftmost part use index still slower than full table scan or split condition mysql statement optimization regularly optimize table table_name merge table space fragments, effective for MyISAM, BDB, INNODB If the prompt does not support, you can use mysql --skip-new or mysql --safe-mode to restart, so that other engines support common optimization Try to avoid full table scan, Index where and orderby columns Avoid using where!= as much as possible avoid expression operations on fields avoid functional operations on fields override queries and return desired fields optimize nested queries associative queries are superior to subqueries composite indexes or composite indexes leftmost index principle replace exist with exist when there is a large amount of duplicate data in an index column SQL queries may not take advantage of index optimization Database object optimization Table data type PROCEDURE ANALYSE (16,256) Excludes more than 16 ENUM recommendations "your sql" PROCEDURE ANALYSE () Table splitting Vertical splitting for certain columns Common, uncommon Horizontal splitting Tables are very large The data in the table is independent and can be easily classified Need to store multiple media in the table Inverse pattern Add redundant columns, Add derived columns, reorganize tables and split tables Use intermediate tables Data query volume Big data statistics, analysis scenarios Mysql engine comparison What engine does mysql have?

commands for table engines show engines; view storage engines supported by myql show variables like '%storage_engine'; view mysql default storage engines show create table_nameView storage engines used by specific tables About innodb1. Provide transaction, rollback, system crash repair capabilities, multi-version concurrency control transactions 2. Support self-addition 3. Support foreign key 4. Support transactions and transaction correlation functions 5. Row-level locks supporting mvcc About MyISAM1. No transaction or row-level lock support. Only concurrent table locks are supported. They are mainly used for select2. Three different storage structures are supported: Static, dynamic, compression parameter optimization mysql background service MyISAM memory optimization #modify the configuration file of the corresponding server location my.cnfkey_buffer_size determines the size of the myisam index block buffer, directly affecting the access efficiency of the table Recommendation 1/4 available memory read_buffer read cache write_buffer write cache InnoDB memory optimization innodb_buffer_pool_size storage engine maximum cache size for table data and index data innodb_old_blocks_pct LRU algorithm determines the proportion of old blocks innodb_old_blocks_pct time LRU algorithm data transfer interval time mysql concurrency parameter max_connections Maximum number of connections, default 151back_log A large number of connections can be processed in a short time, and table_open_cache can be appropriately increased. The number of table caches that can be opened by all SQL execution threads is controlled by other parameters. thread_cache_size controls the number of cache customer service threads, speeding up database connection speed. According to threads_created/connections to measure whether it is appropriate innodb_lock_wait_timeout Control transaction waiting time for row lock, default 50msMysql Application Optimization Introduction Why do application optimization

the importance of data

mysql service and its own performance bottleneck

Ensure stable and reliable operation of large-scale systems

Apply optimization methods

use of a connection pool

Reduce real connections to mysql

a. Avoid duplicate execution of the same data (query cache)

b. Use mysql cache (sql cache)

Load Balancer

a. LVS Distributed

b. Read-write separation (master-master replication, master-slave replication to ensure data consistency)

The above is all the content of this article "MySQL optimization method", thank you for reading! Hope to share the content to help everyone, more relevant knowledge, welcome to pay attention to the industry information channel!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.