在创建新表和数据仓库时，要做很多决定。一些在当时似乎无关紧要的地方，却让你和用户在数据库的生命期内感到痛苦。我们和成千上万的人们以及他们的数据库一道工作，经历了长期的读写查询，我们差不多看到了每种情况。下面是创建免去痛苦模式的 10 条规则。1.只使用小写字母、数字和下划线不要在数据库、模式、表或列名中使用点(dot)、空格、或连接号【注1】。点用于标示对象，通常以 database.schema.table.column 的方式。对象名称中包含点将引起混淆。类似地，在对象名字里使用空格将迫使你在查询语句中添加不必要的引号：select "user name" from events -- vs select user_name from events如果在表或列名里有大写字母，查询语句将难以书写。如果所有字母都是小写的，人们将不必记住 users 表是 Users 还是 users。当你最终修改数据库或把你的表复制到仓库时，你不需要记住哪个表是大小写敏感的。2.使用简单的、自说明的列名如果 users 表需要 packages 表的外键，就把键命名为 package_id。避免使用简短、晦涩的名字，比如 pkg_fk；其他人不知道它代表什么。自说明的名字让其他人更容易理解模式，随着团队规模的增加，这对于维护效率至关重要。不要为多态的数据使用有歧义的名字。如果你发现自己 ...继续阅读 (9)

   有时候，我们会遇到类似下面的报错信息：..... [ERROR] /usr/local/mysql/bin/mysqld: Can't open file: './yejr/access.frm' (errno: 24) [ERROR] /usr/local/mysql/bin/mysqld: Can't open file: './yejr/accesslog.frm' (errno: 24) ...... [ERROR] Error in accept: Too many open files ....   提示信息很明显，打开文件数达到上限了，需要提高上限，或者释放部分已打开的表文件描述符。   在MySQL中，有几个地方会存在文件描述符限制：1、在Server层，整个mysqld实例打开文件总数超过用户进程级的文件数限制，需要检查内核fs.file-max限制、进程级限制ulimit -n及MySQL中的open-files-limit选项，是否有某一个超限了。任何一个条件超限了，就会抛出错误。 2、虽然Server层总文件数没有超，但InnoDB层也有限制，所有InnoDB相关文件打开总数不能超过innodb-open-files选项限制。否则的话，会先把最早打开的InnoDB文件描述符关闭，才能打开新的 ...继续阅读 (9)

   通常，我们会出于以下几个目的对MySQL进行压力测试：1、确认新的MySQL版本性能相比之前差异多大，比如从5.6变成5.7，或者从官方版本改成Percona分支版本； 2、确认新的服务器性能是否更高，能高多少，比如CPU升级了、阵列卡cache加大了、从机械盘换成SSD盘了； 3、确认一些新的参数调整后，对性能影响多少，比如 innodb_flush_log_at_trx_commit、sync_binlog 等参数； 4、确认即将上线的新业务对MySQL负载影响多少，是否能承载得住，是否需要对服务器进行扩容或升级配置；   针对上面这几种压测的目的，相应的测试方法也有所不同。   先说第四种，需要和线上业务结合起来，这时候就需要自行开发测试工具，或者利用tcpcopy将线上实际用户请求导向测试环境，进行仿真模拟测试。   对于前三种，我们通常采用基准测试就可以。比较常用的MySQL基准压力测试工具有tpcc-mysql、sysbench、mysqlslap等几个。   关于压力测试工具的使用，可以查看我之前在ORACLE技术嘉年华上的分享：MySQL压力测试经验，在这里不再细说。   基于促进同行间的交流，统一MySQL压测标准，并且可以相互分享、对比、 ...继续阅读 (18)

前言   mysql的索引合并并不是什么新特性。早在mysql5.0版本就已经实现。之所以还写这篇博文，是因为好多人还一直保留着一条sql语句只能使用一个索引的错误观念。本文会通过一些示例来说明如何使用索引合并。什么是索引合并   下面我们看下mysql文档中对索引合并的说明：The Index Merge method is used to retrieve rows with several range scans and to merge their results into one. The merge can produce unions, intersections, or unions-of-intersections of its underlying scans. This access method merges index scans from a single table; it does not merge scans across multiple tables.   根据官方文档中的说明，我们可以了解到：   1、索引合并是把几个索引的范围扫描合并成一个索引。   2、索引合并的时候，会对索引进行并集，交集或者先交集再并集操作，以便合并成一个索引。&nb ...继续阅读 (22)

