5.3. Performance and Efficiency
5.3.1. Fixed length and variable length table
A data table containing any variable-length fields such as varchar and text is a variable-length table, and vice versa is a fixed-length table.
l For variable-length tables, many deletions and changes on them will make more fragmentation in the table due to different records sizes. OPTIMIZE TABLE is required to run regularly to maintain performance. And there is no such problem in fixed-length tables;
l If there are variable-length fields in the table, converting them into fixed-length fields can improve performance, because fixed-length records are easy to process. But before trying to do this, the following should be considered:
l Using fixed length columns involves some trade-offs. They are faster, but take up more space. Each value of the char(n) type column always takes up n bytes (even an empty string), because when stored in the table, the length of the value is not long enough will fill the space on the right;
l And columns of type varchar(n) take up less space because they are allocated only the space required to store each value, and each value is added with one byte to record its length. Therefore, if you choose between char and varchar types, you need to make a trade-off on time and space;
l The conversion from variable length table to fixed length table cannot be converted only one variable length field, they must be converted all of them. And you must use an ALTER TABLE statement to convert all at the same time, otherwise the conversion will not work;
l Sometimes you cannot use fixed-length types, even if you want to do this, it won’t work. For example, for strings that are longer than 255 characters, there is no fixed-length type;
l. When designing the table structure, if you can use fixed-length data types, try to use fixed-length ones, because the query, retrieval and update speed of fixed-length tables are very fast. If necessary, some key tables that are subject to frequent access can be split, such as a fixed-length data one table and a non-fixed data one table. For example, phpcms_member table of phpcms, etc. Therefore, global considerations are required when planning data structures;
When designing table structures, you should do the right thing and think about it repeatedly to achieve the optimal data storage system.
5.3.2. Operation and Retrieval
Numerical operations are generally faster than string operations. For example, comparison operations can be used to compare the numbers in a single operation. String operations involve several byte-byte comparisons, and if the string is longer, there will be more comparisons.
If the number of values in the series is limited, the advantages of numerical operations should be obtained using ordinary integers or emum types.
Smaller field types are always much faster to process than larger field types. For strings, their processing time is directly related to the string length. Generally, smaller tables are processed faster. For fixed-length tables, the smallest type should be selected, as long as the value in the required range can be stored. For example, if mediumint is sufficient, do not choose bigint. For variable length types, it is still possible to save space. A value of type TEXT records the length of the value with 2 bytes, while a LONGTEXT records the length of its value with 4 bytes. If the stored value length never exceeds 64KB, using TEXT will save 2 bytes per value.
5.3.3. Structural Optimization and Index Optimization
Indexes can speed up query speed, and index optimization and query optimization complement each other. You can optimize the index based on the query or the existing index. This depends on the modification of the query or index, which has the least impact on the existing product architecture and efficiency.
Index optimization and query optimization are the crystallization of years of experience. It cannot be described in detail here, but several basic guidelines are still given.
First, based on the actual operation and access of the product, find out which SQL statements are most commonly executed. The most often executed and most often appear in programs are completely different concepts. The most commonly executed SQL statements can be divided into operations on large tables (with more data entries) and small tables (with fewer data entries). Whether large or small tables, there can be operations that can be divided into multiple reads (SELECT), multiple writes (UPDATE/INSERT) or multiple reads and writes.
For SQL statements that are often executed, special attention should be paid to large table operations:
l If there are many write operations, you can usually use the write cache method. First, cache the data that needs to be written or updated to a file or other table, and regularly perform batch write operations on large tables. At the same time, try to make the large tables that are often read and written as fixed-length type, even if the large tables in the original structure are not fixed-length. The fixed-lengthization of large tables can be achieved by changing the data storage structure and data reading method, splitting a large table into a fixed-length table with more reads and writes, and a variable-length table with more reads and writes less;
l If there are many read operations, you need to set the index and joint index of high-frequency SQL statements specifically for high-frequency.
Small tables are relatively simple, and adding specific indexes that meet the query requirements is usually more obvious. At the same time, fixed-length small tables are also beneficial to improving efficiency and load capacity. Small fixed-length tables with fewer fields may not even require indexes.
Secondly, see whether the conditions and sorting fields of SQL statements are very dynamic (that is, the SQL query conditions and sorting fields vary greatly according to different functional switches or attributes). SQL statements with excessive dynamicity cannot be optimized through indexes. The only way is to cache the data and update it regularly, which is suitable for situations where the results do not require high effectiveness.
MySQL indexes are commonly used in PRIMARY KEY, INDEX, and UNIQUE. For details, please refer to the MySQL documentation. Generally, when the single table data values are not duplicated, PRIMARY KEY and UNIQUE indexes are faster than INDEX, please use them as appropriate.
In fact, indexing consumes the read operation resources of conditional query and sorting and distributes them into write operations. The more indexes, the larger the disk space is consumed and the slower the write operation. Therefore, indexes must not be added blindly. The most fundamental starting point for the field indexing or not is still the probability of SQL statement execution, the size of the table, and the frequency of write operations.
5.3.4. Query Optimization
MySQL does not provide optimization functions for query conditions, so developers need to manually optimize the order of query conditions in the program. For example, the following SQL statement:
SELECT * FROM table WHERE a>'0' AND b<'1' ORDER BY c LIMIT 10;
In fact, no matter which condition a>'0' or b<'1' is in front, the results are the same, but the query speed is very different, especially when operating on large tables.
Developers need to keep this principle in mind: the first condition that appears must be the condition that filters and eliminates more results; the second condition appears second; and so on. Therefore, the distribution of values of different fields in the table has a great impact on query speed. The conditions in ORDER BY are only related to the index and have nothing to do with the order of the condition.
In addition to conditional order optimization, for fixed or relatively fixed SQL query statements, it can also optimize the index structure to achieve a very high query speed. The principle is: in most cases, the joint index established based on the order of the WHERE conditions and the order of the sorting fields of ORDER BY is the optimal index structure that matches this SQL statement. Although, in fact, we cannot only consider one SQL statement, nor can we create too many indexes without considering space usage.
Similarly, the above SQL statement is as an example. The optimal time when the records of the table table reach millions or even tens of millions, you can clearly see the speed improvement brought by index optimization.
According to the above two principles of conditional optimization and index optimization, when the value of the table table is the following scheme, the optimal conditional order scheme can be obtained:
Field a Field b Field c
1 7 11
2 8 10
3 9 13
-1 0 12
Optimal conditions: b<'1' AND a>'0'
Optimal index: INDEX abc (b, a, c)
Reason: b<'1' As the first condition, 75% of the results can be filtered out first. If a>'0' is used as the first condition, you can only filter out 25% of the results first
Note 1: Since field c does not appear in the condition, the conditional order optimization has nothing to do with it.
Note 2: The optimal index is derived from the optimal conditional order, not from the SQL statement in the example
Note 3: The index does not modify the physical order of the data storage, but a virtual pointer implemented through physical data corresponding to a specific offset.
The EXPLAIN statement is an easy way to detect whether the index and query can match well. Run EXPLAIN+ query statements in phpMyAdmin or other MySQL clients, such as EXPLAIN select * FROM table WHERE a>'0' AND b<'1' ORDER BY c; This form can verify whether the index is reasonable even if the developer does not need to simulate millions of pieces of data. Please refer to the MySQL instructions for details.
It is worth mentioning that Using filesort is the least expected situation. If EXPLAIN obtains this result, it means that the database has specially established a temporary table file for this query to cache the results and deleted after the query is completed. As we all know, hard disk I/O speed is always a bottleneck in computer storage. Therefore, in queries, we should try our best to avoid using filesort with high execution frequency SQL statements. Although, developers can never guarantee that all SQL statements in the product will not use filesort.
Due to space limitations, this document is far from covering all aspects of database optimization, such as: the reusability of joint indexes and ordinary indexes, the index design of JOIN connections, MEMORY/HEAP tables, etc. Database optimization is actually a continuous weighing and modifying many factors and pros and cons. Only by repeatedly pondering the experience of success and failure can we obtain experience. This kind of experience is often the most valuable and valuable.
5.3.5. Compatibility issues
Since MySQL 3.23 to 5.0 changes greatly, it is necessary to avoid using special SQL statements in the program to avoid compatibility issues and cause difficulties in database migration.
Usually in MySQL 4.1 or above, phpcms should use a comparable character set to store, such as GBK/BIG5/UTF-8. Although the traditional latin1 encoding has some compatibility, it is still not the recommended choice. When using the corresponding non-default character set, each time the program runs, it is necessary to use SET NAMES ‘character_set’; to specify the character set for connection, transmission and result.
Several new SQL_MODEs have been added to Mysql 5.0 or above. The default SQL_MODE varies according to the server installation settings. Therefore, each time the program runs, you need to use SET SQL_MODE=''; to specify the current SQL mode.
6. Template design
6.1. Code marking
HTML code tags are always in lowercase letters, eliminating any way to use capital letters
All logical bodies in the template, such as {if}, {loop}, etc., must be used before and after using HTML comments (<!----->), that is, a form similar to <!--{if expr}-->. In fact, the phpcms template compiler supports logical writing without HTML annotations, but adding comments can make the template readable better, and it is convenient for users to modify templates using DreamWeaver or FrontPage.
6.2. Writing rules
6.2.1. HTML
All HTML tag parameter assignments must be included in double quotes, for example, you should use
<input type=”text” name=”test” value=”ok”>
It is absolutely not available
<input type=text name=test value=ok>
。
In any case, the template files in the product must be written in handwritten HTML code, and must not be written or modified using automatic web page creation tools such as DreamWeaver and FrontPage.
6.2.2. Variables
The variables used in the template are divided into several ways according to their role and location:
l In the logic body, that is, the surrounded part, such as this form, the variable writing specifications in which are exactly the same as those in the PHP program;
Developers need to use {} to enclose variables to avoid template compilation errors. The possible situations are as follows:
l The variable contains brackets or other sensitive characters (including but not limited to "$", "'", etc.), and the correct way to write it is descriptionnew[{$buddy[buddyid]}];
l If the subscript of the array is a variable, the correct way to write it is {$extcredits[$creditstrans][title]};
l The situation where other variables are very complicated.
6.2.3. Language Elements
6.2.4. Indent
In the *.html template file of phpcms, since it has a logical structure, any indentation of HTML itself is not considered. All indentation means a logical indentation structure. Indentation is TAB, and spaces are not used as indentation symbols, and only needs to break the line appropriately. For example:
<!--{loop $articles $article}-->
<table cellspacing=”0” cellspadding=”0” border=”0”>
<tr><td>{$article[‘title']}</tr></td>
</table>
<!--{/loop}-->
7. Files and Directories
7.1. File Naming
All program files or semi-program files containing PHP code should be used with lowercase .php as the extension, rather than .phtml, .php3, .inc, .class, etc. as the extension.
Normal program
Programs that can be called directly by URLs, for example, are named directly using the program name +.php
Function library and class library programs
Lowercase and . are used as extensions respectively. Function libraries and class libraries can only be referenced by other programs and cannot be run independently. It cannot contain any process-based program code that does not belong to any function or class.
Process procedures
Use lowercase. as the extension. It can only be referenced by other programs and cannot be run independently. Program code that cannot contain any function or class code.
Template source file
Use lowercase .html as the extension. The template source file is written according to the phpcm template encoding rules. It is not an executable program, but can only be parsed by the phpcm template compiler and placed in other template directories under ./templates/default or ./templates.
Template target file
The target program that is automatically generated after the template file is compiled, is stored in the ./data/templates directory in the ./data/templates directory in the lowercase .php as the extension.
Language Pack File
In lowercase. as an extension, only the language package information used by the template or program can be stored.
Cache files
This type of file is automatically generated by the system, named in cache_xxx.php, usergroup_xxx.php, style_xxx.php, etc., and is stored in the ./data/cache directory.
7.2. Directory naming
The agreement in the previous "4.4 Naming Principles" of phpcms directory naming is the basic principle. When possible, it usually appears in plural forms, such as ./templates, ./images, etc.
Since the number of directories is small, directory naming is mostly a habit and convention. If developers need to create a new directory, they should consult with project team members and reach an agreement before implementing it.
7.3. Empty directory index
Please place a 1-byte file in all directories that do not contain ordinary programs (that is, programs that can be called directly by URLs), with a space. Almost all directories except the phpcms root directory belong to this type, so developers need to put all empty files in these directories to avoid the server files being indexed and listed when the Directory Listing of the http server is opened.
For sensitive directories such as attachment directories, to implement corresponding functions in the program, when creating a new lower-level directory, an empty file must be automatically written to avoid the problem of the new directory being indexed.