2. 优化方式
好的,现在我们先来看看有哪些优化方式,然后再这些优化方式当中,我们程序员需要掌握其中的哪些优化方式。
数据库层面的优化
- 表的结构是否正确?三范式
- 是否有正确的索引来提高查询效率?索引
- 是否为每个表使用了适当的存储引擎?存储引擎
- 每个表是否使用适当的行格式?字段压缩方式
- 是否使用了适当的锁策略?事务的隔离级别
- 用于缓存的所有内存区域的大小是否正确?buffpool
硬件层面的优化
- 硬盘
- cpu
- 内存宽带
上面这些优化方式,是mysql官网里面有的。针对这些优化点,对于我们程序员来说,是不是只需要关注数据库层面的优化。数据库层面的优化中我们是不是只需要着重关注索引的优化,所以今天小编会分享一些索引方面的优化点。
3. 慢日志查询
知道了优化点,那我们为什么要优化呢?肯定是执行时间太慢,并发能力上不去。所以,我们需不需要优化就需看我们执行的时间是否满足我们的需求。那我们怎么知道执行时间是否满足我们的需要呢?这个就要看我们的慢日志了。
慢日志参数:
- long_query_time: 超过多少秒进入慢查
SELECT @@long_query_time;--默认是10单位S
SET GLOBAL long_query_time=1;--设置超过1s就算慢查
- min_examined_row_limit: 检索查询的数量的行如果低于这个值,不进入慢查。
SELECT @@min_examined_row_limit;--默认是0
- log_output: 慢日志保存方式
SELECT @@1og_output;--慢查存在哪里
SET GLOBAL log_output='table,file'; -- table:表 file:文件
如果是file,那么保存的文件路径为slow_query_log_file。
SELECT @aslow_query_log_file; -- 查询慢日志存放路径
SET GLOBAL slow_query_log_file=''; -- 设置慢日志存放路径
如果是table,则保存在mysql.slow_log表中。
- slow_query_log: 慢日志开关
SELECT @@slow_query_log; -- 查询慢日志开关
SET GLOBAL slow_query_log=1; -- 开启慢查
4. sql语句优化
Explain执行计划
建立索引建立在where、orderby、groupby的字段上面,提升查询性能;但是就算加了也不一定能走到索引,所以要学会Explain分析。
Explain输出字段
这些字段里面,我们只关注里面几个就行了。
- type列: 这一列显示了访问类型,即MySQL决定如何查找表中的行。
依次从好到差:system,const,eq_ref,ref,fulltext,ref_or_null,unique_subquery,index_subquery,range,index_merge,index,ALL。
这个我们需要优化到 range 级别。
EXPLAIN select * FROM product WHERE id=1; -- const 只有一个匹配行并且id是主键
EXPLAIN select product_price FROM product WHERE product_price=55 -- ref 查询非唯一索引或主键的索引值
EXPLAIN select * FROM product WHERE id in(1,2); -- range 范围扫描
EXPLAIN SELECT * FROM product INNER JOIN product_price on product_new.id=product.product_id; -- eq_ref 组合查询中,用到了主键或者唯一索引
EXPLAIN SELECT product_price FROM product ORDER BY product_price; -- index类型 扫描索引树 比all相对来讲要快
EXPLAIN SELECT product_tag FROM product ORDER BY product_tag; -- all 不是索引,没有对应的索引树一般数据量大的情况下是需要优化的
- possible_keys: 可以选择的索引查询,如果为null则没有索引可以供选择。
- key: 真正使用的索引
- rows: 执行查询必须扫描的行数,对于InnoDB来讲,这个是个预估值,不是非常准确,但是行数越少,性能肯定越好。
- Extra
Using filesort: 排序没有走到索引
Using index 在索引树中能遍历到想要的数据(覆盖索引)
Using index condition 索引条件下推
Using index for group-by group by分组基于索引检索
Using temporary 是否使用临时表,一般在 group by与order by场景
Using where 扫描出来的数据需要进行where匹配
order by优化
如果让orderby的字段走索引,那么排序流程直接可以在索引树完成,如果排序的字段不走索引,整个排序流程必须先把数据放到内存,在内存实现排序。
怎么判断是否orderby用到了索引?
如果输出Extra的列 EXPLAIN 不包含 Using filesort,则使用了索引
如果输出Extra列 EXPLAIN 包含 Using filesort,则没有使用索引
count优化
count()是一个聚合函数,对于返回的结果集 的一个统计,一行一行去判断,如果count括号里的不是null,那么计值+1,否则不加,最后返回一个累计的总数。
count(*):* 是整条数据,也进行了优化,因为整条数据肯定不会为null。所以也不需要去判断
count(1):1 是扫描到数据 扫描到了就固定返回一个1,肯定不为null,不会做null判断。
count(id):id 主键id,肯定不为null,也不会去判断null,但是相对于count(1)来讲,要去解析ID。稍微慢点,但是也可以忽略不计。
count(字段):这个就有影响了,因为扫描行出来,需要判断字段是否为空。
Limit优化
limit m,n ; 扫描m+n条数据,然后过滤掉前面的m条数据,当m越大,那么需要扫描的数据也就越多,性能也会越来越慢。
EXPLAIN SELECT * FROM product LIMIT 100000,10 --很慢很慢
针对这种情况,有以下几种方案可以进行一定的优化。
- 如果id是趋势递增的,那么每次查询都可以返回这次查询最大的ID,然后下次查询,加上大于 上次最大id的条件,这样会通过主键索引去扫描,并且扫描数量会少很多很多。因为只需要扫描where条件的数据
SELECT * FROM product WHERE id > 100000 ORDER BY id LIMIT 10 -- 根据id查询,并且使用where过滤
- 先limit出来主键ID,然后用主表跟查询出来的ID进行inner join 内连接,这样,也能一定上提速,因为减少了回表,查询ID只需要走聚集索引就行。
SELECT * FROM product INNER JOIN(
SELECT id FROM product ORDER BY id LIMIT 100000,10
) a
ON product_new.id=a.id
分库分表
如果sql语句用到了索引,但是查询还是很慢,那么看看数据库表中的数据是否过多或者并发是不是很高。如果并发很高,那么我们可以考虑分库,比如order和product,把业务细化。如果表数据过多,那就需要分表,当然我们也可以引入第三方组件解决数据过多的问题,比如Elasticsearch。
5. 总结
对于上面的优化,基本上都是基于索引的。基于索引的 SQL 优化旨在通过合理设计和使用索引来提升查询性能。索引可以加速数据检索,减少全表扫描,特别是在处理大量数据时。优化策略包括选择合适的索引类型(如单列索引、复合索引)、避免冗余索引、使用覆盖索引来避免访问表数据、并通过 EXPLAIN 分析查询执行计划来确保索引的有效使用。合理设计索引不仅能加速查询,还能减少数据库负担,提高整体性能。