本文起源于有同学留言回复说想了解下MySQL DBA面试时可能涉及到的知识要点，那我们今天就来大概谈谈吧。MySQL DBA职位最近几年特别热门，不少朋友让我帮忙推荐什么的，也有很多公司找不到合适的DBA。原因很简单，优秀的人才要么被大公司圈起来了，要么被创业公司高薪挖走，如果你既不是大公司，又不能出得起高价钱的土豪公司，想要找到优秀人才的几率堪比买彩票中奖的概率，哈哈。本文可以作为MySQL DBA面试官，以及候选人的双向参考：）面试流程接下来先说下我以往在做MySQL DBA面试时的过程（套路）：1.先自我介绍后，再让候选人花2-5分钟做下自我简介有不少人可能对自我简介这个环节嗤之以鼻，觉得多此一举，尤其是技术能力相对较好的更是如此。其实不然，通过短短2-5分钟的自我简介，很快就能考察出候选人是否有用心准备本次面试，其归纳总结能力，以及个人自信心等多方面信息。因此，如果候选人看中这次面试机会的话，还请好好做下功课，做足准备。比如了解下目标公司的大致情况，主营业务，产品特色。可能的话，找同行打听可能的面试官背景信息，没准是校友、以前在同一家公司呆过、或者有其他共同点，这可能会使得面试过程更为顺利。有心的候选人在面试官自我介绍时，就可以趁机也考察对方的情况。通常第一轮面试官很可能是你未来的直接主管，从面试过程中你和对方的沟通交流是否顺利也可预见到未来工作上配合的顺利程度。2.暖身完 ...继续阅读 (11)

昨天晚上接到一个网络服务请求，由于不小心点击了自己产品软件上面的清空数据功能(这个工具确实需要小心,在Oracle恢复案例中,也有xx企业erp该功能导致数据被删除请求恢复)，导致MySQL数据库被直接drop database掉了，之前没有做任何备份，只是发生故障之后，他们立即封存现场，备份出来了ibdata1文件。接到请求之后，通过让其把ibdata1文件发给过来，通过MySQL recovery工具进行分析恢复，经过6个小时的处理，基本上实现核心数据0丢失.解析ibdata1(innodb文件)[root@localhost recovery_MySQL]# ./stream_parser -f /tmp/ibdata1 Opening file: /tmp/ibdata1 File information: ID of device containing file: 2055 inode number: 97257 protection: 100644 (regular file) number of hard links: 1 user ID of owner: 0 grou ...继续阅读 (16)

在我眼里，MySQL和Oracle是这样的而SQLite在是这样的所以这么萌的数据库，我真的应该多了解她的。简介SQLite，是一款轻型的数据库，是遵守ACID的关系型数据库管理系统。它的设计目标是嵌入式的，目前Android和iOS的设备内置的都是SQLite数据库。SQLite虽然娇小，但也支持事务和多数的SQL92标准。主要特点Zero-Configuration 无需安装和管理配置。Serverless 无需服务器支持。Single Database File 数据文件存储在一个单一的磁盘文件。Stable Cross-Platform Database File 数据库文件格式跨平台，无论是大小端，或者是32bit或64bit机器都没有关系Compact 完整特性的SQLite编译出来在500KiB左右，裁剪特性甚至可以得到低于300KiB的库(当前版本3.8.11.1)。Manifest typing 可以声明数据库字段类型，但是字段存储的类型实际的存储类型和实际值相关，单独的一个字段可能包含不同存储类的值。Variable-length records 可变长度记录，例如你存储一个字符到VARCHAR(100) 的列，实际需要的存储空间一个字符加一个字节的存储空间。SQL statements compile into virtual machine code SQL ...继续阅读 (12)

   在以前的文章中说过mysql的数据字典的恢复(使用工具直接抽取MySQL数据字典,缺少SYS_FIELDS表),主要的数据字典有一下几个，在本文中主要对这些数据字典的意义进行一些讲解，为大家更深一步了解mysql恢复处理思路   MySQL恢复字典表mysql> show tables; +----------------+ | Tables_in_test | +----------------+ | SYS_COLUMNS | | SYS_FIELDS | | SYS_INDEXES | | SYS_TABLES | +----------------+ 4 rows in set (0.00 sec)   SYS_TABLES   这个表是mysql恢复的最核心的表之一，主要是记录数据库在InnoDB中表的信息。它默认写在InnoDB的index_ids为1的里面，它的根页在8号page上，他的主要列结构为：mysql> desc SYS_TABLES; +--------------+---------------------+------+-----+---------+-------+ | Field | Type ...继续阅读 (12)

