mysql技巧 -- 基本操作（高级）

1、窗口函数

 

2、case when  多条件判断的语句，根据不同条件来返回相应的值

注意：case函数只返回第一个符合条件的值，剩下的case部分将会被自动忽略

 

 

3、变量

 

4、with as 

① 单表

② 多表

注意：with as 语句之后不能加分号结束，不然会报错，必须用select结束。

 

5、group_concat

 

6、SQL优化--索引

概述：索引是存储引擎用于快速查找记录的一种数据结构，通过合理的使用数据库索引可以大大提高系统的访问性能，接下来主要介绍在MySql数据库中索引类型，以及如何创建出更加合理且高效的索引技巧。

索引的优点：

• 通过创建唯一性索引，可以保证数据库表中每一行数据的唯一性
• 大大加快数据的检索速度，这也是创建索引的最主要原因
• 加快表与表之间的连接，在实现数据的参考完整性方面特别有意义
• 在使用分组和排序，子句进行数据检索时，同样可以显著减少查询中分组和排序的时间

索引类型：

• 普通索引：仅加速查询
• 唯一索引：加速查询 + 列值唯一
• 主键索引：加速查询  + 列值唯一 + 无Null值  + 表中只有一个
• 组合索引：多列值组成一个索引，专门用于组合搜索
• 全文索引：对文本的内容进行分词，进行搜索

 

7、SQL优化--一般方法

（1）查询尽量用确定的列名，少用*号

（2）尽量少嵌套子查询，这种查询会消耗大量CPU资源

（3）避免使用耗费资源的操作

         Distinct （group by代替）

         少用Order by

（4）合理使用索引

（5）多表关联时，应尽量提前过滤条件

 

 8、SQL优化--执行计划

 explain select * from tb_index where a =1

Select_type：https://jingyan.baidu.com/article/f3ad7d0fd599c349c2345b12.html

Type：显示查询使用何种类型索引。效率(const > eq_ref > ref > range > index > all)

<1> const : 查询索引字段，并且表中最多只有一行匹配（只有主键索引查询只匹配一行才会是const）

<2> eq_ref：搜索时使用primary key 或 唯一索引

<3> ref：根据索引查找一个或多个值，非唯一索引

<4> range：对索引列进行范围查找 （例如大小于）

<5> index：全索引表扫描

<6> all ：全表扫描（通常没有建索引的列）

Possible_keys：可能使用的索引， 但不一定被查询实际使用。

Key：实际使用的索引，如果为null，则没有使用索引

Key_len：使用的索引长度，越短越好。

Rows： 这是mysql估算的需要扫描的行数，原则上越少越好。

Extra：

<1>distinct：在select部分使用了distinc关键字

<2>Using filesort：当 Extra 中有 Using filesort 时, 表示 MySQL 需额外的排序操作, 不能通过索引顺序达到排序效果. 一般有 Using filesort, 都建议优化去掉, 因为这样的查询 CPU 资源消耗大

<3>Using index:"覆盖索引扫描", 表示查询在索引树中就可查找所需数据, 不用扫描表数据文件, 往往说明性能不错

<4>Using temporary

查询有使用临时表, 一般出现于排序, 分组和多表 join 的情况, 查询效率不高, 建议优化

 

 9、SQL优化--总结

（1）不在索引列做任何操作（计算，函数，类型转换）

（2）不等于要慎用

（3）not null对索引有影响

（4）通配符开头索引失效

（5）对于有比较多or运行的查询，建议分成多个查询，用union all联结起来