【mysql】语句优化

一、常见通用的join查询

有些查询语句会出现的情况:性能下降sql慢,执行时间长,等待时间长
原因:
1、查询语句写的烂
2、索引失效
select * from user where name=’’ and email=’’;
单值索引:create index idx_user_name on user(name)
复合索引:create index idx_user_nameEmail on user(name,email)

3、关联查询太多join(设计缺陷或不得已的需求)
4、服务器调优及各个参数设置(缓冲/线程数等)

有些情况下,可以使用连接来替代子查询。因为使用join,MySQL不需要在内存中创建临时表。

sql执行顺序
机器读取顺序 from—> on—> join—> where—> group by—> having —>select —>order by —>limit
在这里插入图片描述在这里插入图片描述在这里插入图片描述在这里插入图片描述

在这里插入图片描述

二、索引

是什么:索引时帮助mysql高效获取数据的数据结构,可以理解为排好序的快速查找数据结构。
索引的目的:在与提高查询效率,可以类比字典。如果要查“MySQL”单词,可以根据索引定位到m字母,然后从下往上找到y字母,再找到剩下的sql。
在这里插入图片描述
一般来说索引本身很大,不可能全部存储在内存中,一般以文件的形式存储在磁盘上。
我们平常所说的索引,如果没有特别说明,都是指B树(多路搜索树,并不一定是二叉树)结构组织的索引。其中聚集索引,次要索引,覆盖索引,复合索引,前缀索引,唯一索引默认都是使用B+树搜索,统称索引。除了B+树这种类型的索引之外,还有哈希索引(hash index)等。

优势:提高数据检索的效率,降低数据库的IO成本。通过索引对数据进行排序,降低数据排序的成本,降低了CPU的消耗。
劣势
索引也是一张表,占用空间。
降低更新表速度,需要调整因更新带来的简直变化后的索引信息。
索引需要不停的优化个调整。

索引分类
单值索引:一个索引只包含单个列,一个表可以有多个单列索引。(一张表索引个数最好不要超过5个)

复合索引:即一个索引包含多个列(单值索引和复合索引都可以称为普通索引)。

唯一索引:索引列的值必须唯一,但允许有空值。
创建:
CREATE UNIQUE INDEX <索引的名字> ON tablename (列的列表);
修改表:
ALTER TABLE tablename ADD UNIQUE [索引的名字] (列的列表);
创建表的时候指定索引:
CREATE TABLE tablename ( […], UNIQUE [索引的名字] (列的列表) );
create table ddd(id int primary key auto_increment , name varchar(32) unique);

主键索引:是一种唯一索引,但是不允许为null。必须指定为“PRIMARY KEY”。
主键一般在创建表的时候指定,例如:
“CREATE TABLE tablename ( […], PRIMARY KEY (列的列表) ); ”
但是,我们也可以通过修改表的方式加入主键,例如:
“ALTER TABLE tablename ADD PRIMARY KEY (列的列表); ”

全文索引fulltext:MySql自带的全文索引只能用于数据库引擎为MYISAM的数据表中,是目前实现大数据搜索的关键技术,我们可以通过语句SELECT FROM MATCH AGAINST来在整个表中检索是否有匹配的。

//在title和content两个列上创建全文索引
CREATE TABLE articles (
       id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,
       title VARCHAR(200),
       body TEXT,
       FULLTEXT (title,body)
     )engine=myisam charset utf8;

//查询
SELECT * FROM article WHERE MATCH(title,content) AGAINST (‘查询字符串’);

查询索引
desc 表名; 不能显示索引名称
show index from 表名
show keys from 表名

索引性能分析Explain

是什么:使用explain关键字可以模拟优化执行sql查询语句,从而指导MySQL是如何处理你的sql语句的。分析你的查询语句或是表结构的性能瓶颈。

使用:explain+sql语句
执行计划包含的信息 :在这里插入图片描述
各个字段解释:

