mysql普通索引效率

Question

select * from post where zhuanid = 0 and catid <> 10 and empty =0;
1、本SQL语句：唯一性太差的还要建索引吗 比如empty 就是0或者1 catid一共就8个数 但表数据量是上百万

2、还有2个及2个以上条件查询的 必须要将全部字段建一个复合索引吗

3、如果如上三个字段分别建了一个普通索引 这个and会使用索引吗

对多个查询条件的索引概念比较模糊 但我现在数据库的CPU非常高

Answer 1

Detailed analysis of specific issues. If the uniqueness is too poor, there is no need to create an independent index. It is recommended to create a joint index and adjust the query statement. (zhanid,catid,empty). The statement is rewritten as zhuanid = 0 and catid in(a,b,c,d,e) and empty =0

Answer 2

<> Does this kill the index?

Negative query conditions: NOT, !=, <>, !<, !>, NOT IN, NOT LIKE, etc., will result in full table scan

Answer 3

select It is worth doubting whether is necessary. If indexed separately, the mysql optimizer will make its own judgment. You can use covering index