MySQL系列【一】：索引底层数据结构和优化

索引的本质就是一种排好序的数据结构

一、索引类型

全文索引：full text 要求最小长度还有语言分词(中文建议ES)

1、hash索引

单条数据(等值查询)查询快，但是范围查找效率比较低，另外数据量大也可能会发生Hash碰撞

2、B+树

根节点只存储索引，只有叶子节点存储数据。叶子节点是丛左往右递增的单链表(Mysql对此做了优化，改成了双向链表，这也是能够快速进行范围查找的原因)

特点：
1、关键字数等于度(节点拥有的子数量) // 如果根节点中有1000个键值对，则有1000个分叉的叶子节点

2、只在叶子节点存储数据

3、叶子节点之间形成双向指针

2.1、B+树存储

如果第一层存储了1000个键值+子节点指针，那么第二层就是1000*1000=100w路，其叶子节点就能存储千万级数据，一共树深为2(0、1、2)，一共只需要三次IO
将子节点最小的索引做冗余放在父节点

2.2、聚簇索引和二级索引

一个索引只会对应一棵B+树！

聚簇索引取值：
1、主键值（联合主键也是聚集索引）
2、不包含空值的唯一索引
3、隐藏列row_id作为聚簇索引

二级索引：也叫非聚簇索引，二级索引树存储的是二级索引和聚簇索引值
为何Innodb的二级索引的叶子节点存储的是主键索引，而非直接存储数据？

• ① 节省存储空间(主要目的)
• ② 保证数据的一致性，无需担心主键索引和非主键索引叶子节点数据不一致

联合索引：最左前缀，比如(name, age, position)：会先按照name进行排序，name相同的情况下再对age排序，age又相同的情况下最后对position进行排序

对于where name='xh' and age > 18 and position = 'dev' 这种条件查询，position属性是不会走索引的！
因为索引需要满足顺序性当age是个范围，那position只能在name='xh' 和 age > 18 的范围内全局扫描

2.3、索引建立原则

1、用于where 判断、order排序、join的(on)、group by字段上建立索引
2、索引个数不要过多，误区：在所有字段上建立索引。新增索引是有代价的

• ① 空间代价：占用过多存储空间
• ② 增删改导致树的分裂、合并

3、离散度越高越好，离散度低的字段不要建索引
4、频繁更新的值不要作为索引
5、不建议无序的值(例如身份证、UUID)做索引

• 每次新增、更新删除都会进行索引树的重排序

6、 复合索引把散列性高、常用的值放前面（满足最左匹配）

• 查询条件中的字段， 必须从第一个字段开始，且第一个字段必须为最常用查询字段！！
  案例分析：range分区榜单记录排行表，记录不同时间段的排行榜单数据，其中有个联合索引是(业务id，日志创建时间)，由于大部分时间都是通过创建时间去做分区和排序筛选，所以导致大量sql查询无法走索引

7、索引列长度尽可能小，过长的字段，建立前缀索引（key index (content(6))）// 用前6个字符做索引

• 阿里规范：对于varchar类型，最好选择前20位左前缀为索引。这里20位仅供参考，实际可通过select count(索引列(位数)) / count(*)来计算，得到的值越趋近于1表示离散度越高！找增长率最高点的位数截取即可，比如前11-15位分别为：0.5、0.8、0.9、0.91、0.911，此时选择0.91的这个14位即可(当然还需要考虑数据量大小)。

2.4、什么时候索引失效

1、索引列参与计算
2、or查询有一边没有索引
3、不满足最左前缀
4、字符串不加引号，出现隐式转换
5、mysql优化器觉得全表扫描比走索引快，可通过force index强制走索引
6、负向查询(<>、!=、not in)可能会导致索引失效

• 具体结果不一定，根据优化器基于cost的原则计算

7、总数据量不大的场景以及被查询出来的数据超过一定比例的时候（譬如表里有1000万条记录，一条SQL查的结果有600万），使用in和or <索引字段>
8、联合索引最左前缀的范围查询，索引可能失效

3、索引结构比较

AVL(平衡二叉树)：解决了顺序插入单向链表成为斜树，不平衡的问题
B树(多路平衡查找树)：解决了AVL只有两路，带来浪费存储空间，树的深度过高，查找IO次数过多的问题
B+树：所有数据只存叶子节点，叶子节点之间双向指针形成有序链表，减少了树深和IO次数

