MySQL中提高性能的一个最有效的方式是对数据表设计合理的索引。索引提供了高效访问数据的方法,并且加快查询的速度, 因此索引对查询的速度有着至关重要的影响。
大多数情况下都(默认)采用B+ 树来构建索引。只是空间列类型的索引使R- 树,并且MEMORY 表还支持hash 索引。其实,用不用索引最终都是优化器说了算。
优化器是基于什么的优化器? 基于cost开销(CostBaseOptimizer) ,它不是基于规则( Rule-BasedOptimizer),也不是基于语义。怎么样开销小就怎么来。另外, SQL 语句是否使用索引,跟数据库版本、数据量、数据选择度都有关系。
拓展: Alibaba 《 Java 开发手册》 索引文件具有 B-Tree 的最左前缀匹配特性,如果左边的值未确定,那么无法使用此索引。
2.主键插入顺序
如果此时再插入一条主键值为 9 的记录,那它插入的位置就如下图:
可这个数据页已经满了,再插进来咋办呢?我们需要把当前 页面分裂 成两个页面,把本页中的一些记录移动到新创建的这个页中。页面分裂和记录移位意味着什么?意味着: 性能损耗 !所以如果我们想尽量 避免这样无谓的性能损耗,最好让插入的记录的 主键值依次递增 ,这样就不会发生这样的性能损耗了。
所以我们建议:让主键具有 AUTO_INCREMENT ,让存储引擎自己为表生成主键, 在插入记录时存储引擎会自动为我们填入自增的主键值。这样的主键占用空间小,顺序写入,减少页分裂。
例子:
#创建一个联合索引, 注意字段的顺序 create index idx_age_classid_name on student(age,classid,name); #执行计划 EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE student .age = 30 AND student .classId > 20 AND student .name = 'abc' ;
#再创建一个联合索引,与上面的索引对比字段顺序变了 create index idx_age_name_classid on student(age,name,classid); #再执行一模一样的执行计划 EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE student .age = 30 AND student .classId > 20 AND student .name = 'abc' ;
看到两个执行计划虽然都用到了索引,但是:
拓展: Alibaba 《Java 开发手册》 【强制】页面搜索严禁左模糊或者全模糊,如果需要请走搜索引擎来解决。
统一使用utf8mb4( 5.5.3 版本以上支持 ) 兼容性更好,统一字符集可以避免由于字符集转换产生的乱码。不同的 字符集 进行比较前需要进行 转换 会造成索引失效。