SQL SERVER 查询性能优化分析事务与锁（一）-Mysql Tutorial-php.cn

Home

Database

Mysql Tutorial

SQL SERVER 查询性能优化分析事务与锁（一）

WBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWB

Jun 07, 2016 pm 05:44 PM

serveraffairsoptimizationanalyzeperformanceInquire

一、使用工具观察与分析数据库中锁信息此现象类似高速公路上有大量的车辆进入，而在收费口却只开了一个、二个。造成收费口的车辆拥堵，而后方却又有大量的车辆涌入，这时所有的车辆都会慢下来，驾驶员彼此再减速刹车互相等待，最后导致后方的车子会被迫停下

一、使用工具观察与分析数据库中锁信息

此现象类似高速公路上有大量的车辆进入，而在收费口却只开了一个、二个。造成收费口的车辆拥堵，而后方却又有大量的车辆涌入，这时所有的车辆都会慢下来，驾驶员彼此再减速刹车互相等待，最后导致后方的车子会被迫停下来，形成高速公路停车场。（就如第一次节假日高速公路免费通行那天，即2012年国庆高速公路大堵车的情形，高速公路成停车场）

如果数据库中在设计时，设计不当，设计有过热数据表时，也就是所有应用程序的访问行为都跟某张数据表有关，香港空间，美国服务器，甚至集中访问某些字段，这就像前述例子中的所有车辆都在同一地点（高速公路收费口）驶上高速公路。这时耗时与快速的两种访问行为混合在一起，就像大货车与小轿车一起争车道，很容易发生彼此锁定，形成死锁而动弹不得的情况。

第一步，使用SQL SERVER提供图形界面工具观察哪些进程被加锁。

图1

2) SQL SERVER 2005 的Microsoft SQL Server Management Studio提供图形界面工具中的信息不会自动更新。刷新“活动监视器”界面中的内容，需要手动使用鼠标点击上方工具栏上的“刷新”按钮。如下图2，是未点击“刷新”按钮之前的显示的内容，图3，是点击“刷新”按钮之后的，显示的内容。

　　　　　　　　图2

　　　　　　　　图3

2） SQL SERVER 2005中可以通过下图中的设置，美国服务器，设置成自动更新的时间周期。如下图4。

图4

按对象分类的锁”两个标记进行观察。

活动监视器--》按进程分类的锁

如下图5。

图5

活动监视器--》按对象分类的锁

如下图6。

图6

接下来说一下SQL SERVER 2008中的活动监视器的使用：

1) SQL SERVER 2008的活动监视器就不在管理中了。一、点击工具栏上的图标。二、使用右键点击数据库服务器，如下图7。然后都会出现如图8的图形界面工具。

图7

图8

，然后选择活动监视器获取新的实例信息所用的间隔。

图9

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

How do you handle database upgrades in MySQL?Apr 30, 2025 am 12:28 AM

The steps for upgrading MySQL database include: 1. Backup the database, 2. Stop the current MySQL service, 3. Install the new version of MySQL, 4. Start the new version of MySQL service, 5. Recover the database. Compatibility issues are required during the upgrade process, and advanced tools such as PerconaToolkit can be used for testing and optimization.

What are the different backup strategies you can use for MySQL?Apr 30, 2025 am 12:28 AM

MySQL backup policies include logical backup, physical backup, incremental backup, replication-based backup, and cloud backup. 1. Logical backup uses mysqldump to export database structure and data, which is suitable for small databases and version migrations. 2. Physical backups are fast and comprehensive by copying data files, but require database consistency. 3. Incremental backup uses binary logging to record changes, which is suitable for large databases. 4. Replication-based backup reduces the impact on the production system by backing up from the server. 5. Cloud backups such as AmazonRDS provide automation solutions, but costs and control need to be considered. When selecting a policy, database size, downtime tolerance, recovery time, and recovery point goals should be considered.

What is MySQL clustering?Apr 30, 2025 am 12:28 AM

MySQLclusteringenhancesdatabaserobustnessandscalabilitybydistributingdataacrossmultiplenodes.ItusestheNDBenginefordatareplicationandfaulttolerance,ensuringhighavailability.Setupinvolvesconfiguringmanagement,data,andSQLnodes,withcarefulmonitoringandpe

How do you optimize database schema design for performance in MySQL?Apr 30, 2025 am 12:27 AM

Optimizing database schema design in MySQL can improve performance through the following steps: 1. Index optimization: Create indexes on common query columns, balancing the overhead of query and inserting updates. 2. Table structure optimization: Reduce data redundancy through normalization or anti-normalization and improve access efficiency. 3. Data type selection: Use appropriate data types, such as INT instead of VARCHAR, to reduce storage space. 4. Partitioning and sub-table: For large data volumes, use partitioning and sub-table to disperse data to improve query and maintenance efficiency.

How can you optimize MySQL performance?Apr 30, 2025 am 12:26 AM

TooptimizeMySQLperformance,followthesesteps:1)Implementproperindexingtospeedupqueries,2)UseEXPLAINtoanalyzeandoptimizequeryperformance,3)Adjustserverconfigurationsettingslikeinnodb_buffer_pool_sizeandmax_connections,4)Usepartitioningforlargetablestoi

How to use MySQL functions for data processing and calculationApr 29, 2025 pm 04:21 PM

MySQL functions can be used for data processing and calculation. 1. Basic usage includes string processing, date calculation and mathematical operations. 2. Advanced usage involves combining multiple functions to implement complex operations. 3. Performance optimization requires avoiding the use of functions in the WHERE clause and using GROUPBY and temporary tables.

An efficient way to batch insert data in MySQLApr 29, 2025 pm 04:18 PM

Efficient methods for batch inserting data in MySQL include: 1. Using INSERTINTO...VALUES syntax, 2. Using LOADDATAINFILE command, 3. Using transaction processing, 4. Adjust batch size, 5. Disable indexing, 6. Using INSERTIGNORE or INSERT...ONDUPLICATEKEYUPDATE, these methods can significantly improve database operation efficiency.

Steps to add and delete fields to MySQL tablesApr 29, 2025 pm 04:15 PM

In MySQL, add fields using ALTERTABLEtable_nameADDCOLUMNnew_columnVARCHAR(255)AFTERexisting_column, delete fields using ALTERTABLEtable_nameDROPCOLUMNcolumn_to_drop. When adding fields, you need to specify a location to optimize query performance and data structure; before deleting fields, you need to confirm that the operation is irreversible; modifying table structure using online DDL, backup data, test environment, and low-load time periods is performance optimization and best practice.

See all articles