Optimize Big Data Table query and data table Optimization

Optimize Big Data Table query and data table Optimization

1: Index, the first thing we think of first is creating an index. Creating an index can multiply the query efficiency and save time. However, if the data volume is too large, simply creating an index will not help. We know that if we count the query in a large amount of data, take million data for example, if you use the count function, it will take at least 50 to seconds. Of course, if your server configuration is high enough and the processing speed is fast enough, it may be much less, but it will take more than 10 seconds.
Creating an index is useless. We can add a compress attribute to the index when creating the index. This attribute can well classify the created index. In this way, the query speed will increase by 5-10 times, or higher. However, the only drawback is that the compressed index can only be created manually and cannot be compressed for those keys, because the KEY (primary KEY) is an automatically created index and a mandatory attribute of compress, generally, it is not created by default. Therefore, when creating a compressed index, you can find other key fields for compression, such as the serial number in the ticket table.
2: Use as few functions as possible, such as IS NUll; is not null, IN; not in, and so on. You can use a symbolic program to perform operations.
3: Try to use as few subqueries as possible. If you write a class that imitates the subquery effect, you will find that the subquery is terrible. We can use the combined query, or external connection query, which is much faster than subquery.
4: when using indexes, note the following:
The Where clause contains "! = "Will invalidate the index
Select account_name from test where amount! = 0 (not used)
Select account_name from test where amount> 0 (used)

Adding a handler to a field in the Where condition will not use the column Index
Select * from emp where to_char (hire_date, 'yyyymmdd') = '123' (not used)
Select * from emp where hire_date = to_char ('201312', 'yyyymmdd') (use)

Avoid using is null and is not null in the index column.
Select * from emp where dept_code is not null (not used)
Select * from emp where dept_code> 0 (used)

Use of wildcard %
Select * from emp where name like '% A' (no index is used)
Select * from emp where name like 'a % '(using indexes)