mysql文档摘要续
mysql的权限设置：存储于mysql数据库包含一下表
1、user 最主要的权限设置表，所有的账户，全局权限
2、db 数据库层面的权限设置
3、tables_priv 数据表层面的权限设置
4、colums_priv 行级层面的权限设置
5、proc_priv 存储过程、函数的权限设置
mysql的权限验证一般先检查user表如果通过则授权，没有则继续db表，如果通过则添加到user表，并授权，延续以上规则。。。
mysql一般在启动时将所有的权限表都加载进内存，如果你执行以下 GRANT, REVOKE, SET PASSWORD, or RENAME USER命令，mysql会立即重新加载grant table；
如果是修改grant table，如insert、update，则不会生效，除非mysql restart或者执行flush privileges命令。mysql对权限的改变一般是针对下一个请求
mysql 优化：
1：mysql在新的版本不再以是否扫描超过30%来评价是否使用索引，而是会综合评价各种因素如table size, number of rows, and I/O block size。
2: icp:index condition pushdown索引条件下推 就是筛选字段在索引中的where条件从server层下推到storage engine层，这样可以在存储引擎层过滤数据。由此可见，ICP可以减少存储引擎访问基表的次数和mysql server访问存储引擎的次数。条件：
- 只能用于二级索引(secondary index)。
- explain显示的执行计划中type值（join 类型）为range、 ref、 eq_ref或者ref_or_null。且查询需要访问表的整行数据，即不能直接通过二级索引的元组数据获得查询结果(索引覆盖)。
3.ICP可以用于MyISAM和InnnoDB存储引擎，不支持分区表（5.7将会解决这个问题）。
举个例子：假设一个表包含index(zipcode,lastname,firstname)
SELECT * FROM people WHERE zipcode='95054' AND lastname LIKE '%etrunia%' AND address LIKE '%Main Street%';
mysql通过index扫描zipcode＝95054，由于第二部分的条件lastname like %etrunia%没有办法限制要扫描的rows，如果没有icp的话mysql需要把所以zipcode＝95054的row全部返回，通过icp，存储引擎可以过滤掉不符合lastname的rows。
3:NLJ 算法:将驱动表/外部表的结果集作为循环基础数据，然后循环从该结果集每次一条获取数据作为下一个表的过滤条件查询数据，然后合并结果。如果有多表join，则将前面的表的结果集作为循环数据，取到每行再到联接的下一个表中循环匹配，获取结果集返回给客户端
BNL 算法:将外层循环的行/结果集存入join buffer, 内层循环的每一行与整个buffer中的记录做比较，从而减少内层循环的次数.
for each row in t1 matching range {
for each row in t2 matching reference key {
store used columns from t1, t2 in join buffer
if buffer is full {
for each row in t3 {
for each t1, t2 combination in join buffer {
if row satisfies join conditions,
send to client
}
}
empty buffer
}
}
}
if buffer is not empty {
for each row in t3 {
for each t1, t2 combination in join buffer {
if row satisfies join conditions,
send to client
}
}
}
MySQL使用Join Buffer有以下要点:
1. join_buffer_size变量决定buffer大小。
2. 只有在join类型为all, index, range的时候才可以使用join buffer。
3. 能够被buffer的每一个join都会分配一个buffer, 也就是说一个query最终可能会使用多个join buffer。
4. 第一个nonconst table不会分配join buffer, 即便其扫描类型是all或者index。
5. 在join之前就会分配join buffer, 在query执行完毕即释放。
6. join buffer中只会保存参与join的列, 并非整个数据行。
4:MRR(Multi-Range Read)我们知道，mysql在对辅助索引取数据的时候，先是通过索引页的叶子节点找到对应的主键id，再通过主键id找到对应的数据页，在数据页中最后通过二分查找找到对应的数据。这里我们设想一下，假如在一张表中对某个字段建立一个辅助索引，而这个字段有一些重复的数据，那么我们根据这个字段去做where条件的时候，势必每次取到的id不一定是顺序的，既然不会是顺序的，那么必然会产生一定的随机io。在计算机中，随机io的速度比顺序的io的速度慢很多，因为在一个柱面中，随机io必然会造成磁头的随机旋转，从而产生一定量的磁盘io，而顺序io则可降低到最低。面对这种情况，就是我们下面介绍的mrr优化大显身手的地方了。
对非唯一的辅助索引查询，由于每次在辅佐索引页叶子节点上查找主键的id的时候不一定是顺序的，如果每次都通过查询出来的主键盘id去拿数据的话，就会产生我们上面所说的随机io的情况。mrr优化就在这里做了功夫，在通过辅佐索引页上拿到主键id后，并不是通过id直接去数据页中取数据。而是先通过排序算法，把取到的主键id按照从小到大的方式排序，然后再通过书签查找，取得对应的数据。这样就可以把随机io的情况降到最低。其实mrr优化还有一个重要的作用就是避免了缓冲池中页的频繁更改。再没有启用mrr优化之前，由于主键的id是随机取的，那么可能每次取到的数据都不在同一个页中。比如第一个数据在一个页中，第二个数据又在另一个页中，而第三个数据又在第一个页中，在缓冲池不够大的时候，这样的情况会造成缓冲池中的页不断的离开缓冲池，然后又进入缓冲池，从而造成缓冲池的热点数据频繁更新。而启用了mrr优化以后，由于主键是顺序的，则可以把此开销降为最低。
5：order by的filesort算法 原始算法排序的数据是<sort_key,rowId> ,改进后的是<sort_key,additional_fields(select 选取的字段)>相当于只需要一次表读取
6：group by的三种算法：
a). 使用临时表。创建临时表，放入要group by的数据，按照group by的字段进行排序，生成group by结果集以及计算聚合函数的结果
b). Loose Index Scan
使用条件是针对单表的查询；group by的字段按照顺序前导匹配某个索引；索引必须是有序的；聚合函数只包含min、max，字段必须相同并且在索引中，并且是索引中紧跟着group by字段的下一个字段；索引字段中除了group by的字段外，其他字段如果出现在where条件中，where条件只能是常量值。MySQL 5.4.4加入了几个新的聚合函数：AVG(DISTINCT), SUM(DISTINCT), 和COUNT(DISTINCT)，也是有限制性的
这种方式只需要部分扫描索引即可完成group by操作，如果where子句中只有group by字段的条件，则索引扫描的层级只需要到group by包含的字段（如果索引除了group by字段之外还包含其他字段，这种处理方式根本不需要扫描到索引页节点等层级）。正因为索引中包含的信息有限，因此该算法对聚合函数、索引的要求非常多，适用的场景比较窄，但是效率是最高的
例如表t1(c1,c2,c3,c4)，有索引idx(c1,c2,c3)，下面语句可以使用Loose Index Scan：
SELECT c1, MIN(c2) FROM t1 GROUP BY c1;
SELECT c1, c2 FROM t1 WHERE c1 < const GROUP BY c1, c2;
SELECT MAX(c3), MIN(c3), c1, c2 FROM t1 WHERE c2 > const GROUP BY c1, c2;
SELECT c2 FROM t1 WHERE c1 < const GROUP BY c1, c2;
SELECT c1, c2 FROM t1 WHERE c3 = const GROUP BY c1, c2;
c). Tight Index Scan
其实就是利用有序索引的特性，避免创建临时表以及额外的排序操作，因此前提条件是能够利用有序索引；仅通过索引扫描可以完成查询
例如表t1(c1,c2,c3,c4)，有索引idx(c1,c2,c3)，下面2个语句都可以使用Tight Index Scan进行group by：
SELECT c1, c2, c3 FROM t1 WHERE c1 = 'a' GROUP BY c2, c3;
在EXPLAIN中，Extra中出现Using index for group-by表示使用Loose Index Scan实现；Using Temporary表示使用临时表实现；否则表示使用Tight Index Scan实现
7：子查询
1 semi-join：半连接优化技术，本质上是把子查询上拉到父查询中，与父查询的表做join/semi-join的操作。关键词是“上拉”。
2 Materialization：物化子查询，子查询的结果通常缓存在内存或临时表中
3 EXISTS strategy：把半连接转换为EXISTS操作。本质上是把父表的条件下推到子查询中关键词是“下推”。
|
子查询格式 |
可选的优化策略 |
|
IN/=ANY |
semi-join， Materialization，EXISTS strategy |
|
NOT IN/<>ALL |
Materialization， EXISTS strategy
|
全表扫描总结：
1：表的数据量太小
2：没有设置where或者on语句
3：使用索引所需要计算的表记录太大以至于全表扫描可能更快
4：使用的索引筛选度太低以至于没有全表扫描快
改进：
1：使用analyze table tal_name，分析表的索引，会加读锁
2：使用force index强制索引
3：启动 mysqld 时使用参数 --max-seeks-for-key=1000 或者执行 SET max_seeks_for_key=1000 来告诉优化程序，所有的索引都不会导致超过1000次的索引搜索。
join相关的规则：
1、left join 、straight_join都是强制使用左表作为驱动表
2、inner join自动选择表作为驱动表，MySQL优化器采用了简单粗暴的解决方法：哪个表的结果集小，就以哪个表为驱动表，所以可能会出现不是最优的情况。
3、多表join中，如果排序列不属于驱动表则无法使用索引完成排序操作。
说明：在EXPLAIN结果中，第一行出现的表就是驱动表。
