mysql笔记

[MYSQL] 查询数据库中字段含特定字符串的表
SELECT
  `COLUMN_NAME`,
  `TABLE_NAME`
FROM
  `INFORMATION_SCHEMA`.`COLUMNS`
WHERE `TABLE_SCHEMA` = '表名'
  AND `COLUMN_NAME` LIKE '%包含字符串%'


mysql 替换字符串的实现方法:

mysql中replace函数直接替换mysql数据库中某字段中的特定字符串，不再需要自己写函数去替换，用起来非常的方便。 mysql 替换函数replace()

UPDATE `table_name` SET `field_name` = replace (`field_name`,'from_str','to_str') WHERE `field_name` LIKE '%from_str%'

说明：
table_name —— 表的名字
field_name —— 字段名
from_str —— 需要替换的字符串
to_str —— 替换成的字符串

例如：
mysql> SELECT REPLACE('www.lvtao.net', 'www', 'http://www');
-> 'https://www.lvtao.net'

该函数是多字节安全的,也就是说你不用考虑是中文字符还是英文字符.

 

分布式Mysql不支持存储过程
除非所有操作都在一个DB
银行系统的 SQL 不都封在 DB 过程里面了

Bitmap算法
MySQL 原生并不支持 bitmap 类型，所以就只能存字符串，然后就根据你的 bitmap 长度以及转换方式来选择是用什么类型来存储，处理的过程在代码层面完成。

我简单说下我们做同样的事情的做法，不一定是好方法，可以一起探讨。

首先，我们操作数据库的语言是 PHP。使用的是 ASCII 表里的 0 ~ 127位的字符，所以每一个字符可以存 8bits，然后用一个 char(125) 来存 bitmap 的一个片段，每个片段可以存 1000bits。

通过 PHP 计算某一位在那一个片段的第几位，例如 2345，就在第三个片段的第345位（从1开始的话），然后通过 PHP 进行更新。当然，也可以直接用 SQL 更新，SQL 语句写起来比较麻烦，我写了半天才写出来：

unhex( conv( bin( conv( hex( STR1 ), 16, 10 ) | conv( hex( STR2 ), 16, 10 ) ), 2, 16 ) )
不过我们用 MySQL 存储也就是为了确保数据的安全性，大部分的查询操作都在 redis 里面完成，redis 原生支持 bitmap 用起来又高效又方便。


bitmap就是在一个二进制的数据中,每一个位代表一定的含义,这样最终只需要存一个整型数据,就可以解释出多个含义.

业务中有一个字段专门用来存储用户对某些功能的开启和关闭,如果是传统的思维,肯定是建一个字段来存0代表关闭,1代表开启,那么如果功能很多或者需要加功能开关,就需要不停的创建字段.

使用bitmap的思路就只需要一个字段就可以了,建一个entuserstatus字段,该字段的二进制表示中,从右到做数,从1开始数.比如第19位代表是否开始归档,那么就直接操作这一位的0和1就可以表示该用户是否开启归档功能.

email表的第19位,作为归档开启的位,1是开启 0是关闭;262144代表是第19位为1的十进制数

查询开启的

select email,enterpriseId from email where entuserstatus & 262144=262144;

开启归档

update email set entuserstatus = entuserstatus|262144 where id=670602 limit 1

关闭归档

update email set entuserstatus = entuserstatus^262144 where id=670602 limit 1

另一种形式

查询开启归档的

select id,email,enterpriseId,entuserstatus from email where entuserstatus>>18 & 1=1;

开启归档

update email set entuserstatus = entuserstatus|(1<<18)  where id=670602 limit 1

关闭归档

update email set entuserstatus = entuserstatus^(1<<18)  where id=670602 limit 1

异或(^)运算

异或运算通俗地讲就是一句话

同为假，异为真

所以它是这样的算法:

0&0=0,0&1=1,1&0=1,1&1=0

这就是微学网-程序员之家为你提供的"[MySQL] mysql中bitmap的简单运用"希望对你有所帮助.本文来自网络，转载请注明出处：http://www.weixuecn.cn/article/8488.html

Mysql数据表优化方法
查看进程列表：show processlist；

1、Optimize table table_name
如果您已经删除了表的一大部分，或者如果您已经对含有可变长度行的表（含有VARCHAR, BLOB或TEXT列的表）进行了很多更改，则应使用 OPTIMIZE TABLE。被删除的记录被保持在链接清单中，后续的INSERT操作会重新使用旧的记录位置。您可以使用OPTIMIZE TABLE来重新 利用未使用的空间，并整理数据文件的碎片。

在多数的设置中，您根本不需要运行OPTIMIZE TABLE。即使您对可变长度的行进行了大量的更新，您也不需要经常运行，每周一次或每月一次 即可，只对特定的表运行。

OPTIMIZE TABLE只对MyISAM, BDB和InnoDB表起作用。

注意，在OPTIMIZE TABLE运行过程中，MySQL会锁定表。

与Analyze Table一样，Optimize Table也可以使用local来取消写入binlog。

