MySQL百万数据优化二

Dear 丶 2023-09-28 16:07 7阅读 0赞

## 容易踩雷造成的全表扫描 ##

> **尽量避免null值判断，会导致数据库引擎放弃索引进行全表扫描**
> 
>     SELECT * FROM user WHERE age IS NULL
> 
> 优化方式：可以给字段添加默认值0，对0值进行判断。如下：
> 
>     SELECT * FROM user WHERE age=0

> **避免where条件中等号在左侧进行表达式、函数操作**
> 
> 全表扫描
> 
>     SELECT * FROM user WHERE age/10 = 9
> 
> 走索引
> 
>     SELECT * FROM user WHERE age = 10*9

> **查询条件不能用 <> 或者 !=**
> 
> 使用索引列作为条件进行查询时，需要避免使用<>或者!=等判断条件。如确实业务需要，使用到不等于符号，需要在重新评估索引建立，避免在此字段上建立索引，改由查询条件中其他索引字段代替。
> 
> **解决**：通过把不等于操作符改成or，可以使用索引，避免全表扫描 例如，把column<>'aaa'，改成column>'aaa' or column<'aaa'，就可以使用索引了

> **隐式类型转换造成不使用索引** 
> 
> 如下SQL语句由于索引对列类型为varchar，但给定的值为数值，涉及隐式类型转换，造成不能正确走索引。
> 
> 不走phone索引
> 
>     EXPLAIN SELECT * FROM `user` WHERE phone=1355535
> 
> 走phone索引
> 
>     EXPLAIN SELECT * FROM `user` WHERE phone='1355535

--------------------

## 查询优化 ##

> **避免出现select \***
> 
> 首先，select \* 操作在任何类型数据库中都不是一个好的SQL编写习惯。
> 
> 使用select \* 取出全部列，会让优化器无法完成索引覆盖扫描这类优化，会影响优化器对执行计划的选择，也会增加网络带宽消耗，更会带来额外的I/O,内存和CPU消耗。建议提出业务实际需要的列数，将指定列名以取代select \*。

> **避免出现不确定结果的函数**
> 
> 特定针对主从复制这类业务场景。由于原理上从库复制的是主库执行的语句，使用如now()、rand()、sysdate()、current\_user()等不确定结果的函数很容易导致主库与从库相应的数据不一致。另外不确定值的函数,产生的SQL语句无法利用query cache。

> **多表关联查询时，小表在前，大表在后(小表驱动大表)**
> 
> 在MySQL中，执行 from 后的表关联查询是从左往右执行的（Oracle相反），第一张表会涉及到全表扫描，所以将小表放在前面，先扫小表，扫描快效率较高，在扫描后面的大表，或许只扫描大表的前100行就符合返回条件并return了。
> 
> 例如：表A有100条数据，表B有1000万条数据；如果全表扫描表B，你品。

> **使用表的别名**
> 
> 当在SQL语句中连接多个表时，请使用表的别名并把别名前缀于每个列名上。这样就可以减少解析的时间并减少哪些由列名歧义引起的语法错误。例如：a.id

> **where字句替换HAVING字句**
> 
> 避免使用HAVING字句，因为HAVING只会在检索出所有记录之后才对结果集进行过滤，而where则是在聚合前刷选记录，如果能通过where字句限制记录的数目，那就能减少这方面的开销。HAVING中的条件一般用于聚合函数的过滤，除此之外，应该将条件写在where字句中。
> 
> **where和having的区别**：where后面不能使用组函数

> **调整Where字句中的连接顺序**
> 
> MySQL采用从左往右，自上而下的顺序解析where子句。根据这个原理，应将过滤数据**多**的**条件往前放**，最快速度缩小结果集。

> **JOIN代替子查询**
> 
> 尽量使用 Join 语句来替代子查询，因为子查询是嵌套查询，而嵌套查询会新创建一张临时表，而临时表的创建与销毁会占用一定的系统资源以及花费一定的时间，同时对于返回结果集比较大的子查询，其对查询性能的影响更大

