Learn how MySQL is optimized

MySQL Optimization

• 1. Optimize indexes, SQL statements, analysisSlow query；

• 2. When designing tables, strictly follow the database design paradigm to design the database;

• 3. We can also use our The business architecture is cached, static and distributed;

• 4. Instead of full-text indexing, use Xunsearch,ESOr index on the cloud server;

• 5. If the efficiency is still not good enough, you can use the master-slave method to separate data reading and writing;

• 6. You can add memcached cache to put frequently accessed but infrequently changed data into the memcached cache server, which can save disk I/O;

• 7. Hardware can also be optimized. At the hardware level, we can use better hard drives (solid-state drives) and some disk array technologies (raid0, raid1, raid5)
  - raid0: the simplest (two pieces of hardware add 100G 100G=200G)
  - Raid1: Mirror volume, copy the same data into two copies. It can be read from A/B immediately, which is more efficient. Even if the hard disk is damaged, a piece of data will not be lost;
  - raid5: 3 hard drives, if one is broken, the other two can still work.

• 8. If it is still slow, don’t split the table first. You can use MySQL’s own table partitioning technology to divide the data into different files. This will allow the disk to read more quickly. It is more efficient;

• 9. You can divide the table vertically and put the data that is not frequently read into another table (saving disk I/O);

• 10. The amount of data is extremely large, and it will be difficult for us to optimize it. In this case, we can use database middleware to divide the data into databases, tables, and machines. (Principle: data routing);

• 11. In addition, some faster storage methods can also be used, such as NoSQL to store some data that we need to access frequently (after taking out the database, Then take out some other data from NoSQL);

• 12. In addition, there are some table engine options, and parameter optimization is also related. Tips are all ways to optimize MySQL;

---

Slow query: refers to the SQL statement query that exceeds the specified time. The method to analyze the performance of MySQL statement query is besides making explain output The execution plan also allows MySQL to record queries that exceed the specified time.

Xunsearch: A free and open source professional full-text search solution designed to help general developers quickly and easily build their own full-text search engines for existing massive data. Full-text search can help reduce the search load on the server and greatly improve search speed and user experience.

ElasticSearch: A search server based on Lucene. It provides a distributed multi-user capable full-text search engine based on a RESTful web interface.

##ISAMThe reading speed is fast and does not occupy a lot of memory and storage resourcesDoes not support transactions and cannot be fault-tolerant; if the hard disk crashes, the data files cannot be restored (regular backup)MYISAMISAM extended format and default data engine provide a large number of index and field management functions that are not available in ISAM. MYISAM uses table locksDoes not support transactions, does not support foreign keysINNODB and BERKLEY (BDB)Supports transactions, supports foreign keysData reading is relatively slow SlowHEAPAllows temporary tables that only reside in memory, so it is faster than ISAM and MYISAMThe data managed is not Stable, if the data is not saved before shutting down, all data will be lost (delete the table after the table is used up); it is very useful when you need select expressions to select and manipulate data

Table Engine	Advantages	Disadvantages

---

Normal FormFeaturesFirst Normal Form (1NF) has atomicity, that is, the information in Second normal form (2NF) Based on the first normal form, each instance or Triple Normal Form (3NF)Based on the second normal form, a database that does not contain non-primary key columns that are already in other tables

• Extension:
  What should I do if the mobile terminal does not support session and cookie to achieve user login?
  1. Use Restful at the protocol;
  2. Change token (similar to the appid used during WeChat development);

  This article explains how to optimize MySQL. For more related content, please pay attention to php Chinese net.

  Related recommendations:

  Simple PHP MySQL paging class

  Convert HTML to Excel, and realize printing and downloading functions

  Upload files through CURL in php
  

	column cannot be further divided [unique column]
	row of the data table must be uniquely distinguished (a primary key is built on each row) [row unique]

The above is the detailed content of Learn how MySQL is optimized. For more information, please follow other related articles on the PHP Chinese website!