MySQL Database system

I. Overview of the database

• The database is a software running on the system, used to store the user's personal information, the user's game information, etc.;
• A database is a collection of multiple tables, a warehouse that stores data, and the data that is stored in an organized manner with respect to each other.
• A database consists of multiple tables, and multiple data records form a single table, similar to Excel
• Database structure: Consists of multiple libraries, one library contains multiple tables, and one table contains multiple data records (such as text, sound, graphics)

Ii. Classification of databases

• Relational database: Based on the relational algebra theory, the data structure can be represented by a simple "entity-relation" (e-r) graph using a simple and understandable two-dimensional data table; The E-r diagram contains the entities (data Objects), relationships, and attributes three elements
• Non-relational database: Also known as NoSQL (not-only SQL), storing data is not based on relational models and does not require a fixed tabular format

Third, common database products

• Oracle: Non-open source relational database. Oracle products, well-supported terabytes of data, stable operation, complete functionality, excellent performance, security mechanisms, expensive, cross-platform support. Large Enterprise use
• DB2: Non-open source relational database. IBM products, the most suitable for Yu Hai volume data, can meet the needs of medium and large enterprises, but also for small and medium-sized e-commerce systems, with better scalability, security and cross-platform support
• SQL Server: Non-open source relational database. Microsoft products, a full-featured database that supports only Microsoft operating systems. For medium and large enterprises, there is a great advantage in operability and interactivity compared to other databases
• PostgreSQL: The most distinctive open source database, free of charge. With the most complete data type support, PostgreSQL supports most SQL standards and offers many other modern features: complex queries, foreign keys, triggers, views, transactional integrity, MVCC. There are several ways to extend it, such as adding new data types, functions, operators, aggregate functions, indexes
• MySQL: Open-source relational database. Developed by the Swedish MySQL AB company, currently acquired by Oracle, with small size, fast speed, excellent performance, cross-platform support, low cost, easy to learn, flexible use and other features, many small and medium-sized websites will choose to use MySQL as a database
• Mariadb:mysql was acquired by Sun for $1 billion in 008, and MySQL founder Michael Widenius was dissatisfied with the slow pace of the Sun development team, leaving the Open Source Database Alliance, and from the existing MySQL program code, Developed another extended branching version, the enterprise-level Open source database called the Maria Database.
  Maria database is like the shadow version of MySQL, the Maria database is a branch version of MySQL (branch), not a derived version (folk), and provides the functionality that is fully compatible with MySQL.
• Nosql:nosql (NoSQL = not-only sql), meaning "not just SQL," is a new revolutionary movement in the database. NoSQL refers to a non-relational database. With the rise of Internet web2.0 Web site, the traditional relational database in dealing with web2.0 website, especially the super-large-scale and high-concurrency SNS type web2.0 pure Dynamic website has appeared to be inadequate, exposing a lot of difficult problems to overcome, The non-relational database has been developed very rapidly because of its own characteristics.
  Its representative of open source software such as: Membase, MongoDB, hypertable, Apache Cassandra, Couchdb and so on.

Iv. Overview of MySQL

• MySQL is an open-source relational database management system developed by the Swedish MySQL AB company and currently belongs to Oracle products. MySQL is one of the most popular relational database management systems, and MySQL is the best RDBMS (relational database Management system) application software for WEB applications.
• MySQL is a relational database management system, where relational databases store data in different tables rather than putting all of the data in a large warehouse, which increases speed and increases flexibility.
• The SQL language used by MySQL is the most commonly used standardized language for accessing databases. MySQL software has adopted a dual licensing policy, divided into community and commercial version, due to its small size, fast, low total cost of ownership, especially the open source, the development of the general small and medium-sized web site to choose MySQL as the site database.
• Thanks to the performance of its community edition, PHP and Apache make a good development environment.

V. Features of MySQL

1. Written in C and C + +, and tested with a variety of compilers to ensure the portability of the source code.

2. Support for AIX, FreeBSD, HP-UX, Linux, Mac OS, Novellnetware, OpenBSD, Os/2 Wrap, Solaris, Windows and many other operating systems.

3. Provides APIs for a variety of programming languages. These programming languages include C, C + +, Python, Java, Perl, PHP, Eiffel, and Ruby. NET, Tcl and so on.

4. Support multi-threading, make full use of CPU resources.

5. Optimized SQL query algorithm to improve query speed effectively.

