首页 / MYSQL / 数据库 mysql 优化器原理
数据库 mysql 优化器原理
内容导读
互联网集市收集整理的这篇技术教程文章主要介绍了数据库 mysql 优化器原理，小编现在分享给大家，供广大互联网技能从业者学习和参考。文章包含8665字，纯文字阅读大概需要13分钟。
内容图文
这是test表，其中id是主键，多列索引(username,password,last_login),里面有10000多条数据.
(1) 最左前缀原则
定义：最左前缀原则指的的是在sql where 字句中一些条件或表达式中出现的列的顺序要保持和多索引的一致或以多列索引顺序出现，只要出现非顺序出现、断层都无法利用到多列索引。
举例说明：上面给出一个多列索引(username,password,last_login)，当三列在where中出现的顺序如(username,password,last_login)、(username,password)、(username)才能用到索引，如下面几个顺序(password,last_login)、(passwrod)、(last_login)—这三者不从username开始，(username,last_login)—断层，少了password，都无法利用到索引。
因为B+tree多列索引保存的顺序是按照索引创建的顺序，检索索引时按照此顺序检索
测试：以下测试不精确，这里只是说明如何才能正确按照最左前缀原则使用索引。还有的是以下的测试用的时间0.00sec看不出什么时间区别，因为数据量只有20003条，加上没有在实体机上运行，很多未可预知的影响因素都没考虑进去。当在大数据量，高并发的时候，最左前缀原则对与提高性能方面是不可否认的。
Ps：最左前缀原则中where字句有or出现还是会遍历全表
(1.1)能正确的利用索引
l Where子句表达式顺序是(username)
- mysql> explain select * from one where username=‘abgvwfnt’;
- +—-+————-+——-+——+—————+———-+———+——-+——+————-+
- | id | select_type | table | type | possible_keys | key | key_len | ref |rows | Extra |
- +—-+————-+——-+——+—————+———-+———+——-+——+————-+
- | 1 | SIMPLE | one | ref | username | username | 24 | const |5 | Using where |
- +—-+————-+——-+——+—————+———-+———+——-+——+————-+
- 1 row in set (0.00 sec)
l Where子句表达式顺序是(username,password)
- mysql> explain select * from one where username=‘abgvwfnt’ and password=’123456′;
- +—-+————-+——-+——+—————+———-+———+————-+——+————-+
- | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
- +—-+————-+——-+——+—————+———-+———+————-+——+————-+
- | 1 | SIMPLE | one | ref | username | username | 43 | const,const | 1 | Using where |
- +—-+————-+——-+——+—————+———-+———+————-+——+————-+
- 1 row in set (0.00 sec)
l Where子句表达式顺序是(username,password, last_login)
- mysql> explain select * from one where username=‘abgvwfnt’ and password=’123456′andlast_login=’1338251170′;
- +—-+————-+——-+——+—————+———-+———+——————-+——+————-+
- | id | select_type | table | type | possible_keys | key | key_len | ref| rows | Extra |
- +—-+————-+——-+——+—————+———-+———+——————-+——+————-+
- | 1 | SIMPLE | one | ref | username | username | 83 | const,const,const | 1 | Using where|
- +—-+————-+——-+——+—————+———-+———+——————-+——+————-+
- 1 row in set (0.00 sec)
上面可以看出type=ref 是多列索引，key_len分别是24、43、83，这说明用到的索引分别是(username), (username,password), (username,password, last_login );row分别是5、1、1检索的数据行都很少，因为这三个查询都按照索引前缀原则，可以利用到索引。
(2) Order by 优化
(3) 隔离列：如id+1=2 => id = 1
(4) OR、IN、UNION ALL，可以尝试用UNION ALL
(4.1)or会遍历表就算有索引
(4.2)对于in，这个是有争议的，网上很多优化方案中都提到尽量少用in，这不全面，其实在in里面如果是常量的话，可一大胆的用in
(4.3)UNION All 直接返回并集，可以避免去重的开销。之所说“尝试”用UNION All 替代 OR来优化sql语句，因为这不是一直能优化的了，这里只是作为一个方法去尝试
(5) 索引选择性
索引选择性是不重复的索引值也叫基数（cardinality）表中数据行数的比值，索引选择性=基数/数据行，基数可以通过“show index from 表名”查看。
高索引选择性的好处就是mysql查找匹配的时候可以过滤更多的行，唯一索引的选择性最佳，值为1。
那么对于非唯一索引或者说要被创建索引的列的数据内容很长，那就要选择索引前缀。这里就简单说明一下：
- mysql> select count(distinct(username))/count(*) from one;
- +————————————+
- | count(distinct(username))/count(*) |
- +————————————+
- | 0.2047 |
- +————————————+
- 1 row in set (0.09 sec)
count(distinct(username))/count(*)就是索引选择性的值，这里0.2太小了。
(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、 解析器解析。解析器先词法分析，语法分析，检查错误比如引号有没闭合等，然后生成解析树。
3、 预处理。预处理解决解析器无法决解的语义，如检查表和列是否存在，别名是否有错，生成新的解析树。
4、 优化器做大量的优化操作。
5、 生成执行计划。
6、 查询执行引擎，负责调度引擎获取相应数据
7、 返回结果。
转载地址：http://ourmysql.com/archives/1171
http://blog.chinaunix.net/uid-25311424-id-3957863.html
数据库 mysql 优化器原理
标签：
本文系统来源：http://www.cnblogs.com/yubinbin/p/4748844.html
内容总结
以上是互联网集市为您收集整理的数据库 mysql 优化器原理全部内容，希望文章能够帮你解决数据库 mysql 优化器原理所遇到的程序开发问题。 如果觉得互联网集市技术教程内容还不错，欢迎将互联网集市网站推荐给程序员好友。
内容备注
版权声明：本文内容由互联网用户自发贡献，该文观点与技术仅代表作者本人。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容， 请发送邮件至 gblab@vip.qq.com 举报，一经查实，本站将立刻删除。
内容手机端
扫描二维码推送至手机访问。