返回
基础
分类

可以通过like语句来查询一些特定的内容,来指定所要使用到引擎

日期: 2020-01-02 08:08 浏览次数 : 75

mysql处理存在则更新,不存在则插入(多列唯一索引)

[toc]

概述

mysql5.5之前默认存储引擎是MyISAM,5.5之后改为InnoDB。若要修改默认引擎,可以修改配置文件中的default-storage-engine。可以通过show engines来查看当前数据库支持的引擎。使用select engine from information_schema.engines where transactions = 'yes';来查看哪些引擎支持事务。在创建表到时候通过engine=...或type=...来指定所要使用到引擎。

 

必赢手机登录网址 1

MyISAM

  1. 它不支持事务,也不支持外键,其优势是访问的速度快,对事务完整性没有要求的或者以select/insert为主的应用基本上可以使用这个引擎来创建表。

  2. 每个MyISAM在磁盘上都有3个文件,其文件名都与表名相同,但扩展名是:

    • .frm(表定义)
    • .MYD(MYDate:存储数据)
    • .MYI(MYIndex:存储索引)
  3. .MYD文件和.MYI文件可以放置在不同的目录中,通过 data directory 和index directory语句指定。

  4. MyISAM类型的表可能会损坏,可以使用CHECK TABLE语句来检查MyISAM表的健康,并用REPAIR TABLE语句修复一个损坏到MyISAM表。

  5. MyISAM支持3种不同的存储格式:

    • 必赢手机登录网址 ,静态(固定长度)表
    • 动态表
    • 压缩表

    在静态表中,如果需要保存的内容后面本来就带有空格,那么在返回结果的时候会去掉公共的。

    在动态表中,记录不固定,优点是占用空间相对比较少,缺点是频繁的更新和删除记录会产生碎片,需要定期执行optimize table 来改善性能。

    在压缩表中,由myisampack工具创建,占据非常小的磁盘空间。因为每个记录都被单独压缩的。

mysql处理某个唯一索引时存在则更新,不存在则插入的情况应该是很常见的,网上也有很多类似的文章,我今天就讲讲当这个唯一的索引是多列唯一索引时可能会遇到的问题和方法。

mysql优化

InnoDB

  1. InnoDB支持事务安全,对比MyISAM引擎,InnoDB写的效率差一些,并且会占据更多的磁盘空间。
  2. InnoDB自动增长列可以手工插入,但是插入的值是空或者0,则实际插入的将是自动增长后的值。可以使用last_insert_id()查询当前线程最后插入记录使用的值。可以通过alert table *** auto_increment=n;语句强制设置自动增长值。
  3. 对于InnoDB表,自动增长列必须是索引。如果是组合索引,也必须是组合索引的第一列,但是对于MyISAM表,自动增长列可以是组合索引的其他列,这样插入记录后,自动增长列是按照组合索引到前面几列排序后递增的。
  4. MySQL支持外键的存储引擎只有InnoDB,在创建外键的时候,父表必须有对应的索引,子表在创建外键的时候也会自动创建对应的索引。在创建索引的时候,可以指定在删除、更新父表时,对子表进行的相应操作,包括restrict、cascade、set null和no action。其中restrict和no action相同,是指限制在子表有关联的情况下,父表不能更新;casecade表示父表在更新或删除时,更新或者删除子表对应的记录;set null 则表示父表在更新或者删除的时候,子表对应的字段被set null。当某个表被其它表创建了外键参照,那么该表对应的索引或主键被禁止删除。可以使用set foreign_key_checks=0;临时关闭外键约束,set foreign_key_checks=1;打开约束。
  5. InnoDB存储表和索引有如下两种方式:
    • 使用共享表空间存储。
    • 使用多表空间存储。

 

一 SQL语句优化

MEMORY

  1. memory使用存在内存中的内容来创建表。每个MEMORY表实际对应一个磁盘文件,格式是.frm。MEMORY类型的表访问非常快,因为它到数据是放在内存中的,并且默认使用HASH索引,但是一旦服务器关闭,表中的数据就会丢失,但表还会继续存在。
  2. 每个MEMORY表中放置到数据量的大小,受到max_heap_table_size系统变量的约束,这个系统变量的初始值是16M,同时在创建MEMORY表时可以使用MAX_ROWS子句来指定表中的最大行数。
  3. memory主要用于那些内容变化不频繁的代码表,或作为统计操作的中间结果表。

方法一:

1.1 使用 show status 命令了解sql执行频率

mysql> show session status 查询当前连接统计结果  
mysql> show global status 查询自数据库上次启动至今统计结果  

