本篇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是:
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)
想要了解更多的java应用技术那就加入我们吧!