mysql 中的 my.ini 配置文件

小灰灰 2021-02-12 17:38 547阅读 0赞

第一步，我们找到mysql安装文件下面的my.ini文件，打开可以看到第一句：   
\# MySQL Server Instance Configuration File   
Mysql服务实例配置文件   
  
好，咱们撇开那些不是那么重要的字幕，直接进入重点，看看它可以配置些什么，和到底配置了些什么，那么带着这样的心态，我们往下看。    
  
CLIENT SECTION   
客户端章节：    
\[client\]   
port=3306            端口号=3306

**注：此处可以添加**  如下内容  直接登录mysql

user=XXXXXX

password=XXXXXX

\[mysql\]   
default-character-set=latin1    默认字符编码=latin1   
  
SERVER SECTION   
服务端章节    
\[mysqld\]   
  
\# The TCP/IP Port the MySQL Server will listen on   
TCP/IP Port 监听mysql的端口号       
port=3306   
  
  
\#Path to installation directory. All paths are usually resolved relative to this.    
安装目录路径 。所有的路径都是作为这个路径的相对路径。    
basedir="E:/programfile/MySql5.1/"   
  
\#Path to the database root   
数据库数据的根路径    
datadir="C:/ProgramData/MySQL/MySQL Server 5.1/Data/"   
  
\# The default character set that will be used when a new schema or table is   
\# created and no character set is defined   
默认的字符集编码作用于当一个新的schema 或者表被创建的时候没有指定字符集。    
default-character-set=latin1   
  
\# The default storage engine that will be used when create new tables when   
默认的存储引擎作用于当创建新表的时候    
default-storage-engine=INNODB   
  
\# Set the SQL mode to strict   
设置严格的SQL模型    
  
sql-mode="STRICT\_TRANS\_TABLES,NO\_AUTO\_CREATE\_USER,NO\_ENGINE\_SUBSTITUTION"   
  
  
\# The maximum amount of concurrent sessions the MySQL server will   
\# allow. One of these connections will be reserved for a user with   
\# SUPER privileges to allow the administrator to login even if the   
\# connection limit has been reached.   
Mysql 服务端允许最大的并发数，其中一个连接将留给用户使用超级权限连接数据库，即使连接数已经达到最高限额    
max\_connections=100   
  
\# Query cache is used to cache SELECT results and later return them   
\# without actual executing the same query once again. Having the query   
\# cache enabled may result in significant speed improvements, if your   
\# have a lot of identical queries and rarely changing tables. See the   
\# "Qcache\_lowmem\_prunes" status variable to check if the current value   
\# is high enough for your load.   
\# Note: In case your tables change very often or if your queries are   
\# textually different every time, the query cache may result in a   
\# slowdown instead of a performance improvement.   
查询缓存用于在缓存中SELECT结果，返回不是实际查询数据库的结果。   
从缓存中查询的结果会显著的提升查询速度，如果你有很多相同的查询和很少改变表结构。   
查看“Qcache\_lowmem\_prunes”状态变量来检查当前值对于你的负载是否足够高。   
NOTE：如果你的表结构经常改变或者，你每次查询都不相同，那么使用查询缓存结果可能不是一个性能改进。    
query\_cache\_size=15M   
  
\# The number of open tables for all threads. Increasing this value   
\# increases the number of file descriptors that mysqld requires.   
\# Therefore you have to make sure to set the amount of open files   
\# allowed to at least 4096 in the variable "open-files-limit" in   
\# section \[mysqld\_safe\]   
所有的线程打开表的数量。    
table\_cache=256   
  