前题：要求MySQL 5.7.6及以后版本。所有组中成员gtid_mode 为off状态。实现步骤：所有的Server执行set @@global.enforce_gtid_consistency = warn;特别注意： 这一步是关建的一步使用不能出现警告。2.所有的server上执行：set @@global.enforce_gtid_consistency = on;3.所有的Server上执行（不关心最先最后，但要执行完）：set @@global.gtid_mode = off_permissive;执行：set @@global.gtid_mode=on_permissive;实质在这一步骤生的日志都是带GTID的日志了，这个步骤号称是不关心任何节点，但从实管理上推荐在slave上先执行，然后再去master上执行。确认传统的binlog复制完毕,该值为0show status like ‘ongoing_anonymous_transaction_count’;需要所有的节点都确认为0.所有节点进行判断 show status like ‘ongoing_anonymous_transaction_count’; 为零所有的节点也可以执行一下: flush logs; 用于切换一下日志。所有的节点启用gtid_modeset @@global.gtid_mode=on ...继续阅读 (15)

导读在MySQL里，主键索引和辅助索引分别是什么意思，有什么区别？上次的分享我们介绍了聚集索引和非聚集索引的区别，本次我们继续介绍主键索引和辅助索引的区别。1、主键索引主键索引，简称主键，原文是PRIMARY KEY，由一个或多个列组成，用于唯一性标识数据表中的某一条记录。一个表可以没有主键，但最多只能有一个主键，并且主键值不能包含NULL。在MySQL中，InnoDB数据表的主键设计我们通常遵循几个原则：采用一个没有业务用途的自增属性列作为主键；主键字段值总是不更新，只有新增或者删除两种操作；不选择会动态更新的类型，比如当前时间戳等。这么做的好处有几点：新增数据时，由于主键值是顺序增长的，innodb page发生分裂的概率降低了；可以参考以往的分享“[MySQL FAQ]系列 — 为什么InnoDB表要建议用自增列做主键”；业务数据有变更时，不修改主键值，物理存储位置发生变化的概率降低了，innodb page中产生碎片的概率也降低了。MyISAM表因为是堆组织表，主键类型设计方面就可以这么讲究了。2、辅助索引辅助索引，就是我们常规所指的索引，原文是SECONDARY KEY。辅助索引里还可以再分为唯一索引，非唯一索引。唯一索引其实应该叫做唯一性约束，它的作用是避免一列或多列值存在重复，是一种约束性索引。3、主键索引和辅助索引的区别在MyISAM引擎中，唯一索引除了key值允许 ...继续阅读 (14)

