专栏首页眯眯眼猫头鹰的小树杈猫头鹰的深夜翻译:如何优化MYSQL查询

猫头鹰的深夜翻译:如何优化MYSQL查询

1. 在所有用于where,order bygroup by的列上添加索引

索引除了能够确保唯一的标记一条记录,还能是MySQL服务器更快的从数据库中获取结果。索引在排序中的作用也非常大。

Mysql的索引可能会占据额外的空间,并且会一定程度上降低插入,删除和更新的性能。但是,如果你的表格有超过10行数据,那么索引就能极大的降低查找的执行时间。

强烈建议使用“最坏情况的数据样本”来测试MySql查询,从而更清晰的了解查询在生产中的行为方式。

假设你正在一个超过500行的数据库表中执行如下的查询语句:

mysql>select customer_id, customer_name from customers where customer_id='345546'

上述查询会迫使Mysql服务器执行一个全表扫描来获得所查找的数据。

型号,Mysql提供了一个特别的Explain语句,用来分析你的查询语句的性能。当你将查询语句添加到该关键词后面时,MySql会显示优化器对该语句的所有信息。

如果我们用explain语句分析一下上面的查询,会得到如下的分析结果:

mysql> explain select customer_id, customer_name from customers where customer_id='140385';
+----+-------------+-----------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table     | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-----------+------------+------+---------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | customers | NULL       | ALL  | NULL          | NULL | NULL    | NULL |  500 |    10.00 | Using where |
+----+-------------+-----------+------------+------+---------------+------+---------+------+------+----------+-------------+

可以看到,优化器展示出了非常重要的信息,这些信息可以帮助我们微调数据库表。首先,MySql会执行一个全表扫描,因为key列为Null。其次,MySql服务器已经明确表示它将要扫描500行的数据来完成这次查询。

为了优化上述查询,我们只需要在customer_id这一列上添加一个索引m即可:

mysql> Create index customer_id ON customers (customer_Id);
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

如果我们再次执行explain语句,会得到如下结果:

mysql> Explain select customer_id, customer_name from customers where customer_id='140385';
+----+-------------+-----------+------------+------+---------------+-------------+---------+-------+------+----------+-------+
| id | select_type | table     | partitions | type | possible_keys | key         | key_len | ref   | rows | filtered | Extra |
+----+-------------+-----------+------------+------+---------------+-------------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | customers | NULL       | ref  | customer_id   | customer_id | 13      | const |    1 |   100.00 | NULL  |
+----+-------------+-----------+------------+------+---------------+-------------+---------+-------+------+----------+-------+

从上述的输出结果,显然MySQL服务器会使用索引customer_id来查询表格。可以看需要扫描的行数为1。虽然我只是在一个行数为500的表格中执行这条查询语句,索引在检索一个更大的数据集的时候优化程度更加明显。

2. 用Union优化Like语句

有时候,你可能需要在查询中使用or操作符进行比较。当or关键字在where子句中使用频率过高的时候,它可能会使MySQL优化器错误的选择全表扫描来检索记录。union子句可以是查询执行的更快,尤其是当其中一个查询有一个优化索引,而另一个查询也有一个优化索引的时候。

比如,在first_namelast_name上分别存在索引的情况下,执行如下查询语句:

mysql> select * from students where first_name like 'Ade%' or last_name like 'Ade%'

上述查询和下面使用union合并两条充分利用查询语句的查询相比,速度慢了许多。

mysql> select * from students where first_name like 'Ade%' union all select * from students where last_name like 'Ade%' 

3. 避免使用带有前导通配符的表达式

当查询中存在前导通配符时,Mysql无法使用索引。以上面的student表为例,如下的查询会导致MySQL执行全表扫描,及时first_name字段上加了索引。

mysql> select * from students where first_name like '%Ade'

使用explain分析得到如下结果:

mysql> explain select * from students where first_name like  '%Ade'  ;
+----+-------------+----------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table    | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+----------+------------+------+---------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | students | NULL       | ALL  | NULL          | NULL | NULL    | NULL |  500 |    11.11 | Using where |
+----+-------------+----------+------------+------+---------------+------+---------+------+------+----------+-------------+

如上所示,Mysql将扫描全部500行数据,这将使得查询极其缓慢。

4. 充分利用MySQL的全文检索

如果你正面临着使用通配符查询数据,但是并不想降低数据库的性能,你应当考虑使用MySQL的全文检索(FTS),因为它比通配符查询快得多。除此以外,FTS还能够返回质量更好的相关结果。

