Some advanced usage of mysql

When we master the basic operations of Mysql, such as table creation statements, simple sql statements, etc., we can also do some configuration operations on mysql itself, and even optimize Mysql. For more advanced mysql knowledge, please visit the mysql tutorial channel on the php Chinese website.

1. Mysql configuration file.

In Windows, it is the my.ini file:

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

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

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

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

2. The mysql architecture is divided into four layers from top to bottom:
1), connection layer: connection processing, authorization authentication, and related security solutions.

2), Service layer: Complete SQL optimization, analysis, caching and other functions.

3), Engine layer: Responsible for storing and retrieving data.

4) Storage layer: The data storage layer mainly stores data in the file system and completes the interaction with the engine.

3. mysql engine

1) Check the engine used:

show engins or show

Comparison between MYISAM and INNODB

Does not support primary and foreign key value pairs; supports

does not support transactions; supports

table locks, not suitable for high concurrency; row locks, suitable for high concurrency

Only cache index; cache index and data, higher memory requirements

Small table space; large table space

The focus is on performance, which is suitable for many queries in more cases; Focus on matters.

4. Reasons for the decline in mysql performance optimization

1), poorly written query statements

2), index failure

Single -value index

## Create INDEX IDX_USER_NAME on User (name)

Composite index

## Create Index_user_name_email on User (name, email)

3), the query has too many joins

4), server tuning and various parameters

5, mysql execution order

The machine starts reading from from.

1), inner join

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

2), left outer join

                                                                                                                                                                                                                      # .

3), Right outer connection

SELECT *From Tablea A Right Join Tableb B on A.Key = B.Key

## When A did not, use NULL to complete it

4), left outer join, remove the right table part

                                                                                                                                                                       

Unique to a, b is filled with null at this time, so b is empty at this time.

5), right outer join, remove the left table part

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

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

                                                                                                                                                                                                                                                           

(select *from tableA A right join tableB B )

7), center is empty

## 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) The index is a data structure that improves search efficiency.

Quickly search the data structure in order.

The database maintains data structures that satisfy specific algorithms. This data structure points to the data in a

way, so that advanced super-searching can be implemented on these data structures

Algorithm, this data structure is called an index.

BTREE index (without special designation)

Frequently deleted fields are not suitable for building indexes.

2), Advantages

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

Sort the data through the index column, reduce the cost of data sorting and reduce the CPU Consumption

3) Disadvantages

The index is also a table and takes up disk space.

It is necessary to maintain the index when updating the table, which will reduce the update efficiency.

The index is just a factor that improves efficiency. It takes time and effort to build an index.

4) Single-value indexes only contain one column. A table can have multiple single-value indexes.

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

6), compound index, including multiple columns.

7) Index related operations

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. Under what circumstances should an index be created:

1), Mainly build a unique index automatically

2). Fields that are frequently used as query conditions should be indexed.

3) For fields associated with other tables in the query, indexes should be created using external relationships.

4). Frequently updated fields are not suitable for creating indexes.

5). Create indexes for fields that are not used in the where condition.

6), the choice of single or combined index, tend to create compound index under high concurrency

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

8), statistics or grouping fields in queries

8, in which cases it is not necessary to create an index:

1), table Too few records

2), tables that are frequently added or deleted

3), fields with duplicate data and even distribution.

9. Performance analysis:

mysql query optimizer

The above are some advanced knowledge of Mysql that I have summarized. I hope it will be helpful to everyone. .

Related articles:

mysql basic syntax

Master PHP MySQL Where clause

The above is the detailed content of Some advanced usage of mysql. For more information, please follow other related articles on the PHP Chinese website!