提高工作效率：MySQL的优化技巧-mysql教程-PHP中文网

本篇文章给大家带来的内容是关于提高工作效率：MySQL的优化技巧，有一定的参考价值，有需要的朋友可以参考一下，希望对你有所帮助。

1、在所有用于where,order by和group by的列上添加索引

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

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

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

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

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

登录后复制

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

4）型号，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行的数据来完成这次查询。

5）为了优化上述查询，我们只需要在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  |
+----+-------------+-----------+------------+------+---------------+-------------+---------+-------+------+----------+-------+

登录后复制

2. 用Union优化Like语句

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

比如，在first_name和last_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 wherelast_name like 'Ade%'

登录后复制

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

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

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

登录后复制

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

 | 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. 优化数据库架构

1）规范化

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