Mysql常用的sql语句以及sql优化,索引的优化的注意事项----COOKIE

常用的sql应用场景: 

   两个工具表: 

    user:                                                                                             subject: 

    

可以看到两个表  user 表  和 专业表   user中的  subject  关联到  subject     表    ,下面以此为场景来写sql语句;

 

 

 

1. 学生所属专业学费 大于500   的学生中人数最多的专业: 

解析:    

select t.subjectname ,count(*) from

(select id,subjectname from subject  where money >500) t inner join t_user u on t.id = u.subject  group by u.subject order by count(*) desc limit 1;

利用内链接对学生表进行过滤 选出合适的专业的学生(专业的学费大于500)   ,然后根据学生的专业在进行分组统计每个分组的人数进行排序选择出人数最多的一个组.

这个场景还有很多解法,这只是其中的一种,如果有更优化的解可以在下面评论;

比如：select s.subjectname,count(*) from  t_user u left join subject s  on u.subject = s.id where s.money >500 group by u.subject order by count(*) desc limit 1;

2. 找出每个专业中,最有钱的第一个学生(三种解法)    (假设每个同学手里的钱数都是不可重复的 ): 

 一.写进行排序在进行分组,这样分组后的第一组就是最有钱的一个:

  select * from (select * from t_user order by hasmoney desc limit 10000  ) t group by t.subject ;

1. 缺点很明显：Using temporary; Using filesort

 二.联合查询: 1.先查出每个组最有钱的钱数,然后利用exisits 选取;

select * from t_user u where EXISTS (select * from (select  max(hasmoney) as hasmoney from t_user group by subject) t where u.hasmoney=t.hasmoney);

 

三.左连接查询: 1.先选出每一个组中最有钱的钱数,然后利用左连接进行选取;

select * from  (select max(hasmoney) as hasmoney from t_user group by subject ) t left join t_user s on t.hasmoney =s.hasmoney ;

 

 

3. 查询整个t_user表中拥有的生活费大于平均值的学生的信息

select * from t_user where hasmoney>( select avg(hasmoney) from t_user ); //把整个表作为一个分组,查出平均值;

 

5.查询每个学生的生活费都超过30元的的专业有哪些?

 select * from t_user group by subject having min(hasmoney) >30 ;

 

 

场景二: 删除表中重复的行信息;

删除表中的冗余信息（相当于完全相同的信息）

 自动编号   学号   姓名 课程编号 课程名称 分数

1        2005001 张三 0001     数学    69

2        2005002 李四 0001      数学    89

3        2005001 张三 0001      数学    69

 

解析：

  多字段分组的实质： 就是利用多字段为一个整体的唯一性；

  所以这里要除去多字段相同的信息利用group by就可以解决；

 

 

1. 选出整体唯一的分组然后保留首行；
2. 然后删除除了首行的

Delete table where id not in (select id from table group by(xuehao,name ,kechengbiaohao,kechengmingcheng,fenshu));

 

 

球队拼接比赛:

一个叫 team 的表，里面只有一个字段name, 一共有4 条纪录，分别是a,b,c,d, 对应四个球队，现在四个球队进行比赛，用一条sql 语句显示所有可能的比赛组合.

你先按你自己的想法做一下，看结果有我的这个简单吗？

 

答：select a.name, b.name

from team a, team b 

where a.id< b.id

 

两个表之间相同key的替换: 

有两个表A 和B ，均有key 和value 两个字段，如果B 的key 在A 中也有，就把B 的value 换为A 中对应的value
这道题的SQL 语句怎么写？

update B b set b.value=(select max(a.value) from A a where b.key=a.key)
where exists(select 1 from A c where b.key=c.key)

UPDATE A a,(select a.`key`,b.`value` from A INNER JOIN B on a.`key`=b.`key`) b 
SET a.`value` = b.`value` WHERE a.`key` = b.`key`

use spring_jdbctemplate ;
SET SQL_SAFE_UPDATES = 0;  //如果where 后面buMySql运行在safe-updates模式下，该模式会导致非主键条件下无法执行update或者delete命令，执行命令SET SQL_SAFE_UPDATES = 0;修改下数据库模式
update a  t set  t.pw =(select t_password from bd h where  h.t_username =t.name)  where  exists (select * from bd  c where c.t_username =t.name );

 

 

 

 

 