> **JOIN优化1**
> 
> 要提升join语句的性能，就要尽可能减少嵌套循环的循环次数
> 
> 一个显著优化方式是对被驱动表的join字段建立索引，利用索引能快速匹配到对应的行，避免与内层表每一行记录做比较，极大地减少总循环次数。另一个优化点，就是连接时用小结果集驱动大结果集，在索引优化的基础上能进一步减少嵌套循环的次数
> 
> 如果难以判断哪个是大表，哪个是小表，可以用inner join连接，MySQL会自动选择小表去驱动大表

> **JOIN优化2**
> 
> MySQL中可以通过子查询来使用 SELECT 语句来创建一个单列的查询结果，然后把这个结果作为过滤条件用在另一个查询中。使用子查询可以一次性的完成很多逻辑上需要多个步骤才能完成的 SQL 操作，同时也可以避免事务或者表锁死，并且写起来也很容易。但是，有些情况下，子查询可以被更有效率的连接(JOIN)..替代。
> 
> 例如：假设要将所有没有订单记录的用户取出来，可以用下面这个查询完成：
> 
>     SELECT col1 FROM order 
>     WHERE CustomerID 
>     NOT in (SELECT CustomerID FROM order)
> 
> 如果使用连接(JOIN).. 来完成这个查询工作，速度将会有所提升。尤其是当 order表中对 CustomerID 建有索引的话，性能将会更好，查询如下：
> 
>     SELECT col1 FROM customerinfo  
>     LEFT JOIN order 
>     ON  customerinfo.CustomerID=order .CustomerID       
>     WHERE order .CustomerID IS NULL
> 
> 连接(JOIN).. 之所以更有效率一些，是因为 MySQL 不需要在内存中创建临时表来完成这个逻辑上的需要两个步骤的查询工作。

> **避免使用JOIN关联太多表**
> 
> 对于 MySQL 来说，是存在关联缓存的，缓存的大小可以由join\_buffer\_size参数进行设置，在 MySQL 中，对于同一个 SQL 多关联（join）一个表，就会多分配一个关联缓存，如果在一个 SQL 中关联的表越多，所占用的内存也就越大。如果程序中大量的使用了多表关联的操作，同时join\_buffer\_size设置的也不合理的情况下，就容易造成服务器内存溢出的情况，就会影响到服务器数据库性能的稳定性

> **正确使用联合索引**
> 
> 使用了 B+ 树的 MySQL 数据库引擎，比如 InnoDB 引擎，在每次查询复合字段时是从左往右匹配数据的，因此在创建联合索引的时候需要注意索引创建的顺序
> 
> 例如，我们创建了一个联合索引是idx(name,age,sex)，那么当我们使用，姓名+年龄+性别、姓名+年龄、姓名等这种最左前缀查询条件时，就会触发联合索引进行查询；然而如果非最左匹配的查询条件，例如，性别+姓名这种查询条件就不会触发联合索引

> **排序优化**
> 
> **利用索引扫描做排序**
> 
> MySQL有两种方式生成有序结果：其一是对结果集进行排序的操作，其二是按照索引顺序扫描得出的结果自然是有序的，但是如果索引不能覆盖查询所需列，就不得不每扫描一条记录回表查询一次，这个读操作是随机IO，通常会比顺序全表扫描还慢，因此在设计索引时，尽可能使用同一个索引既满足排序又用于查找行
> 
> 例如：
> 
>     --建立索引（date,staff_id,customer_id）
>     
>     select staff_id, customer_id from test 
>     where date = '2010-01-01' 
>     order by staff_id,customer_id;
> 
> 只有当索引的列顺序和ORDER BY子句的顺序完全一致，并且所有列的排序方向都一样时，才能够使用索引来对结果做排序

> **数据处理**
> 
> 尽量将数据的处理工作放到服务器上，减少网络的开销，如 使用存储过程。存储过程是编译好、优化过、并且被组织到一个执行规划里、且存储在数据库中的SQL语句，是控制流语言的集合，速度当然快。反复执行的动态SQL,可以使用临时存储过程，该过程（临时表）被放在Tempdb中。

