关于SQL性能优化及注意事项

最新推荐文章于 2023-07-28 10:22:25 发布

码农米格

最新推荐文章于 2023-07-28 10:22:25 发布

阅读量182

点赞数

分类专栏：数据库文章标签：数据库 mysql 索引

本文链接：https://blog.csdn.net/e_mmm0629/article/details/109048748

版权

数据库专栏收录该内容

4 篇文章 0 订阅

订阅专栏

sql优化

mysql的性能分析/优化工具：
- mysql show profiles ：mysql 性能分析工具
- Explain ：查看 SQL 的执行计划。计划内容：
  - id ：选择标识符
  - select_type：表示查询的类型
  - table：输出结果集的表
  - partitions：匹配的分区
  - type ：表示表的连接类型
    - 常用的类型包括：ALL、index、range、ref、eq_ref、const、system、NULL（ 从左到右，性能从差到好 ）
    - ALL：Full Table Scan，Mysql将遍历全表已匹配找到的行
    - index：Full Index Scan，Mysql将遍历索引树
    - range：只检索给定范围的行，使用一个索引来选择行
    - ref：表示上述表的连接匹配条件，即哪些列或常量被用于查找索引列的值
    - eq_ref：类似ref，区别是使用的是唯一索引，对于每一个索引值，表中只有一条记录匹配，简单来说，就是多表连接中使用 primary key或者 unique key作为关联条件
    - const、system：当Mysql对查询某部分进行优化，并转换为一个常量时，使用这些类型访问。如将主键置于where列表中，Mysql就能将该查询转化为一个常量，system是const类型的一个特例，当查询的表只有一行的情况下使用system。
    - NULL：Mysql在优化过程中分解语句，执行时甚至不用访问表和索引，例如从一个索引列里选取最小的值可以通过单独索引查询完成。
  - possible_keys：表示查询时，可能使用的索引
  - key ：表示实际使用的索引
  - key_len：索引字段的长度，在组合索引中判断索引被使用的情况尤为重要
  - ref：列与索引的比较
  - rows：扫描出的行数
  - filtered：按表条件过滤的行百分比
  - Extra：执行情况的描述与说明
关于索引的划分
- 主键索引：唯一、非空。如果表上有定义主键，则该主键索引就是聚簇索引；如果没有主键，Mysql 会选取第一个唯一索引且非空的列作为主键， InnoDB 使用它作为聚簇索引。如果没有这样的列， InnoDB 会自己产生一个这样的 id值 ，它有六个字节，而且还是隐藏的，使其作为聚簇索引。
- 二级索引：一般手动创建的索引都是辅助索引，因为辅助索引访问数据总是需要进行二次查找，所以也叫作 二级索引。非聚簇索引都是二级索引
  - 普通索引：无法生效的情况见下方 针对索引优化 的内容。
```
alert table add key(coulmn) 或 add index(column)
```
  - 前缀索引：即将字段名称的前几个字符作为索引
```
alert table add index(coulmn(5))
```
    - 创建原则：需要测试前缀字符的个数和整列索引的个数进行对比，在个数和比之间选择合适的值。
```
select count(distinct aa)/count(*) from table /*值1*/
```
```
select count(distinct left(aa,index))/count(*) from table /*值2*/
```
      当 index 值小，且值1 和值2 相差比较小时，取 index 的值作为前缀索引的字符数。
    - 优点：大量节省索引空间，提高查询效率
    - 无法进行 group by 和 order by 检索
  - ~~哈希索引~~ ：通过 hash值 来维护索引，理论上在冲突较小（即重复数据少）的情况下，效率要比普通索引高很多。（使用频率很小，仅做了解）
```
alert table add index using hash(coulmn)
```
    - hash 索引只有 MyISAM 引擎支持
    - 缺点：不支持范围查询；不支持索引完成排序；不支持联合索引的最左前缀匹配原则。
  - 组合索引：
