mySQL进阶
MySQL体系结构

• 连接层,授权认证\连接处理
• 服务层,SQL语句的优化解析,SQL接口
• 引擎层,负责数据的存储和提取
• 存储层,与磁盘\内存的交互

存储引擎

存储引擎就是存储数据\建立索引\更新,查询数据等技术的实现方式.存储引擎时基于表的,而不是基于库的,所以存储引擎也可被称为表类型

引擎包括
MEMORY
MRG_MYISAM
CSV
FEDERATED
PERFORMANCE_SCHEMA
MyISAM
InnoDB
ndbinfo
BLACKHOLE
ARCHIVE
ndbcluster

创建表时,指定存储引擎

create table my_myisam(id int,name varchar(20)
) engine = Myisam;

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

InnoDB引擎

• InnoDB是一种兼顾高可靠性和高性能的通用存储引擎,在MySQL5.5之后,InnoDB是默认的MySQL存储引擎
• 特点
  DML操作遵循ACID模型,支持事务
  行级锁,提高并发访问性能
  支持外键FOREIGN KEY约束,保证数据的完整性和正确性
• 文件
  xxx.ibd:xxx代表的是表名,innoDB引擎的每张表都会对应这样一个表空间文件,存储该表的表结构(frm,sdi),数据和索引,设置参数innodb_file_per_table
• 存储结构特点,表->段->区->页->行

MyISAM引擎

• MyISAM是MySQL早期的默认存储引擎
• 特点
  不支持事务,不支持外键
  支持表锁,不支持行锁
  访问速度快
• 文件
  xxx.sdi:存储表结构信息
  xxx.MYD:存储数据
  xxx.MYI:存储索引

Memory

• Memory引擎的表数据是存储在内存中,由于受到硬件问题\或断电问题的影响,只能将这些表作为临时表或缓存使用
• 特点
  内存存放
  hash索引
• 文件
  xxx.sdi:存储表结构信息

存储引擎的选择

一般都只选择InnoDB
MyISAM会被MongoDB替代
MEMORY会被redis替代

索引

索引(index)是帮助MySQL高效获取数据的数据结构(有序).在数据之外,数据库系统还维护着满足特定查找算法的数据结构,这些数据结构以某种方式引用(指向)数据,这样就可以在这些数据结构上实现高级查找算法
|优势|劣势|
|提高检索效率(查找和排序查找),降低IO成本|占用空间|
|降低数据排序的成本,降低CPU的消耗|降低更新表的速度,增删改效率降低|

索引数据结构

MySQL的索引是在存储引擎层实现的,不同的存储引擎有不同的结构
最主要的就是B+Tree索引(最常见的索引类型,大部分引擎都支持B+树)

Btree(多路平衡查找树)

最大度数为5阶的Btree是指每个节点最多存储4个key,5个指针(5个子节点)
很好的一个网站,可模拟各种数据结构的增删查找(cs.usfca.edu)

B+Tree

相比于Btree,非叶子节点只存储指针和键值,数据都存储在叶子节点上
MySQL索引数据结构对经典的B+Tree进行了优化,在原B+Tree的基础上,增加了一个指向相邻叶子节点的链表指针,就形成了带有顺序指针的B+Tree,提高区间访问的性能
每个节点都存放在一个单独的页上

Hash索引

• Hash索引只能用于对等比较(=,in),不支持范围查询
• 无法利用索引完成排序操作(换句话说,你都用Hash了,那键一般都是人名之类的,也不需要排序)
• 查询效率高,通常只需要一次检索就可以了,效率通常要高于B+tree索引
  一般会在Memory引擎中用到

为什么InnoDQB存储引擎采用B+tree索引结构

为什么InnoDQB存储引擎采用B+tree索引结构

• 相比于二叉树,层级更少,搜搜效率高
• 对于Btree,无论是叶子节点还是非叶子节点,都会保存数据,这样导致一页中存储的键值减少,指针跟着减少,要同样保存大量数据,只能增加树的深度,导致性能降低
  (每个页的空间是固定的16K,一个节点占一个页,如果不存数据就可以存储更多的键值和地址)
• 相对Hash索引,B+tree支持范围匹配及排序操作

索引分类

根据字段特点分类

• 主键索引,自动创建,只能有一个,PRINMARY
• 唯一索引,可有多个,UNIQUE
• 常规索引,可有多个
• 全文索引,可有多个,FULLTEXT
  InnoDB存储引擎,根据存储形式,又可分为两种
