鲜花网站建设的项目介绍,烈焰传奇网页版,南京app开发公司排名,中国域名注册官网目录 一、影响性能的相关因素存储过程#xff1a; 二、sql优化1、Mysql系统架构2、引擎区别#xff1a; 3、索引1、什么是索引#xff1f;联合主键索引理解#xff1a;索引长度理解#xff1a;什么是慢查询#xff1f; 1#xff09;、索引理解2#xff09;… 目录 一、影响性能的相关因素存储过程 二、sql优化1、Mysql系统架构2、引擎区别 3、索引1、什么是索引联合主键索引理解索引长度理解什么是慢查询 1、索引理解2、树高3、在线二叉树总结4什么是索引覆盖5什么是回表查询 2、Mysql索引实现1、MyISAM索引实现2、Innodb索引实现3、聚簇索引 3、索引的利弊4、创建索引情况5、单值索引和组合索引单值索引组合索引 4、Explain命令1)select_type:查询类型1、union2、subquery3、dependent subquery 和 dependent union4、derived 2type联接类型1、system 和 const2、eq_ref3、ref唯一索引和非唯一索引的区别 4、ref_or_null5、index_merge6、range7、index8、ALL 3Extra详细信息1、Not exists2、Range checked for each record3、Using filesort4、Using index5、Using temporary6、Using where7、Using union8、Using index for group-by 4)命令汇总 三、Join理解及优化1、Inner join2、join3、where4、left join5、straight_join6、场景优化情况7、Show profiles比较性能 四、一些索引测试1、模糊查询like2、范围查询、between 3、函数4、or and5、算数表达式 五、索引使用策略及优化1、索引选择性2、前缀索引3、自增id作主键的原因 六、其他优化1、order by 优化双路排序单路排序区别提问优化 一、影响性能的相关因素 存储过程
存储过程类似可以在数据库里面写代码的一种脚本在Mysql里面可以封装一个函数在这个函数里面可以写很多的逻辑。可以把所有的业务放在存储过程里面去实现。
缺点数据库很耗资源。数据库的连接是有上限的一个请求调用一个存储过程去执行业务逻辑意味着单个线程响应的时间变长在并发高的情况下每个线程返回的时间都变长的话意味着数据库的连接很快就被用完了那么其他现在就会进入等待那么在应用层面就会积压很多线程意味着应用中的线程会被耗尽那么外面的请求就进不来了就会导致服务器的雪崩宕机。
在互联网应用里面并不是多么好可以不在数据库中作的事情就不要在数据库做
如果是传统行业并发比较小那么使用存储过程在sql里面调用函数就也还行。
存储过程中调用的函数有哪些
case when
表的设计也是影响性能的因素
账号密码修改频率低的可以设计在一个实体类里面一张表里面可以放在缓存里面。
修改频率高的放在一个实体类里面不放缓存。
应用层面的设计也是影响性能的因素
如果应用层面代码没设计好所有的请求都落到数据库那么sql优化的再好也顶不住。
代码规范、mysql规范、字段规范
二、sql优化
1、Mysql系统架构 2、引擎
区别
数据库默认是Innodb引擎。
3、索引
1、什么是索引
能帮我们快速的定位数据它是一种新的数据结构。
索引是对数据库表中一列或多列的值进行排序的一种结构使用索引可以快速访问数据库表中的特定信息。 联合主键索引理解
一张表永远只有一个主键索引。
除了主键索引外的其他索引都可以叫做辅助索引。
创建了索引的字段最好设计成不为null最好把【不是null】选项勾选上
这种情况叫做联合主键这三个加起来才是主键才是唯一主键相当于 【组合索引】 一样 索引长度理解 什么是慢查询
mysql可以主动开一个慢查询慢查询就是
超过指定时间的sql就会记录到日志里面 1、索引理解
Navicat里面添加索引
BTREE 底层就是BTREE
如图给这个字段加上索引就是把这个字段作为一个索引然后数据库底层就会把这个字段的数据另外弄成一个二叉树的数据结构 那么在查询的时候如果把这个字段作为查询条件数据库就会直接去这个二叉树数据结构找该数据。
比如表有10000条数据有十条数据有该字段那么没给该字段加索引前数据库需要遍历全表遍历这10000条数据但是如果把该字段弄成索引那么就只需要遍历这个二叉树而已就是只需要遍历10次就行效率就大大提升了。 2、树高
为什么千万级别的数据只需要3层树高就可以
树高是4的话有3次IO。
如图下面的图的树高是4IO次数就是3.因为根节点已经被缓存了所以IO次数就是4-13
因为非叶子节点只存储指针而每个节点属于一次io一次io有16k3次io就可以存有千万级别的数据对应的指针 这个点不是很理解
**磁盘IO次数树的高度 ** 还是 磁盘IO次数树的高度-1
读取每次节点都相当于一次的磁盘IO搜索整棵树路径长度为树高磁盘IO次数树的高度树高越矮磁盘IO次数越少性能就越高。
比如树高4经历了3次磁盘IO因为mysql会把根节点最上面的非叶子节点叫根节点缓存起来所以树高为4的话磁盘IO次数为3。所以根节点被缓存起来后树高就变成3io次数也是3所以磁盘IO次数树的高度
为什么树高4只经历了3次IO
因为根节点被缓存起来了所以树高就变成3了IO次数也就变成3了。
问题那第一次查询的时候树的根节点应该还没被缓存起来所以树高4IO次数也是4吗
3、在线二叉树
https://www.cs.usfca.edu/~galles/visualization/Algorithms.html 磁盘IO次数树高-1 总结
1、索引数据结构都是BTree
2、为什么不用二叉树或者红黑树呢
二叉树特定情况下会变成单向链表性能低可以用更好的数据结构 BTree。
3、有BTree和BTree有什么区别为什么选择BTree
BTree 的非叶子节点是存实际数据的会导致树的高度更高那么磁盘IO次数就会越多效率就会减低
BTree 中 非叶子节点是不存储实际的数据存储的是值和指针因为体积小那么树高就更低磁盘IO次数就更少查询效率就更快
MySql默认一次的磁盘IO是16k16k固定的情况下非叶子节点体积越小能容纳数据就越多树的阶越大树高越矮这个【阶】就是非叶子节点里面存的数据多少。
读取每次节点都相当于一次的磁盘IO搜索整棵树路径长度为树高磁盘IO次数树的高度-1树高越矮磁盘IO次数越少性能就越高。
比如树高4经历了3次磁盘IO因为mysql会把根节点最上面的非叶子节点叫根节点缓存起来所以树高为4的话磁盘IO次数为3。
4什么是索引覆盖
索引覆盖辅助索引里面存的值就已经包括了我需要的字段了如图就是该字段是设置为辅助索引而sql要查的字段就刚好是它所以就不用再通过索引里面的值去回表找主键索引来查询数据
因为辅助索引字段里面的值就已经是我们想要的数据了再回表去查更多的数据也没有什么意义。
也可以简单的理解为索引覆盖就是不用再进行回表查询就是不用通过辅助索引的值去主键索引那里查完整的行数据。通过辅助索引就能找到对应字段的列的数据前提查询的该字段也是一个索引
分析图 覆盖索引的解释 5什么是回表查询
一个表只有一个主键索引其他的我们自己添加的索引都可以叫做辅助索引比如复合索引这些都能叫做辅助索引
回表查询就是 通过辅助索引的叶子节点里面存的主键值到主键索引哪里找到对应的数据。
主键索引里面存的值就是完整的一行数据 2、Mysql索引实现
主键索引就是表的id
辅助索引就是我们自己添加的索引
1、MyISAM索引实现
MyISAM的主键索引和辅助索引找数据的分析图
这里的主键找到数据的索引还得根据索引的物理位置在另外的文件.myd文件去找具体的数据比Innodb慢 2、Innodb索引实现
Innodb的主键索引和辅助索引 3、聚簇索引
聚集索引是指数据库表行中数据的物理顺序与键值的逻辑索引顺序相同。
跟上面分析的索引实现一样
通俗讲: 聚簇索引将数据存储与索引放到了一块找到索引也就找到了数据 非聚簇索引将数据存储于索引分开结构索引结构的叶子节点指向了数据的对应 3、索引的利弊
索引是独立于我们数据之外的另外一种数据结构需要存储空间的。
新增、修改等操作都要重新生成索引树需要额外消耗资源弊但是相比我们查询所带来的性能的提升利这点消耗可以忽略不计。
索引的好处 1提高表数据的检索效率 2如果排序的列是索引列大大降低排序成本 3在分组操作中如果分组条件是索引列也会提高效率
**索引的问题**索引需要额外的维护成本
字段修改的频率比查询的频率高的话就不用建立索引。
4、创建索引情况
1较频繁的作为查询条件的字段应该创建索引
2唯一性太差的字段不适合单独创建索引即使频繁作为查询条件
比如查询 男和女 筛选完还是有海量的数据
3更新非常频繁的字段不适合创建索引
添加索引的话频繁修改就需要频繁重新生成索引树消耗的资源 大于 查询的性能带来的收益
4不会出现在WHERE 子句中的字段不该创建索引
都不作为查询条件更没有必要添加索引
5、单值索引和组合索引
单值索引
只是把一个字段作为索引这个就是单值索引 分析图 组合索引
也叫复合索引
这个索引就是组合索引组合索引用的比较多。 分析图
组合索引比单值索引查询要快但是要记得组合索引得符合【最左原则】
最左原则where后面查询的第一个字段不用按where顺序要和组合索引的第一个字段相同。 注意这个情况也是符合最左原则的最左原则是要条件符合不是说写的顺序要符合。
比如组合索引A、B、C那么where条件 ABC ACB BAC CBA 等都是符合最左原则的。
组合索引A、B、C那么where条件 BC CB BC 等里面都没有A查询的时候无法命中组合索引中的A索引字段那么这个才叫做不符合最左原则 最佳创建组合索引
应该按照最高频的字段来创建组合索引最大程度去覆盖到sql的查询条件这样查询性能也能提升生成的索引树成本也能降低。 4、Explain命令
示例数据库
数据下载:https://github.com/datacharmer/test_dbMySQL官方文档中提供的示例数据库之一employees使用命令导入数据:
mysql -uroot -p123456 -t employees.sqlExplain命令的作用通过这个Explain命令来查看sql的执行计划看能怎么对sql进行优化。
Explain命令主要来查看SQL语句的执行计划查看该SQL语句有没有使用索引有没有做全表扫描等。它可以模拟优化器执行SQL查询语句从而知道MySQL是如何处理用户的SQL语句
1)select_type:查询类型
1、union
union是产生的两个记录集(字段要一样的)并在一起成为一个新的记录集
union是将表内容拼接成一列上下拼接也是根据字段共同属性进行将表与表之间数据进行上下拼接
2、subquery
子查询中的第一个select其select_type为SUBQUERY 3、dependent subquery 和 dependent union
子查询只有一个select 第一个select 就是 subquery
子查询有多个select 第一个 select 就是 dependent subquery
第二个select 使用 union 连接的话那就是 dependent union。
dependent 表示依赖的意思。表示 最外面的select 依赖于 子查询中的结果。 4、derived
当这个语句查询出来的数据作为别人的表的时候查询的数据跟在from后面相当于派生表的类型 2type联接类型
联接类型
1、system 和 const
system查询速度最快直接从内存中查询获取到数据
const通过主键索引查询到数据 2、eq_ref
条件是唯一索引
两张表关联的情况下比如join之类的查询条件的这个字段刚好是主键字段的情况下或者是唯一字段的情况下那么type的类型就是 eq_ref
eq_ref表示查询的那个条件属于该表的【主键字段】或者是【唯一字段】
因为该字段属于唯一主键匹配这个主键成功就能直接找到对应的数据相当于 一对一 的情况。 3、ref
条件非唯一索引
eq_ref 的性能比 ref 要高 如图两张表关联查询时第二张表titles的匹配字段emp_no属于该表的非唯一性主键索引时那么这张表的 type 联接类型就属于 ref。 解答
把那三个字段弄成一个索引相当于弄成一个【非唯一性主键索引】那么在查询的时候类型就是 ref
ref 性能比 eq_ref 低的原因就在于 ref 出现的情况是 第二张表的查询条件是 非唯一性主键索引因为是非唯一性的所以在索引树查询的时候需要匹配如图的三个字段的条件才行不像唯一性主键索引只需要匹配一个就行所以 ref 的性能比 re_ref 低一点。 唯一索引和非唯一索引的区别
一张表永远只有一个主键索引。
这种叫做唯一索引
这张表只有这个字段是主键是主键索引是唯一索引 这种叫非唯一索引
这种情况叫做联合主键这三个加起来才是主键三个加起来才能算是一个【唯一主键】相当于 【组合索引】 一样 4、ref_or_null
出现的场景
查询的条件字段有索引但是设计字段的时候是可以为null所以如果用该字段作为查询条件的话就会出现ref_or_null的联接类型 5、index_merge
出现的场景一张表中有多个查询条件且都有索引时会出现 index_merge 索引合并类型。 针对or导致的索引失效的分析图
OR 前后只要存在非索引的列都会导致索引失效 测试
6、range
按指定的范围进行检索就会显示未 range类型 7、index
ALL 是全表扫描数据index 是全表扫描索引
性能上面index 仅仅比 ALL 好上一点而已
8、ALL
all 类型就是一个全表扫描跟用没用索引一个样。 3Extra详细信息
1、Not exists
查询的条件是冲突的一个是主键是非空的employees一个要查这个字段为null的主键不可能为空就不再继续扫描该表dept_emp了。
mysql根据语义发现这种查法是查不出数据的就显示为 【Not exists】 2、Range checked for each record
范围查询覆盖了整张表mysql表示没有好的索引可以用就显示这个【Range checked for each record】 3、Using filesort
这里的sql是根据字段进行分组所以这里显示的 【Using filesort】 表示按照字段进行排序
这个解释比较笼统后续可以研究 4、Using index
这是性能很高的一种情况。当查询所需的数据可以直接从索引树中检索到时就会显示【Using index】 5、Using temporary
查询的时候Mysql需要创建一张临时表来处理该查询时会显示【Using temporary】
临时表是比较消耗资源的 6、Using where
当sql有where子句时extra都会有说明。
但是这两个where字句一个有显示 Using where 一个没有不太理解 7、Using union
一张表中有多个查询条件且都有索引时会出现type类型为 index_merge 进行索引合并类型
Using union出现的场景
这里也一样会使用union函数进行索引合并跟type联接类型为 index_merge 一样 8、Using index for group-by
Using index for group-by 这个也可以理解为【索引覆盖】
索引覆盖索引已经包括了我需要的字段了就不用再通过索引去回表找主键索引来查询数据
分析图 表明可以在索引中找到分组所需的所有数据不需要查询实际的表。 覆盖索引的理解 4)命令汇总 按顺序从上到下性能最好的是 system 性能最差的是 ALL 三、Join理解及优化
1、Inner join
谁是小表谁是大表MySQL Optimizer会自动判断和我们写表的顺序是无关的 2、join
join 和 inner join 一样的结果谁是小表谁是大表MySQL Optimizer会自动判断和我们写表的顺序是无关的 3、where
where、join 和 inner join 一样的结果谁是小表谁是大表MySQL Optimizer会自动判断和我们写表的顺序是无关的 4、left join
left join 就不用解释了就是左表驱动右表以左边的表为基准。 MySQL优化器要确定以谁为驱动表也就是说以哪个表为基准在处理此类问题时MySQL
Optimizer采用了简单粗暴的解决方法哪个表的结果集小就以哪个表为驱动表.
5、straight_join
straight_join 是能优化sql性能的
STRAIGHT_JOIN 功能同 inner join 、 join 是一样的区别只是能让左边的表来驱动右边的表能改变 表优化器 对于 联表其他的作用和 inner join 、 join 的效果都是一样的 这个是 dept_emp 为驱动表 因为 无论是 Inner join 还是 straight_join , 用 Explain 命令看的话都是以 dept_emp 为驱动表所以两者的耗时其实是差不多的。 因为这条sql语句 无论是用 inner join 还是 straight_join 都是以 dept_emp 为驱动表所以看不出使用 straight_join后的性能提升。
所以我用 straight_join 把employees 表弄成驱动表那么结果应该就是 驱动表为 employees 的sql语句性能比较低。如图也能看出
注意 straight_join 是用来让左边的表作为驱动表的跟left join 一样的作用不过似乎使用 straight_join的性能会比使用 left join 的性能要高。 6、场景优化情况
优化情况添加索引或者是更改驱动表 优化情况根据情况设置缓冲区大小 7、Show profiles比较性能
Show profiles是MySql用来分析当前会话SQL语句执行的资源消耗情况,可以用于SQL的调优测量 四、一些索引测试
1、模糊查询like
用like做查询时通配符% 放在字段值后面样子为—xxx%进行前缀查询索引就能使用
前缀查询的样子abc%就可以拿a、b、c先去索引树进行匹配所以索引就可以使用。
如果把通配符%放在字段值最前面来进行后缀查询样子为—%xxx那么索引就会失效。
比如后缀查询的样子是 【%abc】我们根本不知道%是什么值也就没办法在索引树进行比对所以索引就会失效 2、范围查询
、
进行范围查询后后续的查询条件的字段的索引就会失效 就不会再走索引
大于和小于 会导致后面的索引失效 between
between 不会导致后面索引失效 3、函数
使用函数会导致该字段的索引失效如图 4、or and
**使用or不会导致索引失效的情况**就是or左右的查询条件的字段都有加索引单值索引 或者 复合索引复合索引要符合最左原则才行
使用or导致索引失效的情况 or左右的查询条件的字段有一个以上没有加索引或者不满足索引规则那么使用or就会导致索引失效
or 会导致索引失效的情况 如上图如果想让 这个 or 不导致索引失效我们可以单独给这个title 加上一个所以就可以了。
如图 5、算数表达式
在该字段进行算数表达式也会导致索引失效
如图
五、索引使用策略及优化
1、索引选择性
索引的选择性是指索引列中不同值的数目与表中记录数的比。
如果一个表中有2000条记录表索引列有1980个不同的值那么这个索引的选择性就是1980/20000.99。
比如有2000条数据值记录的是男和女那么这个表索引列就是有2个不同的值索引选择性就是 2/2000就是一千分之一表明索引的效率很低。
如果是20000条数据那么就是 2/20000 等于一万分之一
一个索引的选择性越接近于1这个索引的效率就越高。
既然索引可以加快查询速度那么是不是只要是查询语句需要就建上索引
答案是否定的。
因为索引虽然加快了查询速度但索引也是有代价的索引文件本身要消耗存储空间同时索引会加重插入、删除和修改记录时的负担另外MySQL在运行时也要消耗资源维护索引因此索引并不是越多越好。
情况1如果表的记录比较少少于2000条数据那么就没必要建立索引。
情况2超过2000条数据的表也要看索引的选择性索引的选择性太低也没必要建立索引。比如男和女这种。
除了选择性需要考量这个索引的长度也要考量因为索引的长度越长那么需要消耗的空间就越多。 索引长度理解 2、前缀索引
有一种与索引选择性有关的索引优化策略叫做前缀索引就是用列的前缀代替整个列作为索引key当前缀长度合适时可以做到既使得前缀索引的选择性接近全列索引同时因为索引key变短而减少了索引文件的大小和维护开销。
前缀索引分析
需求 first_name 和 last_name 字段这些字段来查询数据。
1、根据两个字段查询数据没有添加索引的情况下耗时是0.068s 2、根据情况看是否要给字段建立单值索引可通过计算出索引选择性的大小来看。
查看 first_name 和 last_name 字段的索引选择性发现都太小不适合建立单值索引。 3、复合索引
复合索引符合要求索引选择性达到了0.9313可以建立但是可以看到两个字段的长度加起来是30弄成符合索引之后因为长度越长越消耗空间。
有没有兼顾长度和选择性的办法
可以考虑用fifirst_name和last_name的前几个字符建立索引例如fifirst_name, left(last_name, 3)看看其选择性 4、优化复合索引减少复合索引的长度 5、再对比下查询性能速度提升了 278倍 6、注意点
前缀索引兼顾索引大小和查询速度但是其缺点是不能用于ORDER BY和GROUP BY操作也不能用于
Covering index即当索引本身包含查询所需全部数据时不再访问数据文件本身
3、自增id作主键的原因
在使用InnoDB存储引擎时如果没有特别的需要请永远使用一个与业务无关的自增字段作为主键。
为什么要用一个自增的id作为主键
解释
自增id 涉及到磁盘的位置因为如果输入的位置是连续的话可以把一个位置用完之后才会再去申请下一块位置因为位置是连续的。
uuid 因为不是连续的所以存入的位置不会按顺序插入可能这个位置插一个数据另一个位置插入一个数据可能导致的结果就是 一个位置还没用完数据就会插到另一个位置。 这样会产生很多空间的碎片就是一个区域还没用完就会去重新申请新的一块区域导致空间浪费。
比如行李箱一件一件摆好的话存的衣服会更多自增id如果随便丢进行李箱那么能塞进去的衣服就会比较少uuid一些空间缝隙没利用到
为什么要用自增id作为主键的分析图 六、其他优化
1、order by 优化
order by 作用对指定列进行排序
ORDER BY 实现原理 1通过有序索引而直接取得有序的数据 2通过MySQL 的排序算法将存储引擎中返回的数据进行排序然后再将排序后的数据返回
双路排序
顺序读性能比乱序读高
内存排完序之后需要回表进行乱序读查询需要的字段。 单路排序
单路排序没有 row_id这个磁盘地址
因为 sortBuffer 是把表的整行数据都读过去的所以在内存排完序的结果就是最终的结果不用跟双路排序一样还得回表查。
单路排序速度比双路排序要快不过比较占内存。 区别
order by 怎么选择 单路排序还是双路排序
MySQL根据设置的【max_length_for_sort_data】变量来确定走【单路排序】还是【双路排序】默认值是1024字节
如果需要返回的【列的总长度】就是元组长度 大于 【max_length_for_sort_data】走【双路排序】
否则走【单路排序】。
查看show variables like ‘max_sort_length’; 单路排序算法sort buffer 缓冲区缓存整个行的数据在根据排序字段进行排序返回数据给客户端
缺点占用内存
优点避免回表查询
双路排序算法sort buffer 缓冲区只缓存主键id和排序字段在内存中在根据排序字段进行排序在做一次回表查询根据主键id查询该行数据返回给客户端。
区别1
查询返回的字段如果比较多那么 order by 就是走【双路排序】
查询返回的字段如果比较少那么 order by 就是走 【单路排序】。
区别2
双路排序有值的磁盘物理地址单路排序没有。
**元组**双路排序的元组是 row_id磁盘物理地址单路排序的元组就是那些字段所以单路排序的元组比双路排序的元组长
解释
单路因为元组比较长所以需要占的空间就比双路排序的多在同样的内存大小的 Sortbuffer内存缓冲区 中单路存的元组就比双路的少所以产生的临时文件就会比双路的多。
但是双路排序有顺序读和乱序读所以它的磁盘io次数是比单路排序的要多。
所以总的来说单路排序的性能会比双路排序的性能会好些但是还是得看情况而定
1双路排序会读取两次表 且第二次是随机读取的 2单路排序的元组比双路排序要长导致它需要多次向临时文件写入内容增加IO操作当需要返回的列的总长度很长时尤其明显。
提问
问题如果一次排序的顺序高达1万条10万条的数据能一次性加载到内存中吗?
答
如果要排序的数据量太多的话不可能一次性把数据都读取到内存这样会把mysql的服务器的内存一下子就占满了。
MySQL会进行分批次的读取比如有1万的数据在内存可能会一次读1千然后执行完得到最终结果后会把排好序存储在一个临时文件中1千条排序后的结果。
然后再重复上面的操作1万条数据一次1千的分批读那么就会产生10个临时文件然后再针对这10个临时文件重新读取然后在内存中进行归并排序这个时候的性能肯定比直接一万条数据直接排序的性能更高。
数据量多的情况下单路排序和双路排序都是这么分批次处理的。
优化
ORDER BY 实现原理 1通过有序索引而直接取得有序的数据 2通过MySQL 的排序算法将存储引擎中返回的数据进行排序然后再将排序后的数据返回
优化方案 1加大max_length_for_sort_data 参数
加大这个参数那么就提高比元组长度大的概率所以就提高走单路排序的概率
2去掉不必要的返回字段
不必要的返回字段越多元组长度就越长占的空间就越多导致排序缓冲区每次读取的数据量变小
3增大sort_buffer_size 排序缓冲区参数
增大的话排序缓冲区一次性能读取的数据就会变多
增大 sort_buffer_size 和 max_length_for_sort_data 参数尽量走单路排序减少临时文件产生减少IO尽量避免走双路排序。
2、using temporary
http://mysql.taobao.org/monthly/2015/03/04/
3、group by 优化
分组
GROUP BY的前提是排序,所以优化手段和ORDER BY是一样的。