今天一个接到一个朋友求助，说是数据在运行中，数据库的目录被改了权限。如： 数据库目结构如下：123datadir=/data/mysql/mysql3306/datalog-bin=/data/mysql/mysql3306/logstmpdir=/data/mysql/mysql3306/tmp被运维同步执行了：1chown-Rroot:root/data/mysql/mysql33061.构建主从环境1234mysql;3306主/data/mysql/mysql3306/{data,tmp,logs}mysql;3307从/data/mysql/mysql3307/{data,tmp,logs}2. 在主的wubx库里创建：12345CREATETABLE`t2`(`id`int(11)NOTNULLAUTO_INCREMENT,`name`varchar(32)DEFAULTNULL,PRIMARYKEY(`id`))确认复制正常。3. 把主库的目录权限改成root1chown-Rroot:root/data/mysql/mysql33064. 弄出来大量的写入1foriin`seq(100000);domysqlwubx-e"insert into t2(name) vlaues('golang$i')";done5. 观查主库和从库上数据发现日志没有 ...继续阅读 (30)

导读在MySQL复制中，如何忽略slave节点上发生的主键冲突、数据不存在等错误。在MySQL复制中，如果slave节点上遇到错误，比如数据不存在或者主键冲突等错误时，想要忽略这些错误，可以采用以下几种方法：1、未启用GTID模式时只需通过设定SQL_SLAVE_SKIP_COUNTER的值，即可忽略一些复制事件。例如：#需要先关闭SLAVE服务   root@imysql.com [test]> STOP SLAVE;#忽略N个事件(event)，通常一个SQL是一个事件   root@imysql.com [test]> SET SQL_SLAVE_SKIP_COUNTER=N;#再次启动SLAVE服务   root@imysql.com [test]> START SLAVE;2、启用GTID模式时启用GTID，想要忽略某些错误事件就稍微麻烦一点点了。   首先，我们需要先查看当前SLAVE复制的进度：mysql> SHOW SLAVE STATUS\G从中看到，当前SLAVE复制的GTID进展是：Slave_IO_Running: Yes   Slave_SQL_Running: No   Last_Errno: 1062  ...继续阅读 (25)

前言很多时候，RDS用户经常会问如何调优RDS MySQL的参数，为了回答这个问题，写一篇blog来进行解释：哪一些参数不能修改，那一些参数可以修改；这些提供修改的参数是不是已经是最佳设置，如何才能利用好这些参数；哪些参数可以改细心的用户在购买RDS的时候都会看到，不同规格能够提供的最大连接数以及内存是不同的，所以这一些产品规格的限制参数：连接数、内存用户是不能够修改的，如果内存或者连接数出现了瓶颈：内存瓶颈：实例会出现OOM，然后导致主备发生切换连接数瓶颈：应用不能新建立连接到数据库则需要进行应用优化、慢SQL优化或者进行弹性升级实例规格来解决。还有一些涉及主备数据安全的参数比如innodb_flush_log_at_trx_commit、sync_binlog、gtid_mode、semi_sync、binlog_format等为了保证主备的数据安全，目前还暂不提供给用户进行修改。除上述的这些参数外，绝大部分的参数都已经由DBA团队和源码团队优化过，用户不需要过多调整线上的参数就可以把数据库比较好的运行起来。但这些参数只是适合大多数的应用场景，个别特殊的场景还是需要个别对待，比如使用了tokudb引擎，这个时候就需要调整tokudb引擎能使用的内存比例(tokudb_buffer_pool_ratio)；又比如我的应用特点本身需要很大的一个锁超时时间，那么则需要调整innodb ...继续阅读 (35)

导读在MySQL里常用的索引数据结构有B+树索引和哈希索引两种，我们来看下这两种索引数据结构的区别及其不同的应用建议。二者区别备注：先说下，在MySQL文档里，实际上是把B+树索引写成了BTREE，例如像下面这样的写法：CREATE TABLE t(aid int unsigned not null auto_increment,userid int unsigned not null default 0,username varchar(20) not null default ‘’,detail varchar(255) not null default ‘’,primary key(aid),unique key(uid) USINGBTREE,key (username(12)) USINGBTREE—此处 uname 列只创建了最左12个字符长度的部分索引)engine=InnoDB;一个经典的B+树索引数据结构见下图：(图片源自网络)B+树是一个平衡的多叉树，从根节点到每个叶子节点的高度差值不超过1，而且同层级的节点间有指针相互链接。在B+树上的常规检索，从根节点到叶子节点的搜索效率基本相当，不会出现大幅波动，而且基于索引的顺序扫描时，也可以利用双向指针快速左右移动，效率非常高。因此，B+树索引被广泛应用于数据库、文件系统等场景。顺便说一下，xfs文件系统比ext3/e ...继续阅读 (41)

导读从一个现场说起，全程解析如何定位性能瓶颈。排查过程收到线上某业务后端的MySQL实例负载比较高的告警信息，于是登入服务器检查确认。1. 首先我们进行OS层面的检查确认登入服务器后，我们的目的是首先要确认当前到底是哪些进程引起的负载高，以及这些进程卡在什么地方，瓶颈是什么。通常来说，服务器上最容易成为瓶颈的是磁盘I/O子系统，因为它的读写速度通常是最慢的。即便是现在的PCIe SSD，其随机I/O读写速度也是不如内存来得快。当然了，引起磁盘I/O慢得原因也有多种，需要确认哪种引起的。第一步，我们一般先看整体负载如何，负载高的话，肯定所有的进程跑起来都慢。可以执行指令 w 或者 sar -q 1 来查看负载数据，例如：[yejr@imysql.com:~ ]# w 11:52:58 up 702 days, 56 min, 1 user, load average: 7.20, 6.70, 6.47 USER TTY FROM LOGIN@ IDLE JCPU PCPU WHAT root pts/0 1.xx.xx.xx 11:51 0.00s 0.03s 0.00s w或者 sar -q 的观察结果：[yejr@imysql.com:~ ]# sar -q 1 Linux 2.6 ...继续阅读 (32)

导读   遇到SLAVE延迟很大，binlog apply position一直不动的情况如何排查？问题描述   收到SLAVE延迟时间一直很大的报警，于是检查一下SLAVE状态(无关状态我给隐去了)：Slave_IO_State: Waiting for master to send event Master_Log_File: mysql-bin.000605 Read_Master_Log_Pos: 1194 Relay_Log_File: mysql-relay-bin.003224 Relay_Log_Pos: 295105 Relay_Master_Log_File: mysql-bin.000604 Slave_IO_Running: Yes Slave_SQL_Running: Yes Last_Errno: 0 Last_Error: Exec_Master_Log_Pos: 294959 Relay_Log_Space: 4139172581 Seconds_Behind_Master: 10905   可以 ...继续阅读 (33)

       互联网高速发展的成功，得益于MySQL数据库的给力支持。MySQL本身发展的速度较快，性能方面提升显著，让传统企业也有想法使用MySQL提供服务。目前看来MySQL DBA的缺口非常大。所以欢迎加入到MySQL DBA的团队中来。     有同学一提到MySQL DBA或是DBA都把高难度入门联系到一块。我从事MySQL DBA差不多10几年了，在这里我也给大家讲述一下怎么成为一名MySQL DBA, 少走湾路，快速成为MySQL DBA。     首先MySQL大多是跑在Linux环境上的，所以我们需要学习一下Linux的知识，最基础的需要了解：Linux的安装及目录结构意义常用的Linux命令，大概20多个网络基本知识，进一步了解网关及路由相关概念,dhcp撑握一定的Linux网络服务，如果LAMP结构，dns,ftp 等   其实具备上面的条件，你就可以开始MySQL DBA的学习之路了，其它的知识，可以在学习在补。 MySQL DBA比较特殊，不象别的数据库一样，需要分为开发DBA，运维DBA，基本上MySQL DBA要把所有的活都干下来，因为MySQL太简单了，很好搞定。 那么下面给大家说一下MySQL DBA的学习路径：了解MySQL的版 ...继续阅读 (15)

导读关于 lower_case_table_names 选项的设置的建议是怎样的呢？问题由来我个人认为，纠结于这个选项设置源于有些项目是从ORACLE或SQL Server迁移过来，在这两个数据库系统中，都无需关心数据表的大小写。而在MySQL中，默认是要区分大小写的(因为Unix/Linux文件系统是区分文件名大小写的)，除非在windows系统下(windows系统是不区分大小写的)。老叶的建议我在公司制定的规范是要求默认设置lower_case_table_names=0的，也就是区分大小写。那么问题来了，如果是从ORACLE或SQL Server迁移到MYSQL的应用应该怎么处理呢？我的建议是：首先，检查确认在应用程序中(或者抓取一段时间的请求日志)，数据表名的写法是大写、小写还是混用，如果都是大写或者都是小写，那就更简单些了；其次，根据上面检查的结果，确定迁移到MySQL后统一使用大写还是小写(使用哪种规则的改动代价更小)；最后，利用Linux下的awk\sed等工具，将包含数据表关键字的地方全部替换成第二部定义好的表名方案；这样一来，就可以完成数据表名方案的切换了。当然了，肯定有人(比如某领导、某PM，你懂得的，O(∩_∩)O哈哈~)会说全部修改表名风险太大，需要全面测试，这个项目时间进度很紧张，希望能先上线。这种情况就没办法了，只能闲设置lower_case_tabl ...继续阅读 (24)

导读当磁盘空间爆满后，MySQL会发生什么事呢？又应该怎么应对？会发生什么事当磁盘空间写满了之后，MySQL是无法再写入任何数据的，包括对表数据的写入，以及binlog、binlog-index等文件。当然了，因为InnoDB是可以把脏数据先放在内存里，所以不会立刻表现出来无法写入，除非开启了binlog，写入请求才会被阻塞。当MySQL检测到磁盘空间满了，它会：每分钟：检查空间是否得到释放，以便写入新数据。当发现有剩余空间了，就会继续写入数据，一切照旧。每十分钟：如果还是发现没剩余空间，则会在日志中写入一条记录，报告磁盘空间满(这时候只写入几个字节还是够的)。应该怎么办那么，当发现磁盘空间满了之后，我们应该怎么处理呢，建议：提高监控系统检测频率，预防再次发生；及时删除不用的文件，释放空间；若有线程因磁盘满的问题被阻塞了，可先杀掉，等到下一分钟重新检测时它可能又可以正常工作了；可能因磁盘满导致某些线程被阻塞，引发其他线程也被阻塞，可把导致阻塞的线程杀掉，其他被阻塞的线程也就能继续工作了。例外有个例外的情况是：当执行 REPAIR TABLE 或者 OPTIMIZE TABLE 操作时，或者执行完 LOAD DATA INFILE 或 ALTER TABLE 之后批量更新索引时，这些操作会创建临时文件，当执行这些操作过程中mysqld发现磁盘空间满了，就会把这个涉及到的表标记为cra ...继续阅读 (15)

一、简介:   关于在linux上二进制部署mysql,我其实现在linux已经很熟练了,那是一年前的曲折之路。不过这篇文章还是有参考意义,毕竟测试环境可以再3分钟就弄好mysql服务器.还是很happy的一件事情.之前笔记是参考别人写的,现在重新整理.主要问题是centos和ubuntu上,ubuntu上需要注意的事项等说明。二、需求描述:   搞了一天在linux下安装mysql，实在身心疲惫，不过最终还是成功安装上了，不知道为什么mysql 的rpm包安装起来不好使,rpm主要再redhat系列的linux发行版中，老是有问题。但是编译安装需要安装编译环境和编译比较耗时间.于是决定安装tarball(二进制包)的方式安装mysql。从网页上翻了好久找到了安装方法。    我用的mysql的版本的是：mysql-5.5.42-linux2.6-x86_64.tar.gz    在官网上是可以下载到的。但是我一般再国内镜像站下载:http://mirrors.sohu.com/mysql/MySQL-5.5/    提供一个百度云的下载地址：链接:http://pan.baidu.com/s/1c0qbyzI密码: 6vt5三、前置条件(不是必需)：   0. ...继续阅读 (49)

导读EXPLAIN的结果中，有哪些关键信息值得注意呢？MySQL的EXPLAIN当然和ORACLE的没法比，不过我们从它输出的结果中，也可以得到很多有用的信息。总的来说，我们只需要关注结果中的几列：列名备注type本次查询表联接类型，从这里可以看到本次查询大概的效率key最终选择的索引，如果没有索引的话，本次查询效率通常很差key_len本次查询用于结果过滤的索引实际长度，参见另一篇分享(FAQ系列-解读EXPLAIN执行计划中的key_len)rows预计需要扫描的记录数，预计需要扫描的记录数越小越好Extra额外附加信息，主要确认是否出现Using filesort、Using temporary这两种情况首先看下type有几种结果，分别表示什么意思：类型备注ALL执行full table scan，这事最差的一种方式index执行full index scan，并且可以通过索引完成结果扫描并且直接从索引中取的想要的结果数据，也就是可以避免回表，比ALL略好，因为索引文件通常比全部数据要来的小range利用索引进行范围查询，比index略好index_subquery子查询中可以用到索引unique_subquery子查询中可以用到唯一索引，效率比 index_subquery 更高些index_merge可以利用index merge特性用到多个索引，提高查询效率ref_or ...继续阅读 (40)

在MySQL里，聚集索引和非聚集索引分别是什么意思，有什么区别？在MySQL中，InnoDB引擎表是(聚集)索引组织表(clustered index organize table)，而MyISAM引擎表则是堆组织表(heap organize table)。也有人把聚集索引称为聚簇索引。当然了，聚集索引的概念不是MySQL里特有的，其他数据库系统也同样有。简言之，聚集索引是一种索引组织形式，索引的键值逻辑顺序决定了表数据行的物理存储顺序，而非聚集索引则就是普通索引了，仅仅只是对数据列创建相应的索引，不影响整个表的物理存储顺序。我们先来看看两种存储形式的不同之处：简单说，IOT表里数据物理存储顺序和主键索引的顺序一致，所以如果新增数据是离散的，会导致数据块趋于离散，而不是趋于顺序。而HOT表数据写入的顺序是按写入时间顺序存储的。IOT表相比HOT表的优势是：范围查询效率更高；数据频繁更新(聚集索引本身不更新)时，更不容易产生碎片；特别适合有一小部分热点数据频繁读写的场景；通过主键访问数据时快速可达；IOT表的不足则有：数据变化如果是离散为主的话，那么效率会比HOT表差；HOT表的不足有：索引回表读开销很大；大部分数据读取时随机的，无法保证被顺序读取，开销大；每张InnoDB表只能创建一个聚集索引，聚集索引可以由一列或多列组成。上面说过，InnoDB是聚集索引组织表，它的聚集索引选择 ...继续阅读 (19)

导读EXPLAIN中的key_len一列表示什么意思，该如何解读？EXPLAIN执行计划中有一列key_len用于表示本次查询中，所选择的索引长度有多少字节，通常我们可借此判断联合索引有多少列被选择了。在这里key_len大小的计算规则是：一般地，key_len 等于索引列类型字节长度，例如int类型为4-bytes，bigint为8-bytes；如果是字符串类型，还需要同时考虑字符集因素，例如：CHAR(30) UTF8则key_len至少是90-bytes；若该列类型定义时允许NULL，其key_len还需要再加 1-bytes；若该列类型为变长类型，例如 VARCHAR(TEXT\BLOB不允许整列创建索引，如果创建部分索引，也被视为动态列类型)，其key_len还需要再加 2-bytes;综上，看下面几个例子：列类型key_len备注id intkey_len = 4+1 = 5允许NULL，加1-byteid int not nullkey_len = 4不允许NULLuser char(30) utf8key_len = 30*3+1允许NULLuser varchar(30) not null utf8key_len = 30*3+2动态列类型，加2-bytesuser varchar(30) utf8key_len = 30*3+2+1动态列类型，加2-bytes； ...继续阅读 (21)

   一、简介   MySQL在5.5.3之后增加了这个utf8mb4的编码，mb4就是most bytes 4的意思，专门用来兼容四字节的unicode。好在utf8mb4是utf8的超集，除了将编码改为utf8mb4外不需要做其他转换。当然，为了节省空间，一般情况下使用utf8也就够了。   二、内容描述   那上面说了既然utf8能够存下大部分中文汉字,那为什么还要使用utf8mb4呢? 原来mysql支持的 utf8 编码最大字符长度为 3 字节，如果遇到 4 字节的宽字符就会插入异常了。三个字节的 UTF-8 最大能编码的 Unicode 字符是 0xffff，也就是 Unicode 中的基本多文种平面(BMP)。也就是说，任何不在基本多文本平面的 Unicode字符，都无法使用 Mysql 的 utf8 字符集存储。包括 Emoji 表情(Emoji 是一种特殊的 Unicode 编码，常见于 ios 和 android 手机上)，和很多不常用的汉字，以及任何新增的 Unicode 字符等等。   三、问题根源   最初的 UTF-8 格式使用一至六个字节，最大能编码 31 位字符。最新的 UTF-8 规范只使用一到四个字节，最大能编码21位，正好能够 ...继续阅读 (43)

索引目的   索引的目的在于提高查询效率，可以类比字典，如果要查“mysql”这个单词，我们肯定需要定位到m字母，然后从下往下找到y字母，再找到剩下的sql。如果没有索引，那么你可能需要把所有单词看一遍才能找到你想要的，如果我想找到m开头的单词呢？或者w开头的单词呢？是不是觉得如果没有索引，这个事情根本无法完成？索引原理   除了词典，生活中随处可见索引的例子，如火车站的车次表、图书的目录等。它们的原理都是一样的，通过不断的缩小想要获得数据的范围来筛选出最终想要的结果，同时把随机的事件变成顺序的事件，也就是我们总是通过同一种查找方式来锁定数据。   数据库也是一样，但显然要复杂许多，因为不仅面临着等值查询，还有范围查询(>、<、between、in)、模糊查询(like)、并集查询(or)等等。数据库应该选择怎么样的方式来应对所有的问题呢？我们回想字典的例子，能不能把数据分成段，然后分段查询呢？最简单的如果1000条数据，1到100分成第一段，101到200分成第二段，201到300分成第三段……这样查第250条数据，只要找第三段就可以了，一下子去除了90%的无效数据。但如果是1千万的记录呢，分成几段比较好？稍有算法基础的同学会想到搜索树，其平均复杂度是lgN，具有不错的查询性能。但这里我们忽略了一个关键的问题 ...继续阅读 (15)

   在mysql的innodb引擎的数据库异常恢复中,一般都要求有主键或者唯一index,其实这个不是必须的,当没有index信息之时,可以在整个表级别的index_id进行恢复   创建模拟表—无主键mysql> CREATE TABLE `t1` ( -> `messageId` varchar(30) character set utf8 NOT NULL, -> `tokenId` varchar(20) character set utf8 NOT NULL, -> `mobile` varchar(14) character set utf8 default NULL, -> `msgFormat` int(1) NOT NULL, -> `msgContent` varchar(1000) character set utf8 default NULL, -> `scheduleDate` timestamp NOT NULL default '0000-00-00 00:00:00', -> `deliverState` int(1) default NULL, -> `de ...继续阅读 (23)

