简单记录下在centOS上安装MySQL（MariaDB）的过程。 这里我并没有选择特定的MySQL版本，使用的是源默认提供的版本。 1. 执行安装命令： 执行如下指令安装MySQL数据库。 注意这里同时安装了mysql和mysql-server。 在输出一长串信息后，期间可能会需要输入y表示确认，MySQL就安装好了。 安装结束后还需要手动启动MySQL。 2. 启动MySQL 执行如下指令启动MySQL。 启动MySQL后会输出日志到/var/log/mysql/mysql.log。 在日志中可以看到下面这一句： 日志提示mysql root用户的初始密码是空的。据说有的时候会生成一个随机密码写入到日志中，但这次安装明显不是这样的。 另外在日志中也可以看到mysql的版本是8.0.17： 然后，执行如下指令，设置MySQL数据库开机启动 3. 设置root用户密码 执行如下命令设置root用户密码： 执行命令后会提示输入密码并确认。 4. 修改数据库时区 执行如下命令后输入密码进入MySQL数据库： 切换到目标数据库： 查看数据库时间： 查看时区设置： system_time_zone 表示系统使用的时区是EDT即北美的东部夏令时(-4h)。 time_zone 表示 MySQL 采用的是系统的时区。 之前以为如果在安装MySQL就通过tzselect并在profile中修改了时区设置会起到作用，后来测试发现是无效的。 ╮(╯▽╰)╭ 临时修改时区执行如下指令： 永久修改时区需要修改mysql配置文件。执行如下指令打开MySQL配置文件： 在条目[mysqld]下添加时区配置信息： 当然直接编辑/etc/my.cnf也不是不行，不过讲究点儿还是好的。 重启MySQL服务： 再看时间会看到时区修改成功。 5. 创建用户并授权 为MySQL数据库添加一个新用户zhyea，并将密码设置为zhyPass，指令如下： 授予用户zhyea对数据库chobit的所有权限： 6. 数据备份 如需要执行数据迁移，先回到原数据库服务器上，进入MySQL命令行，执行如下命令完成数据备份： 用户为zhyea；密码为zhyPass；目标数据库 chobit。备份文件存储到了/root/zhyea/zhy.sql这个位置。 下载备份文件zhy.sql。 7. 数据迁移 将备份的sql文件 zhy.sql 挪到现服务器上。可以考虑使用rz指令上传数据文件，如未安装该工具可以用如下指令安装： 进入MySQL命令行，切换到目标数据库，执行如下命令完成数据迁移： 8. 删除已安装的版本 执行如下指令： 一切搞定。 End！
[阅读更多...]-
Linux 下安装MySQL并迁移备份
-
MySQL的索引
为什么要使用索引 使用索引主要是为了加快查询速度。通常影响查询速度的最大的也是索引的正确使用。 一个没有索引的数据表就是一个无序的数据行集合，如果我们要查询数据表的某个数据行，就要检查数据表的每一个数据行，看是否与期望值匹配。如果数据表很大，这个过程就很慢，效率很低。 针对某个（或某些）字段创建的索引中包含了数据表里每一个数据行的对应字段的值。索引中的值是经过分类排序的。使用索引可以得知匹配数据行在什么位置结束，从而跳过其余部分。这也是索引可以提高搜索效率的一个原因。另外一个原因则是定位算法的使用，使用定位算法可以不用从索引开始位置线性扫描就能快速定位到第一个匹配项。关于定位算法目前先不多说，只需要知道这些算法可以加快索引速度，并且索引的确是一个提速的好方法。 索引的存储细节 对于不同的存储引擎，索引的存储细节有所不同。 对于MyIsam数据表来说，数据表的数据行是在数据文件里，而索引值保存在索引文件里。一个数据表可以有多个索引，但如果这样的话，所有的索引都储存在同一个索引文件里。索引文件里的每一个索引都是由分类的关键记录数组组成的，这些数组用于快速访问数据文件。 Innodb存储引擎没有按照上面的方法将数据行和索引值分开。默认情况下，Innodb存储引擎使用的是一个表空间。在这个表空间里，它管理着所有的Innodb类型数据表的数据和索引的存储。虽然可以通过配置让Innodb为每个数据表创建一个自己的表空间，数据表的数据和索引也是保存在同一个表空间文件里的。 使用索引的方式 MySQL使用索引的方式有如下几种： 快速查询，一是在查询操作中把与where子句所给出的条件相匹配的数据行尽快找出来；二是在关联操作中把与其他数据表里的数据行相匹配的数据行尽快找出来； 对于使用MIN()或MAX()函数的查询，如果数据列带索引，那么它的最大值和最小值能够被迅速找到而不用通过逐行检查的方法来查找； MySQL经常使用索引来迅速地完成order by子句和group by子句的排序和分组操作； MySQL可以通过使用索引来避免为一个查询整体读取数据行。比如要从MyIsam数据表的一个有索引的数据列里选取值，而且并不打算选取数据表里的其他数据列。在这种情况下，MySQL从这个索引文件读取索引值时，实际上就已经得到了这个值，而不需要再次读取数据文件。 索引的缺点 一般情况下，使用索引都是最好的提速方案之一。但是使用索引也有一些时间和空间上的缺点： 索引加快了查询速度，但是却降低了在带索引的数据列里插入、删除以及修改数据的速度。这是因为写入一条记录时不仅会写入到数据文件，还会要求维护所有的索引。一个数据表的索引越多，需要做出的调整就越多，平均性能就下降越多。 索引要占据磁盘空间，索引越多，占据的表空间越大，也就容易更快达到数据表的尺寸极限。 有这两方面的缺点，可以得到一个结论：如果不需要某个特定的索引来加快查询速度，就不要创建它。 挑选索引 挑选索引及使用索引可以从如下几个方向来考虑。 1. 尽量为用来搜索、排序或分组的数据列编制索引，不要为作为输出显示的数据列编制索引。换句话说，适合用来作为索引的列是在where子句中、在联结子句中、在order by或者group by子句中出现的数据列。 2. 综合考虑各数据列的维度。数据列的维度等于它所容纳的非重复值的个数。当查询优化程序确定出某一个数值在数据表中出现的频率超过30%时，查询优化程序通常会跳过索引而进行全表扫描。比如标识性别的列就不值得为其创建索引。 3. 为短小的值进行索引。在创建索引时，应尽量选择比较“小”的数据类型。因此在建表时对关键字段类型的选择需要注意。 4. 为字符串值的前缀编索引。假如要为字符串的数据列编索引，应当尽可能给出前缀长度。比如有一个长度为char(200)的数据列，大多数的值的前10个或者前20个字符都是唯一的，那就不用为整个数据列编制索引，仅为前20个或前30个字符编制索引即可。这样不仅可以节省索引空间，且可以使查询进行得更快。 5. 充分利用最左边的前缀。这个是在使用复合索引时需要注意的一点。假使有这样一个学生信息表(学校编号,班级编号,学生编号,学生姓名,性别,电话,联系地址)，为其中的学校编号、班级编号、学生编号创建了一个复合索引。索引中的数据行是以“学校编号/班级编号/学生编号”的顺序存储的，因此索引中的记录也是以“学校编号/班级编号”的顺序存储的，自然也是以“学校编号”的顺序存储的。有了这个复合索引后就无需以“学校编号”或“学校编号/班级编号”再创建索引。在这例子中，执行查询时，如果没有在where子句中使用学校编号，那么就无法使用索引。如果查询的是学校编号和学生编号的组合，也只能使用学校编号这个索引，而无法使用复合索引。 6. 让索引类型与打算进行比较操作的类型保持匹配。这是说在挑选索引类型的时候，一定要考虑打算在被索引的数据列上进行什么类型的比较操作。比如散列索引在判断是否相等时速度极快，在进行范围比较如“>、<、>=、<=”或者between/and操作时却表现不佳；B树索引在使用精确比较操作和范围比较操作时都很有效率。如果匹配模式是以一个纯字符串而不是一个通配符开头的话，B树索引还可以使用LIKE操作符进行模式匹配操作。 7. 谨慎使用比较函数。对于某些比较函数（比如STRCMP()），服务器必须为每个数据行计算出函数值，这就排除了使用该数据列上的索引的可能性。 8. 利用慢查询日志找出性能低劣的查询。 #######
[阅读更多...] -
MySQL分区 – 限制
这一节主要说明MySQL中对分区的一些限制和不足。 禁止使用的结构 如下MySQL结构禁止在分区表达式中使用： 存储过程、存储函数、UDF（用户自定函数）或者插件； 已声明的变量和用户变量。 算数运算符和逻辑运算符 分区表达式中是允许使用+、-和*这些算术运算符的，但是，运算结果必须是一个整型（Integer）值或NULL值（除非是采用了KEY或LINEAR KEY分区方案）； DIV运算函数也是可以使用的，但是“/”运算符却是不被允许的； 位运算符|、&、^、<<、>>和~也不允许在分区表达式中使用。 HANDLER声明 在MySQL5.6中，不支持对分区表使用HANDLER声明。 Server SQL mode 在创建分区表后，修改SQL mode需要慎重，不然有可能会导致脏数据、数据丢失或者分区表不可访问。 数据库服务器sql mode也会影响分区表的数据复制。如果master和slave的sql mode不一致，就有可能会导致master和slave主机上的数据不一致，甚至会导致从master复制数据到slave失败。因此，要尽量让slave主机的sql mode和master保持一致。 对性能的影响 分区操作对性能有如下影响： 文件系统操作： 分区或者重分区的操作依赖于文件系统的支持。这意味着分区的效率也会受到诸如文件系统类型、字符集、硬盘转速、交换区大小、操作系统处理效率以及一些MySQL文件处理参数的影响。最重的是要确定是否已经开启了large_files_support，并为open_files_limit设置恰当的值。如果是MyIsam引擎的话，提高myisam_max_sort_file_size 的值可以提升性能；如果要提升InnoDB分区表的性能可以开启innodb_file_per_table设置。 MyIsam和分区文件描述符： 对于一个MyIsam的分区表，MySQL会为每个打开的分区提供两个文件描述符。这意味着操作一个MyIsam的分区表要比相同的不分区的表需要太多文件描述符，尤其是在执行ALTER TABLE（比如要增加一个分区）操作时（大概会是分区数量的4倍）。这就需要注意open_files_limit是不是设置得太低了。 表锁定： 在一个表上做分区操作时会对表进行写锁定。此时读取数据不会受到影响，要执行的INSERT或UPDATE操作会在分区操作完成后执行。 存储引擎： 在MyIsam引擎的表上执行分区操作、查询或是更新操作通常都会比InnoDB或NDB快一些。 索引和分区调整： 在使用索引提升查询效率这一项上，不分区的表要比分区的表效果明显。但是使用分区本身就可以极大的提升查询效率。 分区的表不支持索引条件推送。 LOAD DATA的性能： 在MySQL5.6中，LOAD DATA使用缓冲来提升性能。需要注意的是，MySQL会为每个分区使用13KB的内存来做这些事情。 分区的最大数量 不使用NDB存储引擎的话，在MySQL5.6.7版本之前，一个表的最大分区数量是1024；在MySQL5.6.7版本以后，一个表的分区数量的上限是8192。这里提到的分区也包括二级分区。 使用NDB存储引擎的话，这个上限受到MySQL集群软件的版本、数据节点的数量、还有一些其它因素的影响。 在创建一个有大量分区的表的时候，有可能会收到这样的错误信息：Got error … from storage engine: Out of resources when opening file。此时可以尝试调高open_files_limit的值。然而，这个还要看操作系统是否支持。此外考虑到其它的因素，在某些情况下，使用大量的（数百个）分区并不能得到更好的性能提升。 不支持查询缓存 分区的表不支持查询缓存。从MySQL5.6开始，遇到对分区表的查询时，查询缓存会自动关闭，而且这个不能手动开启。 每个Partition的KEY缓存 MySQL5.6支持MyIsam分区表的KEY缓存，这个可以使用CACHE_INDEX和LOAD_INDEX_INTO_CACHE声明来实现。 可以为一个、几个或者全部分区定义KEY缓存。一个、几个或者全部分区的索引也会预加载到KEY缓存中。 在InnoDB分区表中不支持外键 使用InnoDB存储引擎的分区表不支持外键。由此可以引申出如下两个声明： 一个InnoDB表中使用了分区后就不会存在外键引用，一个InnoDB表中存在外键引用就不能再被分区； 在InnoDB表中不可能存在指向分区表的外键引用，在InnoDB的分区表中不会存在被外键引用的字段。 ALTER TABLE … ORDER BY 在一个分区表上执行 ALTER TABLE … ORDER BY column 语句的结果是：不会得到全表排序，只是在每个分区里完成了排序。 使用REPLACE 调整主键的影响 在某些情况下，修改一个表的主键是有必要的。但是需要注意，如果是使用了REPLACE 语句调整主键，可能会得到完全不同的结果。 全文索引 分区表不支持全文索引或者是全文检索，即使使用的存储引擎是InnoDB或MyIsam也不行。 空间字段 分区表中不可以有空间数据类型比如POINT或者GEOMETRY的字段。 临时表 临时表不可以被分区。 系统日志表 对系统日志表进行分区也是不可行的。在系统日志表执行 ALTER TABLE … PARTITION BY … 语句会报错。 分区键的数据类型 分区键必须是一个Integer字段或者返回值为Integer的表达式。采用枚举类型字段的表达式也是不可用的。字段或表达式的值也可以是NULL。 这个限制有两个例外： 1. 当采用KEY或者是LINEAR KEY分区方案时，可以使用TEXT或BLOB之外的任意数据类型，因为MySQL内置的key-hash计算函数可以从这些数据类型计算出正确的数据类型。举例，如下的两个SQL语句都是正确的： 2. 当采用RANGE COLUMNS或者LIST COLUMNS分区方案时，可使用字符串、DATE或者是DATETIME作为分区键。 这两个例外都不适用于BLOB或TEXT类型的字段。 子查询 分区键不可以是子查询，就算子查询返回的是一个整型值或者是NULL值。 关于二次分区 二次分区必须采用HASH或者KEY分区方案。只有RANGE和LIST方案的分区可以被二次分区。HASH和KEY方案的分区不可以被二次分区。 不支持延迟操作 使用延迟INSERT DELAYED向分区表中插入记录是不被允许的。执行时会报错。 关于数据目录和索引目录 在分区表上使用 DATA DIRECTORY和INDEX DIRECTORY有如下限制： 表级别的
[阅读更多...] -
MySQL分区 – 概述
引言 MySQL的分区方案可以将一个表中的数据分别保存到不同的位置。用户用来分区的规则被称为分区函数。分区函数可以是模运算函数，数值List集合匹配，内置的hash函数或者线性Hash函数。用户可以按照自己的需要选择分区函数，并为分区函数提供一个参数表达式。这个参数表达式可以是一个字段的值，也可以是面向一个或多个字段的函数运算，也可以是一个或多个字段的值的集合，这取决于用户选择的分区方案。 MySQL的分区方案是一种水平分区方案，也就是说一个表中的行可以被分发到不同的物理分区。MySQL5.6暂时还不支持垂直分区。 要判断使用的MySQL是否支持分区可以在MySQL命令行中使用“SHOW PLUGINS;”语句查询MySQL中的插件。如果查询结果中有partition插件，且Status为Active，那么可以执行分区。这个信息也可以查询表INFORMATION_SCHEMA.PLUGINS得到。 分区和存储引擎 在创建分区表的时候，可以使用MySQL支持的大部分存储引擎。MySQL的分区引擎运行在一个独立的层面上，可以与任何存储引擎自由交互。在MySQL5.6中，一个分区表的所有分区的存储引擎必须是一致的。举个例子：对于同一个表，不允许这个表的一个分区的引擎是MyIsam，而另一个分区的引擎是Innodb。 在使用MERGE, CSV, 或者FEDERATED存储引擎的时候，不可以使用分区。 NDB存储引擎只支持KEY或LINEAR KEY分区方案。 在建表语句中，存储引擎的声明要在分区声明语句前面。如下： 需要注意的点 分区作用于一个表的全部数据上，不可能只对数据分区而不对索引分区，反过来也是的。同样，也不可能只对一个表的部分数据进行分区。 在建表时，可以在分区声明语句中使用DATA DIRECTORY和INDEX DIRECTORY将数据或索引保存到指定的目录。如果分区表使用的是MyIsam引擎，DATA DIRECTORY和INDEX DIRECTORY在windows系统上的表并不适用。如果使用的是Innodb引擎，则全平台都可以使用。 在一个表的分区表达式中使用的字段必须是这个表的全部唯一键（包括主键）的一部分。这就意味着使用如下语句创建的表不能被分区： 因为主键pk和唯一键uk并没有交集，这就意味着没有任何字段可以用在分区表达式中。 补充一点，如果表中没有主键或唯一键，可以取任意字段用在分区表达式中。 此外，可以使用MAX_ROWS和MIN_ROWS来限制每个分区中保存的记录的最大数量和最小数量。 使用分区的优势 使用分区有如下优势： 可以突破硬盘或文件系统分区的限制，存储更多的数据； 采用删除分区的方式可以轻松地从一个分区表中删除无用的数据，同样也可以创建新的分区来保存新的数据； 在执行查询时，如果在where子句中查询的记录保存在一个或少数几个分区上，查询的效率将会得到极大的提升；MySQL5.6也支持在查询语句中指定要查询的分区，如 还有一些关于分区的好处，MySQL现在并不支持，但是是可以期待的： 在使用聚集函数时，函数的运算可以在同时在几个分区上并发执行； 可以同时从多个硬盘检索数据，得到更大的吞吐量。
[阅读更多...]