```
alert table add index(column1,column2)
```
    - 最左匹配原则：
      - 索引生效的范围，条件只能是 （1）、（1,2） 这种格式，如果是单独的（2），则索引不生效，与查询条件的摆放顺序无关，因为 mysql 会根据索引的顺序自动优化条件顺序。
      - 当组合索引中的一个列的索引失效，后面的索引将全部失效。如（a1,a2,a3），a2 like ‘%xxx%’，则a3也将失效。
      - 索引做排序：只有当索引的列顺序和 order by 的列顺序完全一致且排序为全部升序或全部降序时，索引排序才能生效。
        
        特殊情况：当索引的最左列在 where 条件中是一个 = 号条件，后面的排序生效，例：
        
        index(aa,bb,cc) select * from table where aa='daaf' order by bb,cc
        
        多表关联查询时，order by的字段全部来自一张表，索引排序才有可能生效。
  - ~~覆盖索引~~ （由于 Mysql 自带 sql 优化的能力，对于索引也是择优选择，因此一般 不会去考虑使用覆盖索引（除非你是数据库大牛） ，仅做了解）：需要查询的所有字段及条件中的字段都可以从索引中找到。换句话说就是查询的列和条件被使用的索引覆盖了。
- 索引碎片：数据的添加或删除会产生索引碎片，导致查询变慢。下面给出索引碎片相关的两个命令：
  - ```
  show table status like '表名' /*查看 data_free > 0 则表示有碎片*/
```
- ```
optimize table '表名' /* 清理碎片 */
```
针对索引进行优化：
- 创建索引的原则： 有大量不重复数据。可以将该列不重复（distinct ）数据的count与该列的所有数据的count进行比较，比例越高越有价值。查询频率越高，创建索引的价值越高。
- 1、对查询进行优化，要考虑在 where 和 order by 所涉及的列上建立索引。
- 2、 查询语句尽可能简单，大语句拆成小语句，减少锁时间。（引出问题：如何拆分？）
- 3、 列表数据使用分页查询，每页数据量不要太大。
  - 分页查询的思想是避免数据量大时扫描过多的记录。通常用 limit 关键字实现分页查询，limit 接受一个或两个参数，且参数必须是一个整数常量，第一个参数表示返回记录行的偏移量，第二个参数表示返回记录行的最大数目。
    
    查询从第 1000000 行之后的 30 条记录：
```
select * from table order by id limit 1000000,30
```
    直接实例 limit 获取第 1000000 条数据后的 30 条数据，会使引擎扫描前 1000000 条数据直到定位到第 1000000 条，随着 limit 偏移量的不断增大，速度会越来越慢。因此我们可以对其进行修改，先通过索引定位到第 1000000 条数据，然后在获取后 30条记录。
    - 当查询数列比较多的时候，将浪费很多的内存空间。
    - 对 limit 的优化，不是直接使用 limit，而是首先获取到 offset 的 id，然后直接使用 limit size 来获取数据。
    - 子查询的分页方式
```
select * from table where id >= (select id from table order by id limit 1000000,1) limit 30
```
    - JOIN 分页方式
```
select * from table as t1 join (select id from table order by id desc limit (($page-1)*$pagesize),1 as t2 where t1.id <= t2.id order by t1.id desc limit $pagesize
```
    - join分页 和 子查询分页，效果上相差不多。
- 4、尽量避免在 where 子句中对字段进行 is null 值判断，否则将导致引擎放弃使用索引而进行全表扫描。
```
select id from table where num is null
```
  设置字段时，尽量不要使用 null 填充字段，因为 NULL 同样占用空间。如：char(100) 型，在字段建立时空间就固定了，不管是否插入值（NULL 也包含在内），都是占用 100 个字符的空间，但 varchar 字段中，null 不占用空间。
  - 可以通过其他办法，使字段表示无值，如：在 num 上设置默认值 0，确保表中 num 列没有 null，在查询时使用如下语句查询：
```
select id from table where num = null
```
- 5、尽量避免在 where 子句中使用 != 或 < > 操作符，否则将导致引擎放弃使用索引而进行全表查询
- 6、尽量避免在 where 子句中使用 or 来连接条件，如果一个字段有索引，一个字段没有索引，将导致引擎放弃使用索引而进行全表扫描，如：
```
select id from table where num=5 or Name='miger'
```
  可以修改语句，通过使用 union 或 union all（包含重复项） 来拼接两个查询结果：
```
select id from table where num=5
union all
select id from table where Name='miger'
```
- 7、关于 in 和 exists ，需要考虑要查询的两张表的大小关系。
  - mysql 中对 in 的子查询支持很糟，尽量用左联或 exists 代替。
  - 如果两张表的大小相当，则两种关键字的差别不大，
  - 如果两张表中一张小表，一张大表，则子查询的表大的用 exists，子查询表小的用 in。
  也可以尝试使用 exists 代替 in：
