INSERT 语句

INSERT … ON DUPLICATE KEY UPDATE

MySQL 数据库中,如果在insert语句后面带上ON DUPLICATE KEY UPDATE 子句,而要插入的行与表中现有记录的唯一索引或主键中产生重复值,那么就会发生旧行的更新;如果插入的行数据与现有表中记录的唯一索引或者主键不重复,则执行新纪录插入操作。

如果一个表中既有 primary key 又有unique 索引, 仅仅是 pk 生效

影响

  • 如果是插入操作,受到影响行的值为1;
  • 如果更新操作,受到影响行的值为2;
  • 如果更新的数据和已有的数据一样(就相当于没变,所有值保持不变),受到影响的行的值为0
阅读更多

FAQ

[1071] Specified key was too long; max key length is 767 bytes

对业务数据进行重建, 需要从自建库更改到 RDS 库
在执行一条 sql 的时候出现

[42000][1071] Specified key was too long; max key length is 767 bytes

阅读更多

DataGrip - FAQ

[08S01]
Communications link failure

The last packet successfully received from the server was 193 milliseconds ago.  The last packet sent successfully to the server was 172 milliseconds ago.

No appropriate protocol (protocol is disabled or cipher suites are inappropriate).

useSSL改成 false,如果不可以,在改成 no,就可以正确连接, 我的是改成 no就可以
image.png

阅读更多

10 个MySQL数据库备份教程推荐

MySQL是动态网站开发中最著名的开源数据库系统。如果你在网站中使用了MySQL,那么你应该定期备份你的数据以防止它丢失。

本文将介绍自动或手动备份MySQL数据库的10个教程,看看有没有最适合你的方法。

1.Backing Up Using MySQLDump(利用MySQLDump备份MySQL数据库)

可以使用MySQL自带的mysqldump工具来备份数据。在mysqldump中提供了许多的示例,包括如何将数据库备份到一个文件、另一个服务器,甚至gzip压缩文件。

2.MySQL Export: How to Backup Your MySQL Database?(MySQL Export:怎样备份你的MySQL数据库)

你可以将使用本地账户创建的数据库生成一个dump文件(export /backup)。但是这样的话你可能需要使用cPanel主机中的phpMyAdmin工具。

3.Automatically Backup Mysql Database to Amazon S3(使用Amazon S3服务自动备份MySQL数据库)

你也可以使用Amazon S3云存储服务来备份你的MySQL数据库。这篇文章中有一个自动脚本,它可以自动将mysql数据库备份并上传至Amazon S3系统。

4.How to Backup MySQL Databases, Web Server Files to an FTP Server Automatically(自动备份MySQL数据库及Web服务器文件至FTP服务器)

对于有自己专门的Web和MySQL服务器或者使用VPS的用户,这里有一个简单的数据备份方法。这样的好处是,使用FTP或NAS备份时,数据是安全的。首先你需要使用mysqldump命令备份每个单独数据库,然后可以定制一个自动任务计划进行tar打包,设置cron作业,并创建FTP备份脚本。

5.How to E-Mail Yourself an Automatic Backup of Your MySQL Database Table with  PHP(用PHP自动备份MySQL数据库并发送到邮箱)

此方法可以帮助你轻松备份指定的表,会给你发送一封附有.sql文件的email。你可以创建一个特定的email账号来接收这些备份。

6.How to Backup MySQL Database Using PHP(PHP备份MySQL数据库)

在PHP文件中执行一个数据库备份语句。要恢复备份的数据,只要运行LOAD DATA INFILE语句即可。

7.Backup Your Database Into an XML File By Using PHP(通过PHP实现XML备份数据库)

该方法会出现一段PHP代码片段,备份后的数据库将以XML格式输出。对于存储一个数据表,XML不是最便捷的格式,但阅读起来要轻松得多。

8.Backup MySQL Database Through SSH(通过SSH备份MySQL数据库)

SSH可以用于备份MySQL大数据。你必须在你的cPanel或Plesk控制面板中启用shell访问,然后使用一个像PuTTY之类的工具远程登录服务器。

