由于mysql的广泛使用,今天正好看了MySQL技术内幕的查询优化,所以做一些笔记。
索引是什么和索引的好处:
索引的代价:
因为增加索引会增加额外的时间和空间成本,所以不要建立太多的索引,只建立必要的索引。比如经常用于查询数据时使用的限定条件,比方放到where , order 和groupby 等等关键词后面的属性。且针对两种引擎,MyISAM大量建立索引,有时会导致索引文件比数据文件更快达到极限。对于InnoDB中的表,索引和数据表文件是放在同一空间的,如果索引建立太多会导致数据文件的存储空间变少。
挑选索引的原则:
1.为用于搜索,排序或分组的列创建索引,对用于输出显示的列不创建索引
简单说就是 select * from A where..., select * from A order by..., select * from A group by...,对select后面*的部分,及挑出来显示的数据列不建立索引,对用于筛选数据的,如where, order by, group by 后面的数据列建立索引。
2.认真考虑数据列的基数
比如(1,3,7,4,7,3)数列,这个数列的基数就是4,因为有1,3,4,7四中不同类型的数,如果添加索引的数据列基数过大,比如加了索引后还是挑选出了非常多数据,那么当数据大到一定程度时,索引可能无法使用,转而使用全表扫描。
3.索引短小值
索引尽量选用较小的数据类型,短小值可以让比较操作更快,短小值可以让索引更短,短小的键值,可以在缓存的索引块里放更多索引。
4.索引字符串值的前缀
对字符串进行索引,尽量指定前缀长度。找不同的部分就可以了,不需要进行全串扫描,增加工作量。
5.不要建立过多的索引
前面已经提过了,不要建立非必要索引,浪费时间空间。
6.让参加比较的索引类型保持匹配
选择索引的类型,B树索引,还是哈希索引等,根据实际情况来设计索引方式。
7.利用慢查询日志来找性能低劣的查询
慢查询日志是一个文本文件,可以用任何文件显示程序打开,根据日志来发现低劣查询项。
查询优化的常用方法:
1.分析表
2.使用EXPLAN验证优化程序的操作
3.在必要时给予优化程序提示或改写它
4.比较拥有相同数据类型的列
5.让索引列在表达式中单独出现
6.不要在LIKE模式的开始位置使用通配符
7.利用优化程序的长处
8.测试查询的各种代替形式,并多次运行他们
9.避免过多使用自动类型转换
10.使用EXPLAN多分析查询语句
有利于高效查询的数据类型:
1.多用数字运算,少用字符串运算
2.当较小类型够用时,不要用较大类型
3.把数据声明成NOT NULL
4.考虑使用ENUM列
5.使用PROCEDURE ANALYSE()
6.整理表碎片
7.使用合成索引