注：在我们推出的免费文本教程中，有一些最受欢迎的主题，本文是某系列「入门」的一部分。为了跳到某些章节，请参考下面的目录表：介绍数据库的介绍四种基本的 SQL 操作（CRUD）创建数据读取数据更新数据删除数据结论介绍SQL已经应用到了我们周围的各个角落，不管你信不信。操纵任何种类数据的每个应用程序都需要将数据存放在某处。无论它是大数据，还是只有简单数行的数据包；无论是政府、还是创业公司；无论是横跨多台服务器的大型数据库、还是运行着自己小型数据库的手机，SQL 无处不在。但是，SQL 是什么呢？SQL 代表结构化查询语言，通常，其发音为“ess-que-el”。SQL 是数据库语言，专门为了和数据库通信而建立的。SQL 是一门简单的语言，和英语语言类似，因为命令和英语句子有着类似的结构。那些句子组织为声明式的语句，这样 SQL 也被叫做声明式语言【注1】。在可视化地编写 SQL 查询语句方面，已经有很多可用的工具了，为什么还要学习一个全新的语言呢？当你用某些 SQL 工具时，重要的是理解 SQL 语言、理解可视化工具正在做什么、以及为什么那样做。有时候，需要手动写一些 SQL 语句，不仅因为这是最快的方法，而且这更强大、经常是完成预定目标的唯一方法。数据库的介绍我们刚才提到了，SQL 是数据库语言。那么，数据库是什么？数据库是一种存储机制，被设计为用来访问存储的信息及其操作。数据库里的 ...继续阅读 (65)