> **线程数配置**
> 
> 当服务器的内存够多时，配制线程数量 = 最大连接数+5，这样能发挥最大的效率；否则使用 配制线程数量<最大连接数启用SQL SERVER的线程池来解决,如果还是数量 = 最大连接数+5，严重的损害服务器的性能。

> **exists代替count**
> 
> 尽量使用exists代替select count(1)来判断是否存在记录，count函数只有在统计表中所有行数时使用，而且count(1)比count(\*)更有效率。在整个表使用count(\*)，可能锁住整张表

> **SQL关键字大写**
> 
> sql语句用大写，因为oracle 总是先解析sql语句，把小写的字母转换成大写的再执行。MySQL ：同样一句sql，性能比较：关键字大写>所有语句大写>所有语句小写，推荐关键字大写
> 
> 可以看这个老哥的测试结果：[mysql的大小写对性能的影响问题\_T-OPEN的博客-CSDN博客\_mysql表大小对性能影响][mysql_T-OPEN_-CSDN_mysql]

> **如果明确知道结果只返回一条可以加limit** 
> 
>     select * from `user` where `name`='xxx' #2.648s
>     
>     --改为
>     
>     select * from `user` where `name`='xxx' LIMIT 1 #0.279s

> **优化group by语句**
> 
> 默认情况下，MySQL 会对GROUP BY分组的所有值进行排序，如 “GROUP BY col1，col2，....;” 查询的方法如同在查询中指定 “ORDER BY col1，col2，...;” 如果显式包括一个包含相同的列的 ORDER BY子句，MySQL 可以毫不减速地对它进行优化，尽管仍然进行排序。
> 
> 因此，如果查询包括 GROUP BY 但你并不想对分组的值进行排序，你可以指定 ORDER BY NULL禁止排序。例如：
> 
>      SELECT col1, col2, COUNT(*) FROM table GROUP BY col1, col2 ORDER BY NULL ;

> **优化union查询**
> 
> MySQL通过创建并填充临时表的方式来执行union查询。除非确实要消除重复的行，否则建议使用union all。原因在于如果没有all这个关键词，MySQL会给临时表加上distinct选项，这会导致对整个临时表的数据做唯一性校验，这样做的消耗相当高。
> 
>     --高效：
>     
>     SELECT COL1, COL2, COL3 FROM TABLE WHERE COL1 = 10  
>     
>     UNION ALL
>     
>     SELECT COL1, COL2, COL3 FROM TABLE WHERE COL3= 'TEST';
>     
>     --低效：
>     
>     SELECT COL1, COL2, COL3 FROM TABLE WHERE COL1 = 10  
>     
>     UNION  
>     
>     SELECT COL1, COL2, COL3 FROM TABLE WHERE COL3= 'TEST';

> **拆分复杂SQL为多个小SQL，避免大事务**
> 
> 1. 简单的SQL容易使用到MySQL的QUERY CACHE；
> 
> 2. 减少锁表时间特别是使用MyISAM存储引擎的表；
> 
> 3. 可以使用多核CPU。

> **truncate代替delete**
> 
> 当删除全表中记录时，使用delete语句的操作会被记录到undo块中，删除记录也记录binlog，当确认需要删除全表时，会产生很大量的binlog并占用大量的undo数据块，此时既没有很好的效率也占用了大量的资源。
> 
> 使用truncate替代，不会记录可恢复的信息，数据不能被恢复。也因此使用truncate操作有其极少的资源占用与极快的时间。另外，使用truncate可以回收表的水位，使自增字段值归零。

