MySQL 性能调优、查询索引优化

  • 2016-06-07
  • 1461
  • 0

mysql

前不久听了公司一个前辈的关于MySQL的分享课,大部分知识点虽已知道,但还是有某些冷门的知识点未曾涉及。现将他的分享点整理一下,以供后续查看、巩固。

1、有符号的整型才需要11位的长度,如果你的数据中没有负数,请一定都使用无符号类型(Unsigned)。

图片1

 

图片2

1 bytes = 8 bit ,一个字节最多可以代表的数据长度是2的8次方,在计算机中也就是 -128到127
tinyint 一个字节、 smallint  两个字节、 MEDIUMINT 三个字节、 int 四个字节、 BIGINT 八个字节。

TINYINT[(M)] [UNSIGNED]   M 默认为3(有符号的,M 默认为 4)
很小的整数。带符号的范围是 -128到127,无符号的范围是 0到255

SMALLINT[(M)] [UNSIGNED]   M默认为 5(有符号的,M默认是 6)
小的整数。带符号的范围是 -32768到32767,无符号的范围是 0到65535

MEDIUMINT[(M)] [UNSIGNED]   M默认为 8(有符号的,M默认是 9)
中等大小的整数。带符号的范围是 -8388608到8388607,无符号的范围是 0到16777215

INT[(M)] [UNSIGNED]   M默认为 10(有符号的,M默认是 11)
普通大小的整数。带符号的范围是 -2147483648到2147483647,无符号的范围是 0到4294967295

BIGINT[(M)] [UNSIGNED]  M默认为 20 (有符号的,M默认也是 20)
大整数。带符号的范围是 -9223372036854775808到9223372036854775807,无符号的范围是 0到18446744073709551615

 

注意:这里的M代表的并不是存储在数据库中的具体的长度,许多人总是会误以为int(3)只能存储3个长度的数字,int(11)就会存储11个长度的数字,这是大错特错的。

其实当我们在选择使用int的类型的时候,不论是int(3)还是int(11),它在数据库里面存储的都是4个字节的长度,

在使用 int(3) zerofill 的时候如果你输入的是10,会默认给你存储位010
也就是说这个3代表的是默认的一个长度,当你不足3位时,会帮你补全,当你超过3位时,就没有任何的影响(数据的存入,取出、展示都没有影响)。

在我们使用的时候是没有任何区别的。int(10)也可以代表2147483647这个值int(11)也可以代表。
要查看出不同效果记得在创建类型的时候加 zerofill 这个值,表示用0填充,否则看不出效果的。
我们通常在创建数据库的时候都不会加入这个选项,所以可以说他们之间是没有区别的。

 

2、MySQL 聚合函数统计中的所谓“陷阱”

MySQL的聚合函数的结果集只根据选定行中非NULL的值进行计算,NULL值会被忽略,比如有如下数据,我执行图中显示的SQL语句,从结果中就可以发现, count(*) 可以统计NULL行,而 count(字段) 则不能
我这里说的聚合函数,是指在放在 HAVING子句 后的那些表达式,比如 SUM() ,AVG() , COUNT() ,MAX() 等

图片3

图片4

提示:没有where子句的count(*)是经过内部优化的,能够快速返回表中所有的记录条数

 

3、避免使用 NULL 列

在MySQL中,含有空值的列很难进行查询优化(索引采用B-Tree实现,而B-Tree是为了处理,等于,范围查找,排序等操作,另外相等操作还有BETWEEN ,and , IN 等形式,而 null 没法采用比较操作)
因为它们使得索引、索引的统计信息以及比较运算更加复杂。
通常应该用 0、一个特殊的值或者一个空字符串代替空值。
在MySQL字符串连接函数 CONCAT() 中,如有任何一个参数为NULL ,则返回值为 NULL
NULL 值与其它任何值的比较(包括它自己)都永远不会返回“真”,包含NULL的表达式总是会导出NULL值 ,和数学中的 ∞ ,Javascript中的 NaN 一样,MySQL中的 NULL 也是一个不定值,不能用于比较
常识普及:数学中 ∞ 不能比较 ∞   , Javascript 中 NaN 不能比较 NaN  , MySQL 中 NULL 不能比较 NULL