MySQL在以下几种情况会创建临时表：1、UNION查询； 2、用到TEMPTABLE算法或者是UNION查询中的视图； 3、ORDER BY和GROUP BY的子句不一样时； 4、表连接中，ORDER BY的列不是驱动表中的； 5、DISTINCT查询并且加上ORDER BY时； 6、SQL中用到SQL_SMALL_RESULT选项时； 7、FROM中的子查询； 8、子查询或者semi-join时创建的表； EXPLAIN 查看执行计划结果的 Extra 列中，如果包含Using Temporary就表示会用到临时表。 当然了，如果临时表中需要存储的数据量超过了上限(tmp-table-size或max-heap-table-size中取其大者)，这时候就需要生成基于磁盘的临时表了。 在以下几种情况下，会创建磁盘临时表：1、数据表中包含BLOB/TEXT列； 2、在 GROUP BY 或者 DSTINCT 的列中有超过 512字符 的字符类型列(或者超过 512字节的 二进制类型列，在5.6.15之前只管是否超过512字节)； 3、在SELECT、UNION、UNION ALL查询中，存在最大长度超过512的列(对于字符串类型是512个字符，对于二进制类型则是512字节)； 4、执行SHOW COLUMNS/FIELDS、DESCRIBE等SQL命令，因为它们的执行结 ...继续阅读 (32)