> **合理的分页方式以提高分页效率**
> 
> 使用合理的分页方式以提高分页效率 针对展现等分页需求，合适的分页方式能够提高分页的效率。
> 
>     例1：SELECT * FROM `user` WHERE id=10000 and age=23 ORDER BY id asc LIMIT 0,15
> 
> 上述例子通过一次性根据过滤条件取出所有字段进行排序返回。数据访问开销=索引IO+索引全部记录结果对应的表数据IO。因此，该种写法越翻到后面执行效率越差，时间越长，尤其表数据量很大的时候。
> 
> 适用场景：当中间结果集很小（10000行以下）或者查询条件复杂（指涉及多个不同查询字段或者多表连接）时适用。
> 
>     例2：SELECT a.* FROM (SELECT * FROM `user` WHERE id=10000 and age=23 ORDER BY id asc LIMIT 0,15)a
> 
> 上述例子必须满足a表主键是id列，且有覆盖索引secondary key:(thread\_id, deleted, gmt\_create)。通过先根据过滤条件利用覆盖索引取出主键id进行排序，再进行join操作取出其他字段。数据访问开销=索引IO+索引分页后结果对应的表数据IO。因此，该写法每次翻页消耗的资源和时间都基本相同，就像翻第一页一样。
> 
> 适用场景：当查询和排序字段（即where子句和order by子句涉及的字段）有对应覆盖索引时，且中间结果集很大的情况时适用。

> **GROUP BY的效率**
> 
> 提高GROUP BY语句的效率, 可以通过将不需要的记录在GROUP BY 之前过滤掉.下面两个查询返回相同结果，但第二个明显就快了许多.
> 
>     低效: SELECT job, AVG(sal) FROM emp GROUP BY job HAVING job='president' OR job='manger'
>     
>     高效: SELECT job, AVG(sal) FROM emp WHERE job='president' OR job ='manager' GROUP BY job

> **时间排序**
> 
> 查询时间字段作为条件，可以使用order by desc ，desc的速度会比asc快。因为是对时间建立了索引，最近的时间一定在最后面，升序查询，需要查询更多的数据，才能过滤出相应的结果，所以慢。

>  **order By 优化**
> 
> 当查询语句中使用 order by 进行排序时，如果没有使用索引进行排序，会出现 filesort 文件内排序，这种情况在数据量大或者并发高的时候，会有性能问题，需要优化。
> 
> filesort 出现的情况举例：
> 
>  *  order by 字段不是索引字段
>  *  order by 字段是索引字段，但是 select 中没有使用覆盖索引，如：select \* from table order by age asc;
>  *  order by 中同时存在 ASC 升序排序和 DESC 降序排序，如：select a, b from table order by a desc, b asc;
>  *  order by 多个字段排序时，不是按照索引顺序进行 order by，即不是按照最左前缀法则，如：select a, b from table order by b asc, a asc;
> 
> 解决方法：避免以上情况 ，或者参考如下：
> 
>     SELECT * FROM `user`  FORCE INDEX(create_time) ORDER BY create_time
>     --或者
>     SELECT create_time FROM `user`   ORDER BY create_time
>     --再或者
>     SELECT *  FROM `user`   ORDER BY create_time LIMIT 10
> 
> **如果a字段上没有索引，MySQL innodb引擎 就会有两种排序方式：**
> 
> **全字段排序**：将所有要选择的字段加入到sort\_buffer中，然后在内存或者外部进行排序。如果能在内存中进行排序就在内存中进行排序。
> 
> 如果要排序的数据量小于 sort\_buffer\_size，排序就在内存中完成。但如果排序数据量太大，内存放不下，则不得不利用磁盘临时文件辅助排序。
> 
> 如果查询要返回的字段很多的话，那么 sort\_buffer 里面要放的字段数太多，这样内存里能够同时放下的行数很少，要分成很多个临时文件，排序的性能会很差。这个时候MySQL就会采用rowId排序。
> 
> **rowid排序**：MySQL取出需要排序的字段和ID放入sort\_buffer中进行排序，最后按照排序的结果，通过ID回表，返回数据到客户端。
> 
> MySQL 的一个设计思想是如果内存够，就要多利用内存，尽量减少磁盘访问。所以对应sort\_buffer足够大的情况，MySQL会优选选择全字段排序。

