sql 高级查询汇总

慢查询相关设置 查询慢查询日志的开启状态和慢查询日志储存的位置 show variables like '%quer%'; set global slowquerylog='ON'; set global longquerytime=1; 然后退出重新进入

开启开启未使用索引SQL过滤配置:【SQL语句记录到慢查询日志文件】 show variables like 'logqueriesnotusingindexes%'; set global logqueriesnotusingindexes =1;

MySQL中使用SHOW PROFILE命令分析性能的用法整理 https://www.jb51.net/article/74697.htm

一些状态查询语句 执行该SQL,使用show engine innodb status;查询锁状况 FLUSH status; //清空 SHOW GLOBAL STATUS; //查询系统状态SHOW [SESSION|GLOBAL] STATUS LIKE '%Status_name%';//里面的LOCK WAIT看等待线程

查看事务锁等待状态情况 select * from informationschema.innodblocks; select * from informationschema.innodblockwaits; select * from informationschema.innodb_trx;

查看SQL性能 SHOW VARIABLES LIKE '%profil%'; set profiling=1; SHOW PROFILES SHOW PROFILE FOR QUERY 1;

sql优化工具使用之explain EXPLAIN SELECT * FROM tbl_accounts WHERE user_id = 1087 OR user_id = 1463 https://www.jianshu.com/p/c5026c716946 https://www.cnblogs.com/zhp-king/p/7250810.html

计算表和索引占用空间 use mapp; OPTIMIZE TABLE tblorders; use informationschema; SELECT TABLENAME,concat(round(sum(INDEXLENGTH/1024/1024), 2),'MB') AS INDEXLENGTH , concat(round(sum(DATALENGTH/1024/1024), 2),'MB') AS DATALENGTH, concat(round(sum((DATALENGTH+INDEXLENGTH)/1024/1024), 2),'MB') AS 'DATALENGTH+INDEXLENGTH', TABLEROWS FROM TABLES WHERE TABLESCHEMA='mapp' AND TABLENAME='tbl_orders'

校验sql是行锁还是表锁 session1: START TRANSACTION; SELECT FROM tbl_accounts WHERE (user_id = 1470 OR user_id = 1471) AND (currency =1001 OR currency =1) LIMIT 4 FOR UPDATE COMMIT; seesion2: SELECT FROM tbl_accounts WHERE (user_id = 1470 OR user_id = 1471) AND (currency =1001 OR currency =1) LIMIT 4 FOR UPDATE SELECT FROM tbl_accounts WHERE user_id = 1471 LIMIT 4 FOR UPDATE SELECT FROM tbl_accounts WHERE user_id = 1474 LIMIT 4 FOR UPDATE (会卡住知道session1 commit)【如果session2 不加for update 的话,就算session2 不commit也是可以查询的,不过update之类的不行】 【实际上这个表的user_id currency 组成唯一索引,所以是行锁】 以下in 和or的效率也是一样的,使用了唯一索引,可以锁行 SELECT * FROM tbl_accounts WHERE (user_id IN(1470,1471)) AND (currency =1001 OR currency =1) LIMIT 4 FOR UPDATE

SELECT * FROM tbl_trade_messages WHERE state =2 FOR UPDATE 【对于此语句,尽管for uodate进行了行锁,在这个时候,其他session也是不能对表进行插入语句的时候如果存在state=2行(插入的state不等于2就可以), 也会阻塞的,因为存在间隙锁。如果插入的时候不存在state字段就可以插入 (如果state字段默认值是2的话也是要阻塞的)】

由于MySQL的行锁是针对索引加的锁,不是针对记录加的锁,所以虽然是访问不同行的记录, 但是如果是使用相同的索引键,是会出现锁冲突的。应用设计的时候要注意这一点。 session1 : select * from tabwithindex where id = 1 and name = '1' for update; session2 : select * from tabwithindex where id = 1 and name = '4' for update; (阻塞) +------+------+ | id | name | (id 为普通索引) +------+------+ | 1 | 1 | | 1 | 4 | +------+------+ https://blog.csdn.net/tanga842428/article/details/52748531 【强烈推荐阅读,含有间隙锁】 https://blog.csdn.net/qingsong3333/article/details/78024931 行级锁并不是直接锁记录,而是锁索引,如果一条SQL语句用到了主键索引,mysql会锁住主键索引;如果一条语句操作了非主键索引,mysql会先锁住非主键索引,再锁定主键索引。

