目录
一 . 存储引擎
1.1 存储引擎的定义
二 . 索引的结构
2.1 索引的定义与引擎对索引的支持
2.2 hash索引与B+ tree索引的结构
2.3 索引的分类
2.4 索引的语法
三. SQL语句的性能分析
3.1 CRUD次数查询
3.2 慢查询日志分析
3.3 profiles全语句分析
3.4 EXPLAIN/DES性能分析
四 索引的使用规则
4.1 最左前缀法则
4.2 索引失效的原因
4.3 SQL提示
4.4 覆盖索引
4.5 前缀索引
4.6 单列索引&联合索引
一.存储引擎
存储引擎：存储数据，建立索引，更新/查询数据等技术的实现方式。
存储引擎基于表而不是基于库，所以存储引擎也叫做表类型，MySQL的常见表类型就有InnoDB和MyISAM。
二 . 索引的结构
2.1 索引的定义与引擎对索引的支持
索引：帮助MySQL高效获取数据的有序数据结构。（无索引时查找数据进行全表扫描）
索引优点：提高数据检索速度（select），降低数据库IO成本，降低数据排序成本，降低CPU消耗
索引缺点：降低了（insert，uodate，delete）效率，因为索引列也占用空间
对于存储引擎InnoDB，MyISAM，Memory引擎对常用四种索引的支持情况：
| 索引 | InnoDB | MyISAM | Memory |
| B+ tree索引（B+树索引） | 支持 | 支持 | 支持 |
| Hash索引（哈希索引） | 不支持 | 不支持 | 支持 |
| R-tree索引（空间索引） | 不支持 | 支持 | 不支持 |
| Full-text（全文索引） | 5.6版本后支持 | 支持 | 不支持 |
2.2 hash索引与B+ tree索引的结构
B+ tree索引
二叉树到b+树的结构变化：普通二叉树 --> 特殊二叉树（红黑树）--> B-tree --> Mysql的B+Tree
1.普通二叉树：顺序插入形成链表，查询性能大大降低，层级深导致检索速度慢
2.红黑树：解决了舒徐插入形成链表的问题，但大数据量下层级依旧较深，检索速度慢
3.B树（多路平衡查找树）：（最大度数为n，则每个节点存储n-1个key，n个指针）（度数：一个节点的子节点个数）当数据插入时当key数已经达到n-1时key向上分裂。B树由于每层的节点可以存储多个key，比起红黑树层级又小一点了。
4.（非MySQL索引数据结构）B+树：B树的所有节点都可以存储数据，而B+树只有叶子节点存储数据且叶子节点形成单向链表，所以当新增索引时若key已经为n-1，此时会导致树向上分裂，而在叶子节点中的原有索引除了在向上移时其在叶子节点仍有相同索引存在数据，所以叶子节点的节点也会进行分裂。
5.（MySQL索引数据结构）B+Tree：在上面的B+Tree基础上，增加一个指向相邻叶子节点的链表指针，叶子节点形成带顺序的双向循环链表，提高了区间访问性能
哈希索引
哈希索引使用hash算法将键值换算成新的hash值，映射到对应的槽位上（某行数据的物理指针），存储在hash表中，通过指针可以访问数据行，但是不同键值可能计算处哈希值映射的槽位是相同的，此时就需要在对应位置添加链表处理
哈希索引优点：查询效率高，一次检索就够了，通常高于B+tree索引
哈希索引缺点：只能进行（=，in），不支持范围查询，无法利用索引进行排序
所以支持hash索引的是Memory引擎，InnoDB引擎具有自适应hash功能（将B+tree索引自动变化为哈希索引）
总结：
innoDB引擎选择B+tree索引结构而不是B-tree索引和红黑树索引的原因：
1.相对于二叉树红黑树，层级更少，搜索效率更高
2.B-tree所有节点存储数据，一页中的键值存储减少，指针减少导致树的高度增加，性能降低
2.3 索引的分类
| 分类 | 含义 | 特点 | 关键字 |
| 主键索引 | 针对于表中主键创建的索引 | 默认自动创建，只有一个 | PRIMARY |
| 唯一索引 | 避免同一个表中某数据列中的值重复 | 可以有多个 | UNIQUE |
| 常规索引 | 快速定位特定数据 | 可以有多个 | |
| 全文索引 | 全文索引查找的是文本中的关键次，而不是比较索引中的值 | 可以有多个 | FULLTEXT |
| 分类 | 含义 | 特点 |
| 聚集索引（Clustered Index） | 将数据存储与索引放到一块，索引结构的叶子节点保存了行数据 | 必须有，而且只有一个 |
| 二级索引（Secondary Index） | 将数据与索引分开存储，索引结构的叶子节点关联的是对应的主键 | 可以存在多个 |
二级索引记录主键，主键对应的聚集索引记录行数据，查找数据先从二级索引找到主键，再在B+tree树从根节点开始往下查找对应的聚集索引数据。
注：索引的key数为指针数-1，如【10，20】分别三个指针指向,<10,>20,10和20之间
2.4 索引的语法
4.1创建索引
CREATE [UNIQUE|FULLTEXT] index_name ON table_name (index_col_name,...);
4.2查看索引
SHOW INDEX FROM table_name;
4.3删除索引
DROP INDEX index_name ON table_name;
三. SQL语句的性能分析
3.1次数查询
show global status like 'Com_______'; --查询CRUD等的次数
3.2慢查询
show variables like 'slow_query_log'; --查询是否开启慢查询日志
慢查询日志：记录了所有执行事件超过指定参数（long_query_time）的SQL语句的日志，
可在/etc/my.cnf 配置中开启慢查询日志。在/var/lib/mysql/user_name-slow.log查看。
slow_query_log=1 #开启
long_query_time=2 #SQL视为慢查询的超时时间
3.3 profiles
慢查询只能查看超时的SQL语句，而show profiles可以查看所有SQL语句的耗时时间。
select @@have_profiling; --查看数据库是否支持profiles
select @@profiling; --查看数据库是否打开profiles
set profiling=1; --若为打开，开启profiles
show profiles; --每一条SQL耗时时间
show profile for query query_id; --指定语句各个阶段耗时情况
show profile cpu for query query_id --指定语句CPU使用情况
3.4 explain
EXPLAIN或者DESC命令获取MYSQL如何执行语句的信息，包括表的连接和连接顺序
EXPLAIN SELECT 字段列表 FROM 表明 WHERE 条件;
| 字段 | 含义 |
| Id | 执行select子句或操作表的顺序，id越大越先执行，相同则顺序从上到下 |
| select_type | select类型，分为SIMPLE(简单表)，PRIMARY（外层查询），UNION（union第二个及以上的查询语句）,SUBQUERY（select/where包含子查询的） |
| type |
连接性能，由好到坏分为 NULL , system , const , eq_ref , ref , range , index , all (尽量不要为all，all为全表扫描) |
| possible_key | 这张表可能应用的索引，一个或多个 |
四 索引的使用规则
4.1 最左前缀法则
对于联合索引，查询从索引的最左列开始，并且不能跳过索引中的列，否则跳过的索引失效
最左列索引存在即可，顺序可以随意写，但不存在最左列就会全部索引失效
4.2 索引失效的几种原因
1.对索引进行运算如使用substring函数
2.字符串索引未加引号‘’
3.索引可以使用后缀模糊查询但不可以使用前缀模糊查询
4.or连接后有一侧没用索引都不会用索引
5.范围查询全表扫描比索引更快索引也会失效
4.3 SQL提示
可以分别使用use，ignore，force来建议，忽视，强制数据库使用索引
explain select * from user use index(index_user)where password='123456';
4.4 覆盖索引
需要返回的列在该索引中已经可以全部找到，不然从二级索引得到的id在聚合索引进行回表查询
4.5 前缀索引
字符串取一部分前缀作为索引降低磁盘IO的浪费
语法：
create index idx_xxx on table_name(column(n));
前缀长度可以根据索引的选择性决定：
select count(distinct emain)/count(*) from tb_user;
select count(distinct substring(emain,1,5))/count(*) from tb_user;
扫一扫
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
