乐趣区

关于java:MySQL性能优化MySQL索引优化order-by优化explain优化

jiezi

前言

明天咱们来讲讲如何优化 MySQL 的性能，次要从索引方面优化。下期文章讲讲 MySQL 慢查问日志 ，咱们是根据慢查问日志来判断哪条 SQL 语句有问题，而后在进行优化，敬请期待 MySQL 慢查问日志篇

建表 

// 建表
CREATE TABLE IF NOT EXISTS staffs(
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(24) NOT NULL DEFAULT ""COMMENT' 姓名 ',
    age INT NOT NULL DEFAULT 0 COMMENT'年龄',
    pos VARCHAR(20) NOT NULL DEFAULT ""COMMENT' 职位 ',
    add_time TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT'入职事件'
) CHARSET utf8 COMMENT'员工记录表';
// 插入数据
INSERT INTO `test`.`staffs` (`name`, `age`, `pos`, `add_time`) VALUES ('z3', 22, 'manager', now());
INSERT INTO `test`.`staffs` (`name`, `age`, `pos`, `add_time`) VALUES ('July', 23, 'dev', now());
INSERT INTO `test`.`staffs` (`name`, `age`, `pos`, `add_time`) VALUES ('2000', 23, 'dev', now());
// 建设复合索引（即一个索引蕴含多个字段）ALTER TABLE staffs ADD INDEX idx_staffs_nameAgePos(name, age, pos);

优化一：全副用到索引

介绍

建设的复合索引蕴含了几个字段，查问的时候最好能全副用到，而且严格依照索引程序，这样查问效率是最高的。（最现实状况，具体情况具体分析）

SQL 案例

优化二：最左前缀法令

介绍

如果建设的是复合索引，索引的程序要依照建设时的程序，即从左到右，如：a->b->c（和 B+ 树的数据结构无关）

有效索引举例

  • a->c：a 无效，c 有效
  • b->c：b、c 都有效
  • c：c 有效

SQL 案例

优化三：不要对索引做以下解决

以下用法会导致索引生效

  • 计算，如：+、-、*、/、!=、<>、is null、is not null、or
  • 函数，如：sum()、round() 等等
  • 手动 / 主动类型转换，如：id = “1”，原本是数字，给写成字符串了

SQL 案例

优化四：索引不要放在范畴查问左边

举例

比方复合索引：a->b->c，当 where a=”” and b>10 and 3=””，这时候只能用到 a 和 b，c 用不到索引，因为在范畴之后索引都生效（和 B+ 树结构无关）

SQL 案例

优化五：缩小 select * 的应用

应用笼罩索引

即：select 查问字段和 where 中应用的索引字段统一。

SQL 案例

优化六：like 含糊搜寻

生效状况

  • like “% 张三 %”
  • like “% 张三 ”

解决方案

  • 应用复合索引，即 like 字段是 select 的查问字段，如：select name from table where name like “% 张三 %”
  • 应用 like “ 张三 %”

SQL 案例

优化七：order by 优化

当查问语句中应用 order by 进行排序时，如果没有应用索引进行排序，会呈现 filesort 文件内排序，这种状况在数据量大或者并发高的时候，会有性能问题，须要优化。

filesort 呈现的状况举例

  • order by 字段不是索引字段
  • order by 字段是索引字段，然而 select 中没有应用笼罩索引，如：select * from staffs order by age asc;
  • order by 中同时存在 ASC 升序排序和 DESC 降序排序，如：select a, b from staffs order by a desc, b asc;
  • order by 多个字段排序时，不是依照索引程序进行 order by，即不是依照最左前缀法令，如：select a, b from staffs order by b asc, a asc;

索引层面解决办法

  • 应用主键索引排序
  • 依照最左前缀法令，并且应用笼罩索引排序，多个字段排序时，放弃排序方向统一
  • 在 SQL 语句中强制指定应用某索引，force index(索引名字)
  • 不在数据库中排序，在代码层面排序

order by 排序算法

  • 双路排序

    Mysql4.1 之前是应用双路排序，字面的意思就是两次扫描磁盘，最终失去数据，读取行指针和 ORDER BY 列，对他们进行排序，而后扫描曾经排好序的列表，依照列表中的值从新从列表中读取对数据输入。也就是从磁盘读取排序字段，在 buffer 进行排序，再从磁盘读取其余字段。

文件的磁盘 IO 十分耗时的，所以在 Mysql4.1 之后，呈现了第二种算法，就是单路排序。

  • 单路排序

    从磁盘读取查问须要的所有列，依照 orderby 列在 buffer 对它们进行排序，而后扫描排序后的列表进行输入，它的效率更快一些，防止了第二次读取数据，并且把随机 IO 变成程序 IO，然而它会应用更多的空间，因为它把每一行都保留在内存中了。

当咱们无可避免要应用排序时，索引层面没法在优化的时候又该怎么办呢？尽可能让 MySQL 抉择应用第二种单路算法来进行排序。这样能够缩小大量的随机 IO 操作, 很大幅度地进步排序工作的效率。上面看看单路排序优化须要留神的点

单路排序优化点

  • 增大 max_length_for_sort_data

    在 MySQL 中, 决定应用 ” 双路排序 ” 算法还是 ” 单路排序 ” 算法是通过参数 max_length_for_ sort_data 来决定的。当所有返回字段的最大长度小于这个参数值时,MySQL 就会抉择 ” 单路排序 ” 算法, 反之, 则抉择 ” 多路排序 ” 算法。所以, 如果有短缺的内存让 MySQL 寄存须要返回的非排序字段, 就能够加大这个参数的值来让 MySQL 抉择应用 ” 单路排序 ” 算法。

  • 去掉不必要的返回字段，防止 select *

    当内存不是很富余时, 不能简略地通过强行加大下面的参数来强制 MySQL 去应用 ” 单路排序 ” 算法, 否则可能会造成 MySQL 不得不将数据分成很多段, 而后进行排序, 这样可能会得失相当。此时就须要去掉不必要的返回字段, 让返回后果长度适应 max_length_for_sort_data 参数的限度。

  • 增大 sort_buffer_size 参数设置

    这个值如果过小的话, 再加上你一次返回的条数过多, 那么很可能就会分很屡次进行排序, 而后最初将每次的排序后果再串联起来, 这样就会更慢, 增大 sort_buffer_size 并不是为了让 MySQL 抉择 ” 单路排序 ” 算法, 而是为了让 MySQL 尽量减少在排序过程中对须要排序的数据进行分段, 因为分段会造成 MySQL 不得不应用长期表来进行替换排序。

然而 sort_buffer_size 不是越大越好：

  • Sort_Buffer_Size 是一个 connection 级参数, 在每个 connection 第一次须要应用这个 buffer 的时候, 一次性调配设置的内存。
  • Sort_Buffer_Size 并不是越大越好, 因为是 connection 级的参数, 过大的设置和高并发可能会耗尽零碎内存资源。
  • 据说 Sort_Buffer_Size 超过 2M 的时候, 就会应用 mmap() 而不是 malloc() 来进行内存调配, 导致效率升高。

优化八：group by

其原理也是先排序后分组，其优化形式可参考 order by。where 高于 having, 能写在 where 限定的条件就不要去 having 限定了。

IT 老哥

一个通过自学，进入大厂做高级 Java 开发的程序猿，心愿能通过我的分享，让你学到常识

退出移动版