图片来自Pexels
MySQL用于存储和查询大量信息，因此其优化对于提高系统性能尤为重要。
由于MySQL的优化范围很广，从软件到硬件，从配置到应用，不可能面面俱到。
今天我将从开发者的角度介绍MySQL应用优化。包括数据类型、数据表查询/修改、索引与查询等。
数据类型优化
字段是用于存储数据的单位。设计好字段是设计数据库的第一步，也会影响系统的性能。
设计字段有一个基本原则，小而不大，即使用字节较少的字段而不是字节较大的字段。目的是节省空间，提高查询效率。
较小的字段占用较小的磁盘空间、内存空间，IO消耗也较小。下面是根据使用场景选择字段类型的一些经验，供大家参考。
数字型
手机号码：通常我们在存储手机号码的时候，喜欢使用Varchar类型。
如果是11位的手机号码，假设我们使用utf8编码，每个字节需要3个字节，所以需要11*33=33个字节来存储；如果我们使用bigint，则只需要8个字的部分就可以存储。
IP地址：同上，IP地址也可以用int（4个字节）来存储，通过INET_ATON()函数可以将IP地址转换为数字。这里需要注意溢出问题，需要使用unsigned int。
Age，枚举类型：可以用tinyint存储，只占用1个字节。无符号tinyint可以表示0-255的范围，基本够用了。
字符类型
Char和Varchar是我们常用的字符类型。 char(N)用于记录定长字符。如果长度小于N，则用空格填充。
varchar(N)用于存储变长字符，它会额外增加1-2个字节来存储字符串的长度。
Char和Varchar所占用的字节数根据数据库的编码格式的不同而不同。 Latin1占用1个字节，gbk占用2个字节，utf8占用3个字节。
从使用上来说，如果存储的内容是可变长度的，比如家庭地址，用户描述可以使用Varchar。
如果内容是固定长度的，比如UUID（36位）或者MD5加密字符串（32位），可以使用Char来存储。
时间类型
Datetime和Timestamp都是可以精确到秒的时间类型，但是Datetime占用8个字节，而Timestamp占用4个字节。
因此，创建日表时，Timestamp的选择范围有限。但是，它们有以下需要注意的细微差别。
区别一：存储数据的方式不同。
时间戳转换为UTC时间进行存储。查询时，转换为客户端时间返回。
区别二：两者的保存时间范围不同。
时间戳为“1970-01-01 00:00:01.000000”到“2038-01-19 03:14:07.999999”。
日期时间为“1000-01-01 00:00:00.000000”到“9999-12-31 23:59:59.999999”。
数据表查询/修改优化
说完如何高效地选择存储数据的类型，我们再来看看如何高效地读取数据。 MySQL作为关系型数据库，在处理复杂的业务时，经常会选择表之间的关联。
这就会导致我们在查询数据的时候，会关联其他的表，尤其是在查询一些多维数据的时候，这种关联性尤为突出。
这时，为了提高查询的效率，我们会对一些字段进行冗余处理，使得这些字段同时存在于多个表中。
然而，这会带来其他问题。例如，如果修改了冗余字段，则需要修改多个表，并且需要将修改保留在一个事务中。
如果处理不当，可能会导致数据不一致。需要根据具体情况采取查询策略。例如，需要跨多个表查询公司销售信息。
由于销售信息需要连接多个表，并且必须对销售量和金额进行求和，因此直接查询显然不合适。
可以生成后台服务来定期从相关表中检索信息并将结果计算到汇总表中。
在汇总表中需要查询的条件字段中添加索引信息，提高查询效率。这种方式仅限于查询数据不是很实时的情况。
在高速迭代开发过程中，业务快速变化，数据库会根据业务的变化进行迭代。因此，在开发新产品的初期，表结构会面临频繁的修改。
MySQL的ALTERTABLE操作性能对于大表来说是一个问题。 MySQL执行修改表结构操作的方式是创建一个新结构的空表，从旧表中取出所有数据，插入到新表中，然后删除旧表。
这个操作需要很多时间。如果内存不足且表数据较大且索引较多，就会导致表长时间被锁。
在极端情况下，某些ALTERTABLE 操作需要数小时甚至数天才能完成。
这里推荐两个技巧：
首先将数据库复制到非生产服务器上并修改其上的表。此时的修改不会影响生产数据库。修改完成后进行数据库切换，将非生产数据库切换到生产数据库。但需要注意的是，修改表结构时，生产库会生成一些数据。这里需要通过脚本按照时间间隔导入这部分数据。
“影子复制”是指生成一张表结构相同但名称不同的新数据表（改变数据结构后的表）。然后将原表的数据导入到新表中。导入成功后，停止数据库，修改原表和新表的名称，最后将数据访问指向新表。
正常运行后，删除原表。有现成的工具可以帮助完成以上操作，“在线架构更改”、“openark工具包”
如果只是删除或更改某个列的默认值，可以直接使用Alert table修改列和Alert tablealert列来实现。
索引优化
说完字段和表，我们来谈谈索引。网上关于指标优化的意见很多，都是在实际工作中总结出来的。这里没有一定的标准。
对于我们经常使用的InnoDB存储引擎（使用B-Tree索引），我们向大家推荐几种方法。
指数独立
“索引独立”意味着索引列不能是表达式或函数参数的一部分。例如：假设create_date和userId分别设置为User表中的索引。
select *from user where date(create_date)=curdate() selectuserId from user where userId+1=5 类似上面的语句使用索引作为函数中参数和表达式的一部分，不推荐这样做。
前缀索引
有时索引字段长度很大，如：VarChar、Blob、Text。这使得索引很大并且搜索时很慢。
通常的做法是在开头对部分字符进行索引，这样可以节省索引空间，提高索引效率。
既然索引所有字符是行不通的，那么索引多少个字符就是我们需要讨论的问题。
这里需要引入一个概念，索引选择性。索引选择性是指唯一索引值与数据表中记录总数的比例。
索引的选择性越高，查询效率越高，因为高选择性的索引可以让MySQL在搜索时过滤掉更多的行。
例如：有一个用户表，其中一个字段是FirstName。该场选择性的计算方法如下：
Select1.0*count(distinct FirstName)/count(*) from user 假设结果为0.75，然后使用left函数从字段中取出一些字符，例如从左边。
分别看选择性，目的是看选择了多少个字符，选择性最接近0.75。
从左侧取3 个字段时，选择1.0*count(distinct left(FirstName,3))/count(*) from user 结果为0.58 从左侧取4 个字段时，选择1.0*count(distinct left(FirstName) ,4))/count(*) 来自用户结果是0.67。当从左侧取5 个字段时，选择1.0*count(distinct left(FirstName,5))/count(*) from user 结果为0.74。从上面可以看出，字段FirstName取的是左边的字符。从3-5的获取可以看出，当从左边取第5个字符时，选择性0.74最接近0.75。
因此，可以使用FirstName的前5个字符作为前缀索引，这样索引的效果与FirstName的所有字符基本相同。而不是将整个FirstName 字段视为索引。
所以可以使用下面的语句来修改索引信息：
Alter tableuser 添加key(FirstName(5)) 多列索引及其顺序
多列索引，顾名思义，就是使用多列字段作为索引。假设您正在通过搜索LastName 和FirstName 条件来查找用户表中的数据。
可能会出现以下语句：
选择*来自LastName='Green' 的用户选择*来自LastName='Green' 且FirstName='Jack' 的用户选择*来自LastName='Green' 且（FirstName='Jack' 或FirstName='Michael' 的用户选择*来自用户，其中LastName='Green' 且FirstName='M' 和FirstName'N' 如果分别在LastName 和FirstName 上创建索引：
Select *from user where LastName=‘Green’ and FirstName=‘Jack’ 运行上述代码时，系统将使高选择性SQL 索引生效，而不会使用其他索引。因此，我们需要创建多列索引（合并索引）。
声明如下：
Alter table user add key(LastName, FirstName) 既然定义了多列索引，是不是还需要考虑索引顺序呢？在多列B-Tree 索引中，索引列的顺序意味着索引首先按照最左边的列进行排序，然后是第二列。
可以按升序或降序扫描索引，以满足使用与确切列顺序匹配的ORDERBY、GROUPBY 和DISTINCT 子句的查询。
因此，需要考虑多列索引的顺序。这里给出的建议是将最具选择性的索引列放在前面。
继续上面的示例，LastName 和FirstName 仍然用作多列索引。让我们看看谁应该放在前面。
按照选择性规则，编写以下SQL语句：
首先计算用户的LastName Selectcount(disctinc LastName)/count(*)的选择性，结果为0.02。然后计算用户的FirstName Selectcount(disctinc FirstName)/count(*) 的选择性，结果为0.05。 FirstName 的选择性高于LastName。选择性。因此，调整多列索引的顺序如下：
更改表用户添加键（名字，姓氏）覆盖索引
使用Select时，数据列只从索引中获取，不必从数据表中读取。换句话说，查询列必须被所使用的索引覆盖。
例如：LastName用作User表中的索引。如果编写如下查询语句：
从userLastName中选择LastName作为索引，并显示在查询内容中，那么LastName就是覆盖索引。
覆盖索引是查找行的有效方法。通过索引可以读取数据，不需要读取数据表中的数据。
并且覆盖索引会标注Usingindex，可以通过Explain语句查看。
解释查看覆盖指数标志
覆盖索引主要用于Count等一些聚合操作，以提高查询效率。例如，上面提到的来自用户的Selectcount(LastName)可以将LastName设置为索引。
还有列查询的表返回优化，如下：
Select LastName, FirstName from user where LastName=‘Jack’ 如果此时设置LastName为索引，则可以将LastName和FirstName设置为多列索引（联合索引）。
避免表返回行为。这里的回表是指查找二级索引，然后查找聚合索引，然后查找PK的过程。
这需要两次搜索。简单来说，使用覆盖索引后，你可以一次找到想要的记录，而不必检查第二次。
表达意图
查询优化
作为程序开发人员，使用最多的是SQL语句，最常见的操作是查询。
我们来看看影响查询记录的因素有哪些，查询的基本原理是什么，以及如何发现和优化SQL语句。
影响查询效率的因素
一般来说，影响查询的因素由三部分组成，如下：
响应时间由两部分组成，即服务时间和排队时间。服务时间是数据库处理查询所花费的时间。排队时间是指服务器等待某些资源所花费的时间。例如：I/O操作、等待其他事务释放锁的时间。
扫描记录行数，查询过程中数据库锁扫描的行记录。理想情况下，扫描的行数和返回的行数相同。不过，一般来说，扫描的行数会大于返回的行数。返回记录行数和实际要查询的结果。查询基础知识
查询流程图
说完影响查询效率的因素，我们再来看看MySQL中查询是如何工作的。它可以帮助我了解查询优化工作是在哪里执行的：
客户端向服务器发送查询。服务器首先检查查询缓存，如果命中缓存，则立即返回缓存中存储的结果。解析器解析SQL。它通过关键字解析SQL语句并生成相应的“解析树”。 MySQL 解析器将使用MySQL 语法规则验证和解析查询。预处理器根据一些MySQL规则进一步检查解析树是否合法并验证权限。例如，检查数据表和数据列是否存在，解析名称和别名是否存在歧义。 MySQL根据优化器生成的执行计划调用存储引擎的API来执行查询。将结果返回给客户端。如何查找慢SQL 查询
说完了影响慢查询的因素以及查询的基本流程，我们来看看如何找到慢SQL查询。这里MySQL提供了日志，可以在其中查询执行缓慢的SQL。
检查慢查询日志是否开启
SHOWVARIABLESLIKE'%slow_query_log%';
如果没有启用，通过命令启用慢查询日志
SETGLOBAL Slow_query_log=1;
设置慢查询日志的时间。这里的单位是秒，也就是说只有执行时间超过X秒的查询语句才会记录在这个日志中。这里的X就是你要设置的。 （下例设置3秒）
SETGLOBAL long_query_time=3;
检查有多少条SQL语句超过查询阈值（3秒）
解释分析SQL 查询
通过上面的方法，可以知道哪些SQL语句花费时间较多，那么如何分析这些SQL语句。毕竟我们的目标是通过分析来优化SQL并提高其性能。
通过在要执行的SQL语句前面放置Explain关键字，您可以模拟优化器执行SQL语句，从而了解MySQL如何处理您的SQL语句。
解释SQL执行图
上述各个字段的含义这里不再赘述。
SQL优化建议
如果我们发现慢查询SQL，我们需要针对问题进行优化。以下是针对几种常见SQL的一些优化建议。
网上有很多类似SQL优化的文章和例子。建议优化前先检查一下慢查询日志和Explain语句，然后再进行针对性的优化。
计数优化
搜索user表中所有id大于7的用户。如果是InnoDB存储引擎，则会逐行扫描。如果表中有很多记录，性能就会出现问题。
select count(*) from user where id7 如果先统计所有行，然后减去id=7的记录，会更快。
select (select count(*) - (select count(*) from user where id=7) from user) 如果有一个goods表项，里面有一个color字段来表示商品的颜色，如果需要知道颜色是蓝色还是红色货物的数量可以这样写：
Select count(color='blue' or color='red') from items Select count(*) from items where color='blue' and color='red' 但是，颜色本身是独占字段，因此可以优化后的SQL如下。
从items中选择count(color=‘blue’或null)为蓝色，count(color=‘red’或null)为红色GROUPBY优化
MySQL 使用索引来优化GROUPBY 查询。当无法使用索引时，会使用两种优化策略：临时表和文件排序分组。
其性能可以通过两个参数SQL_BIG_RESULT和SQL_SMALL_RESULT来提高。
这两个参数仅对Select 语句有效。它们告诉优化器使用临时表并对GROUPBY 查询进行排序。
SQL_SMALL_RESULT 告诉优化器结果集很小，可以将结果集放在内存中索引临时表中以避免排序操作。
如果是SQL_BIG_RESULT，则告诉优化器结果集可能非常大，建议使用磁盘临时表进行排序操作。
例如：
SelectSQL_BUFFER_RESULTfield1, count(*) from table1 groupby field1 假设两个表在做关联查询，那么在查询表中选择标识列（主键）的分组效率会很高。
例如，演员表和电影表通过actorId关联起来。查询如下：
select actor.FirstName, actor.LastName,count(*) from film inside join actor using(actorId) Group by actor.FirstName,actor.LastName 可以修改为：
从电影内部加入演员中选择actor.FirstName、actor.LastName、count(*) using(actorId) 按film.actorId 分组限制
Limit对于我们来说是非常熟悉的，尤其是在做分页操作的时候，我们经常会用到它。但当偏移量很大时就会出现问题。
例如，Limit 1000, 20 需要偏移1000 条数据才能返回接下来的20 条记录。前1000条数据将被丢弃。
根据上面的例子，SQL代码如下：
select name from user order by id limit1000,20这里通过id索引到第1001条记录，然后取出20条记录。这里我们利用id的索引直接跳过前1000条记录。
Select name from user where id=1001order by id limit 20总结
从开发人员的角度了解MySQL 应用程序优化。从数据类型的选择开始，分别给出了数值类型、字符类型、时间类型的示例。
接下来我们就来说说在优化数据表查询和修改时应该注意哪些细节。然后，我们讲了独立索引、前缀索引、多列索引、覆盖索引的优化方法。
最后，探讨了最常用查询的优化。从影响查询的因素到查询基础知识，再到如何发现慢速查询，我们以一些SQL 优化建议结束了我们的MySQL 应用程序优化之旅。
本文采摘于网络，不代表本站立场，转载联系作者并注明出处：https://www.iotsj.com//kuaixun/5666.html
用户评论
这标题太唬人了吧？想学习一下哪些高阶法术！
有18位网友表示赞同！
终于要讲真功夫了，那些小白技巧都不好用的时候...
有7位网友表示赞同！
我正被MySQL数据库卡着进度呢！希望这篇文章能拯救我orz
有10位网友表示赞同！
性能优化这个话题一直都是宝藏文章!
有13位网友表示赞同！
骨灰级高阶神技？期待能学到一些新知识！
有10位网友表示赞同！
我的表越来越大，mysql速度越来越慢，求帮帮忙！！
有8位网友表示赞同！
看来得把之前的优化笔记翻翻重温了.
有11位网友表示赞同！
MySQL性能提升确实需要点技巧才行, 期待深入内容!
有13位网友表示赞同！
索引、存储引擎、查询优化...这些都是老神仙都必须掌握的技能吧~
有7位网友表示赞同！
希望别太理论，能讲一些实际案例就好了。
有15位网友表示赞同！
我一直想了解一下MySQL的内 workings，这篇文章会不会说呢？
有16位网友表示赞同！
骨灰级指的是什么？高手们都在用哪些方法优化数据库？
有7位网友表示赞同！
文章地址哪里？
有15位网友表示赞同！
感觉能学到一招就值几个小时了！
有14位网友表示赞同！
MySQL性能优化确实是一门学问，需要不断钻研.
有7位网友表示赞同！
期待一些实战经验分享，能够解决实际问题!
有16位网友表示赞同！
这篇文章是不是作者自己写的？感觉很牛逼啊！
有16位网友表示赞同！
MySQL真是个好东西，可惜有时慢到爆炸...
有16位网友表示赞同！
性能优化，我可是想把数据库调到极致的那 kind!
有7位网友表示赞同！
快进快进...
有11位网友表示赞同！