MySql(五)之sql优化

2023-05-16

##概述

作为一个高级程序员，不仅要熟练使用sql语句，更应该使用高效的sql语句。

本篇blog讲解的内容主要包括：

索引的使用。
执行计划分析。
sql优化常见案例分析。

##索引

一，什么是索引

索引的作用只有一个，提高查找效率。

如下面sql语句：

select name from person where age = 16;

在正常情况下，MySql是从第一条数依次遍历，直到读完整个表才能得到所有age等于16的数据。

如果对age字段建立索引，MySql会维护一个索引表，索引表中存储不同age与数据对对应关系，当查找age等于16的数据时会先在索引表中找到age等于16对应的数据id，然后直接根据数据id从数据表中需要的数据。这就类似于新华字典中的拼音查字法，先从拼音序列中找出这个字在哪一页，然后直接去那一页去找，避免翻遍整个字典，很大的提供了查找效率。

二，索引的利弊分析

上面说到创建索引会很大的提高查找效率，但索引也有弊端。

创建索引系统会自动维护一个索引表，每当数据增加，更新，删除时都需要更改索引表，所以创建索引会降低增加，更新，删除的效率。

三，创建索引的原则

创建索引针对的对象是字段，所以我们要找出适合创建索引的字段。

建议创建索引的列：

定义主键的数据列一定要建立索引。
定义有外键的数据列一定要建立索引。
对于经常查询的数据列最好建立索引。
对于需要在指定范围内的快速或频繁查询的数据列最好创建索引;
经常用在WHERE句中的数据列最好创建索引。
经常出现在关键字order by、group by、distinct后面的字段，建立索引。如果建立的是复合索引，索引的字段顺序要和这些关键字后面的字段顺序一致，否则索引不会被使用。

不建议创建索引的列：

对于那些查询中很少涉及的列，重复值比较多的列不要建立索引。
对于定义为text、image和bit的数据类型的列不要建立索引。
对于经常存取的列避免建立索引
限制表上的索引数目。对一个存在大量更新操作的表，所建索引的数目一般不要超过3个，最多不要超过5个。索引虽说提高了访问速度，但太多索引会影响数据的更新操作。
对复合索引，按照字段在查询条件中出现的频度建立索引。在复合索引中，记录首先按照第一个字段排序。对于在第一个字段上取值相同的记录，系统再按照第二个字段的取值排序，以此类推。因此只有复合索引的第一个字段出现在查询条件中，该索引才可能被使用,因此将应用频度高的字段，放置在复合索引的前面，会使系统最大可能地使用此索引，发挥索引的作用。

注：具体怎么哪些列可以创建索引，哪些列不建议创建索引，需要具体情况具体分析，需要开发中慢慢积累经验。

四，创建索引

索引分为单索引和组合索引。但索引是给一个字段创建的索引，组合索引是给多个字段创建的索引。

比如给person表中的age字段创建索引的sql是：

ALTER TABLE `xia`.`person` ADD INDEX(`age`) USING BTREE COMMENT '给age创建索引';

navicate也支持创建索引的图形化操作，如下：

##执行计划

执行计划就是分析执行某个sql语句的详细细节。

查看某个sql语句的执行计划很容易，只有在sql语句前添加EXPLAIN关键词即可。如下：

EXPLAIN select  * from person WHERE age = 16 and id > 0;

执行结果如下：

说明：

type：表示执行sql使用了哪种类型。类型的种类从好到差依次是：const、eq_reg、ref、range、indexhe和ALL。
possible_keys：可能使用到的索引。此时有两个：PRIMARY，index_age。其中PRIMARY是主键，index_age是自己创建的索引。
key：实际用到的索引。
key_len：使用索引的长度，该值越小越好。
ref：
rows：数据遍历的行数，数值越小越好。

总结：要学会且有意识的多使用执行计划，查看每一个sql语句的执行计划，尽量优化sql，提高项目的执行效率。

##sql优化具体分析

sql优化主要针对查询sql进行优化，因为只有查询语句才会出现扫描全表的情况。随着数据量的增大，扫描全表会非常耗时，优化sql的目的是尽量扫描少的数据量。

具体细节如下：

1，尽量给字段设置默认值，避免字段值为null情况。

如果字段是int类型的age，首先给age创建索引时null不会参与创建索引。where age=0比where age is null效率高。

2，在where语句中少使用or，使用or将不使用索引。

如select name where age = 16 or age = 20

可以写成：

select name where age = 16 union all select name where age = 20

3，在where语句中避免使用in，

如select * from score where student_id in (select id from student where name='guoxiang')

可以写成：

select * from score sc where exists (select 1 from student st where st.name='guoxiang' and st.id = sc.student_id)

本文内容由网友自发贡献，版权归原作者所有，本站不承担相应法律责任。如您发现有涉嫌抄袭侵权的内容，请联系:hwhale#tublm.com(使用前将#替换为@)

mysql

sql