9.How to Backup MySQL Database Automatically (For  Linux  Users)(怎样自动备份MySQL数据库——Linux用户篇)

如果你使用的是Linux系统,你可以利用cron作业自动备份MySQL数据库。cron是一个Unix/Linux系统下的定时执行工具。

10.Ubuntu Linux Backup MySQL Server Shell Script(Ubuntu Linux下备份MySQL服务器的Shell脚本)

如果你拥有一台专门的Ubuntu Linux VPS服务器,可以将你的MySQL服务器数据库备份至FTP服务器中。


语雀镜像 : 10 个MySQL数据库备份教程推荐 ,点此 提问

mysql 和 sphinx全文搜索

IBM 课程:

       http://www.ibm.com/developerworks/cn/opensource/os-php-sphinxsearch/


A 下载安装:

http://www.sphinxsearch.com/downloads.html

B 建立相关文件夹,  
E:/spnix
      /data               存放索引文件
      /log                 存放日志文件
      sphinx.conf     配置参数文件


  1. 创建 sphinx.conf 文件

索引的定义
source 源名称
{
     #some config option
}

index 索引名称
{
     source= 源名称
}

索引器的配置
indexer
{
     # some options
     mem_limit = 32M
}

守护程序的配置
searchd
{
          #配置 searchd 守护程序本身
}

2.Source 源名称 相关配置说明

Type=                     数据库类型 (Mysql 或 SQL);
Sql_host=       数据库主机地址 ( 如果是外网 , 请确保防火墙允许链接 )
Sql_user=       数据库用户名
Sql_pass=       数据库密码
Sql_db=           数据库名称
Sql_port=        数据库端口
Sql_query_pre=   执行 SQL 前设置的编码 (SET NAMES UTF8/GBK)
Sql_query=       全文检索要显示的内容
          据官方说法:尽可能不要使用 WHERE 或 GROUPBY , 将其交给 SPHINX 效率会更高 ;
          select 出来的字段必须包含至少一个唯一主键 , 以及全文检索的字段

Sql_query_info=<br />          SELECT * FROM Inventory WHERE id=$id

          来查找匹配记录 在查询中,$id  被替换为 searchd  返回的每个主键

Strip_html= 0/1       是否去掉 HTML 标签

Sql_attr_uint=        无符号整数属性 , 可以设置多个 , 设置数据库字段 , 设置哪个能显示出哪个字段数据的整形来

三.     Index 中配置说明

Source=              数据源名称
Path =                索引记录存放目录 , 注 : d:/sphinx/data/cg 这个的意思是 在 data 目录下创建一个名为 cg 的文件
min_word_len=        索引的词的最小长度 设为 1 既可以搜索单个字节搜索 , 越小 索引越精确 , 但建立索引花费的时间越长
charset_type=         utf-8/gbk 设置数据编码
charset_table=       字符表和大小写转换规则 . 频繁应用于 sphinx 的分词过程
min_prefix_len=       最小前缀 (0)
min_infix_len=      最小中缀 (1)

//以下三句是关于UTF-8中文一元分词的配置
ngram_len =   1       
          对于非字母型数据的长度切 (1)/简单分词,只支持0和1,如果要搜索中文,请指定为1
charset_table  = 0..9, A..Z->a..z, _, a..z, U+410..U+42F->U+430..U+44F, U+430..U+44F
ngram_chars     = U+3000..U+2FA1F
      需要分词的字符,如果要搜索中文,去掉前面的注释

四.     searchd 配置说明守护进程

port=            sphinx 的端口 (9312)
log=             服务进程日志存放路径,一旦 sphinx 出现异常,基本上可以从这里查询有效信息
query_log=           客户端查询日志 尤为有用 它将在运行时显示每次搜索并显示结果
read_timeout=        请求超时 (5)
max_children=        同时可执行的最大 searchd 进程数 (30)
pid_file= D:/sphinx/log/searchd.pid
          进程 ID 文件
max_matches=          查询结果的最大返回数
seamless_rotate=     是否支持无缝切换,做增量索引时通常需要 (0/1)

