What is a federated index?
Indexes built on multiple fields can speed up the retrieval of rechecking query conditions.
For example, the login service requirements are as follows:
select uid, login_time from t_user where
login_name=? and passwd=?
You can create a joint index (login_name, passwd).
Joint index can meet the demand of the left-most query, for example (a, b, c) a combination of three columns index, accelerate a | | (a, b) (a, b, c) three sets of query requirements.
This is why the joint index (passwd, login_name) is not created. There are few single-condition query requirements for passwd, but many single-condition query requirements for login_name.
In a SQL statement, does the order of and in the WHERE condition affect the index hit?
No (this is where many friends get it wrong).
Here’s an example:
select uid, login_time from t_user where
passwd=? and login_name=?
Can the union index (login_name, passwd) be matched?
Yes, the left-most query requirement does not mean that the SQL statement must be written in order of the index.
What are the primary key and unique index constraints? MySQL primary key and unique index constraints unknown primary key and unique index constraints
Does type mismatch and character encoding mismatch affect index hits?
Yes, see “two types of very covert full table scan, index hit”, introduced in more detail.
Negative query, IN, OR, UNION which will affect index hits?
Here’s an example:
Assume that the order business table structure is:
Order (OID, date, UID, status, money, time,…)
Related fields are also indexed.
Suppose an order has three states: 0 placed, 1 paid, and 2 completed
Business requirements, query outstanding orders, which SQL is faster?
(1)select * from order where status***! * * * = 2
(2)select * from order where status IN*(0,1)*
(3)select * from order where status=0 or status=1
(4)select * from order where status=0
*union all*
select * from order where status=1
Select * from order where status! = 2
Negative queries must not hit the index, undisputed.
Voice-over:
Leading fuzzy queries, like ‘%XX’, do not match indexes;
Non-leading fuzzy queries, like ‘XX%’, can hit indexes.
One last one:
select * from order where status=0
union all
select * from order where status=1
The union of two SQL result sets can definitely hit the status index, without dispute.
IN the query:
Select * from order where status in (0,1)
Try to explain? It is possible to hit the index.
The OR query:
select * from order where status=0 or status=1
New version of MySQL, can be optimized to IN query, so also can hit the index.
Some other practices:
(1) Index should not be used for fields with little data differentiation.
Such as:
select * from user where sex=1
Because the gender is only male and female, little data is filtered out each time, and the performance is similar to that of full table scan.
(2) The calculation on the attribute cannot match the index.
select * from order where YEAR(date) < =’2019′
Mysql > select * from date; mysql > select * from date; mysql > select * from date;
select * from order where date < =CURDATE()
Or:
select * from order where date < =’2019-10-24′
The latter two can hit the index.
(3) If the column allows NULL values, the query will have a huge pit.
Such as:
select * from user where name ! = ‘shenjian’
Suppose the data table has:
(1, shenjian)
(2, zhangsan)
(3, lisi)
(4, null)
What records can you guess will be in the result set? Voiceover: ** “Databases allow null values, often the beginning of tragedy”.