sql优化整理(一)
sql的编写语法是这样的：
SELECT DISTINCT
<select_list>
FROM
<left_table> <join_type>
JOIN <right_table> ON <join_condition>
WHERE
<where_condition>
GROUP BY
<group_by_list>
HAVING
<having_condition>
ORDER BY
<order_by_condition>
LIMIT <limit_condition>
MySQL读取的顺序是这样的：
FROM <left_table>
ON <join_condition>
<join_type> JOIN <right_table>
WHERE <where_condition>
GROUP BY <group_by_list>
HAVING <having_condition>
SELECT
DISTINCT <select_list>
ORDER BY <order_by_condition>
LIMIT <limit_condition>
MySQL官方对索引的定义：索引是帮助MySQL高效获取数据的数据结构；索引的目的在于提高查询效率，可以类似字典；排好序的快速查找的数据结构；索引会影响where后面的查询和order by后面的排序；一般来说索引本身也很大，不可能全部存储在内存中，因此索引往往会以索引文件的形式存储在磁盘上；频繁增删改的表不适合建索引;
索引优势：
提高数据检索的效率，降低数据库的IO成本；通过索引列对数据进行索引，降低数据排序的成本，降低CPU的消耗；
索引的劣势：
实际上索引也是一张表，该表保存了主键与索引字段，并指向实体表的记录，所以索引列也是要占用空间的；虽然索引大大提高了查询速度，但会降低更新表的速度，如对表进行INSERT，UPDATE和DELETE；因为更新表时，MySQL不仅要保存数据，还要保存索引文件每次更新添加的索引列字段，都会调整因为更新所带来的键值变化后的索引信息；
适合建立索引的情况：
1.主键自动建立唯一索引
2.频繁作为查询条件的字段应该建立索引
3.查询中与其他表关联的字段，外键关系建立索引
4.频繁更新的字段不适合建立索引，因为每次更新不单是更新了记录还会更新索引结构；
5.where条件里用不到的字段不创建索引
6.查询中排序的字段，排序字段若通过索引去访问将大大提高排序速度
7.查询中统计或分组的字段
不适合创建索引的情况：
1.表记录太少不适合建立索引；
2.经常增删改的表不适合建立索引；提高了查询速度，同时却会更新表的速度，如对表进行插入操作，因为更新表时，MySQL不仅要保存数据，还要保存表的索引结构；
3.数据重复且分布均匀的表的字段不适合建立索引，因此应该只为最经常查询的和最经常排序的数据建立索引；注意，如果某个数据列包含重复的内容，为它建立的索引就没有太大的效果；
MySQL常见的性能瓶颈：
1.CPU：CPU在饱和的时候一般会发生在数据装入内存或从磁盘上读取数据时候
2.IO：磁盘I/O瓶颈发生在装入数据远大于内存容量的时候
3.服务器硬件的性能瓶颈：top, free, iostat和vmstat来查看系统性能状态
EXPLAIN用途：
1.表的读取顺序
2.数据读取操作的操作类型
3.哪些索引可以使用
4.哪些索引被实际使用
5.表之间的引用
6.每张表有多少行被优化器查询
EXPLAIN字段解释：
id
select查询的序列号，包含一组数字，表示查询中执行select子句或操作表的顺序;
三种情况：
- id相同，执行顺序由上至下；
- id不同，如果是子查询，id的序号会递增，id值越大优先级会越高，越先执行
- id不同与不同，同时存在；id如果相同，可以认为是一组，从上往下执行；在所有组中，id值越大，优先级越高，越先执行；derived为衍生，是临时表，后面接数字对应相应的id;
select_type
查询的类型，主要用于区别普通查询，联合查询，子查询等的复杂查询（SIMPLE, PRIMARY, SUBQUERY, DERIVED, UNION, UNION RESULT）
- SIMPLE：简单的select查询，查询中不包含子查询或UNION
- PRIMARY：查询中若包含任何复杂的子查询，最外层查询被标记为PRIMARY
- SUBQUERY：在SELECT或WHERE列表中包含了子查询
- DERIVED：在FROM列表中包含的子查询被标记为DERIVED(衍生)，MySQL会递归执行这些子查询，把结果放到临时表中；
- UNION：若第二个SELECT出现在UNION之后，则标记为UNION；若UNION包含在FROM字句的子查询中，外层SELECT将被标记为DERIVED;
- UNION RESULT： UNION操作的结果，id值通常为NULL
table
显示这一行的数据关于哪张表的
type：
显示查询使用了哪种类型
从最好到最差依次是：
system>const>eq_ref>fulltext>ref_or_null>index_merge>unique_subquery>index_subquery>range>index>ALL
system>const>eq_ref>ref>range>index>ALL
system
表只有一行记录(等于系统表)，这是const类型的特列，平时不会出现，这个也可以忽略不计
MySQL5.7版本不会出现该字段值，只能出现const，但是在MySQL5.7以下的版本可以出现该情况；猜测MySQL5.7版本是不是进行了优化；
同样的语句在5.5版本会产生临时表；
const
表示通过索引一次就找到了，const用于比较primary key或unique索引，因为只匹配了一行数据，所以很快；如果将主键置于where列表中，MySQL就能将该查询转换为一个常量
eq_ref
通常出现在多表的join查询，被驱动表通过唯一性索引(UNIQUE或PRIMARY KEY)进行访问，此时被驱动表的访问方式就是eq_ref；
eq_ref是除const之外最好的访问类型；
ref
非唯一性索引扫描，返回匹配某个单独值的所有行。本质上也是一种索引访问，返回匹配某值（某条件）的多行值，属于查找和扫描的混合体
range
只检索给定范围的行，使用一个索引来选择行，key列显示使用了哪个索引；一般就是在where语句中出现了between，<，>，in等的查询；这种范围扫描索引比全表扫描要好，因为它只需要开始于索引的某一点，结束于某一点，不用扫描全部索引；
index
index与ALL区别为index类型只遍历索引树；这通常比ALL快，因为索引文件通常比数据文件小（all和index都是读全表，但index是从索引中读取的，而all从硬盘中读出来的）
Full Index Scan，查询全部索引中的数据（比不走索引要快）；
ALL
Full Table Scan，如果没有索引或者没有用到索引，type就是ALL，代表全表扫描；将遍历全表以找到匹配的行
possible_keys
显示可能应用在这张表的索引，一个或多个，查询涉及到的字段若存在索引，则该索引将被列出，但不一定被查询实际使用；
key
实际使用的索引；如果为NULL，则没有使用索引；查询中若使用了覆盖索引，则该索引仅出现key列表中；
key_len
表示索引中使用的字节数，可通过该列计算查询中使用的索引的长度；在不损失精度的情况下，长度越短越好；key_len显示的值为索引字段的最大可能长度，并非实际使用长度，即key_len是根据表定义计算而得，不是通过表内检索出来的；
key_len显示了条件检索子句需要的索引长度，但 ORDER BY、GROUP BY 子句用到的索引则不计入 key_len 统计值；
计算规则：
1.当索引字段为定长数据类型，比如：char，int，datetime，需要有是否为空的标记，这个标记需要占用1个字节；
2. 当索引字段为变长数据类型，比如：varchar，除了是否为空的标记外，还需要有长度信息，需要占用2个字节；
3.当字段定义为非空的时候，是否为空的标记将不占用字节；
4.同时还需要考虑表所使用字符集的差异，latin1编码一个字符1个字节，gbk编码一个字符2个字节，utf8编码一个字符3个字节；
例如：
varchr(10)变长字段且允许NULL : 10*(Character Set：utf8=3,gbk=2,latin1=1)+1(NULL标记位)+2(变长字段)
varchr(10)变长字段且不允许NULL : 10*(Character Set：utf8=3,gbk=2,latin1=1)+2(变长字段)
char(10)固定字段且允许NULL : 10*(Character Set：utf8=3,gbk=2,latin1=1)+1(NULL标记位)
char(10)固定字段且不允许NULL : 10*(Character Set：utf8=3,gbk=2,latin1=1)
key_len过长会导致索引失效；
- filtered
这个字段表示存储引擎返回的数据在server层过滤后，剩下多少满足查询条件的记录数量的比例，它是一个百分比；
如果比例很低，说明存储引擎层返回的数据需要进行大量的过滤，这个会消耗性能的；
ref
显示索引被哪一列使用，哪些列或者常量被用于查找索引上的值，其中const为常量；
rows
根据表统计信息及索引选用情况，大致估算出所需的记录所需要读取的行数
建表sql
CREATE TABLE `t1` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`col1` varchar(10) DEFAULT NULL,
`col2` varchar(10) DEFAULT NULL,
`col3` varchar(10) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `idx_t1_col1_col2_col3` (`col1`,`col2`,`col3`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8; ALTER t1 ADD INDEX `idx_t1_col1_col2_col3`(`col1`, `col2`, `col3`) USING BTREE;
ALTER t1 ADD INDEX `idx_t1_col1_col2`(`col1`, `col2`) USING BTREE;
INSERT INTO t1(col1, col2, col3) VALUES('a1', 'a2', 'a3');
INSERT INTO t1(col1, col2, col3) VALUES('b2', 'b3', 'b3');
INSERT INTO t1(col1, col2, col3) VALUES('c1', 'c2', 'c3');
INSERT INTO t1(col1, col2, col3) VALUES('d1', 'd2', 'd3');
INSERT INTO t1(col1, col2, col3) VALUES('e1', 'e2', 'e3');
INSERT INTO t1(col1, col2, col3) VALUES('f1', 'f2', 'f3');
INSERT INTO t1(col1, col2, col3) VALUES('i1', 'i2', 'i3');
INSERT INTO t1(col1, col2, col3) VALUES('j1', 'j2', 'j3');
INSERT INTO t1(col1, col2, col3) VALUES('k1', 'k2', 'k3');
extra
包含不适合在其他列显示中显示，但十分重要的额外信息
Using filesort
说明mysql会对数据使用一个外部的索引排序，而不是按照表内的索引排序进行读取，即没有使用索引；mysql中无法利用索引完成的排序操作称为文件排序，效率较低；
这里where条件是col1，order by排序字段为col3，中间的 col2没有用到；而索引的建立规则是col1，col2，col3，因此查询用到了索引，排序没有用到索引；
将order by 条件换成 col1, col3，结果如下：
将 order by 条件换成 col2, col3，结果如下：
Using temporary
使用了临时表保存中间结果，MySQL在对查询结果排序时使用临时表；
常用于 distinct非索引列，group by非索引列， 使用join的时候，group任意列；
Using temporary优化可创建复合索引；
MySQL 5.7 执行下面sql，报错解决方案，http://dev.mysql.com/doc/refman/5.7/en/sql-mode.html#sqlmode_only_full_group_by
上面的sql 的 group by条件只有col2，没有按索引那样col1, col2;
将 order by的条件改为 col1, col2
改成col1, col2与索引顺序一致；
Using index
表示相应的select操作中使用了覆盖索引，避免了访问表的数据行；查询语句可以通过索引里的信息得到结果（不需要进行回表操作）
如果同时出现using where，表明索引被用来执行索引的键值查找；
如果没有同时出现using where，表明索引用来读取数据而非执行查找操作；
不读取源文件，只从索引文件中获取数据 (不需要回表查询)，只要使用到的列全部都在索引中，就是索引覆盖Using index；
覆盖索引的理解方式：
1.select的数据列只用从索引列中就能够获得，不必读取数据行，MySQL可以利用索引返回select列表中的字段，而不必根据索引再次读取数据文件；查询列要被所建的索引覆盖；
2.索引是高效找到行的一个方法，但是一般数据库也能使用索引找到一个列的数据，因此它不必读取整个行；毕竟索引叶子节点存储了它们的索引数据，当能通过读取索引就可以得到想到的数据，那就不需要读取行，一个索引包含了(或覆盖了)满足查询结果的数据叫作覆盖索引
注意：如果要使用覆盖索引，一定要注意select列表中只取出需要的列，不可select *，如果将所有字段一起做索引会导致索引文件过大，查询性能下降；
Using where：表示需要查询磁盘里存储的数据，需要回表查询；
使用了where 过滤，表示存储引擎层返回的记录并不是所有的都满足查询条件，需要在server层进行过滤（跟是否使用索引没有关系）
Using join buffer：使用了连接缓存
保证JOIN语句中被驱动表上JOIN条件字段已经添加索引；
当使用left join时，左表是驱动表，右表是被驱动表
当使用right join时，右表时驱动表，左表是被驱动表
当使用join时，mysql会选择数据量比较小的表作为驱动表，大表作为被驱动表
impossible where：where子句的值总是false，不能用来获取任何元组
select tables optimized away：在没有group by子句的情况下，基于索引优化MIN/MAX操作或对于MyISAM存储引擎优化COUNT(*) 操作，不必等到执行阶段再进行计算，查询执行计划生成的阶段完成优化；
distinct：优化distinct，在找到第一匹配的元组后即停止找相同值的工作
如果索引有多列，要遵守最左前缀法则，指的是查询从索引的最左前列开始并且不跳过索引中的列，即最左优先；
针对单列索引，左边准确而右边模糊，可以用到索引,反之则不可以，如 where name like ‘poly%’，可以用到，而”%poly”则不用到；
针对多列索引，左边的列用到索引后，右侧的列才有可能用到索引；如 index(a,b,c)，where a=? and b=? ，b列索引会用到，如果直接 where b=?，因为a列没用索引，所以b索引，用不到；如下：
虽然显示Using where，Using index，但rows的值还是全表，没有执行覆盖索引
加上col1的条件，可以执行覆盖索引
说明：
1.MySQL 会一直向右匹配直到遇到范围查询(>、<、between、like)就停止匹配，比如a = 1 and b = 2 and c > 3 and d = 4， 如果建立(a,b,c,d)顺序的索引，d是用不到索引的，如果建立(a,b,d,c)的索引则都可以用到，a，b，d的顺序可以任意调整；
2.=和in可以乱序，比如a = 1 and b = 2 and c = 3 建立(a,b,c)索引可以任意顺序，MySQL的查询优化器会优化成索引可以识别的形式；
关于使用select *需要注意的有如下事项：
1.select * 会导致联合索引的覆盖索引失效；
2.select * 会增加解析器的工作；
Order By 优化
Group By 优化
Group By 实质是先排序后进行分组，遵照索引建的最佳左前缀；
最新文章
- PHP中new static()与new self()的比较
- Chrome 用户数据配置文件夹保存路径在哪？（Mac OS X/Windows/Linux）
- window共享linux下的文件 samba
- Java Mysql分页显示
- bat 命令分行写
- [转] error LNK2026: 模块对于 SAFESEH 映像是不安全的
- 使用NPIO操作Excel
- 隐马尔科夫模型 介绍 HMM python代码
- 『重构--改善既有代码的设计』读书笔记----Change Value to Reference
- Floyd算法(弗洛伊德算法)
- NEO从入门到开窗(3) - NEO编译器
- HTTP笔记01-http相关的基础知识
- [机器学习&数据挖掘]SVM---核函数
- MFC onpaint() ondraw()
- 【重大更新】DevExpress v17.2新版亮点—WinForms篇（二）
- NOIP2016 组合数问题
- CSS3动画效果之Transform
- 怎么查这个文件在linux下的哪个目录
- unittest单元测试框架之coverage代码覆盖率统计
- Mint-ui 中 Popup 作为组件引入，控制弹出框的显示与隐藏遇到的问题。