优化可以分几个层面来分析:
额外补充知识点:
mysql的innodb引擎下的索引的数据结构:B+树
为什么选择B+树来做索引呢,相对于AVL,红黑树,hash树,B树来说的优势如下(简单的说):
第一,降低了树的高度,提高了查询的效率,B+树采用的是非叶子结点保存索引,叶子结点保存数据的方式,每一层能存储16kb的数据(页,innodb引擎磁盘管理的最低单位,默认为16kb)
第二,支持范围查询,因为叶子结点之间是通过从左往右递增的方式来存储数据,每个数据之间都有双向的链表指针。
数据的读取,是从磁盘中读取命中的数据+后面一定长度的数据到内存(buffer_pool)中(磁盘预读原理)
回表:首先mysql使用的B+树来做索引,其中就有聚簇索引跟非聚簇索引的查询,聚簇索引的查询能直接查到想要的数据(b+树数据结构设计),非聚簇索引第一次只能查询索引字段对应的主键值,从而进一步转为聚簇索引的查询方式
索引下堆:在通过索引进行查询数据的同时,通过where的条件来过滤不符合条件的数据。原来的查询方式是先将所有满足索引字段的数据查询出来之后,在进行where条件的过滤。
- sql语句本身优化
1.1 避免索引失效:模型数空运最快(忘了是哪位大佬的七字真言)
模:like的模糊查询,如果是以%开头的
型:数据类型的不匹配 比如字段类型是整数类型的,但是你加上了单引号的操作,变成了字符串
数:函数的使用
空:查询条件出现非空,空的判断
运:在查询条件上加入了加、减、乘、除等运算
最:最左匹配原则(新的版本有跳跃索引这一特性)
快:全局扫描速度更快
业务应急的情况下索引失效:可以考虑 force index强制使用索引
1.2 合理使用索引:优先考虑最左前缀原则、覆盖索引
第一,用覆盖索引来替代单索引值的查询,来减少回表的次数(mysql5.6版本后,支持索引下堆的功能,explain计划中可以看到在Extra这列中 Using index condition)
第二,如果是读多写少的情况下,可以使用普通索引,会将写操作更新到change buffer中,而不是立即更新,最后在记录一条redo log,如果后续有读操作,那么将对应的数据页载入到buffer pool中,同时将change pool中的更新操作应用到该数据页中,这个过程叫merge
2.架构层面优化(未完待续)
redis 分库分表 es 等