6. Can be used as a separate application in the Client server network environment, but also as a library and embedded in other software.

7. Provide multi-language support, common encoding such as Chinese GB 2312, BIG5, Japanese shift_jis, etc. can be used as data table name and data column name.

8. Provides a variety of database connectivity pathways such as TCP/IP, ODBC, and JDBC.

9. Provide management tools for managing, inspecting, and optimizing database operations.

10. Support for large databases. You can handle large databases that have thousands records.

11. Support multiple storage engines.

12.MySQL is open source, so you don't have to pay an extra fee.

13.MySQL uses the standard SQL data language form.

14.MySQL has good support for PHP, and PHP is a popular WEB development language.

15.MySQL can be customized, using the GPL protocol, you can modify the source code to develop their own MySQL system.

16. Online ddl/change function, data architecture supports dynamic application and developer flexibility (5.6 new)

17. Replicate the global transaction identity, which supports self-healing clusters (5.6 new)

18. Replication no crash slave for increased availability (5.6 new)

19. Copy multi-threaded slave to improve performance (5.6 new)

20.3 Times times faster performance (5.7 new)

21. New Optimizer (5.7 new)

22. Native JSON support (5.7 new)

23. Multi-source replication (5.7 new)

24.GIS Space Expansion (5.7 new)

VI. mysql Storage engine

A storage mechanism, different functions depending on the engine, and different performance

1. Storage Engine Classification

• The default database engine before Myisam:mysql 5.0 is most commonly used. Has a higher insert, query speed, but does not support transactions. Table-level locking: The entire table is locked when data is updated. Consumes less resources and has high performance. Weak write performance

• InnoDB: The preferred engine for transactional databases, supports acid transactions, supports row-level locking, and MySQL 5.5 becomes the default database engine. There is better cache support than MyISAM, which supports table space, table partitioning, and high hardware resources.

• BDB: Another option for transactional databases originating from Berkeley DB, supporting other transactional features such as commit and rollback
• Memory: All data is placed into the storage engine, which has very high insertion, update and query efficiency. However, it occupies a memory space that is proportional to the amount of data. And its contents are lost when MySQL restarts
• Merge: Combine a certain number of MyISAM tables into a single unit that is useful for ultra-large data storage
• Archive: Ideal for storing large amounts of independent data as historical records. Because they are not often read. Archive has an efficient insertion speed, but its support for queries is relatively poor
• Federated: Combine different MySQL servers to logically make up a complete database. Ideal for distributed applications
• CLUSTER/NDB: A highly redundant storage engine that provides services in conjunction with multiple data machines to improve overall performance and security. Suitable for applications with high data volumes, security and performance requirements
• CSV: A storage engine that logically divides data by commas. It will create a. csv file for each data table in the database subdirectory. This is an ordinary text file, where each row of data occupies a line of text. The CSV storage engine does not support indexing.
• Blackhole: Black hole engine, any data written will disappear, generally used to record binlog do replication relay
• The EXAMPLE storage engine is a stub engine that doesn't do anything. It is intended as an example of MySQL source code to demonstrate how to start writing a new storage engine. Similarly, its main interest is to developers. The EXAMPLE storage engine does not support indexing.

In addition, MySQL's storage engine interface is well defined. Interested developers can write their own storage engine by reading the documentation.

2. Engine operation (1) View

mysql> show engines;                        //查看当前数据库所有支持的存储引擎mysql> show table status from 库名 where name=‘表名‘\G;     //查看指定表的状态信息，可查看表使用的存储引擎mysql> show create table 表名\G;              //查看创建表时的结构，也可查看表创建时指定的存储引擎

(2) Change (2.1) Change the default storage engine (takes effect the next time the table is created, the previous table storage engine does not change)

vim /etc/my.cnf[mysqld]default-storage-engine=引擎/etc/init.d/mysqld restartmysql -u root -pmysql> show engines;                        //验证是否更改

(2.2) Changing the storage engine of an existing table

mysql> alter table 表名 engine=引擎;                //将指定表更改为指定的存储引擎

(2.3) Specifying the storage engine when creating a table

mysql> create table 表名 字段 engine=引擎;            //创建使用指定引擎的表

(2.4) Change the InnoDB of all tables under the specified library to MyISAM

