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 链接条件
3. JOIN 链接
4. WHERE 条件过滤
5. GROUP BY 分组
6. AGG_FUNC(column or expression),... 聚合
7. HAVING 分组过滤
8. SELECT (9) DISTINCT column,... 选择字段、去重
9. ORDER BY 排序
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, 无用的索引最好及时删除