查看死锁 deadlock SHOW ENGINE INNODB STATUS\G; //LATEST DETECTED DEADLOCK开始 项目中遇到的一个比较高级的死锁解决【见本目录 同一行不同索引争夺的死锁.txt】 技巧 要分析死锁,可以通过开启sql_log,查看完整的sql过程,绝对很有帮助 总结 解决的方案,尽量使用相同的索引加锁,而且id按相同顺序排序

Mysql不支持事务嵌套,第二个事务开始之前,会隐式地把第一个事务commit掉

在下面的例子中,检索值的数据类型与索引字段不同,虽然MySQL能够进行数据类型转换,但却不会使用索引, 从而导致InnoDB使用表锁。通过用explain检查两条SQL的执行计划,我们可以清楚地看到了这一点。 例子中tabwithindex表的name字段有索引,但是name字段是varchar类型的, 如果where条件中不是和varchar类型进行比较,则会对name进行类型转换,而执行的全表扫描。 mysql> alter table tabnoindex add index name(name); Query OK, 4 rows affected (8.06 sec) Records: 4 Duplicates: 0 Warnings: 0 mysql> explain select * from tabwithindex where name = 1 \G * 1. row * id: 1 selecttype: SIMPLE table: tabwithindex type: ALL possiblekeys: name key: NULL keylen: NULL ref: NULL rows: 4 Extra: Using where 1 row in set (0.00 sec) mysql> explain select * from tabwithindex where name = '1' \G * 1. row * id: 1 selecttype: SIMPLE table: tabwithindex type: ref possiblekeys: name key: name keylen: 23 ref: const rows: 1 Extra: Using where 1 row in set (0.00 sec)

索引使用【MySQL中Cardinality值的介绍】 按性别进行查询时,可取值一般只有M、F。因此SQL语句得到的结果可能是该表50%的数据(加入男女比例1:1)这时添加B+树索引是完全没有必要的。 相反,如果某个字段的取值范围很广,几乎没有重复,属于高选择性。则此时使用B+树的索引是最合适的。例如对于姓名字段,基本上在一个应用中不允许重名的出现 https://blog.csdn.net/shiyifei/article/details/51659364

隔离级别和脏读、不可重复读、幻读 (mysql默认RR隔离级别,存在间隙锁,可以解决幻读) https://blog.csdn.net/Somhu/article/details/78775198

优化分页查询 一般分页查询,通过创建覆盖索引能够比较好地提高性能。一个场景是”limit 1000,20”, 此时Mysql排序出前1020条数据后仅仅需要返回第1001到1020条记录,前1000条数据都被抛弃,查询和排序代价非常高 优化方式:可以增加一个字段lastpagerecord.记录上一页和最后一页的编号,通过(最合适的是主键id) explain select ...where lastpagerecord<... desc limt .. 如果排序字段出现大量重复字段,不适用这种方式进行优化

当只要一行数据时使用 LIMIT 1 在这种情况下,加上 LIMIT 1 可以增加性能。这样一样,MySQL数据库引擎会在找到一条数据后停止搜索,而不是继续往后查少下一条符合记录的数据。

优化查询缓存 MySQL的查询缓存对这个函数不起作用。所以,像 NOW() 和 RAND() 或是其它的诸如此类的SQL函数都不会开启查询缓存, 因为这些函数的返回是会不定的易变的。所以,你所需要的就是用一个变量来代替MySQL的函数,从而开启缓存。

在Join表的时候使用相同类型的字段,并将其索引 如果你要把 DECIMAL 字段和一个 INT 字段Join在一起,MySQL就无法使用它们的索引。 对于那些STRING类型,还需要有相同的字符集才行。(两个表的字符集有可能不一样)

假设kid 是表table 的 一个索引字段 且值不唯一 1.如果kid 有多个值为12的记录那么: update table set name=’feie’ where kid=12;
会锁表 2.如果kid有唯一的值为1的记录那么: update table set name=’feie’ where kid=1;
不会锁 总结:用索引字段做为条件进行修改时, 是否表锁的取决于这个索引字段能否确定记录唯一,当索引值对应记录不唯一,会进行锁表,相反则行锁。 https://www.cnblogs.com/paul8339/p/6877729.html