或者在操作系统的终端执行

shell> mysqladmin extended-status 

可以通过like语句来查询一些特定的内容

mysql> show global status like "Com_%";

上次启动以来的每个Com_xxx语句执行的次数

Com_select: 执行select查询的次数  
Com_insert: 执行insert的次数  
Com_update: 执行update的次数  
Com_delete: 执行delete的次数  

以上针对所有存储引擎表操作记录,专门针对某一些存储引擎的如下:

Innodb_rows_read  
Innodb_rows_insert  
Innodb_rows_update  
Innobd_rows_delete  

通过上面这些数据的比较可以判断数据库是写为主,还是查询为主

事务信息 可通过

Com_commit

Com_rollback

来了解提交和回滚情况。如回滚操作很频繁,说明应用编写存在问题。

其他重要参数,可展示数据库基本情况句子:

Connections:  试图连接Mysql数据库的次数  
Uptime:        服务器工作时间  
Slow_queries: 慢查询的次数  

MERGE

  1. merge存储引擎是一组MyISAM表的组合,这些MyISAM表结构必须完全相同,MERGE表中并没有数据,对MERGE类型的表可以进行查询、更新、删除的操作,这些操作实际上是对内部的MyISAM表进行操作。
  2. 对于对MERGE表进行的插入操作,是根据INSERT_METHOD子句定义的插入的表,可以有3个不同的值,first和last值使得插入操作被相应的作用在第一个或最后一个表上,不定义这个子句或者为NO,表示不能对这个MERGE表进行插入操作。
  3. 可以对MERGE表进行drop操作,这个操作只是删除MERGE表的定义,对内部的表没有任何影响。
  4. MERGE在磁盘上保留2个以MERGE表名开头文件:.frm文件存储表的定义;.MRG文件包含组合表的信息,包括MERGE表由哪些表组成,插入数据时的依据。可以通过修改.MRG文件来修改MERGE表,但是修改后要通过flush table刷新。
  5. merge表与分区表的区别是:merge表不会智能的将记录写到对应的表中,而分区表可以的。

使用

1.2 定位执行效率比较低的sql语句

两种方法:

总结

仍一张表作为总结:

必赢手机登录网址 2

mysql常用存储引擎

 INSERT INTO ON ... DUPLICATE KEY UPDATE ...

1)慢查询日志定位

用--log-slow-queries[=filename]选项启动时,mysqld写一个包含所有执行时间超过long_query_time秒得sql语句的日志文件。

2)show processlist

查看MySQL在进行的线程,包括线程状态,是否锁表等,可实时查看sql执行情况,并对一些锁表操作优化。

表的创建如下:

1.3 通过Explain分析低效SQL执行计划

通过上面步骤,查到低效sql语句后,可通过 explaindesc命令获取mysql如何执行select语句信息,如表连接。

explain 
select sum(money) 
from sales a, company b 
where a.company_id = b.id and a.year = 2006  


*************************** 1. row *****************
           id: 1  
  select_type: SIMPLE  
        table: user  
         type: system  
possible_keys: NULL  
          key: NULL  
      key_len: NULL  
          ref: NULL  
         rows: 0  
        Extra: const row not found  
1 row in set (0.00 sec)  

ERROR:  
No query specified  

每一列的含义说明

select_type: 
表示select的类型
(SIMPLE=>简单表,不使用表连接或者子查询,PRIMARY=>主查询,
外层的查询,UNION=>UNION中的第二个或者后面的查询语句,
SUBQUERY=>子查询中的第一个select)

table: 输出结果集的表 

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

key: 表示实际使用的索引

key_len: 索引字段的长度

rows; 扫描的行的数量

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

type: 
表示表的连接类型,性能有好到差的链接类型为:  
   system=>只有一行,也就是常量表;  
   const=>单表中最多有一个匹配行,例如primary key或者unique index;  
   eq_ref=>对于前面的每一行,在此表中只查询一条记录,简单来说,就是多表连接中使用primary key或者unique index;   
   ref=>与eq_ref类似,区别在于不是使用primay key或者unique index而是使用普通的索引;  
   ref_or_null=>与ref类似,区别在于条件中包含对null的查询;  
   index_merge=>索引合并优化;  
   unique_subquery=>in的后面是一个查询主键字段的子查询;  
   index_subquery=>与unique_subquery类似,区别在于in的后面是查询非唯一索引字段的子查询;  
   range=>单表中的查询范围;  
   index=>对于前面的每一行,都通过查询索引来得到数据;  
   all=>对于前面的每一行,都通过全表扫描来得到数据  
