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引用顾客,而不是反过来。下图显示了没有任何数据冗余而设计的数据库架构。
5.使用最佳数据类型
1)MySQL支持各种数据类型,包括integer,float,double,date,datetime,varchar,text等。当设计数据库表时,应当尽可能使用能够满足特性的最短的数据类型。
比如,如果你在设计一个系统用户表,而该用户数量不会超过100个人,你就应该对user_ud使用'TINYINT'类型,该类型的取值范围为-128至128。如果一个字段需要存储date型值,使用datetime类型比较好,因为在查询的时候无需进行复杂的类型转换。
当值全为数字类型时,使用Integer。在进行计算时,Integer类型的值比文本类型的值速度更快。
以上就是提高工作效率:MySQL的优化技巧的详细内容,更多请关注php中文网其它相关文章!