MySQL进阶版

1. join
2. 索引：
   索引实际上也是一张表，保存了主键与索引字段，并指向实体表的记录，所以索引列也是占空间的
   虽然索引提高了查询速度，但是降低了更新表的速度，如对表进行inset，update，和delete，因为在更新表时，不仅要保存数据，还要保存索引文件每次更新添加了索引列的字段，都会调整因为更新带来的键值变化后的索引信息
   2.1 单值索引：一个索引只包含单个列，一个表可以有多个单列索引
   2.2 唯一索引：索引列的值必须唯一，但是允许有空值
   2.3 复合索引：一个索引包含多个列
   2.4 基本语法：
   2.4.1 创建：create [unique] index indexname ON mytable(columnname(length));
   或 alter mytable ADD [unique] index [indexname] ON (columnname(length));
   2.4.2 删除：drop index [indexname] ON mytable;
   2.4.3 查看：show index from table_name\G
   2.5 哪些情况下建立索引：
   主键自动建立唯一索引
   频繁作为查询条件的字段应该创建索引
   查询中与其他表关联的字段，外键关系建立索引
   频繁更新的字段不适合创建索引
   where条件里用不到的字段不创建索引
   选择复合索引
   查询中排序的字段，排序字段若通过索引去访问将大大提高排序速度
   查询中统计或者分组字段
   2.6 哪些情况不建立索引
   表记录太少
   经常增删改的表
   数据重复且平均分布的表字段
3. explain
   3.1 使用explain可以模拟优化器执行SQL查询语句，从而知道MySQL是如何处理你的SQL语句的
   3.2 使用：explain+SQL语句
   id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra
   3.3 作用
   表的读取顺序
   数据读取操作的操作类型
   哪些索引可以使用
   哪些索引被实际使用
   表之间的引用
   每张表有多少行被优化器查询
   3.4 各字段解释
   3.4.1 id: select查询的序列号，包含一组数字，表示查询中执行select子句或操作表顺序，id相同，执行顺序由上而下；id不同，如果是子查询，id的序号会递增，id值越大优先级越高，越先被执行；id相同不同，id如果相同，可以认为是一组，从上往下顺序执行，在所有组中，id值越大，优先级越高，越先执行
   3.4.2 select_type:
   simple：简单的select查询，查询中不包含子查询或union
   primary：查询中包含任何复杂的子部分，最外层查询则被标记为primary
   subquery：在select或where列表中包含子查询
   derived：在from列表中包含的子查询被标记为derived（衍生）MySQL会递归执行这些子查询，把结果放在临时表里
   union：若第二个select出现在union之后，则被标记为union，若union包含在from子句的查询中，外层select被标记为derived
   union result：多个union合并的结果集
   3.4.3 type：
   system > const > eq_ref > ref > range > index > all
   const 表示通过索引一次就找到了，const用于比较primary key或者unique索引，因为只匹配一行数据，所以很快，如将主键置于where列表中，MySQL就能将该查询转换为一个常量
   eq_ref 唯一性索引扫描，对于每个索引建，表中只有一条记录与之匹配，常见于主键或唯一索引扫描
   ref 非唯一性索引扫描，发那会匹配某个单独值的所有行，本质上也是一种索引访问，它返回所有匹配某个单独值的行，然而，它可能会找到多个符合条件的行，属于查找和扫描的混合体
   range 只检索给定范围的行，使用一个索引来选择行。一般就是在你的where语句中出现了between，<，>，in等
   index 全索引扫描，遍历索引树
   all 遍历全表
   3.4.4 possible_keys：显示可能应用在这张表中的索引，一个或多个，查询涉及到字段上若存在索引，则该索引将被列出，但不一定被查询实际使用
   3.4.5 keys：实际使用的索引，如果为NULL，则没有使用索引；查询中若使用了覆盖索引，则索引仅出现在key列表中
   3.4.6 key_len：表示索引中使用的字节数，可通过该列计算查询中使用的索引的长度，在不损失精确性的情况下，长度越短越好。key_len显示的值为索引字段的最大可能长度，并非实际使用长度，则key_len是根据表定义计算而得，不是通过表内检索出的
   3.4.7 ref：显示索引的哪一列被使用了，如果可能的话，是一个常数，哪些列或常量被用于查找索引列上的值
   3.4.8 rows：根据表统计信息及索引选用情况，大致估算出找到所需的记录所需要读取的行数
   3.4.9 extra：
   using filesort：说明MySQL会对数据使用一个外部的索引排序，而不是按照表内的索引顺序进行读取，MySQL中无法利用索引完成的排序操作成为“文件排序”
   using temporary：使用了临时表保存中间结果，MySQL在对查询结果排序时使用临时表，常见于排序order by和分组查询group by
   using index：表示相应的select操作中使用了覆盖索引，避免了访问表的数据行，效率不错。如果同时出现using where，表明索引呗用来执行索引键值的查找；如果没有同时出现using where，表明索引用来读取数据而非执行查找动作
   覆盖索引：查询列要被所建的索引覆盖，select的数据列只用从索引中就能取得，不必读取数据行，如果要使用覆盖索引，一定要注意select列表中只取出需要的列，不可select*
