由浅入深探究 MySQL索引结构原理、性能分析与优化(三)

(2.3)where + orerby 类型,where满足最左前缀原则,且orderby的列和where子句用到的索引的列的子集。即是(a,b,c)索引,where满足最左前缀原则且order by中列a、b、c的任意组合

mysql> explain select* fromone whereusername='abgvwfnt'andpassword='123456
'andlast_login='1338251001'orderbypassworddesc,last_login desc;
 
+----+-------------+-------+------+---------------+----------+---------+-------------------+------+-------------+
| id | select_type | table| type | possible_keys | key| key_len | ref
 
| rows| Extra |
+----+-------------+-------+------+---------------+----------+---------+-------------------+------+-------------+
| 1 | SIMPLE | one | ref | username | username | 83 | const,c
onst,const | 1 | Using where|
+----+-------------+-------+------+---------------+----------+---------+-------------------+------+-------------+
1 row inset(0.00 sec)
 
mysql> explain select* fromone whereusername='abgvwfnt'andpassword='123456
'andlast_login='1338251001'orderbypassworddesc,leveldesc;
+----+-------------+-------+------+---------------+----------+---------+-------------------+------+----------------------------+
| id | select_type | table| type | possible_keys | key| key_len | ref| rows| Extra |
+----+-------------+-------+------+---------------+----------+---------+-------------------+------+-----------------------------+
| 1 | SIMPLE | one | ref | username | username | 83 | const,c
onst,const | 1 | Using where; Using filesort |
+----+-------------+-------+------+---------------+----------+---------+-------------------+------+-----------------------------+
 
1 row inset(0.00 sec)


上面两条语句明显的区别是多了一个非索引列level的排序,在extra这列对了Using filesort

笔者测试结果:where满足最左前缀且order by中的列是该多列索引的子集时(也就是说orerby中没最左前缀原则限制),不管是否有asc ,desc混合出现,都能用索引来满足order by。

笔者测试过,因为篇幅比较大,这里就不一一列出。

Ps:很优化博文都说order by中的列要where中出现的列(是索引)的顺序一致,笔者认为不够严谨。

(2.3) where + orerby+limit

这个其实也差不多,只要where最左前缀,orderby也正确,limit在此影响不大

(2.4)如何考虑order by来建索引

这个回归到创建索引的问题来,在比较常用的oder by的列和where中常用的列建立多列索引,这样优化起来的广度和扩张性都比较好,当然如果要考虑UNION、JOIN、COUNT、IN等进来就复杂很多了

(3) 隔离列

隔离列是只查询语句中把索引列隔离出来,也就是说不能在语句中把列包含进表达式中,如id+1=2、inet_aton(’210.38.196.138′)—ip转换成整数、convert(123,char(3))—数字转换成字符串、date函数等mysql内置的大多函数。

非隔离列影响性能很大甚至是致命的,这也就是赶集网石展的《三十六军规》中的一条,虽然他没说明是隔离列。

以下就测试一下:

首先建立一个索引(last_login ),这里就不给出建立的代码了,且把last_login改成整型(这里只是为了方便测试,并不是影响条件)

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

mysql> explain select* fromone wherelast_login = 8388605;

+----+-------------+-------+------+---------------+------------+---------+-------+-------+-------------+

| id | select_type | table| type | possible_keys | key| key_len | ref | rows| Extra |

+----+-------------+-------+------+---------------+------------+---------+-------+-------+-------------+

| 1 | SIMPLE | one | ref | last_login | last_login | 3 | const

| 1 | Using where|

+----+-------------+-------+------+---------------+------------+---------+-------+-------+-------------+

1 row inset, 1 warning (0.00 sec)

容易看出建的索引已起效

mysql> explain select* fromone wherelast_login +1= 8388606 ;

+----+-------------+-------+------+---------------+------+---------+------+-------+-------------+

| id | select_type | table| type | possible_keys | key| key_len | ref | rows

| Extra |

+----+-------------+-------+------+---------------+------+---------+------+-------+-------------+

| 1 | SIMPLE | one | ALL| NULL| NULL| NULL| NULL| 2049

