web开发性能优化---数据库篇
1、 查询出的数据量过大(可以采用多次查询,其他的方法降低数据量),尽量采取分页查询数据 2、锁或者死锁(这也是查询慢最常见的问题,是程序设计的缺陷) 3、返回了不必要的行和列 用OR的字句可以分解成多个查询,并且通过UNION链接多个查询。它们的速度只
1、 查询出的数据量过大(可以采用多次查询,其他的方法降低数据量),尽量采取分页查询数据
2、锁或者死锁(这也是查询慢最常见的问题,是程序设计的缺陷)
3、返回了不必要的行和列
用OR的字句可以分解成多个查询,并且通过UNION链接多个查询。它们的速度只与是否使用索引有关,如果查询需要用到联合索引,用UNION all执行的效率更高。
4、如果是使用like进行查询的话,简单的使用index是不行的,但是全文索引,耗空间。 like 'a%' 使用索引 like '%a' 不使用索引用 like '%a%' 查询时,查询耗时和字段值总长度成正比,所以不能用CHAR类型,而是VARCHAR。对于字段的值很长的建全文索引。
5、 尽量将数据的处理工作放在服务器上,减少网络的开销,如使用存储过程。存储过程是编译、优化过,并且被组织到一个执行规划里,且存储在数据库中的SQL语句(存储过程是数据库服务器端的一段程序),是控制流语言的集合,速度当然快。
6、将需要查询的结果预先计算好放在表中,查询的时候再Select。这在SQL7.0以前是最重要的手段。例如计算商品购买小计计算。
7、没有必要时不要用DISTINCT和ORDER BY,这些动作可以改在客户端执行。它们增加了额外的开销。这同UNION和UNION ALL一样的道理。
8、一次更新多条记录比分多次更新每次一条快,就是说批处理好
9、用临时表,尽量用结果集和Table类性的变量来代替它,Table 类型的变量比临时表好
10、数据库设计:数据库内所有表结构均添加索引
调整原因:
近日数据库压力很大,经查有些大数据量表的查询速度很慢,导致数据库服务器CPU一直持续90%-100%,将这些表添加索引后,CPU很快变正常。
根据查询条件,建立索引,优化索引、优化访问方式,限制结果集的数据量。注意填充因子要适当(最好是使用默认值0)。索引应该尽量小,使用字节数小的列建索引好(参照索引的创建),不要对有限的几个值的字段建单一索引如性别字段
11、 将大数据表做分库、分区处理:
具体操作如下:
1)、将大数据表与主数据库分离,单独新建一个数据库,然后将这些表做分区;
2)、将数据插入到消息队列内,后台利用windows计划任务执行(5分钟执行一次)C#控制台程序将消息队列内的数据批量(消息队列内有50000条记录,一次性插入到数据表内)插入到相应的数据表内;
调整原因:
例如:用户访问日志,每次用户访问一个页面的时候我们之前的操作是直接将数据插入数据库,这样做对数据库的访问及操作太大,严重影响其他数据插入、查询的效率,利用分库、分区、消息队列完成此操作的好处是用户访问页面的时候不直接对数据库操作,而是在消息队列内积累一定数量的数据后批量插入数据库,只执行一次数据库操作,而且因为数据库分离的原因,对其他的查询及插入不会有影响;
本文为个人经实际工作经验和收集总结整理,写得不到之处请给出宝贵意见,谢谢。

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

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.

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*.

To monitor the health and performance of MySQL servers, you should pay attention to system health, performance metrics and query execution. 1) Monitor system health: Use top, htop or SHOWGLOBALSTATUS commands to view CPU, memory, disk I/O and network activities. 2) Track performance indicators: monitor key indicators such as query number per second, average query time and cache hit rate. 3) Ensure query execution optimization: Enable slow query logs, record and optimize queries whose execution time exceeds the set threshold.

The main difference between MySQL and MariaDB is performance, functionality and license: 1. MySQL is developed by Oracle, and MariaDB is its fork. 2. MariaDB may perform better in high load environments. 3.MariaDB provides more storage engines and functions. 4.MySQL adopts a dual license, and MariaDB is completely open source. The existing infrastructure, performance requirements, functional requirements and license costs should be taken into account when choosing.

MySQL uses a GPL license. 1) The GPL license allows the free use, modification and distribution of MySQL, but the modified distribution must comply with GPL. 2) Commercial licenses can avoid public modifications and are suitable for commercial applications that require confidentiality.

The situations when choosing InnoDB instead of MyISAM include: 1) transaction support, 2) high concurrency environment, 3) high data consistency; conversely, the situation when choosing MyISAM includes: 1) mainly read operations, 2) no transaction support is required. InnoDB is suitable for applications that require high data consistency and transaction processing, such as e-commerce platforms, while MyISAM is suitable for read-intensive and transaction-free applications such as blog systems.

In MySQL, the function of foreign keys is to establish the relationship between tables and ensure the consistency and integrity of the data. Foreign keys maintain the effectiveness of data through reference integrity checks and cascading operations. Pay attention to performance optimization and avoid common errors when using them.


Hot AI Tools

Undresser.AI Undress
AI-powered app for creating realistic nude photos

AI Clothes Remover
Online AI tool for removing clothes from photos.

Undress AI Tool
Undress images for free

Clothoff.io
AI clothes remover

Video Face Swap
Swap faces in any video effortlessly with our completely free AI face swap tool!

Hot Article

Hot Tools

VSCode Windows 64-bit Download
A free and powerful IDE editor launched by Microsoft

MinGW - Minimalist GNU for Windows
This project is in the process of being migrated to osdn.net/projects/mingw, you can continue to follow us there. MinGW: A native Windows port of the GNU Compiler Collection (GCC), freely distributable import libraries and header files for building native Windows applications; includes extensions to the MSVC runtime to support C99 functionality. All MinGW software can run on 64-bit Windows platforms.

EditPlus Chinese cracked version
Small size, syntax highlighting, does not support code prompt function

SAP NetWeaver Server Adapter for Eclipse
Integrate Eclipse with SAP NetWeaver application server.

Dreamweaver Mac version
Visual web development tools
