mysql 进阶
1、存储引擎
存储引擎就是存储数据、建立索引、更新/查询数据等技术的实现方式 。
1.1mysql体系结构：
连接层 ：主要完成一些类似于连接处理、授权认证、及相关的安全方案。
服务层 ：主要完成大多数核心服务功能、如SQL接口，并完成缓存的查询，SQL的分析和优化，是否使用索引，部分内置函数的执行。
引擎层 ：存储引擎真正的负责了MySQL中数据的存储和提取，服务器通过API和存储引擎进行通信。不同的存储引擎具有不同的功能，这样我们可以根据自己的需要，来选取合适的存储引擎。数据库中的索引是在存储引擎层实现的。
存储层 ：主要是将数据(如: redolog、undolog、数据、索引、二进制日志、错误日志、查询日志、慢查询日志等)存储在文件系统之上，并完成与存储引擎的交互。
1.2 存储引擎的介绍
存储引擎，也是一样，他是mysql数据库的核心，我们也需要在合适的场景选择合适的存储引擎。
==存储引擎就是存储数据、建立索引、更新/查询数据等技术的实现方式 。==存储引擎是基于表的，而不是基于库的，所以存储引擎也可被称为表类型。我们可以在创建表的时候，来指定选择的存储引擎，如果没有指定将自动选择默认的存储引擎。
建表指定存储引擎
CREATE TABLE 表名(
字段1 字段1类型 [ COMMENT 字段1注释 ] ,
......
) ENGINE = INNODB [ COMMENT 表注释 ] ;
1.3 存储引擎的特点
InnoDB是一种兼顾高可靠性和高性能的通用存储引擎，在 MySQL 5.5 之后，InnoDB是默认的。
特点 ：事务、行级锁、外键
文件 ：tablename.ibd：tablename代表的是表名，innoDB引擎的每张表都会对应这样一个表空间文件，存储该表的表结构（frm-早期的 、sdi-新版的）、数据和索引。
逻辑存储结构：
表空间、段、区、页、行
1.3.2 MyIsAM
是早起的默认存储引擎，不支持事务、外键、行锁。访问速度快，表锁
2、索引
索引 ：（index）是帮助mysql高效获取数据的数据结构（有序）。
优点 ：提高搜索效率 有序降低排序成本。 缺点 ： 占用空间 增删效率降低。
2.1 索引结构
B+TREE索引、Hash索引、R-tree（空间索引）、Full-text（全文索引）、B-TREE
2.1.1 B-TREE
5阶B-TREE，每个节点最多4个key，5个指针。
- 一旦节点存储的key数量到达5，就会裂变，中间元素向上分裂。
- 在B树中，非叶子节点和叶子节点都会存放数据。
2.1.2 B+TREE***
与B-TREE不同的地方
- 所有节点均能在叶子节点找到。
- 所有的数据只在叶子节点。
- 所有叶子节点组成单向链表。
- MySQL索引数据结构对经典的B+Tree进行了优化。在原B+Tree的基础上，增加一个指向相邻叶子节点的链表指针，就形成了带有顺序指针的B+Tree，提高区间访问的性能，利于排序。
2.1.3 联合索引下的B+TREE
设置联合索引的字段为a,b,c
select ... where a = 1
先根节点匹配a=1，在左树，再找到a=1，再去左树匹配，因为没有要求b的值，所以查到一条结果(1,1,4,5)由于叶子节点是链表，紧着匹配下一个节点，a=1，匹配成功继续返回，以此类推，最后返回2条结果
select ... where a=13 and b=12
先根节点匹配a=13，走156的间隙的指针到左树。继续匹配a=13，走1221的间隙的指针到右树，找到一个结果（13,12,4），紧接匹配b=12，成功。然后顺着链表，找到(13,16,1,6)，逐个匹配a,b，失败，以此类推。最后返回一条
2.1.4 其他
- Hash索引
- 检索数据复杂度是常数阶
- 但是，不支持排序，<,>，between等范围查找
- 二叉树
- 结构简单，容易实现
- 阶太少，数据量多时，检索深度过高
2.3 索引分类
2.3.1 索引分类
主键索引、唯一索引、常规索引、全文索引
| 分类 | 含义 | 特点 | 关键字 |
|---|---|---|---|
| 主键 | 针对于表中主键创建的索引 | 默认自动创建, 只能有一个 | PRIMARY |
| 唯一 | 避免同一个表中某数据列中的值重复 | 可以有多个 | UNIQUE |
| 常规 | 快速定位特定数据 | 可以有多个 | |
| 全文 | 全文索引查找的是文本中的关键词，而不是比较索引中的值 | 可以有多个 | FULLTEXT |
2.3.2聚集索引&二级索引
| 分类 | 含义 | 特点 |
|---|---|---|
| 聚集索引 | 将数据存储与索引放到了一块，索引结构的叶子节点保存了行数据 | 每张表必须有,而且只有一个 |
| 二级索引 | 将数据与索引分开存储，索引结构的叶子节点关联的是对应的主键 | 可以存在多个 |
- 聚集索引
- 存在主键，主键即聚集索引
- 不存在主键，将使用第一个UNIQUE索引。
- 都没有，则创建隐藏的rowid为聚集索引
- 二级索引
- 非聚集索引就是二级索引
- 非主属性可以创建二级索引
可以看出，聚集索引的叶子节点放的是一行的数据，用其他字段来查的时候，找到id值，最终都是要依靠聚集索引来找到最终数据，亦称之为回表查询。
2.4 索引语法
2.4.1 创建索引
create [UNIQUE|FULLTEXT] index index_name on table_name(col1,col2...)
- 默认是常规索引，当字段不唯一时，为联合索引
- UNIQUE：只能给唯一索引的字段创建索引
- FULLTEXT：全文索引
2.4.2 删除索引
drop index index_name on table_name
2.4.3 查看索引
show index from table_name
2.5 索引性能分析
2.5.1 explain
explain <select ...>
explain select * from user where id=1;
- id
- 查询的序号，包含一组数字，表示查询中执行select子句或操作表的顺序
两种情况
id相同，执行顺序从上往下
id不同，id值越大，优先级越高，越先执行
- 查询的序号，包含一组数字，表示查询中执行select子句或操作表的顺序
- select_type
- 表示 SELECT 的类型，常见的取值有
- SIMPLE（简单表，即不使用表连接 或者子查询）、
- PRIMARY（主查询，即外层的查询）、
- UNION（UNION 中的第二个或者后面的查询语句）、
- SUBQUERY（SELECT/WHERE之后包含了子查询）
- type
- 表示连接类型，由好到差
- system：表中仅有一行。
- const：通过索引一次就找到 （主键或者唯一索引） 。
- eq_ref：唯一性索引扫描，对于每个索引键，表中只有一条记录与之匹配。常见于唯一索引或者主键扫描。
- ref:非唯一性索引扫描，返回匹配某个单独值的所有行，本质上也是一种索引访问，它返回所有匹配某个单独值的行，可能会找多个符合条件的行，属于查找和扫描的混合体。
- range: 对索引进行范围检索。
- index: 物理文件全扫描，速度非常慢
- all：遍历全表以找到匹配的行。
- possible_keys
- 可能使用的索引
- key
- 实际用到的索引
- key_len
- 表示索引中使用的字节数，该列计算查询中使用的索引的长度在不损失精度的情况下，长度越短越好。
- ref
- 显示索引的哪一列被使用了。联合索引时作用可参考
- rows
- MySQL认为必须要执行查询的行数，在innodb引擎的表中，是一个估计值， 可能并不总是准确的
- Extra
- 包含不适合在其他列中显示，但是十分重要的额外信息
- Using filesort：说明mysql会对数据适用一个外部的索引排序。而不是按照表内的索引顺序进行读取。MySQL中无法利用索引完成排序操作称为“文件排序”
- Using temporary:使用了临时表保存中间结果，mysql在查询结果排序时使用临时表。常见于排序order by和分组查询group by。
- Using index:表示相应的select操作用使用覆盖索引，避免访问了表的数据行。如果同时出现using where，表名索引被用来执行索引键值的查找；如果没有同时出现using where，表名索引用来读取数据而非执行查询动作。
- Using where :表明使用where过滤
- using join buffer:使用了连接缓存
- impossible where:where子句的值总是false，不能用来获取任何元组
- select tables optimized away：在没有group by子句的情况下，基于索引优化Min、max操作或者对于MyISAM存储引擎优化count（*），不必等到执行阶段再进行计算，查询执行计划生成的阶段即完成优化。
- distinct：优化distinct操作，在找到第一匹配的元组后即停止找同样值的动作。
2.5.2 其他小技巧
\G #将查询出来的结果以列表显示
select ...\G
2.6 索引使用原则
2.6.1 最左前缀法则
联合索引，必须要遵守最左前缀法。
最左前缀法则指的是查询从索引的最左列开始，并且不跳过索引中的列。如果跳跃某一列，索引将会后续失效
具体最左的列是什么，是由索引创建时候决定的。如有索引
create index idx_user_name_age_sex table(name,age,sex)
正例：
select ... where name='Jack'
select ... where name='Jack' and age = '18'
select ... where name='Jack' and age = '18' and sex = '男'
select ... where age = '18' and sex = '男' and name='Jack' #不受顺序影响
反例：
select ... where name='Jack'
select ... where name='Jack' and age = '18'
select ... where name='Jack' and age = '18' and sex = '男'
select ... where age = '18' and sex = '男' and name='Jack' #不受顺序影响
2.6.2 范围查询
联合索引中，出现范围查询(>,<)，范围查询右侧的列索引失效。
select ... where name='Jack' and age > 1
优化（可能还是range级别）
select ... where name='Jack' and age >= 1
2.6.3 索引失效
不要在索引列上进行运算操作， 索引将失效。
反例：
select ... where substring(phone,10,2) = '13';
2.6.4 字符串不加引号
MySQL某字段是字符串类型，但是SQL中该字段没加引号，索引将失效
select ... where phone = 1300
SQL中非纯数字不加引号会报错！所以可能会在以字符串形式存储纯数字的字段中出现索引失效，因为得先不报错，才能执行SQL。
2.6.5 模糊查询
头部模糊查询，索引失效。仅仅尾部则不会。
有效 ： select ... from name like 'Ja%' 无效： select ... from name like '%ck'
2.6.7 or 连接条件
但凡or的一侧没有索引，则索引失效(若对age创建索引，则索引有效！)
select ... where id = 1 or age='18'
#id一定有索引，但是age无索引则也是白搭，索引完全失效！
2.6.8 数据分布影响
如果MySQL优化器评估使用索引比全表更慢，则不使用索引。
因为MySQL在查询时，会评估使用索引的效率与走全表扫描的效率，如果走全表扫描更快，则放弃索引，走全表扫描。 因为索引是用来索引少量数据的，如果通过索引查询返回大批量的数据，则还不如走全表扫描来的快，此时索引就会失效。
- 对于有序的列表，比如手机号，如果手机号共有100w个，phone>1开始查，那么走全表扫描。如果phone>50w，则可能走索引（具体得看优化器的阈值）
- is null 与is not null 当列的绝大部分都是null时，【is null】则会全表，【is not null】会使用索引，反之也成立。
2.6.9 SQL提示
SQL提示，是优化数据库的一个重要手段，简单来说，就是在SQL语句中加入一些人为的提示来达到优化操作的目的。
select * from table_name <use|ignore|force> index(index_name) where ...
1
- use ： 建议MySQL使用的索引，类似建议java调用gc，不一定会用
- ignore：忽略指定索引。
- force ：强制使用。
2.6.10 覆盖索引
尽量使用覆盖索引，减少select *。 那么什么是覆盖索引呢？ 覆盖索引是指 查询使用了索引，并且需要返回的列，在该索引中已经全部能够找到 。
比如
select name,phone... where name='Jack'
1
因为phone没有不在name所在的联合索引，因此phone需要回表查询，效率很低！
又如
select id,username,password where username='admin'
1
如果username与password建立了联合索引，那么就不需要回表查询，效率高！（此时的联合索引的数据域是id)
- explain的额外信息
| Extra | 含义 |
|---|---|
| Using where; Using Index | 查找使用了索引，但是需要的数据都在索引列中能找到，所以不需要回表查询数据 |
| Using index condition或者NULL | 查找使用了索引，但是需要回表查询数据 |
2.6.11 前缀索引
当字段类型为字符串（varchar，text，longtext等）时，有时候需要索引很长的字符串，这会让索引变得很大，查询时，浪费大量的磁盘IO， 影响查询效率。此时可以只将字符串的一部分前缀，建立索引，这样可以大大节约索引空间，从而提高索引效率。
- 创建语法
create index idx_name_n on table_name(column_name(n))
- n值确定
可以根据索引的选择性来决定，而选择性是指不重复的索引值（基数）和数据表的记录总数的比值，索引选择性越高则查询效率越高， 唯一索引的选择性是1，这是最好的索引选择性，性能也是最好的。
select count(distinct substring(email,1,5)) / count(*) from tb_user ;
选择性的值小于等于1，1是最好的即唯一索引。但要考虑索引使用空间，所以上面n=5跟n=6的选择性一样时，选n=5
2.6.12 单列索引与联合索引选择
单列索引：即一个索引只包含单个列。
联合索引：即一个索引包含了多个列 。
在业务场景中，如果存在多个查询条件，考虑针对于查询字段建立索引时，建议建立联合索引，而非单列索引。
另外，当单例索引与联合索引同时包含存在时，要use table_name(index_name)来建立MySQL使用联合索引。
create index on table(col1,col2)
create index on table(col2,col1)
是有区别的！要考虑最左匹配原则。
2.7 索引设计原则
2.7.1 什么时候建立
- 针对于数据量较大且查询比较频繁的表，建立索引。
- 针对于常作为查询条件（where）、排序（order by）、分组（group by）操作的字段建立索引。
2.7.2 建立什么索引
- 尽量选择区分度高的列作为索引，尽量建立唯一索引 （区分度越高，使用索引的效率越高）
- 字符串类型的字段的长度较长，建立前缀索引。
- 尽量使用联合索引，减少单列索引，查询时，联合索引很多时候可以覆盖索引，节省存储空间，避免回表，提高查询效率。
2.7.3 注意
- 如果索引列不能存储NULL值，请在创建表时使用NOT NULL约束它。（当优化器知道每列是否包含NULL值时，它可以更好地确定哪个索引最有效地用于查询，比如受数据分布影响的索引失效）
- 要控制索引的数量，索引并不是多多益善，索引越多，维护索引结构的代价也就越大，会影响增删改的效率。
837
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
