SQL系列

SQL编写的注意事项

我们对速度和资源进行了优化，优化后的查询可以运行得更快，所需的计算能力也更低

  使用EXPLAIN 优化查询
  EXPLAIN 语句提供了关于 MySQL 如何执行语句的信息。根据 MySQL 文档，EXPLAIN 与 SELECT、 DELETE、 INSERT、 REPLACE 和 UPDATE 语句一起工作。它显示内置的 MySQL 优化器提供的关于语句执行计划和每个表中扫描的行数的信息。因此，我们可以确定查询的开销。下面的查询显示了 EXPLAIN 如何处理 SELECT 语句。
  在 MySQL 中，慢速查询被记录在内置的查询日志中。在查询日志中发现速度慢的查询后，使用 EXPLAIN 语句确定为什么查询速度慢并对其进行优化。

在短语结尾使用通配符

在 MySQL 中，通配符与 LIKE 操作符和 NOT LIKE 操作符一起使用。它们用于搜索与某些搜索条件匹配的数据
通配符在搜索数据时导致最广泛的扫描，这是非常低效的。前导通配符是最低效的，特别是当与结束通配符结合使用时。在这种情况下，MySQL必须搜索所有的记录来匹配。因此，您应该避免引导通配符

//尽量避免使用
select * from user where userName like '%li%';

在 SELECT 函数中指定列

SELECT * (SELECT all)用作查询表中所有可用列的快捷方式。这比使用只包含特定查询所需列的 SELECT 语句需要更多的资源

//推荐
select id, userName from user;
//避免
select * from user;

避免选择 DISTINCT

DISTINCT 用于使用 SELECT 语句删除重复的行。DISTINCT 命令需要对数据库进行更多的排序和读取，这需要更强的处理能力。DISTINCT 可以用 GROUP BY 替换以得到相同的结果

//推荐
select name, hobby from user group by name, hobby;
//避免
select distinct name, hobby from user;

使用LIMIT

有时，我们需要从结果集中指定数量的行。LIMIT 子句用于在查询中指定行数，而不是获取整个结果集。与获取指定数量的行相比，获取整个结果集需要更多的资源

//推荐
select name, hobby from user limit 10;
//避免
select name, hobby from user;

查询缓存

提供数据库缓存功能。SELECT 语句文本和检索到的结果存储在缓存中。当您执行与缓存中已有的查询类似的查询时，MySQL 将响应并给出缓存中已有的查询。通过这种方式，使用的资源更少，查询运行得更快
这对于执行更多选择查询的数据库最有效。表更新后，缓存的查询和结果将无效。因此，高速缓存可能不适用于频繁更新表的应用程序

//查询是否开启缓存
SHOW VARIABLES LIKE 'have_query_cache';

将外连接转换为内连接

INNER JOIN 返回包含两个表中的列的行。与 INNER JOIN 不同，OUTER JOIN 返回两个表上都没有匹配项的行。因此，OUTER JOIN 比 INNER JOIN 做更多的工作，增加了总的执行时间
尽可能使用 INNER JOIN。当不需要指定列之外的数据时，使用 OUTER JOIN 会浪费性能

//推荐
select u.id  u.name, r.id, r.resource
from user u
inner join role r on u.id = r.userId
//避免
select u.id  u.name, r.id, r.resource
from user u
full outer join role r on u.id = r.userId

使用 UNION 子句优化 LIKE 语句

OR 运算符用于组合两个布尔表达式，并在满足其中一个条件时返回 true。在查询中使用比较运算符‘ or’时，MySQL 优化器可能会错误地选择全表扫描来检索结果集。这使得查询运行得更慢。UNION 子句运行得更快，并且得到相同的结果

//推荐
select * 
from user
where name like 'li%' 
union all 
	select * 
	from user
	where district like 'z%';
//避免
select * 
from username
where name like 'li%' or district like 'z%';

索引的使用注意事项

索引越少越好 在修改数据时，每个索引都要进行更新，会降低写速度
最窄的字段放在键的左边
避免file sort排序，临时表和表扫描

此处参考mysql稀疏稠密索引 &mysql索引的类型
普通索引
  普通索引(由关键字KEY或INDEX定义的索引)的唯一任务是加快对数据的访问速度。因此，应该只为那些最经常出现在查询条件(WHERE column = …)或排序条件(ORDER BY column)中的数据列创建索引。只要有可能，就应该选择一个数据最整齐、最紧凑的数据列(如一个整数类型的数据列)来创建索引。

复合|聚合
  索引可以覆盖多个数据列，如像INDEX(columnA, columnB)索引。这种索引的特点是MySQL可以有选择地使用一个这样的索引。如果查询操作只需要用到columnA数据列上的一个索引，就可以使 用复合索引INDEX(columnA, columnB)。不过，这种用法仅适用于在复合索引中排列在前的数据列组合。比如说，INDEX(A, B, C)可以当做A或(A, B)的索引来使用，但不能当做B、C或(B, C)的索引来使用。

全文索引
  文本字段上的普通索引只能加快对出现在字段内容最前面的字符串(也就是字段内容开头的字符)进行检索操作。如果字段里存放的是由几个、甚至是多个单词构成 的较大段文字，普通索引就没什么作用了。这种检索往往以LIKE %word%的形式出现，这对MySQL来说很复杂，如果需要处理的数据量很大，响应时间就会很长。
  这类场合正是全文索引(full-text index)可以大显身手的地方。在生成这种类型的索引时，MySQL将把在文本中出现的所有单词创建为一份清单，查询操作将根据这份清单去检索有关的数据记录。