\# Maximum size for internal (in-memory) temporary tables. If a table   
\# grows larger than this value, it is automatically converted to disk   
\# based table This limitation is for a single table. There can be many   
\# of them.   
临时表最大内存大小。如果一个表生长大于这个值，它会自动转换为磁盘，这只是限制单个表，可以有很多个    
tmp\_table\_size=18M   
  
  
\# How many threads we should keep in a cache for reuse. When a client   
\# disconnects, the client's threads are put in the cache if there aren't   
\# more than thread\_cache\_size threads from before.  This greatly reduces   
\# the amount of thread creations needed if you have a lot of new   
\# connections. (Normally this doesn't give a notable performance   
\# improvement if you have a good thread implementation.)   
有很多线程我们应该保持在一个缓存里以便重用。在没有超过超过线程缓存大小之前，当一个客户端断开连接时,把客户端线程放在缓存中。如果你有很多新的连接，那么这大大降低了线程的数量创造需要。（如果你有一个好的改进线程实现方案，那么这个并不会给出一个显著的性能）    
thread\_cache\_size=8   
  
\#\*\*\* MyISAM Specific options   
MYISAM 特殊配置项    
  
\# The maximum size of the temporary file MySQL is allowed to use while   
\# recreating the index (during REPAIR, ALTER TABLE or LOAD DATA INFILE.   
\# If the file-size would be bigger than this, the index will be created   
\# through the key cache (which is slower).   
Mysql临时文件允许使用重建索引(在修复，修改表结构，导入数据文件时)的最大大小。   
如果文件大小会比这个大,该指数将被创建通过键缓存(很慢)    
myisam\_max\_sort\_file\_size=100G   
  
\# If the temporary file used for fast index creation would be bigger   
\# than using the key cache by the amount specified here, then prefer the   
\# key cache method.  This is mainly used to force long character keys in   
\# large tables to use the slower key cache method to create the index.   
如果临时文件用于快速创建索引将比使用键缓存更大，那么键缓存的方法是很好的。这主要用于在比较大型的表使用缓慢的基础上，使用长字符串创建索引。    
myisam\_sort\_buffer\_size=35M   
  
\# Size of the Key Buffer, used to cache index blocks for MyISAM tables.   
\# Do not set it larger than 30% of your available memory, as some memory   
\# is also required by the OS to cache rows. Even if you're not using   
\# MyISAM tables, you should still set it to 8-64M as it will also be   
\# used for internal temporary disk tables.   
在MyISAM表里用于缓存索引块。不要把它设置为大于你的可用内存的30%，因为一些内存还需要由OS缓存行，即使您没有使用MyISAM表,您仍然应该将其设置为8 - 64 m,因为它也会用于内部临时磁盘表    
key\_buffer\_size=8M   
  
\# Size of the buffer used for doing full table scans of MyISAM tables.   
\# Allocated per thread, if a full scan is needed.   
MyISAM表用来做全表扫描的缓冲区大小   
如果需要一个完整的扫描，就分配给每一个线程    
read\_buffer\_size=64K   
read\_rnd\_buffer\_size=256K   
  
\# This buffer is allocated when MySQL needs to rebuild the index in   
\# REPAIR, OPTIMZE, ALTER table statements as well as in LOAD DATA INFILE   
\# into an empty table. It is allocated per thread so be careful with   
\# large settings.   
这个缓冲区是分配给：当MYSQL在需要修理,OPTIMZE,修改表结构语句以及导入加载数据   
到一个空表时重建索引。它是分配给每个线程，所以在大型设置中要小心使用。    
sort\_buffer\_size=256K   
  
  
\#\*\*\* INNODB Specific options \*\*\*   
INNODB特殊项    
INNODB保存数据目录   
innodb\_data\_home\_dir="E:/MySQL Datafiles/"   
  
\# Use this option if you have a MySQL server with InnoDB support enabled   
\# but you do not plan to use it. This will save memory and disk space   
\# and speed up some things.   
\#skip-innodb   
  
\# Additional memory pool that is used by InnoDB to store metadata   
\# information.  If InnoDB requires more memory for this purpose it will   
\# start to allocate it from the OS.  As this is fast enough on most   
\# recent operating systems, you normally do not need to change this   
\# value. SHOW INNODB STATUS will display the current amount used.   
InnoDB存储元数据信息时的额外的内存池。如果需要更多的内存，InnoDB为此它会分配它的操作系统内存。因为在大多数操作系统中，这已经是足够快的了，所以通常不需要修改这个值。SHOW INNODB STATUS会显示当前的使用量    
innodb\_additional\_mem\_pool\_size=2M   
  
