阿里云网站怎么备案域名,wordpress 用户中心,附近找工作招聘信息,工程项目建设网站文章目录 存储引擎InnoDB引擎MyISAM引擎Memory存储引擎的选择 索引索引数据结构Btree(多路平衡查找树)BTreeHash索引为什么InnoDQB存储引擎采用Btree索引结构 索引分类思考题 索引语法索引性能分析慢查询日志show profiesexplain 索引的使用规则最左前缀法则索引失效SQL提示覆盖… 文章目录 存储引擎InnoDB引擎MyISAM引擎Memory存储引擎的选择 索引索引数据结构Btree(多路平衡查找树)BTreeHash索引为什么InnoDQB存储引擎采用Btree索引结构 索引分类思考题 索引语法索引性能分析慢查询日志show profiesexplain 索引的使用规则最左前缀法则索引失效SQL提示覆盖索引前缀索引单列联合索引 索引设计原则 SQL优化插入数据主键优化order by优化group by优化limit优化count优化update优化 undo log和redo log 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的索引是在存储引擎层实现的,不同的存储引擎有不同的结构 最主要的就是BTree索引(最常见的索引类型,大部分引擎都支持B树)
Btree(多路平衡查找树)
最大度数为5阶的Btree是指每个节点最多存储4个key,5个指针(5个子节点) 很好的一个网站,可模拟各种数据结构的增删查找(cs.usfca.edu)
BTree
相比于Btree,非叶子节点只存储指针和键值,数据都存储在叶子节点上 MySQL索引数据结构对经典的BTree进行了优化,在原BTree的基础上,增加了一个指向相邻叶子节点的链表指针,就形成了带有顺序指针的BTree,提高区间访问的性能 每个节点都存放在一个单独的页上
Hash索引
Hash索引只能用于对等比较(,in),不支持范围查询无法利用索引完成排序操作(换句话说,你都用Hash了,那键一般都是人名之类的,也不需要排序)查询效率高,通常只需要一次检索就可以了,效率通常要高于Btree索引 一般会在Memory引擎中用到
为什么InnoDQB存储引擎采用Btree索引结构
为什么InnoDQB存储引擎采用Btree索引结构
相比于二叉树,层级更少,搜搜效率高对于Btree,无论是叶子节点还是非叶子节点,都会保存数据,这样导致一页中存储的键值减少,指针跟着减少,要同样保存大量数据,只能增加树的深度,导致性能降低 (每个页的空间是固定的16K,一个节点占一个页,如果不存数据就可以存储更多的键值和地址)相对Hash索引,Btree支持范围匹配及排序操作
索引分类
根据字段特点分类
主键索引,自动创建,只能有一个,PRINMARY唯一索引,可有多个,UNIQUE常规索引,可有多个全文索引,可有多个,FULLTEXT InnoDB存储引擎,根据存储形式,又可分为两种聚集索引,数据和索引放在一个数据结构中,必须有(不然去哪找数据),只有一个二级索引,数据和索引分开存储,叶子节点存储的是对应的主键(一般是) 存在主键,主键索引就是聚集索引,不存在主键,第一个UNIQUE就是,没有合适的字段,也会生成一个rowid作为隐藏的聚集索引 所以用二级索引的字段查表效率要低于用id主键查,需要回表查询
思考题
表-段-区-页-行中的行就是指表中的一行数据 假设:一行数据大小为1K,一页中可以存储16行这样的数据,InnoDB的指针占用6个字节的空间,主键即使2为bigint,占用字节数为8 那么当树的高度为2的时候 n8(n1)6 161024,n约为1170 叶子节点存储的数据量为:11711618736 高度为3 117111711621939856,大约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_log1
//设置慢日志的时间为2秒
long_query_time2配置完毕后,重新启动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 age31 and status0
explain select * from tb_user where profession 软件工程 and status0,status失效
explain select * from tb_user where age31 and status0 and profession 软件工程,都有效,只要最左存在就行,不要求顺序
explain select * from tb_user where age31 and status0,age和status失效出现范围查询,范围查询右侧的列索引失效
explain select * from tb_user where profession 软件工程 and age31 and status0,status失效
explain select * from tb_user where profession 软件工程 and age31 and status0,都有效索引失效
假设有索引,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 age31 and status0,status失效
//模糊匹配
//如果是头部模糊匹配则失效
explain select * from tb_user where profession %工程,失效
explain select * from tb_user where profession 软件%,不失效
//or连接的条件
//如果or前的条件中的列有索引,后面的列没有索引,那么涉及的索引都不会被用到
explain select * from tb_user where id10 or age31;
//数据分布影响
如果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 age31 and status0;覆盖索引
//为什么在idx_user_pro_age_sta中能查到id,因为叶子节点中就存储了id值,别忘了如果需要回表查询,还要通过id回表呢
explain select id,profession,age,status from tb_user where profession 软件工程and age31 and status0;覆盖索引执行计划中的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_infile1;
//执行load指令将准备好的数据,加载到表结构中
load data local infile /root/sql1.log into table tb_user fileds terminated by , lines terminated by \n;load插入1000万条数据可能需要几秒,而单条insert插入可能就需要十几分钟
主键优化
在InnoDB存储引擎中,表数据都是根据主键顺序组织存放的,这种存储方式的表称为索引组织表(IOT) (也就是Btree的那个双向链表中存储的数据,聚集索引)
页分裂 页可以为空,也可以填充一半,也可以填充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
//因为是要靠Btree中的双向链表来排序的,如果索引是按照先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 indexgroup by优化
为什么当不满足前缀法则时,where age31,执行计划中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 temporarylimit优化
常见又非常头痛的问题时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;用时10sselect * 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 no2000100100 where id1;主键有索引,行锁,锁此行
commit;
begin;
update student set no2000100100 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中的逻辑记录读取到相应的内容并进行回滚
