MySQL数据库查询和索引的优化方式

本篇内容主要讲解“MySQL数据库查询和索引的优化方式”，感兴趣的朋友不妨来看看。本文介绍的方法操作简单快捷，实用性强。下面就让小编来带大家学习“MySQL数据库查询和索引的优化方式”吧!

一、数据库查询性能的优化涉及到的技术面非常广，一般建议用以下几个手段实行：

1、减少数据访问

相关的技术就是建立合适的索引，将全表扫描、索引扫描(scan)等耗时的操作转化为索引查找(seek)。建立正确的索引，能让数据库查询性能提升100-1000倍甚至更高，就好比一本非常厚的词典，如果没有任何索引，你要查一个东西，那可是相当费尽，需要整本书查一遍，有索引就可以直接根据索引定位了。这是最重要的改善性能的途径。

2、减少返回的数据

在网络中传输数据，带宽是有限的，如果能按需提取最少量的数据，会起到不错的作用。这里需要注意的是，在SQL中，不要出现select *，而是需要什么字段，就提取什么字段。

3、减少与数据库交互次数

网络资源有限，显然，频繁与数据库交互，也是制约性能的一个因素。一个良好的建议就是，使用存储过程，或者批处理语句，这样能减少与数据库的交互，提升一部分性能。

4、减少CPU的负荷

这里，主要是使用缓存计划。在查询中，尽量使用参数化的查询。这样的话，数据库会对查询参数进行缓存，从而复用查询计划。

5、提升硬件性能

这是最后一招了，如果其他方面都已经做得非常不错了，性能瓶颈在CPU，内存和磁盘上，那采取提升硬件性能的方案就会显得比较合适了，否则还是先去优化其他的地方吧。

以上5个层次的优化带来的性能改善，是依次下降的，是一个倒置的金字塔。

二、下面说一下索引以及优化建议

索引能大幅度提高查询和排序性能，但是，在插入、删除、以及修改了主键的操作中，是需要维护索引顺序的。如果一张频繁变更的表，是不宜建立过多的索引的，索引带来的负面性能影响，将会得不偿失。

索引优化，是一个很考究的事情，它需要找到一个平衡点。

MySQL的优化主要分为结构优化(Scheme optimization)和查询优化(Query optimization)。本章讨论的高性能索引策略主要属于结构优化范畴。本章的内容完全基于上文的理论基础，实际上一旦理解了索引背后的机制，那么选择高性能的策略就变成了纯粹的推理，并且可以理解这些策略背后的逻辑。

索引优化建议

1、前缀索引

前缀索引就是用列的前缀代替整个列作为索引key，当前缀长度合适时，可以做到既使得前缀索引的选择性接近全列索引，同时因为索引key变短而减少了索引文件的大小和维护开销。

一般来说以下情况可以使用前缀索引：

字符串列(varchar,char,text等)，需要进行全字段匹配或者前匹配。也就是=‘xxx’ 或者 like ‘xxx%’

字符串本身可能比较长，而且前几个字符就开始不相同。比如我们对中国人的姓名使用前缀索引就没啥意义，因为中国人名字都很短，另外对收件地址使用前缀索引也不是很实用，因为一方面收件地址一般都是以XX省开头，也就是说前几个字符都是差不多的，而且收件地址进行检索一般都是like ’%xxx%’，不会用到前匹配。相反对外国人的姓名可以使用前缀索引，因为其字符较长，而且前几个字符的选择性比较高。同样电子邮件也是一个可以使用前缀索引的字段。

前一半字符的索引选择性就已经接近于全字段的索引选择性。如果整个字段的长度为20，索引选择性为0.9，而我们对前10个字符建立前缀索引其选择性也只有0.5，那么我们需要继续加大前缀字符的长度，但是这个时候前缀索引的优势已经不明显，没有太大的建前缀索引的必要了。

2、主键外检一定要建索引。

3、对 where,on,group by,order by 中出现的列使用索引。

4、尽量选择区分度高的列作为索引,区分度的公式是count(distinct col)/count(*)，表示字段不重复的比例，比例越大我们扫描的记录数越少，唯一键的区分度是1，而一些状态、性别字段可能在大数据面前区分度就是0。

5、对较小的数据列使用索引,这样会使索引文件更小,同时内存中也可以装载更多的索引键。

6、索引列不能参与计算，保持列“干净”，比如from_unixtime(create_time) = ’2014-05-29’就不能使用到索引，原因很简单，b+树中存的都是数据表中的字段值，但进行检索时，需要把所有元素都应用函数才能比较，显然成本太大。所以语句应该写成create_time = unix_timestamp(’2014-05-29’)。

7、为较长的字符串使用前缀索引。

8、尽量的扩展索引，不要新建索引。比如表中已经有a的索引，现在要加(a,b)的索引，那么只需要修改原来的索引即可。

9、不要过多创建索引, 权衡索引个数与DML之间关系，DML也就是插入、删除数据操作。这里需要权衡一个问题，建立索引的目的是为了提高查询效率的，但建立的索引过多，会影响插入、删除数据的速度，因为我们修改的表数据，索引也需要进行调整重建。

10、对于like查询，”%”不要放在前面。

SELECT * FROMhoudunwangWHEREunameLIKE'后盾%' -- 走索引。

SELECT * FROMhoudunwangWHEREunameLIKE "%后盾%" -- 不走索引。

11、查询where条件数据类型不匹配也无法使用索引。

字符串与数字比较不使用索引;

CREATE TABLEa(achar(10))。

EXPLAIN SELECT * FROMaWHEREa="1" – 走索引。

EXPLAIN SELECT * FROM a WHERE a=1 – 不走索引。

正则表达式不使用索引,这应该很好理解,所以为什么在SQL中很难看到regexp关键字的原因。

到此，相信大家对“MySQL数据库查询和索引的优化方式”有了更深的了解，不妨来实际操作一番吧！这里是亿速云网站，更多相关内容可以进入相关频道进行查询，关注我们，继续学习！