Shulou(Shulou.com)06/01 Report--

Xiaosheng blog: http://xsboke.blog.51cto.com

-Thank you for your reference. If you have any questions, you are welcome to communicate.

Type of database

1) the first generation database:

Database based on hierarchical Model and mesh Model

The hierarchical database is the IMS (InformationManagement System) database of IBM Company. The hierarchical database provides good integrity support and the model is simple. It is very natural, intuitive and easy to understand for the departments with one-to-multi-layer relationship. It is more suitable for those environments where the relationship between entities is fixed and predefined, and its performance is better than the relational model. However, the hierarchical model database has many restrictions on query, insert and delete operations.

The network database can describe the real world more directly, with good performance and high access efficiency; but with the increase of the application environment, the structure of the database will become more and more complex, and its DDL/DML language is also relatively complex, which is not conducive to the use of users.

2) second generation database:

Relational database

The commonly used relational databases are db2, oracle, sqlserver and mysql.

Relational database is suitable for storing structured data, which is a two-dimensional table based on rows and columns, and unstructured data is generally stored in the system storage.

3) the third generation database

Object-oriented database

Object-oriented database system is a new generation of database system to meet the needs of new database applications, which has the characteristics of easy maintenance, high quality, high efficiency and easy expansion, mainly to support very complex data models, which are suitable for the field of engineering design. However, because of the complexity of the model, many system management functions are difficult to achieve and do not have the powerful ability of SQL to deal with collective data.

2. Factors affecting the performance of mysql

1. The impact of business demand

For example, the total number of forum posts is required to be updated in real time

1) Myisam storage engine:

Myisam has a special counter, which can be read directly when counting rows, so the database using myisam storage engine does not need to worry about this, but myisam does not support transactions and row locks, so it is rarely used now.

2) Innodb storage engine:

The Innodb storage engine does not have a dedicated counter, so it will take a lot of time to count the total number of posts. Although one can be created specifically for counting the total number of posts, if there are very large concurrent writes, then this method is not very difficult to solve.

two。 The impact of system architecture and implementation

1) binary multimedia data

If you store some other related binaries such as pictures, music, videos and so on directly in the database, it will take up a lot of database space resources and consume the CPU resources of the database host. The solution is to store these binaries on disk.

2) very large text data

For some large text data, it is not suitable to be stored in the database, because it will make the performance of the database underground and waste space, so it can be stored on disk.

3. The waste of performance by query statements

For database management software, the biggest performance bottleneck is the disk IO. When querying a certain data, disk IO needs to be done many times. Load the data block into memory and do data lookup. When querying some data, you may need to perform this disk IO operation hundreds of times, so it is very expensive in terms of resource consumption, but if you look for a certain point of content in a different way, the number of IO required may be relatively reduced, that is, indexing.

4. The influence of Database schema Design on performance

Is the data structure of the database.

5. Impact of hardware performanc

Icano performance of disk: memory, hard disk, network card, etc.

L CPU's processing power

L network equipment: switching routes, network cables, network cards, etc.

Third, mysql performance optimization-index

1. Index function

The index optimizes the query speed by reducing the disk IO, but reduces the performance of adding, deleting and modifying to a certain extent, because the index is equivalent to the directory of a book, the content of the book changes, the index also needs to be updated, and the index is stored in physical files, which relatively takes up disk space.

two。 Type of index

Indexes are generally divided into B-tree, R-tree and full-text indexes, but the innodb storage engine does not support full-text indexes, and the commonly used index is B-tree index, also known as balanced tree index.

3. The structure and principle of B-tree index.

When querying data, disk block 1 is first called into memory, then the corresponding pointer is queried according to the range of the data item, and then a disk block is called again according to the pointer, so the query scope is reduced by circular query, and finally the desired data is obtained.

Using the index to query may only take a few disk IO to find the desired data, while without the index, you need to traverse the database to find the data. It may take tens of hundreds or more disk IO to get the desired data, which reflects the point of the index.

The bottom layer that stores real data is called leaf, while the other layers are non-leaf nodes.

4. Advantages of indexing

Improve query speed, quickly locate data, and greatly improve the efficiency of mysql query (including sorting, grouping)

5. Shortcomings of the index

The index is stored on the hard disk in the form of files, which takes up the hard disk space to a certain extent.

The index needs to be updated when writing data, which is a great overhead for the database.

6. The use of indexes is not recommended

When the table has fewer records (usually 1000 rows)

