山西中交建设工程招标有限公司网站,基金会网站开发方案,开发公司成本部年终总结,网站建设公司上海做网站公司排名索引1. 索引怎么建好#xff1f;2. 索引容易失效的场景3. 连接查询索引优化4. order by#xff0c;group by5. 覆盖索引6. 索引下推1. 索引怎么建好#xff1f;
单表 主键必须唯一#xff0c;且单调递增有唯一键的#xff0c;尽量建立唯一键where条件用得比较多的字段查询…
索引1. 索引怎么建好2. 索引容易失效的场景3. 连接查询索引优化4. order bygroup by5. 覆盖索引6. 索引下推1. 索引怎么建好
单表 主键必须唯一且单调递增有唯一键的尽量建立唯一键where条件用得比较多的字段查询条件多的用联合索引如果带有范围条件的将这个列放在联合索引的后面 多表查询时候的索引 on条件后面列建立索引select a.a1, a.a2, b.b1, b.b2 from a join b on a.a1 b.b1; 这种sql两个列的类型要相同。多用join代in查询 其它 order by, group by的列UPDATE、DELETE 的 WHERE 条件列
2. 索引容易失效的场景
①. 全值匹配, 联合索引下每个列都用上索引
②. 最左匹配原则
create index idx_name_age_classId on student(name, age, classId);-- 不生效
explain select * from student where age 1;查询上面的语句不会走索引, 如果是name作为条件索引才会生效
下面走了索引name age
explain select id, name, age, classId from student where name jack and age 1;③. 排序字段应该放最后
# age 作为排序字段放到后面
create index idx_cid_name_age on student(classId, name, age);explain select * from student where age 48 and classId 99 and name jack;④ 不等于, (, !) 索引失效
⑤ is null 有效, is not null 索引失效
⑥ like查询以通配符开始的失效如果不走索引覆盖的话。用es。
⑦ 函数导致索引失效
⑧ or 前后存在非索引的列, 索引失效
⑨ 数据库和表的字符集统一使用utf8mb4
3. 连接查询索引优化
① 左连接 左边的表是驱动表 右边的表是被驱动表. ② 内连接, 小表驱动大表, 小表是结果集少的 ③ 为join的字段创建索引并且join的字段类型要一致 保证被驱动表的JOIN字段已经创建了索引 需要JOIN 的字段数据类型保持绝对一致。 LEFT JOIN 时选择小表作为驱动表 大表作为被驱动表 。减少外层循环的次数。 INNER JOIN 时MySQL会自动将 小结果集的表选为驱动表 。选择相信MySQL优化策略。 能够直接多表关联的尽量直接关联不用子查询。(减少查询的趟数) 不建议使用子查询建议将子查询SQL拆开结合程序多次查询或使用 JOIN 来代替子查询。 衍生表建不了索引。 4. order bygroup by 排序, 单个字段, 请建立索引。多个字段建立联合索引。 尽可能的使用index索引排序, 如果不能, 使用file sort 文件排序,加大内存参数 order by 有两种排序方法1走索引2file sort 内存排序。索引快如果用内存排序吃资源。 SQL 中可以在 WHERE 子句和 ORDER BY 子句中使用索引目的是在 WHERE 子句中 避免全表扫描 在 ORDER BY 子句 避免使用 FileSort 排序 。当然某些情况下全表扫描或者 FileSort 排序不一定比索引慢。但总的来说我们还是要避免以提高查询效率。尽量使用 Index 完成 ORDER BY 排序。如果 WHERE 和 ORDER BY 后面是相同的列就使用单索引列如果不同就使用联合索引无法使用 Index 时需要对 FileSort 方式进行调优。 group by 也请走索引… group by 使用索引的原则几乎跟order by一致 group by 即使没有过滤条件用到索引也可以直接使用索引。 group by 先排序再分组遵照索引建的最佳左前缀法则 当无法使用索引列增大 max_length_for_sort_data 和 sort_buffer_size 参数的设置 where效率高于having能写在where限定的条件就不要写在having中了 减少使用order by和业务沟通能不排序就不排序或将排序放到程序端去做。Order by、group by、distinct这些语句较为耗费CPU数据库的CPU资源是极其宝贵的。 包含了order by、group by、distinct这些查询的语句where条件过滤出来的结果集请保持在1000行以内否则SQL会很慢。 5. 覆盖索引
对联合索引来说的查询直接查索引。不会回表一次查询出来结果。查询的列都是索引查询条件也是所有列。
所以这里的like统配匹配不会失效。
6. 索引下推
这个也是联合索引中出现的最大的特征是用explain分析工具查询extra信息显示Using index condition。 ① 索引的每个列和查询的值比较一遍 ② 匹配到的索引再进行回表拿到结果。 ICP的使用条件 ① 只能用于二级索引(secondary index) ②explain显示的执行计划中type值join 类型为 range 、 ref 、 eq_ref 或者 ref_or_null 。 ③ 并非全部where条件都可以用ICP筛选如果where条件的字段不在索引列中还是要读取整表的记录 到server端做where过滤。 ④ ICP可以用于MyISAM和InnnoDB存储引擎 ⑤ MySQL 5.6版本的不支持分区表的ICP功能5.7版本的开始支持。 ⑥ 当SQL使用覆盖索引时不支持ICP优化方法。
