MySQL数据库优化--SQL_MySQL-Mysql Tutorial-php.cn

Home

Database

Mysql Tutorial

MySQL数据库优化--SQL_MySQL

WBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWB

Jun 01, 2016 pm 01:53 PM

Database optimizationharddiskability

　　1:磁盘寻道能力,以高速硬盘(7200转/秒),理论上每秒寻道7200次.这是没有办法改变的,优化的方法是----用多个硬盘,或者把数据分散存储.
　　
　　2:硬盘的读写速度,这个速度非常的快,这个更容易解决--可以从多个硬盘上并行读写.
　　
　　3:cpu.cpu处理内存中的数据,当有相对内存较小的表时,这是最常见的限制因素.
　　
　　4:内存的限制.当cpu需要超出适合cpu缓存的数据时,缓存的带宽就成了内存的一个瓶颈---不过现在内存大的惊人,一般不会出现这个问题.
　　
　　第二步: (本人使用的是学校网站的linux平台(Linux ADVX.Mandrakesoft.com 2.4.3-19mdk ))
　　
　　1:调节服务器参数
　　
　　用shell>mysqld-help这个命令声厂一张所有mysql选项和可配置变量的表.输出以下信息:
　　
　　possible variables for option--set-variable(-o) are:
　　
　　back_log current value:5 //要求mysql能有的连接数量.back_log指出在mysql暂停接受连接的时间内有多少个连接请求可以被存在堆栈中
　　
　　connect_timeout current value:5 //mysql服务器在用bad handshake(不好翻译)应答前等待一个连接的时间
　　
　　delayed_insert_timeout current value:200 //一个insert delayed在终止前等待insert的时间
　　
　　delayed_insert_limit current value:50 //insert delayed处理器将检查是否有任何select语句未执行,如果有,继续前执行这些语句
　　
　　delayed_queue_size current value:1000 //为insert delayed分配多大的队
　　
　　flush_time current value:0 //如果被设置为非0,那么每个flush_time 时间,所有表都被关闭
　　
　　interactive_timeout current value:28800 //服务器在关上它之前在洋交互连接上等待的时间
　　
　　join_buffer_size current value:131072 //用与全部连接的缓冲区大小
　　
　　key_buffer_size current value:1048540 //用语索引块的缓冲区的大小,增加它可以更好的处理索引
　　
　　lower_case_table_names current value:0 //
　　
　　long_query_time current value:10 //如果一个查询所用时间大于此时间,slow_queried计数将增加
　　
　　max_allowed_packet current value:1048576 //一个包的大小
　　
　　max_connections current value:300 //允许同时连接的数量
　　
　　max_connect_errors current value:10 //如果有多于该数量的中断连接,将阻止进一步的连接,可以用flush hosts来解决
　　
　　max_delayed_threads current value:15 //可以启动的处理insert delayed的数量
　　
　　max_heap_table_size current value:16777216 //
　　
　　max_join_size current value:4294967295 //允许读取的连接的数量
　　
　　max_sort_length current value:1024 //在排序blob或者text时使用的字节数量
　　
　　max_tmp_tables current value:32 //一个连接同时打开的临时表的数量
　　
　　max_write_lock_count current value:4294967295 //指定一个值(通常很小)来启动mysqld,使得在一定数量的write锁定之后出现read锁定
　　
　　net_buffer_length current value:16384 //通信缓冲区的大小--在查询时被重置为该大小
　　
　　query_buffer_size current value:0 //查询时缓冲区大小
　　
　　record_buffer current value:131072 //每个顺序扫描的连接为其扫描的每张表分配的缓冲区的大小
　　
　　sort_buffer current value:2097116 //每个进行排序的连接分配的缓冲区的大小
　　
　　table_cache current value:64 //为所有连接打开的表的数量
　　
　　thread_concurrency current value:10 //
　　
　　tmp_table_size current value:1048576 //临时表的大小
　　
　　thread_stack current value:131072 //每个线程的大小
　　
　　wait_timeout current value:28800 //服务器在关闭它3之前的一个连接上等待的时间
　　
　　根据自己的需要配置以上信息会对你帮助.

