SQL知识点补充及SQL优化

  • Z时代
  • 2024-01-10
  • 分类：综合

database

SQL知识点补充及SQL优化

磨石是快刀的朋友，草原是骏马的朋友，障碍是意志的朋友，困难是胜利的朋友。命运不是天能注定的，命运是依人奋斗的程度由人自己来决定的。

SQL的视图

可以把SQL的查询结果缓存起来,存入视图中.

  • 好处: 简化了SQL的编写

  • 坏处: 没法做SQL的优化,占用空间

创建视图:缓存查询结果

CREATE VIEW students_view AS (SELECT * FROM students WHERE class = "95033")

使用视图

SELECT * FROM students_view WHERE sex = "男";

SQL的执行顺序

  1. FROM [left_table] 选择表

  2. ON <join_condition> 链接条件

  3. <join_type> JOIN <right_table> 链接

  4. WHERE <where_condition> 条件过滤

  5. GROUP BY <group_by_list> 分组

  6. AGG_FUNC(column or expression),... 聚合

  7. HAVING <having_condition> 分组过滤

  8. SELECT (9) DISTINCT column,... 选择字段、去重

  9. ORDER BY <order_by_list> 排序

  10. LIMIT count OFFSET count; 分页

SQL优化

  • 查询SQL尽量不要使用select *，而是具体字段

    • 字段多时，大表能达到100多个字段甚至达200多个字段只取需要的字段，节省资源、减少网络开销select * 进行查询时，很可能不会用到索引，就会造成全表扫描

  • 避免在where子句中使用or来连接条件

    • 使用or可能会使索引失效，从而全表扫描

  • 使用varchar代替char

    • varchar变长字段按数据内容实际长度存储，存储空间小，可以节省存储空间，char按声明大小存储，不足补空格其次对于查询来说，在一个相对较小的字段内搜索，效率更高

  • 尽量使用数值替代字符串类型

    • 主键（id）：primary key优先使用数值类型int，tinyint
      性别（sex）：0-代表女，1-代表男；数据库没有布尔类型，mysql推荐使用tinyint
      支付方式（payment）：1-现金、2-微信、3-支付宝、4-信用卡、5-银行卡
      服务状态（state）：1-开启、2-暂停、3-停止
      商品状态（state）：1-上架、2-下架、3-删除

  • 查询尽量避免返回大量数据

    • 如果查询返回数据量很大，就会造成查询时间过长，网络传输时间过长。同时，大量数据返回也可能没有实际意义。如返回上千条甚至更多，用户也看不过来。通常采用分页，一页习惯10/20/50/100条。

  • 使用explain分析你SQL执行计划

    • SQL提供了explain关键字，它可以分析你的SQL执行计划，看它是否最佳。Explain主要看SQL是否使用了索引。

  • 是否使用了索引及其扫描类型

    • type：

      • ALL 全表扫描，没有优化，最慢的方式

      • index 索引全扫描

      • range 索引范围扫描，常用语<，<=，>=，between等操作

      • ref 使用非唯一索引扫描或唯一索引前缀扫描，返回单条记录，常出现在关联查询中

      • eq_ref 类似ref，区别在于使用的是唯一索引，使用主键的关联查询

      • const/system 单条记录，系统会把匹配行中的其他列作为常数处理，如主键或唯一索引查询

        null MySQL不访问任何表或索引，直接返回结果

    • key：

      • 真正使用的索引方式

  • 优化like语句

    • 模糊查询，程序员最喜欢的就是使用like，但是like很可能让你的索引失效

    • #反例：%开头
      SELECT NAME FROM student WHERE NAME LIKE "%某"
      SELECT NAME FROM student WHERE NAME LIKE "%某%"
      #正例
      SELECT NAME FROM student WHERE NAME LIKE "某%"

  • 字符串怪现象

    • #反例：未使用索引
      SELECT * FROM student WHERE NAME=123
      #正例：
      SELECT * FROM student WHERE NAME="123"

    • 因为不加单引号时，是字符串跟数字的比较，它们类型不匹配，MySQL会做隐式的类型转换，把它们转换为数值类型再做比较

  • 索引不宜太多，一般5个以内

    • 索引并不是越多越好，虽其提高了查询的效率，但却会降低插入和更新的效率

    • 索引可以理解为一个就是一张表，其可以存储数据，其数据就要占空间

    • 再者，索引表的一个特点，其数据是排序的，那排序要不要花时间呢？肯定要

    • insert或update时有可能会重建索引，如果数据量巨大，重建将进行记录的重新排序，所以建索引需要慎重考虑，视具体情况来定

    • 一个表的索引数最好不要超过5个，若太多需要考虑一些索引是否有存在的必要

  • 索引不适合建在有大量重复数据的字段上

    • 如性别字段。因为SQL优化器是根据表中数据量来进行查询优化的，如果索引列有大量重复数据，Mysql查询优化器推算发现不走索引的成本更低，很可能就放弃索引了。

  • 批量插入性能提升

    • 默认新增SQL有事务控制，导致每条都需要事务开启和事务提交；而批量处理是一次事务开启和提交。自然速度飞升

    • 数据量小体现不出来

  • 提高group by语句的效率

    • 先过滤，后分组

  • 删除冗余和重复的索引

  • 不要有超过5个以上的表连接

    • 关联的表个数越多，编译的时间和开销也就越大

    • 每次关联内存中都生成一个临时表

    • 应该把连接表拆开成较小的几个执行，可读性更高

    • 如果一定需要连接很多表才能得到数据，那么意味着这是个糟糕的设计了

    • 阿里规范中，建议多表联查三张表以下

  • inner join 、left join、right join，优先使用inner join

    • 三种连接如果结果相同，优先使用inner join，如果使用left join左边表尽量小

      • inner join 内连接，只保留两张表中完全匹配的结果集

      • left join会返回左表所有的行，即使在右表中没有匹配的记录

      • right join会返回右表所有的行，即使在左表中没有匹配的记录

    • 如果inner join是等值连接，返回的行数比较少，所以性能相对会好一点

    • 同理，使用了左连接，左边表数据结果尽量小，条件尽量放到左边处理，意味着返回的行数可能比较少。这是mysql优化原则，就是小表驱动大表，小的数据集驱动大的数据集，从而让性能更优

总结

1, 尽量使用字段名代替*

2, 做表设计时,字段的类型最好是varchar代替char

3, 字段里的值,最好用数字代替字符串

4, 尽量把过滤条件精细,能用and不用or

5, 索引的设计,最多5个,不能太多

6, 模糊查询,尽量要确定开始元素,让索引生效

7, 数据库对数字没有严格的要求,name=123索引失效,name="123’索引生效

8, 无用的索引最好及时删除

以上是 SQL知识点补充及SQL优化 的全部内容， 来源链接： utcz.com/z/535867.html

回到顶部