MySQL优化
在我们的实际场景中经常会遇到sql查询较慢的问题,今天特地写一篇文章来聊聊我对于MySQL调优相关内容的知识,以及从哪些点去进行优化.
MySQL表字段的设计
1.字段长度尽量不要设置太大,用多少设置多少,因为在MySQL底层没有用的长度都会被占用.
2.该什么类型就设置什么类型,比如日期就不要设置成String,这样查询效率也会降低.
短的经常修改的字符串可以用char进行存储,占用空间小,效率较高.
3.字段尽量不要设置为null,比如用户填写一些非必要信息的话就无需遵守.
4.不一定要遵守三范式,必要的时候可以反三范式,最好的其实就是两者皆有,根据我们的实际业务场景决定是否要冗余空间换查询时间.
比如一个订单表内有用户的信息,就无需进行多表关联查询了,因为在某种情况下,表的数据量太大的话,这个多表查询性能浪费是很多的.
5.主键尽量选择代理主键,不要选择自然主键,代理主键不与业务结合,更有利于维护,或者使用那种生成随机主键的工具类进行主键的获取.
MySQL的存储引擎
默认存储引擎是InnoDB,其他的还有几类是MyISAM和Memory
InnoDB和MyISAM用的是B + Tree.
Memory用的是Hash结构.
数据结构区别:
Hash不适用于范围查询,因为用的是内存级的存储引擎,非范围查询速度优于另外2个搜索引擎,
B + Tree是从二叉树一步步演变过来,也是IO次数相对最少的数据结构,前面2层放索引,根据指针指向对应的数据.
索引区别:
MyISAM:非聚促索引
InnoDB:聚促索引
MySQL分析器
profile
具有最基本的功能,但是在后续版本会被淘汰,在企业中经常使用.
Performance Schema
复杂,功能完善,可以理解为profile的替代品,具体需要查询MySQL英文文档.
平时存放在Performance Schema数据库中,存储引擎也为Performance Schema,对MySQL进行全局监控,我们可以根据里面表中的信息,以及设置具体的变量,来对我们的sql进行分析
三范式
一种数据库创建的规范,遵守了之后可以实现规范化,但是我们不一定就要遵守他,需要根据具体的业务场景来调整我们的策略.
范式
优点:范式会更加规范,更新速度也比反范式要快.
缺点:查询的时候往往需要关联很多张表,耦合程度较高.
反范式
优点:在某些地方可以极大提高我们的效率,使本来三四表联查的数据,只需查询一张表就可以出来.
缺点:容易出现重复数据,表格内的冗余数据较多,需要进行空间换时间.
索引前置知识
索引覆盖
EXPLAIN SELECT st.SIdFROM student st WHERE st.Sname = '赵雷';
我们先按照where后面的条件去Sname索引的B + Tree中查询,在根节点中我们会找到赵雷对应的那条记录的主键id,这样子我们无需查询其他的就可以得到我们最后想要的数据,这就叫索引覆盖.
回表
select * from student st where st.age = 21;
第一步:这个sql会先去定位到age索引的那颗B+Tree,找到对应age为21的节点上,节点上存放着对应的主键id.
第二步:然后我们根据具体的id去下方id的B+Tree根节点中找到对应的整条数据,这就叫做回表操作.
索引下推
索引下推指的是,在我们没有启动索引下推前,我们本身筛选数据是查询出所有的数据,然后按照具体的范围条件,在server层进行筛选,而现在在索引下推后,我们筛选数据是在存储引擎层实现的,到业务层的时候已经筛选完毕了
下推前
下推后
最左匹配
最左匹配原则只适用于联合索引,要求联合索引的最左索引必须存在,否则会导致索引失效,具体场景可以看上方图片.
MySQL的索引
MySQL索引失效
#首先建立了一个联合索引顺序为age name birthday
A:explain select * from student st where st.age = 21and st.name = '张三'and st.birthday= '2021-8-31'; #索引都启用
A:explain select * from student st where st.birthday= '2021-8-31'and st.age = 21and st.name = '张三'; #索引都启用
A:explain select * from student st where st.age = 21and st.name = '张三'; #索引都启用
A:explain select * from student st where st.age = 21and st.birthday= '2021-8-31'; #后面一个索引未启用
A:explain select * from student st where st.age = 21and st.namelike'%张三'; #后面一个索引未启用
A:explain select * from student st where st.age <22and st.name = '张三'and st.birthday= '2021-8-31'; #启用了开头的索引
#联合查询在某些情况下会导致索引失效
explain select * from student st where st.age + 1 = 22;
#在where后面做计算会导致索引失效
explain select * from student st where st.namelike'%张三';
#百分号在前会导致索引失效
explain select * from student st where st.age > 21and st.name = '张三'orderby st.birthdayDESC;
#范围查询后面无法用到索引,orderby也一样
explain select * from student st where st.age = 21orderby st.nameDESC,st.birthdayDESC;
#order by后面排序规则尽量保持一致,不然会无法使用索引
explain select * from student st where st.age = 21and st.name = '张三'orderby st.sexDESC;
#引用了一个不是索引的列 也会导致索引失效
EXPLAIN SELECT * FROM student WHERE student.SId = 6or student.sex = '女';
#or前后必须都要使用索引,否则会导致索引失效
EXPLAIN SELECT * FROM student LEFT JOIN sc ON student.SId = sc.Sidand student.SId = 01;
#关联条件前后数据类型需要设置一致,否则可能导致索引失效
EXPLAIN SELECT * FROM student LEFT JOIN sc ON student.SId = sc.Sidand student.SId = 11;
EXPLAIN SELECT * FROM student LEFT JOIN sc ON student.SId = sc.SidWHERE student.SId = 11;
#第一条是 index + ref
#第二条是 const + ref
#建议用where代替and 进行后面的筛选
explain select * from student st where st.age = '22';
#强制类型转换会导致索引失效
EXPLAIN select * from rental where rental_date>'2005-08-19'orderby rental_date,inventory_id #ALL profileTime:0.0101335
EXPLAIN select * from rental where rental_date>'2005-08-20'orderby rental_date,inventory_id #range profileTime:0.0059805
EXPLAIN select * from rental FORCE INDEX (`rental_date`) where rental_date>'2005-08-19'orderby rental_date,inventory_id;
#range 强制索引命中 0.006391
#查询数据量到达总数据量的百分之30也会索引失效 高性能MySQL,实际测试出来在百分之18左右.
MySQL的执行计划
也就是explain关键字,在我们的sql优化中经常会用到他,当把explain放到我们sql语句前的时候,执行查询,会出现下列的一些字段,其中最重要的就是type和ke以及Extra.
MySQL官网内容:https://dev.mysql.com/doc/refman/5.5/en/explain-output.html
type代表索引优化程度.
key代表用了哪些索引.
Extra代表补充说明.
测试数据准备,MySQL存储计划插入
delimiter $$$
create procedure zqtest()
begin
declare i int default 0;
set i=0;
start transaction;
while i<1000000 do --执行次数
--这里输入插入语句
set i=i+1;
end while;
commit;
end
$$$
delimiter;
call zqtest();
id
平时稍微注意一下就行,可被用来做隐式内连接STRAIGHT_JOIN的优化.
--sample:简单的查询，不包含子查询和union
explain select * from emp;
--primary:查询中若包含任何复杂的子查询，最外层查询则被标记为Primary
explain select staname,ename supname from (select ename staname,mgr from emp) t join emp on t.mgr=emp.empno ;
--union:若第二个select出现在union之后，则被标记为union
explain select * from emp where deptno = 10unionselect * from emp where sal >2000;
--dependent union:跟union类似，此处的depentent表示union或union all联合而成的结果会受外部表影响
explain select * from emp e where e.empno in ( select empno from emp where deptno = 10unionselect empno from emp where sal >2000)
--union result:从union表获取结果的select
explain select * from emp where deptno = 10unionselect * from emp where sal >2000;
--subquery:在select或者where列表中包含子查询
explain select * from emp where sal > (select avg(sal) from emp) ;
--dependent subquery:subquery的子查询要受到外部表查询的影响
explain select * from emp e where e.deptnoin (selectdistinct deptno from dept);
--DERIVED: from子句中出现的子查询，也叫做派生类，
explain select staname,ename supname from (select ename staname,mgr from emp) t join emp on t.mgr=emp.empno ;
--UNCACHEABLE SUBQUERY：表示使用子查询的结果不能被缓存
explain select * from emp where empno = (select empno from emp where deptno=@@sort_buffer_size);
--uncacheable union:表示union的查询结果不能被缓存：sql语句未验证
table
对应行正在访问哪一个表，表名或者别名，可能是临时表或者union合并结果集
1、如果是具体的表名，则表明从实际的物理表中获取数据，当然也可以是表的别名
2、表名是derivedN的形式，表示使用了id为N的查询产生的衍生表
3、当有union result的时候，表名是union n1,n2等的形式，n1,n2表示参与union的id
type
type显示的是访问类型，访问类型表示我是以何种方式去访问我们的数据，最容易想的是全表扫描，直接暴力的遍历一张表去寻找需要的数据，效率非常低下，访问的类型有很多，效率从最好到最坏依次是：
system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL
一般情况下，得保证查询至少达到range级别，最好能达到ref
--all:全表扫描，一般情况下出现这样的sql语句而且数据量比较大的话那么就需要进行优化。
explain select * from emp;
--index：全索引扫描这个比all的效率要好，主要有两种情况，一种是当前的查询时覆盖索引，即我们需要的数据在索引中就可以索取，或者是使用了索引进行排序，这样就避免数据的重排序
explain select empno from emp;
--range：表示利用索引查询的时候限制了范围，在指定范围内进行查询，这样避免了index的全索引扫描，适用的操作符： =, <>, >, >=, <, <=, IS NULL, BETWEEN, LIKE, or IN()
explain select * from emp where empno between7000and7500;
--index_subquery：利用索引来关联子查询，不再扫描全表
explain select * from emp where emp.jobin (select job from t_job);
--unique_subquery:该连接类型类似与index_subquery,使用的是唯一索引
explain select * from emp e where e.deptnoin (selectdistinct deptno from dept);
--index_merge：在查询过程中需要多个索引组合使用，没有模拟出来
--ref_or_null：对于某个字段即需要关联条件，也需要null值的情况下，查询优化器会选择这种访问方式
explain select * from emp e where e.mgrisnullor e.mgr=7369;
--ref：使用了非唯一性索引进行数据的查找
create index idx_3 on emp(deptno);
explain select * from emp e,dept d where e.deptno =d.deptno;
--eq_ref ：使用唯一性索引进行数据查找
explain select * from emp,emp2 where emp.empno = emp2.empno;
--const：这个表至多有一个匹配行，
explain select * from emp where empno = 7369;
--system：表只有一行记录（等于系统表），这是const类型的特例，平时不会出现
possible_keys
显示可能应用在这张表中的索引，一个或多个，查询涉及到的字段上若存在索引，则该索引将被列出，但不一定被查询实际使用
explain select * from emp,dept where emp.deptno = dept.deptno and emp.deptno = 10;
key
`实际使用的索引，如果为null，则没有使用索引，查询中若使用了覆盖索引，则该索引和查询的select字段重叠。`
explain select * from emp,dept where emp.deptno = dept.deptno and emp.deptno = 10;
key_len
表示索引中使用的字节数，可以通过key_len计算查询中使用的索引长度，在不损失精度的情况下长度越短越好。
explain select * from emp,dept where emp.deptno = dept.deptno and emp.deptno = 10;
ref
显示索引的哪一列被使用了，如果可能的话，是一个常数
explain select * from emp,dept where emp.deptno = dept.deptno and emp.deptno = 10;
rows
根据表的统计信息及索引使用情况，大致估算出找出所需记录需要读取的行数，此参数很重要，直接反应的sql找了多少数据，在完成目的的情况下越少越好
explain select * from emp;
extra
包含额外的信息。
--using filesort:说明mysql无法利用索引进行排序，只能利用排序算法进行排序，会消耗额外的位置
explain select * from emp order by sal;
--using temporary:建立临时表来保存中间结果，查询完成之后把临时表删除
explain select ename,count(*) from emp where deptno = 10 group by ename;
--using index:这个表示当前的查询时覆盖索引的，直接从索引中读取数据，而不用访问数据表。如果同时出现using where 表名索引被用来执行索引键值的查找，如果没有，表面索引被用来读取数据，而不是真的查找
explain select deptno,count(*) from emp group by deptno limit 10;
--using where:使用where进行条件过滤
explain select * from t_user where id = 1;
--using join buffer:使用连接缓存，情况没有模拟出来
--impossible where：where语句的结果总是false
explain select * from emp where empno = 7469;
MySQL实战优化细节
union all和union
尽量使用前者,
后者虽然不会重复,但是前者效率更高,在对数据重复性要求不是特别高的情况下,建议会用union.
in和or使用in,他的效率会更高,or需要多次判断,in则是在一个集合当中进行筛选.
使用索引扫描来排序
EXPLAIN SELECT
rental_id,
staff_id
FROM
rental
WHERE
rental_date = '2005-05-24 22:53:30'
ORDER BY
inventory_id,
customer_id
ORDER BY 分组的效率非常低,是在内存中进行分组的,当我们执行explain的时候显示的是Using FileSort我以前看到过一个很有趣的问题,是大数据方面的,问题的原话是这样的,你有一个G的数据,要对它进行分组,但是你实际内存只有300M,该怎么去处理,这里就引入了hadoop的分而治之的概念,将数据分割成一个个的小块,分治在归并.而在MySQL当中,我们其实也可以应用这个,既然ORDER BY效率低,我们就利用索引来达到分而治之的效果.
建立一个索引不仅可以用来查询优化,还可以进行分组优化,我们都知道group by一般都意味着All级别的全表检索,但是在某些情况下,我们可以建立联合索引让他进行索引覆盖,也就是根据where条件后面的字段进行索引覆盖.
使用limit来对单条数据进行优化
使用前提条件:当你知道只会查询出一条语句的时候,使用limit来对单条数据进行优化,可以直接省略之后的查询步骤,因为默认是要走全表的.
SELECT * FROM student LEFT JOIN sc ON student.SId = sc.Sid WHERE sc.id = 1; #0.00027275
SELECT * FROM student LEFT JOIN sc ON student.SId = sc.Sid WHERE sc.id = 1 LIMIT 1; #0.00021725
联合索引优化(范围查询)
建立联合索引可以避免文件排序
EXPLAIN SELECT * FROM `student` WHERE student.Sname = '郑竹' and student.Sage > '1988-01-01 00:00:00' ORDER BY student.Ssex;
# 创建索引顺序为where条件后非范围查询的字段->需要排序的字段->需要范围查询的字段
# 按次序创建索引 可以保证不会违背最左匹配原则导致索引失效
limit分页优化
前面的值不宜不过,我们可以使用子查询来进行优化.
SELECT
rental.*
FROM
rental
WHERE
rental.rental_id >= (SELECT Max(rental_id) FROM (SELECT rental_id FROM rental ORDER BY rental_id LIMIT 1000, 5) AS tmp)
LIMIT 5; #根据子查询中之前的偏移量 拿取5条数据
MySQL优化器
MySQL优化器会对我们的sql进行一些细节性的优化,但是请记住,可能有时候会出现并不是绝对正确的优化,这些原因可能来与错误的统计信息或者是成本估算与实际不匹配,那么某些情况下,我们就需要自己指定他们的优化顺序,比如强制使用某个索引,内连接时的驱动表选择等等.
分区表
概念
使用分而治之的思想把表分成一个个的区域,分成N个文件进行存储.
对于用户而言，分区表是一个独立的逻辑表，但是底层是由多个物理子表组成。分区表对于用户而言是一个完全封装底层实现的黑盒子，对用户而言是透明的，从文件系统中可以看到多个使用#分隔命名的表文件。
mysql在创建表时使用partition by子句定义每个分区存放的数据，在执行查询的时候，优化器会根据分区定义过滤那些没有我们需要数据的分区，这样查询就无须扫描所有分区。
分区的主要目的是将数据安好一个较粗的力度分在不同的表中，这样可以将相关的数据存放在一起.
作用
分区表的数据更加容易维护,比如我要删除某一个分区内的所有数据.
分区表的数据可以分布在不同的硬件设备上.
分区表可以用来备份和恢复.
和limit的作用很像,分而治之,达到快速定位的效果,可以让MySQL查询更少的数据.
大表拆分
水平拆分
水平拆分：简单来说就是将一张表的数据分别存储到多个数据库中，所以一个库中只保存一部分数据；
垂直拆分
垂直拆分：简单的说就是将数据库中的各个表，依据业务情况将一些表放在一个数据库中，一些表放在另外一个数据库中， 比如商城这些的数据库端按照业务垂直拆分：按照业务订单数据库、用户数据库、商品数据库等进行拆分。
拆分规则
优先考虑使用缓存来降低对数据库的读操作。
再考虑读写分离，降低数据库写操作。
最后开始数据拆分,首先考虑按照业务垂直拆分。
再考虑水平拆分：先分库(设置数据路由规则，把数据分配到不同的库中)；可以通过mycat来处理
最后再考虑分表，单表拆分到数据1000万以内。
查漏补缺
LEFT JOIN AND的问题
SELECT * FROM student LEFT JOIN sc ON student.SId = sc.Sid and student.SId = 6;
#这句话会匹配出左表的所有数据,至于右表的数据只会显示sid为6的,其他全部为null
ON不会参与连接的运算
SELECT * FROM student LEFT JOIN sc ON 1 != 1;
