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:存放的数据表的结构信息
table_name.myd:存放的数据表的内容
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后面可以有多个排序条件
就是在排序和分组(创建倒排表)
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