添加一个全文检索索引到student样表上的语句如下:

mysql> alter table students add fulltext(first_name, last_name)';
mysql> select * from students where match(first_name, last_name) against ('Ade');

在上面的例子中,我们针对搜索关键字Ade指定了想要匹配的列(first_name, last_name)。如果查询优化器如上语句的执行情况,将得到下面的结果:

mysql> explain Select * from students where match(first_name, last_name) AGAINST ('Ade');
+----+-------------+----------+------------+----------+---------------+------------+---------+-------+------+----------+-------------------------------+
| id | select_type | table    | partitions | type     | possible_keys | key        | key_len | ref   | rows | filtered | Extra                         |
+----+-------------+----------+------------+----------+---------------+------------+---------+-------+------+----------+-------------------------------+
|  1 | SIMPLE      | students | NULL       | fulltext | first_name    | first_name | 0       | const |    1 |   100.00 | Using where; Ft_hints: sorted |
+----+-------------+----------+------------+----------+---------------+------------+---------+-------+------+----------+-------------------------------+

5. 优化数据库架构

规范化

首先,规范化所有数据库表,即使可能会有些损失。比如,如果你需要创建两张表分别用来记录customers和orders数据,你应当在order表上用顾客id引用顾客,而不是反过来。下图显示了没有任何数据冗余而设计的数据库架构。

除此以外,对相似的值使用同一种数据类型类存储。

使用最佳数据类型

MySQL支持各种数据类型,包括integer,float,double,date,datetime,varchar,text等。当设计数据库表时,应当尽可能使用能够满足特性的最短的数据类型。

比如,如果你在设计一个系统用户表,而该用户数量不会超过100个人,你就应该对user_ud使用'TINYINT'类型,该类型的取值范围为-128至128。如果一个字段需要存储date型值,使用datetime类型比较好,因为在查询的时候无需进行复杂的类型转换。

当值全为数字类型时,使用Integer。在进行计算时,Integer类型的值比文本类型的值速度更快。

避免NULL

NULL指该列没有任何值。你应当尽可能的避免这类型的值因为他们会损害数据库结果。比如你需要获得数据库中所有订单金额的和,但是某个订单记录中金额为null,如果不注意空指针,很有可能导致计算结果出现异常。在某些情况下,你可能需要为列定义一个默认值。

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

我来说两句

0 条评论
登录 后参与评论

相关文章

  • 猫头鹰的深夜翻译:spring事务管理

    Spring Framework提供了两种编程式事务管理方法。 a. 使用TransactionTemplate (Spring推荐这种实现): Context...

    眯眯眼的猫头鹰
  • leetcode453. Minimum Moves to Equal Array Elements

    从一个长度为n非空整数数组中,找到能够使得数组中每个元素的值都相等的最少步数,一步是指选择对数组中的n-1个元素加一。比如将[1,2,3]这个数组达到均等的最小...

    眯眯眼的猫头鹰
  • docker指令学习记录

    本文为学习整理和参考文章,不具有教程的功能。其次,后面将会陆续更新各种应用的容器化部署的实践,如MySQL容器化,Jenkins容器化,以供读者参考。

    眯眯眼的猫头鹰
  • SQL操作五

    爱撒谎的男孩
  • salesforce lightning零基础学习(四) 事件(component events)简单介绍

    用户1169343
  • 如何在Ubuntu上配置MySQL组复制

    传统MySQL复制备份架构是将数据从一个数据库同步到另一个数据库。主要操作是将主数据库的数据复制到辅助服务器,当主服务器数据出问题时,自动同步辅助服务器的数据到...

    爆栈工程师
  • MySQL数据库的事务隔离和MVCC

    事务必须服从ISO/IEC所制定的ACID原则。ACID是原子性(atomicity)、一致性(consistency)、隔离性(isolation)、持久性(...

    数据和云
  • 初识Hibernate之关联映射(二)

         上篇我们介绍了关联映射的几种形式,有单向多对一,单向一对多,还有双向一对多。本篇接着介绍有关关联映射的其他几种映射方式,主要有以下几种: 基于外键的单...

    Single
  • 深入理解HDFS 一

    Hadoop的发展至今已经有十余年的历史了,其核心设计HDFS和MapReduce,分别解决了海量数据的存储和计算这两个问题。

    soundhearer
  • 【眼见为实】自己动手实践理解数据库READ COMMITTED && MVCC

    撸码那些事

扫码关注云+社区

领取腾讯云代金券