Mysql索引研究总结

闲来无事，研究了一下mysql索引，场景如下：

有一张MyISAM 类型的zt_action表，数据大约230W行，建两个索引，
CREATE INDEX `read` ON zt_action(`read`)

CREATE INDEX `Type_Id_read` ON zt_action(`objectType,objectID,read`)

CREATE INDEX `actor_action_date` ON zt_action(`actor,action,date`)

EXPLAIN SELECT objectType FROM zt_action WHERE objectType='user' AND objectID=1 AND `read`='0'    ('Using where; Using index')     使用索引Type_Id_read，type为ref
EXPLAIN SELECT * FROM zt_action WHERE `read`='3'     ('Using index condition')   使用索引read，然后为了select * ,所以进行回表操作
EXPLAIN SELECT * FROM zt_action WHERE `read`='0' AND objectType='user'     ('Using index condition')   使用索引Type_Id_read，然后进行回表操作
EXPLAIN SELECT `read` FROM zt_action      ('Using index')  未使用索引，但是遍历了索引表read
EXPLAIN SELECT `read` FROM zt_action WHERE `read`='0'       ('Using where; Using index')  使用索引read
EXPLAIN SELECT `read` FROM zt_action WHERE `read`='0' AND objectType='user'    ('Using index condition; Using where') ('Using where; Using index')Type_id_read  使用索引read, 这个不清楚了
EXPLAIN SELECT `read` FROM zt_action WHERE objectID=1   ('Using where; Using index')  未使用索引，但是遍历索引表Type_Id_read
EXPLAIN SELECT `date` FROM zt_action WHERE objectID=1   ('Using where')  未使用索引，全表扫描
EXPLAIN SELECT `read` FROM zt_action WHERE objectType='user'   ('Using where; Using index')  使用了索引Type_Id_read
EXPLAIN SELECT `date` FROM zt_action WHERE `action` LIKE '%ct%';    ('Using where; Using index')   未使用索引，但是遍历了索引表actor_action_date

转载于:https://www.cnblogs.com/mei0619/p/8377357.html