索引(一般监控到慢SQL，才会建索引)

InnoDB  引擎 btree 
memory引擎中的hash索引，排序无法优化
在不考虑hash冲突的时候，每次只要查询一次就可以找到，因为在磁盘上都是根据算出的hash存储的，
但是遇到范围查询
例如： where  id > 4 查询就会比较慢 ，因为>4的数据，根据hash算出之后已经分散，但是用btree进行范围查询就会比较快
（比4大的右边所有拿走，比4 小的左b所有拿走）

范围查询数据速度快

前缀索引

btree 可以使用
hash 无法使用
比如 helloworld 如果用hello去查询 helloword btree索引可以用上 因为前缀一样，
而hash经过hash算法之后就不可以用了，算出的完全不一样

常见索引误区

 独立的索引同时只能用上一个 where id=3 and price>100

多列联合索引

为了让id和price都发挥作用，可以建立多列联合索引
注意： 多列联合索引上发挥作用，需要遵循（左前缀原则）

左前缀例子（是一种联合索引的匹配规则）

   以index(a,b,c)为例（和顺序有关）

左前缀原则

索引除了可以提高查询速度外，还可以提高排序和分组的速度

where a=3 and b=4 order by c   ，则a,b,c 都可以用上

聚簇索引与非聚簇索引 (从物理存储角度)

非聚簇索引 数据和索引不在同一个文件（mysql下有三个存储数据的文件） 
    非聚簇索引的叶子节点并不是真实的数据，他的叶子节点依然是索引节点，存放的是该索引字段的值以及对应的主键索引(聚簇索引)
聚簇索引 
    根据主键查询条目时,不用回行(数据就在主键节点下)
    如果碰到不规则数据插入时,造成频繁的页分裂
    在Mysql中是没有语句来另外生成的，在Innodb中，Mysql中的数据是按照主键的顺序来存放的，那么聚簇索引就是按照每张表的主键来 
    构造一颗B+树，叶子节点存放的就是整张表的行数据，由于表里的数据只能按照一个B+树排序，因此一张表只能有一个聚簇索引

在Innodb中，聚簇索引默认就是主键索引

如果表中没有主键呢
- 没有主键时，会用一个唯一且不为空的索引列做为主键，成为此表的聚簇索引
- 如果没有这样的索引，InnoDB会隐式定义一个主键来作为聚簇索引。

什么情况下不去聚簇索引树上查询呢