id: select查询的序列号,包含一组数字,表示查询中执行select子句或操作表的顺序。
三种情况:
id相同,执行顺序由上至下
id不同,如果是子查询,id的序号会递增,id值越大优先级越高,越被先执行。
id既有相同又有不同,id相同可以认为是一组,从上往下顺序执行,所有组中,id越大优先级越高,越先执行。
select_type: 查询的类型,主要用于区别普通查询/联合查询/子查询等复杂查询
type:访问类型排序
在这里插入图片描述
只显示查询使用了何种类型,最好到最差的依次为:
system——>const——>eq_ref——>ref——>range——>index——>all

all:将遍历全表找到匹配的行。
index:只遍历索引树,通常比all快。
range:只检索给定范围的行,使用一个索引来选择行。开始与索引某一点,不用扫描全部索引。
ref:非唯一性索引扫描,返回匹配某个单独值得所有行,但有时也会找到多个符合条件的行,所以属于查找和扫描的混合体。
eq_ref:唯一性索引扫描,对于每个索引建,表中只有一条与之匹配。常见于主键或唯一索引扫描。
const:表示通过索引一次就找到了,const用于比较primary key或者unique索引。
system:表只有一行记录(等于系统表),这是const类型的特例,平时不会出现。

possible_keys: 显示可能应用在这张表中的索引
key:实际使用的索引,如果为null,则没有使用索引。查询中若使用了覆盖索引,则该索引和查询的select字段重叠。
key_len:表示索引中使用的字节数,可通过该列计算查询中使用的索引长度。在不损坏精确度的前提下,越小越好。key_len显示的值为索引字段的最大可能长度,并非实际使用长度。
ref:显示索引的那一列被使用了,如果可能的话,是一个常数。那些列或常量被用来查找索引列上的值。
rows:根据表统计信息及索引选用情况,大致估算出找到所需的记录所需要读取的行数。
extra:包含不适合在其他列中显示但十分重要的额外信息(自行查询)。

索引优化
1、尽量索引全值匹配。
2、最佳左前缀法则。
如果索引了多列,查询从索引的最做前列开始并且不跳过索引中的列。
3、不在索引列上做任何操作。
4、存储引擎不能使用索引中范围条件右边的列。
5、尽量使用覆盖索引(减少使用select*)。
6、mysql在使用不等于的是有无法使用索引,会导致失效。
7、is null , is not null 也无法使用索引
8、like以通配符开头,mysql索引失效会变成全表扫描操作。
9、字符串不加单引号索引失效。
10、少用or,用它来连接时会索引失效。

三、查询优化

1、永远小表驱动大表
即小数据集驱动大数据集
在这里插入图片描述
2、order by 关键字优化

考虑在 where 及 order by 涉及的列上建立索引
在这里插入图片描述
3、group by关键字优化
在这里插入图片描述
使用group by 分组查询时,默认分组后,还会排序,可能会降低速度,在group by 后面增加 order by null 就可以防止排序.
explain select * from emp group by deptno order by null;

4、避免在 where 子句中对字段进行 null 值判断

应尽量避免在 where 子句中对字段进行 null 值判断,否则将导致引擎放弃使用索引而进行全表扫描,如:
select id from t where num is null

  • 最好不要给数据库留 NULL,尽可能的使用 NOT NULL 填充数据库。
  • 备注、描述、评论之类的可以设置为 NULL,其他的,最好不要使用 NULL。
  • 不要以为 NULL 不需要空间,比如:char(100) 型,在字段建立时,空间就固定了, 不管是否插入值(NULL 也包含在内),都是占用 100 个字符的空间的,如果是 varchar 这样的变长字段, null 不占用空间。
  • 可以在 num 上设置默认值0,确保表中 num 列没有 null 值,然后这样查询:
    select id from t where num = 0

四、mysql锁机制

:是计算机协调多个进程或线程并发访问某一资源的机制。

在数据库中,除传统的计算机资源的争用以外,数据也是一种供许多用户共享的资源。如何保证数据并发访问的一致性/有效性是所有数据库必须解决的一个问题。

锁的分类
1、从对数据操作的类型分
读锁:又叫共享锁,针对同一份数据,多个读操作可以同时进行而不会相互影响。
写锁:又称排斥锁,当前写操作没有完成前,它会阻断其他写锁的读锁。

2、从对数据操作的粒度分
表锁:
偏向MyISAM存储引擎,开销小,加锁快;无死锁;锁粒度大,发生所冲突的概率最高,并发度最低。