2、Analyze table table_name
MySQL 的Optimizer（优化元件）在优化SQL语句时，首先需要收集一些相关信息，其中就包括表的cardinality（可以翻译为“散列程度”），它表示某个索引对应的列包含多少个不同的值——如果cardinality大大少于数据的实际散列程度，那么索引就基本失效了。

我们可以使用SHOW INDEX语句来查看索引的散列程度：

SHOW INDEX FROM PLAYERS;

TABLE   KEY_NAME COLUMN_NAME CARDINALITY
------- -------- ----------- -----------
PLAYERS PRIMARY PLAYERNO             14

因为此时PLAYER表中不同的PLAYERNO数量远远多于14，索引基本失效。
下面我们通过Analyze Table语句来修复索引：

ANALYZE TABLE PLAYERS;
SHOW INDEX FROM PLAYERS;
结果是：
TABLE   KEY_NAME COLUMN_NAME CARDINALITY
------- -------- ----------- -----------
PLAYERS PRIMARY PLAYERNO           1000

此时索引已经修复，查询效率大大提高。

需要注意的是，如果开启了binlog，那么Analyze Table的结果也会写入binlog，我们可以在analyze和table之间添加关键字local取消写入 

-----------------------------------------------------------------------------------------

MySQL查询优化器是MySQL服务器的重要组成部分，可为查询设置最佳的问题执行。对于特定查询，查询优化器使用存储的键分布和其他因素来确定执行联接时应联接表的顺序以及特定表应使用哪个索引。

但是，键分布有时可能不准确，例如，在表中进行了许多数据更改(包括插入，删除或更新)之后。如果键分配不正确，问题优化器可能会选择一个讨厌的查询执行安排，这将导致严重的性能问题。

ANALYZE TABLE语句可以解决以上问题。我们需要如下运行ANALYZE TABLE语句-ANALYZE TABLE table_name

恢复删除的数据
重新创建一个数据库

找到最近一次完整备份，导入完整备份

打开最近一次完整备份到你要恢复点的binlog,前提是打开log-bin

根据时间点，执行binlog日志，恢复到你所要的恢复点

binlog操作方法

mysqlbinlog -hlocalhost -uroot -p mysql-bin.000001>1.sql

delete删除数据以后，使用optimize优化数据表的一些注意事项，如下
当对MySQL进行大量的增删改操作的时候，很容易产生一些碎片，这些碎片占据着空间，所以可能会出现删除很多数据后，数据文件大小变化不大的现象。当然新插入的数据仍然会利用这些碎片。但过多的碎片，对数据的插入操作是有一定影响的，此时，我们可以通过optimize来对表的优化。
为了更加直观的看到数据碎片，Mysql可以使用如下命令查看
show table status [like table_name]

data_free选项代表数据碎片。
针对MySQL的不同数据库存储引擎，在optimize使用清除碎片，回收闲置的数据库空间，把分散存储（fragmented）的数据和索引重新挪到一起(defragmentation)，对I/O速度有好处。
当然optimize在对表进行操作的时候，会加锁，所以不宜经常在程序中调用。

optimize补充如下：

1.MySQL官方建议不要经常(每小时或每天)进行碎片整理，一般根据实际情况，只需要每周或者每月整理一次即可。
2.OPTIMIZE TABLE只对MyISAM，BDB和InnoDB表起作用，尤其是MyISAM表的作用最为明显。此外，并不是所有表都需要进行碎片整理，一般只需要对包含上述可变长度的文本数据类型的表进行整理即可。
3.在OPTIMIZE TABLE运行过程中，MySQL会锁定表。
4.默认情况下或者开启了独享表空间时，直接对InnoDB引擎的数据表使用OPTIMIZE TABLE，可能会显示「 Table does not support optimize, doing recreate + analyze instead」的提示信息。这个时候，我们可以用mysqld --skip-new或者mysqld --safe-mode命令来重启MySQL，以便于让其他引擎支持OPTIMIZE TABLE。

#InnoDB存储引擎
InnoDB引擎的表分为独享表空间和同享表空间的表，我们可以通过show variables like ‘innodb_file_per_table’;来查看是否开启独享表空间。


MySQL删除数据几种情况以及是否释放磁盘空间
1、drop table table_name 立刻释放磁盘空间 ，不管是 InnoDB和MyISAM

2、truncate table table_name 立刻释放磁盘空间 ，不管是 Innodb和MyISAM 。

      truncate table其实有点类似于drop table 然后create。只不过这个create table 的过程做了优化，比如表结构文件之前已经有了等等，就不需要重新再搞一把。所以速度上应该是接近drop table的速度。

3、对于delete from table_name  删除表的全部数据

      对于MyISAM 会立刻释放磁盘空间 （应该是做了特别处理，也比较合理）；     InnoDB 不会释放磁盘空间

4、对于delete from table_name where xxx带条件的删除

     不管是innodb还是MyISAM都不会释放磁盘空间。

5、delete操作以后 使用optimize table table_name 会立刻释放磁盘空间。不管是InnoDB还是MyISAM 。

      所以要想达到清理数据的目的，请delete以后执行optimize table 操作。

6、delete from表 以后虽然未释放磁盘空间，但是下次插入数据的时候，仍然可以使用这部分空间