Personal selection of storage engines, build tables, build indexes, SQL optimization of some summary, to provide some reference for readers

Recommended to visit my personal website, typesetting better look:Chenmingyu. Top/mysql – the optim…

The storage engine

Mysql > support engine SQL

show engines; 
Copy the code

Comparison of storage engines used in daily work: InnoDB, MyISAM

InnoDB MyISAM
Storage limits 64T 256T
Support transactions yes no
Support the index yes yes
Full text index support no yes
Data caching support yes no
Support foreign keys yes no
Hash index support no no

InnoDB has supported full-text indexes since MySQL5.6

innodb

Transactional security (ACID) with commit, rollback, and crash recovery capabilities, row locking, and foreign key integrity constraints

Suitable for the scene

  • Need transaction processing
  • Large amount of table data, high concurrency operation
MyISAM

MyISAM storage engine provides high-speed retrieval and storage capabilities that support full-text indexing

Suitable for the scene

  • A lot of counts
  • Queries are very frequent

Other storage engines

The MEMORY engine

Data is only stored in memory, because it is in memory, has a high efficiency of insert, update, query, but after the restart of the data will be lost, table level lock, low concurrency performance.

The MERGE engine

Merge a merge table is a group of MyISAM tables, so there is no data in the merge table. The operation on the merge table is actually the operation on the internal MyISAM table. Merge multiple MyISAM tables to do some reporting operations.

ARCHIVE the engine

Only support insertion and query, using zlib compression library, when the record is requested real-time compression, does not support transactions, support row-level locking, suitable for storing a large number of log data.

Personally, I recommend Innodb engine, and it is also stipulated in the company that Innodb engine must be used when creating tables. Innodb engine can provide more functions than MyISAM engine. Caching can be used for non-real-time query scenarios, and ES can be used for near-real-time query, of course, this is just my opinion. It is necessary to choose different storage engines for different scenarios. Therefore, you can select a proper storage engine based on service requirements after knowing the features of different storage engines.

Principle of building table

Try to follow these guidelines when building a table

  1. Tinyint (1 byte)> SmallINT (2 bytes)>int(4 bytes)> BigINT (8 bytes). For example, if the yn field is logically deleted (1 indicates available, 0 indicates available), tinyInt (1 byte) can be selected

  2. Try to keep the length of the field data type fixed

  3. Avoid using NULL as it is difficult to optimize a field query using NULL, affecting the index. Use 0 or ” instead

  4. Avoid wide tables and split them if you can. A table usually corresponds to a single entity domain, just like when designing objects

  5. Avoid using text and BLOb. If you must, separate the fields of type text and blob into a new table and use the primary key corresponding to the original table

  6. Do not use float or double, this pit is too large, float or double has precision problems, when comparing or adding or subtracting operations will lose precision and result in data exception, when using float or double consider whether to use int or bigint instead. For example, when using the dollar as a float or double, consider using the int and bigint instead, and then converting the units by the business code.

  7. Every table plus createUser, createTime updateUser, updateTime fields

  8. Name to standard, including: library name, table name, field name, index name

  9. Index frequently used fields

  10. Try to avoid the use of foreign keys, no foreign key constraints, higher performance, and data integrity is managed by the program

  11. If the number of tables can be predicted to be very large, it is best to divide the table when the table is being built, so as not to cause efficiency problems due to the large amount of data at a time

    To be added,,

The index

An index is a kind of scattered data result created to speed up the retrieval of data rows in the table. It is built for the table. It is composed of index pages outside the data page. Innodb indexes are implemented based on B Tree

The index type

Normal index: The most basic index, unlimited

CREATE INDEX idx_username ON sys_user(user_name(32)); ALTER table sys_user ADD INDEX idx_username(user_name(32))Copy the code

Primary key index: A table can have only one primary key index and cannot be empty

The primary key index is usually created when the table is created

CREATE TABLE `sys_user` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `user_name` varchar(32) DEFAULT NULL,
  `pass_word` varchar(32) DEFAULT NULL,
  `token` varchar(32) DEFAULT NULL,
  `token_expire` int(11) DEFAULT NULL,
  `yn` smallint(6) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=348007 DEFAULT CHARSET=utf8;