二、执行计划

可以通过Explain sql + show warings 查看Mysql的执行计划
这里针对Explain参数做一个简单总结

1、id

此列代表sql的执行顺序，从大到小排列（若id相同，则自上而下顺序执行），id越大优先级越高

2、slect_type

此列表示对应行是简单还是复杂查询
① simple：简单的单表查询，不包含子查询和union
② primary：复杂查询最外层的select
③ subquery：在select中嵌套的select查询
④ derived：在from中嵌套的select查询
比如：select (select 1 from actor where id=1) from (select … where id=1) t，最外层的第一个select对应primary，第二个select对应了subquery，第三个select对应了derived

3、table

此列代表的是表名

4、 type

此列代表关联或访问类型，即mysql查找数据行的大概范围
① system：from查询的表中有且仅有一条数据(一般为系统表)
② const：根据条件能够检索到唯一一条数据(走唯一索引或主键)
③ eq-ref：主键或唯一索引关联查询，最多返回一条符合条件的记录
④ ref： 相较eq-ref，使用普通索引或联合唯一索引的部分前缀
⑤ range：使用索引进行范围查找
⑥ index：扫描全索引获取结果（从二级索引树叶子节点全索引扫描）
⑦ All：全表扫描（从聚簇索引的叶子节点遍历扫描）
这里eq-ref只针对关联查询，而ref可以是单表也可以是关联查询；对于①-④的查询效率都是比较高的，而对于⑤-⑦需要考虑优化

这里面还有个特殊的类型index_merge(索引合并)：通常情况下，一个select查询语句在执行过程中最多只会使用一个二级索引，即使where条件中包含多个二级索引。但在某些特定情况下可能会走索引合并(本质就是取聚簇索引的一个去重集合)：

• 等值匹配(主键可以是范围查询)：多个索引列都是等值匹配；若是联合索引，则联合索引中的每个列都必须等值匹配

索引合并分为两种：一种是交集合并(多个索引间 and 连接)，一种是并集合并(多个索引间 or 连接)。对于最终是否走索引合并，还得取决于成本计算，如果当单个索引筛选出的聚集索引范围过大，而多个索引通过去重后可以大大减少聚簇索引的范围，才会使用索引合并来减少回表次数！

5、key_len

此列表示索引长度，可以通过该属性了解用到了联合索引中的哪些字段

6、ref

该列表示表查找值所用到的列或常量，常见的有：
① const：常量1，对应where id=1这种
② 字段名：a.id，对应a join b on a.id = b.id 这种

7、rows

此列代表mysql执行计划预估可能扫描的行数，扫描行数少不一定cost就低！但扫描行数和返回结果数比例过大就一定有问题！

8、Extra

此列是一些额外信息，常见的比如：
① using index：索引覆盖，无需回表（只针对二级索引）
order by走索引也需要满足最左前缀！满足两种情况会使用索引覆盖：

• order by语句使用索引最左列
• 使用where子句和order by子句条件列组合满足索引最左前缀，比如index(name, age)，where name=‘doge’ order by age

② using where(需优化)：使用where进行条件查询，一般有两层意思

• 表示通过索引访问时，需要回表访问数据（若执行计划显示走了索引，extra显示using where，那么执行效率不会很好，代表索引访问成本主要在回表，可考虑索引覆盖优化）
• 过滤条件发生在server层，而非存储引擎层

③using index condition：索引下推，主要是针对联合索引，比如查询条件满足最左前缀第一列但其他列并不满足最左前缀，导致后面索引未生效。此时会去从索引层进行index filter筛选（先在存储引擎端的二级索引树中过滤，然后回表去查询叶子节点）
表示将过滤下压到存储层执行，防止server层过滤过多数据

这里对where中过滤条件的处理，根据索引使用情况分成了三种：index key、index filter和table filter:

• index key：用于确定sql查询在索引中的连续范围(起始范围+结束范围)的查询条件，由于一个范围包含起始和终止，因此index key也被拆分为index first key和index last key分别用于定位索引查找的起始、终止条件
• index filter：在使用index key确定了起始和结束范围后，在此范围内还有些不符合where条件的，若这些条件可以用索引进行过滤，那么就是index filter
• table filter：where条件不能使用索引进行处理的，只能访问table进行条件过滤

