|
MyISAM, a commonly used storage engine in MySQL characteristic:
1. Concurrency and lock levels
2. Repair table damage
check table tablename
repair table tablename
3. Index types supported by MyISAM tables ①, full-text index ②, prefix index
4. MyISAM table supports data compression
myisampack
limit:
The default table size is 4G for versions < MySQL 5.0
If the storage meets the requirements, modify MAX_Rows and AVG_ROW_LENGTH
The default support for versions > MySQL 5.0 is 256 TB Applicable scenarios:
1. Non-transactional applications
2. Read-only applications
3. Space applications
Innodb, a commonly used storage engine in MySQL Characteristics of the Innodb storage engine
1. Innodb is a transactional storage engine
2. Fully support ACID features of transactions
3. Redo Log and Undo Log
4. Innodb supports row-level locks Innodb uses tablespaces for data storage and creates a separate tablespace for each table
innodb_file_per_table
ON: Independent tablespace: tablename.ibd
OFF: System tablespace: ibdataX (X is a number starting from 1)
How to choose and compare system tablespace and independent tablespace:
The system tablespace cannot be shrunk. The independent tablespace can be shrunk by using the optimize table command. The system file system tablespace will cause an IO bottleneck. The independent tablespace can refresh data to multiple files at the same time.
Steps for table transfer:
1. Use mysqldump to dump all database table data
2. Stop the MySQL service, modify the parameters, and delete Innodb related files
3. Restart the MySQL service and rebuild the Innodb system tablespace
4. Re-import data
CSV, a common storage engine in MySQL File system storage characteristics
1. Data is stored in text format
2. .csv file stores table contents
3. The .csm file stores table metadata such as table status and data volume
4. .frm file stores table structure information
5. Store in csv format
6. All columns must not be Nullable
7. No index support Applicable scenarios:
Suitable as an intermediate table for data exchange (spreadsheet -> csv file -> MySQL database directory)
Archive, a commonly used storage engine in MySQL File system storage characteristics 1. Compress table data with zlib to reduce disk I/O
2. Data is stored in files with the suffix ARZ Features of Archive storage engine
1. Only insert and select operations are supported
2. Only allow indexes on auto-incrementing ID columns
Applicable scenarios:
Logging and data collection applications
MySQL Common Storage Engine Memory File system storage characteristics
1. It is also called HEAP storage engine, so the data is stored in memory Features:
1. Support HASH index and Btree index
2. All fields have a fixed length varchar(10)=char(10)
3. Does not support large fields such as BLOG and TEXT
4. Memory storage engine uses table-level locks
5. The maximum size is determined by the max_heap_table_size parameter Applicable scenarios:
1. Used to search or map tables, such as the correspondence table between postal codes and regions
2. Used to save the intermediate table generated in the data distraction
3. Result table for caching periodic aggregation data
MySQL Common Storage Engine Federated Features:
1. Provides a method to access tables on remote MySQL servers
2. Data is not stored locally, and all data is placed on the remote server
3. The table structure and remote server connection information need to be saved locally How to use the default static state, enable the need to increase the federated parameter at startup
mysql://user_name[:password]@host_name[:port]/db_name/table_name Applicable scenarios:
Occasional statistical analysis and manual query How to choose the right storage engine Reference conditions
1. Whether to support transactions
2. Regular backup
3. Crash recovery
4. Unique features of storage engines
Introduction to Mysql server parameters MySQL obtains configuration information path 1. Command line parameters mysqld_safe --datadir=/data/sql_data 2. Configuration File View the configuration file command:
[root@localhost ~]# mysqld --help --verbose | egrep -A 1 'Default options'
A valid path to a configuration file
/etc/my.cnf /etc/mysql/my.cnf /usr/etc/my.cnf ~/.my.cnf Scope of MySQL Configuration Parameters 1. Global parameters
set global parameter name = parameter value;
set @@global.parameter name:=parameter value;
2. Session parameters
set [session] parameter name = parameter value;
set @@session.parameter name:=parameter value; Memory configuration related parameters
1. Determine the upper limit of available memory
2. Determine the memory used by each MySQL connection
sort_buffer_size
join_buffer_size
read_buffer_size
read_rnd_buffer_size
3. Determine how much memory you need to reserve for the operating system
4. How to allocate memory for the cache pool
Innodb_buffer_pool_size
Note: The consideration criteria for setting the size of the cache pool is: total memory - (memory required for each program * number of connections) - system reserved memory
key_buffer_size
select sum(index_length) from information_schema.tables where engines='myisam'
I/O related configuration parameters
Innodo I/O related configuration
Innodb_log_file_size The size of a single transaction log
Innodb_log_files_in_group controls the number of file days. Total transaction log size = Innodb_log_files_in_group * Innodb_log_file_size
Innodb_log_buffer_size = (32M or 128M)
Innodb_flush_log_at_trx_commint
0: Write the log to cache once a second and flush the log to disk
1 [Default]: Write the log to cache and flush the log to disk at each transaction commit
2[Suggestion]: Each time a transaction is committed, the log data is written to the cache and flush log to disk once a second
Innodb_flush_method=O_DIRECT
Innodb_file_per_table = 1
Innodb_doublewrite = 1 MyISAM I/O related configuration
delay_key_write
OFF: flush dirty blocks in the key buffer to disk after each write operation
ON: Delayed refresh is used only for tables for which the delay_key_write option was specified when keying the table.
ALL: Use delayed write for all MyISAM tables
Security-related configuration parameters
expire_logs_days specifies the number of days to automatically clean up binlog
max_allowed_packet controls the packet size that MySQL can connect to. It is recommended to set it to 32M. If master-slave replication is used, the parameter should be set to the same
skip_name_resolve disables DNS lookups
sysdate_is_now Ensures that sysdate() returns a valid date
read_only prohibits write operations by users without super privileges. Note: It is recommended to enable this function in the slave database in master-slave replication. To ensure that the operations in the slave database cannot be modified, they can only be synchronized from the master database
skip_slave_start disables automatic recovery of Slave (used from settings in the library)
sql_mode sets the SQL mode used by MySQL (be careful, as this may cause MySQL to fail to execute)
① strict_trans_tables If the given data cannot be inserted into the database, the transaction engine will terminate the operation, and it will have no effect on the non-transaction engine. ② no_engine_subitiution When specifying engines in create table, if the engine is not available, the default engine will not be used to create the table. ③ no_zero_date The date of 0 year 0 month 0 day cannot be inserted into the table. ④ no_zero_in_date Do not accept a date with a partial value of 0. ⑤ noly_full_group_by
Other commonly used configuration parameters
sync_binlog controls how MySQL flushes binlog to disk
tmp_table_size and max_heap_table_size control the size of temporary memory tables (do not set them too large to avoid memory overflow)
max_connections controls the maximum number of connections allowed (the default is 100, which is a bit small, adjust the size appropriately according to your business) What affects performance The impact of database design on performance
1. Excessive denormalization creates too many columns for the table
2. Excessive normalization leads to too many table associations (the number of associated tables should be controlled within 10 as much as possible)
3. Using non-prefixed partition tables in OLTP environments
4. Use foreign keys to ensure data integrity Summarize Order of performance optimization
1. Database structure design and SQL statements
2. Configuration of selection parameters of database storage engine
3. System selection and optimization
4. Hardware upgrade You may also be interested in:- Detailed explanation and practical exercises of Mysql tuning Explain tool (recommended)
- 15 important variables you must know about MySQL performance tuning (summary)
- In-depth analysis of I/O overhead for SQL Server performance tuning
- SQL Server performance tuning: How to reduce query time from 20 seconds to 2 seconds
- SQL Server Performance Tuning Cache
- Practical sharing of SQL tuning for systems with tens of millions of users
| 