yum -y install perl-DBD-MySQL       //安装命令所需的软件包 mysql_convert_table_format --user=用户名 --password=‘密码‘ --socket=/tmp/mysql.sock --type=MyISAM 数据库名           //更改指定库下的所有表的存储引擎；适用于源码mysql_convert_table_format --user=用户名 --password=‘密码‘ --socket=/var/lib/mysql/mysql.sock --type=MyISAM 数据库名         //更改指定库下的所有表的存储引擎；适用于RPM或YUM

Note: The Mysql_convert_table_format command can only be changed from the InnoDB engine to MyISAM

Vii. MySQL indexing function 1. Role of the Index

• Greatly accelerate the retrieval speed of data and increase the efficiency of query
• Reduce disk I/O costs
• Ensuring uniqueness of data records
• Enables faster execution of SQL statements applied to tables

2. Index overview

• In a relational database, an index is a single, physical storage structure that sorts the values of one or more columns in a database table, a collection of one or more column values in a table, and a logical pointer list corresponding to the data pages that physically identify those values in the table. The index acts as a catalog of books, and you can quickly find what you want based on the page numbers in the catalog.
• An index is a special kind of file (an index on a InnoDB data table is an integral part of a table space), and they contain reference pointers to all records in the datasheet. Indexes are not universal, and indexes can speed up data retrieval operations, but slow down data modification operations. Each time a data record is modified, the index must be refreshed once. To compensate for this flaw in some way, many SQL commands have a delay_key_write entry. The purpose of this option is to temporarily stop MySQL from updating the index immediately after each new record is inserted and after each modification of the command, the refresh of the index will wait until all records have been inserted/modified. The Delay_key_write option is very obvious when you need to insert many new records into a data table. In addition, the index takes up considerable space on the hard disk. Therefore, you should only index the most frequently queried and most frequently sorted columns of data. Note that if a data column contains a lot of duplicate content, indexing it does not have much practical effect.
• Theoretically, you can create an index for each field in the data table, but MySQL limits the total number of indexes in the same table to 16.

Index of the InnoDB data table

• On InnoDB data tables, indexes are much more important to InnoDB data tables. On the InnoDB data table, the index will not only play a role in searching data records, but also the Acenaphthene and foundation of the data row-level locking mechanism. "Data row-level locking" means that the individual records being processed are locked during the execution of a transactional operation and are not accessible to other users. This lock affects (but is not limited to) SELECT, Lockinsharemode, select, forupdate commands, and INSERT, UPDATE, and DELETE commands. For efficiency reasons, data row-level locking of InnoDB data tables actually occurs on their indexes, not on the data table itself. Obviously, the data row-level locking mechanism works only when the relevant data table has an appropriate index to lock on.

3. Limitations of the Index

• If the WHERE clause has a non-equal sign (where Coloum! =) in the query condition, MySQL will not be able to use the index. Similarly, if a function (where day (column) =) is used in the query condition of the WHERE clause, MySQL will not be able to use the index. In a JOIN operation (when data needs to be extracted from multiple data tables), MySQL can use the index only if it has the same data type as the primary key and foreign key.
• If you use the comparison operator like and regexp,mysql in the query condition of the WHERE clause, you can use the index only if the first character of the search template is not a wildcard. For example, if the query condition is like ' abc% ', MySQL will use the index, and if the query condition is like '%abc ', MySQL will not use the index.
• In an ORDER by operation, MySQL uses the index only if the sort condition is not a query-condition expression. (Even so, in the case of multiple data-table queries, those indexes have little effect in speeding up the ORDER by if there are indexes available). If a data column contains many duplicate values, it does not have a good effect even if the index is indexed. For example, if a data column contains something like "0/1" or "y/n" equivalent, there is no need to create an index for it.

4. Classification of Indexes (1) General index

• The most basic type of index, no restrictions such as uniqueness.

(2) Unique index

• A unique index is one that does not allow any two rows to have the same index value.

• When duplicate key values exist in existing data, most databases do not allow a newly created unique index to be saved with the table. The database may also prevent the addition of new data that will create duplicate key values in the table. For example, if a unique index is created on the employee's last name (lname) in the Employees table, none of the two employees will have a namesake.

• After a unique index is created on a column, when a new record is inserted, the database management system automatically checks to see if the new record has duplicate values on that column, and the UNIQE constraint in the Create TABLE command implicitly creates a unique index.

(3) Primary key index