Mysql 5.6之后，index filter和table filter分离，index filter下降到innodb的索引层进行过滤，减少了回表与返回Mysql Server层的记录交互开销，提高sql执行效率，所以所谓的ICP(Index Condition Pushdown)技术，其实就是index filter
若走table filter，会将index first key - index last key范围内的索引记录，回表读取完整数据，然后返回给Server层进行过滤！

比如index(name,age)，where name like 'Fake%' and age=30，5.6之前只会去查询符合name like 'Fake%' 的聚簇索引，接着回表查询该索引范围的所有数据，最后加载到Server层进行过滤
5.6之后，通过ICP技术，在查询符合name like 'Fake%' 的条件后，还会去索引中判断age=30的索引集，减少了回表和与Server层的数据交互

又比如联合索引(name,phone)，第二个字段未满足左前缀
select * from t where name=“xh” and phone like ‘%7520’;
查询过程：

1、先根据name查询二级索引数据
2、再从二级索引筛选出phone中以以7520结尾的索引(避免直接从叶子节点从大量数据中筛选，影响性能)
3、最后回表到主键索引上查询全部符合条件的数据，返回给server层

④ using temporary： 表示语句执行过程中使用了临时表，比如用到order by、group by、distinct和union等。数据不能直接返回给用户，此时就需要临时表来缓存数据，需要关注缓存数据的rows
可以通过走索引来消除临时表
不带order by，innodb默认会根据主键从小到大排序
⑤ using filesort：文件重排序，扫描的包含数据的聚集索引文件
针对order by不走索引，filesort和index的区别就是：
index ：通过有序索引顺序扫描直接返回有序数据，不需要额外的排序
filesort：通过对返回数据进行排序，filesort 并不代表通过磁盘文件排序，而是说明进行了一个排序操作，至于排序操作是否使用了磁盘文件或临时表等，则取决于MySQL服务器对排序参数的设置和需要排序数据的大小
filesort文件排序方式：

• 单路排序：一次性取出满足条件的所有数据，放在sortBuffer(线程私有)内存中排序（内存开销大，sortBuffer默认是1M，不够用则放在临时磁盘文件中，然后加载到内存中排序）
• 双路排序(回表排序)：取出相应排序字段和可以直接定位行数据的行id放在sortBuffer内存中排序，之后再去回表查询数据行
  单路是整个数据行排序，双路是按关键字排序后再去回表，内存占用小

三、范式化设计

第一范式：每一列属性都是不可分割的，保证列的原子性(比如家庭地址，又可以分割成省市区)
第二范式(基于第一范式)：通过表的主关键字可以定位一条数据(避免联合主键)
第三范式(基于第二范式)：要求数据库中不包含已在其他表中包含的非主关键字信息
满足范式三必须满足范式二，满足二又必须满足一

分库分表做一些字段冗余其实就违反了第三范式，说到这里，我们引入了反范式设计！

范式化设计优点是表数据量小，单表查询性能高。缺点就是复杂业务存在多表关联
反范式化设计反过来，优点是字段冗余关联查询少，缺点是表字段多，单表性能低

四、Mysql查询优化

首先我们需要知道Mysql数据类型主要包含：整型、小数、字符、日期、文本和二进制

1、优化方向

性能参数调整（一般是DBA做的，暂不考虑）
从开发侧考虑：根据阿里调优金字塔，从硬件、表结构和存储引擎以及Sql、架构自上而下成本越来越低，但收益却越来越明显，随之难度也更高
这里架构调优放在最下层，我理解合理的架构设计本身就能节省很多的成本，甚至无需再去做mysql或硬件调优。从开发者角度，还是优先从业务侧和对Mysql进行调优

再细粒度一点，可以进一步进行拆分

参考：MySQL性能调优“金字塔”

2.1、硬件和OS调优

硬件层面无非就是加机器、扩内存、使用SSD等等，Mysql无法横向扩容，只能加从库(读写分离)或者做分库
OS层面就是一些系统参数的配置：比如服务端最大连接数、客户端连接池、buffer_pool、sort_buffer缓存区等

2.2、表结构和存储引擎优化

表结构：