[sql] 
CREATE TABLE `test_table` (  
  `id`  int(11) NOT NULL AUTO_INCREMENT ,  
  `var1`  varchar(100) CHARACTER SET utf8 DEFAULT NULL,  
  `var2`  tinyint(1) NOT NULL DEFAULT '0',  
  `var3`  varchar(100) character set utf8 default NULL,  
  `value1`  int(11) NOT NULL DEFAULT '1',  
  `value2`  int(11) NULL DEFAULT NULL,  
  `value3`  int(5) DEFAULT NULL,  
  PRIMARY KEY (`Id`),  
  UNIQUE INDEX `index_var` (`var1`, `var2`, `var3`)  
) ENGINE=MyISAM DEFAULT CHARACTER SET=latin1 AUTO_INCREMENT=1;  

二 索引问题

 

2.1 索引存储分类

MyISAM存储索引  表数据 和 索引 自动分开存储,各自独立文件 
InnoDB存储引擎  表数据 和 索引 存储在同一表空间,但可有多个文件组成 
Mysql存储类型只有两种,BTREE和HASH,具体情况和表的存储引擎有关 
MyISAM和InnoDb存储引擎 都支持 BTREE索引 
MEMORY/HEAP存储引擎 支持HASH,BTREE索引 

mysql不支持函数索引,但是能对队列的前面的某一部分进行索引,例如name字段,可以只取name的前四个字符来进行索引。 

 

2.2 使用索引

对相关列使用索引是提高select性能的最佳途径。

使用索引的条件:

a、查询条件中有索引关键字,
b、多列索引只有查询条件使用了多列关键字最左边的前缀时,才可以使用索引,否则将不能使用索引。

其中该表中var1、var2和var3完全相同的记录只能有一条,所以建了一个多列唯一索引index_var,这样一来我们就可以使用  INSERT INTO ON ... DUPLICATE KEY UPDATE ... 来实现插入数据时存在则更新,不存在则插入的功能了,如下:

2.2.1 使用索引

一下情况中会使用到索引:

 (1) 多列索引,只要查询的条件中用到了最左边的列,索引一般就会被使用 
 (2) 对于使用like查询,后面如果是常量,只有%号不在第一个字符时,
    索引才可能被用到 比如 like "%3" 不会用索引, like "3%"就会走索引 
 (3) 对大文本进行搜索的时候,使用全文索引,而不是使用 like '%...%' 
 (4) 如果列名是索引,使用column_name is null将使用索引, 
  例如: select * from aaa where name is null(name是索引列) 
[sql] 
INSERT INTO `test_table`   
(`var1`, `var2`, `var3`, `value1`, `value2`, `value3`) VALUES   
('abcd', 0, 'xyz', 1, 2, 3)   
ON DUPLICATE KEY UPDATE `value1` = `value1` + 1 AND   
`value2` = `value2` + 2 AND `value3` = `value3` + 3;  

2.2.2 存在索引但不使用

在下列情况下,虽然mysql存在索引,但是并不会使用到索引

 (1)如果Mysql估计使用索引比全表扫描更慢,则不使用索引。
  例如如果列key_1 均匀分布在1和100之间,那么查询 select * from table where key_1 > 1 and key_1 < 90;

 (2)如果使用MEMPRY/HEAP表并且,where条件中不使用“=”进行索引列,
    那么不会用到索引。heap表只有在使用“=”的时候,才使用索引

 (3)用or隔开的条件,如果or前面的列中有索引,而后面的列中没有索引,
    那么涉及的索引都不会被用到(or中有一个条件中的列没有索引就用不到索引)

 (4)如果不是索引列的第一部分(复合索引的第一部分) 
 (5)如果like是%开始的

 (6)如果列类型是字符串,那么一定记得在where条件中把字符常量值用引号引起来,
    否则即便是这个列上有索引,也不用用到(比如name字段是字符串的,却写了name=123。要改成“123”) 

 

2.3 查看索引使用情况

Handler_read_key

代表一个行被索引值读的次数。
如果索引正在工作,值将很高,很低表明增加索引性能改善不高。

Handler_read_rnd_next

代表数据文件中读下一行的请求数。

查看方法:

show status like 'Handler_read%

值高则查询效率低,应建立索引补救。
如果正在进行大量的表扫描,Handle_read_rnd_next的值较高,则通常说明表索引不正确或者写入的查询没有利用索引。

该条插入语句的含义是:向test_table表中插入,如果不存在val1 = 'abcd',val2 = 0, val3 = ‘xyz’的记录,那就插入

3 两个简单优化方法

