Mysql-语法与常见优化思路

本文最后更新于：1 年前

[TOC]

sql 语法举例

有一张表，三列：name,course(课程)，score，查询出每个学生的总成绩并按从高到低排序。

SELECT NAME,SUM(score) FROM chengji

GROUP BY NAME

ORDER BY SUM(score) DESC;

顺序

Select 
From
Join on
Where
Group by
Having
Order by
Limit

单表

查找面积超过 3,000,000 或者人口数超过 25,000,000 的国家。

SELECT name,
    population,
    area
FROM
    World
WHERE
    area > 3000000
    OR population > 25000000;

查找 id 为奇数，并且 description 不是 boring 的电影，按 rating 降序。

SELECT
    *
FROM
    cinema
WHERE
    id % 2 = 1
    AND description != 'boring'
ORDER BY
    rating DESC;

查找有五名及以上 student 的 class。

SELECT
    class
FROM
    courses
GROUP BY
    class
HAVING
    count( DISTINCT student ) >= 5;

重复的 email。

SELECT
    Email
FROM
    Person
GROUP BY
    Email
HAVING
    COUNT( * ) >= 2;

多表

查找 FirstName, LastName, City, State 数据，而不管一个用户有没有填地址信息。

涉及到 Person 和 Address 两个表，在对这两个表执行连接操作时，因为要保留 Person 表中的信息，即使在 Address 表中没有关联的信息也要保留。此时可以用左外连接，将 Person 表放在 LEFT JOIN 的左边。

SELECT
    FirstName,
    LastName,
    City,
    State
FROM
    Person P
    LEFT JOIN Address A
    ON P.PersonId = A.PersonId;

没有订单的客户信息？

SELECT
    C.Name AS Customers
FROM
    Customers C
    LEFT JOIN Orders O
    ON C.Id = O.CustomerId
WHERE
    O.CustomerId IS NULL;
 ----------------------------------------------------------------------------   
SELECT
    Name AS Customers
FROM
    Customers
WHERE
    Id NOT IN ( 
        SELECT CustomerId 
        FROM Orders 
    );

查找一个 Department 中收入最高者的信息

SELECT
    D.NAME Department,
    E.NAME Employee,
    E.Salary
FROM
    Employee E,
    Department D,
    ( SELECT DepartmentId, MAX( Salary ) Salary 
     FROM Employee 
     GROUP BY DepartmentId ) M
WHERE
    E.DepartmentId = D.Id
    AND E.DepartmentId = M.DepartmentId
    AND E.Salary = M.Salary;

优化

思路

sql 优化？

索引合理？

读写分离？

垂直分区？按照列

水平分区？

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

只取需要的字段，节省资源、减少网络开销。
select * 进行查询时，很可能就不会使用到覆盖索引了，就会造成回表查询。

2、如果知道查询结果只有一条或者只要最大/最小一条记录，建议用 limit 1
Select id，name from employee wherename=’jay’limit1;
加上 limit 1 后,只要找到了对应的一条记录,就不会继续向下扫描了,效率将会大大提高。
当然，如果 name 是唯一索引的话，是不必要加上 limit 1 了，因为 limit 的存在主要就是为了防止全表扫描，从而提高性能,如果一个语句本身可以预知不用全表扫描，有没有 limit ，性能的差别并不大。

3、应尽量避免在 where 子句中使用 or 来连接条件
select * from userwhere userid=1or age =18
假设它走了 userId 的索引，但是走到 age 查询条件时，它还得全表扫描，也就是需要三步过程：全表扫描+索引扫描+合并如果它一开始就走全表扫描，直接一遍扫描就完事。

4、优化 limit 分页
当偏移量特别大的时候，查询效率就变得低下。
当偏移量最大的时候，查询效率就会越低，因为 Mysql 并非是跳过偏移量直接去取后面的数据，而是先把偏移量+要取的条数，然后再把前面偏移量这一段的数据抛弃掉再返回的。
//方案一：返回上次查询的最大记录(偏移量)
Select id，name from employee where id>10000 limit 10.
//方案二：order by + 索引
Select id，name from employee order by id limit 10000，10

如果使用优化方案一，返回上次最大查询记录（偏移量），这样可以跳过偏移量，效率提升不少。
方案二使用 order by+索引，也是可以提高查询效率的。

5、优化你的 like 语句
把%放前面，并不走索引。