锁冲突的产生
由于共享锁与排他锁是互斥的,当一方拥有了某行记录的排他锁后,另一方就不能其拥有共享锁,同样,一方拥有了其共享锁后,另一方也无法得到其排他锁。所 以,当语句(1)、(2)同时运行时,相当于两个事务会同时申请某相同记录行的锁资源,于是会产生锁冲突。由于两个事务都会申请主键索引,锁冲突只会发生 在主键索引上。

手动添加表锁:lock table 表名字 read(write), 表名字2 read(write)
查看表上加过的锁:show open tables;
分析表锁定:show status like ‘table%’;
变量说明:
在这里插入图片描述
行锁:
偏向InNoDB存储引擎,开销大,加锁慢,会出现死锁,锁定粒度最小,发生锁冲突的概率最低,并发度也最高。

InNoDB与MyISAM的最大不同有两点:
一 是支持事务
二是采用了行级锁

并发事务处理可能带来的问题:
更新丢失
读脏数据
不可重复读
幻读

在这里插入图片描述
优化建议
1、尽可能让所有数据检索都通过索引来完成,避免无索引行锁升级为表锁。
2、合理设计索引,尽量减少锁的范围。
3、尽可能较少检索条件,避免间隙锁。
4、尽量控制事务大小,减少锁定资源量和时间长度。
5、尽可能低级别事务隔离。

五、慢查询

是什么:MySQL默认10秒内没有相应SQL结果,则为慢查询。
如何修改慢查询:

--查询慢查询时间
show variables like 'long_query_time';
--修改慢查询时间
set long_query_time=1; ---但是重启mysql之后,long_query_time依然是my.ini中的值

如何从一个大的项目中,迅速定位执行速度慢的语句。(定位慢查询):
使用show status使用show status查看MySQL服务器状态信息。

--mysql数据库启动了多少时间
show status like 'uptime';

--显示数据库的查询,更新,添加,删除的次数
show stauts like 'com_select'  
show stauts like 'com_insert' ...类推 update  delete

--取出当前窗口的执行
show [session|global] status like .... 
//如果你不写  [session|global] 默认是session 会话。如果你想看所有(从mysql 启动到现在,则应该 global)

--显示到mysql数据库的连接数
show status like  'connections '; 

--显示慢查询次数
show status like 'slow_queries';

如何将慢查询定位到日志中
在默认情况下,我们的mysql不会记录慢查询,需要在启动mysql的时候,指定记录慢查询才可以。

bin\mysqld.exe --safe-mode --slow-query-log [mysql5.5 可以在my.ini指定]
安全模式启动,数据库将操作写入日志,以备恢复。

bin\mysqld.exe –log-slow-queries=d:/abc.log [低版本mysql5.0可以在my.ini指定]
先关闭mysql,再启动, 如果启用了慢查询日志,默认把这个文件放在my.ini 文件中记录的位置。

#Path to the database root
datadir=" C:/ProgramData/MySQL/MySQLServer 5.5/Data/"

六、MySQL数据引擎

使用的存储引擎 myisam/ innodb/ memory

myisam 存储: 如果表对事务要求不高,同时是以查询和添加为主的,我们考虑使用myisam存储引擎. ,比如 bbs 中的发帖表,回复表.

INNODB 存储: 对事务要求高,保存的数据都是重要数据,我们建议使用INNODB,比如订单表,账号表.

MyISAM 和 INNODB的区别

  1. 事务安全(MyISAM不支持事务,INNODB支持事务)
  2. 查询和添加速度(MyISAM批量插入速度快)
  3. 支持全文索引(MyISAM支持全文索引,INNODB不支持全文索引)
  4. 锁机制(MyISAM时表锁,innodb是行锁)
  5. 外键 MyISAM 不支持外键, INNODB支持外键. (在PHP开发中,通常不设置外键,通常是在程序中保证数据的一致)

Memory 存储:比如我们数据变化频繁,不需要入库,同时又频繁的查询和修改,我们考虑使用memory,速度极快. (如果mysql重启的话,数据就不存在了)

存储引擎允许的索引类型
myisambtree
innodbbtree
memory/yeapHash,btree
  • 0
    点赞
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值