至此 spninx.conf 配置文件结束 ; 注意 : 如果有换行 必须用反斜杠 \ 链接

五.     sphinx 建立索引及监听

切换到 DOS   sphinx/bin 目录下

  1. 建立索引

       indexer.exe –all      # 建立所有的索引
       indexer –c sphinx.conf  

索引名称 /–all (–all 参数是建立所有索引 )

完成后提示如下 :

total 535600 docs, 10707632 bytes
total 34.323 sec, 311958 bytes/sec, 15604.27 docs/sec
total 5 reads, 0.282 sec, 45592.6 kb/call avg, 56.4 msec/call avg
total 547 writes, 12.172 sec, 1017.0 kb/call avg, 22.2 msec/call avg

  1. 建立完成后

可以执行 search [–c] sphinx.conf 搜索内容 (-c 参数 : 是否允许模糊搜索 )

  1. 监听端口, 启用守护进程来监听端口

Searchd

运行后 提示

listening on all interfaces, port=9312
accepting connections

监听成功后就可以运行 PHP 程序 进行 搜索

require(“sphinxapi.php”);                // 加载类文件
$cl=new SphinxClient();                  // 实例化类
$cl->SetServer(‘localhost’,9312);             // 设置服务
$cl->SetArrayResult(true);               // 设置 显示结果集方式
$cl->SetLimits(0,10);                    // 同 sql 语句中的 LIMIT
$cl->SetSortMode(“mode”);                // 排序模式 SPH_SORT_ATTR_DESC 和 SPH_SORT_ATTR_ASC
$result=$cl->Query(‘ff’,[ 索引名称可选 ]);// 执行搜索
Var_dump($result);                       // 输出


语雀镜像 : mysql 和 sphinx全文搜索 ,点此 提问

Mysql开发笔记 - 优化篇

  1. 优化SQL的一般步骤
          SHOW STATUS 可以提供服务器状态信息
                    Com_select : 执行select 操作的次数
                    Com_insert : 执行insert 操作的次数
                    Com_update : 执行update 操作的次数
                    Com_delete : 执行delete 操作的次数
              
               Innodb
                Innodb_rows_read select : 查询返回的行数;
                        Innodb_rows_inserted     :  执行Insert 操作插入的行数;
                        Innodb_rows_updated       :  执行update 操作更新的行数;
                        Innodb_rows_deleted       :  执行delete 操作删除的行数;
                    
               事务操作
               Com_commit    : 提交型          
                    Com_rollback : 回滚, 回滚太多说明编写存在问题

           数据库的基本情况
            Connections           试图连接Mysql 服务器的次数
           Uptime                     服务器工作时间
           Slow_queries      慢查询的次数
      定位执行效率较低的sql
           使用慢查询日志来定位哪些执行效率较低的SQL

            –log-slowqueries[=file_name] 文件位置
                long_query_time   执行秒数
      SHOW PROCESSLIST
           显示当前的链接数据库的状态
通过explain 分析低效sql的执行计划
      explain select * from mg_news
       >
            select_type: select 类型
table: 输出结果集的表
type: 表示表的连接类型

  • 当表中仅有一行是type的值为system是最佳的连接类型;
  • 当select操作中使用索引进行表连接时type的值为ref;
  • 当select的表连接没有使用索引时,经常会看到type的值为ALL,表示对该表
  • 进行了全表扫描,这时需要考虑通过创建索引来提高表连接的效率。

possible_keys: 表示查询时,可以使用的索引列.

