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 joins4), server tuning and various parameters5, 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 null6), 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?
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 structuresAlgorithm, 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 Consumption3) DisadvantagesThe 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:
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!