数据库的优化:

 

数据库进行优化的思路：

索引的优化：

四种索引的使用场景：

PRIMARY, INDEX, UNIQUE 这3种是一类

PRIMARY 主键。 就是 唯一 且 不能为空。

INDEX 索引，普通的

UNIQUE 唯一索引。 不允许有重复。

FULLTEXT 是全文索引，用于在一篇文章中，检索文本信息的。

 

 

单个索引的情况 ( 也就是一个索引只有一个字段  ):

1.查询条件为 userid and mobile and billMonth

我们发现三个单列索引只有 userid 有效（位置为查询条件第一个），其他两个都没有用上。

那么为什么没有用上呢？按照我们的理解，三个字段都加索引了，无论怎么排列组合查询，应该都能利用到这三个索引才对！

其实这里其实涉及到了mysql优化器的优化策略！当多条件联合查询时，优化器会评估用哪个条件的索引效率最高！它会选择最佳的索引去使用，也就是说，此处userid 、mobile 、billMonth这三个索引列都能用，只不过优化器判断只需要使用userid这一个索引就能完成本次查询，故最终explain展示的key为userid。

 

 

 

 

联合索引： 一个索引包含多个数据列，index（字段1，字段二）

利用索引中的附加列，您可以缩小搜索的范围，但使用一个具有两列的索引 不同于使用两个单独的索引。复合索引的结构与电话簿类似，人名由姓和名构成，电话簿首先按姓氏对进行排序，然后按名字对有相同姓氏的人进行排序。如果您知道姓，电话簿将非常有用；如果您知道姓和名，电话簿则更为有用，但如果您只知道名不姓，电话簿将没有用处。

所以说创建复合索引时，应该仔细考虑列的顺序。对索引中的所有列执行搜索或仅对前几列执行搜索时，复合索引非常有用；仅对后面的任意列执行搜索时，复合索引则没有用处。

 

 

2.查询条件为 userid  or  mobile

 

这次把 and 换成 or，发现两个查询条件都用上索引了！

我们在网上可能常常看到有人说or会导致索引失效，其实这并不准确。而且我们首先需要判断用的是哪个数据库哪个版本，什么引擎？

比如我用的是mysql5.7版本，innodb引擎，在这个环境下我们再去讨论索引的具体问题。

关于or查询的真相是：

所谓的索引失效指的是：假如or连接的俩个查询条件字段中有一个没有索引的话,引擎会放弃索引而产生全表扫描。我们从or的基本含义出发应该能理解并认可这种说法，没啥问题。

 

此刻需要注意type类型为index_merge。

自从5.0版本开始引入了index_merge索引合并优化！也就是说，我们现在可以利用上多个索引去优化or查询了

 

index_merge作用：

1、索引合并是把几个索引的范围扫描合并成一个索引。

2、索引合并的时候，会对索引进行并集，交集或者先交集再并集操作，以便合并成一个索引。

3、这些需要合并的索引只能是一个表的。不能对多表进行索引合并。

 

index_merge应用场景：

 

1.对OR语句求并集，如查询SELECT * FROM TB1 WHERE c1="xxx" OR c2=""xxx"时，如果c1和c2列上分别有索引，可以按照c1和c2条件进行查询，再将查询结果合并（union）操作，得到最终结果；

 

2.对AND语句求交集，如查询SELECT * FROM TB1 WHERE c1="xxx" AND c2=""xxx"时，如果c1和c2列上分别有索引，可以按照c1和c2条件进行查询，再将查询结果取交集（intersect）操作，得到最终结果

3.对AND和OR组合语句求结果

 

重点：

 

多个单列索引在多条件查询时优化器会选择最优索引策略，可能只用一个索引，也可能将多个索引全用上！ 但多个单列索引底层会建立多个B+索引树，比较占用空间，也会浪费一定搜索效率，故如果只有多条件联合查询时最好建联合索引！

 

总结：

  最左前缀原则：

建立联合索引时要将常用得的字段放置在前面；

 

 