6、使用 where 条件限定要查询的数据，避免返回多余的行
查询某个用户是否是会员
select user Id from user where userId=’userId’ and isVip=’1’

7、尽量避免在索引列上使用 mysql 的内置函数
索引列上使用 mysql 的内置函数，索引失效

8、应尽量避免在 where 子句中对字段进行表达式操作，这将导致系统放弃使用索引而进行全表扫描
select * from user where age-1 =10

9、Inner join 、left join、right join，优先使用 Inner join，如果是 left join，左边表结果尽量小

10、应尽量避免在 where 子句中使用!=或<>操作符，否则将引擎放弃使用索引而进行全表扫描
!=和<>很可能会让索引失效

11、使用联合索引时，注意索引列的顺序，一般遵循最左匹配原则
KEY idx_userid_age (userId,age`) USING BTREE

//符合最左匹配原则
select * from user where userid=10 and age =10；
//符合最左匹配原则
select * from user where userid =10;
当我们创建一个联合索引的时候，如(k1,k2,k3)，相当于创建了（k1）、(k1,k2)和(k1,k2,k3)三个索引，这就是最左匹配原则。
联合索引不满足最左原则，索引一般会失效，但是这个还跟 Mysql 优化器有关的。

12、对查询进行优化，应考虑在 where 及 order by 涉及的列上建立索引，尽量避免全表扫描
select * from user where address =’深圳’ order by age ;
alter table user add index idx_address_age (address,age)

13、在适当的时候，使用覆盖索引

覆盖索引（covering index ，或称为索引覆盖）即从非主键索引中就能查到的记录，而不需要查询主键索引中的记录，避免了回表的产生减少了树的搜索次数，显著提升性能。

覆盖索引避免了回表现象的产生，从而减少树的搜索次数，显著提升查询性能，所以使用覆盖索引是性能优化的一种手段。

就是把单列的非主键索引修改为多字段的联合索引,
在一棵索引数上就找到了想要的数据, 不需要去主键索引树上,再检索一遍
这个现象,称之为索引覆盖。

空间换时间。

SELECT age FROM student WHERE name = ‘小李’；

索引为（name,age）

14、慎用 distinct 关键字
字段很多的时候使用，却会大大降低查询效率。
使用 distinct，数据库引擎就会对数据进行比较，过滤掉重复数据，然而这个比较，过滤的过程会占用系统资源，cpu 时间。

15、删除冗余和重复索引

16、where 子句中考虑使用默认值代替 null
如果 mysql 优化器发现，走索引比不走索引成本还要高，肯定会放弃索引，这些条件！=，>is null，is not null 经常被认为让索引失效，其实是因为一般情况下，查询的成本高，优化器自动放弃的。

如果把 null 值，换成默认值，很多时候让走索引成为可能，同时，表达意思会相对清晰一点。

17、尽量使用数字型字段，若只含数值信息的字段尽量不要设计为字符型
连表越多，编译的时间和开销也就越大。
把连接表拆开成较小的几个执行，可读性更高。
如果一定需要连接很多表才能得到数据，那么意味着糟糕的设计了。

18、索引不适合建在有大量重复数据的字段上，如性别这类型数据库字段
SQL 优化器是根据表中数据量来进行查询优化的，如果索引列有大量重复数据，Mysql 查询优化器推算发现不走索引的成本更低，很可能就放弃索引了。

19、尽量避免向客户端返回过多数据量，为了提高 group by 语句的效率，可以在执行到该语句前，把不需要的记录过滤掉

20、使用 explain 分析你 SQL 的计划

explain 各个字段代表的意思

常用 explain analyze，查看执行计划，每一步的耗时等

explain analyze sql

EXPLAIN ANALYZE是一个用于查询的分析工具，它向用户显示MySQL在查询上花费的时间以及原因。它将产生查询计划，并对其进行检测和执行，同时计算行数并度量执行计划中不同点上花费的时间。执行完成后，EXPLAIN ANALYZE将输出计划和度量结果，而不是查询结果。

这项新功能建立在常规的EXPLAIN基础之上，可以看作是MySQL 8.0之前添加的EXPLAIN FORMAT = TREE的扩展。EXPLAIN除了输出查询计划和估计成本之外，EXPLAIN ANALYZE还会输出执行计划中各个迭代器的实际成本。

explain sql

id ：select 查询的序列号，包含一组数字，表示查询中执行 select 子句或操作表的顺序

select_type ：查询类型或者是其他操作类型

table ：正在访问哪个表

type ：访问的类型

possible_keys ：显示可能应用在这张表中的索引，一个或多个，但不一定实际使用到

key ：实际使用到的索引，如果为 NULL，则没有使用索引

key_len ：表示索引中使用的字节数，可通过该列计算查询中使用的索引的长度

ref ：显示索引的哪一列被使用了，如果可能的话，是一个常数，哪些列或常量被用于查找索引列上的值

rows ：根据表统计信息及索引选用情况，大致估算出找到所需的记录所需读取的行数

Extra ：包含不适合在其它列中显示但十分重要的额外信息

id 与 table 字段
通过这两个字段可以完全判断出你的每一条 SQL 语句的执行顺序和表的查询顺序。
id 相同，从上到下
id 越大，优先级越高

select_type 字段

主要是用于区别普通查询、联合查询、子查询等的复杂查询

Type

显示的是访问类型，是较为重要的一个指标，结果值从最好到最坏依次是：
system>const>eq_ref>ref>range>index>ALL

system =表只有一行记录（等于系统表），这是 const 类型的特列，平时不会出现，这个也可以忽略不计。

const =表示通过索引一次就找到了,const 用于比较 primary key 或者 unique 索引。因为只匹配一行数据，所以很快如将主键置于 where 列表中，MySQL 就能将该查询转换为一个常量。

eq_ref =唯一性索引扫描，对于每个索引键，表中只有一条记录与之匹配。常见于主键或唯一索引扫描。
SELECT * FROM student AS S JOIN stu_course AS SC ON S.id= SC.cid

ref =非唯一性索引扫描，返回匹配某个单独值的所有行。本质上也是一种索引访问，它返回所有匹配某个单独值的行，然而，它可能会找到多个符合条件的行，所以他应该属于查找和扫描的混合体。

range(尽量保证)= 只检索给定范围的行,使用一个索引来选择行。key 列显示使用了哪个索引，一般就是在你的 where 语句中出现了 between、<、>、in 等的查询。这种范围扫描索引扫描比全表扫描要好，因为它只需要开始于索引的某一点，而结束语另一点，不用扫描全部索引。

index= Full Index Scan，index 与 ALL 区别为index 类型只遍历索引树。这通常比 ALL 快，因为索引文件通常比数据文件小。（也就是说虽然 all 和 Index 都是读全表，但 index 是从索引中读取的，而 all 是从硬盘中读的）。

ALL= Full Table Scan，将遍历全表以找到匹配的行。
一般来说，得保证查询至少达到 range 级别，最好能达到 ref。

extra

不推荐 join

之所以不建议使用join查询，最主要的原因就是join的效率比较低。

MySQL是使用了嵌套循环（Nested-Loop Join）的方式来实现关联查询的，简单点说就是要通过两层循环，用第一张表做外循环，第二张表做内循环，外循环的每一条记录跟内循环中的记录作比较，符合条件的就输出。

在MySQL 中，可以使用 JOIN 在两个或多个表中进行联合查询，join有三种，分别是inner join、left join 和 right join。

INNER JOIN（内连接,或等值连接）：获取两个表中字段匹配关系的记录。
○取两个表的交集部分
LEFT JOIN（左连接）：获取左表所有记录，即使右表没有对应匹配的记录。
取两个表的交集部分+左表中的数据
RIGHT JOIN（右连接）：与 LEFT JOIN 相反，用于获取右表所有记录，即使左表没有对应匹配的记录。
取两个表的交集部分+右表中的数据

在配合join一起使用的还有on关键字，用来指明关联查询的一些条件。

如果不能通过数据库做关联查询，那么需要查询多表的数据的时候要怎么做呢？

主要有两种做法：

1、在内存中自己做关联，即先从数据库中把数据查出来之后，我们在代码中再进行二次查询，然后再进行关联。

2、数据冗余，那就是把一些重要的数据在表中做冗余，这样就可以避免关联查询了。

3、宽表，就是基于一定的join关系，把数据库中多张表的数据打平做一张大宽表，可以同步到ES或者干脆直接在数据库中直接查都可以

Mysql

#basic

Mysql-架构与存储引擎上一篇

Java并发-并发容器下一篇