图片5

 

4、mysql索引基数

索引基数是数据列所包含的不同值的数量。

很关键的一个参数,索引的选择性。是指索引列中不同值的数目与表中记录数的比。如果一个表中有2000条记录,表索引列有1980个不同的值,那么这个索引的选择性就是1980/2000=0.99。一个索引的选择性越接近于1,这个索引的效率就越高。
索引基数( Cardinality )可以通过“show index from 表名”查看。

图片6

高索引选择性的好处就是mysql查找匹配的时候可以过滤更多的行,
唯一索引的选择性最佳,值为1 。

基数是数据列所包含的不同值的数量。例如,某个数据列包含值1、3、7、4、7、3,那么它的基数就是4。索引基数相对于数据表行数较高(也就是说,列中包含很多不同的值,重复的值很少)的时候,它的工作效果最好。如果某数据列含有很多不同的年龄,索引会很快地分辨数据行。

如果某个数据列用于记录性别(只有”M”和”F”两种值),那么索引的用处就不大。如果值出现的几率几乎相等,那么无论搜索哪个值都可能得到一半的数据行。在这些情况下,最好根本不要使用索引,因为查询优化器发现某个值出现在表的数据行中的百分比很高的时候,它一般会忽略索引,进行全表扫描。惯用的百分比界线是”30%”。

 

5、最左前缀原则

实例:现在我们想查出满足以下条件的用户id:
SELECT uid FROM people WHERE lname=’Liu’  AND  fname= ‘ Che’ AND  age=26
因为我们不想扫描整表,故考虑用索引。

1)单列索引:
ALTER TABLE people ADD INDEX lname (lname);
将lname列建索引,这样就把范围限制在lname=’Liu’的结果集1上,之后扫描结果集1,产生满足fname= ‘ Che’的结果集2,再扫描结果集2,找到 age=26的结果集3,即最终结果。

由于建立了lname列的索引,与执行表的完全扫描相比,效率提高了很多,但我们要求扫描的记录数量仍旧远远超过了实际所需要的。虽然我们可以删除lname列上的索引,再创建fname或者age 列的索引,但是,不论在哪个列上创建索引搜索效率仍旧相似。

2)多列索引:
ALTER TABLE people ADD INDEX lname_fname_age (lame, fname, age);
为了提高搜索效率,我们需要考虑运用多列索引,由于索引文件以B-Tree格式保存,所以我们不用扫描任何记录,即可得到最终结果。

注:在mysql中执行查询时,只能使用一个索引,如果我们在lname, fname, age上分别建索引,执行查询时,只能使用一个索引,mysql会选择一个最严格(索引基数最高)的索引。

3)最左前缀:顾名思义,就是最左优先,上例中我们创建了lname_fname_age多列索引,相当于创建了
组合一:(lname) 单列索引,
组合二:(lname, fname) 组合索引
组合三:(lname, fname, age) 组合索引。

注:在创建多列索引时,要根据业务需求,where子句中使用最频繁的一列放在最左边。

 

6、不要过度索引

每个额外的索引都要占用额外的磁盘空间,并降低写操作的性能,在修改表的内容时,索引必须进行更新,有时可能需要重构,因此,索引越多,所花的时间越长。如果有一个索引很少利用或从不使用,那么会不必要地减缓表的修改速度。此外,MySQL在生成一个执行计划时,要考虑各个索引,这也要费时间。创建多余的索引给查询优化带来了更多的工作。索引太多,也可能会使MySQL选择不到所要使用的最好索引。只保持所需的索引有利于查询优化。如果想给已索引的表增加索引,应该考虑所要增加的索引是否是现有联合索引的最左索引  。如果是,则就不要费力去增加这个索引了,因为已经有了。

 

7、创建适度优化的索引

注意:上面数据表中的字段长度为80,而创建的索引的字段长度为20,这样做的目的是为了提高查询效率,优化查询速度。

按照实际生产环境中,邮箱和用户名等设置前20位就能大部分区分某条记录了。

 

8、联表查询关键

