MYSQL 性能优化: 查询速度优化、数据库结构优化、MYSQL服务器优化 原则是减少系统的瓶颈,减少资源的占用,增加系统的反应速度。 通过优化文件系统,提高磁盘I\O的读写速度; 通过优化操作系统调度策略,提高MYSQL 在高负荷情况下的负载能力; 优化表结构、索引、查询语句等使查询响应更快。 show status like 'str' 常用的性能参数如下: connections 连接MYSQL服务器的次数 uptime MYSQL服务器的上线时间 slow_queries 慢查询的次数 com_select 查询操作的次数 com_insert 插入操作的次数 com_update 更新操作的次数 com_delete 删除操作的次数 ======================================优化查询====================================== 索引不要多,一般不超过6个 explain 语句 和 describe 语句 索引失效场景 1.列类型是字符串,查询条件未加引号。 like以通配符开头(‘%abc..’)会导致索引失效 多列索引 最左前缀满足原则 OR关键字 前后两个条件中的列都是索引时,索引有效否则失效 mysql在使用不等于(!= 或 <> 或 not in)时无法使用索引 is null,is not null也无法使用索引 字符串不加单引号,该字段以后的索引失效 少数据值的列也不应该增加索引,只有两种情况,且平均分布,加了索引反而降低速度 in与exists使用场合 坚持小表驱动大表的原则 in : 当B表的数据集必须小于A表的数据集时,in优于exists exists:当A表的数据集小于B表的数据集时,exists优于in 对Order By的优化 用order by子句的重点是是否会产生filesort。建索引时已经排好序,所以order by的顺序和索引最好一致,避免再一次排序。 所建的索引默认升序,一升序一降序会产生内排序 状态最好是using index,让mysql通过扫描索引本身完成排序。 能使用index方式排序的情况:order by语句使用索引最左前列,或where子句与order子句条件组合满足索引最左前列。 (1)order by语句使用索引最左前列,order by后字段同为asc或desc都行 (2)加上where子句的条件与order by子句条件列组合满足索引最左前列 不能使用索引的情况 /* 排序不一致*/ ORDER BY a ASC, b DESC ,c ASC /* 丢失a索引*/ ORDER BY b,c /* 丢失b索引*/ ORDER BY c /* d不是索引的一部分 */ ORDER BY a,d /* 对于排序来说,多个相等条件也是范围查询*/ WHERE a in(..) ORDER BY b,c 3、filesort的两种算法 (1)双路排序:两次扫描磁盘(读取行指针和order by列,对他们进行排序,然后扫描已排好序的列表,重新列表读取数据输出)。 (2)单路排序:mysql4.1版本后,从磁盘读取查询需要的所有列,按order by列在buffer对它们排序,然后扫描排序后的列表输出,只读取一次数据,且把随机IO变为顺序IO,但会使用更多空间,因为它把每一行都保存在内存中。 单路排序存在的问题: 因为要把所有字段取出,可能要取出的大小超出sort_buffer容量,导致每次只能取sort_buffer容量大小的数据进行排序(创建tmp文件,多路合并),排完再取sort_buffer容量大小的数据,反而会导致更多I/O操作。 4、order by优化策略: (1)单路多路算法的数据都有可能超过sort_buffer_size,超出后会建tmp文件进行合并排序,导致多次I/O,可以根据系统能力增大sort_buffer_size参数设置 (2)增大max_length_for_sort_data参数,会增加用单路排序的概率,但如果设太大,也会更容易使数据超过sort_buffer_size,当query的字段大小总和小于max_length_for_sort_data且排序字段不是text/blob类型时,才会用单路排序,否则还是用多路排序。 (3)order by时不要用select *,只select需要的字段,多余的字段会占用sort_buffer的内存。 使用关联查询优化子查询 多个索引同时作为查询条件时:type:index_merge extra:Using intersect(MainOrder_NormalIndex,IX_Feeds_AccountNo); Using where 建索引的几大原则 1.最左前缀匹配原则,非常重要的原则,mysql会一直向右匹配直到遇到范围查询(>、<、between、like)就停止匹配,比如a = 1 and b = 2 and c > 3 and d = 4 如果建立(a,b,c,d)顺序的索引,d是用不到索引的,如果建立(a,b,d,c)的索引则都可以用到,a,b,d的顺序可以任意调整。 2.=和in可以乱序,比如a = 1 and b = 2 and c = 3 建立(a,b,c)索引可以任意顺序,mysql的查询优化器会帮你优化成索引可以识别的形式 3.尽量选择区分度高的列作为索引,区分度的公式是count(distinct col)/count(*),表示字段不重复的比例,比例越大我们扫描的记录数越少,唯一键的区分度是1,而一些状态、性别字段可能在大数据面前区分度就是0,那可能有人会问,这个比例有什么经验值吗?使用场景不同,这个值也很难确定,一般需要join的字段我们都要求是0.1以上,即平均1条扫描10条记录 4.索引列不能参与计算,保持列“干净”,比如from_unixtime(create_time) = '2014-05-29'就不能使用到索引,原因很简单,b+树中存的都是数据表中的字段值,但进行检索时,需要把所有元素都应用函数才能比较,显然成本太大。所以语句应该写成 create_time = unix_timestamp('2014-05-29'); 5.尽量的扩展索引,不要新建索引。比如表中已经有a的索引,现在要加(a,b)的索引,那么只需要修改原来的索引即可 美团的慢查询调优 https://www.cnblogs.com/php-rearch/p/5034118.html MySQL优化原理 https://www.iteye.com/news/32381 http://blog.jobbole.com/100349/ ======================================优化数据库结构====================================== 空间换时间 1、将字段很多的表分解成多个表 2、增加中间表 关联查询-》单表查询 3、增加冗余字段 4、优化插入的记录的速度 5、analyze 分析表 ======================================优化数据库服务器====================================== 具体参考MySQL5.7从入门到精通@www.java1234.com.pdf 16.4
mysql调优
最新推荐文章于 2024-04-18 03:29:48 发布