7 | Using where|

+----+-------------+-------+------+---------------+------+---------+------+-------+-------------+

1 row inset(0.00 sec)

last_login +1=8388608非隔离列的出现导致查找的列20197,说明是遍历整张表且索引不能使用。

这是因为这条语句要找出所有last_login的数据,然后+1再和20197比较,优化器在这方面比较差,性能很差。

所以要尽可能的把列隔离出来,如last_login +1=8388606改成login_login=8388607,或者把计算、转换等操作先用php函数处理过再传递给mysql服务器

(4) OR、IN、UNION ALL,可以尝试用UNION ALL

(4.1)or会遍历表就算有索引

1

2

3

4

5

6

7

mysql> explain select* fromone whereusername = 'abgvwfnt'orpassword='123456';

+----+-------------+-------+------+---------------+------+---------+------+-------+-------------+

| id | select_type | table| type | possible_keys | key| key_len | ref | rows| Extra |

+----+-------------+-------+------+---------------+------+---------+------+-------+-------------+

| 1 | SIMPLE | one | ALL| username | NULL| NULL| NULL| 20259 | Using where|

+----+-------------+-------+------+---------------+------+---------+------+-------+-------------+

1 row inset(0.00 sec)

(4.2)对于in,这个是有争议的,网上很多优化方案中都提到尽量少用in,这不全面,其实在in里面如果是常量的话,可一大胆的用in,这个也是赶集网石展、阿里hellodab的观点(笔者从微博中获知)。应用hellodab一句话“MySQL用IN效率不好,通常是指in中嵌套一个子查询,因为MySQL的查询重写可能会产生一个不好的执行计划,而如果in里面是常量的话,我认为性能没有任何问题,可以放心使用”———当然对于这个比较的话,没有实战数据的话很难辩解,就算有,影响性能的因素也很多,也许会每个dba都有不同的测试结果.这也签名最左前缀中“补洞”一个方法

(4.3)UNION All 直接返回并集,可以避免去重的开销。之所说“尝试”用UNION All 替代 OR来优化sql语句,因为这不是一直能优化的了,这里只是作为一个方法去尝试。

(5) 索引选择性

索引选择性是不重复的索引值也叫基数(cardinality)表中数据行数的比值,索引选择性=基数/数据行,基数可以通过“show index from 表名”查看。

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

那么对于非唯一索引或者说要被创建索引的列的数据内容很长,那就要选择索引前缀。这里就简单说明一下:

1

2

3

4

5

6

7

mysql> selectcount(distinct(username))/count(*) fromone;

+------------------------------------+

| count(distinct(username))/count(*) |

+------------------------------------+

| 0.2047 |

+------------------------------------+

1 row inset(0.09 sec)

count(distinct(username))/count(*)就是索引选择性的值,这里0.2太小了。

假如username列数据很长,则可以通过