• 聚集索引,数据和索引放在一个数据结构中,必须有(不然去哪找数据),只有一个
• 二级索引,数据和索引分开存储,叶子节点存储的是对应的主键(一般是)
  存在主键,主键索引就是聚集索引,不存在主键,第一个UNIQUE就是,没有合适的字段,也会生成一个rowid作为隐藏的聚集索引
  所以用二级索引的字段查表效率要低于用id主键查,需要回表查询

思考题

表->段->区->页->行中的行就是指表中的一行数据
假设:一行数据大小为1K,一页中可以存储16行这样的数据,InnoDB的指针占用6个字节的空间,主键即使2为bigint,占用字节数为8
那么当树的高度为2的时候
n8+(n+1)6 = 161024,n约为1170
叶子节点存储的数据量为:117116=18736
高度为3
1171117116=21939856,大约2200万

索引语法

show index from tb_user;展示表tb_user的索引
create index idx_user_name on tb_user(name),为表tb_user中name字段创建索引命名为idx_user_name
create unique index idx_user_phone on tb_user(phone),为表tb_user中phone字段创建索引命名为idx_user_phone
create index idx_user_pro_age_sta on tb_user(profession,age,status),创建联合索引
drop index idx_user_email on tb_user,删除索引

索引性能分析

查询当前数据库的增删改查的访问频次
show global status like ‘Com_______’,七个下划线代表七个字符

慢查询日志

慢查询日志记录了所有执行时间超过指定参数时间的所有SQL语句的日志
默认不开启,需要在MySQL的配置文件(/etc/my.cnf)中配置如下信息

//开启慢查询开关
slow_query_log=1
//设置慢日志的时间为2秒
long_query_time=2

配置完毕后,重新启动MySQL服务器,查看慢日志/var/lib/mysql/localhost-slow.log

show profies

//查看每一条SQL的耗时基本情况
show profiles;
//查看指定query_id的SQL语句各个阶段的耗时情况
show profile for query query_id;
//查看指定query_id的SQL语句CPU的使用情况
show profile cpu for query query_id;

explain

在做优化时,主要关注的字段type,possible_keys,key,key_len,rows,Extra
explain执行计划各字段含义:

• id,select查询的序列号,表示查询中执行select子句或者时操作表的顺序(id相同,执行顺序从上到下;id不同,值越大,越先执行)
• select_type,表示select的类型,常见的有simple(简单表),primary(主查询),union,subquery(子查询)
• type,表示连接类型,性能由好到坏的连接类型为,NULL,system,const,eq_ref,ref,range,index,all(优化尽量往前优化)
• possible_key,可能使用的索引
• key,实际用到的索引
• key_len,索引中使用的字节数,字段的占用字节数
• rows,mysql认为必须要执行查询的行数
• filtered,表示返回结果的行数占需读取行数的百分比,值越大越好
• Extra,额外展示信息

索引的使用规则

索引的建立很有必要,比如1000万的数据表,用非主键查询时可能需要20s,但是建立索引后可能只需要0.01s

最左前缀法则

如果索引了多列(联合索引),要遵循最左前缀法则,最左前缀法则指的是查询从索引的最左列开始,并且不跳过索引中的列
如果跳跃某一列,索引将部分失效(后面的字段索引失效)

若有联合索引,idx_user_pro_age_sta
explain select * from tb_user where profession ="软件工程" and age=31 and status='0'
explain select * from tb_user where profession ="软件工程" and status='0',status失效
explain select * from tb_user where age=31 and status='0' and profession ="软件工程",都有效,只要最左存在就行,不要求顺序
explain select * from tb_user where age=31 and status='0',age和status失效

出现范围查询,范围查询右侧的列索引失效
explain select * from tb_user where profession ="软件工程" and age>31 and status='0',status失效
explain select * from tb_user where profession ="软件工程" and age>=31 and status='0',都有效

索引失效

假设有索引,idx_user_pro_age_sta,idx_user_phone

//索引列运算
//不要在索引列上进行运算操作,会索引失效
explain select * from tb_user where substring(phone,10,2)='15',索引失效
//字符串不加引号
//不加引号失效
explain select * from tb_user where profession ='软件工程'and age=31 and status=0,status失效
//模糊匹配
//如果是头部模糊匹配则失效
explain select * from tb_user where profession ='%工程',失效
explain select * from tb_user where profession ='软件%',不失效
//or连接的条件
//如果or前的条件中的列有索引,后面的列没有索引,那么涉及的索引都不会被用到
explain select * from tb_user where id=10 or age=31;
//数据分布影响
如果MySQL评估使用索引比全表更慢,则不使用索引

SQL提示

