This is distributed by the database. The application is transparent. There is no need to modify anything in the code.
2.2 internal documents
Go to the data directory first. If you don't know the directory location, you can:
Next, look at the internal files:
As can be seen from the above figure, there are two types of files frm files and ibd file
- **. frm file: * * table structure file
- **. ibd file: * * in InnoDB, the index and data are in the same file ibdata (your execution results may be. MYD index files and. MYI data files. It doesn't matter. This is the MyIsAm storage engine, corresponding to the. ibd file of InnoDB). Because the Order table is divided into five areas, there are five such files
- **. Par file: * * the results of your execution may be different The par file may not exist. Note: from MySQL 5.7 6 start, no longer create Par partition definition file. Partition definitions are stored in the internal data dictionary.
2.3 data processing
After partitioning the table, MySql performance is improved. If there is only one watch, there is only one ibd file, a large B + tree. If the table is divided, it will be divided into different zones according to the partition rules, that is, a large B + tree will be divided into multiple small trees. How many rows of data can an InnoDB B + tree store?
The reading efficiency must be improved. If the partition key index is used, the auxiliary index B + tree of the corresponding partition shall be used first, and then the clustered index B + tree of the corresponding partition shall be used.
If you do not press the partition key, it will be executed once in all partitions. It will cause multiple logical IO! In normal development, if you want to view partition queries of sql statements, you can use the explain partitons select xxxxx statement. You can see that a select statement takes several partitions.
mysql> explain partitions select * from TxnList where startTime>'2016-08-25 00:00:00' and startTime<'2016-08-25 23:59:00'; +----+-------------+-------------------+------------+------+---------------+------+---------+------+-------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------------------+------------+------+---------------+------+---------+------+-------+-------------+ | 1 | SIMPLE | ClientActionTrack | p20160825 | ALL | NULL | NULL | NULL | NULL | 33868 | Using where | +----+-------------+-------------------+------------+------+---------------+------+---------+------+-------+-------------+ row in set (0.00 sec)
III Sub database and sub table
When a table develops with time and business, the amount of data in the library table will become larger and larger. Data operations will become larger and larger. The resources of a physical machine are limited, and the amount of data that can be carried and the processing capacity of data will be limited. At this time, sub database and sub table will be used to undertake super large-scale tables, which can not be put on a single machine.
Different from partitions, partitions are generally placed in a single machine, and more time range partitions are used to facilitate archiving. The only thing is that database and table partitioning need code implementation, and partitioning is an internal implementation of mysql. The sub database, sub table and partition do not conflict, and can be used together.
Failed to transfer deposit. Re upload is cancelled
3.1 implementation
3.1. 1. Standards for sub warehouse and sub table
- Storage occupation: 100G+
- Data increment: 200w per day+
- The number of single tables is 100 million+
3.1. 2. Sub database and sub table fields
The value of sub database and sub table fields is very important
- In most scenarios, this field is a query field
- Numerical type
userId is generally used to meet the above conditions
3.2 distributed database middleware
There are two kinds of distributed database middleware, proxy and client architecture. Proxy modes include MyCat and DBProxy, and client-side architectures include TDDL and sharding JDBC. So what's the difference between proxy and client architecture? What are their advantages and disadvantages? In fact, you can know by looking at a picture.
In the proxy mode, our select and update statements are sent to the proxy, which operates the specific underlying database. Therefore, the agent itself must be required to ensure high availability, otherwise the database will not be down and the proxy will hang up, which will go far.
The client mode is usually encapsulated on the connection pool, internally connected with different libraries, and the sql is handed over to the smart client for processing. Usually only one language is supported. If other languages are to be used, multilingual clients need to be developed.
Their advantages and disadvantages are as follows:
3.3 internal documents
Find an example of sub database and sub table + partition, which is basically the same as that of partition table, but there are many more tables ibd file, with the explanation of the file:
[miaojiaxing@Grim testmydata]# ls | grep 'base_info' base_info_00.frm base_info_00#P#p_2018.ibd base_info_00#P#p_2019.ibd base_info_00#P#p_2020.ibd base_info_00#P#p_2021.ibd base_info_00#P#p_init.ibd base_info_00#P#p_max.ibd base_info_01.frm base_info_01#P#p_2018.ibd base_info_01#P#p_2019.ibd base_info_01#P#p_2020.ibd base_info_01#P#p_2021.ibd ## last In view of the fact that many people are interviewing recently, I have also sorted out a considerable number of interview special materials, as well as the experience of other large factories. I hope I can help you. > The latest interview questions >  > The answers to the above interview questions are compiled into document notes. > I also sorted out some interview materials&The latest 2021 collection of real interview questions from some large factories,**If necessary, you can[Click here to get it for free](https://gitee.com/vip204888/java-p7)** > Latest finishing e-book  > Organize the latest interview documents of large factories  p7)** > Latest finishing e-book [External chain picture transfer...(img-d8v5lTRd-1628620324223)] > Organize the latest interview documents of large factories [External chain picture transfer...(img-3yooigdZ-1628620324224)] The above is the whole content of this article. I hope it will be helpful to your study and I hope you can support it.