4. 索引优化（避免索引失效）
   4.1 全值匹配我最爱
   4.2 最佳左前缀法则：如果索引了多列，要遵守最左前缀法则，指的是查询从索引的最左前列开始并且不跳过索引中的列
   4.3 不再索引列上做任何操作（计算，函数，（自动或手动）类型转换），会导致索引失效
   4.4 存储引擎不能使用索引中范围条件右边的列
   4.5 尽量使用覆盖索引（只访问索引的查询（索引列和查询列一致）），减少select*
   4.6 MySQL在使用不等于！=或<>会导致索引失效，全表扫描
   4.7 is null和 is not null也无法使用索引
   4.8 like以通配符开头（’%bc…’）（尽量只在右边写%）MySQL索引失效会变成全表扫描，如果一定要写两个%的话，则用覆盖索引
   4.9 字符串不加单引号会导致索引失效（发生了类型转换）
   4.10 少用or，用它来连接时会索引失效
5. 查询优化
   5.1 小表驱动大表
   5.2 order by和group by也遵守最佳左前缀法则，能在where写的不写在having
6. show profile：MySQL提供用来分析当前会话中语句执行的资源消耗情况
7. 表锁（偏读）：偏向MyISAM存储引擎，开销小，加锁快，无死锁，锁的粒度大，并发低
   show open tables; //查看表上加过的锁
   lock table 表名字 read（write），表名字2 read（write）； //手动添加表锁
   unlock tables； //释放表锁
   在session1里加了读锁，session1 可以读自己，不可以修改自己，不可以读别的表；session2 可以读自己，修改自己会阻塞，可以读或写别的表
   在session1里加了写锁，session1 可以读自己，可以修改自己，不可以读别的表；session2 读自己会阻塞，修改自己会阻塞，可以读或写别的表
   MyISAM的读写锁调度是写优先，这也是MyISAM不适合做写为主表的引擎。因为写锁后，其他线程不能做任何操作，大量的更新会使查询很难得到锁，从而造成永远阻塞
8. 行锁（偏写）：偏向InnoDB存储引擎，开销大，加锁慢，可能出现死锁，锁的粒度小，并发高
   InnoDB和MyISAM的两个不同点：支持事务和采用了行级锁
   无索引（索引失效）行锁会变成表锁
   可以利用select XXX for update 锁定某一行
9. 间隙锁：当我们用范围条件而不是相等条件检索数据，InnoDB会锁定整个范围内所有的索引键值，即使这个键值不存在；比如表里没有a=2这一项，在一个事务里更新a>1的项时，在另一个事务里插入a=2，会阻塞
10. 考虑到磁盘IO是非常高昂的操作，计算机操作系统做了一些优化，
    当一次IO时，不光把当前磁盘地址的数据，而是把相邻的数据也都读取到内存缓冲区内，
    因为局部预读性原理告诉我们，当计算机访问一个地址的数据的时候，与其相邻的数据也会很快被访问到。