1)MySQL 表关联的算法是 Nest Loop Join,以驱动表的结果集作为循环的基础数据,然后将结果集中的数据作为过滤条件一条条地到下一个表中查询数据,最后合并结果;此时还有第三个表,则将前两个表的 Join 结果集作为循环基础数据,再一次通过循环查询条件到第三个表中查询数据,如此反复。

2)小结果集驱动大结果集。通常情况下,驱动表的选择上以索引基数高的,where中的限制条件较多,返回的结果集较少的表适合做驱动表。始终要记住的就是返回的结果集(某个表被过滤后的数据,或者某些表join 产生的数据)较小的表适合做驱动表,也就是说,哪个返回数据少 ,哪个就做驱动表,即连接操作应从返回较少行上驱动。

3)EXPLAIN 结果中,第一行出现的表就是驱动表,对驱动表可以直接排序,对非驱动表的字段排序需要对循环查询的合并结果(临时表)进行排序。

4)主表驱动外表,那么 p表(products) 很明显就是 referenced table  (被参照表 [主表] )
而 pd表(products_description) 就肯定是 referencing table  (参照表 [外表])

那么可以想象, pd表 必然是受到 p表 的约束(外键约束)
因此正确SQL语句中,实际上应该是  p表 作为驱动表
这里,是 referenced table [主表] 驱动 referencing table [外表]

5)关联字段一定要类型一致(数据类型,长度,符号,空值支持一致),
比如user表经常和order表进行关联查询 USING(uid) ,
那么这两张表里面的 uid字段最好都是  INT(10) UNSIGNED NOT NULL

 

9、利用索引优化ORDER BY排序语句 

MySQL索引通常是被用于提高WHERE条件的数据行匹配或者执行联结操作时匹配其它表的数据行的搜索速度(这里作者的意思就是,联表查询是的用的连接字段要做成索引)MySQL也能利用索引来快速地执行ORDER BY和GROUP BY语句的排序和分组操作。
通过索引优化来实现MySQL的ORDER BY语句优化:

1)ORDER BY的索引优化。如果一个SQL语句形如:
SELECT  [column1], [column2] , …. FROM  [TABLE]  ORDER  BY  [sort]  ;
在 [sort] 这个栏位上建立索引就可以实现利用索引进行 order by 优化。

2)WHERE + ORDER BY 的索引优化,形如:
SELECT [column1], [column2], ….  FROM  [TABLE]  WHERE  [columnX] = [value]  ORDER  BY  [sort] ;
建立一个联合索引(columnX, sort)来实现order by 优化(注意顺序)
SELECT  [column1], [column2], …. FROM  [TABLE]  WHERE  [columnX]  IN  ( [value1], [value2], …)  ORDER  BY  [sort]  ;
注意:如果 columnX  对应多个值,则上面的语句就无法利用索引来实现order by的优化(但还是能走索引,只是排序不被优化而已)
3)WHERE + 多个字段ORDER BY
SELECT  *  FROM  [table]  WHERE  uid=1  ORDER  x,y  LIMIT  0,10;
建立索引 (uid, x, y) 实现order by的优化,比建立 (x, y, uid) 索引效果要好得多。

图片7

type 表连接类型。以下列出了各种不同类型的表连接,依次是从最好的到最差的:

const    表中最多只有一行匹配的记录,它在查询一开始的时候就会被读取出来。由于只有一行记录,在余下的优化程序里该行记录的字段值可以被当作是一个 恒定值。const表查询起来非常快,因为只要读取一次!const 用于在和 primary key 或unique 索引中有固定值比较的情形。

eq_ref   从该表中会有一行记录被读取出来以和从前一个表中读取出来的记录做联合。与const类型不同的是,这是最好的连接类型。它用在索引所有部 分都用于做连接并且这个索引是一个primary key 或 unique 类型。eq_ref可以用于在进行”=”做比较时检索字段。比较的值可以是固定值或者是表达式,表达示中可以使用表里的字段,它们在读表之前已准备好

