An introduction to database architecture

1. Introduction to MySQL

Overview

• MySQL is a relational database management system developed by the Swedish MySQL AB company and currently belongs to Oracle.

• MySQL is a relational database management system that stores data in different tables instead of putting all data in one big warehouse, which increases speed and improves flexibility.

• Mysql is open source, so you don’t need to pay extra.

• Mysql supports large databases. Can handle large databases with tens of millions of records.

• MySQL uses the standard SQL data language form.

• Mysql can be used on multiple systems and supports multiple languages. These programming languages ​​include C, C++, Python, Java, Perl, PHP, Eiffel, Ruby and Tcl, etc.

• Mysql has good support for PHP, which is currently the most popular web development language.

• MySQL supports large databases and data warehouses with 50 million records. The 32-bit system table file can support a maximum of 4GB, and the 64-bit system supports a maximum table file of 8TB.

• Mysql can be customized and adopts the GPL protocol. You can modify the source code to develop your own Mysql system.

Advanced MySQL involves knowledge

• mysql kernel

• sql optimization siege lion

• Optimization of mysql server

• Various parameter constant settings

• Query statement optimization

• Master-slave replication

• Software and hardware upgrade

• ##Disaster recovery backup

• sql programming

• Complete mysql optimization requires a deep foundation. Large companies even have dedicated DBAs to write the above

• This time I installed MySQL 5.5, and the installation environment is CentOS 6.5

• version download Address official website download address

• Download

  MySQL-Client andMySQL-Server

• downloads.skysql.com/archives/mysql-5.5/MySQL-server-5.5.16-1.rhel4.i386.rpm

• downloads.skysql.com/archives/mysql- 5.5/MySQL-client-5.5.16-1.rhel4.i386.rpm

• downloads.skysql.com/archives/mysql-5.5/MySQL-devel-5.5.16-1 .rhel4.i386.rpm

• Check whether MySQL is installed on the current system

##Query command:
• rpm -qa|grep -i mysql

Delete command:
• rpm -e --nodeps RPM package full name

Install mysql server (
• Pay attention to the tips

  )

• 
  Set password prompt

Install mysql client
View the mysql user and mysql group created during MySQL installation
• # cat /etc/passwd | grep mysql

• cat /etc/group | grep mysql

Start + stop mysql service
##View character set

• Modify the character set and modify the previously copied configuration file. (Detailed follow-up code)

• MySQL installation location

• ##show variables like 'character%';

• show variables like '%char%';
• ##Character set

  
  

  The default is that both the client and the server use latin1, so the characters will be garbled.

• View the installation directory under linux

  ps -ef|grep mysql

• Copy the current 5.5 version

  :
cp /usr/share/mysql/my-huge.cnf /etc/my.cnf

• ##5.6 version

  cp /usr/share/mysql/my-default.cnf /etc/my.cnf

• chkconfig mysql on Set automatic startup

• # chkconfig --list | grep mysql Check whether automatic startup is set

• # /etc/init .d/mysql start

• # /etc/init.d/mysql stop

• or

• #service mysql start

• service mysql stop

• View MySQL start and stop status: # ps -ef | grep mysql

• Start and stop operations:

Set up MySQL self-starting service
Modify the configuration file location
Modify the character set and data storage path

MySQL installation location

[client]
#password = your_password
port = 3306
socket = /var/lib/mysql/mysql.sock

# 这一行需要设置字符集
default-character-set=utf8

# The MySQL server
[mysqld]
port = 3306

# 还有这三行
character_set_server=utf8
character_set_client=utf8
collation-server=utf8_general_ci

socket = /var/lib/mysql/mysql.sock
skip-external-locking
key_buffer_size = 384M
max_allowed_packet = 1M
table_open_cache = 512
sort_buffer_size = 2M
read_buffer_size = 2M
read_rnd_buffer_size = 8M
myisam_sort_buffer_size = 64M
thread_cache_size = 8
query_cache_size = 32M
# Try number of CPU's*2 for thread_concurrency
thread_concurrency = 8

[mysql]
no-auto-rehash
# 还有这一行
default-character-set=utf8

3. Mysql configuration file

Main configuration file

• Binary log log-bin

