1.Mysql基本架构图
一.Innodb和MylSAM的区别
1.Innodb 支持事务，MylSam 不支持事务
2.Innodb支持外键，Mylsam不支持
3.Innodb支持行锁
4.Innodb删除数据表，是一行一行删除，Mylsam会重建
5.Mylsam支持全文索引，Innodb不支持
二.索引
索引的三大优点：索引大大减少了服务器需要扫描的行数，索引可以帮助服务器避免排序和临时表，索引可以将随机IO变成顺序IO
1.聚集索引
定义：数据行的物理顺序和列（主键）的逻辑顺序相同，一个表中只能有一个聚集索引。叶子节点就是对应的数据节点。
聚集索引就是按照每一张表的主键构造的一棵B+树，同时叶子节点中存放的即是整张表的行记录数据
2.非聚集索引（辅助索引）
定义：索引中索引的逻辑顺序与磁盘上行的物理存储顺序不同，一个表中可以拥有多个非聚集索引
非聚集索引，叶子节点并不包含行记录的全部数据，叶子节点除键值以外，每个叶子节点中的索引行还包含了一个书签。指向了主键索引的主键，然后再通过主键索引来找到一个完整的行记录
非聚集索引的二次查询问题
非聚集索引叶节点仍然是索引节点，只是有一个指针指向对应的数据块，此如果使用非聚集索引查询，而查询列中包含了其他该索引没有覆盖的列，那么他还要进行第二次的查询，查询节点上对应的数据行的数据
举个例子：
使用以下语句进行查询，不需要进行二次查询，直接就可以从非聚集索引的节点里面就可以获取到查询列的数据。
select id, username from t1 where username = '小明'
select username from t1 where username = '小明'
但是使用以下语句进行查询，就需要二次的查询去获取原数据行的score：
select username, score from t1 where username = '小明'
- 通过cardinality关键字来决定，建索引的必要性，如果关键字越小，则需考虑是否有必要建立这个索引
参考资料：https://blog.csdn.net/weigeshikebi/article/details/80214965
3.联合索引
1. 联合索引指的是对表上的多个列进行索引。
2.能够使用联合索引的情况
①全匹配
select * from t where a=xxx and b=xxx
②最左前缀匹配
对于单个的a列，也可以用到(a,b,c)联合索引
select * from t where a=xxx
select * from t where a=xxx and b=xxx （可以)
select * from t where a=xxx and b=xxx and c=xxx（可以）
select * from t where b=xxx and c=xxx (则不行)
select * from t where b=xxx（不行）我们可以看到，叶子节点上的b为1，2，1，4，1，2不是有序的
③不能使用联合索引
叶子节点上b的值为1,2,1,4,1,2，显然不是排序的。
select * from t where b=xxx
同理，如果建立(a,b,c)索引，则下面的查询都能用到索引。
select * from t where a=xxx and b=xxx and c=xxx
select * from t where a=xxx and b=xxx
select * from t where a=xxx
select * from t where b=xxx and c=xxx
3.联合索引可对第二个列进行排序处理，减少一次filesort。
在联合索引(a,b)中，由于a相同的情况下b本来就是排序的，所以下面的查询能够用到(a,b)索引，且不需要额外再进行排序。
select * from t where a=xxx order by b
同理，如果建立(a,b,c)索引，下面的查询也能少一次fileSort。
select * from t where a=xxx and b=xxx order by c
select * from t where b=xxx order by c
select * from t where a=xxx order by b
但是对于
select * from t where a=xxx order by c
则不行，c还需要再进行一次排序
4.覆盖索引
如果一个索引包含(或覆盖)所有需要查询的字段的值，称为‘覆盖索引
150多万的数据，这么一个简单的语句：
慢查询日志里居然很多用了1秒的，Explain的结果是：
从Explain的结果可以看出，查询已经使用了索引，但为什么还这么慢？
分析：首先，该语句ORDER BY 使用了Using filesort文件排序，查询效率低；其次，查询字段不在索引上，没有使用覆盖索引，需要通过索引回表查询；也有数据分布的原因。
知道了原因，那么问题就好解决了。
解决方案：由于只需查询uid字段，添加一个联合索引便可以避免回表和文件排序，利用覆盖索引提升查询速度，同时利用索引完成排序。
覆盖索引：SQL只需要通过索引就可以返回查询所需要的数据，而不必通过二级索引查到主键之后再去查询数据。
我们再Explain看一次：
Extra信息已经有'Using Index'，表示已经使用了覆盖索引。经过索引优化之后，线上的查询基本不超过0.001秒。
5.索引下推优化（ICP优化）-减少回表次数
而 MySQL 5.6 引入的索引下推优化（index condition pushdown)， 可以在索引遍历过程中，对索引中包含的字段先做判断，直接过滤掉不满足条件的记录，减少回表次数。
图 3 和图 4，是这两个过程的执行流程图。
在图 3 和 4 这两个图里面，每一个虚线箭头表示回表一次。
图 3 中，在 (name,age) 索引里面我特意去掉了 age 的值，这个过程 InnoDB 并不会去看 age 的值，只是按顺序把“name 第一个字是’张’”的记录一条条取出来回表。因此，需要回表 4 次。
图 4 跟图 3 的区别是，InnoDB 在 (name,age) 索引内部就判断了 age 是否等于 10，对于不等于 10 的记录，直接判断并跳过。在我们的这个例子中，只需要对 ID4、ID5 这两条记录回表取数据判断，就只需要回表 2 次。
6.MRR优化--将随机IO转变为顺序IO提高效率
1.将查询得到的辅助索引键值放入一个缓存当中，这时缓存中的数据是根据辅助索引键值排序的
2.将缓存中的键值，根据RowID（主键）进行排序
3.根据RowID的排序顺序来访问实际的数据文件。
没有MRR之前：
在没有MRR之前,或者没有开启MRR特性时，MySQL 针对基于辅助索引的查询策略是这样的：
selectnon_key_colfromtb where key_col=x;
MySQL 执行查询的伪代码
第一步 先根据where条件中的辅助索引获取辅助索引与主键的集合，结果集为rest。
select key_col,pk_col from tb where key_col=x order by key_col
第二步 通过第一步获取的主键来获取对应的值。
foreach pk_column valueinrest do:
select non_key_column from tb where pk_column=val
由于MySQL存储数据的方式： 辅助索引的存储顺序并非与主键的顺序一致，从图中可以看出,根据辅助索引获取的主键来访问表中的数据会导致随机的IO . 不同主键不在同一个page 里面时必然导致多次IO 和随机读。
在使用MRR优化特性的情况下，
MySQL 针对基于辅助索引的查询策略是这样的：
第一步 先根据where条件中的辅助索引获取辅助索引与主键的集合rest
select key_col,pk_col from tb where key_col=x order by key_col;
第二步 将结果集rest放在buffer里面(read_rnd_buffer_size 大小直到buffer满了)，然后对结果集rest按照pk_col排序，得到结果集是rest_sort
第三步 利用已经排序过的结果集，访问表中的数据，此时是顺序IO.
select non_key_col fromtb where pk_colin(rest_sort)
从图示所看，MySQL 将根据辅助索引获取的结果集根据主键进行排序，将乱序化为有序，可以用主键顺序访问基表，将随机读转化为顺序读，多页数据记录可一次性读入或根据此次的主键范围分次读入，以减少IO操作，提高查询效率。
7.普通索引和唯一索引
MySQL中普通索引和唯一索引的区别详解
一、查询和更新上的区别
这两类索引在查询能力上是没差别的，主要考虑的是对更新性能的影响。建议尽量选择普通索引。
查询操作：
■ 普通索引
查找到第一个满足条件的记录后，继续向后遍历，直到第一个不满足条件的记录。
■ 唯一索引
由于索引定义了唯一性，查找到第一个满足条件的记录后，直接停止继续检索。
普通索引会多检索一次，几乎没有影响。因为 InnoDB 的数据是按照数据页为单位进行读写的，需要读取数据时，并不是直接从磁盘读取记录，而是先把数据页读到内存，再去数据页中检索。
一个数据页默认 16 KB，对于整型字段，一个数据页可以放近千个 key，除非要读取的数据在数据页的最后一条记录，就需要再读一个数据页，这种情况很少，对CPU的消耗基本可以忽略了。
因此说，在查询数据方面，普通索引和唯一索引没差别。
更新操作：
更新操作并不是直接对磁盘中的数据进行更新，是先把数据页从磁盘读入内存，再更新数据页。
■ 普通索引
将数据页从磁盘读入内存，更新数据页。
■ 唯一索引
将数据页从磁盘读入内存，判断是否唯一，再更新数据页。
由于 MySQL 中有个 change buffer 的机制，会导致普通索引和唯一索引在更新上有一定的区别。
change buffer的作用是为了降低IO 操作，避免系统负载过高。change buffer将数据写入数据页的过程，叫做merge。
如果需要更新的数据页在内存中时，会直接更新数据页；如果数据不在内存中，会先将更新操作记入change buffer，当下一次读取数据页时，顺带merge到数据页中，change buffer也有定期merge策略。数据库正常关闭的过程中，也会触发merge。
对于唯一索引，更新前需要判断数据是否唯一（不能和表中数据重复），如果数据页在内存中，就可以直接判断并且更新，如果不在内存中，就需要去磁盘中读出来，判断一下是否唯一，是的话就更新。change buffer是用不到的。即使数据页不在内存中，还是要读出来。
change buffer 用的是 buffer pool 里的内存，因此不能无限增大。change buffer 的大小，可以通过参数 innodb_change_buffer_max_size 来动态设置。这个参数设置为 50 的时候，表示 change buffer 的大小最多只能占用 buffer pool 的 50%。
结论：唯一索引用不了change buffer，只有普通索引可以用。
八.如何给字符串加索引呢？
针对字符串的索引，一般会采用的是前缀索引，比如举个例子，有邮箱号列如QQ邮箱 975289275@qq.com为了，那邮箱号重复的地方比如@qq.com我们就可以忽略，只对前面的@qq.com得前面项加前缀索引，那么如何决定前缀索引的长度呢？可以通过以下命令计算出这个列有多少不同的值，然后取区分度最大的长度加索引。
select count(dinstinct left(email,length)) as l from table
其中length 可以是1，2，3，4，5，6......
一般遵循索引选择性原则，索引选择性原则指的是 不重复的索引值 和 据表记录总数的比值，比值越高，性能越好
前缀索引，遵循完整列的选择性 count(distinc left(emial,length))/count(*) as from table 这个比值越高，则性能越好
2.刚刚看到一个很不错的方法，使用伪hahs索引，就是将该字符串维护一个用hash函数处理的列，然后在该hash列上加索引即可，但是缺陷是要维护hash值
8.Hash索引的缺点
1、不能避免读取行
哈希索引只包含哈希值和行指针，而不存储字段值，所以不能使用索引中的值来避免读取行。不过，访问内存中的行的速度很快，所以大部分情况下这一点对性能的影响并不明显。
2、无法用于排序
哈希索引数据并不是按照索引值顺序存储的，所以也就无法用于排序。
3、无法使用部分索引列匹配查找
哈希索引也不支持部分索引列匹配查找，因为哈希索引始终是使用索引列的全部内容来计算哈希值的。例如，在数据列（A,B）上建立哈希索引，如果查询只有数据列A，则无法使用该索引。
4、只支持等值查找
哈希索引只支持等值比较查询，包括=、IN()、<=>（注意<>和<=>是不同的操作）。也不支持任何范围查询，例如WHERE price>100。
5、存在Hash冲突
访问哈希索引的数据非常快，除非有很多哈希冲突（不同的索引列值却有相同的哈希值）。当出现哈希冲突的时候，存储引擎必须遍历链表中所有的行指针，逐行进行比较，直到找到所有符合条件的行。
同时，当哈希冲突很多的时候，一些索引维护操作的代价也会很高。例如，如果在某个选择性很低（哈希冲突很多）的列上建立哈希索引，那么当从表中删除一行时，存储引擎需要遍历对应哈希值的链表中的每一行，找到并删除对应行的引用，冲突越多，代价越大。
9.B树的缺点
1.每个节点中既要存索引信息，又要存其对应的数据，如果数据很大，那么当树的体量很大时，每次读到内存中的树的信息就会不太够。
2.B树遍历整个树的过程和二叉树本质上是一样的，B树相对二叉树虽然提高了磁盘IO性能，但并没有解决遍历元素效率低下的问题。
3.范围查询的效率太低，没有B+树的高