key: 表示使用的索引
key_len: 索引长度
rows: 扫描范围
Extra: 执行情况的说明和描述

  1. 索引问题   
          位置存放         
              MyISAM 表的数据文件和索引文件是分开的.Innodb 是放到一个表空间中, 但是可以有多个文件组成  建立索引的语法       > 索引类型            MyiSAM : btree      Innodb : btree       memory/heap    : hash , btree 查看索引使用情况      SHOW STATUS LIKE ‘Handler_read%’;        >      handler_read_key                     如果索引正在工作,这个值将会很高, 表示这个行被索引值读的次数, 次数低表明增加索引得到的性能改善不高      Handler_read_rnd_next                      高值意味着低效, 含义是在数据文件中读下一行文件的请求数
    3.定期分析表       
    ANALYZE TABLE       >      用于分析和存储表的关键字分布, 在分析期间,读取锁定表, 这对于MyISAM, BDB, InnoDB表有用        > CHECK TABLE      检查一个或者多个表是否有错误, 对于MyISAM , InnoDB有作用, 同样也可以检查视图        > CHEKCSUM TABLE      报告一个表校验和        > OPTIMIZE TABLE       >      对表进行优化, 如果删除了表的一大部分, 或者varchar,blob, text列的表进行了很多更改,则应该进行优化;
    4.常用SQL的优化
          大量数据的插入
               MyIsAM
                        默认是禁用索引,然后导入数据,然后再开启索引
    ALTER TABLE tblname DISABLE KEYS;loading the dataALTER TABLE tblname ENABLE KEYS;      InnodB            Innodb类型的表示按照主键的顺序进行顺序保存de,可以尝试建立主键再进行导入            导入的时候关闭唯一性校验,完成之后再开启            导入的时候应该关闭自动提交,然后结束的时候开启自动提交 Insert        使用多个值表的insert       使用insert delayed 将需要加载的语句放到内存       Low_priorty 是在所有用户读取/写入完成之后才进行插入       将索引文件和数据文件在不同的磁盘上存放       如果进行批量插入,可以增加bulk_insert_buffer_size进行提高速度,仅仅适用于myisam       使用文本文件装载一个表的时候 Load Data Infile 比insert快20倍       根据情况使用replace替代insert       根据情况使用ignore忽略重复记录 GROUP BY      使用order by null 对group by 进行优化加速 JOIN      使用子查询可以一次性完成很多逻辑上使用许多个步骤才能完成的sql操作.同时也恶意避免事务或者表锁死, 但是可以被更有效的链接(JOIN)来替代, INSERT UPDATE DELETE      1. 写入操作优先于读取操作       2. 对于某张数据表的操作某一时刻只能发生一次,写入请求按照他们到达的次数来处理       3. 对某张表的多个读取操作可以同时进行             LOW_PRIORITY 关键字应用于DELETE, INSERT, LOAD DATA, REPLACE, UPDATE       HIGH_PRIORITY 关键字应用于 SELECT INSERT       DELAYED 关键字 应用于INSERT REPLACE SELECT SQL_BUFFER_RESULT      会让客户端稍微费点力气才能够处理的大的结果集的时候才会使用,以免表发生死锁 USE INDEX, IGNORE INDEX, FORCE INDEX      索引的使用
  2. 其他优化措施
          1.使用持久的连接数据库
          2.经常检查所有查询是否使用了必要的索引
          3.避免在频繁更新的表上执行复杂的select操作
          4.对于没有删除操作的myisam, 操作和查询可以并行进行,删除应当在空闲时间进行
          5.充分利用列有默认值的事实, 只有当插入的值不同于默认值时候才会进行插入,这有助于提高插入速度
          6.对于经常访问的可以重构的数据使用内存表,可以显著提高访问的效率
          7.通过复制可以提高某些操作的性能
          8.表的字段尽量不适用自增长变量,高并发的情况下该字段的自增可能对效率有比较大的影响
    6.优化表的数据类型
          1. 使用 PROCEDURE ANALYSE() 优化分析表
               对于输出的每一项提出优化建议
  3. 通过拆分表,提高访问效率这里主要指的是 MYISAM类型的表      纵向拆分将经常访问的字段和不经常访问的字段进行拆分,经常访问的字段尽量是定长的           横向拆分是按照应用的情况有目的的将数据横向拆分成几个表或者通过分区分到不同的分区3. 逆规范化      强调数据的独立性,数据尽可能减少冗余       但是对于查询很多的应用,一次查询可能需要访问多表进行, 这样可以考虑有效的使用冗余数据来提高效率 4.使用临时统计表      对于大表的统计分析,可以建立 [create temporary table]进行统计 5.选择更合适的表      如果应用出现较严重的锁冲突可以考虑更改存储引擎到 innodb, 如果查询很多,且对事务完整性要求不严格,可以考虑使用myisam
    7.锁问题
          获取锁等待状态

          获取表锁状态
                
          获取行锁等待状态
                     > 表锁和行锁的区别      表锁的优越性            1.很多操作都是读取表            2.严格条件的索引上读取和更新, 当更新或者删除可以用单独的索引来读取            3. Update tbl_name SET column=value WHERE unique_key_col = v                      4. DELETE FROM tbl_name WHERE unique_key = value                     5. select 和 insert的并发执行但是有很少的update和delete           6. 很多的扫描表和对全表的GROUP BY 操作,但没有任何写表        行锁的优缺点            优点            1.许多县城访问不同的行时候只有少量的冲突            2.回滚时候只有少量的更改            3.可以长时间锁定单一的行            缺点            1.比页锁和表所占用更多的内存            2.当大部分使用是,比页锁或者表锁慢            3.经常扫描整个表或者GROUP操作比表锁慢很多            4.用高级别锁定, 通过支持的不同的类型锁定,你可以很容易调解程序, 成本小于行级别锁定 insert…select 带来的问题          如果select 的表是innodb 类型的,不论insert 的表是什么类型的表,都会对select 的表的纪录进行锁定L:63>
    >
    >
    >

     