\# If set to 1, InnoDB will flush (fsync) the transaction logs to the   
\# disk at each commit, which offers full ACID behavior. If you are   
\# willing to compromise this safety, and you are running small   
\# transactions, you may set this to 0 or 2 to reduce disk I/O to the   
\# logs. Value 0 means that the log is only written to the log file and   
\# the log file flushed to disk approximately once per second. Value 2   
\# means the log is written to the log file at each commit, but the log   
\# file is only flushed to disk approximately once per second.   
如果设置为1，InnoDB会在每个提交磁盘冲洗事务日志,这提供了完整的ACID的行为.   
如果你想安全，并且正在执行一些小操作，你可以设置为0或者2在减少磁盘I / O日志。    
\#innodb\_flush\_log\_at\_trx\_commit=1   
  
\# The size of the buffer InnoDB uses for buffering log data. As soon as   
\# it is full, InnoDB will have to flush it to disk. As it is flushed   
\# once per second anyway, it does not make sense to have it very large   
\# (even with long transactions).   
这个大小用于缓冲InnoDB在缓冲日志数据。一旦它满了，InnoDB会在磁盘上释放它。   
它无论如何都会一秒缓冲一次，这对于非常大的日志是没有意义的。（即使有长事务）    
innodb\_log\_buffer\_size=1M   
  
\# InnoDB, unlike MyISAM, uses a buffer pool to cache both indexes and   
\# row data. The bigger you set this the less disk I/O is needed to   
\# access data in tables. On a dedicated database server you may set this   
\# parameter up to 80% of the machine physical memory size. Do not set it   
\# too large, though, because competition of the physical memory may   
\# cause paging in the operating system.  Note that on 32bit systems you   
\# might be limited to 2-3.5G of user level memory per process, so do not   
\# set it too high.   
InnoDB,不像MyISAM,使用一个缓冲池来缓存的两个索引和行数据。这个磁盘I / O设置的更大，需要访问的数据表更少。在专用的数据库服务器上你可以设置参数高达80%的机器的物理内存大小。不用把它设置的太大，不过，因为在操作系统中竞争的物理内存可能导致内存不够分。注意,32位系统上的你可能被限制在2 - 3.5g的用户级内存，所以不要设置的太高。    
innodb\_buffer\_pool\_size=83M   
  
\# Size of each log file in a log group. You should set the combined size   
\# of log files to about 25%-100% of your buffer pool size to avoid   
\# unneeded buffer pool flush activity on log file overwrite. However,   
\# note that a larger logfile size will increase the time needed for the   
\# recovery process.   
每个日志文件在日志组里的大小。你应该将日志文件大约为25% - -100%缓冲池大小合并来避免不必要的缓冲池的缓冲行为覆盖日志文件。然而，一个大的日志文件将增加复苏过程的所需时间。    
innodb\_log\_file\_size=17M   
  
\# Number of threads allowed inside the InnoDB kernel. The optimal value   
\# depends highly on the application, hardware as well as the OS   
\# scheduler properties. A too high value may lead to thread thrashing.   
InnoDB的内核允许多线程。最高性能取决于高的应用程序、硬件以及操作系统。   
一个很高的值可能导致线程抖动    
innodb\_thread\_concurrency=8   
  
总结：Mysql数据库，分INNODB和MYISAM存储引擎，MYISAM不支持多线程，也就是说，当有一个链接在对某表进行存储操作的时候，会自动锁表。当这个链接操作完成以后，后面的链接才能继续执行存储操作。而INNODB支持多线程，只要不是自己手动锁表，都不是出现类似MYISAM的情况。然后再查询速度上，比如：select count(\*) from table1;，MYISAM要强于INNODB，因为，在表数据存进来的时候，MYISAM的引擎就会自动存储一个该表的总数量等信息，那么查询的时候就会自动从那里取得。一般MYISAM在统计数据时用的比较多，差不多了，我所知有限，如果有错误的地方，请各位提出来。