假设有索引,idx_user_pro_age_sta,idx_user_profession
SQL提示,是优化数据库的一个重要手段,就是在SQL语句中加入一些人为的提示来达到优化操作的目的

explain select * from tb_user where profession ='软件工程',此时可能用到的索引是idx_user_pro_age_sta或idx_user_profession,SQL评估后会选择idx_user_pro_age_sta
explain select * from tb_user use index(idx_user_profession) where profession ='软件工程',用idx_user_profession索引
explain select * from tb_user ignore index(idx_user_profession) where profession ='软件工程',忽略idx_user_profession索引
explain select * from tb_user force index(idx_user_profession) where profession ='软件工程',必须用这个idx_user_profession索引

覆盖索引

尽量使用覆盖索引(查询使用了索引,并且需要返回的列,在该索引中全部能够找到),减少select*的使用

explain select id from tb_user where profession ='软件工程'and age=31 and status='0';覆盖索引
//为什么在idx_user_pro_age_sta中能查到id,因为叶子节点中就存储了id值,别忘了如果需要回表查询,还要通过id回表呢
explain select id,profession,age,status from tb_user where profession ='软件工程'and age=31 and status='0';覆盖索引

执行计划中的Extra中为
using index condition,那么需要回表查询
using where;using index,查找使用了索引.但需要的数据在索引中都能找到不需要回表查询

前缀索引

当字段类型为字符串时,有时候需要索引很长的字符串,这会让索引变得很大,查询时浪费大量的磁盘IO,影响查询效率.此时可以只将字符串的一部分前缀,建立索引.这样可以大大节约索引空间,从而提高索引效率

create index idx_xxxx on table_name(column(n));取column的前n行建立索引idx_xxxx
前缀的长度可以根据不重复的索引值和数据表的总记录数的比值(索引的选择性)确定
唯一索引的选择性是1,这是最好的索引选择性,性能也最好
select count(distinct email)/count(*) from tb_user;来查看索引选择性值
select count(distinct substring(email,1,5))/count(*) from tb_user;取前缀后的索引选择性值

单列&联合索引

单列索引:即一个索引值包含单个列
联合索引:即一个索引包含多个列
在业务场景中,如果存在多个查询条件,考虑针对于查询字段建立索引时,建议建立联合索引
多条件联合查询时,MySQL优化器会评估哪个字段的索引效率更高,会选择该索引完成本次查询

索引设计原则

• 不查询表的时候别建
• 数据量大,再建
• 常查询\排序\分组的字段建立
• 字符串型字段字符串长,建立前缀索引
• 多条件查询,尽量用联合索引
• 尽量建唯一索引,效率高
• 如果索引列不能存储NULL值,建表时用NOT NULL约束它.当优化器知道每列是否包含NULL值时,可以更好地确定哪个索引最有效用于查询

SQL优化

插入数据

相对于单条insert插入,可以优化为:

• 批量插入
• 手动提交事务
• 主键顺序插入
  对于大批量插入数据,可以使用load插入

//客户端连接服务端时,加上参数--local-infile
mysql --local-infile -u root -p
//设置全局参数local_infile为1,开启从本地加载文件导入数据的开关
set global local_infile=1;
//执行load指令将准备好的数据,加载到表结构中
load data local infile '/root/sql1.log' into table 'tb_user' fileds terminated by ',' lines terminated by '\n';

load插入1000万条数据可能需要几秒,而单条insert插入可能就需要十几分钟

主键优化

在InnoDB存储引擎中,表数据都是根据主键顺序组织存放的,这种存储方式的表称为索引组织表(IOT)
(也就是B+tree的那个双向链表中存储的数据,聚集索引)

• 页分裂
  页可以为空,也可以填充一半,也可以填充100%,每个页包含了2-N行数据(如果一行数据太大,会行溢出),根据主键排列
  当主键乱序插入的时候,就可能会发生页分裂
• 页合并
  当删除一行记录时,实际上记录并没有被物理删除,只是记录被标记为删除并且它的空间变得允许被其他记录声明使用
  (就是程序员这里的逻辑删除就是在表中标记,程序员这里的物理删除相当于数据库的逻辑删除,数据库的逻辑删除也是对一行数据做一个标记,方便找回数据,当被其他数据覆盖掉,才是真正的删除掉)
  当页中删除的记录达到merge_threshold(默认为页的50%),InnoDB会开始寻找最靠近的页看看是否可以将两个页合并,优化空间
• 主键设计原则
  满足业务需要的情况下,尽量降低主键的长度
  插入数据时,尽量选择顺序插入,选择使用AUTO_INCREMENT自增主键
  尽量不要使用UUID做主键或者是其他自然主键,如身份证号
  业务操作时,避免对主键的修改