[https://mp.weixin.qq.com/s?__biz=MzIwMDgzMjc3NA==&mid=2247484040&idx=1&sn=5918c27351f366a777484b440aeeaa22&chksm=96f660e1a181e9f7e9dc6f6b0570352c20650c9de51ffbd236e0d71d4edaf99805334361ac72&scene=21#wechat_redirect](https://mp.weixin.qq.com/s?__biz=MzIwMDgzMjc3NA==&mid=2247484040&idx=1&sn=5918c27351f366a777484b440aeeaa22&chksm=96f660e1a181e9f7e9dc6f6b0570352c20650c9de51ffbd236e0d71d4edaf99805334361ac72&scene=21#wechat_redirect)

多加一个索引，就会多生成一颗非聚簇索引树，因此，索引不能乱加，因为有几个索引，就有几颗非聚簇索引树，你在做插入操作的时候，
需要维护这几棵树的变化，所以，索引太多，插入性能就会下降

自增主键和uuid作为主键的区别

  由于主键使用了聚簇索引，如果主键是自增id，那么对应的数据一定也是相邻地存放在磁盘上，写入性能比较高
  如果是uuid的形式，频繁的插入会使Innodb频繁地移动磁盘块，写入性能就比较低了

理想的索引

- 查询频繁
- 区分度高
    100万用户，性别基本上男 、女 各为50W，区分度就低
- 长度小
       索引长度直接影响索引文件的大小，影响增删改的速度，并间接影响查询速度（占用内存多）
- 尽量能覆盖常用查询字段

索引的例子

 针对列中的值，从左到右截取部分，来建立索引，
  一点点...    一丝丝....    一丢丢....   一丁点..
 1. 截取越短，区分度越小，所以效果就越不好  如果根据"一" 去查询  则上述数据区分不出来
 2.截的越长，重复越低，区分度越高，索引效果越好，但带来的影响也越大，增删改变慢，并间接影响查询速度。
 所以我们要 在 区分度  长度 两者上 取得一个平衡

聚簇索引的页分裂

索引覆盖

索引覆盖是指如果查询的列恰好是索引的一部分，那么查询只需要在索引文件上进行，不需要回行到磁盘在找数据，
这种查询速度非常快，称为“索引覆盖”
eg: 有一张表table1 ，有一个联合索引（a,b）
  select  a,b from table1 在索引上就能找到结果，就不用回表去查询
  select a,b,c from table1 c列在索引上不存在，就需要回表查询

索引与排序 （排序可能发生的2种情况） ？？？

-  对于覆盖索引，直接在索引上查询是，就是有顺序的，using index;
    在InnoDB引擎中，沿着索引字段排序，也是自然有序的
    对于MyISAM引擎中，如果按照某索引字段排序，如id,但取出的字段中，有未索引字段，如： good_name,
    MyISAM的做法不是 索引->回行，索引->回行，而是先取出所有行，再进行排序
-   2.  先取出数据，形成临时表，filesortwe文件排序，但文件可能在磁盘上，也可能在内存上
  我们的争取目标 --取出来的数据本身就是有序的，利用索引来排序

重复索引和冗余索引 ？？？

- 重复索引：是指在同一个类（如：age）或者 顺序相同的几个列（age,school）建立多个索引，成为重复索引，重复索引没有任何帮助，
    只会增大索引文件，拖慢更新进度
- 冗余索引：冗余索引是指2个索引索覆盖的列有重叠。

查询优化

Mysql 是否扫描了额外的记录，理想情况下扫描的行数和返回的行数应该是相同的 
select * from user where id= 1
如果id有索引将根据索引去查询并返回结果 ，但如果把id的索引删除掉之后则MySQL需要扫描所有记录然后根据Where 条件过滤，最后返回id= 1 的结果

优化方式：

  什么情况下需要优化?
  如果发现查询需要扫描大量的数据但只返回少量的行。
 1.使用索引覆盖扫描，把所有需要用的列都放到索引中，这样存储引擎无须回表获取对应行就可以返回结果了
 2.改变库表结构，例如使用单独的汇总表
 3.重写这个复杂的查询，让MySQL优化器能够以更优化的方式执行这个查询（后续讨论）

6.3 重构查询的方式

6.3.1  一个复杂查询还是多个简单查询
6.3.2  切分查询
6.3.3  分解关联查询
    可以增加数据查询的缓存，可以把一个多表关联的查询 分解成多次查询，返回结果在应用程序中进行关联

6.4 查询执行的基础

 1.客户端发送一条查询给服务器
 2.服务器先检查查询缓存，如果命中了缓存， 则立刻返回存储在缓存中的结果，否则进入下一阶段
 3.服务器端进行SQL解析，预处理、再由优化器生成对应的执行计划
 4.MySQL根据优化器生成的执行计划，调用存储引擎的API来执行查询
 5.将结果返回给客户端

6.4.1 MySQL客户端/服务器通信协议

    MySQL客户端和服务器之间的通信协议是“半双工"，这意味着，在任何时刻，要么是有服务器向客户端发送数据，要么是由客户端向服务 
    器发送消息
    max_allowed_packet: 如果查询太大，服务器端会拒绝接收更多的数据并抛出相应错误。
    如果需要返回一个很大的结果集的时候，缓存数据可能并不好，因为库函数会花很多的时间和内存来存储所有的结果集。  
 该状态表示了MySQL当前正在做什么，
 show FULL PROCESSLIST 

6.4.2 查询缓存

6.4.3 查询优化处理

 查询的生命周期的下一步是将一个SQL转换成一个执行计划，MySQL在依照这个执行计划和存储引擎进行交互，这包括多个子阶段
 解析SQL   、预处理、优化SQL执行计划。
 eg： select  * from user where id = 1
SHOW STATUS LIKE 'Last_query_cost'   查询出来的Value 表示：需要做多少个数据页的随机查找才能完成上面eg处的查询

优化策略：静态优化和动态优化

limit及翻页优化

limit offset,N 当offset非常大时，效率极低
原因是mysql并不是跳过offset行，然后单取N行,而是取offset+N行,返回放弃前offset行，返回N行。
效率较低，当offset越大时，效率越低。
eg: limit  10000, 5
  它并不是跳过10000，然后取出5条 而是 取出10005条然后丢掉前面10000条
优化办法:
    1.从业务上去解决
    办法:不允许翻过100页
    2.不用offset ,用条件查询
    将 select * from lx_com limt 1000000,3  很慢很慢
    改为
    select * from lx_xom where id >1000000 limit 3;
  但是数据不可以删 如果要删除只能改状态
3.非要物理删除，还要用offset精确查询，还不限制用户分页怎么办？

分析

使用select的正确姿势

  为什么不推荐使用select * from table
 - 网络IO问题
   如果应用程序和数据库是同一个机器那没有影响
   select *  会查询出不需要的、额外的数据，这些额外的数据在网络上进行传输，必定会造成性能延迟，万一table中有一个文本类型的字 
   段，如果不在统一台机器上那网络传输会非常慢
 - 索引问题
    select coll from table;
    select * from table;
    如果coll字段包含索引信息，那么这2句sql执行时间差距会非常大
    在coll字段有索引的情况下，mysql是可以不用读data，直接使用index里面的值就返回结果的，但是用了
    select *  ，就需要读取其他列的数据，这是在读完index以后还需要去读data才会返回结果，这就造成了额外的性能开销

回表

  create table test(
      id int primary key,
      a int not null,
      name varchar,
      index(a)
  )engine = InnoDB;

 这个时候InnoDB就会建立2个B+索引树
  一个是主键的聚簇索引（叶子节点才存上了整条记录的值） ，另一个是普通索引的非聚簇索引（辅助索引） 
  select name from test where a = 30;
 该查询MySQL是会走a的索引，但是a索引上并没有存储name的值，此时就需要拿到a上的主键值，然后通过这个主键去走聚簇索引，最终拿到其中的name值，这个过程就叫回表
所以，MySQL在辅助索引上找到对应的主键值并通过主键值在聚簇索索引找到对应的数据就叫回表

连接查询

left join : 以左表为主 左表有 右表没有 ，则 输出左表所有的内容 右表字段内容显示 NULL
right join :  以右表为主 ，右表有 左表没有 ，则输出右表所有的内容，左表字段内容显示null 
join:  显示两张表共同存在的 否则不查询出来

参考: https://juejin.im/post/5db19103e51d452a300b14c9