mysql优化注意点网上资料一大堆,不过个人建议还是先了解原理,然后再去看优化技巧,不仅能让你更好地因地制宜的优化,也能让你对mysql有一个新的认识高度,在此先浅谈mysql的执行过程和sql缓存以及索引,后面再更新一下
/*************************************************************************************************************
MySQL整个查询执行过程,总的来说分为6个步骤:
1.客户端向MySQL服务器发送一条查询请求
2.服务器首先检查查询缓存,如果命中缓存,则立刻返回存储在缓存中的结果。否则进入下一阶段
3.服务器进行SQL解析、预处理、再由优化器生成对应的执行计划
4.MySQL根据执行计划,调用存储引擎的API来执行查询
5.将结果返回给客户端,同时缓存查询结果
*************************************************************************************************************/
所以检索效率高低主要根据mysql的优化器选择的执行计划而定,所有的优化策略也是因此产生的
mysql优化点主要从以下几点着重描述一下:
- sql缓存
- 索引
- 表结构
- mysql分层架构
- sql细节
- mysql连接
/*********************************************[sql缓存]******************************************************************
1.原理:sql缓存默认是开启的,query_cache_type='ON' 则查询都会走缓存的,在解析一个查询语句前,如果查询缓存是打开的,那么MySQL会检查这个查询语句是否命中查询缓存中的数据。如果当
前查询恰好命中查询缓存,在检查一次用户权限后直接返回缓存中的结果。这种情况下,查询不会被解析,也不会生成执行计划,更不会执行.
2.命中问题:MySQL将缓存存放在一个引用表(不要理解成table,可以认为是类似于HashMap的数据结构),通过一个哈希值索引,这个哈希值通过查询本身、当前要查询的数据库、客户端协议版本号等一
些可能影响结果的信息计算得来。所以两个查询在任何字符上的不同(例如:空格、注释),都会导致缓存不会命中。
3.失效的情况:如果查询中包含任何用户自定义函数、存储函数、用户变量、临时表、mysql库中的系统表,其查询结果都不会被缓存。比如函数NOW()或者CURRENT_DATE()会因为不同的查询时间,返回不同的查询结
果,再比如包含CURRENT_USER或者CONNECION_ID()的查询语句会因为不同的用户而返回不同的结果,将这样的查询结果缓存起来没有任何的意义
4.缓存的优缺点和注意事项:MySQL的查询缓存系统会跟踪查询中涉及的每个表,如果这些表(数据或结构)发生变化,那么和这张表相关的所有缓存数据都将失效。正因为如此,在任何的写操作时,MySQL必须将对应表的所有缓存都设
置为失效。如果查询缓存非常大或者碎片很多,这个操作就可能带来很大的系统消耗,甚至导致系统僵死一会儿。而且查询缓存对系统的额外消耗也不仅仅在写操作,读操作也不例外:
a.任何的查询语句在开始之前都必须经过检查,即使这条SQL语句永远不会命中缓存
b.如果查询结果可以被缓存,那么执行完成后,会将结果存入缓存,也会带来额外的系统消耗
基于此,我们要知道并不是什么情况下查询缓存都会提高系统性能,缓存和失效都会带来额外消耗,只有当缓存带来的资源节约大于其本身消耗的资源时,才会给系统带来性能提升。但要如何评估打开缓存
是否能够带来性能提升是一件非常困难的事情,也不在本文讨论的范畴内。如果系统确实存在一些性能问题,可以尝试打开查询缓存,并在数据库设计上做一些优化,比如:
a.用多个小表代替一个大表,注意不要过度设计
b.批量插入代替循环单条插入
c.合理控制缓存空间大小,一般来说其大小设置为几十兆比较合适
d.可以通过SQL_CACHE和SQL_NO_CACHE来控制某个查询语句是否需要进行缓存
5.最好的用法:不要轻易打开查询缓存,特别是写密集型应用。如果你实在是忍不住,可以将query_cache_type设置为DEMAND,这时只有加入SQL_CACHE的查询才会走缓存,其他查询则不会,这样可以非常自由地控制哪些查询需要被缓存
*************************************************************************************************************/
-- 查看是否开启缓存
SHOW VARIABLES LIKE '%query_cache%';
-- 设置缓存信息
SET GLOBAL QUERY_CACHE_TYPE='demand' ;
SET GLOBAL query_cache_size='600000';
-- 不走缓存的sql
SELECT SQL_NO_CACHE * FROM whelp_center_config;
-- 走缓存的sql
SELECT SQL_CACHE * FROM whelp_center_config WHERE tnt_inst_id='MYBKC1CN';
SHOW STATUS LIKE 'Qca%';
SHOW STATUS LIKE 'Com_sel%'; -- 缓存命中率 ,服务器执行了多少select语句
SHOW STATUS LIKE 'last_query_cost';
/***********************************************[索引]******************************************************************
索引类型大致分为:普通索引,唯一索引,主键索引,组合索引,全文索引
其中:
普通索引:是最基本的索引,没有任何的限制
唯一索引:索引列的值必须唯一,不过被索引的字段的值可以为null
主键索引:特殊的唯一索引,不过列的值不可为null,且一张表只能有一个主键索引
组合索引:值在多个字段上创建的索引,索引是否生效遵循断点规则
全文索引:主要用来查找文本中的关键字,而不是直接与索引中的值相比较
组合索引注意事项:
生效原则:从前往后依次使用生效,如果中间某个索引没有使用,那么断点前面的索引部分起作用,断点后面的索引没有起作用
(a,b,c) 三个列上加了联合索引(是联合索引 不是在每个列上单独加索引)而是建立了a,(a,b),(a,b,c)三个索引,另外(a,b,c)
多列索引和 (a,c,b)是不一样的,只跟索引顺序有关系跟sql条件查询的顺序无关
全文索引注意事项:
1.MySQL自带的全文索引只能用于数据库引擎为MyISAM的数据表,如果是其他数据引擎,则全文索引不会生效。此外,MySQL自带
的全文索引只能对英文进行全文检索,目前无法对中文进行全文检索。如果需要对包含中文在内的文本数据进行全文检索,我
们需要采用Sphinx(斯芬克斯)/Coreseek技术来处理中文。
2.目前,使用MySQL自带的全文索引时,如果查询字符串的长度过短将无法得到期望的搜索结果。MySQL全文索引所能找
到的词的默认最小长度为4个字符。另外,如果查询的字符串包含停止词,那么该停止词将会被忽略
3.如果可能,请尽量先创建表并插入所有数据后再创建全文索引,而不要在创建表时就直接创建全文索引,因为前者比后者的全文索引效率要高
索引失效或则不适合的情况:
1.不适合重复数据太多的列(假如索引列TYPE有5个键值,如果有1万条数据,那么 WHERE TYPE = 1将访问表中的2000个数据块。再加上访问索引块,
一共要访问大于2000个的数据块.如果10条数据一个数据块的话,全表扫描也才扫描1000个数据块,索引的效果就达不到了)
2.前导模糊查询不能利用索引(like '%XX'或者like '%XX%')
假如有这样一列code的值为'AAA','AAB','BAA','BAB' ,如果where code like '%AB'条件,由于前面是
模糊的,所以不能利用索引的顺序,必须一个个去找,看是否满足条件。这样会导致全索引扫描或者全表扫
描。如果是这样的条件where code like 'A % ',就可以查找CODE中A开头的CODE的位置,当碰到B开头的
数据时,就可以停止查找了.
3.如果条件中有or,即使其中有条件带索引也不会使用(这也是为什么尽量少用or的原因)
要想使用or,又想让索引生效,只能将or条件中的每个列都加上索引
4.对于多列索引,不是使用的第一部分,则不会使用索引
5.like查询以%开头
6.如果列类型是字符串,那一定要在条件中将数据使用引号引用起来,否则不使用索引
7.如果mysql估计使用全表扫描要比使用索引快,则不使用索引
8.尽量避免逆向查询,逆向查询索引出的列过多,mysql选择执行计划的时候考虑到内存损耗问题会直接全表查询
9.is null不走索引 is not null走索引
10.尽量避免函数或则计算表达式,网上很多说函数表达式不走索引,这个得看表达式运用的地方,如果运用在静态值的话是走的,运用在数据库字段的话
是不走的
11.现在mysql已经优化,只要查询结果过多,就算正常使用了索引,也不会走索引的
***********************************************************************************************************************/
-- 全文索引
ALTER TABLE student ADD FULLTEXT INDEX fulltext_name(student_name, course);
SELECT * FROM student WHERE MATCH(student_name,course) AGAINST('zhang'); -- 因为是innodb,全文索引不生效
-- 组合索引[百分号]
EXPLAIN SELECT * FROM testA WHERE scene='ASK1' AND NAME LIKE'%cba';-- 百分号在前面无论内容是什么都不走索引
EXPLAIN SELECT * FROM testA WHERE scene='ASK1' AND NAME LIKE'9999%'; -- 百分号在后面会走索引
EXPLAIN SELECT * FROM testA WHERE scene='ASK1' AND NAME LIKE'%9999%'; -- 前后百分号不走索引
EXPLAIN SELECT * FROM testA WHERE scene='ASK1' AND NAME LIKE'%9999';
-- 组合索引[join] 条件:两边都建立过索引且字段类型相同,且索引类型相同
EXPLAIN SELECT url
FROM testA t1
LEFT JOIN help_center_entry t2
ON t1.name=t2.name
AND t1.`scene`=t2.`scene`
WHERE t2.`scene`='ASK1' AND t2.`help_type`='HELP' AND t2.name='9999'
-- 组合索引or
EXPLAIN SELECT * FROM testA WHERE scene='ASK1' OR id=512 -- 不走索引
EXPLAIN SELECT * FROM testA WHERE scene='ASK1' AND NAME='9999' OR id=512 -- 全部组合索引字段加上自带索引的id,走索引
-- 逆向查询 注意了,逆向查询不一定失效,只是大多数逆向查询出的结果过多导致不走索引,如果逆向查询的结果很少还是会的
EXPLAIN SELECT * FROM testA WHERE scene IN ('NEWTEST') -- 查询结果不多,走索引
EXPLAIN SELECT * FROM testA WHERE scene IN ('ASK1') -- 查询结果过多,不走索引
EXPLAIN SELECT * FROM testA WHERE scene NOT IN ('NEWTEST') -- 逆向结果过多 不走索引
EXPLAIN SELECT * FROM testA WHERE scene NOT IN ('ASK1') -- 逆向结果不多 走索引
EXPLAIN SELECT * FROM testA WHERE scene='ASK1'
AND NAME IN
(
SELECT NAME FROM help_center_entry WHERE NAME='相思无情9' -- in中查询结果比较少,外层查询走索引
)
EXPLAIN SELECT * FROM testA WHERE scene='ASK1'
AND NAME IN
(
SELECT NAME FROM help_center_entry -- in中查询结果比较多,外层查询不走索引
)
EXPLAIN SELECT * FROM testA WHERE scene !='NEWTEST' -- 包括不等于 <> not in not exists都是如此
-- 函数表达式是否走索引 函数表达式在=号右边或则静态的话就走索引,如果对字段操作函数的话就不走索引
EXPLAIN SELECT * FROM testA WHERE scene=CONCAT('ASK1','') AND NAME =CONCAT('简单样式','走一个') LIMIT 1 -- 走索引
EXPLAIN SELECT * FROM testA WHERE scene='ASK1' AND NAME ='简单样式走一个'
EXPLAIN SELECT * FROM testA WHERE scene='ASK1' AND NAME=LPAD('简单样式走一个哈哈',7,'') -- 走索引
EXPLAIN SELECT * FROM testA WHERE id=137+1; -- 走索引
EXPLAIN SELECT * FROM testA WHERE scene='ASK1' -- 搜索结果过多,不走索引
EXPLAIN SELECT * FROM testA WHERE scene=SUBSTR('NEWTEST',1,8) -- 走索引
EXPLAIN SELECT * FROM testA WHERE scene=SUBSTRING('NEWTEST123',1,7) -- 走索引
-- 运用在数据库字段上,其他函数不一一列举了
EXPLAIN SELECT * FROM testA WHERE SUBSTRING(scene,1,7)='NEWTEST' -- 不走索引
EXPLAIN SELECT * FROM testA WHERE id+1=128 -- 不走索引
EXPLAIN SELECT * FROM testA WHERE id=127; -- 走索引
EXPLAIN SELECT * FROM testA WHERE CONCAT(NAME,'1')='乐相思又恨相思1'; -- 不走索引
EXPLAIN SELECT * FROM testA WHERE LPAD(NAME,8,'')='乐相思又恨相思1'; -- 不走索引
EXPLAIN SELECT * FROM testA WHERE NAME >='乐相思又恨相思' AND NAME <='乐相思又恨相思M' -- 替代模糊查询like的优化
--hash索引和BTree索引
由于哈希查找比起B-Tree索引,其本身对于单行查询的时间复杂度更低,有了哈希索引后明显可加快单行查询速度。
但是哈希索引也有它自己的限制:
- 哈希索引只包含哈希值和行指针,而不存储字段值,所以不能使用索引中的值来避免读取行。不过,访问内存中的行的速度很快,所以大部分情况下这一点对性能的影响并不明显。
- 哈希索引数据并不是按照索引值顺序存储的,所以也就无法用于排序。
- 哈希索引也不支持部分索引列匹配查找,因为哈希索引始终是使用索引列的全部内容来计算哈希值的。例如,在数据列(A, B)上建立哈希索引,如果查询只有数据列A,则无法使用该索引。
- 哈希索引只支持等值比较查询,包括=、in()、<=>。不支持任何范围查询,例如where price > 100。
- 访问哈希索引的数据非常快,除非有很多哈希冲突。
- 如果哈希冲突很多的话,一些索引维护操作的代价也很高。
由上述劣势可分析,哈希索引一般适用于:不需要做排序、范围查询的需求。一般在BTree索引基础上,mysql可能对于其查询频繁的会在基础之上再加上hash索引
/*********************************************[表结构优化]********************************************************
1.在查询时,MYSQL只能使用一个索引,如果建立的是多个单列的普通索引,在查询时会根据查询的索引字段,从中选择一个
限制最严格的单例索引进行查询。别的索引都不会生效。所以在创建表的时候,想要多个索引都生效的话用组合索引
2.永远为每张表设置一个ID主键
3.使用ENUM而不是VARCHAR(原理:小的字段类型,偏移量就会小,效率会高很多)
ENUM 类型是非常快和紧凑的。在实际上,其保存的是 TINYINT,但其外表上显示为字符串。这样一来,用这个字段来做一些
选项列表变得相当的完美。 如果我们有一个字段,比如“性别”,“国家”,“民族”,“状态”或“部门”,我们知道这些字段的取
值是有限而且固定的,那么,我们应该使用 ENUM 而不是 VARCHAR
4.固定长度的表会更快
如果表中的所有字段都是“固定长度”的,整个表会被认为是 “static” 或 “fixed-length”。 例如,表中没有如下类型的字段:
VARCHAR,TEXT,BLOB。只要我们包括了其中一个这些字段,那么这个表就不是“固定长度静态表”了,这样,MySQL 引擎会用
另一种方法来处理。 固定长度的表会提高性能,因为MySQL搜寻得会更快一些,因为这些固定的长度是很容易计算下一个数
据的偏移量的,所以读取的自然也会很快。而如果字段不是定长的,那么,每一次要找下一条的话,需要程序找到主键。 并
且,固定长度的表也更容易被缓存和重建。不过,唯一的副作用是,固定长度的字段会浪费一些空间,因为定长的字段无论
我们用不用,他都是要分配那么多的空间。另外在取出值的时候要使用trim去除空格
5.垂直分割
“垂直分割”是一种把数据库中的表按列变成几张表的方法,这样可以降低表的复杂度和字段的数目,从而达到优化的目的。
6.越小的列会越快
对于大多数的数据库引擎来说,硬盘操作可能是最重大的瓶颈。所以,把我们的数据变得紧凑会对这种情况非常有帮助,因
为这减少了对硬盘的访问。 参看 MySQL 的文档 Storage Requirements 查看所有的数据类型。 如果一个表只会有几列罢了
(比如说字典表,配置表),那么,我们就没有理由使用 INT 来做主键,使用 MEDIUMINT, SMALLINT 或是更小的 TINYINT
会更经济一些。如果我们不需要记录时间,使用 DATE 要比 DATETIME 好得多
**************************************************************************************************************/
-- 查看索引
SHOW INDEX FROM testA;
SHOW KEYS FROM testA;
/*************************************[数据库存储引擎选择方面]*************************************************
在MYSQL中有两个存储引擎MyISAM和InnoDB,每个引擎都有利有弊。
MyISAM不支持事务,不支持数据行锁定,不支持外键约束,支持数据表锁定,表空间相对较小,支持全文索引,擅长搜索性能
InnoDB不支持全文索引,支持事务,支持数据行锁定,支持外键约束,支持表锁定,擅长事务安全性,查询性能偏低(阿里的
mysql服务默认用的InnoDB)
**************************************************************************************************************/
/*******************************************[数据库分层架构]***************************************************
通常一个大型应用的数据仓库分为三层架构:基础表->中间表->应用表,由于数据的敏感性,基础表一般禁止直接查询,想要实现上
层应用的即席查询或则预定于报表的效果,就需要从中间表进行汇总查询分析.那么接下来主要讨论下数据仓库为什么存在,有什么
用,以及其特性
这种分层架构主要用在企业数据分析上,其实就是数据仓库,数据仓库就是为支持企业决策而特别设计和建立的数据集合。
企业建立数据仓库是为了填补现有数据存储形式已经不能满足信息分析的需要。数据仓库理论中的一个核心理念就是:
事务型数据和决策支持型数据的处理性能不同
广义的说,基于数据仓库的决策支持系统由三个部件组成:
数据仓库技术,联机分析处理技术和数据挖掘技术,其中数据仓库技术是系统的核心,目前市场上比较流行的是阿里的odps在线流数据处理服务以及传统的TERADATA,EXDATA一体机的oracle服务
数据仓库通常处理逻辑:
1)收集和分析业务需求
数据仓库价值曲线
2)建立数据模型和数据仓库的物理设计
3)定义数据源
4)选择数据仓库技术和平台
5)从操作型数据库中抽取、净化、和转换数据到数据仓库
6)选择访问和报表工具
7)选择数据库连接软件
8)选择数据分析和数据展示软件
9)更新数据仓库
数据仓库特点:
1、数据仓库是面向主题的;操作型数据库的数据组织面向事务处理任务,而数据仓库中的数据是按照一定的主题域进行组织。主题是指用户使用数据仓库进行决策时所关心的重点方面,一个主题通常与多个操作型信息系统相关。
2、数据仓库是集成的,数据仓库的数据有来自于分散的操作型数据,将所需数据从原来的数据中抽取出来,进行加工与集成,统一与综合之后才能进入数据仓库;
数据仓库中的数据是在对原有分散的数据库数据抽取、清理的基础上经过系统加工、汇总和整理得到的,必须消除源数据中的不一致性,以保证数据仓库内的信息是关于整个企业的一致的全局信息。
数据仓库的数据主要供企业决策分析之用,所涉及的数据操作主要是数据查询,一旦某个数据进入数据仓库以后,一般情况下将被长期保留,也就是数据仓库中一般有大量的查询操作,但修改和删除操作很少,通常只需要定期的加载、刷新。
数据仓库中的数据通常包含历史信息,系统记录了企业从过去某一时点(如开始应用数据仓库的时点)到当前的各个阶段的信息,通过这些信息,可以对企业的发展历程和未来趋势做出定量分析和预测。
3、数据仓库是不可更新的,数据仓库主要是为决策分析提供数据,所涉及的操作主要是数据的查询;
4、数据仓库是随时间而变化的,传统的关系数据库系统比较适合处理格式化的数据,能够较好的满足商业商务处理的需求。稳定的数据以只读格式保存,且不随时间改变。
5、汇总的。操作性数据映射成决策可用的格式。
6、大容量。时间序列数据集合通常都非常大。
7、非规范化的。Dw数据可以是而且经常是冗余的。
8、元数据。将描述数据的数据保存起来。
9、数据源。数据来自内部的和外部的非集成操作系统
10.效率足够高,数据仓库的分析数据一般分为日、周、月、季、年等,可以看出,日为周期的数据要求的效率最高,要求24小时甚至12小时内,客户能看到昨天的数据分析。由于有的企业每日的数据量很大,设计不好
的数据仓库经常会出问题,延迟1-3日才能给出数据,显然不行的
11.数据质量。数据仓库所提供的各种信息,肯定要准确的数据,但由于数据仓库流程通常分为多个步骤,包括数据清洗,装载,查询,展现等等,复杂的架构会更多层次,
那么由于数据源有脏数据或者代码不严谨,都可以导致数据失真,客户看到错误的信息就可能导致分析出错误的决策,造成损失,而不是效益
12.扩展性。之所以有的大型数据仓库系统架构设计复杂,是因为考虑到了未来3-5年的扩展性,这样的话,未来不用太快花钱去重建数据仓库系统,就能很稳定运行。主要体现在数据建模的合理性,
数据仓库方案中多出一些中间层,使海量数据流有足够的缓冲,不至于数据量大很多,就运行不起来了
**************************************************************************************************************/
/*********************************************[sql优化]********************************************************
1.不要使用ORDER BY RAND()
MySQL会不得不去执行RAND()函数(很耗CPU时间),而且这是为了每一行记录去记行,然后再对其排序
2.避免使用SELECT *
客户端用一个单独的数据包将查询请求发送给服务器,所以当查询语句很长的时候,需要设置max_allowed_packet参数。
但是需要注意的是,如果查询实在是太大,服务端会拒绝接收更多数据并抛出异常。与之相反的是,服务器响应给用户
的数据通常会很多,由多个数据包组成。但是当服务器响应客户端请求时,客户端必须完整的接收整个返回结果,而不
能简单的只取前面几条结果,然后让服务器停止发送。因而在实际开发中,尽量保持查询简单且只返回必需的数据,减
小通信间数据包的大小和数量是一个非常好的习惯,这也是查询中尽量避免使用SELECT *以及加上LIMIT限制的原因之一
sql优化其实主要就围绕着索引和缓存以及系统损耗的方向去思考即可
**************************************************************************************************************/
-- 分页优化(当limit的数据达到数千万条的时候,查询百万页的数据效率问题)
SELECT COUNT(*) FROM student;
SELECT * FROM student LIMIT 4224560,10;-- 不优化的分页
INSERT INTO student(course) SELECT course FROM student LIMIT 0,2000000
SELECT id,student_name,sex,course FROM student WHERE id >= ( SELECT id FROM student LIMIT 4224560,1) LIMIT 0,10;-- 优化后的分页,利用了id的索引快速定位到要开始分页的那条数据
-- sql同时删除多张表的数据
DELETE help_center_entry,whelp_center_config,help_template
FROM help_center_entry
LEFT JOIN help_template
ON help_center_entry.tnt_inst_id=help_template.tnt_inst_id
LEFT JOIN whelp_center_config
ON help_center_entry.tnt_inst_id=whelp_center_config.tnt_inst_id
WHERE help_center_entry.tnt_inst_id='YVXNVICN';
-- 利用LIMIT 1取得唯一行,有时,当你要查询一张表是,你知道自己只需要看一行。你可能会去的一条十分独特的记录,
-- 或者只是刚好检查了任何存在的记录数,他们都满足了你的WHERE子句。
-- 在这种情况下,增加一个LIMIT 1会令你的查询更加有效。这样数据库引擎发现只有1后将停止扫描,而不是去扫描整个表或索引。
SELECT * FROM student WHERE course='计算机科学与技术1111' -- 0.004s
SELECT * FROM student WHERE course='计算机科学与技术1111' LIMIT 1 -- 7.5s
3.各种count计数效率的比较
count(*)包括了所有的列,相当于行数,在统计结果的时候,不会忽略列值为NULL (如果在表有索引或者主键的时候,数据库会优化选择索引进行count)
count(1)包括了忽略所有列,用1代表代码行,在统计结果的时候,不会忽略列值为NULL
count(列名)只包括列名那一列,在统计结果的时候,会忽略列值为空(这里的空不是只空字符串或者0,而是表示null)的计数,即某个字段值为NULL时,不统计。
执行效率上:
列名为主键,count(列名)会比count(1)快
列名不为主键,count(1)会比count(列名)快
如果表多个列并且没有主键,则 count(1) 的执行效率优于 count(*)
如果有主键,则 select count(主键)的执行效率是最优的
如果表只有一个字段,则 select count(*)最优。
4.in和exists的选择
ELECT * FROM A WHERE id IN (SELECT id FROM B);
等价于:1、SELECT id FROM B ----->先执行in中的查询
2、SELECT * FROM A WHERE A.id = B.id
以上in()中的查询只执行一次,它查询出B中的所有的id并缓存起来,然后检查A表中查询出的id在缓存中是否存在,如果存在则将A的查询数据加入到结果集中,直到遍历完A表中所有的结果集为止。
以下用遍历结果集的方式来分析IN查询
通过以上程序可以看出,当B表的数据较大时不适合使用in()查询,因为它会将B表中的数据全部遍历一次
例如:
1、A表中有100条记录,B表中有1000条记录,那么最多可能遍历100*1000次,效率很差
2、A表中有1000条记录,B表中有100条记录,那么最多可遍历1000*100此,内循环次数减少,效率大大提升
结论:IN()查询适合B表数据比A表数据小的情况,IN()查询是从缓存中取数据
2、EXISTS查询分析
语法:SELECT 字段 FROM table WHERE EXISTS(subquery);
SELECT * FROM a WHERE EXISTS(SELECT 1 FROM b WHERE B.id = A.id);
以上查询等价于:
1、SELECT * FROM A;
2、SELECT I FROM B WHERE B.id = A.id;
EXISTS()查询会执行SELECT * FROM A查询,执行A.length次,并不会将EXISTS()查询结果结果进行缓存,因为EXISTS()查询返回一个布尔值true或flase,它只在乎EXISTS()的查询中是否有记录,与具体的结果集无关。
EXISTS()查询是将主查询的结果集放到子查询中做验证,根据验证结果是true或false来决定主查询数据结果是否得以保存。
以下用遍历结果集的方式来分析EXISTS查询
从以上程序可以看出:
当B表的数据比A表的数据大时适合使用EXISTS()查询,因为它不用遍历B操作,只执行一次查询就OK了
例如:
1、A表有100条记录,B表有1000条记录,那么EXISTS()会执行100次去判断A表中的id是否与B表中的id相等.因为它只执行A.length次,可见B表数据越多,越适合EXISTS()发挥效果.
2、A表有10000条记录,B表有100条记录,那么EXISTS()还是执行10000次,此时不如使用in()遍历10000*100次,因为IN()是在内存里遍历数据进行比较,而EXISTS()需要查询数据库,我们都知道查询数据库所消耗的性能更高,而内存比较很快.
3、结论:
exists()适合B表比A表数据大的情况
当A表数据与B表数据一样大时,in与exists效率差不多,可任选一个使用
/*****************************************[数据库连接方面]*****************************************************
max_connections是指MySQL服务实例能够同时接受的的最大并发连接数。MySQL实际上支持最大连接数加一的算法,保障当连接
数用完的时候,超级管理员依然可以和服务端建立连接,进行管理。
max_user_connections设置指定账号的最大并发连接数。
max_connect_errors 当某台非法主机恶意连接MySQL服务端,遭到的错误达到设置值后,MySQL会解决来自该主机的所有连接。
但执行flush hosts后会清零。
Connection_errors_max_connections 当MySQL的最大并发数大于系统变量(show variables)中max_connections的最大并发数,
因此而被拒绝的次数,将会记录在这个变量里。如果Connection_error_max_connections值比较大,则说明当前系统并发比较高,
要考虑调大max_connections的值。
Connections表示MySQL从启动至今,成功建立连接的连接数,这个值是不断累加的。
Max_used_connections表示MySQL从启动至今,同一时刻并发的连接数,取得是最大值。如果这个值大于 max_connections则表明
系统经常处于高并发的状态,应该考虑调大最大并发连接数。
thread_cache_size 设置连接线程缓存的数目。这个缓存相当于MySQL线程的缓存池(thread cache pool),将空闲的连接线程放
入连接池中缓存起来,而非立即销毁。当有新的连接请求时,如果连接池中有空闲的连接,则直接使用。否则要重新创建线程。
创建线程是一个不小的系统开销。MySQL的这部分线程处理和Nginx 的线程处理有异曲同工之妙,以后介绍Nginx的线程处理时,会拿来做对比。
thread_handling 默认值是: one-thread-per-connection 表示为每个连接提供或者创建一个线程来处理请求,直至请求完毕,
连接销毁或者存入缓存池。当值是no-threads 时,表示在始终只提供一个线程来处理连接,一般是单机做测试使用的。
thread_stack stack 是堆的意思,由PHP 进程详解这篇博客,知道进程和线程都是有唯一的ID的,进程的ID系统会维护,二线
程的ID,则由具体的线程库区维护,当进程或者线程休眠的时候,进程的上下文信息要在内存中开辟出一块区域,保存进程的上
下文信息,以便于迅速唤醒程序。默认为MySQL的每个线程设置的堆栈大小为:262144/1024=256k
Thread_cached 当前线程池的线程数
Thread_connected 当前的连接数
Thread_created: 当前连接线程创建数, 如果这个值过高,可以调整threadcachesize 也就是调整线程缓存池的大小。
Thred_running: 当前活跃的线程数。
**************************************************************************************************************/
SHOW VARIABLES LIKE '%connect%'; -- 连接参数
SHOW STATUS LIKE '%connections%'; -- 连接状态
SHOW VARIABLES LIKE 'thread%'; -- 连接线程参数
SHOW STATUS LIKE 'Thread%'; -- 查看线程状态信息
转载来源:https://blog.csdn.net/weixin_38907570/article/details/80684961