PHP engineers and php engineers

PHP engineers and php engineers

I. http/https protocol, TCP/IP protocol, websocket, session, cookie

 

 

Ii. php: oop, thinkphp5, laravel

 

 

Iii. mysql, memcache, and redis

3.1 mysql interview questions

3.1.1 MySQL replication principles and procedures

 

 

3.1.2 what is the difference between char and varchar?

* Char is used to store strings of a fixed length. The maximum length is 255. a value greater than the specified length is truncated, and a value smaller than the specified length is filled with spaces.

* Varchar is used to save a variable-length string. The maximum length is 65535. It only stores the actual length required by the string. It adds an additional byte to save the length of the string, varchar uses an additional 1 ~ 2 bytes to store the length of the value. If the maximum length of a column is less than or equal to 255, 1 byte is used; otherwise, 2 bytes is used.

* Char and varchar are closely related to character encoding. lantin1 occupies 1 byte, gbk occupies 2 bytes, and utf8 occupies 3 bytes.

3.1.3 mysql slow query log

* The Slow query log of Mysql, as its name implies, records the SQL statements whose execution time exceeds the set value (10 s by default) into the log. This feature must be manually enabled, but it may cause certain performance loss.

* Check whether show variables like '% slow_query_log %' is enabled ';

* Enable slow query: set global slow_query_log = 1;

* My. cnf settings:

Slow_query_log = 1

Long_query_time = 10

Slow_query_file =/path/mysql_slow.log

* Log_outpout:

Specify the log storage method. Log_output = 'file' identifies the log to be saved to the FILE. The default value is 'file '. Log_output = 'table' indicates storing logs into the database, so that the log information is written into the mysql. slow_log TABLE.

The Database supports two log storage methods at the same time, which can be separated by commas (,) during configuration, for example, log_output = 'file, Table '.

Logs are recorded in the system's dedicated log table, which consumes more system resources than those recorded in files. Therefore, to enable slow query logs, you must obtain higher system performance, we recommend that you record files first.

* Log-query-not-using-indexes:

Queries without indexes are recorded in slow query logs (optional ). We recommend that you enable this option for optimization. In addition, if this parameter is enabled, the SQL statements that use full index scan will also be recorded in slow query logs.

* Log_slow_admin_statements:

Indicates whether to record slow query logs using slow management statements such as analyze table and alter table.

* Analysis tool mysqldumpslow-slow query analysis tool

 

3.1.4 index type

* Primary key index. The Primary key is automatically used as the Primary index (type Primary)

* UNIQUE index (UNIQUE)

* Full-text indexing (FULLTEXT) is only supported by the MyISAM storage engine. It is used in many content systems and more for websites in full English. Chinese data is not commonly used and is of little significance. Domestic full-text indexes are usually completed using sphinx. Full-text indexes can only be created in char, varchar, and text fields)

* Comprehensive use => Compound Index

 

3.1.5 lock

Https://www.cnblogs.com/chenqionghe/p/4845693.html

A lock is a mechanism by a computer to coordinate multiple processes or pure threads to concurrently access a certain resource.In addition to competing for traditional computing resources (CPU, RAM, and I/O) in databases, data is also a resource shared by multiple users. How to ensure consistency and effectiveness of Concurrent Data Access is a problem that all databases must solve. Lock conflicts are also an important factor affecting the concurrent database access performance. From this perspective, locks are especially important and more complex for databases.

 

Overview

Compared with other databases, MySQL locks are relatively simple. The most notable feature is that different storage engines support different locks. MySQL can be classified into the following three types of locks:

* Table-level locks: the costs are small and the locks are fast. No deadlocks occur. The lock granularity is large, and the probability of lock conflicts is highest and the concurrency is lowest.

* Row-level locks: high overhead and slow locking; deadlocks may occur; the minimum lock granularity, the lowest probability of lock conflicts, and the highest concurrency.

* Page lock: the overhead and lock time are between table locks and row locks. Deadlocks may occur. The lock granularity is between table locks and row locks, and the concurrency granularity is general.

 

Mysql Table-Level Lock mode (MyISAM) has two types: Table shared Lock (Read Lock, Table Read Lock) and Table exclusive Write Lock (Table Write Lock ).

* Read operations on MyISAM will not block other users' requests to the same table, but will block write requests to the same table;

* Write operations on MyISAM will block read and write operations on the same table by other users;

* The read and write operations of MyISAM tables are serial.

After a thread acquires a write lock on a table, only the lock-holding thread can update the table. Read and Write operations of other threads will wait until the lock is released.

 

3.1.6 sph (sfink, full-text search engine)

Sphinx is an SQL-based full-text search engine that can be used in combination with MySQL and PostgreSQL for full-text search. It provides more professional search functions than the database itself, this makes it easier for applications to implement professional full-text retrieval. Sphinx specially designs search API interfaces for some scripting languages, such as PHP, Python, Perl, and Ruby. It also designs a storage engine plug-in for MySQL.

Sphinx a single index can contain a maximum of 0.1 billion records. The query speed of 10 million records is 0. x seconds (in milliseconds ). The index creation speed of sphenders is: 3 to 3 for creating an index with 1 million records ~ In four minutes, you can create an index of 10 million records within 50 minutes, and only the incremental Index containing the latest 0.1 million records takes dozens of seconds to re-build the index.

The main features of Sphinx include:

High-speed indexing (nearly 10 Mb/s on the new CPU );

High-speed search (the average query speed of 2-4g text is less than 0.1 seconds );

High Availability (up to GB of text and MB of documents are supported on a single CPU );

Provides good relevance ranking and supports distributed search;

Generate document summaries;

Provides search from the plug-in storage engine of MySQL, and supports query of Boolean, phrase, and synonym;

Supports multiple full-text search domains for each document (up to 32 by default );

Multiple attributes of each document are supported;

Support word breaking;

Supports single-byte and UTF-8 encoding.

 

3.1.7

 

3.1.8

 

3.1.9

 

 

3.1.10

 

 

Iv. apache and nginx

 

 

V. shell scripts, common linux server maintenance commands