When the selectivity of the index is low, for example, one column is gender, and there are only two kinds of data for men and women, the selectivity is lower.

7. Type of index

Indexes include single-column indexes and combined indexes

1) General index

This is the most basic index, it has no restrictions.

CREATE INDEX indexName ON tablename (column1 [, column2, …])

2) unique index

It is similar to the previous normal index, except that the value of the index column must be unique, but null values are allowed, which refers to null. If it is a composite index, the value of the combined column must be unique

CREATE UNIQUE INDEX indexName ON tablename (column1 [, column2, …])

3) Primary key index: a special unique index that does not allow null values. Generally, a primary key index is established at the same time when the table is built.

CREATE TABLE tablename (ID INT NOT NULL, username VARCHAR (16) NOT NULL, PRIMARY KEY (ID))

4) combined index

In order to further improve the efficiency of MySQL, it is necessary to consider establishing a composite index, which is an index composed of multiple columns.

Valid columns of a combined index

A combined index has the characteristics of the leftmost prefix, such as creating an index with column values A, B, and C. when using the command to create an index, the order of the three values will determine when the index is used. If it is A, B, C once from left to right, then the index is used only when the conditions are ABC, AB, A, and the order of the conditions cannot be reversed, if the conditions are BC, B, C. The index will not be used

5) full-text indexing (rarely used, generally used when querying big data)

Used only for MyISAM tables to index text fields. Field types include char, varchar, text

However, keep in mind that for large data tables, generating a full-text index is a very time-consuming and hard disk space-consuming practice.

CREATE FULLTEXT INDEX indexname ON tablename (column)

8. Query index

Mysql > show index from table name

Mysql > show keys from table name

9. Under what circumstances do you need to build an index and what columns should be selected to build the index?

When the table has more records and the query speed is slow, we need to optimize the query performance of the database by establishing an index.

L columns that generally appear frequently in where and join clauses need to be indexed

Considerations for using indexes

When using like for fuzzy queries, queries that start with the wildcard "%" or "_" will not use indexes

When an operation is performed on a column, the index also fails.

MySQL uses the index only on the following operator: =, between,in

Don't overdo indexing, that's enough, because each additional index takes up extra disk space and degrades the performance of write operations, because when you modify the contents of a table, the index must be updated, and sometimes it may need to be refactored, so the more indexes, the longer it takes.

Fourth, the idea of mysql performance optimization.

1. Basic ideas

Through the show query system status and other information, to optimize the database

Optimize query speed through slow query logs

The Explain tool can simulate the execution of the statement, and then get the execution information of the statement by outputting the result.

The Profiling tool can actually execute the statement and get the execution information of the statement by outputting the result.

two。 Index optimization

1) for example, some users complain that the query data of the company's website is very slow, and then we need to optimize the query performance of the database. Users do not know what sql statement is used, so we need to find the SQL statement and optimize it.

First, open the slow query log and record the statements that query more than N seconds.

Then create indexes and so on according to the conditions.

2) slow query log

3) simulate the execution of statements by explain tools to analyze and query

4) the actual query statement is analyzed by profiling tool, which is more accurate than explain.

Execute the statement to be tested and view the feedback

You can view the details of statement execution through the query_id number of the statement.

V. configuration optimization

Is the parameter configuration in my.cnf

1. Max_connections: maximum number of connections, valid only for myisam storage engine

View currently established connections

Check the number of attempted connections or connected

Error 1040:too many connections may be reported when the number of connections is greater than the maximum number of connections

The method to determine the current maximum number of connections:

2. The number of Back_log:mysql request queues, which is only valid for myisam storage engine

The number of connections allowed to be queued when the number of mysql concurrent connections reaches its maximum

Check the number of queues currently waiting

3. Wait_timeout and interactive_timeout, valid only for myisam storage engine

Non-interactive and interactive idle timeout

4. Key_buffer_size: index buffer size, valid only for myisam storage engine

Function:

Determine the speed of index processing

Optimization method:

5. Query_cache_size: query cache, or QC for short, is only valid for myisam storage engine

1) configuration parameters

2) function:

Using the query buffer, mysql stores the queried results in the buffer. In the future, for the same select statements (case and spaces of select statements), the results will be read directly from the buffer.

3) you can judge whether the QC setting is reasonable by checking the status value 'Qcache%'.

Qcache_free_blocks: the number of adjacent memory blocks in the cache. If the value is larger, there are more memory fragments in Query Cache, and the query cache fragmentation rate = Qcache_free_blocks / Qcache_total_blocks * 100%. If the query cache fragmentation rate is more than 20%, use FLUSHQUERY CACHE to defragment the cache.