> **OR**
> 
> OR改写成IN：OR的效率是n级别，IN的效率是log(n)级别，in的个数建议控制在200以内

--------------------

## 设计优化 ##

> **前缀索引**
> 
> 索引越长占用的磁盘空间就越大，那么在相同数据页中能放下的索引值也就越少，这就意味着搜索索引需要的查询时间也就越长，进而查询的效率就会降低，所以我们可以适当的选择使用前缀索引，以减少空间的占用和提高查询效率比如，邮箱的后缀都是固定的“@xxx.com”，那么类似这种后面几位为固定值的字段就非常适合定义为前缀索引
> 
>     alter table test add index index2(email(6));
> 
> 使用前缀索引，定义好长度，就可以做到既节省空间，又不用额外增加太多的查询成本，需要注意的是，前缀索引也存在缺点，MySQL无法利用前缀索引做order by和group by 操作，也无法作为覆盖索引

> **尽量避免使用NULL**
> 
> NULL在MySQL中不好处理，存储需要额外空间，运算也需要特殊的运算符，含有NULL的列很难进行查询优化，应当指定列为not null，用0、空串或其他特殊的值代替空值，比如定义为int not null default 0

> **最小数据长度**
> 
> 越小的数据类型长度通常在磁盘、内存和CPU缓存中都需要更少的空间，处理起来更快

> **使用最简单数据类型**
> 
> 简单的数据类型操作代价更低，比如：能使用 int 类型就不要使用 varchar 类型，因为 int 类型比 varchar 类型的查询效率更高

> **尽量少定义 text 类型**
> 
> text 类型的查询效率很低，如果必须要使用 text 定义字段，可以把此字段分离成子表，需要查询此字段时使用联合查询，这样可以提高主表的查询效率

> **适当分表、分库策略**
> 
> **分表**是指当一张表中的字段更多时，可以尝试将一张大表拆分为多张子表，把使用比较高频的主信息放入主表中，其他的放入子表，这样我们大部分查询只需要查询字段更少的主表就可以完成了，从而有效的提高了查询的效率.
> 
> **分库**是指将一个数据库分为多个数据库。比如我们把一个数据库拆分为了多个数据库，一个主数据库用于写入和修改数据，其他的用于同步主数据并提供给客户端查询，这样就把一个库的读和写的压力，分摊给了多个库，从而提高了数据库整体的运行效率