• The value of a column or combination of columns (fields) in a database table uniquely identifies each row in the table as a primary index. This column is called the primary key of the table.

• Defining a primary key for a table in a database diagram automatically creates a primary key index, which is a specific type of unique index. The index requires that each value in the primary key be unique. When a primary key index is used in a query, it also allows quick access to the data.

• Tip Although a unique index helps locate information, for best performance results, it is recommended that you use a primary key index instead.

(4) Candidate index

• As with the primary index, the uniqueness of field values is required, and the order in which records are processed is determined. In databases and free tables, you can establish multiple candidate indexes for each table.

(5) Composite Index

• An index on two or more columns is called a composite index. With additional columns in the index, you can narrow the scope of your search, but using an index with two columns differs from using two separate indexes. Duplicate values and null values are not allowed for data logging.

(6) Full-text indexing

• Full-text indexing is the key technology to realize big data search at present. can use the word segmentation technology and other algorithms to intelligently analyze the text of the key words in the frequency and importance, and then according to a certain algorithm rules to intelligently filter out the search results we want. Used for multiple column values, allowing duplicate values and null values for data records

• MySQL comes with a full-text index only for data tables that are MyISAM by the database engine

(7) Spatial index

• In the MySQL 5.7.4 lab release, the InnoDB storage engine has added support for geometric data spatial indexes. Prior to this, InnoDB stored geometry data as BLOB (binary large object) data, and only prefix indexes were created on spatial data, which was very inefficient when it came to spatial search, especially when complex geometry data was involved. In most cases, the only way to get results is to scan the table. In the new version of MySQL, InnoDB supports spatial indexes, which are implemented by R-trees, making space search more efficient.

5. Use of the index (1) Create an index

create [unique | fulltext | spatial] index 索引名 on 表名(字段)

Detailed

Unique: Single Index

Fulltext: Full-text index, INNODB does not support fulltext type of index

Spatial: Spatial Index

Case

Mysql> CREATE index Hehe_1 on Hehe.user (user_name); User_name field in user table new normal index

Mysql> Create unique index hehe_2 on hehe.user (USER_PASSWD); New unique index USER_PASSWD field in user table

Mysql> CREATE index Hehe_3 on Hehe.user (USER_NAME,USER_PASSWD);

The user_name, user_passwd fields in the user table are combined as composite indexes

(2) View index

show index from 数据库名.表名;

(3) Deleting an index

drop index 索引名 on 数据库名.表名;

Eight, MySQL transaction function 1. Brief description of the transaction

A transaction is a contiguous set of database operations, as if it were a single unit of work. In other words, it will never be a complete transaction unless each individual operation within that group is successful. If any operation on the transaction fails, the entire transaction fails.

2. Transaction characteristics

• Atomicity: In the manipulation of a transaction, either it is executed, or neither is executed
• Consistency: In a transaction, the data is guaranteed to become another consistent state from one consistent state
• Isolation: Each transaction does not interfere with each other and can be executed together
• Persistence: After a transaction commits, the effect of the data is permanent

3. Transaction use (1) Auto-commit

mysql> set autocommit=1;            //开启事务自动提交，set autocommit=0禁止自动提交

(2) Manual submission

mysql> begin;                   //事务开始声明mysql> SQL语句...;               //执行的SQL语句mysql> rollback;                //回滚事务mysql> commit;                  //提交事务

NOTE: Rollback can only be rolled back before the current transaction commits, all failures of the current transaction commit succeeded

(3) Automatic rollback of the situation

• Manually exit the database after begin (such as CTRL + C, quit, exit)
• Server Power off

(4) Example: Transfer

1. Transfer to the other 1000, the account is not correct

2. Enter the amount of the transfer

3. Submit Confirmation

Begin

Create database hehe;

CREATE TABLE Hehe.a (number Int,user char (4), Menony Int (4));

INSERT into HEHE.A values (' 1 ', ' a ', ' 1000 ');

INSERT into HEHE.A values (' 2 ', ' B ', ' 10 ');

Commit Normal submission

Transactional operations

Begin Begin

Update hehe.a set menony= ' 0 ' where user= ' a ';

Rollback Rollback operation

Begin

Update hehe.a set menony= ' 0 ' where user= ' a ';

Update hehe.a set menony= ' 1010 ' where user= ' B ';

Commit Submit

Rollback Because the system has been submitted, it cannot be revoked,

MySQL Database system