千万不要 ORDER BY RAND() 进行随机抽样 优化方法: 首先:SELECT MIN(id), MAX(id) FROM tablename 然后:$id=rand($min,$max); //通过rand返回刚才取到的最大id和最小id之间的一个id号。 最后:SELECT * FROM tablename WHERE id='$id' LIMIT 1 多条的话,1、WHERE id = '{$id['1']}' OR id = '{$id['2']}' OR ..... [最好优化成union] 2、SELECT id FROM tablename WHERE id > '$id' LIMIT 50 然后再在在结果中随机选择10个

索引列不能参与计算,保持列“干净” 比如fromunixtime(createtime) = ’2014-05-29’就不能使用到索引,原因很简单, b+树中存的都是数据表中的字段值,但进行检索时,需要把所有元素都应用函数才能比较,显然成本太大。 所以语句应该写成createtime = unixtimestamp(’2014-05-29’);

使用 procedure analyse()函数对表进行分析, 该函数可以对表中列的数据类型提出优化建议 select * from tbltrademessages procedure analyse();

聚簇索引 mysql中不支持直接允许选择那个索引做聚簇索引,innodb将通过主键聚集数据,如果没有定义主键, 则会选择一个唯一非空的索引代替,如果没有这样的索引,则会隐式定义一个主键

varchar ,char 总结 varchar:用于存储可变长字符串的列 ,需要额外1,2个字节存储长度,varchar(1000)需要1002字节。省空间,但不利于频繁更新(有额外开销) 适用于 字符串最大长度比平均长度大很多;列更新较少; char:定长;不易产生碎片;非常合适存储md5之类的; 【当然varchar不是设置越大越好,尽管对于小字符串而言实际存储空间开销不变,但是会消耗更多内存,特别是使用内存临时表进行排序或者操作】

MySQL 自身的优化 对 MySQL 自身的优化主要是对其配置文件 my.cnf 中的各项参数进行优化调整。 如指定 MySQL 查询缓冲区的大小, 指定 MySQL 允许的最大连接进程数等。

优化嵌套查询 子查询可以被更有效率的连接替代 explain select * from customer where customerid not in(select customerid from payment) 改: explain select * from customer a left join payment b on a.customerid=b.customerid where b.customer id is null 连接之所用更有效率是因为mysql不需要在内存中创建临时表来完成这个逻辑上需要两个步骤的查询工作

对查询进行优化,应尽量避免全表扫描,首先应考虑在 where 及 order by 涉及的列上建立索引。

应尽量避免在 where 子句中对字段进行 null 值判断,否则将导致引擎放弃使用索引而进行全表扫描, Sql 代码 : select id from t where num is null; 可以在 num 上设置默认值 0,确保表中 num 列没有 null 值,然后这样查询: Sql 代码 : select id from t where num=0; 尽量把所有的列设置为 NOT NULL,如果你要保存 NULL,手动去设置它,而不是把它设为默认值。

应尽量避免在 where 子句中使用!=或<>操作符,否则将引擎放弃使用索引而进行全表扫描。

应尽量避免在 where 子句中使用 or 来连接条件,否则将导致引擎放弃使用索引而进行全表扫描, Sql 代码 : select id from t where num=10 or num=20; 可以这样查询: Sql 代码 : select id from t where num=10 union all select id from t where num=20; 【实际测试发现如果有索引,还是会走索引的,不过普通索引是范围查询,EXPLAIN SELECT * FROM tbl_accounts WHERE user_id = 1087 OR user_id = 1463】 【另外科普下,Union 和 Union All的区别之一在于对重复结果的处理。Union All的结果集不会进行去重复】 【能用UNION ALL就不要用UNION】

in 和 not in 也要慎用,否则会导致全表扫描,如: Sql 代码 : select id from t where num in(1,2,3); IN、OR 子句常会使用工作表,使索引失效。如果不产生大量重复值,可以考虑把子句拆开。拆开的子 句中应该包含索引。

对于连续的数值,能用 between 就不要用 in 了: Sql 代码 : select id from t where num between 1 and 3;

