Shulou(Shulou.com)06/01 Report--

This article is to share with you about the ways to optimize MySQL. The editor thought it was very practical, so I shared it with you as a reference. Let's follow the editor and have a look.

MySQL optimization

1. Optimize indexes, SQL statements, and analyze slow queries

two。 When designing tables, the database is designed strictly according to the design paradigm of the database.

3. We can also cache, statically and distribute our business architecture

4. Do not use full-text indexing, use an index on Xunsearch,ES or a cloud server

5. If the efficiency is still not good enough, the data can be read and written separately in a master-slave manner.

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

7. We can also optimize the hardware. At the hardware level, we can use some better hard drives (solid state drives) and some disk array technology (raid0,raid1,raid5).

-raid0: the simplest (two pieces of hardware plus 100G+100G=200G)

-raid1: mirror the volume and put two copies of the same data. It can be read from Ascarb immediately, it is more efficient, and even a piece of data will not be lost if the hard drive is broken.

-raid5:3 block hard drive, one piece is broken, the other two pieces can also work.

8. If it is still slow, do not split the table first. You can use the table partitioning technology that comes with MySQL to divide the data into different files, which can make the disk more efficient when reading.

9. You can do a vertical table and put the data that you read infrequently into another table (save disk Icano)

10. The amount of data is very large, and it will be very difficult for us to optimize it. At this time, we can use database middleware to divide the data into databases and tables. (principle: data routing)

11. In addition, some faster storage methods can be used, such as NoSQL to store some data that we need to access frequently (after the database is taken out, we can retrieve some other data from the NoSQL)

twelve。 In addition, there are some table engine choices, parameter optimization and some related tips are all ways to optimize MySQL.

Slow query: refers to the SQL statement query that exceeds the specified time. The method of analyzing the query performance of MySQL statement not only enables explain to output the execution plan, but also allows MySQL to record statements that have exceeded the specified time.

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

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

The advantage of table engine is that ISAM is fast to read, does not take up a lot of memory and storage resources, does not support transactions, and cannot tolerate faults. If the hard disk crashes, the data file cannot be restored (often backed up) the MYISAMISAM extension format and the default data engine, providing a large number of functions of index and field management that are not available in ISAM. MYISAM does not support transactions by using table locks, does not support foreign keys INNODB and BERKLEY (BDB) to support transactions, and supports foreign key data reading slower HEAP allows temporary tables that only reside in memory, so data managed faster than ISAM and MYISAM are unstable. If the data is not saved before shutting down, all data will be lost (delete the form after the form is used up) It is very useful when you need select expressions to select and manipulate data. The first normal form (1NF) is atomic, that is, the information of the column can no longer be divided [column unique] the second normal form (2NF) is based on the first normal form, and each instance or row of the data table must be uniquely distinguished (primary key on each row) [row unique] third normal form (3NF) is based on the second normal form, a database. Does not include non-primary key columns that are already in other tables

Expand:

The mobile phone does not support session and cookie. What should I do to achieve user login?

1. Use Restful at the protocol

two。 Change to token (similar to the appid used in Wechat development)

Thank you for reading! About which methods to optimize MySQL to share here, I hope the above content can be of some help to you, so that you can learn more knowledge. If you think the article is good, you can share it and let more people see it.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.