mysql总结

事务并发问题

脏读：一个事务读取到了另一个事务修改但未提交的数据
不可重复读：同一个事务中先后两次都去相同记录但是数据不同
幻读：同一个事务中先后读取数据的行数不同

解决事务并发问题

使用事务隔离级别
读未提交：都不能解决
读已提交：解决脏读
可重复读：解决脏读和不可重复读（mysql默认使用的隔离级别）
串行化：都已解决

show engines;命令可查看当前数据库支持的存储引擎

Innodb特点：支持事务、支持行级锁提高并发、支持外键保证数据完整性和正确性
5.5版本后默认存储引擎
每张表都有一个idb文件（包括表结构、数据、索引）
存储分为：表空间 -> 段 -> 区 -> 页 -> 行
一个区固定为1M，一个页固定为16K，每行中包括事务id、指针和每个字段

MyISAM特点：不支持事务、不支持外键、支持表锁不支持行锁、访问速度快
5.5版本之前默认存储引擎
每张表都有三个文件，sdi是表结构信息、MYD是表数据、MYI是索引

Memory特点：表数据都存储在内存中，所以只能将这些表作为临时表或者内存；hash索引（默认）
每张表只有一个文件sdi表结构

索引是帮助mysql高效获取数据的有序的数据结构。
数据库系统除了维护数据外还要维护这些数据结构到数据的引用。

索引结构

其在存储引擎层实现，不同的存储引擎有不同的结构
（1）B+Tree索引：最常见的索引类型，大部分引擎都支持
1）二叉树：每个节点最多有两个子节点且左子节点的值比父节点小，右子节点的值比父节点大；缺点：若顺序插入则会形成单链条，若数据量大则层次太深查找较慢
2）红黑树：类似二叉树，节点是一层黑一层红，根节点永远是黑色，但其是自平衡的，但是数据量大时则层次太深查找较慢
3）B-Tree：多路平衡查找树，每个节点可以存储多个数据，且每个节点可以存在多个子节点，同时父节点有指向子节点的引用（若子节点个数为5即5阶，则每个节点数据个数为4，指针为5）
裂变方式：中间元素向上裂变（所有节点均存储真正的数据和其子节点的引用（如果有子节点））
4）B+Tree：是B-Tree的变种，与B-Tree不同的是所有的数据都会出现在叶子节点中且叶子节点形成了单向链表，而非叶子节点只是用于索引数据的作用不存储真实数据
在mysql中对原有的B+Tree进行了优化，在原基础上增加一个指向相邻叶子节点的链表指针（双向），就形成了带有顺序指针的B+Tree提高了区间访问的性能。
（2）Hash索引：底层数据结构是通过哈希表实现的，只有精确匹配索引列的查询才有效，不支持范围查询
哈希索引就是采用一定的hash算法，将键值换算成新的hash值，映射到索引字段（hash）对用的槽位上然后存储在哈希表中；若多个映射到相同槽位就产生了hash冲突，可通过链表解决
（3）R-Tree空间索引：空间索引是myIsam引擎的一个特殊索引类型，主要用于地理空间数据类型，通常使用较少
（4）Full-text全文索引：是一种通过建立倒排索引，快速匹配文档的方式。例如es
问：Innodb中使用B+Tree的原因？
（1）相对于二叉树，B+Tree层级更少，搜索效率高；（经计算存储两千多万条数据时深度为3）
（2）相对于Hash索引，B+Tree支持范围匹配及排序操作；
（3）相对于B-Tree，无论是叶子节点还是非叶子节点都会保存数据，这样导致一页中的键值减少，指针跟着减少，在同样保存大量数据时，只能增加树的高度这样性能就会降低。

SQL执行频率

可查看全局的sql执行频率，包括增删改查的次数
show global status like ‘Com_______’;-- 一共七个_

慢查询日志

记录了所有执行时间超过阈值（long_query_time，默认10秒）的所有sql语句的日志
默认关闭(查看开关是否开启：show variables like ‘slow_query_log’😉，可在配置文件my.cnf中配置开启：
#开启慢查询日志
slow_query_log=1
#设置慢查询sql执行时间，默认10秒
long_query_time=2
默认生成的慢查询日志位置：/var/lib/mysql/localhost_slow.log

profiles

查看sql语句执行中各个阶段的耗时情况
默认关闭
#查看每一条sql的耗时基本情况
show profiles;// 其中包含了query_id
#查看指定query_id的sql语句各个阶段的耗时情况
show profile for query query_id;
#查看指定query_id的sql语句各个阶段的cpu情况
show profile cpu for query query_id;

explain执行计划

id：表示查询中select字句或者操作表的执行顺序（id相同，从上至下执行；id不同，值越大越先执行）
select_type（参考）：查询类型，常见的有SIMPLE(简单表)、PRIMARY(主查询)、UNION(联合查询第二或后面的查询)、SUBQUERY(select/where之后包含的子查询)等
type：连接类型，性能由好到差为NULL、system、const、eq_ref、ref、range、index、all
possible_key：可能用到的索引，一个或多个
key：实际用到的索引，如果为NULL则表示没有用到索引
Key_len：索引中使用的字节数，该值越小越好
rows（参考）：必须要执行查询的行数，是一个预估值
filtered：返回结果的行数占需要读取行数的百分比，该值越大越好
Extra：额外信息

索引失效情况

（1）联合索引
1）未遵循最左前缀法则（最左列必须存在，且不能跳过索引中的列，否则索引将部分失效）
2）范围><查询时，范围查询右侧的列索引失效，可通过>= <=来避免索引失效
（2）在索引列上进行运算会失效
（3）字符串字段不加引号会失效
（4）模糊查询时，头部模糊匹配会失效,如like ‘%工程’ 即%在前
（5）使用or分隔开的条件时，如果or前的条件中有索引而后面没有索引会失效，解决需要对另一个字段创建索引
（6）数据分布影响，如果mysql评估使用索引相比全表扫描更慢时则不是用索引
（7）

SQL提示

是优化数据库的一个重要手段，即就是在sql语句中加入一些人为的提示来达到优化操作的目的。
（1）use index：建议使用指定索引（至于是否使用最红由mysql决定）
explain select * form tb_user use index(idx_user_pro) where profession = ‘软件’;
（2）ignore index：不要使用指定索引
explain select * form tb_user ignore index(idx_user_pro) where profession = ‘软件’;
（3）force index：强制使用指定索引
explain select * form tb_user force index(idx_user_pro) where profession = ‘软件’;

覆盖索引

查询使用到了索引，并且需要返回的列在该索引中已经全部可以找到；所以尽量使用覆盖索引，避免select *和回表操作
Extra中提示解读：
（1）using index condition：查找使用到了索引，但是需要回表查询数据
（2）using where；using index：查找使用到了索引，同时需要的数据都在索引列中能找到，不需回表操作，效率高。

前缀索引

当字段类型为字符串（varchar或text等）时，有时需要索引很长的字符串这会让索引变得很大，查询时浪费大量磁盘io，影响效