如果你的数据只有你所知的少量的几个。最好使用 ENUM 类型

如果你想校验表里是否存在某条纪录,不要用count()那样效率很低,而且浪费服务器资源。可以用EXISTS代替。如: IF (SELECT COUNT() FROM tablename WHERE columnname = 'xxx') 可以写成: IF EXISTS (SELECT * FROM tablename WHERE columnname = 'xxx')

应尽量避免在 where 子句中对字段进行表达式操作, 这将导致引擎放弃使用索引而进行全表扫描。 Sql 代码 : select id from t where num/2=100; 可以这样查询: Sql 代码 : select id from t where num=100*2; 任何对列的操作都将导致表扫描,它包括数据库教程函数、计算表达式等等,查询时要尽可能将操作移 至等号右边。

并不是所有索引对查询都有效,SQL 是根据表中数据来进行查询优化的,当索引列有大量数据重复时, SQL 查询可能不会去利用索引, 如一表中有字段 *,male、female 几乎各一半,那么即使在 * 上建 了索引也对查询效率起不了作用。

定期优化表。 如果删除了表的一大部分,或者如果已经对含有可变长度行的表(含有 VARCHAR、BLOB或TEXT列的表)进行更多更改,则应使用OPTIMIZE TABLE命令来进行表优化。 这个命令可以将表中的空间碎片进行合并,并且可以消除由于删除或者更新造成的空间浪费,但OPTIMIZE TABLE 命令只对MyISAM、 BDB 和InnoDB表起作用。 例如: optimize table table_name 注意: analyze、check、optimize执行期间将对表进行锁定,因此一定注意要在MySQL数据库不繁忙的时候执行相关的操作。

mysql强制使用索引:force index(索引名或者主键PRI) select from table force index(PRI) limit 2;(强制使用主键) select from table force index(ziduan1index) limit 2;(强制使用索引”ziduan1index”) select * from table force index(PRI,ziduan1index) limit 2;(强制使用索引”PRI和ziduan1index”)

一个用户有多个兴趣和资源,需要去根据某用户这两个属性给全部人匹配度排序。。(加权) SELECT SUM(t.hsum),t.userid FROM (SELECT COUNT() 20 AS hsum,userid FROM hobbies WHERE hid IN (1,2,3) GROUP BY userid UNION ALL SELECT COUNT() 80 AS hsum,userid FROM resource WHERE rid IN (1,2,3) GROUP BY userid) AS t GROUP BY t.user_id 【UNION ALL 需要两次查询的字段一致】【in后加上order by field可以按in的顺序排序】

——MySQL多表查询合并结果和内连接查询 1、使用union和union all合并两个查询结果:select 字段名 from tablename1 union select 字段名 from tablename2; 注意这个操作必须保证两张表字段相同,字段数据类型也相同。另外,使用union的时候会去除重复(相同)的记录,而union all则不会。

So here is an example in mysql.

SELECT * FROM mytable UNION ALL SELECT * FROM mytable WHERE id IN (1,2) Then if you are using Laravel's eloquent like I am use this.

$queryh = HobbyUser::select(DB::raw('count() 20 as hsum'),'userid') $query = Class::whereIn('id', $array); Class::where('id','>=',0)->unionAll($query)->get();

            $offset=($page-1) * $per_page;
            $hobby_ids=array_pluck(DB::select('select hobby_id  from hobby_user where user_id='.$user->id),'hobby_id');
            $resource_ids=array_pluck(DB::select('select resource_id  from resource_user where user_id='.$user->id),'resource_id');

            if (!count($hobby_ids) && !count($resource_ids))
                return $this->toJson('请先完善 兴趣爱好 或者 资源 ');
            $user_ids=DB::select('SELECT t.user_id FROM (SELECT COUNT(*) *20 AS h_sum,user_id FROM hobby_user WHERE hobby_id IN ('.join(',',$hobby_ids).') GROUP BY user_id UNION ALL SELECT COUNT(*) *80 AS h_sum,user_id FROM resource_user WHERE resource_id IN ('.join(',',$resource_ids).') GROUP BY user_id) AS t GROUP BY t.user_id ORDER BY  t.h_sum DESC limit '.$offset.','.$per_page);
            $userlist=[];
            foreach ($user_ids as $user_idx){
                $userlist[]=User::where('id',$user_idx->user_id)->with('hobbies')->with('resources')
                    ->with(['resume'=>function ($queryx){
                        $queryx->with('education_exprience')->with('work_exprience');
                    }])->first();
            }
            $this->calDistant($userlist,$user->lat,$user->lng);
            return $this->toJson(0,'',$userlist);