联合索引的本质： 

当创建**(a,b,c)联合索引时，相当于创建了(a)单列索引**，(a,b)联合索引以及**(a,b,c)联合索引**

想要索引生效的话,只能使用 a和a,b和a,b,c三种组合；a,c组合也可以，但实际上只用到了a的索引，c并没有用到！

只适用于and ,把 and 换成 or，发现联合所索引无效！

 

优化注意： 

1. 避免在where子句中使用or来连接条件,因为如果俩个字段中有一个没有索引的话,引擎会放弃索引而产生全表扫描

 

 

 

应尽量避免在 where 子句中对字段进行表达式操作，这将导致引擎放弃使用索引而进行全表扫描。如：

select id from t where num/2=100

　　应改为:

select id from t where num=100*2

 

 

 

 

 

Sql语句的优化:

 

EXPLAIN查看ｓｑｌ的执行效果：

EXPLAIN : 常用explain来查看sql语句的使用情况:

Type : 链接类型；一个好的sql语句至少要达到range级别，避免出现all；

explain分析SQL    Explain select * from emp where ename=“zrlcHd”会产生如下信息：

select_type:表示查询的类型。

table:输出结果集的表

type:表示表的连接类型

possible_keys:表示查询时，可能使用的索引

key:表示实际使用的索引
key_len:索引字段的长度
rows:扫描出的行数(估算的行数)

Extra:执行情况的描述和说明

 

 

  1.   尽量少在where语句中添加 <>!= NULL　　的相关判定　　　NULL可以进行默认为0；

 

 

 

  2.区分in ， exists ， not in ，not exists 的使用

一: 先在一个表中B查询出符合条件的B数据,然后放入一个容器中存储,然后再去查询A表中id时候在这个容器中存在;适合查询内表比较小的情况;--------in

 slect  * from   A  where  id  in  select  number  from  B ;

二 ,在A表中逐条查看,看数据在B表中是否满足; 适合外表比较小的情况;--------exist;

 Slect  * from  A where exists (select * from B where A.id =B.number)

 

 

利用left  join   来替换  not exists和not in  或者  inner join 替换 in 和  exist :

in：

select  *  from B b inner join A a on b.number =a.id  ; 

not in :  

原SQL语句：

select colname … from A表 where a.id not in (select b.id from B表)

高效的SQL语句：

select colname … from A表 Left join B表 on  a.id = b.id where b.id is null

 

关于Join的优化: 

关于join 优化：

使用inner join，避免left join：

 

参与联合查询的表至少为2张表，一般都存在大小之分。如果连接方式是inner join，在没有其他过滤条件的情况下MySQL会自动选择小表作为驱动表，但是left join在驱动表的选择上遵循的是左边驱动右边的原则，即left join左边的表名为驱动表。

 

巧用STRAIGHT_JOIN：

inner join是由MySQL选择驱动表，但是有些特殊情况需要选择另个表作为驱动表，比如有group by、order by等「Using filesort」、「Using temporary」时。STRAIGHT_JOIN来强制连接顺序，在STRAIGHT_JOIN左边的表名就是驱动表，右边则是被驱动表。在使用STRAIGHT_JOIN有个前提条件是该查询是内连接，也就是inner join。其他链接不推荐使用STRAIGHT_JOIN，否则可能造成查询结果不准确。

 

 

 

分页查询sql 的优化：

优化的方法如下：可以取前一页的最大行数的id，然后根据这个最大的id来限制下一页的起点。比如此列中，上一页最大的id是866612。SQL可以采用如下的写法：

select id,name from product where id> 866612 limit 20

 

 

 

 

进行模糊查询的时候建立的索引失效，那麽如何解决这个问题呢？

使用全文索引；

在我们查询中经常会用到select id,fnum,fdst from dynamic_201606 where user_name like '%zhangsan%'; 。这样的语句，普通索引是无法满足查询需求的。庆幸的是在MySQL中，有全文索引来帮助我们。

创建全文索引的SQL语法是：

ALTER TABLE `dynamic_201606`  ADD  FULLTEXT INDEX  `idx_user_name`    (`user_name`);

