MySQL表空间大小变化解析及优化策略

mysqladd表数据后，表空间大小为什么不变

这是由于删除操作后数据文件中残留的碎片造成的。
DELETE只删除数据标识位，插入新数据时不对数据文件进行排序，具有该删除标识的记录空间将被重新使用。
另外，在实际操作过程中，还发现有2种情况存在这个问题。
(1)当DELETE后面跟一个条件时，就会出现这个问题。
例如：根据deletefromtable_namewhere条件删除数据后，数据表占用的空闲空间不会改变。
(2)不遵循条件直接删除时。
比如deletefromtable_name删除数据，数据表空间也会变成0。
网站实际运行中存在问题。
经常会有这样的条件数据删除操作。
随着时间的推移，这是否会浪费数据库大量空间？此时我们应该使用OPTIMIZETABLE命令来优化表。
如何使用OPTIMIZE以及何时应使用OPTIMIZE命令？命令语法：OPTIMIZE[LOCAL|NO_WRITE_TO_BINLOG]TABLEtbl_name[,tbl_name]...最简单：Optimizetablephpernote_article;如果您删除了表的大部分内容，或者修改了包含可变长度行（包含VARCHAR、BLOB或TEXT列）的表，则应使用OPTIMIZETABLE。
删除的记录保存在一个链表中，后续的INSERT操作将重用旧的记录位置。
您可以使用OPTIMIZETABLE重新使用未使用的空间并对数据文件进行碎片整理。
在大多数设置中，您不需要运行OPTIMIZETABLE。
即使您对可变长度行进行了大量更新，您也不需要经常运行它，只需每周一次或每月一次，仅针对特定表。
OPTIMIZETABLE仅适用于MyISAM、BDB和InnoDB表。
请注意，MySQL在OPTIMIZETABLE运行时锁定表。
因此，此操作必须在网站访问量较少的时期进行。
TRUNCATE的语法结构为：TRUNCATE[TABLE]tbl_name这是一个简单的示例。
我想删除朋友表中的所有记录。
我可以使用以下命令：truncatetablefriends;mysql表中一条条记录删除完成后，截断相当于保留了mysql表的结构，重新创建了相当于新表的所有状态，因此容量减少了。
当然，用truncatetable来删除我们的网站是不行的，因为之后所有数据都会丢失，这样删除肯定不合理，然后修复优化表。

如何设置合理的mysql的参数？