大多数MySQL规范在网上也都能找得到相关的分享，在这里要分享的是老叶个人认为比较重要的，或者容易被忽视的，以及容易被混淆的一些地方。 1、默认使用InnoDB引擎 【老叶观点】已多次呼吁过了，InnoDB适用于几乎99%的MySQL应用场景，而且在MySQL 5.7的系统表都改成InnoDB了，还有什么理由再死守MyISAM呢。 此外，频繁读写的InnoDB表，一定要使用具有自增/顺序特征的整型作为显式主键。 【参考】：[MySQL FAQ]系列 — 为什么InnoDB表要建议用自增列做主键。 2、字符集选择utf-8 【老叶观点】若为了节省磁盘空间，则建议选择latin1。建议选择utf-8通常是为了所谓的“通用性”，但事实上用户提交的utf-8数据也一样可以以latin1字符集存储。 用latin1存储utf-8数据可能遇到的麻烦是，如果有基于中文的检索时，可能无法100%准确(老叶亲自简单测试常规的中文完检索全不是问题，也就是一般的中文对比是没问题的)。 用latin1字符集存储utf-8数据的做法是：在web端(用户端)的字符集是utf-8，后端程序也采用utf-8来处理，但 character_set_client、character_set_connection、character_set_results、character_set_d ...继续阅读 (21)