使用全文索引的SQL语句是：

select id,fnum,fdst from dynamic_201606 where match(user_name) against('zhangsan'  in boolean  mode);

注意：在需要创建全文索引之前，请联系DBA确定能否创建。同时需要注意的是查询语句的写法与普通索引的区别

 

 

 

数据结构的优化：

         范式的优化： 避免表结构的冗余；

         拆分表： 分区将数据在物理层上分开，保存在不同磁盘的文件中，可以按月进行分区；

               垂直拆分：A表放在server 0    B表放置在server1    ；

                    好处： 缓解了表与表之间的IO竞争

　         　　坏处：单表中数据量增加的压力

             水平拆分  ： 将一个表中的数据进行拆分；

                   好处：缓解单表中数据量增加的压力

              　 坏处： 表与表之间的IO竞争没有解决

 

 

 

 

Mylsam 和 InnoDB数据引擎 : 

Mylsam: 

1. MyIASM是MySQL默认的引擎，但是它没有提供对数据库事务的支持，也不支持行级锁和外键，因此当INSERT(插入)或UPDATE(更新)数据时即写操作需要锁定整个表，效率便会低一些。

 

MyIsam 存储引擎独立于操作系统，也就是可以在windows上使用，也可以比较简单的将数据转移到linux操作系统上去。

 

 

 

适用场景：

 

1）查询速度很快，如果数据库insert和update的操作比较多的话比较适用。

2）MyISAM极度强调快速读取操作。

3）MyIASM中存储了表的行数，于是 SELECT COUNT(*) FROM TABLE  时只需要直接读取已经保存好的值而不需要进行全表扫描。如果表的读操作远远多于写操作且不需要数据库事务的支持，那么MyIASM也是很好的选择。

 

缺点：

就是不能在表损坏后恢复数据。（是不能主动恢复）

 

 

InnoDB:

1. InnoDB是一个事务型的存储引擎。

Innodb引擎提供了对数据库ACID事务的支持，并且实现了SQL标准的四种隔离级别，关于数据库事务与其隔离级别的内容请见数据库事务与其隔离级别这类型的文章。

2.该引擎还提供了行级锁和外键约束

它的设计目标是处理大容量数据库系统，它本身其实就是基于MySQL后台的完整数据库系统，MySQL运行时Innodb会在内存中建立缓冲池，用于缓冲数据和索引。但是该引擎不支持FULLTEXT类型的索引，而且它没有保存表的行数，当SELECT COUNT(*) FROM TABLE时需要扫描全表。当需要使用数据库事务时，该引擎当然是首选。由于锁的粒度更小，写操作不会锁定全表，所以在并发较高时，使用Innodb引擎会提升效率。但是使用行级锁也不是绝对的，如果在执行一个SQL语句时MySQL不能确定要扫描的范围，InnoDB表同样会锁全表。

//这个就是select锁表的一种，不明确主键。增删改查都可能会导致锁全表，在以后我们会详细列出。

SELECT * FROM products WHERE name='Mouse' FOR UPDATE;

 

适用场景：

1）经常更新的表，适合处理多重并发的更新请求。

2）支持事务。

3）可以从灾难中恢复（通过bin-log日志等）。

4）外键约束。只有他支持外键。

5）支持自动增加列属性auto_increment。

 

MySQL官方对InnoDB的讲解：

1）InnoDB给MySQL提供了具有提交、回滚和崩溃恢复能力的事务安全（ACID兼容）存储引擎。

2）InnoDB锁定在行级并且也在SELECT语句提供一个Oracle风格一致的非锁定读，这些特色增加了多用户部署和性能。没有在InnoDB中扩大锁定的需要，因为在InnoDB中行级锁定适合非常小的空间。

3）InnoDB也支持FOREIGN KEY强制。在SQL查询中，你可以自由地将InnoDB类型的表与其它MySQL的表的类型混合起来，甚至在同一个查询中也可以混合。

4）InnoDB是为处理巨大数据量时的最大性能设计，它的CPU效率可能是任何其它基于磁盘的关系数据库引擎所不能匹敌的。

5） InnoDB被用来在众多需要高性能的大型数据库站点上产生。