语雀镜像 : Mysql开发笔记 - 优化篇 ,点此 提问

[转]MySQL Binlog 介绍

原文地址 :

MySQL中一般有以下几种日志:

日志类型 写入日志的信息
错误日志 记录在启动,运行或停止mysqld时遇到的问题
通用查询日志 记录建立的客户端连接和执行的语句
二进制日志 记录更改数据的语句
中继日志 从复制主服务器接收的数据更改
慢查询日志 记录所有执行时间超过 long_query_time 秒的所有查询或不使用索引的查询
DDL日志(元数据日志) 元数据操作由DDL语句执行

本文主要介绍二进制日志 binlog。
MySQL 的二进制日志 binlog 可以说是 MySQL 最重要的日志,它记录了所有的 DDLDML 语句(除了数据查询语句select、show等),以事件形式记录,还包含语句所执行的消耗的时间,MySQL的二进制日志是事务安全型的。binlog 的主要目的是复制和恢复

阅读更多

mysqlnd 库大大提升了数据库访问性能

PHP 5.3+ 版本,提供了一个 mysqlnd 库,用来访问 MySQL数据库,大大提升了PHP的数据库访问性能。
我们知道,PHP访问MySQL数据库,是通过MySQL数据库的libmysql client库,这个libmysql client是用C/C++编写的,虽然一直以来PHP通过libmysql访问数据库性能也一直很好,但是却无法利用PHP本身的很多特性。
mysqlnd提供了和Zend引擎高度的集成性,更加快速的执行速度,更少的内存消耗,利用了PHP的Stream API,以及客户段缓存机制。由于mysqlnd是透过Zend引擎,因此提供提供更多高级特性,以及有效利用Zend进行加速,原理图如下:

从上图可以看出来,libmysql是直接访问数据库的,而mysqlnd是通过Zend访问数据库。


语雀镜像 : mysqlnd 库大大提升了数据库访问性能 ,点此 提问

MySQL - FAQ

mysql Error 1040 too many connection解决办法

最近在用SpringMVC开发的时候,突然出现1040 too many connection的错误,看错误的意思是连接的人数太多了。

阅读更多

Mysql 数据库常用操作

  • mysql 命令行
  • 数据类型
    • String
    • 二进制
    • 数值
    • 日期
    • 枚举
  • 函数
    • 数学函数
    • 字串函数
    • 日期函数
  • 基础 MySQL 操作
    • 数据库操作
    • 表结构/操作
      • MODIFIY (修饰符)
    • 表查询
      • 插入数据
      • 删除数据
      • 更新
      • 查找数据
      • 统计, 函数使用, 技巧
  • 维护
    • 数据库账号权限和密码
    • 数据库引擎
阅读更多

