建立一个联合索引
alert table test add test idx_name_age_sex(name, age, sex)
验证最左匹配原则是否与条件顺序有关?
explain select * from staffs where name = 'starsky' and age = 20 and sex = 1;
explain select * from staffs where name = 'starsky' and sex = 1 and age = 20 ;
explain select * from staffs where age = 20 and name = 'starsky' and sex = 1;
explain select * from staffs where sex = 1 and age = 20 and name = 'starsky';
idx_name_age_sex =》 (name)(name,age)(name,age,sex)
只要在条件当中存在的索引列,那么sql语句可以命中索引。
匹配最左边的列 如果不是依次查询?
explain select * from staffs where name = 'starsky';
explain select * from staffs where name = 'starsky' and age = 20;
explain select * from staffs where name = 'starsky' and sex = 1;
explain select * from staffs where age = 20 and sex = 1;
总结:只要是按照了索引创建顺序来编写where条件,那么就可以使用到这个索引,并且大几率是覆盖索引,同时也符合最左匹配原则。
而最左匹配原则与条件中的字段顺序无关。只需要按照索引创建顺序最左字段存在即可。
原因:这是因为MySQL中有查询 优化器explain,所以sql语句中字段的顺序不需要和联合索引定义的字段顺序相同,查询优化器会判断纠正 这条SQL语句以什么样的顺序执行效率高,最后才能生成真正的执行计划,所以不论以何种顺序都可使用到 联合索引
2.mysql的优化器在选择索引时的策略:where > group by > order by
3. 模糊查询
对于模糊匹配的查询,如果是前缀匹配用的是索引,中坠和后缀用的是全表扫描
索引使用的建议:
- 唯一字段可以单独建立单索引,非唯一考虑联合索引,推荐尽量使用唯一字段建立索引
- 索引的个数,联合索引的个数 最佳 6个 以内,如果索引因为项目需求:最多 10个
- 索引的使用遵循最左匹配原则其次覆盖索引
- 尽量选择小的字段建立索引 int ,varchar(10), char(5)
- 避免<,<= ,> ,>= , % ,between 之前的条件。选择索引的字段的范围和模糊之前,因 为范围与模糊 会引起索引失效,针对于联合索引,就是联合索引的中间尽量不要有范围查询的字段
- 尽量多使用explain分析
- 避免更新频繁的字段 (二叉树会一直变化,导致性能变慢)
- 建立的索引- 优先考虑 建立 联合索引
- 索引字段不要有 null, 不是 ‘’
加一个排查步骤
1.开启慢日志
2.按慢日志的重复率高并时间长顺序逐一解决
3.可以用 show processlist 查询正在查询的语句