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

This article mainly gives you a brief account of the more advanced uses of mysql. You can look up the relevant professional terms on the Internet or find some related books to supplement them. We will not dabble here, so let's go straight to the topic. I hope this article on the more advanced uses of mysql can bring you some practical help.

1. Configuration file of mysql.

In windows is the my.ini file:

1), log-bin: indicates the log file of master-slave replication, which is used for master-slave replication.

2), log-error= "SO3JFYNN8EW6GMO.err": indicates the error log.

3), server-id=1: indicates that the serial number of the machine is 1.

4), datadir=D:/mysql5.7/Data: the directory where the database information is stored

2. The architecture of mysql is divided into four layers from top to bottom:

1) connection layer: connection processing, authorization and authentication, related security schemes.

2), service layer: complete sql optimization, analysis, caching and other functions.

3) engine layer: responsible for storing and extracting data.

4) Storage layer: data storage layer, which mainly stores data in the file system and interacts with the engine.

3. Mysql engine

1) View the engine used:

Show engins or show

Comparison between MYISAM and INNODB

Do not support primary foreign key value pairs; support

Do not support transactions; support.

Table lock, not suitable for high concurrency; row lock, suitable for high concurrency

Cache indexes only; cache indexes and data, requiring more memory

Small tablespace; large tablespace

The focus is on performance, and in more cases it is suitable for situations where there are many queries; focus transactions.

4. Reasons for the decline of mysql performance optimization.

1) the query statement is poorly written

2), index invalidation

Single-valued index

Create index idx_user_name on user (name)

Composite index

Create index idx_user_name_email on user (name,email)

3) there are too many join in the query

4), CVM tuning and various parameters

5. The execution order of mysql

The machine starts reading from from.

1), internal connection

Select * from tableA An inner join tableB B on A.key=B.key.

2), left outer connection

Select * from tableA A left join tableB B on A.key = B.key

When b doesn't have it, use null to make it up.

3), right outer connection

Select * from tableA A right join tableB B on A.key=B.key

When an is not available, use null to complete it

4), left outer connection, remove the right table

Select * from tableA A left join tableB B on A.key=B.key where B.key is null

An is unique, at this time b is filled with null, so b is empty at this time.

5), right outer connection, remove the left table

Select * from tableA A right join tableB B on A.key = B.key where A.key is null

6), full connection (you can add the two) union can be combined and deduplicated

Select * from tableA A left join tableB B on A.key=B.key union

(select * from tableA A right join tableB B)

7) the center is vacant

Select * from tableA A left join tableB B on A.key=B.key where B.key is null union

(select * from tableA A right join tableB B on A.key=B.key where A.key is null)

6. What is an index

1) Index is a kind of data structure, which is to improve the efficiency of search.

Ordered quick lookup data structure.

The database maintains a data structure that satisfies a specific algorithm.

Point to the data in some way, so that you can achieve advanced super-search on these data structures.

Algorithm, this data structure is called an index.

BTREE index (without special specification)

Fields that are deleted frequently are not suitable for indexing.

2), advantage

Improve the efficiency of data retrieval and reduce the io cost of database

Sort the data through the index column, reduce the cost of data sorting and reduce the consumption of cpu

3), disadvantage

An index is also a table that takes up disk space.

Maintaining the index when updating the table will reduce the update efficiency.

Indexing is only a factor to improve efficiency, and it takes time and effort to set up an index.

4), single-valued index, which contains only one column, and a table can have multiple single-valued indexes.

5) unique index, the value of the index column must be unique and null is allowed.

6), composite index, which contains multiple columns.

7), related operations of the index

Create index idx_user_name on user (name)

Alter user add index idx_user_name on (name)

Show index from user

Drop index idx_user_name on user

8), index structure

Hash index

Full-text full-text index

R-Tree index

BTree Index:

7. What are the circumstances in which to create an index:

1), the main builder automatically sets up a unique index

2) the fields that are frequently used as query criteria should be indexed

3), the fields associated with other tables in the query are indexed by external relationships.

4), fields that are updated frequently are not suitable for creating indexes

5) create an index with fields that are not used in the where condition.

6), the choice of single index or combined index tends to create composite index under high concurrency.

7), the sorted fields in the query, if the sorted fields are accessed according to the index, it will improve the efficiency

8), statistics or grouping fields in the query

8. Which situations do not need to create an index:

1), the table records are too few

2) tables that are frequently added and deleted

3) Fields with duplicate data and evenly distributed data.

9. Performance analysis:

Mysql query optimizer

What are the more advanced uses of mysql to tell you here, for other related issues you want to know can continue to pay attention to our industry information. Our section will capture some industry news and professional knowledge to share with you every day.

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.