> **数据类型的优化**
> 
>  *   *  更小的通常更好,应当尽量使用可以正确存储数据的最小数据类型,更小的数据类型通常更快,因为它们占用更少的磁盘,内存和CPU缓存,并且处理时需要的CPU周期更少,但是要确保没有低估需求存储的值得范围,如果无法确定那个数据类型,就选择你认为不会超过范围的最小类型
>  *  **简单就好**
>     
>      *  简单数据类型的操作通常需要更少的CPU周期,例如:
>      *  整型比字符串代价更低,因为字符串集和校对规则是字符串比较,比整形比较更复杂
>      *  使用mysql自建类型而不是字符串来存储日期和实践
>      *  用整型存储ip地址 使用 INET\_ATON函数进行转换 select INET\_ATON(192.168.0.1) select INET\_ATOA(19216801)
>  *  **实际细则**
>     
>      *  **整形类型**
>      *   *   *  可以使用几种整形类型:TINYINT,SMALLINT,MEDIUMINT,INT,BIGINT 分别8,16,24,32,64字节存储空间,尽量使用满足需求的最小数据类型
>          *  **字符跟字符串类型**
>             
>              *  **varchar**:根据实际内容长度保存数据
>                 
>                  *  使用最小的符合需求的长度
>                  *  varchar(n) n小于等于255 使用额外一个字节保存长度,n>255 使用额外两个字节保存长度
>                  *  varchar(5)于varchar(255)保存同样的内容,硬盘存储空间相同,但内存空间占用不同,是指定的大小
>                  *  varchar在mysql5.6之前变更长度,或者从255一下变更到255以上时,都会导致锁表
>                  *  应用场景:
>                     
>                      *  存储长度波动较大的数据,如:文章,有的很长有的很短
>                      *  字符串很少更新的场景,每次更新后都会重算并使用额外的存储空间保存长度
>                      *  适合保存多字节字符,如汉字,特殊字符等
>              *  char:固定长度字符串
>                 
>                  *  最大长度255
>                  *  会自动删除末尾空格
>                  *  检索效率,写效率会比varchar高,以空间换时间
>                  *  应用场景
>                     
>                      *  存储长度波动不大的数据,如 md5摘要
>                      *  存储短字符串,经常更新的新字符串
>              *  blob和text类型
>                 
>                  *  MySQL把每个blob和text值当作一个独立的对象处理.
>                  *  两者都是为了存储很大数据而设计的字符串类型,分别采用二进制和字符串方式存储
>          *  **datetime和timestamp**
>             
>              *  dateTime
>                 
>                  *  占8个字节
>                  *  与时区无关,数据库底层时区配置,对dateTime无效
>                  *  可以保存到毫秒
>                  *  可以保存时间范围大
>                  *  不要使用字符串存储日期类型,占用空间大,损失日期类型函数的便捷性
>              *  timestamp
>                 
>                  *  占用4个字节
>                  *  时间范围:1970-01-01到2038-01-19
>                  *  精确到秒
>                  *  采用整型存储
>                  *  依赖数据库设置的时区
>                  *  自动更新timestamp列的值
>              *  date
>                 
>                  *  占用的节字节数比字符串,datetime,int存储要少,使用date类型只需要3个字
>                  *  使用date类型还可以利用日期函数进行日之间的计算
>                  *  date类型用于保存1000-01-01到9999-12-31之间的日期
>          *  使用枚举代替字符串
>             
>              *   *  有时使用枚举代替常用的字符串类型,MySQL村粗枚举类型会非常紧凑,会根据列表值得数据压缩到一个或两个列表的位置保存为整数,并且在表的.frm文件中保存 "数字-字符串"映射关系的查找表
>                  *  create table enum\_test(e enum('fish','apple','dog') not null); // e是列名
>                  *  insert into enum\_test(e) values('fish'),('dog'),('apple');
>                  *  select e+0 from enum\_test;
>                  *  select \* from enum\_test where e=1;
> 
>  *   *  特殊数据类型
>         
>          *  人们经常使用varchar(15)来存储ip地址然而他的本质是32位无符号整数不是字符串,可以使用inet\_aton()和 inet\_ntoa函数在这两种表示方法之间转换
>          *  案例:  
>             select inet\_aton('1.1.1.1')  
>             select inet\_ntoa('12312312')

> **适当的数据冗余**
> 
> 1.  被频繁引用且只能通过join2张表(或者更多)大表的方式才能得到独立小字段.
> 2.  这样的场景由于每次join仅仅只是为了获取某个小字段的值,join到记录又大,会造成大量不必要的IO,完全可以通过空间换取时间的方式来优化.不过,冗余的同时需要确保数据的一致性不遭到破坏,确保更新时间的同时冗余字段也被更新

> **适当拆分**
> 
> 当我们的表中存在类十余text或者是很大的varchar类型的大字段的时候，如果我们大部分访问这张表的时候不需要这个字段，我们就该义无反顾的将其拆分到另外的独立表中，以减少常用数据所占得存储空间，这样做的一个明显好处就是每个数据块中可以存储的数据条可以大大增加。减少物理IO次数，也能大大 提高缓存命中率。
> 
>  *  垂直切分：按照业务切分，把不同的表放到不同的物理服务器里面 ，这样请求会被分配到不同的物理服务器减少服务器压力
>  *  水平切分：可以按1到一千字段放到放一个物理服务器。。。

这是小编在开发学习使用和总结的小Demo， 这中间或许也存在着不足，希望可以得到大家的理解和建议。如有侵权联系小编！

[mysql_T-OPEN_-CSDN_mysql]: https://blog.csdn.net/weter_drop/article/details/103362169