ref   该表中所有符合检索值的记录都会被取出来和从上一个表中取出来的记录作联合。ref用于连接程序使用键的最左前缀或者是该键不是 primary key 或 unique索引(换句话说,就是连接程序无法根据键值只取得一条记录)的情况。当根据键值只查询到少数几条匹配的记录时,这就是一个不错的连接类型。 ref还可以用于检索字段使用 =操作符来比较的时候。

ref_or_null    这种连接类型类似 ref,不同的是mysql会在检索的时候额外的搜索包含null 值的记录。这种连接类型的优化是从mysql4.1.1开始的,它经常用于子查询 (因此把字段设置成 非空 ,能加快查询速度)

range    只有在给定范围的记录才会被取出来,利用索引来取得一条记录。key字段表示使用了哪个索引。key_len字段包括了使用的键的最长部分。这种类型时 ref 字段值是 null。range用于将某个字段和一个定植用以下任何操作符比较时 =,  <>,  >, >=, < , <=, is null, <=>, between, 或 in

Index     连接类型跟 all 一样,不同的是它只扫描索引树。它通常会比 all快点,因为索引文件通常比数据文件小。mysql在查询的字段知识单独的索引的一部分的情况下使用这种连接类型。

all   将对该表做全部扫描以和从前一个表中取得的记录作联合,这是最糟糕的情况。正常地,可以通过增加索引使得能从表中更快的取得记录以避免 all

extra 字段    查询中mysql的附加信息。以下是这个字段的几个不同值的解释: range checked for each record (index map: #)           mysql没找到合适的可用的索引。取代的办法是,对于前一个表的每一个行连接,它会做一个检验以决定该使用哪个索引(如果有的话),并且使用这个索引来从表里取得记录。这个过程不会很快,但总比没有任何索引时做表连接来得快。 using  filesort     mysql 需要额外的做一遍从而以排好的顺序取得记录。排序程序根据连接的类型遍历所有的记录,并且将所有符合 where条件的记录的要排序的键和指向记录的指针存储起来。这些键已经排完序了,对应的记录也会按照排好的顺序取出来。
using index        信息直接从索引树(这里指的应该是 Btree )中的信息取得,而不再去扫描实际的记录。这种策略用于查询时的字段是一个独立索引的一部分。 using temporary     mysql需要创建临时表存储结果以完成查询。这种情况通常发生在查询时包含了group  by 和 order  by 子句,它以不同的方式列出了各个字段。
using where       where子句将用来限制哪些记录匹配了下一个表或者发送给客户端。除非你想取得或者检查表中的所有记录,否则当查询的extra 字段值不是 using where 并且表连接类型 (type) 是 all 或 index 时可能表示有问题。 想要让查询尽可能的快,应该避免 extra 字段的值为 using  filesort 和 using  temporary 的情况
(个人见解:这两个通常会导致慢查询的产生,它们比较容易出现在联表查询的排序中)

possible_keys     是指 mysql在搜索表记录时可能使用哪个索引。注意,这个字段完全独立于explain 显示的表顺序。这就意味着 possible_keys里面所包含的索引可能在实际的使用中没用到。如果这个字段的值是null,就表示没有索引被用到。这种情况下,就可以检查 where子句中哪些字段那些字段适合增加索引以提高查询的性能。就这样,创建一下索引,然后再用explain 检查一下。想看表都有什么索引,可以通过   SHOW  INDEX  FROM  tbl_name    来看。   key     显示了mysql实际上要用的索引。当没有任何索引被用到的时候,这个字段的值就是null。想要让mysql强行使用或者忽略在 possible_keys字段中的索引列表,可以在查询语句中使用关键字force index, use index,或 ignore index。如果是 myisam 和 bdb 类型表,可以使用 analyze table 来帮助分析使用使用哪个索引更好。

key_len        显示了mysql使用索引的长度。当 key 字段的值为 null时,索引的长度就是 null。注意,key_len的值可以告诉你在联合索引中mysql会真正使用了哪些索引。 ref     显示了哪些字段或者常量被用来和 key配合从表中查询记录出来。


上一篇:  下一篇:

评论

还没有任何评论,你来说两句吧

Copyright © 2014-2016 lxlxw All Right Reserved