客户端通过以SQL语句的形式发送请求给服务器端从而从表中获取数据或对表数据进行修改。而服务端程序使用以下两层处理模型以执行每个语句请求：dbdao.com上层包括SQL解析器(parser)和优化器(optimizer)。下层包括了一组存储引擎。需要注意的是，SQL层并不依赖于存储引擎管理的表。这意味着客户端通常不需要考虑哪个引擎被涉及处理其SQL语句，也不用考虑访问和更新的表是受到哪个引擎的管理。当然也有例外：CREATE TABLE语句有ENGINE项可用于在建立表时启用你所定义的存储引擎。ALTER TABLE语句也含有一个ENGINE项可以使你能将原表转换使用一个不同的存储引擎。一些索引类型仅在某些特定存储类型中可用。例如, 仅MyISAM引擎支持空间索引(spatial index), 仅MyISAM, InnoDB（6.4以上）支持全文索引(full-text)等。dbdao.comCOMMIT和ROLLBACK仅对由事务型存储引擎(如InnoDB)管理的表有效果。存储引擎细分下图简单展示了MySQL服务和其存储引擎之间的交互。依赖存储引擎不同会有以下不同：存储方法– 每张表中的数据按其自身的方法进行存储。事务处理能力– 某些存储引擎可以对事务进行处理，在执行多SQL语句时能确保数据库的完整性。锁– 是指每个引擎对于多线程执行场景中同 ...继续阅读 (22)