MySql 笔记

数据结构：B+TREE 

二叉树：顺序增长依次查询效率低

红黑树： 数据多了深度越深，效率自然低了

HASH： 查询条件限制

B-TREE：度（degree）-节段的数据存储个数，叶节点具有 相同的深度，叶节点的指针为空，节点的数据key从左到右递增排列，度有上限，查找数据时会受到内存与硬盘的交互限制

B+TREE：非叶子节点不存储数据，只存储key,可以增大度，叶子节不存储指针，叶子节点存储数据，顺序访问指针提高区间访问的性能，叶子节点有指针支持条件搜索；度一般会超过100，因此深度非常小（一般为3-5之间）

算法演示网站：Data Structure Visualization

索引引擎 默认：InnoDB

MyisAM引擎：（非聚集）索引文件和数据文件是分离的

InnoDB引擎：（聚集）索引文件和数据文件放在一起

性能调优：Explain

EXPLAIN SELECT (SELECT id FROM actor LIMIT 1) FROM film;

id

id 列的编号是 SELECT 的序列号，有几个 SELECT 就有几个 id，并且 id 的顺序是按 SELECT 出现的顺序增长的。MySQL 将 SELECT 查询分为简单查询(SIMPLE)和复杂查询(PRIMARY)。
复杂查询分为三类：简单子查询、派生表（FROM 语句中的子查询）、UNION 查询。
id 列越大执行优先级越高，id 相同则从上往下执行，id 为 NULL 最后执行

select_type

表示对应行是简单还是复杂的查询
SIMPLE：简单查询。查询不包含子查询和 UNION
PRIMARY：复杂查询中最外层的 SELECT
SUBQUERY：包含在 SELECT 中的子查询（不在 FROM 子句中）
DERIVED：包含在 FROM 子句中的子查询。MySQL 会将结果存放在一个临时表中，也称为派生表UNION：在 UNION 中的第二个和随后的 SELECT
UNION RESULT：从 UNION 临时表检索结果的 SELECT

table

这一列表示 explain 的一行正在访问哪个表。
当 from 子句中有子查询时，table 列是 <derivenN> 格式，表示当前查询依赖 id=N 的查询，
于是先执行 id=N 的查询。
当有 union 时，UNION RESULT 的 table 列的值为<union1,2>，1 和 2 表示参与 union 的
select 行 id。

type

这一列表示关联类型或访问类型，即 MySQL 决定如何查找表中的行，查找数据行记录的大概
范围。
依次从最优到最差分别为：system > const > eq_ref > ref > range > index > ALL
一般来说，得保证查询达到 range 级别，最好达到 ref

NULL：mysql 能够在优化阶段分解查询语句，在执行阶段用不着再访问表或索引。例如：在
索引列中选取最小值，可以单独查找索引来完成，不需要在执行时访问表

const, system：mysql 能对查询的某部分进行优化并将其转化成一个常量（可以看 show
warnings 的结果）。用于 primary key 或 unique key 的所有列与常数比较时，所以表最多
有一个匹配行，读取 1 次，速度比较快。system 是 const 的特例，表里只有一条元组匹配时
为 system

eq_ref：primary key 或 unique key 索引的所有部分被连接使用 ，最多只会返回一条符合条
件的记录。这可能是在 const 之外最好的联接类型了，简单的 select 查询不会出现这种
type。

ref：相比 eq_ref，不使用唯一索引，而是使用普通索引或者唯一性索引的部分前缀，索引
要和某个值相比较，可能会找到多个符合条件的行。

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

index：扫描全表索引，这通常比 ALL 快一些。（index 是从索引中读取的，而 all 是从硬盘中
读取）

ALL：即全表扫描，意味着 mysql 需要从头到尾去查找所需要的行。通常情况下这需要增加索
引来进行优化了

possible_keys

这一列显示查询可能使用哪些索引来查找。
explain 时可能出现 possible_keys 有列，而 key 显示 NULL 的情况，这种情况是因为表中数
据不多，mysql 认为索引对此查询帮助不大，选择了全表查询。
如果该列是 NULL，则没有相关的索引。在这种情况下，可以通过检查 where 子句看是否可以
创造一个适当的索引来提高查询性能

key

这一列显示 mysql 实际采用哪个索引来优化对该表的访问。
如果没有使用索引，则该列是 NULL。如果想强制 mysql 使用或忽视 possible_keys 列中的索
引，在查询中使用 force index、ignore index。

key_len

这一列显示了 mysql 在索引里使用的字节数，通过这个值可以算出具体使用了索引中的哪些列。

key_len 计算规则：
字符串
char(n)：n 字节长度
varchar(n)：2 字节存储字符串长度，如果是 utf-8，则长度 3n + 2
数值类型
tinyint：1 字节
smallint：2 字节
int：4 字节
bigint：8 字节
时间类型
date：3 字节
timestamp：4 字节
datetime：8 字节
如果字段允许为 NULL，需要 1 字节记录是否为 NULL
索引最大长度是 768 字节，当字符串过长时，mysql 会做一个类似左前缀索引的处理，将前半
部分的字符提取出来做索引。

ref

这一列显示了在 key 列记录的索引中，表查找值所用到的列或常量，常见的有：const（常
量），字段名（例：film.id）

rows

这一列是 mysql 估计要读取并检测的行数，注意这个不是结果集里的行数。

Extra

这一列展示的是额外信息。常见的重要值如下：
Using index：查询的列被索引覆盖，并且 where 筛选条件是索引的前导列，是性能高的
表现。一般是使用了覆盖索引(索引包含了所有查询的字段)。对于 innodb 来说，如果是辅助索
引性能会有不少提高

Using where：查询的列未被索引覆盖，where 筛选条件非索引的前导列

Using where Using index：查询的列被索引覆盖，并且 where 筛选条件是索引列之
一但是不是索引的前导列，意味着无法直接通过索引查找来查询到符合条件的数据

NULL：查询的列未被索引覆盖，并且 where 筛选条件是索引的前导列，意味着用到了索
“ ”引，但是部分字段未被索引覆盖，必须通过 回表 来实现，不是纯粹地用到了索引，也不
是完全没用到索引

Using index condition：与 Using where 类似，查询的列不完全被索引覆盖，where
条件中是一个前导列的范围；

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

Using filesort：mysql 会对结果使用一个外部索引排序，而不是按索引次序从表里读取
行。此时 mysql 会根据联接类型浏览所有符合条件的记录，并保存排序关键字和行指针，然后
排序关键字并按顺序检索行信息。这种情况下一般也是要考虑使用索引来优化的。

索引实践：

1. 全值匹配 （如 联合索引，index(a,b,c)  全部用上）

2.最佳左前缀法则 （如 联合索引，index(a,b,c)  按照顺序 a,b,c全部用上）

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

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

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

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

6.mysql 在使用不等于（!= 或者 <>）的时候无法使用索引会导致全表扫描

7.is null,is not null 也无法使用索引

8.like 以通配符开头（'$abc...'）mysql 索引失效会变成全表扫描操作
    问题：解决 like'%字符串%'索引不被使用的方法？
         SELECT name form tb_user where name like '%Lei%';

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

10.少用or或in,用它查询时，非主要字段的索引会失效，主键索引有时生效，有时不生效，根据数量有关，具体还得看mysql的查询优化结果