mysql百万级以上查询优化

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

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

select  id  from  t  where  num  is  null  

可以在num上设置默认值0,确保表中num列没有null值,然后这样查询:  

select  id  from  t  where  num=0

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

4.应尽量避免在  where  子句中使用  or  来连接条件,否则将导致引擎放弃使用索引而进行全表扫描,如:  

select  id  from  t  where  num=10  or  num=20  

可以这样查询:  

select  id  from  t  where  num=10  

union  all  

select  id  from  t  where  num=20

5.in  和  not  in  也要慎用,否则会导致全表扫描,如:  

select  id  from  t  where  num  in(1,2,3)  

对于连续的数值,能用  between  就不要用  in  了:  

select  id  from  t  where  num  between  1  and  3

6.下面的查询也将导致全表扫描:  

select  id  from  t  where  name  like  ‘%abc%’  

若要提高效率,可以考虑全文检索。

7.如果在  where  子句中使用参数,也会导致全表扫描。因为SQL只有在运行时才会解析局部变量,但优化程序不能将访问计划的选择推迟到运行时;它必须在编译时进行选择。然而,如果在编译时建立访问计划,变量的值还是未知的,因而无法作为索引选择的输入项。如下面语句将进行全表扫描:  

select  id  from  t  where  num=@num  

可以改为强制查询使用索引:  

select  id  from  t  with(index(索引名))  where  num=@num

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

select  id  from  t  where  num/2=100  

应改为:  

select  id  from  t  where  num=100*2

9.应尽量避免在where子句中对字段进行函数操作,这将导致引擎放弃使用索引而进行全表扫描。如:  

select  id  from  t  where  substring(name,1,3)=’abc’–name以abc开头的id  

select  id  from  t  where  datediff(day,createdate,’2005-11-30’)=0–‘2005-11-30’生成的id  

应改为:  

select  id  from  t  where  name  like  ‘abc%’  

select  id  from  t  where  createdate>=’2005-11-30’  and  createdate<’2005-12-1’

10.不要在  where  子句中的“=”左边进行函数、算术运算或其他表达式运算,否则系统将可能无法正确使用索引。

11.在使用索引字段作为条件时,如果该索引是复合索引,那么必须使用到该索引中的第一个字段作为条件时才能保证系统使用该索引,否则该索引将不会被使用,并且应尽可能的让字段顺序与索引顺序相一致。

12.不要写一些没有意义的查询,如需要生成一个空表结构:  

select  col1,col2  into  #t  from  t  where  1=0  

这类代码不会返回任何结果集,但是会消耗系统资源的,应改成这样:  

create  table  #t(…)

13.很多时候用  exists  代替  in  是一个好的选择:  

select  num  from  a  where  num  in(select  num  from  b)  

用下面的语句替换:  

select  num  from  a  where  exists(select  1  from  b  where  num=a.num)

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

15.索引并不是越多越好,索引固然可以提高相应的  select  的效率,但同时也降低了  insert  及  update  的效率,因为  insert  或  update  时有可能会重建索引,所以怎样建索引需要慎重考虑,视具体情况而定。一个表的索引数最好不要超过6个,若太多则应考虑一些不常使用到的列上建的索引是否有必要。

16.应尽可能的避免更新  clustered  索引数据列,因为  clustered  索引数据列的顺序就是表记录的物理存储顺序,一旦该列值改变将导致整个表记录的顺序的调整,会耗费相当大的资源。若应用系统需要频繁更新  clustered  索引数据列,那么需要考虑是否应将该索引建为  clustered  索引。

17.尽量使用数字型字段,若只含数值信息的字段尽量不要设计为字符型,这会降低查询和连接的性能,并会增加存储开销。这是因为引擎在处理查询和连接时会逐个比较字符串中每一个字符,而对于数字型而言只需要比较一次就够了。

18.尽可能的使用  varchar/nvarchar  代替  char/nchar  ,因为首先变长字段存储空间小,可以节省存储空间,其次对于查询来说,在一个相对较小的字段内搜索效率显然要高些。