The amount of memory currently remaining in the Qcache_free_memory:Query Cache.

Qcache_hits: indicates how many times the cache has been hit. We can mainly use this value to verify the effect of our query cache. The higher the number, the better the cache.

Qcache_inserts: indicates how many misses and then inserts.

Qcache_lowmem_prunes: how many Query are purged out of Query Cache due to insufficient memory. Through the combination of "Qcache_lowmem_prunes" and "Qcache_free_memory", we can know more clearly whether the memory size of Query Cache in our system is really enough and whether Query is swapped out very frequently because of insufficient memory.

Qcache_not_cached: the number of queries that are not suitable for caching, usually because they are not SELECT statements or use functions such as now ().

Qcache_queries_in_cache: the number of Query of cache in the current Query Cache

Qcache_total_blocks: the number of block in the current Query Cache.

4) configuration of query_cache

6. Max_connect_errors: counter value related to mysql security, maximum number of error connections

Valid only for myisam storage engine

Prevent brute force cracking, locking the user after entering the wrong password for many times in a row

Clear counters: restart the mysql service or use the command flush hosts

7. Sort_buffer_site: sort buffer size, valid only for myisam storage engine

Affect the query speed of query statements with order by or group by

This configuration is a connection-level (connection-level) parameter, with each connected user assigned a separate buffer.

8. Max_allowed_packet: limit the size of select and update statement packages accepted by server

Valid only for myisam storage engine

The maximum is 1GB

9. Join_buffer_size: affects query speed with multi-table query type statements

Connection level parameter, valid only for myisam storage engine

10. Tread_cache_size: set server thread cache, which is only valid for myisam storage engine

Setting rules: 1GB memory 8, 2GB memory 16, 3GB memory 32

This value indicates that the number of threads saved in the cache can be reused, and when disconnected, the client thread will be placed in the cache to respond to the next customer rather than destroy (provided that the cache number does not reach the limit)

11. Innodb_buffer_pool_size: equivalent to what key_buffer_size does to the MyISAM table

InnoDB uses this parameter to specify the size of memory to buffer data and indexes. For a separate MySQL database server, you can set this value to up to 80% of physical memory

According to the MySQL manual, the recommended value for machines with 2G memory is 1G (50%). If you don't have a lot of data and don't burst, you don't need to set the innodb_buffer_pool_size too big.

12. Innodb_flush_log_at_trx_commit: controls the point in time at which transaction logs are written

13. Innodb_thread_concurrency: sets the number of concurrent innodb threads

The default value is 0, which means there is no limit. If you want to set it, it will be the same as the CPU core of the server or 2 times the number of cpu cores. It is recommended to use the default setting, which is generally 8.

14. Innodb_log_buffer_size

Determines the amount of memory used by the transaction log file, in M units. Larger buffers can improve performance, and for larger transactions, you can increase the cache size.

15. Innodb_log_file_size

Determine the size of the data log file, in M units, larger settings can improve performance.

16. Innodb_log_files_in_group

Set the number of groups for log files

17. Read_buffer_size:

MySql read buffer size.

A request for a sequential scan of the table allocates a read buffer and MySql allocates a memory buffer for it. If sequential scan requests for tables are very frequent, and you think frequent scans are too slow, you can improve its performance by increasing the value of the variable and the memory buffer size.

Like sort_buffer_size, the allocated memory corresponding to this parameter is exclusive to each connection.

18. Read_rnd_buffer_size:

The random read (query operation) buffer size of the MySql.

When rows are read in any order (for example, in sort order), a random read cache is allocated. When sorting a query, MySql scans the buffer first to avoid disk search and improve query speed. If you need to sort a large amount of data, you can increase the value appropriately. However, MySql allocates this buffer space for each customer connection, so you should try to set this value appropriately to avoid excessive memory overhead.

Sequential reading means that the required row data can be read sequentially according to the leaf node data of the index. Random reading means that it is generally necessary to find the actual row data according to the primary key in the secondary index leaf node, but the data segment of the secondary index and the primary key is different, so the access mode is random.

19. Bulk_insert_buffer_size:

Bulk insert data cache size, can effectively improve the insertion efficiency, the default is 8m.

20. Configuration parameters of binary log log file

6. After optimization, the mysqlslap tool can be used for testing.

Before optimization

Write all optimized statements for testing

VII. Summary of optimized sentences

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.