Copy the code

Unique index: Similar to a normal index, except that the value of the index column must be unique, but empty values are allowed. If it is a composite index, the combination of column values must be unique

CREATE UNIQUE INDEX idx_token ON sys_user(token_expire)
Copy the code

Combined index: indicates an index created on multiple fields. The index can be used only when the first field is used in the query condition. Follow the leftmost prefix set when using composite indexes

ALTER TABLE sys_user ADD INDEX idx_un_te (user_name(32),token_expire); 
Copy the code

Full-text indexes: Used to find keywords in text rather than compare them directly to values in the index. You can create full-text indexes only on char, vARCHar, and text columns

CREATE FULLTEXT INDEX idx_ ON sys_user(pass_word)
Copy the code
Create rules for using indexes

  1. The index field should be as small as possible. The speed of querying data according to the index depends on the height of b tree. When the data amount is constant, the fewer bytes, the more indexes are stored, and the lower the height of the tree is

    For example, if vARCHAR (10) is set, only the first 10 bytes of the field will be stored when the index is created. If the number of bytes of the field is set too small, the index may find too much data, and then return to the table, resulting in performance degradation. Therefore, the value of the field should be decided by yourself

  2. Follow the leftmost matching rule for indexes

  3. Do not use “%a%” when using “like”. Instead, use “A %” to go through the index

  4. Do not perform calculations on index columns, such as select * from sys_user where token_EXPIRE +1 = 10000. Such statements do not carry indexes

  5. Where, group by, order by, etc

  6. Try to consider index push-down optimization when using federated indexes

  7. For conditions that use OR, the index will be used only when the conditions around OR are indexes. Otherwise, union can be used instead of full table scan

  8. Avoid the use of SELECT *. If you only need to query the primary key or only have the index in the WHERE condition, this will overwrite the index to create less table times

  9. In MySQL, the comparison between a string and a number is performed by converting a string to a number. For example, if the field is of type VARCHar, but the input parameter is of type int, the field will not be converted even if it has an index, because an implicit conversion is performed

In short, the use of index, need to consider more places, but in the final analysis is to query as far as possible index, index as far as possible to avoid back to the table or reduce the number of back to the table

Disadvantages of indexes

Although indexing can improve query efficiency, insert, UPDATE, and DELETE operations are inefficient because indexes are saved along with data.

Don’t build too many index in a list, problems like the above, the operating data of time efficiency is lower, and the data quantity is little table depends indexed, if indexed with without the effect of the indexed almost less don’t indexed, if it is a large quantity of data table, you need to build the index to optimize the query efficiency.

Analysis the explain SQL

You can use Explain to analyze SQL execution, for example

explain select * from sys_user where token_expire = 10000; 
Copy the code

As mentioned in ali’s development manual, the standard of SQL performance optimization: at least range, ref level, conSTS is the best

For clarification, the level here refers to the type field above:

  • A consts is a table with at most one matching row (primary key or unique index)
  • Ref refers to using a normal index
  • Range refers to a range query on an index

SQL optimization

The optimization of SQL statements is mainly two aspects, one is the construction of SQL when the need to pay attention to the problem, the other is in the discovery of slow SQL can be analyzed according to different situations, and then optimize SQL

Suggestions for optimization
  1. Always use limit when querying
  2. Limit 1 is used for queries that require only one piece of data as a result
  3. usecount(*)Or count (1) to count the number of rows to query, using count (column), need to check whether the column in this is null, this as a null, not statistics and mysql alreadyOn the count (*)optimized
  4. Do not use select * to query data, use the column name required by select
  5. Use join links instead of subqueries
  6. Do not use foreign keys, the constraints of foreign keys can be solved in the program
  7. Control the number of sets of in operations so that they are not too large
  8. Use Explain to analyze the cause of slow queries, and then optimize the SQL to go as far as possible by index

The above four aspects is my attention to all aspects of SQL optimization, I hope to provide you with a reference, there are problems can be pointed out, exchange