MySql 索引学习记录

MySql 索引学习记录索引分类聚集索引非聚集索引1.主键索引2.唯一索引3.普通索引4.全文索引索引失效场景1.违反最左前缀法则2.使用不等于操作符不等于操作符!会使索引失效或者对索引列使用函数计算3.以%开头的Like模糊查询索引失效4.索引列如果是字符串查询时不使用引号会导致全表扫描5.使用OR条件没有所有索引列使用OR条件时只有当OR条件中的所有列都有索引时索引才会生效6.复合索引中使用了范围查询复合索引中如果有范围查询如BETWEEN, , ,LIKE则其右边的列索引失效7.oder by当对主键排序时可以命中主键索引其他索引普通、唯一等都会失效Explain 关键字的使用回表查询索引分类聚集索引将数据存储与索引放到了一块索引结构的叶子节点保存了行数据有且只有一个聚集索引选取规则:如果存在主键主键索引就是聚集索引。如果不存在主键将使用第一个唯一UNIQUE索引作为聚集索引。如果表没有主键或没有合适的唯一索引则InnoDB会自动生成一个rowid作为隐藏的聚集索引非聚集索引将数据与索引分开存储索引结构的叶子节点存放的是对应的主键id因此可能涉及回表查询1.主键索引2.唯一索引唯一索引是允许索引列有null值的且可以存在多个null3.普通索引4.全文索引索引失效场景1.违反最左前缀法则例如组合索引no_age_name 当查询字段顺序不是按照这个来则会导致索引失效2.使用不等于操作符不等于操作符!会使索引失效或者对索引列使用函数计算eg:SELECT * FROM users WHERE age 30; 此时age索引失效3.以%开头的Like模糊查询索引失效4.索引列如果是字符串查询时不使用引号会导致全表扫描egSELECT * FROM t_user WHERE mobile 189; mobile是字符串此时索引就会5.使用OR条件没有所有索引列使用OR条件时只有当OR条件中的所有列都有索引时索引才会生效eg: SELECT * FROM users WHERE age 30 OR username alice; 因为name没有索引导致age的索引也会失效6.复合索引中使用了范围查询复合索引中如果有范围查询如BETWEEN, , ,LIKE则其右边的列索引失效no_age_name 当对age使用上述操作符时name的索引就会失效7.oder by当对主键排序时可以命中主键索引其他索引普通、唯一等都会失效Explain 关键字的使用possible_key当前sql可能会使用到的索引key当前sql实际命中的索引key_len索引占用的大小ref使用的常数等值查询这里会显示const。否则可能展示为funcrows这里是执行计划中估算的扫描行数不是精确值typesql的连接的类型性能由好到差为NULL、system、const、eq_ref、ref、range、 index、allNULL查询没有使用到表通常不会有system查询系统中的表通常不会有const根据主键查询或唯一索引且返回数据只有一行时唯一索引当查询到多条数据(null) 时也不会consteq_ref主键索引查询或唯一索引查询ref索引查询range范围查询 ----------- 最差也要命中这个index索引树扫描all全盘扫描回表查询eg: t_user 表有idname, age, phone 等字段现在有复合索引(name_age)现查询select * from t_user where name like “张%” and age 10的 数据1.server层在执行时会先生成执行计划优化器会判断是否覆盖了所有查询列包括select和where的.2.5.6版本有ICP(索引下推): 扫描到name符合条件的记录并判断age是否满足如果满足则回表查询反之丢弃。5.6版本之前不具备该功能是直接回表3.提交存储引擎查询4.如果索引覆盖server会指示引擎扫描索引并返回索引的数据即可5.引擎根据索引查询到对应的数据并返回给server6.server层收到数据后可能需要做其他过滤如果 WHERE 条件未完全利用索引但无需再回表因此在使用复合索引时谨慎使用范围查询。注IN不会因为优化器会生成namexx or name xx多个值会使用or替代。当in覆盖了全表大部分数据时mysql也可能基于情况全表扫描放弃age的索引