In the application development of the database, there is often a contradiction between performance and expense. Take the author's problems encountered in the development of stock market query and trading systems. To record more than 1,000 stocks in real time, the market data is updated every minute, in response to a large number of concurrent users' data query requests. Considering the cost / performance and easy maintenance, the system requires implementation in a hardware and software environment based on PC servers and Windows NT platforms. Start, we use the MS SQL Server 6.5 as the database system, developed the front end of the access database with Visual C 6.0, applying the ODBC data interface, after the large amount of database configuration and program optimization, find still cannot meet performance requirements. After using SQL Server's DB-library interface, bypass the ODBC interpretation layer, you can update market data 30 times a second, and support 20-30 concurrent users to conduct market queries, basically meet the requirements (single PC server, single PII350 CPU, memory 128m, SCSI hard disk). Is it possible to further improve the performance and load capacity of the system? After analysis, the database server is the bottleneck of the system. Of course, a system platform for UNIX servers large databases can be used, but its development, operation, and maintenance costs are several times higher than the cost of microcomputer Windows NT platform. In the development of other systems, we have often encountered such contradictions. How to build large capacity, high efficiency, easy maintenance, and cost-effective database systems on the microcomputer platform?
---- Investigate domestic microcomputer platform database applications, typical, such as NetEase distributed mail system, using freebsd mysql platform, its capacity, load capacity, and response speed are excellent. The author reviews the relevant documentation of MySQL, found that mysql is a very good database system in GNU software (ie OpenSource Free Software), which is fully compliant with SQL92 (Entry Level) and ODBC (Level 0-2) specification, in line with POSIX specifications A very efficient relational database management system is implemented on the system. According to the documentation provided by MySQL, its data operation is most efficient in all databases, Benchmark as follows:
Reading 2000000 Rows by Index
Database second
MySQL 367
MySQL_ODBC 464
DB2_ODBC 1206
Informix_odbc 121126
MS-SQL_ODBC 1634
Oracle_odbc 20800
SOLID_ODBC 877
Sybase_odbc 17614
INSERTING (350768) ROWS
Database second
MySQL 381
MySQL_ODBC 619
DB2_ODBC 3460
Informix_odbc 2692
MS-SQL_ODBC 4012
Oracle_odbc 11291
SOLID_ODBC 1801
Sybase_odbc 4802
(Run on the Same NT 4.0 Machine)
---- From Mysql's Benchmark, you can see that MySQL performance is very outstanding (of course, the Test's Mysql system may be optimized, and the measured data may be for mysql selection), and MySQL provides support for Windows NT. . Can Windows NT MYSQL become ideal for building high-performance database applications? The author has rewritten the program with MySQL data interface. After a period of operation, it proves that mysql is indeed efficient and stable database, which is ideal for building large capacity, high efficiency, easy maintenance, cost-effective database application systems. The experience of mysql installation, operation, and development is shared with you. Second, mysql installation and operation
---- First from http://www.mysql.com/ (domestic users can download the mysql execution code and source code) from http://www.freecode.com.cn/mirror/mysql/. Note that Windows NT users want to select the execution code under NT, I downloaded mySQL-SHAREWARE-3.22.32-WIN.ZIP. After unpacking, execute Setup, press the screen prompt to complete the installation.
---- Copy the My-Example.cnf to c: my.cnf in the MySQL root directory (C: mysql), edit my.cnf (if the root directory of MySQL is c: mysql, you can not Change my.cnf). In the Console window of the NT, enter the execution directory of MySQL (C: mysqlbin), execute
---- C: mysqlbinmysqld-shareware --Standalone
---- The database engine of MySQL is started. Open another NT console window, execute
---- C: mysqlbinmysql MySQL
---- Establish a database connection, after the "mysql>" prompt, execute
---- MySQL> Delete from user where host = 'localhost' and user = '';
---- MySQL> quit
---- Delete all unauthorized users.
---- then execute
---- C: mysqlbinmysqladmin reload
---- C: mysqlbinmysqladmin -u root password your_password
---- Among them, your_password is the password of the database administrator you choose, and must be kept safely.
---- If you have to connect to the database as an administrator each time, edit C: my.cnf, join in the [Client] segment:
User = root
Password = your_password
If you want to stop MySQL's database engine, you can execute
C: mysqlbinmysqladmin -u = root -p shutdown
After prompted to enter the administrator password, MySQL's database engine stops.
Third, the development of mysql client applications
---- MySQL provides a rich data interface API, including C, C , Perl, PHP, Python, TCL, etc. API and JDBC, ODBC interface. For performance, we use the MySQL C API to develop. Now use the Visual C environment as an example to make a brief introduction.
---- Create "C: MySQLINCLUDE" to the included path (add / i "d: mysqlinClude" in Project Options). New main.c file, the main body is as follows: #include
#include
#include
#include
Int main (int Argc, char * argv [])
{
Char sztargetdsn [] = "TEST";
Char szsqltext [500] = "";
Char aszflds [25] [25];
Mysql * mydata;
Mysql_res * res;
Mysql_field * fd;
Mysql_row rotion;
INT I, J, K;
Bool bcreate = true;
IF ((MyData = mysql_init))))
// Initialization data structure
&& mysql_real_connect (MyData, Null,
//Connect to the database
"root", "Your_Password", SZTARGETDSN,
MySQL_Port, NULL, 0))
{
IF (BCREATE)
{
Sprintf (Szsqltext, // Construct SQL statement
"CREATE TABLE MYTABLE"
// Newly built a table
"(Time DateTime, S1 Char (6),"
"S2 CHAR (11), S3 INT, S4 INT");
IF (MySQL_QUERY (MyData, Szsqltext))
/ / Execute SQL statement
{// Execute SQL statement error
Errlog ("can't create table";
MySQL_Close (MyData);
Return False;
}
}
Sprintf (Szsqltext,
Insert InTo MyTable
/ / Insert data into the table
VALUES ('2000-3-10 21:01:30', "
// Notes the format of the time
"'Test', 'MySQLTEST', 2000, 3)");
IF (MySQL_QUERY (MyData, Szsqltext))
{// Execute SQL statement error
Errlog ("Can't Insert Data Table");
MySQL_Close (MyData);
Return False;
}
Sprintf (Szsqltext, "Select * from myTable");
IF (MySQL_QUERY (MyData, Szsqltext))
/ / Data search
{
// Execute SQL statement error
MySQL_Close (MyData);
Return False;
}
Else
{
Res = mysql_store_result (mydata);
/ / Get the results of the query
i = (int) mysql_num_rows (res);
// Number of effective records
Printf ("Query:% SN% LD Records Found:
n ", szsqltext, i);
For (i = 0; fd = mysql_fetch_field (res); i )
STRCPY (ASZFLDS [I], FD-> NAME);
/ / Get the name of each field
For (i = 1; row = mysql_fetch_row (res);)
// read each record in turn
{j = mysql_num_fields (res);
// Number of fields in the record
Printf ("Record #% LD: -N", i );
For (k = 0; k / / Output value of each field Printf ("FLD #% D (% s):% SN", K 1, Aszflds [K], (((row [k] == null) || (! Strlen (row [k])))))))? "NULL": Row [k])); Puts ("================================ n"); } MySQL_Free_Result (res); } } Else {// Connect the database error Errlog ("Can't Connect To The MySQL Server); MySQL_Close (MyData); Return False; } MySQL_Close (MyData); Return True; } ---- For several functions, for details, detailed instructions, please refer to the mysql document: ---- 1. MySQL * mysql_init (mysql * mysql) ---- Initialize a data structure of a MySQL, preparing for Mysql_Real_Connect (). The parameter mysql is a pointer to this structure. If mysql is NULL, new and initialize a MySQL data structure. The newly built structure will be released in MySQL_Close (). ---- If successful, return a pointer to the initialized mysql data structure, otherwise returns NULL. ---- 2. MySQL * mysql_real_connect (mysql * mysql, const char * HOST, ---- Const Char * User, Const Char * Passwd, Const Char * DB, ---- Unsigned Int port, const char * unix_socket, unsigned int client_flag ---- Establish a connection with the MySQL database engine. Before performing a further data operation, you must ensure that mysql_real_connect () successfully returns. ---- Parameter mysql is the return value of mysql_init (); ---- Parameter Host is the TCP / IP host name of the machine running the MySQL database engine. If NULL is default, "localhost" is default. ---- Parameter USER and Passwd are legitimate users and passwords for the MySQL database; ---- Parameter DB is the database name connection; ---- Parameter port, unix_socket, and client_flag generally take default. ---- 3. Int MySQL_Query (mysql * mysql, const char * query) ---- Execute the SQL statement in the query string, Query must end with 0. If successful, return 0. ---- 4. MySQL_RES * MySQL_Store_Result (mysql * mysql) ---- Returns the results of SELECT, SHOW, DESCRIBE, EXPLAIN and other statements. The function created a data structure of MySQL_RES and stores the result in this structure. Returns the empty data set if the query does not match the result. After processing the result set, mysql_free_Result () must be called. ---- If an error, returns NULL, otherwise the pointer to the mysql_RES structure is returned. ---- 5. MySQL_ROW MYSQL_FETCH_ROW (mysql_res * result) ---- Retrieve the next record in the result set, if there is no record or error, return null. The number of fields in a record can be obtained with mysql_num_fields (Result), and the values of each field can be accessed with Row [0] to the array of ROW [mysql_num_fields (result) -1]. ---- In the link options of the project, add C: mysqlliblibmysql.lib's interface library, copy libmysql.dll to the system directory of the operating system (C: WinntSystem32), you can compile it. ---- Here, a simple database application is developed. Of course, MySQL has a set of functional APIs, you can view the documentation. In addition, if you need a cross-platform graft, you can consider using MySQL ODBC interfaces. You can configure MySQL ODBC connection properties, you can also download myodbc-2.50.29-nt.zip toolkit to configure. Fourth, summary ---- The author built high-performance, high-stable database systems on Windows NT in WINDOWS NT. In this way, you can use Windows NT and Visual C 's friendly interface, and you can get MYSQL power. Practice has proved that this program is low (don't forget Mysql to be free), high efficiency, short development cycle, running stability (MySQL stability is highly evaluated in sites such as Yahoo, 163, 263.). ---- Currently, my stock market query and trading system have received 70-100 stock market data per second, while responding to the performance of data queries in more than 50 concurrent users, and the system development, operating costs are substantially reduce. If you are working with the database efficiency on the PC, try Mysql. Author: Li Wei Qing Source: PHP Yi Station