select count(distinct(concat(first_name, left(last_name, N))/count(*) from one;测试出接近1的索引选择性,其中N是索引的长度,穷举法去找出N的值,然后再建索引。

(6) 重复或多余索引

很多phper开始都以为建索引相对多点性能就好点,压根没考虑到有些索引是重复的,比如建一个(username),(username,password), (username,password,last_login),很明显第一个索引是重复的,因为后两者都能满足其功能。

要有个意识就是,在满足功能需求的情况下建最少索引。对于INNODB引擎的索引来说,每次修改数据都要把主键索引,辅助索引中相应索引值修改,这可能会出现大量数据迁移,分页,以及碎片的出现。

3、系统配置与维护优化

(1) 重要的一些变量

l key_buffer_size索引块缓存区大小, 针对MyISAM存储引擎,该值越大,性能越好.但是超过操作系统能承受的最大值,反而会使mysql变得不稳定. —-这是很重要的参数

l sort_buffer_size 这是索引在排序缓冲区大小,若排序数据大小超过该值,则创建临时文件,注意和myisam_sort_buffer_size的区别—-这是很重要的参数

l read_rnd_buffer_size当排序后按排序后的顺序读取行时,则通过该缓冲区读取行,避免搜索硬盘。将该变量设置为较大的值可以大大改进ORDER BY的性能。但是,这是为每个客户端分配的缓冲区,因此你不应将全局变量设置为较大的值。相反,只为需要运行大查询的客户端更改会话变量

l join_buffer_size用于表间关联(join)的缓存大小

l tmp_table_size缓存表的大小

l table_cache允许 MySQL 打开的表的最大个数,并且这些都cache在内存中

l delay_key_write针对MyISAM存储引擎,延迟更新索引.意思是说,update记录时,先将数据up到磁盘,但不up索引,将索引存在内存里,当表关闭时,将内存索引,写到磁盘

更多参数查看http://www.phpben.com/?post=70

(2) optimize、Analyze、check、repair维护操作

l optimize 数据在插入,更新,删除的时候难免一些数据迁移,分页,之后就出现一些碎片,久而久之碎片积累起来影响性能,这就需要DBA定期的优化数据库减少碎片,这就通过optimize命令。

如对MyisAM表操作:optimize table 表名

对于InnoDB表是不支持optimize操作,否则提示“Table does not support optimize, doing recreate + analyze instead”,当然也可以通过命令:alter table one type=innodb; 来替代。

l Analyze 用来分析和存储表的关键字的分布,使得系统获得准确的统计信息,影响 SQL 的执行计划的生成。对于数据基本没有发生变化的表,是不需要经常进行表分析的。但是如果表的数据量变化很明显,用户感觉实际的执行计划和预期的执行计划不 同的时候,执行一次表分析可能有助于产生预期的执行计划。

Analyze table 表名

l Check检查表或者视图是否存在错误,对 MyISAM 和 InnoDB 存储引擎的表有作用。对于 MyISAM 存储引擎的表进行表检查,也会同时更新关键字统计数据

l Repair optimize需要有足够的硬盘空间,否则可能会破坏表,导致不能操作,那就要用上repair,注意INNODB不支持repair操作

以上的操作出现的都是如下这是check

+———-+——-+————–+————-+

| Table | Op | Msg_type| Msg_text |

+———-+——-+————–+————-+

| test.one | check | status | OK |

+———-+——-+————–+————-+

其中op是option 可以是repair check optimize,msg_type 表示信息类型,msg_text 表示信息类型,这里就说明表的状态正常。如在innodb表使用repair就出现note | The storage engine for the table doesn’t support repair

注意:以上操作最好在数据库访问量最低的时候操作,因为涉及到很多表锁定,扫描,数据迁移等操作,否则可能导致一些功能无法正常使用甚至数据库崩溃。

(3)表结构的更新与维护

l 改表结构。当要在数据量千万级的数据表中使用alter更改表结构的时候,这是一个棘手问题。一种方法是在低并发低访问量的时候用平常的alter更改表。另外一种就是建另一个与要修改的表,这个表除了要修改的结构属性外其他的和原表一模一样,这样就能得到一个相应的.frm文件,然后用flush with read lock 锁定读,然后覆盖用新建的.frm文件覆盖原表的.frm,最后unlock table 释放表。

l 建立新的索引。一般方法这里不说。

1、 创建没索引的a表,导入数据形成.MYD文件。

2、 创建包括索引b表,形成.FRM和.MYI文件

3、 锁定读写

4、 把b表的.FRM和.MYI文件改成a表名字

5、 解锁

6、 用repair创建索引。

这个方法对于大表也是很有效的。这也是为什么很多dba坚持说“先导数据库在建索引,这样效率更快”

l 定期检查mysql服务器

定期使用show status、show processlist等命令检查数据库。这里就不细说,这说起来也篇幅是比较大的,笔者对这个也不是很了解

第四部分:图说mysql查询执行流程

1、 查询缓存,判断sql语句是否完全匹配,再判断是否有权限,两个判断为假则到解析器解析语句,为真则提取数据结果返回给用户。

2、 解析器解析。解析器先词法分析,语法分析,检查错

相关文章

留言列表
  1. 妾身太美了   2017-03-23 00:15
    真不知道,还有这么好心的博主 支持
留言板
QQ登录