===================================

参考链接 https://www.jianshu.com/p/3985b1f9d9b4 http://www.ihref.com/read-16422.html http://blog.itpub.net/21220384/viewspace-2140025/ https://searchdatabase.techtarget.com.cn/7-18321/ http://aoxueshou.blog.163.com/blog/static/1002357142013817515604/ http://www.cnblogs.com/yubinfeng/archive/2010/11/02/1867386.html

使用rand()抽样调查,随机抽取2个员工,查看其资料 mysql> select * from emp order by rand() limit 2; 不要这么写,效率不高,搜索order by RAND()可查看另一处写了优化方法。。。

查询结果的字段联合和重新命名 mysql> select concat(empid," ",empname) from emp;

统计男女职工数目:(GROUP BY语句分类) mysql> select empsex,count(*) from emp group by empsex;

查询班级信息,统计班级学生人数 SELECT ,(SELECT COUNT() FROM managerstudent WHERE classid=managerclass.id) AS studentnum FROM managerclass

查询某学校的所有班级及每个班级的学生人数 SELECT ,(SELECT COUNT() FROM managerstudent WHERE classid=managerclass.id) AS studentnum FROM managerclass WHERE manager_class.school_id=30

查询某学校的所有班级及每个班级的学生人数及指定日期的出勤人数 SELECT *,(SELECT COUNT(DISTINCT b.device_id)num FROM managerstudent a RIGHT JOIN xskattendance b ON a.device_id=b.deviceid WHERE classid=managerclass.id AND DATEFORMAT(b.time,'%Y-%m-%d') ='2016-05-26')attandanceNum,(SELECT COUNT(*) FROM managerstudent WHERE classid=managerclass.id) AS studentnum FROM managerclass WHERE manager_class.school_id=30

  1. 查找出符合条件的记录, 按userid asc, createtime desc 排序; select ord.userid, ord.money, ord.createtime from orders ord where ord.userid > 0 and createtime > 0 order by userid asc , createtime desc
  2. 将(1)中记录按userid分组, groupconcat(money); select t.userid, groupconcat( t.money ) moneys from (select ord.userid, ord.money, ord.createtime from orders ord where ord.userid > 0 and createtime > 0 order by userid asc , createtime desc) t group by userid userid moneys 1 100,50 2 200,100 [groupconcat()函数需要与group by语句在一起使用,才能得到需要的效果, 自定义分隔符:select id,groupconcat(name separator ';') from aa group by id; 自定义排序:select id,groupconcat(name order by name desc) from aa group by id;
    去除冗余name:select id,group
    concat(distinct name) from aa group by id; ]

  3. 这时, 如果用户有多个消费记录, 就会按照时间顺序排列好, 再利用 subStringindex 函数进行切分即可 select t.userid, substringindex(groupconcat( t.money ),',',1) lastestmoney from (select ord.userid, ord.money, ord.createtime from orders ord where ord.userid > 0 and createtime > 0 order by userid asc , createtime desc) t group by userid ;

mysql replace用法 1.replace into REPLACE INTO table1 (fileId,pName) VALUES ('8','222'),('6','bb') 此语句的作用是向表table中插入两条记录。如果主键fileId为1或2不存在

mysql数据库replace、regexp的用法 http://www.jb51.net/article/27997.htm 特别注意中文的话: SELECT * FROM table1 a WHERE a.city REGEXP '(呵){2}'

善用mysql的时间函数 UPDATE xsk_command SET begin_time=DATEADD(now(),INTERVAL 10 SECOND), sendtimes = 0 WHERE id=

MySQL性能优化的21个最佳实践 https://searchdatabase.techtarget.com.cn/7-18321/

30多条mysql数据库优化方法,千万级数据库记录查询轻松解决 http://www.ihref.com/read-16422.html `

© since 2020 Bajian All Rights Reserved. 
粤ICP备16116222号