• Master-slave replication

• 
  

  
  

• ##Error log log-error

• The default is turned off, recording serious warnings and error messages, every Startup and shutdown details, etc.

• Query log log

• is turned off by default and records query sql statements. If it is turned on, it will reduce the overall performance of mysql. , because recording logs also consumes system resources

• data files

• windows

• Linux:

• #You can choose many libraries under the D:\devSoft\MySQLServer5.5\data directory

• Default path

  #cd /var/lib/mysql/

• Look at all the libraries in the current system and then go in

  #ls -1F | grep ^d

• Two systems

• frm file: Stores the table structure

• ##myd file:

  Store table data

• myi file:

  Store table index

How to configure
Windows: my.ini file
Linux: /etc/my.cnf file

4. Introduction to Mysql logical architecture

Overview

Compared with other databases, MySQL is a little different. Architecture can be applied and work well in many different scenarios. Mainly reflected in the architecture of the storage engine,
• the plug-in storage engine architecture separates query processing from other system tasks and data storage and extraction

  . This architecture allows the selection of appropriate storage engines based on business needs and actual needs.

The data storage layer mainly stores data on the file system running on the raw device and completes the interaction with the storage engine.
Storage engine layer, the storage engine is really responsible for the storage and retrieval of data in MySQL, and the server communicates with the storage engine through API. Different storage engines have different functions, so we can choose according to our actual needs. MyISAM and InnoDB will be introduced later
The second-layer architecture mainly completes most of the core service functions, such as SQL interfaces, and completes cached queries, SQL analysis and optimization, and some built-in functions. implement. All cross-storage engine functions are also implemented in this layer, such as procedures, functions, etc. At this layer, the server will parse the query and create the corresponding internal parse tree, and complete the corresponding optimization such as determining the order of the query table, whether to use indexes, etc., and finally generate the corresponding execution operation. If it is a select statement, the server will also query the internal cache. If the cache space is large enough, it can greatly improve system performance in an environment that handles a large number of read operations.
The top layer is some client and connection services, including local sock communication and most tcp/ip-like communication based on client/server tools. It mainly completes some connection processing, authorization authentication, and related security solutions. The concept of thread pool is introduced on this layer to provide threads for clients that securely access through authentication. SSL-based secure links can also be implemented on this layer. The server also verifies the operating permissions it has for each client that securely accesses it.

• 
  

  
  

##1. Connection layer
2. Service layer
3. Engine layer
4. Storage layer
Query description

The mysql client establishes a connection with the mysql server through the protocol, sends the query statement, and first checks the query cache. If there is a hit, the result is returned directly, otherwise the statement is parsed

• There is a series of preprocessing, such as checking whether the statement is written correctly, and then query optimization (such as whether to use index scanning, if it is an impossible condition, terminate early), and generate queries Plan, then the query engine starts, starts executing the query, calls the API from the underlying storage engine to obtain the data, and finally returns it to the client. How to store data and how to retrieve data are all related to the storage engine.

• Then, mysql uses the BTREE index by default, and a general direction is that no matter how you toss sql, at least for now, mysql only uses at most one index in the table.

5. Mysql storage engine

• View command

• show variables like '%storage_engine%';

• mysql> show engines;

• View What storage engine does the current MySQL provide?

• Depends on the current default storage engine of your MySQL:

• 
  

  Default storage engine

• MyISAM and InnoDB

• 
  

  Comparison of two engines

• For Alibaba and Taobao Which

• 
  

  
  

• ##Percona for MySQL database server Improvements have significantly improved functionality and performance compared to MySQL. This version improves the performance of InnoDB under high load conditions, provides DBAs with some very useful performance diagnostic tools, and has more parameters and commands to control server behavior.

• The company has created a new storage engine called

  xtradb which can completely replace innodb , and do better in performance and concurrency,

• Most of Alibaba's mysql databases actually use the prototype of percona to be modified.

【Related recommendations】
1.

Free mysql online video tutorial

2.

MySQL latest manual tutorial

3.

Boolean Education Yan Shiba mysql introductory video tutorial

The above is the detailed content of An introduction to database architecture. For more information, please follow other related articles on the PHP Chinese website!