mysql复习

Mysql索引底层数据结构与算法

索引-是帮助mysql高效获取数据的一种数据结构

二叉树：二叉树对于单边增长的列没有效果，如以1作为开头，1，2，3，4，5，6

红黑树-二叉平衡树，会单方面比较大小做一次平衡，对于二叉树是优化了，但高度H会非常大

B-TREE:相对于红黑树来说，高度可控，每个节点存放多个数据，一个节点中也是按照大小排好序的，这样高度是比较小的，

B+TREE:非叶子节点不储存数据，只有叶子节点存数据，这样非叶子叶子节点能存储更多的数据，数据量越大，高度比B树更低，而且mysql可以长时间把非叶子节点数据长时间放入内存中，减少IO，叶子节点之间也是，对于mysql一次IO一个节点16K，而叶子节点的关联指针很好的支持了查询范围的全部值

hash索引：会对索引进行一次Hash运算，以确定索引储存范围，但不支持范围查询，仅仅支持in等，效果单一，而范围查询很多

引擎

myISAM：索引文件和数据是分离的

innoDB（引擎）:按照B+树结构存储数据，主键索引和具体数据是一起的，就是一个聚集索引，只有主键是按B+树的聚集索引，普通索引是没有具体数据的，需要重新在查询一遍主键俗称回表

问题：为什么innoDB建表必须使用主键，并且建议主键是整形并且自增的呢

因为如果我们不建主键，Mysql内部自己会建主键，而那个主键是我们无法控制的，为什么整形呢，因为整形更好的比大小，字符串之类的要转换 整形比，为啥要自增呢，主要是减少分裂

联合索引：

最左匹配：联合索引是复合索引，要按照最左一个一个匹配才满足这个元素，比如图里，要先满足name,在满足age，才算匹配，没有name，那肯定是不匹配的

Explain详解与索引最佳实践：

explain的列

id ：指一条sql里面各个查询运行的先后顺序，id的列越大，优先度就越高，两个相同，则排在前面的先运行，

select_type 查询的复杂程度（不重要）

table 指对应的表格

type：访问类型(非常重要)   依次从最优到最差分别为：system > const > eq_ref > ref > range > index > ALL

一般来说，得保证查询达到range级别，最好达到ref，当然在range一下就要优化了

system和const sysmet,通过主键或者唯一索引只有一条匹配，const就相当于查常量，所以都非常快

eq_ref 多张表通过主键索引直接关联的，查询指返回一条记录，查询也很快

ref 使用普通索引，或者唯一索引的部分前缀，查出多行匹配数据

range：范围扫描通常出现在 in(), between ,> ,<, >= 等操作中。使用一个索引来检索给定范围的行

index ：这种扫描全索引就能拿到结果，直接是从第一个索引的位置开始往后扫

all:是全表扫描，是从跟根节点逐一扫描，必然要优化的

posble_keys是可能用到了索引，这是运行前的预测，如果运行反而没用到，那说明全表扫描更快

key:显示生效的索引

key_len 指的使用索引的字节长度，通过key_len能够推测索引具体使用了那些列

key_len计算规则如下：

1. 字符串，char(n)和varchar(n)，5.0.3以后版本中，n均代表字符数，而不是字节数，如果是utf-8，一个数字或字母占1个字节，一个汉字占3个字节
   1. char(n)：如果存汉字长度就是 3n 字节
   2. varchar(n)：如果存汉字则长度是 3n + 2 字节，加的2字节用来存储字符串长度，因为varchar是变长字符串
2. 数值类型
   1. tinyint：1字节
   2. smallint：2字节
   3. int：4字节
   4. bigint：8字节　　
3. 时间类型　
   1. date：3字节
   2. timestamp：4字节
   3. datetime：8字节

 注意：如果字段允许为 NULL，需要1字节记录是否为 NULL

Extra列：

Using index：覆盖索引,不用回表

Using where： 查询的列未被索引覆盖  列如：explain select * from actor where name = 'a';，name没有索引，需要优化

Using temporary：mysql需要创建一张临时表来处理查询。出现这种情况一般是要进行优化的，

比如：

1. actor.name没有索引，此时创建了张临时表来distinct

mysql> explain select distinct name from actor;

2. film.name建立了idx_name索引，此时查询时extra是using index,没有用临时表

mysql> explain select distinct name from film;

Using filesort：将用外部排序而不是索引排序，数据较小时从内存排序，否则需要在磁盘完成排序。这种情况下一般也是要考虑使用索引来优化的。

1. actor.name未创建索引，会浏览actor整个表，保存排序关键字name和对应的id，然后排序name并检索行记录

mysql> explain select * from actor order by name;

2. film.name建立了idx_name索引,此时查询时extra是using index

mysql> explain select * from film order by name;

6）Select tables optimized away：使用某些聚合函数（比如 max、min）来访问存在索引的某个字段是

mysql> explain select min(id) from film;

防止索引失效注意事项

1 如果索引了多列，要遵守最左前缀法则。指的是查询从索引的最左前列开始并且不跳过索引中的列。

2.不在索引列上做任何操作（计算、函数、（自动or手动）类型转换），会导致索引失效而转向全表扫描

3.存储引擎不能使用索引中范围条件右边的列

4.尽量使用覆盖索引（只访问索引的查询（索引列包含查询列）），减少 select * 语句

5.mysql在使用不等于（！=或者<>），not in ，not exists 的时候无法使用索引会导致全表扫描

< 小于、 > 大于、 <=、>= 这些，mysql内部优化器会根据检索比例、表大小等多个因素整体评估是否使用索引

6.is null,is not null 一般情况下也无法使用索引

7.like以通配符开头（'%abc...'）mysql索引失效会变成全表扫描操作

a）使用覆盖索引，查询字段必须是建立覆盖索引字段

EXPLAIN SELECT name,age,position FROM employees WHERE name like '%Lei%';

8.字符串不加单引号索引失效

11.范围查询优化，查询范围太大也会有可能导致不走索引，可以分成多段去查

好玩的表格：

索引下推：这个值针对联合索引，像SELECT * FROM employees WHERE name like 'LiLei%'  AND age = 22 AND position ='manager'  这种情况只会走name字段索引，因为根据name字段过滤完，得到的索引行里的age和 position是无序的，无法很好的利用索引，但在mysql5.7，后面name,和age也会过滤看下，在回表，这个只针对联合索引