(1)、字符型固定长度优先使用char(存储到数据库长度不足会空格补齐，取出来时会去掉空格)，变长字符串varchar会额外占用1-2字节来存储额外的内部结构数据，若设置的`字符长度小于等于255，那么占用1字节`，如果设置的`长度大于255，那么占用2字节`
对于text和blog字段，若sql查询不常用，建议使用一张单独表，通过唯一索引进行关联
(2)、非空值不要设置default null，额外占用1个字节，如果业务要求需要有空值，我们尽量用实际的默认值（比如int默认为0）
(3)、数据结构是否合理
字段多的表分解成多表，将使用频率低的字段分离出来，减少每次查询IO的量
联合查询比较频繁的表，考虑增加中间表，做字段冗余，多表改成查询单表(避免为了一两个字段去做联表查询)，减少查询IO次数

存储引擎优化就是根据合适的场景选择合适的存储引擎

2.3、sql语句优化

第一步先通过Explain查看执行计划（主要参数：type、possible key、key、key_len 、Extra ）

1、type连接类型
    system：可遇不可求，可以忽略
	const：表最多有一个匹配行，一定走主键索引或者唯一索引
	eq_ref：join,被驱动表关联唯一性索引
    ref：join,被驱动表关联非唯一性索引
    range：索引 范围查询
    Index：full index scan
    All：全表扫描
2、possible key 可能会用到的索引
   key 可能会用到的索引 （若possible key为空，Key可能会走索引的场景：索引覆盖、索引下推）
3、key_len 索引长度，越小越好，优先选取数据类型小的字段作为索引字段，节省每一页可以放的索引数
4、Extra 额外信息
   	using index：索引覆盖
   	using where：在server层进行过滤
   	using Index Condition（默认开启）：索引下推
   	using filesort：不能直接用索引排序
   	using temporary：临时表存储 // 优化：为字段建立索引

优化思路：
(1)、是否走索引
若没有索引则考虑去新增索引
若有索引，则找出不走索引的原因，比如like查询或联合索引没有最左匹配

(2)、针对关联查询优化：
① 首先阿里编程规范建议关联表不得超过3张，避免多表笛卡儿积过大
② 复杂的sql查询做分解和优化
比如联合查询，优先把数据量小的表作为驱动表(小表驱动大表，即查询次数取决于驱动表结果集的记录数)，或者将一条查询语句拆分成多条，将一些关联筛选操作可以放到Java内存中处理

• 对于inner join，mysql执行器会判断数据小的做驱动表(可通过straight_join强制使用左侧表做驱动表，避免执行器判断错误)
• 对于left join，左表为驱动表(先对左表全表扫描，然后一条一条的到右表去查询，右连接同理。)
• 对于right join，右表为驱动表

PS：保证关联表的字符集相同

(3)、order by引起的filesort和temporary
filesort：通过将查询sql的索引和order by条件字段做联合索引
temporary：order by 一定要对驱动表进行排序，否则就会产生临时表
(4)、优化分页查询
比如要筛选出第10001条数据，limit 10000,1会先查询出10001条数据并舍弃掉前面10000条数据
考虑如下优化：
将查询的列都建立索引，避免回表操作
通过主键查询索引表先过滤掉前10000行数据，然后再去叶子节点查询第10001行的数据
通过关联延迟，先索引覆盖查询出需要的主键，再通过主键关联去查询需要的数据
另外对于一次性大批量数据查询，也需要进行批量查询分而治之来优化，根据互联网经验值，每次查询数据应控制在5000~10000条
(5)、对count函数选择：

• 当字段有索引：count(*) ≈ count(1) > count(二级索引) > count(主键) （因为二级索引比聚集索引小得多，效率自然更高）
• 当字段无索引：count(主键) > count(二级索引)

这里count(*)会统计值为Null的行，而count(索引)不会统计列名为Null的行！

2.4、架构

(1)、读多写少，做基于主从复制的读写分离
(2)、分库分表
(3)、对于多表关联或者耗时的查询可以考虑放到缓存
(4)、限流降级
(5)、分流

3、慢查询问题定位

查询Sql语句耗时之前，需要关掉缓存，再去执行select：

set global query_cache_size=0;
set gloabl query_cache_type=0;

1、开启mysql慢查询slow_query_log，定义慢查询的时间long_query_time(通常设置为500ms)
2、分析慢查询的日志（默认存放data目录下，借助慢查询日志分析工具mysqldumpslow进行分析，打开优化器追踪记录optimizer_trace，查看具体的执行计划）
慢查询日志主要包含这几个参数