```
select num from a where num in(select num from b)
```
  替换成：
```
select num from a where exists(select 1 from b where num=a.num)
```
  这里可以注意到我使用的是 1 而不是，select 1 from table / select * from table 在用法上大同小异，都是查看表中是否有记录，select 1 from table 中的 1 是一常量，查到的所有行的值都是它。但从效率讲 1>，因为不用查字典表。
  - 对于 not in 和 not exists ：使用 not in 会对内外表都进行全表扫描，没有用到索引；而 not exists 的子查询依然可以用索引，所以使用 not exists 比使用 not in 要快。
- 8、在使用关键字 like 进行模糊查询时，尽量避免第一个字符就是 % 的情況出现如：‘%xxx%’、%xxx ，会使引擎放弃使用索引而进行全表扫描，如：
```
select id from table where name like '%世俗%'
```
  只要第一个字符不是 % ，查询时都会使用索引。
- 9、尽量避免在 where 子句中对字段使用函数或触发器。
- 10、 查询是否会产生临时表，临时表的数据量是不是很大。
  - 查看方式：可以通过使用 Explain 分析执行计划，其中的字段 extra 为 Using Temporary ，因为临时表是没有索引的，如果临时表过大，不仅占内存而且查询慢。
  - 可能会产生临时表的查询关键字：
    - union 关键字；
    - group by 和 order by 不是同一个字段；
    - distinct 查询中加 order by 字段；
    - 关联查询时， order by 的字段不是驱动表的字段（驱动表即 explain 查看汇总第一行的表）；
    - from 中的子查询。
- 11、 当无条件时，查询表中的精确数据，count(*) 比 count(column) 效率高
- 12、 关联查询需要 explain 查看查询顺序，通常情况下 mysql 会用数据最少的一张表作为第一顺序查询，此时把索引建在第二张表的查询比较高效
  - order by 和 group by 要在一张表中才能更好的使用索引
- 13、 当遇到多张表关联查询时， mysql 的优化方式是把查询次数最少的那张表作为最外层查询的表，优先使用那张表的索引。我们也可以通过 hint 命令指定表的查询顺序，将 join 换成 STRAIGHT_JOIN 来指定表的执行顺序。
- 14、 当关联（join、left join等）多表查询多个数据时，可以考虑拆分成两个查询，一个查询作为原来查询的列和查询的条件，另一个查询做一个列的查询并做第一个查询的过滤条件。根据阿里的开发规范手册建立， 关联的表最多不要超过3张表 。
- 15、 尽量使用 union all 而非 union ，使用 union 时，将条件写在子查询中。
针对 表结构 设计优化
- 使用可以存下数据的最小数据类型。
- 尽量使用 tyntint、smallint、mediumint 作为整数类型而非 int。
- 尽量使用 not null 定义字段，因为 null 占用4字节空间，如数字可以默认为 0，字符串默认为 “”。
- 尽量少用 text 类型，非用不可时最好独立出一张表。
- 尽量使用 timestap ，而非 datetime。
- 单表不要有太多字段，建议在 20个字段以内。
需要关注的问题：
- 索引是建的越多越好么：
  - 数据量小的表不需要建立索引，建立会增加额外的索引开销。
  - 不常引用的列，不要建立索引，没多大意义
  - 经常频繁更新的列不要建立索引，会影响插入或更新的效率，因为不仅要保存数据，还要保存索引文件
  - 数据重复且分布均匀的字段，如：性别（只有男女，不适合建立索引）
  - 数据变更需要维护索引，意味着索引越多，维护成本越高
  - 建立索引会产生索引文件，占用存储空间。

参考链接

limit的用法和分页查询优化
 https://www.cnblogs.com/yunfeifei/p/3850440.html 这个建议挑着看吧。

码农米格

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
关于SQL性能优化及注意事项

sql优化mysql的性能分析/优化工具：mysql show profiles ：mysql 性能分析工具Explain ：查看 SQL 的执行计划。计划内容：id ：选择标识符select_type：表示查询的类型table：输出结果集的表partitions：匹配的分区type ：表示表的连接类型常用的类型包括：ALL、index、range、ref、eq_ref、const、system、NULL（从左到右，性能从差到好）ALL：Full Table Scan，My
复制链接

扫一扫

专栏目录