MySQL数据库查询索引失效场景

在连表情况下,如果排序字段涉及到了两个表,排序字段将无法走索引.

加上第二个排序字段之后,走全表扫描了.

或者尽量让两次排序都用同一个表的字段,这样可以建联合索引让排序也能走索引.（不想建联合索引的话，可以第二次排序用表id，这样单个的索引也能实现二次排序）

使用了GROUP BY 导致排序字段索引失效

加了分组之后,order_no和school_customer_id都是有建索引的

连表字段编码方式不一致导致索引失效

如下图,如果o.id和op.order_id的编码方式不一样的话,索引会失效,并且这个索引失效通过查看执行计划看不出来.

LIKE查询左侧使用通配符

LIKE查询的时候如果左侧使用%或者_,索引会失效.

但如果左侧是具体的值,则还可以走索引.

使用!=或<>

联合索引不符合最左前缀

比如新建一个联合索引idCard_email,

如果直接查email是不会走该索引的.

但如果查询联合索引左侧的idCard则会走索引

或者两个字段都查的时候也走索引,两个字段都查的时候,SQL执行器会优化查询顺序的.

条件字段类型不一致

数据库字段是varchar,传入数字类型的话索引会失效

条件匹配对数据库字段使用了函数

范围查询的时候数据量超过达到全表的10%~30%,优化器会转用全表扫描

非SQL层面的优化

可以考虑增大buffer pool的大小,单位是Byte

mysql> show global variables like 'innodb_buffer_pool_size';
+-------------------------+-----------+
| Variable_name           | Value     |
+-------------------------+-----------+
| innodb_buffer_pool_size | 134217728 |
+-------------------------+-----------+
1 row in set (0.01 sec)

mysql> set global innodb_buffer_pool_size = 536870912;
Query OK, 0 rows affected (0.01 sec)

mysql> show global variables like 'innodb_buffer_pool_size';
+-------------------------+-----------+
| Variable_name           | Value     |
+-------------------------+-----------+
| innodb_buffer_pool_size | 536870912 |
+-------------------------+-----------+
1 row in set (0.01 sec)

如果数据库连接数不够用,还可以考虑增加数据库最大连接数

查看当前数据库连接使用情况

show status like  'Threads%';

Threads_connected ：这个数值指的是打开的连接数.
Threads_running ：这个数值指的是激活的连接数，这个数值一般远低于connected数值.

查看最大数据库连接数

show variables like '%max_connection%';

set global max_connections=1000;