• query_time：sql执行时间
• lock_time：数据锁定时间
• rows_sent：sql执行返回给客户端的数据总行数
• rows_examined：mysql执行计划扫描的总行数

这里需要关注的一个是执行时间、锁定时间，还有就是扫描行数和返回行数的比率(比率过大就需要进行索引优化)

3、若非sql问题导致的慢查询，我们接着分析：通过show profile去分析mysql线程在哪个阶段花费了过多时间，是数据发送还是执行计划…因为某些时候并不一定是sql本身的问题

4、sql关键字以及常用函数

1、整型
整型建表时括号中的值代表的是显示宽度，比如int(5)代表的是前面补0的个数(00001)，且只有字段设置UNSIGNED ZEROFILL时生效

2、ON DUPLICATE KEY UPDATE

insert into t (..) values (..) ON DUPLICATE KEY UPDATE xx=xx

有则更新，无则插入；根据主键和唯一索引判断，主键冲突则执行后面的更新，但更新的字段不能包含主键或唯一索引
3、sum求和函数
sum函数是和group by联合使用的，一般有两种求和方式
普通求和（字符型需要进行数据类型转换）
条件求和：比如说有负数，select sum(if(value>0,value,0)) … group by xx
4、count
count(if(type=1,true,null))true则计数，null就不计数

select 
count(case when 属性 in (1,2) then 1 else 0 end) as sum1,
count(case when  属性 in (3) then 1 else 0 end) as sum2,
sum(case when  属性 in (4,5) then 1 else 0 end) as sum3

SELECT
count(if(type=1,true,null)) AS sum1,
count(if(type=2,true,null)) AS sum2

5、substring_index和substring
substring(val,1,3)：1是开始位置，3是截取的长度
substring_index(val,’,’,1)：中间逗号是分割的依据，后面数字>0表示从左分割，<0表示从右分割，数字大小就表示分割的量

• 支持嵌套，substring_index(substring_index(…),…,…)

6、exists和in区别
exists走的是子查询中的索引(要不返回符合条件的所有数据，要不返回空)
in走的是父查询的索引

select * from A where id in(select..);
select * from A where exists (select..);

这里in函数是先被执行的；而exists函数是后被执行的，exists(subquery)函数只返回true和false

7、时间类型

• data类型： 3个字节 YYYY-MM-DD 只精确到日
• datatime类型： 8个字节 YYYY-MM-DD HH:MM:SS 精确到时分秒
• timestamp类型：4个字节 YYYYMMDD hhmmss 精确到时分秒

对于需要精确到时分秒的日期格式如何选择？
建议使用datatime，有两个原因：
① timestamp只能存储最大不超过20380119…而datatime可以到9999-12-31…
②timestamp只存储UTC时间戳，它会根据系统时区修改而自动更新，可能会出现前后时间不一致

五、总结和思考

1、Mysql索引有哪些数据类型，及其特点？

• hash(内部使用，无法自己去新建)：可以快速进行精准查询，但是不支持范围查询且会产生hash冲突
• B+树：把非叶子节点做了冗余，数据只存储在叶子节点，且叶子节点间双向指针形成有序链表，大大减少了大数据量的树深和IO查找次数

2、为何推荐整数自增主键？

• ① 整型在树结构中比较大小效率比字符串类型高(字符串需要转Ascii码)
• ② 整型相比字符串空间占用率更小，一个页缓存可以存更多索引
• ③ 之所以使用自增索引，是为了避免磁盘页的分裂和树的重平衡

3、为什么说索引会降低插入、删除、修改等维护任务的速度？？

• 由于innodb在存储索引的时候，底层用的数据结构是B+树，B+树是一颗平衡树，如果我们对这颗树增删改的话，那肯定会引起树节点的频繁分裂和合并，导致索引会降低增删改的速度

4、三星索引

• ① 离散型高
• ② 索引列的数据顺序和排序顺序一致(order by)
• ③ 索引列中包含查询所需的全部列(索引覆盖)

5、都知道索引最左前缀，那后缀索引该如何设计？
比如根据邮箱后缀进行条件筛选，可通过代码层做反转入库，以相反的顺序存储到MySQL