val1 = 'abcd',val2 = 0, val3 = ‘xyz’,value1 = 1, value2 = 2, value3 = 3的记录,

3.1 定期分析表和检查表

如果存在,那就更新value1的值为value1+1,更新value2的值为value2+2,更新value3的值为value3+3。

1)分析表语法

analyze [local | no_write_to_binlog] table tab1_name [, ta1_name] ...

分析和存储表的关键字分布,得到更准确地统计信息,使sql正确执行。

如果用户感觉实际执行计划并不是与预期的执行计划,执行一次分析表可能会解决问题。

在分析期间,使用一个读取锁对表进行锁定,这对于MyISAM,BDB和InnoDb表有作用。
对于MyISAM表,与使用myisamchk -a 相当。

mysql> analyze table user;

+------------+---------+----------+----------+  
| Table      | Op      | Msg_type | Msg_text |  
+------------+---------+----------+----------+  
| mysql.user | analyze | status   | OK       |  
+------------+---------+----------+----------+  
1 row in set (0.05 sec)  

 

2)检查表语法

check table tab1_name [,tab1_name] ... [option] .. 
option = {QUICK | FAST | MEDIUM | EXTENDED | CHANGED}

作用:
检查一或多表错误。对MyISAM和InnoDB表有用,对MyISAM表,关键字统计数据被更新。

例如:

mysql> check table user;  
+------------+-------+----------+----------+  
| Table      | Op    | Msg_type | Msg_text |  
+------------+-------+----------+----------+  
| mysql.user | check | status   | OK       |  
+------------+-------+----------+----------+  
1 row in set (0.00 sec)  

check table也可检查视图错误,如:视图定义被引用表不存在。

这样,的确是没有问题的,但是,如果表的创建如下:

3.2 定期优化表

语法:

optimize [local | no_write_to_binlog] table tab1_name [, tab1_name] ... 

适用范围:

a、删除了表的一部分 
b、对含有可变长度行表(varchar,blob,text列的表)进行了很多更改。 

作用:
将表空间碎片合并,消除删除或者更新造成的空间浪费。只适用MyISAM,BDB和InnoDb表。

mysql> optimize table user;  
+------------+----------+----------+----------+  
| Table      | Op       | Msg_type | Msg_text |  
+------------+----------+----------+----------+  
| mysql.user | optimize | status   | OK       |  
+------------+----------+----------+----------+  
1 row in set (0.01 sec)  
[sql] 
CREATE TABLE `test_table` (  
  `id`  int(11) NOT NULL AUTO_INCREMENT ,  
  `var1`  varchar(1024) CHARACTER SET utf8 DEFAULT NULL,  
  `var2`  tinyint(1) NOT NULL DEFAULT '0',  
  `var3`  varchar(1024) character set utf8 default NULL,  
  `value1`  int(11) NOT NULL DEFAULT '1',  
  `value2`  int(11) NULL DEFAULT NULL,  
  `value3`  int(5) DEFAULT NULL,  
  PRIMARY KEY (`Id`),  
  UNIQUE INDEX `index_var` (`var1`, `var2`, `var3`)  
) ENGINE=MyISAM DEFAULT CHARACTER SET=latin1 AUTO_INCREMENT=1;  

4 常用SQL优化

 

4.1 大批量插入数据优化

当用load命令导入数据的时候,适当的设置可以提高导入的速度

注意:var1和var3的最大长度由100变成了1024,此时执行该创建语句时会报如下错误:

4.1.1 MyISAM引擎表插入大数据

alter table tab_name disable keys;  
load the data  
alter table tab_name enable keys;  

以上是打开或者关闭MyISAM表非唯一索引的更新。

注意:

导入非空数据表,上面方法很有效,但导入空表,索引是数据导入完毕之后才去创建的,所以没有影响。

[sql] 
Specified key was too long; max key length is 1000 bytes  

4.1.2 对InnoDB表数据导入大数据

 (1)InnoDB类型表按主键顺序存储,故导入数据按主键顺序排列,可有效提高导入效率; 
 (2)导入数据前,关闭唯一性校验set unique_checks=0, 导入结束后设为 1 开启,可提高效率; 
 (3)如应用使用自动提交,导入数据的时候执行 set autocomment=0关闭自动提交,导入后打开;

 

4.2 优化insert

(1)如从同一客户插入很多行,使用多值表insert语句,将缩短客户端与数据库间链接、关闭等资源消耗,使效率快
如:

insert to test values(1,2),(1,3),(1,4)....

(2)如从不同客户插入多行,使用insert delayed语句得更高速度。