Statement

The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn

What are the differences in syntax between MySQL and other SQL dialects?Apr 27, 2025 am 12:26 AM

MySQLdiffersfromotherSQLdialectsinsyntaxforLIMIT,auto-increment,stringcomparison,subqueries,andperformanceanalysis.1)MySQLusesLIMIT,whileSQLServerusesTOPandOracleusesROWNUM.2)MySQL'sAUTO_INCREMENTcontrastswithPostgreSQL'sSERIALandOracle'ssequenceandt

What is MySQL partitioning?Apr 27, 2025 am 12:23 AM

MySQL partitioning improves performance and simplifies maintenance. 1) Divide large tables into small pieces by specific criteria (such as date ranges), 2) physically divide data into independent files, 3) MySQL can focus on related partitions when querying, 4) Query optimizer can skip unrelated partitions, 5) Choosing the right partition strategy and maintaining it regularly is key.

How do you grant and revoke privileges in MySQL?Apr 27, 2025 am 12:21 AM

How to grant and revoke permissions in MySQL? 1. Use the GRANT statement to grant permissions, such as GRANTALLPRIVILEGESONdatabase_name.TO'username'@'host'; 2. Use the REVOKE statement to revoke permissions, such as REVOKEALLPRIVILEGESONdatabase_name.FROM'username'@'host' to ensure timely communication of permission changes.

Explain the differences between InnoDB and MyISAM storage engines.Apr 27, 2025 am 12:20 AM

InnoDB is suitable for applications that require transaction support and high concurrency, while MyISAM is suitable for applications that require more reads and less writes. 1.InnoDB supports transaction and bank-level locks, suitable for e-commerce and banking systems. 2.MyISAM provides fast read and indexing, suitable for blogging and content management systems.

What are the different types of JOINs in MySQL?Apr 27, 2025 am 12:13 AM

There are four main JOIN types in MySQL: INNERJOIN, LEFTJOIN, RIGHTJOIN and FULLOUTERJOIN. 1.INNERJOIN returns all rows in the two tables that meet the JOIN conditions. 2.LEFTJOIN returns all rows in the left table, even if there are no matching rows in the right table. 3. RIGHTJOIN is contrary to LEFTJOIN and returns all rows in the right table. 4.FULLOUTERJOIN returns all rows in the two tables that meet or do not meet JOIN conditions.

What are the different storage engines available in MySQL?Apr 26, 2025 am 12:27 AM

MySQLoffersvariousstorageengines,eachsuitedfordifferentusecases:1)InnoDBisidealforapplicationsneedingACIDcomplianceandhighconcurrency,supportingtransactionsandforeignkeys.2)MyISAMisbestforread-heavyworkloads,lackingtransactionsupport.3)Memoryengineis

What are some common security vulnerabilities in MySQL?Apr 26, 2025 am 12:27 AM

Common security vulnerabilities in MySQL include SQL injection, weak passwords, improper permission configuration, and unupdated software. 1. SQL injection can be prevented by using preprocessing statements. 2. Weak passwords can be avoided by forcibly using strong password strategies. 3. Improper permission configuration can be resolved through regular review and adjustment of user permissions. 4. Unupdated software can be patched by regularly checking and updating the MySQL version.

How can you identify slow queries in MySQL?Apr 26, 2025 am 12:15 AM

Identifying slow queries in MySQL can be achieved by enabling slow query logs and setting thresholds. 1. Enable slow query logs and set thresholds. 2. View and analyze slow query log files, and use tools such as mysqldumpslow or pt-query-digest for in-depth analysis. 3. Optimizing slow queries can be achieved through index optimization, query rewriting and avoiding the use of SELECT*.

See all articles