order by优化

• 根据排序字段建立合适的索引,多字段排序时,也遵循最左前缀法则
• 尽量使用覆盖索引
• 多字段排序,一个升序一个降序.此时需要注意联合索引在创建时的规则(asc\desc)
• 如果不可避免的出现filesort,大数据量排序时,可以适当增大排序缓冲区大小sort_buffer_size(默认256K)
  using filesort:通过表的索引或全表扫描.然后在排序缓冲区sort buffer中完成排序操作
  using index:通过有序索引排序
  假设有索引,idx_user_age_phone

select id,age,phone from tb_user order by age,phone;//是using index
select id,age,phone from tb_user order by phone,age;//是using index和using filesort
//因为是要靠B+tree中的双向链表来排序的,如果索引是按照先age后phone建立,那么当order by age,phone可以很丝滑地取到数据,但是当order by phone,age,phone相同再按age升序排,这个顺序与索引中并不一致,就导致了using filesort可能会查询很慢
//如果一个升序一个降序
select id,age,phone from tb_user order by age asc,phone desc;
//创建索引
creat index idx_user_age_phone_ad on tb_user(age asc,phone desc);
//就可避免explain执行计划中using index和using filesort都被用到,只用using index

group by优化

为什么当不满足前缀法则时,where age=31,执行计划中type就是ALL,而order by age和group by age就是index

• 分组操作,可以通过索引来提高效率
• 分组操作时,索引的使用也是满足最左前缀法则的
  假设有索引idx_user_pro_age_sta

select age,count(*) from tb_user where profession ='软件工程' group by age;也是走完全走索引的using index 不会是using index和using temporary

limit优化

常见又非常头痛的问题时limit 2000000,10.
此时需要MySQL排序前2000010记录,仅仅返回2000000-2000010的记录,其他记录丢弃,查询排序的代价非常大
优化思路:一般分页查询时,通过创建覆盖索引能够比较好地提高性能,可以通过覆盖索引加子查询形式进行优化

select * from tb_sku limit 2000000,10;用时20s
select id from tb_sku order by id limit 2000000,10;用时10s
explain select * from tb_sku t ,(select id from tb_sku order by id limit 2000000,10) a where t.id = a.id;用时10s

select * from tb_sku order by id limit 2000000,10;为什么不直接这样
select id from tb_sku order by id limit 2000000,10;和select * from tb_sku limit 2000000,10;的用时为什么有差异

count优化

MyISAM引擎把一个表的总行数存在了磁盘上,因此执行count()的时候会直接返回这个数,很快
InnoDB麻烦了,执行count()的时候需要把数据一行一行地从引擎里读出来,然后累计计数
所以只能自己计数
select count(1) from tb_user;也是记录整张表的数据条数
按照效率,count(字段)<count(主键id)<count(1)约等于count(),所以尽量使用count()

update优化

begin;
update student set no='2000100100' where id=1;主键有索引,行锁,锁此行
commit;
begin;
update student set no='2000100100' where name ='韦一笑';如果name字段没索引,将锁住整张表
commit;

InnoDB的行锁是针对索引加的锁,不是针对记录加的锁,并且该索引不能失效,否则回从行锁升级为表锁

undo log和redo log

缓冲池(buffer pool):主内存中的一个区域,里面可以缓存磁盘上经常操作的真实数据,在执行增删改查操作时,先操作缓冲池中的数据(若缓冲池没有数据,则从磁盘加载并缓存),以一定频率刷新到磁盘,从而减少磁盘IO,加快处理速度
数据页(page):是InnoDB存储引擎磁盘管理的最小单位,每个页的大小默认为16KB,页中存储的是行数据
redo log
重做日志,
记录的是事务提交时数据页的物理修改,是用来实现事务的持久性的
该日志文件由两部分组成:重做日志缓冲(redo log buffer)以及重做日志文件(redo log file),前者是在内存中,后者在磁盘中.当事务提交后会把所有修改信息都存到该日志文件中,用于在刷新脏页到磁盘,发生错误时,进行数据恢复使用
undo log
回滚日志,
用于记录数据被修改前的信息,作用给包含两个:提供回滚和MVCC(多版本并发控制).undolog和redo log记录的物理日志不一样,它是逻辑日志

• 可以认为当delete一条记录时,undolog中会记录一条对应的insert记录,反之亦然
• 当update一条记录时,它记录一条对应相反的delete记录.当执行rollback时,就可以从undo log中的逻辑记录读取到相应的内容并进行回滚