19.任何地方都不要使用  select  *  from  t  ,用具体的字段列表代替“*”,不要返回用不到的任何字段。

20.尽量使用表变量来代替临时表。如果表变量包含大量数据,请注意索引非常有限(只有主键索引)。

21.避免频繁创建和删除临时表,以减少系统表资源的消耗。

22.临时表并不是不可使用,适当地使用它们可以使某些例程更有效,例如,当需要重复引用大型表或常用表中的某个数据集时。但是,对于一次性事件,最好使用导出表。

23.在新建临时表时,如果一次性插入数据量很大,那么可以使用  select  into  代替  create  table,避免造成大量  log  ,以提高速度;如果数据量不大,为了缓和系统表的资源,应先create  table,然后insert。

24.如果使用到了临时表,在存储过程的最后务必将所有的临时表显式删除,先  truncate  table  ,然后  drop  table  ,这样可以避免系统表的较长时间锁定。

25.尽量避免使用游标,因为游标的效率较差,如果游标操作的数据超过1万行,那么就应该考虑改写。

26.使用基于游标的方法或临时表方法之前,应先寻找基于集的解决方案来解决问题,基于集的方法通常更有效。

27.与临时表一样,游标并不是不可使用。对小型数据集使用  FAST_FORWARD  游标通常要优于其他逐行处理方法,尤其是在必须引用几个表才能获得所需的数据时。在结果集中包括“合计”的例程通常要比使用游标执行的速度快。如果开发时间允许,基于游标的方法和基于集的方法都可以尝试一下,看哪一种方法的效果更好。

28.在所有的存储过程和触发器的开始处设置  SET  NOCOUNT  ON  ,在结束时设置  SET  NOCOUNT  OFF  。无需在执行存储过程和触发器的每个语句后向客户端发送  DONE_IN_PROC  消息。

29.尽量避免大事务操作,提高系统并发能力。

30.尽量避免向客户端返回大数据量,若数据量过大,应该考虑相应需求是否合理。


语雀镜像 : mysql百万级以上查询优化 ,点此 提问

mysql 的 float 和 decimal 的区别

  1. 表结构

  1. 测试insert 数据

  1. 原因

< http://www.dewen.io/q/1211 >
float,double容易产生误差,对精确度要求比较高时,建议使用decimal来存,decimal在mysql内存是以字符串存储的,用于定义货币要求精确度高的数据。在数据迁移中,float(M,D)是非标准定义,最好不要这样使用。M为精度,D为标度。
mysql> create table t1(c1 float(10,2), c3 decimal(10,2));  
Query OK, 0 rows affected (0.02 sec)
mysql> insert into t1 values(1234567.23, 1234567.23);
Query OK, 1 row affected (0.01 sec)
mysql> select * from t1;
+————+————+
| c1 | c3 |
+————+————+
| 1234567.25 | 1234567.23 |  
+————+————+
1 row in set (0.02 sec)
mysql> insert into t1 values(9876543.21, 9876543.12);
Query OK, 1 row affected (0.00 sec)
mysql>  
mysql> select * from t1;
+————+————+
| c1 | c3 |
+————+————+
| 1234567.25 | 1234567.23 |  
| 9876543.00 | 9876543.12 |  
+————+————+
2 rows in set (0.00 sec)
不定义fload, double的精度和标度时,存储按给出的数值存储,这于OS和当前的硬件有关。
decimal默认为decimal(10,0)
因为误差问题,在程序中,少用浮点数做=比较,可以做range比较。如果数值比较,最好使用decimal类型。
精度中,符号不算在内:
mysql> insert into t1 values(-98765430.21, -98765430.12);
Query OK, 1 row affected (0.01 sec)
mysql> select * from t1;
+————–+————–+
| c1 | c3 |
+————–+————–+
| 1234567.25 | 1234567.23 |  
| 9876543.00 | 9876543.12 |  
| -98765432.00 | -98765430.12 |  
+————–+————–+
3 rows in set (0.00 sec)
float占4个字节,double占8个字节,decimail(M,D)占M+2个字节。


语雀镜像 : mysql 的 float 和 decimal 的区别 ,点此 提问