唯一索引： 如果能确定某个数据列将只包含彼此各不相同的值，在为这个数据列创建索引的时候就应该用关键字UNIQUE把它定义为一个唯一索引。这么做的好处：一是简 化了MySQL对这个索引的管理工作，这个索引也因此而变得更有效率；二是MySQL会在有新记录插入数据表时，自动检查新记录的这个字段的值是否已经在 某个记录的这个字段里出现过了；如果是，MySQL将拒绝插入那条新记录。也就是说，唯一索引可以保证数据记录的唯一性。事实上，在许多场合，人们创建唯 一索引的目的往往不是为了提高访问速度，而只是为了避免数据出现重复
稠密索引： 在密集索引中，数据库中的每个搜索键值都有一个索引记录。这样可以加快搜索速度，但需要更多空间来存储索引记录本身。索引记录包含搜索键值和指向磁盘上实际记录的指针
稀疏索引： 在稀疏索引中，不会为每个搜索关键字创建索引记录。此处的索引记录包含搜索键和指向磁盘上数据的实际指针。要搜索记录，我们首先按索引记录进行操作，然后到达数据的实际位置。如果我们要寻找的数据不是我们通过遵循索引直接到达的位置，那么系统将开始顺序搜索，直到找到所需的数据为止

• 若一个主键被定义，该主键则作为密集索引
• 若没有主键被定义，该表的第一个唯一非空索引则作为密集索引
• 若不满足以上条件，innodb内部会生成一个隐藏主键(密集索引)
• 非主键索引存储相关键位和其对应的主键值，包含两次查找

延申

索引底层数据结构

MySQL索引底层使用的B+树
B + 树是一种基于 b 树的优化算法。树的非叶节点只存储索引，不存储数据，使非叶节点可以包含更多的节点。这样做有两个优点: 一是树的高度大大降低; 二是将数据范围改变为多个间隔，从而提高了检索效率。叶节点存储所有的索引和数据，叶节点之间相互连接，形成链式结构，具有较高的范围查询性能

在 InnoDB 的存储引擎下，将数据和索引放在一起

• .frm 存储表结构
• .IBD 存储表数据和索引

1. 叶节点直接存储索引和数据
2. InnoDB 至少有一个聚集索引。通常，主键为 b + 树索引。如果没有主键，将选择唯一键。如果没有唯一键，将自动生成一个6位 ROWID 作为主键
3. 在非聚集索引中，叶节点存储数据行的主键，然后通过聚集索引查找相应的数据，即遍历 b + 树两次，称为回表

索引的限制

默认情况下，InnoDB 引擎单一字段索引的长度最大为 767 字节，同样的，前缀索引也有同样的限制。当使用 UTF-8 字符集，每一个字符使用 3 字节来存储，在 TEXT 或者 VARCHAR 类型的字段上建立一个超过 255 字符数的前缀索引时就会遇到问题。可以启用服务器选项 innodb_large_prefix 使得这个限制增加到 3072 字节，而且表的 row_format 需要使用 compressed 或者 dynamic
myisam存储引擎在创建索引的时候，索引键长度是有一个较为严格的长度限制的，所有索引键最大长度总和不能超过1000，而且不是实际数据长度的总和，而是索引键字段定义长度的总和

什么样的SQL不使用索引?
此处引用SQL索引规则

没有查询条件，或者查询条件没有索引
查询的结果占总数据的 15% 以上 不使用索引
查询条件字段参与了运算（在=号左侧有特殊符号不使用索引，右侧可以使用）
字符串与数字比较不使用索引
ike “%_” 百分号在最前面不使用索引
组合索引的顺序（最左原则，从左到右如果中间哪个索引不是条件后续的索引不使用）

MySQL各种常用的函数

聚合函数

// 返回表中此列的平均值
AVG(column)
//返回表中所有行
COUNT(*)
//返回此列的最大数
MAX(column)
//返回此列的最小数
MIN(column)

比较函数

//COALESCE 函数将查找state中的值，如果找到 NULL，它将用 n/a 替换它
COALESCE(state, 0)
//上面的代码将返回列表中的最高值和最低值。如果有一个 NULL 值，那么它将返回 NULL 而不进行任何计算
GREATEST(column,column...)
LEAST(column,column...)
//它检查parameter是否为NULL是则返回1，否则返回0
ISNULL (parameter)
//判断parameter1是否为空，是则用parameter2代替
ifnull(parameter1, parameter2)
//如果parameter1= parameter2成立，那么返回值为NULL，否则返回值为parameter1
nullif(parameter1, parameter2)

数学函数

//MOD(N,M)或%:返回N被M除的余数。
select MOD(7,2); 
//CEILING(X):返回不小于X的最小整数值。 
select CEILING(2.47);
//ROUND(X) :返回参数X的四舍五入的一个整数。
select ROUND(6.82); 

日期函数

//以 YYYY-MM-DD 格式返回当前日期
CURDATE()
//计算第一个日期和第二个日期之间的时差
DATEDIFF(date1, date2)
//从当前日期中选择日期
DAY('Date')

字符串函数

// 将参数转换为字符串，然后连接到单个值。NULL 是 NULL 中任何参数的输出
CONCAT(str1, str2, str3, ...)
// 返回字符串(str)的长度(以字节为单位)
LENGTH(str)