[客户端]\x0d\x0aport=3306\x0d\x0asocket=/tmp/mysql.sock\x0d\x0a[mysqld]\x0d\x0aport=3306\x0d\x0asocket=/tmp/mysql.sock\x0d\x0a\x0d\x0abasedir=/usr/local/mysql\x0d\x0adatadir=/data/mysql\x0d\x0apid-file=/data/mysql/mysql.pid\x0dmaster\x0d\x0a\x0d\x0askip-name-resolve\x0d\x0a#外部连接上的MySQLDNS拒绝解析使用此选项可以防止MySQL在DNS解析上花费时间。
但需要注意的是，如果启用此选项\x0d\x0a#所有远程主机连接授权都必须使用IP地址，否则MySQL将无法正常处理连接请求\x0d\x0a\x0d\x0a#。
Skip-networking\x0d\x0a\x0d\x0aback_log=600\x0d\x0a#MySQL可以拥有的连接数。
当主MySQL线程在短时间内收到大量连接请求\x0d\x0a#并且主线程需要一些时间（无论多么短）来检查连接并启动新线程时，此方法有效。
back_log值表示在MySQL暂时停止响应新请求之前短时间内可以排队多少请求。
\x0d\x0a#如果您预计短时间内会有大量连接，则需要添加此内容。
即如果MySQL连接数据达到max_connections，则超过新请求的栈栈数back_log将被存入栈中，并且不会提供连接资源。
\x0d\x0a#此外，该值(back_log)受到操作系统传入TCP/IP连接的侦听队列大小的限制。
\x0d\x0a#您的操作系统对此队列大小有自己的限制（有关此变量的最大值，请参阅操作系统的文档）\x0d\x0a\x0d\x0amax_connections=1000\x0d\x0a#\x0d\x0aMySQL的最大连接数增加了并行连接数。
它提供了一个缓冲区，占用了更多内存，所以修复这个问题。
定价要合理，不要盲目提高套餐价格。
要确定该值的大小，可以使用“conn%”通配符来查看当前状态下的连接数。
\x0d\x0a\x0d\x0amax_connect_errors=6000\x0d\x0a#如果同一主机的断开错误连接数超过此参数值，则该主机将被阻止连接。
要删除主机：执行FLUSHOST。
\x0d\x0a\x0d\x0aopen_files_limit=65535\x0d\x0a#MySQL中打开文件描述符默认最小限制为1024，如果不设置open_files_limit，则比较max_con与最大位数*5，使用ulimit-n\x0d\x0a#当设置了open_files_limit时，open_files_limit和顶部连接*5使用它。
\x0d\x0a\x0d\x0atable_open_cache=128\x0d\x0a#MySQL每次打开一个表时，都会读取一些数据到table_open_cache中。
选择。
默认值为64\x0d\x0a#如果假设系统有200个串口连接，则该参数为200*N（N为每个连接所需的文件描述符数量）；如果系统有200个串口连接，则\x0d太大无法处理那么多。
文件描述符，那么客户端就会失败并且\x0d\x0a\x0d\x0amax_allowed_pa​​cket=4M\x0d\x0a#可接受的数据包大小，增加这个变量的值是非常安全的，因为它只在必要时才使用分配更多内存。
例如，仅当您提交长查询或MySQLd必须返回大结果行时，MySQLd才会分配更多内存。
\x0d\x0a#该变量采用较小的默认值的原因是作为预防措施，以捕获客户端和服务器之间的错误数据包并防止意外使用大数据包导致内存溢出。
\x0d\x0a\x0d\x0abinlog_cache_size=1M\x0d\x0a#如果事务没有提交，则生成的日志会写入缓存中；当事务提交时，日志保留在磁盘上。
默认binlog_cache_size为32K。
该值用于计算内存表的最大行大小。
该变量支持\x0d\x0a\x0d\x0atmp_table_size=16M\x0d\x0a#MySQL动态更改堆（堆叠）表缓冲区的大小。
所有连接都在单个DML语句中完成，大多数连接甚至可以在没有临时表的情况下完成。
\x0d\x0a#大多数临时表是内存中（HEAP）表。
具有较大记录长度（所有列长度之和）或BLOB列的临时表存储在硬盘上。
\x0d\x0a#\x0d\x0a\x0d\x0a如果内部堆（heap）表的大小超过tmp_table_size，MySQL会根据需要自动将内存中堆表转换为基于硬盘的不可接吻表。
\x0d\x0a您还可以通过设置tmp_table_size选项来增加临时表的大小。
换句话说，如果增加这个值，MySQL会增加堆栈表的大小，从而提高连接查询的速度\x0d\x0a\x0d\x0aread_buffer_size=2M\x0d\x0a#MySQL读取。
按缓冲区大小。
用于读取表的串行扫描请求的缓冲区分配，MySQL分配内存缓冲区。
read_buffer_size变量控制该缓冲区的大小。
\x0d\x0a#如果表的串行扫描请求非常频繁，并且您认为频繁的扫描运行得太慢，可以通过增加该变量的值和内存缓冲区大小来提高性能\x0d\x0a\x0d。
\x0aread_rnd_buffer_size=8M\x0d\x0a#MySQL随机读缓冲区大小。
当以任何顺序（例如，以排序顺序）读取行时，会分配随机读取缓冲区。
在处理排序查询时，\x0d\x0a#MySQL首先扫描缓冲区，以避免磁盘搜索，提高查询速度。
然而，MySQL为每个客户端连接分配这个缓冲区空间，所以你应该尝试正确设置MySQL用于执行排序的缓冲区大小。
如果你想提高ORDERBY的速度，首先看看MySQL是否可以使用索引来代替额外的排序步骤。
\x0d\x0a#如果没有，可以尝试增大type_buffer_size变量的大小\x0d\x0a\x0d\x0join_buffer_size=8M\x0d\x0a#可供联合查询操作使用。
缓冲区大小，和sort_buffer_size一样，这个参数关联的内存也是每个连接的一张表\x0d\x0a\x0d\x0athread_cache_size=8\x0d\x0a#这个值（默认8）只是表。
缓存中存储的线程数量表示可重用性，如果连接断开时缓存还有空间，则客户端线程将被放入缓存中。
\x0d\x0a#如果再次请求该线程，则该请求。
如果有线程\x0d\x0a#增加该值将提高Connections和Threads_created的系统性能。
通过比较状态就可以看出它的作用。
灵活的。
(_>表示要编辑的值)\x0d32\x0d\x0a#大于3G—>64\x0d\x0a\x0d\x0aquery_cache_size=8M\x0d，对于同一条SELECT语句（区分大小写）直接读取结果从缓存中。
根据MySQL用户指南，使用查询缓冲可以实现高达238%的效率。
\x0d\x0a#可以通过检查status值'Qcache_%'来检查query_cache_size设置是否合理：如果Qcache_lowmem_prunes的值太大，说明经常出现buffer不足的情况，如果che_hits的值太大，说明查询缓存使用过于频繁，Qcache_hits的值如果不大，说明你的查询频率太低。
\x0d\x0a#在这种情况下，您可能需要考虑不使用查询缓冲，因为使用查询会影响效率。
另外，在SELECT语句中添加SQL_NO_CACHE可以明确表示不使用查询缓存。
1M\x0d\x0a\x0d\x0akey_buffer_size=4M\x0d\x0a#指定用于索引的缓冲区大小，为了获得更好的索引性能（对于所有读取和多次写入），请尽可能增加它。
如果你把它设置得太大，\x0d\x0a#系统将开始打印并适当缩小它。
对于内存4GB左右的服务器，该参数可以设置为384M或512M。
可以通过检查key_read_requests和key_read,\x0d\x0a#的状态值来检查key_buffer_size设置是否合理。
key_reads/key_read_requests的比例应尽可能低，\x0d\x0a#至少为1:100，1:1000更好（上面的状态值可以使用SHOWSTATUSLIKE'key_read%'获取）。
注意：该参数值设置太大会降低服务器的整体性能\x0d\x0a\x0dx0atransaction_isolation=REPEATABLE-READ\x0d\x0a#MySQL支持4种事务隔离级别，分别是：COMMITTED、REPEATABLE-READ、\x0d\x0a#如果不指定，MySQL默认为REPEATABLE-READ，ORACLE默认为READ-COMMITTED\x0d\x0a\x0d\x0alog_bin=mysql-bin\x0d\x0abinlog_format=mix\x0d\x0aexpire_log。
s_days=30#删除超过30天的binlog\x0d\x0a\x0d\x0alog_error=/data/mysql/mysql-error.log#错误日志路径\x0d\x0aslow_query_log=1\x0d\x0along_query_time=1#延迟查询慢查询时间是否超过1秒is\x0d\x0aslow_query_log_file=/data/mysql/mysql-slow.log\x0d\x0a\x0d\x0aperformance_schema=0\x0d\x0aclear_default_for时间戳\x0d\x0a\x0d\x0a#lower_case_table_names=1#不区分大小写/x0d\dex0以避免外部锁定。
默认情况下启用此选项创建数据空间\x0d\x0a#independent。
表空间的优点：\x0d\x0a#1。
每个表都有自己独立的表空间。
\x0d\x0a#2。
每个表的数据和索引都存储在自己的表空间中。
\x0d\x0a#3。
单个表可以在不同数据库之间移动。
\x0d\x0a#4。
空间可以回收（空表不能自行回收，废弃的操作除外）。
添加的表非常大，例如超过100G\x0d\x0a#总结：\x0d\x0a#共享表空间相对于插入操作来说优势不大。
其他人不执行独立的表位置。
启用独立表空间时，请合理调整：innodb_open_files\x0d\x0a\x0d\x0ainnodb_open_files=500\x0d\x0a#限制Innodb打开的表的数据如果库中表较多，请添加此项。
该值的默认值为300\x0d\x0a\x0d\x0ainnodb_buffer_pool_size=64M\x0d\x0a#InnoDB使用缓冲池来存储索引和核心。
原始数据与MyISAM不同。
\x0d这个变量应该是服务器物理内存大小的80%\x0d在32位系统上，你的每个输入进程可能会受到2-3.5G用户级内存限制\x0d\x0a#太高了，不要。
\x0d\x0a\x0d\x0ainnodb_write_io_threads=4\x0d\x0ainnodb_read_io_threads=4\x0d\x0a#innodb转换为CPU核心处理数据页读写请求的数量，默认4\x0d64\x0d\x0a\x0d\x0ainnodb_thread_concurrency=0\x0d\x0a#default设置0是，这意味着CPU多建议将其设置为0，以更好地利用核心处理。
提高并发能力\x0d\x0a\x0d\x0ainnodb_purge_threads=1\x0d\x0a#InnoDB中的purge功能是一种操作，通常用于删除冗余数据。
在以前的版本中，清理操作是主线程的一部分，这意味着它可能会在运行时阻塞其他数据库操作。
=x0d用户可以通过设置innodb_purge_threads配置参数来选择刷新操作。
默认情况下，该参数设置为0（不使用单独的线程）。
如果innodb_flush_log_at_trx_commit的值为0，则日志缓冲区每秒刷新到磁盘，并且在不执行任何操作时处于空闲状态。
事务由mysqlmast执行，erthread线程\x0d\x0a#每秒将日志缓冲区写入磁盘，默认值1，每次进入事务时都会将日志缓冲区写入日志。
.x0d每秒刷新日志100%请注意，不能保证它会被下载到磁盘。
\x0d\x0a#每次执行事务时，都会将数据写入物理磁盘。
\x0d\x0a#默认值1是为了验证你是否可以将此配置项设置为1以外的值，当然但是当系统崩溃时，你会丢失1秒的数据。
如果\x0d\x0a#设置为0，当mysqld进程崩溃时，最后1秒的事务将会丢失。
如果设置为2，则只有在操作系统崩溃或关闭时才会丢失最后1秒的数据。
InnoDB在执行恢复时忽略该值。
\x0d\x0a#摘要\x0d\x0a#设置为1确实很可靠，但页面性能较差（相对于其他两个参数而言，却是不能接受的）。
如果数据一致性和完整性要求不高，可以设置为2。
如果您只想获得最佳性能，例如高并发写入的日志服务器，请将其设置为0以获得最佳性能。
较大的缓冲区可以提高性能，但意外故障可能会导致数据丢失。
MySQL开发者建议将其设置在1-8M\x0d\x0a\x0d\x0ainnodb_log_file_size=32M\x0d\x0a之间#此参数决定数据日志文件的大小，但较大的设置将提高性能并防止失败。
Database\x0d\x0a\x0d\x0ainnodb_log_files_in_group=3\x0d\x0a#增加返回时间为了提高性能，MySQL可以将日志文件以循环方式写入多个文件。
建议设置为3\x0d\x0a\x0d\x0ainnodb_max_dirty_pages_pct=90\x0d\x0a#innodb主线程刷新缓存池中的数据使脏数据量增加到90%以下\x0d\x0a\x0d\x0ainnodb_lock_wait_timeout=120\x0d\x0a#InnoDB事务在回滚之前等待锁的秒数。
InnoDB自动检测锁定表上的事务死锁并回滚事务。
InnoDB使用LOCKTABLES语句观察锁定设置。
默认值为50秒。
非常适合一次导入100-1000多条记录时提高效率。
默认值为8M。
数据量可以增加一倍。
\x0d\x0a\x0d\x0amyisam_sort_buffer_size=8M\x0d\x0a#设置MyISAM恢复表、在恢复中创建索引或使用CREATEINDEX或ALTERT时要使用的缓冲区大小。
ABLE进程中排序缓冲区分配的MyISAM索引\x0d\x0a\x0d\x0amyisam_max_sort_file_size=10G\x0d\x0a#如果临时文件大于索引，请勿使用快速排序索引。
注：该参数在byte\x0d\x0a\x0d\x0amyisam_repair_threads=1\x0d\x0a#如果该值大于1，则修复并行化该过程。
创建MyISAM表索引（每个索引在其自己的线程中）\x0d\x0a\x0d\x0ainteractive_timeout=28800\x0d\x0a#服务器在关闭交互式连接之前等待活动的秒数。
交互式客户端被定义为使用mysql_real_connect()中的CLIENT_INTERACTIVE选项的客户端。
默认值：28800秒（8小时）\x0d\x0a\x0d\x0await_timeout=28800\x0d\x0a#在服务器关闭非交互式连接之前等待活动。
秒。
当线程启动时，会话超时值根据全局超时值或全局interactive_timeout值进行初始化，具体取决于客户端类型（通过mysql_real_connect()的连接选项CLIENT_INTERACTIVE）。
参数默认值：28800秒（8小时）\x0d\x0a#MySQL服务器支持的最大连接数是有上限的，因为建立每个连接都会占用内存，我们希望在客户端响应完成后才起作用。
在操作\x0d\x0a#之后连接到MySQL服务器应该断开连接并释放保留的内存。
如果你的MySQL服务器有大量的空闲连接，它们只会白白消耗内存，但如果连接不断堆积和断开，\x0d\x0a#最终会达到MySQL服务器连接的最大限制，此时会报“连接太多”错误。
wait_timeout值的设置要根据系统的运行状态来判断。
\x0d\x0a#系统运行一段时间后，可以使用showprocesslist命令查看当前系统连接状态。
太大了，\x0d\x0a#可以适当调整一下。
Interactive_timeout和wait_timeout必须同时设置才能生效。
\x0d\x0a\x0d\x0a[mysqldump]\x0d\x0aquick\x0d\x0amax_allowed_pa​​cket=16M#服务器发送和接收的最大数据包长度\x0d\x0a[myisamchk]\x0d\x0akey_buffer_size=8M\x0d\x0sort_buffer_size=8M\x0d\x0aread_buffer=4M\x0d\x0write_buffer=4M