MySQL高性能SQL语句的编写和优化
下文给大家带来有关MySQL高性能SQL语句的编写和优化内容，相信大家一定看过类似的文章。我们给大家带来的有何不同呢？一起来看看正文部分吧，相信看完MySQL高性能SQL语句的编写和优化你一定会有所收获。
员工经过长期磨合与沉淀，具备了协作精神，得以通过团队的力量开发出优质的产品。创新互联坚持“专注、创新、易用”的产品理念，因为“专注所以专业、创新互联网站所以易用所以简单”。公司专注于为企业提供成都网站建设、做网站、微信公众号开发、电商网站开发，小程序设计，软件按需策划设计等一站式互联网企业服务。
环境与数据库
因为个人爱好，我的系统是Linux mint19；数据库版本是MySQL 5.7.23-0ubuntu0.18.04.1 (Ubuntu)。简单介绍一下查看MySQL版本的方式
# 1.连接服务端，会直接输出Server版本 ckmike@ckmikePC:~$ mysql -uroot -p Enter password: Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 7 Server version: 5.7.23-0ubuntu0.18.04.1 (Ubuntu) Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql> #2.进入云服务器端后使用命令查看 mysql> status -------------- mysql Ver 14.14 Distrib 5.7.23, for Linux (x86_64) using EditLine wrapper Connection id: 7 Current database: Current user: root@localhost SSL: Not in use Current pager: stdout Using outfile: '' Using delimiter: ; Server version: 5.7.23-0ubuntu0.18.04.1 (Ubuntu) Protocol version: 10 Connection: Localhost via UNIX socket Server characterset: latin1 Db characterset: latin1 Client characterset: utf8 Conn. characterset: utf8 UNIX socket: /var/run/mysqld/mysqld.sock Uptime: 2 days 13 hours 58 min 13 sec Threads: 1 Questions: 53 Slow queries: 0 Opens: 112 Flush tables: 1 Open tables: 104 Queries per second avg: 0.000 -------------- mysql> #3.使用系统预定义函数查看 mysql> select version() -> ; +-------------------------+ | version() | +-------------------------+ | 5.7.23-0ubuntu0.18.04.1 | +-------------------------+ 1 row in set (0.00 sec) mysql> #4.使用Linux下查看 ckmike@ckmikePC:~$ mysql --version mysql Ver 14.14 Distrib 5.7.23, for Linux (x86_64) using EditLine wrapper #5.Linux借助安装工具查看版本 rpm -qa|grep mysql 当然如果是使用yum安装的同样可以用yum查看 yum list installed|grep mysql
备注：SQL是一种标准化的查询语言，抛开各个数据库厂商实现的不同，原理上都是相同的，但根据厂商实现的不同，获取相同结果集会有一些不一样的写法，比如说MySQL实现分页使用的是limit，而oracle是rownum,SQLServer是用在2005版本之前用top,后面使用row_number()。因为SQL的优化肯定是要针对厂商实现进行，我这里选MySQL,我使用最多最熟练，其次是oracle，而SQLServer除了在大学用，未曾在工作中使用。MySQL的流行以及为啥流行就不用我多说了。
数据库基础巩固
在说SQL性能优化之前，我们首先需要先掌握数据库基础知识，因为任何SQL都是跑在数据库引擎之上的，及好比讲解Linux命令和Shell语言一样，它们都是在某个内核或者某个引擎上的。
数据库语言分类
根据操作对象和操作粒度的不同，把数据库语言分为四大类：
DQL(Data Query Language)
数据库查询语言：这个就不用我多说了吧。
DDL（Data Definition Language）
数据库定义语言：关系模式的定义、修改、删除。
DML（Data Manipulation Language）
数据库操作语言：元祖数据的插入、修改、删除。
DCL（Data Control Language）
数据库控制语言：权限的授权与回收，事务的回滚与提交等。
不管是MySQL，oracle，还是SQLServer以及其他想PSQL,红狐狸都是由这些组成的一个产品。就类似安卓系统是一个标准，但安卓系统的各种发现版本各不相同，但核心都是依照安卓系统的标准来的，只是各个发行版本各自实现了一些不一样的交互效果不一样的特性，但本质都是一个标准。所以我们了解数据库这套标准是非常重要的，不管产品如何变，但内部核心标准是不会变的，不同的只是因为实现不同而写法不一，仅此而已。
说明：对于开发SQL的人来说，她关键在于DQL、DDL、DML。而优化也正好是这些部分。
基础概念
关系：简单来说就是一张二维表。
元祖：简单来说就是二维表的一行，也就是一条记录。
属性：简单说就是一个列，代表了一个属性，比如说：男女（sex）
备注：这是个人简单的理解，其实专业的定义在我看来是十分拗口的，所以我拷贝那些理论了，但请不清楚的一定要掌握，理解透彻，我的描述可能会误导你。
集合与代数操作
数学课又要开始了，好，我这里就不讲那些枯燥的数据理论。通俗的讲集合就是一类具有共同属性的元素组成，比如说人是一个集合概念：都有眼睛、鼻子、嘴巴、头、躯干、脚等各种器官与组织组成，这是生物上的组成，还有比如说有：性别、姓名、年龄、身高、体重等属性组成。而一个人就是人这个集合中的一个元素。在结合基础概念是不是就是集合类似与一张二维表（关系），而具体的人则是一个元祖。
说道集合必然需要提到集合的运算操作：并、差、笛卡尔积、选择、投影。
延续上面的人这个集合来简单说并操作：
并运算：比如说中国人=（（北京人）,（天津人）,......(台湾人),(香港人),(澳门人))等各个省份人的合计。当然在这里根据定义的不同其实可以包括一些移民的华人，但我这里是按照国籍，户籍来分的。就是把所有集合的元素集合在一起就是并运算。
其他操作如果不懂的可以自行补习。篇幅关系不再赘述。
范式
这个概念可能很多人已经不记得了，但只要你涉及数据库就一定会用到。所以这个的重要性是可想而知的。如果不知道的可到百度数据库范式进行了解与补习。
DQL
DQL是我们工作中最为常见，使用最为频繁的语言，比如说查看报表。
关键字:select
DDL
DDL是关系定义、修改、删除的语言，包括创建数据库、表、索引、触发器、存储过程、函数。
关键字:create
DML
DML是操作元祖的语言，包括元祖的删除、插入、修改
关键字:insert、update、delete
说道这里很多基础的知识点我们已经过了一遍，但是像一些分组、函数、排序呀我就不在这里说了，后面说道了我们再继续详细讲解。
聚焦SQL优化
上面所写都不是今天的重点，但确实是非常重要的基础。SQL性能的优化其实就是针对DQL语句来的，不管你删除、更新都是要先定位到元祖，所以我们常常说的SQL性能优化其实就是针对查询语句的部分进行优化的。
那么影响SQL查询性能的点有那些呢？我们接下来一个一个罗列。
搜索列加上索引
索引是提升搜索速度最直接最有效的方法，但切忌滥用索引，因为索引对更新，删除有负面影响，同时索引也很吃资源。
切忌使用*
我刚接触数据库时，我的大学老师（人称段龙王）就跟我们说：切忌使用select * from这样的语句,特别是在子查询中。
我们可以来做个实验。我现在有一张表user，里面有大概一百多万条数据。
# 统计条数，他们都是第一次执行，没有缓存过 mysql> select count(id) from user; +-----------+ | count(id) | +-----------+ | 1050506 | +-----------+ 1 row in set (0.17 sec) mysql> select count(*) from user; +----------+ | count(*) | +----------+ | 1050506 | +----------+ 1 row in set (0.19 sec) mysql> 感觉效果不明显，有时候count(*)还比count(id)更快。我想这个应该跟count()函数有关系，那么我们直接查询整个表看看。 mysql>select * from user; | 1050504 | ckmike699997 | 18 | 17996 | 2019-05-11 | | 1050505 | ckmike699998 | 18 | 11907 | 2019-05-11 | | 1050506 | ckmike699999 | 18 | 17726 | 2019-05-11 | | 1050507 | ckmike700000 | 18 | 1563 | 2019-05-11 | +---------+--------------+------+--------------+------------+ 1050506 rows in set (0.61 sec) mysql>select name from user; | ckmike699998 | | ckmike699999 | | ckmike700000 | +--------------+ 1050506 rows in set (0.35 sec)
之所以不要使用的原因是耗费资源，在网络传送上不必要的字段不用查出来，数据包能多小就多小，这是一个点。但如果在所有自动都要的情况下也尽量不要使用。如果在子查询中就更是如此了，会成倍成倍的放大消耗时间。这样的语句一定不要使用!
join（left,inner,right）关联
通常关联都是PK\FK进行关联，所以关联字段加上索引，且尽量不要使用函数。
where 查询字段优化
1.查询字段尽可能加上索引
2.where查询字段时仅能不适用函数，因为会使得索引失效，进行全表扫描。
3.where把可以确定更小结果集、可以更快扫选结果集的查询字段放在前面
4.尽量避免使用like
5.不可在“=”左边进行函数、算术运算或其他表达式运算，可能无法正确使用索引。
6.尽可能不用!=、<> ，会导致全表扫描
7.如果是一个复合索引的字段，那么顺序要按照索引定义属性来，否则无法保证索引生效
8.如果使用了变量@这类的会导致索引失效
like切忌使用‘%%’
like '%%'会使得索引失效，从而进行全表扫描，这是非常致命的。而索引的有点完全无法发挥，但却保留了索引的痛点。那么是不是就不能使用like呢？当然不是，我们可以使用like 'abdc%',这样索引的功能生效，又可以使用like,这是一种折中的处理方式，使用like是低效，
但需求有时候是强硬的。
子查询结果集优先过滤
限制子查询结果集的大小是非常有效的，尽量把过滤条件放在子查询中的where语句中，而不是放在外部过滤。
in与exists合理使用
in是使用的是内外表的hash连接，而exists的使用的是loop循环遍历字表。
那么这里就存在一个集合大小影响性能的问题了，如果两个集合差不多大，其实两个都差不多，则内表大的用exists，内表小的用in:
表A（小表），表B（大表）1： select * from A where cc in (select cc from B) 效率低，用到了A 表上cc 列的索引； select * from A where exists(select cc from B where cc=A.cc) 效率高，用到了B 表上cc 列的索引。
not in 与not exists合理使用
查询语句使用了not in 那么内外表都进行全表扫描，没有用到索引；而not exists 的子查询依然能用到表上的索引。所以无论那个表大，用not exists 都比not in 要快。
避免使用OR操作符
如果一个字段有索引，一个字段没有索引，将导致引擎放弃使用索引而进行全表扫描，这个时候我们可以拆分成两个或者多个结果集进行union。
对于上文关于MySQL高性能SQL语句的编写和优化，大家觉得是自己想要的吗？如果想要了解更多相关，可以继续关注我们的行业资讯板块。
分享名称：MySQL高性能SQL语句的编写和优化
网页链接：http://6mz.cn/article/iijsde.html