mysql的性能优化，提⾼查询效率
1 商业需求对性能的影响
①不合理的需求
②⽆⽤功能堆积
⽐如需求:⼀个论坛贴⼦的总量统计
附加要求实时更新
count(*)⽐count(id)耗性能
并发请求压⼒测试
使⽤冗余数据.
update锁表时候select不能执⾏,mysql做的好，只锁定⼏⾏，但是还是存在这个select不能读取的问题.
提交的请求失败，会在下次重新提交.增加请求量.
解决办法：判断是不是真的需要实时更新.不做实时更新对性能有没有影响，看不到精确的数据redis⼀秒接收10w次的更新
⼤数据翻页
⽐如100w数据,⼀页100个，得1W页所以⼀共写⼀个100
2 mysql的执⾏流程
所有的查询缓存sql，都遵循默认情况下，只要⼀个表的任何⼀条数据发⽣变化，查询缓存都失效，
通过权限检查之后，如果查询缓存查不到数据,antlr(语法解析) 解析器解析语句，变成解析树，
对树进⾏预处理和优化,(常量，计算等等处理)，查询优化器(按照什么来查询,mysql对数据的统计信息，索引,⼀共有多少数据，从多种查询⽅案，根据mysql的统计信息去执⾏性能最快的执⾏⽅案)
sql的优化，是让查询优化器去执⾏程序员想让其执⾏的⽅案去执⾏这个⽅案，步骤⽂档传递给查询执⾏引擎，调⽤对应的执⾏引擎，到磁盘上去⽂件
查询语句快慢根据i/o耗时，根据查询计划来决定的，查询计划根据解析树，解析树根据sql，如果开了查
询缓存，在客户端显⽰，查询缓存存⼀份优化sql，就是让查询优化器根据程序员的计划选择匹配的计划，来减少i/o操作的时间
sql的优化原理:
①索引和索引的优化:
1索引的原理：把⽆序的数据变成有序的查询
建⽴索引执⾏流程
1如果没有索引，遍历整张表再⽐较，全表扫描,i/o多
把表的内容按照索引列排序
1把数据按照某⼀列进⾏排序
2把排序的结果变成⼀个倒排表
②索引表的物理结构
1数据库⽂件存储的位置是在my.ini配置⽂件中，dataDir对能赢的数据⽬录中
2每个数据库⼀个⽂件夹
1mysam引擎：每个表⼀个table_name
table_name.MYL:：索引信息和索引内容
TABLE_NAME.frm：存放的数据表的结构信息
d：存放的数据表的内容
2innodb在：每⼀个表(table_name)
frm：存放的是数据表的结构信息
数据⽂件和索引⽂件都是统⼀存放在ibdata⽂件中
3索引⽂件都是额外存在的，对索引的查询和维护
都是需要i/o的
③索引的结构(数据结构和算法基础)
1默认情况下,⼀旦创建了⼀个表，还设置了主键
mysql会⾃动为这个主键创建⼀个unique的索引
2索引类型:
1 normal普通索引，允许⼀个索引值后⾯跟上多个⾏值，在⼀条链上
2unique：唯⼀索引,⼀个索引后⾯只能有⼀个⾏值，唯⼀约束也就是添加⼀个unique，当为⼀个表添加主键的时候，也就是为这个表的主键列添加了⼀个唯⼀索引
3fulltext：全⽂检索,mysql的全⽂检索只能使⽤mysam引擎，性能低，不建议使⽤
3索引的⽅法(规定索引的结构)
1  b+tree:
平衡树：允许下⾯有很多⼦节点，但是整棵树是平衡的
主键索引保存的是地址
其他是引⽤的主键的地址
btree中的诗句都是按照⼀定的顺序保存的数据，是可以允许在
范围内进⾏查询
where *<100
2  hash
把索引的值做hash运算，并保存在hash表⾥⾯
优点：查询单个，性能⾼
缺点：只能精确查询,⽆法使⽤范围查询，没有顺序
如果⼤量的hash值相同，则性能较低
要想优化：从数据结构上想办法
索引的利弊:
好处:1提⾼检索效率
2排序列是索引列，如果查询条件等于排序的列，并且做了索引)⼤⼤降低排序成本
3分组操作中，分组条件也是排序列，效率也⾼
问题:
索引需要额外的维护成本，因为其是单独存在的，对数据增删改都需要对索引进⾏额外操作，都会增加i/o ④怎么创建索引?
1查询较频繁的字段作为索引
2如果不能有效的区分数据，那么这个列就不适合作为索引
⽐如:accountTYPE 作为索引列,accountType只有14种
如果作为索引，只能按照1/14的⽐例过滤数据.
但是如果可能出现，只按照该条件查询，考虑其他的性能提升的⽅式
select sum (amount )From accountflow Where accountType=0
第⼀种⽅案：单独创建⼀个系统摘要表，建⼀个列较系统充值⾦额
第⼆种⽅案：使⽤增量查询;
1创建⼀张表，记录每⼀天的充值总⾦额(begindate,enddate,totalamount)，每天使⽤
计时器对当天的充值记录进⾏结算，记录的都是截⽌昨天的数据
2创建⼀张⽉充值表，记录⼀个⽉的充值总⾦额，每⽉最后⼀天进⾏结算.
3查询总充值，从⽉报表中查询当⽉之前汇总，从⽇充值中查询当天之前截⽌时间的流⽔数据进汇总
使⽤另外⼀张当天流⽔表.记录当天的流⽔，再把三个数据相加.
3更新⾮常频繁的字段不适合做索引
索引有维护成本
4不会出现在where字句中的字段不应该创建索引
没意义
5索引不是越多越好(只为必要的列创建索引)
①不管多少个索引,⼀次查询⼀条sql只⽤⼀个索引
⼀个索引是⼀棵树，索引和索引之间是独⽴的
②因为索引和索引之间是独⽴的，都是单独维护的，数据的增删改,
所有的索引都需要单独改;
3 mysql中的索引的使⽤限制
1blpb和text类型的列只能创建前缀索引
2mysql⽬前不⽀持函数索引(在mysql中索引只能是⼀个列的原始值，不能把计算的值作为索引)
eg：查询1981年⼊职的员⼯
select* from emp where year(hire_hade)='1981'
问题：经过函数的运算，就算作为索引，也不会使⽤索引
解决⽅案
1:select *from emp where hire_date between '1981-01-01' and '1981-12-31'
2：再创建⼀列，这列是year(hire_date)作为索引
3使⽤不等于⽆法使⽤索引
4join语句中on 两边的字段不⼀样是⽆法⽤索引
5使⽤like如果以('%adc')，通配符开始⽆法⽤索引
1字符串可以⽤索引
2字符创创建的索引按照字母顺序排序
a
ab
abb
baa
bab
3使⽤like，第⼀个为%或者_通配符，第⼀个字母⽆法识别排序
货号 uq20160122222
查出2016的商品
6⾮等值查询⽆法使⽤hash索引
单列索引和符合索引
1⼀位⼀个查询⾄多只能使⽤⼀个索引，如果都是⽤单值索引
在数据量较⼤的情况下，不能很好地区分数据,
2mysql引⼊了多值索引(复合索引)
复合索引：多列的值组成的索引，多列的索引是有顺序的;
3复合索引的原理: 类似orderby后⾯可以有多个排序条件
就是在排序和分组(创建倒排表)
查看mysql索引select * from accountflow where action_time<'****' and accound_id =5
可以使⽤ action_time+accound_id的复合索引
select * from accountflow where action_time<'****'
可以使⽤ action_time+accound_id的复合索引
select * from accountflow where accound_id =5
不能使⽤action_time+accound_id的复合索引
select * from accountflow where accound_id =5 and action_time<'****' 不能使⽤action_time+accound_id的复合索引
4复合索引在查询的时候，按照复合索引的顺序依次查询，不管查询条件是不是完全
满⾜所有的索引的列，都可以使⽤部分的复合索引,
5在实际应⽤中都是使⽤的复合索引
查看mysql的执⾏计划和执⾏明细状态(explain profiling)
1 explain可以让我们查看⼀条sql的执⾏计划
explain sql语句
type列：对表查询所使⽤的⽅式
all：全表扫描
ref：使⽤索引查询
key列 ；最终选折的索引
keylen列：键长
ref列：过滤⽅式
rows列：过滤查询之后剩下的数据
extra列：查询中每⼀步实现的细节
等值查询优先于范围查询
join 优化:
⼩表连接⼤表，在⼩表中进⾏循环，减少内层循环
(⽤⼩的结果集去连⼤的结果集查询)
保证join条件的字段作为索引
如果连接条件是a join b 哪个结果集⼩，哪个放前⾯,
在连接过程中，每连接⼀次 ，都会产⽣⼀个中间表，放在 join bufffer
中
sql 优化原则:
1选折需要优化的sql，不是所有的sql都需要优化，在优化的过程中,⾸选
更需要优化的sql
怎么选折?优先选折⾼并发低消耗的sql,
1 1⼩时请求10000次,1次10个i/o
2,1⼩时请求10次,1次10000个i/o
考虑:
1从单位时间产⽣的总数来说，是相同的
2针对⼀个sql，如果我能把10个i/o变成7个i/o
针对第⼆个sql，如果把1w变成7k个i/o
3从难度1w变成7000难得多
4从整体性能上来说.
如果并发太⾼，每⼀个慢⼀点点，就时间太⼤了，优化连接次数多的，对系统的影响就⼤⼀些.，先优化请求量⼤的
②定位性能的瓶颈
1sql运⾏较慢的有两个影响原因,i/o和cpu
明确性能瓶颈所在
2明确优化⽬标，合理使⽤explain 和profile⼊⼿
1explain得到执⾏计划
2profile明确sql的问题和优化的结果
3⼩结果集驱动⼤结果集
4在索引中完成排序
5使⽤最⼩的columns
①减少⽹络的传输数据量
②mysql的排序原理:
把所以胡的column数据全部取出，在排序u安存去排序，再返回结果，如果column数据量⼤，排序区容量不够的时候，就会使⽤colum,n排序，再取数据，再返回多次请求⽅式，orderby ⾮常消耗性能
6 使⽤最有效的过滤条件
过多的where条件不⼀定能够提⾼访问性能，⼀定要让where使⽤⾃⼰预期的执⾏计划
7避免复杂的join和⼦查询
5 mysql主从:
①什么是mysql的组从，为什么要使⽤主从?
⼀个应⽤中⼤量消耗mysql的地⽅还是在查询把mysql拆开，公路分路⼀样.
配置主从的数据库服务器：就算
1mysql拆成了多个，也必须分出主从所有的写都在主的上⾯,
2所有的从都同步于主.
3既然涉及到同步就⼀定会有延迟
就可能在读的时候产⽣脏数据，所以在从上⾯进⾏的读操作,⼀定是对实时性和脏数据，有⼀定容忍度的数据,⽐如登录⽇志，报表⾸页,⾸页统计信息来源⽂章咨询,sns消息.
4在p2p中，做主从,⼤部分的读操作必须在主mysql上执⾏
(登录⽇志,⼀审⼆审列表,⽤户的流⽔信息，充值明细，投标明细，查询类的业务可以定位到从mysql)
5注意：在mysql主从时,⼀个业务(service中的⼀个⽅法，如果
既有读操作⼜有w操作，所以在⼀个事务中的所有的数据；资源只能来⾃于⼀个mysql)否则会出现脏数据的问题
②mysql主从原理
1要完成主从同步，就必须让在master上执⾏的dml和ddl能够在salve上，mysql选折使⽤⽂件来记录sql
2主服务器上的bin-log记录所有的dml和ddl+tcl操作
3mysql使⽤被动注册的⽅式来让那个从服务器请求同步主mysql的binlog:
4主从配置:
读写分离:
需求: 在后台的登录⽇志中，让登录⽇志的查询重数据库中查询，其他业务都还是使⽤主数据库
1⼀个service⽅法必须要定位到⼀个唯⼀的数据库上
mapper由sqlsessionfactory创建的,datasource创建的不同的⽅法对应不同的datasource
我们在程序中在⽅法中直接告诉应该去访问哪个datasource
2引⼊路由datasource之后，在应⽤中，需要⾃⼰去确定(告诉路由这次要访问的真实⽬标的datasource）
3让路由ds知道有哪些真实的datasourc和它们的对应的名字
4让路由知道我传⼊的名字去返回真实的ds

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。