delayed延迟insert语句执行,数据放在内存队列,并没有真正写入磁盘,这比每一条数据分别插入快得多。

low_priority 相反,所有其他用户对表读写后才插入。

3)将索引文件和数据文件 分不同磁盘存放;

4)批量插入增加 bulk_insert_buffer_size 变量值提高速度,但只对MyIsAM表使用;

5)文本文件装载表,使用load file insert比通常sql语句快20倍。

这是由于index_var索引的为1024 * 3 + 1 + 1024 * 3 > 1000导致的,如果遇到这种情况怎么办?有两种解决办法。

4.3 优化group by

查询含group by但要避免排序结果的消耗,可指定order by null禁止排序

如:

select id, sum(money) from sale2  
group by id 
order by null  

第一,将数据库的engine由MyISAM换成InnoDB就可以了,那么这两个引擎有什么区别呢?

4.4 优化order by

可使用索引满足一个order by语句。

条件:

where条件和order by使用相同索引;
order by顺序和索引顺序相同;
order by字段都升或降序 

如:

order by key1,key2  
where key1=123 order by key1 desc, key2 desc  
order by key1 desc, key2 desc  

以下情况不行

order by key1 asc, key2 desc  混合使用ASC和DESC  
where key2 = 1 , order by key1  查询关键字和排序的不一样  
order by key1,key2  对不同关键字使用排序  

看这里

4.5 优化嵌套查询

有时子查询可被更有效的join代替

比如

select * from a where b_id not in(select id from b)   

换成

select * from a left join b on a.b_id = b.id 
where a.b_id is not null  

不过,这样换有一个缺点,就是InnoDB的性能没有MyISAM的好,那么如果想要不牺牲性能的话,那就只有用第二个方法了,也就是我们这里说的方法二!

4.6 优化OR

or每一个条件都使用索引
但若or几个条件是复合索引元素,则无优化效果

 

4.7 使用SQL提示

在sql中加入一些人为提示进行优化。

例如:

  select sql_buffer_results * from

指示MySql生成临时结果集,所有表锁被释放。

这能解决表锁问题,或要花长时间将结果传给客户端,因为资源被快速释放。

方法二:

1)use index

查询语句表名后加use index指定mysql参考的索引列表,不考虑其他索引。

select * from a use index(ind_a_id) where id = 1  

使用dual虚拟表来实现。

2)ignore index

若打算忽略一多个索引,可用ignore index为hint。

select * from a ignore index(ind_a_id) where id = 1  

使用dual虚拟表来实现的话就不需要创建多列唯一索引了,表的创建如下:

3)force index

强制MySQL使用特定索引,可在查询中使用。

[sql] 
CREATE TABLE `test_table` (  
  `id`  int(11) NOT NULL AUTO_INCREMENT ,  
  `var1`  varchar(1024) CHARACTER SET utf8 DEFAULT NULL,  
  `var2`  tinyint(1) NOT NULL DEFAULT '0',  
  `var3`  varchar(1024) character set utf8 default NULL,  
  `value1`  int(11) NOT NULL DEFAULT '1',  
  `value2`  int(11) NULL DEFAULT NULL,  
  `value3`  int(5) DEFAULT NULL,  
  PRIMARY KEY (`Id`)  
) ENGINE=MyISAM DEFAULT CHARACTER SET=latin1 AUTO_INCREMENT=1;  

 

插入语句则是形如:

[sql] 
INSERT INTO table  
(primarykey, field1, field2, ...)  
SELECT key, value1, value2, ...  
FROM dual  
WHERE not exists (select * from table where primarykey = id);  

 

的语句,此时我们可以用以下语句代替:

[sql] 
INSERT INTO `test_table` SELECT 0, 'abcd', 0, 'xyz', 1, 2, 3  
FROM dual WHERE NOT EXISTS (  
SELECT * FROM `test_table` WHERE   
`var1` = 'abcd' AND `var2` = 0 AND `var3` = 'xyz');  

 

此时,如果val1 = 'abcd',val2 = 0, val3 = ‘xyz’的记录不存在,那么就会执行该插入语句插入该记录,如果存在,那就需要我们再使用相应的更新语句来更新记录:

[sql] 
UPDATE `test_table` SET   
`value1` = `value1` + 1, `value2` = `value2` + 2, `value3` = `value3` + 3  
WHERE `val1` = 'abcd' AND `val2` = 0 AND `val3` = 'xyz';  

 

 

OK!到这里,基本上讲完了。

 

mysql处理某个唯一索引时存在则更新,不存在则插入的情况应该是很常见的,网上也...