High-performance database application development based on mysql (using C connection mysql)
Sonymusic
First, the selection of high performance databases
---- In the application development of the database, there is often a contradiction between performance and cost. With the author in developing stock market
The problem encountered in the situation inquiry and trading system is an example, and it is necessary to update more than 1,000 stocks in real time.
While affecting data query requests in response to a large number of concurrent users. Considering the cost / performance and easy maintenance, the system is
Requirements in a hardware and software environment based on a PC server and a Windows NT platform. Start, we use MS SQL
Server 6.5 As a database system, use Visual C 6.0 to develop the front-end of the access database, apply ODBC
Data interfaces, after a large amount of database configuration and program optimization, finding still cannot meet performance requirements. Rear
With SQL Server's DB-library interface, bypass the ODBC interpretation layer, you can update market data 30 times per second, the same
Support for 20-30 concurrent users to conduct market queries, basically meet the requirements (single PC server, single PII3
50 CPU, memory 128m, SCSI hard disk). Is it possible to further improve the performance and load capacity of the system? through
After analysis, the database server is the bottleneck of the system. Of course, you can use the system of Unix server large databases.
Platform, but its development, operation, maintenance cost is several times higher than the cost of the microcomputer Windows NT platform. we are at
In the development of some other systems, this contradiction is often encountered. How to build a large capacity on the microcomputer platform, efficient
Rate, easy to maintain, cost-effective database systems?
---- Investigate domestic microcomputer platform database application systems, typically distributed mail systems such as Netease,
The platform of FreeBSD MySQL, its capacity, load capacity, and response speed are excellent. The author is appreciated by MySQL
Related documents, found that mysql is a very good database system in GNU software (ie OpenSource Free Software),
It is fully compliant with SQL92 (Entry Level) and ODBC (Level 0-2) specification, in line with POSIX specification
A very efficient relational database management system is implemented on the system. According to the documentation provided by MySQL, its data operation
It is the 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)
---- You can see from MySQL Benchmark, MySQL performance is very outstanding (of course, the Test Mysql system)
It may be optimized, and the measured data may be selected by mysql), and MySQL provides Windows NT.
stand by. 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 is proved that mysql is indeed a efficient and stable database.
It is ideal for building large-capacity, high efficiency, easy maintenance, and cost-effective database applications. The installation of MySQL is now
, The experience of running and developing is shared with everyone.
Second, mysql installation and operation
---- First from http://www.mysql.com/ (domestic users can be from http://www.freecode.com.cn/m
Irror / mysql /) Download Mysql's execution code and source code. Note that Windows NT users should choose the execution of NT
Down code, I downloaded mySQL-SHAREWARE-3.22.32-WIN.ZIP. After unpacking, execute Setup, press the screen
Mark now can be installed.
---- Copy My-Example.cnf to C: /my.cnf in the mysql root directory (C: / mysql), prompt in the file
Edit My.cnf (if the root directory of MySQL is C: / MySQL, you will not change my.cnf). At the console window in NT
In the execution directory (C: / mysql / bin), execution of MySQL
---- C: / mysql / bin / mysqld-shareware --standalone
---- The database engine of MySQL is started. Open another NT console window, execute
---- C: / mysql / bin / mysql 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: / mysql / bin / mysqladmin reload
---- C: / mysql / bin / mysqladmin -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] section.
:
User = root
Password = your_password
If you want to stop MySQL's database engine, you can execute
C: / mysql / bin / mysqladmin -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 Visual C
The environment is an example, making a brief introduction.
---- Add a Win32 Console Application's Project, add "C: / MySQL / Include" to the editor
The translation is included in the path (add / i "in Project Options D: /MYSQL/InClude&quounter.cgol.net 魈 魈 缦 缦?
#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:% S / N% 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 Printf ("FLD #% D (% S):% S / N", 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 type of data 峁 衜 衜 衜 衜 衜 衜 衜 做 做 准 准 准 做 做 准. 做. 做 做 做 做.. 做 做 做 做 做parameter MySQL is a pointer to this structure if mysql is NULL 蛐 蛐 跏 跏 鯩 鯩 鯩 鯩 数据 数据 数据 数据 数据 数据 数据 数据 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. MYSQL_RE must be guaranteed before performing further data operations Al_Connect () successfully returned. ---- 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, such as NULL, default "LO Calhost "; ---- 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. Function New Mysql_ The data structure of the RES is stored 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. A field in a record The number can be obtained with mysql_num_fields (result), the value of each field can be used with Row [0] to ROW [mysql_nu An array of m_fields -1 is accessed. ---- In the link option of the project, join the interface library of c: / mysql/lib/libmysql.lib, put libmysql.d LL is copied to the system's system directory (C: / WinNT / System32), you can compile it. ---- Here, a simple database application is developed. Of course, MySQL has a set of functions of a rich AP I, you can view the documentation. In addition, if you need a cross-platform graftability, you can consider using MySQL ODBC interface. . You can configure MySQL's ODBC connection properties, you can also download myodbc-2.50.29-nt.zip toolkit. . Fourth, summary ---- The author built high-performance, high-stable database systems on Windows NT in WINDOWS NT. In this way, it can be To take advantage of Windows NT and Visual C , you can get MYSQL powerful features. Practice proves, This kind of program cost is low (don't forget MySQL is free), high efficiency, short development cycle, stable operation (mysql Stability Get a very high evaluation in sites such as Yahoo, 163, 263. ---- Currently, my stock market query and trading system have received 70-100 stocks per second. According to the performance of the data query in response to 50 concurrent users, the system is developed, the cost of operation is significantly reduced. . If you are working with the database efficiency on the PC, try Mysql.
