1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 | <pre>DELIMITER $$ CREATE FUNCTION `regex_replace`(pattern VARCHAR(1000),replacement VARCHAR(1000),original VARCHAR(1000)) RETURNS VARCHAR(1000) DETERMINISTIC BEGIN DECLARE temp VARCHAR(1000); DECLARE ch VARCHAR(1); DECLARE i INT; SET i = 1; SET temp = ''; IF original REGEXP pattern THEN loop_label: LOOP IF i>CHAR_LENGTH(original) THEN LEAVE loop_label; END IF; SET ch = SUBSTRING(original,i,1); IF NOT ch REGEXP pattern THEN SET temp = CONCAT(temp,ch); ELSE SET temp = CONCAT(temp,replacement); END IF; SET i=i+1; END LOOP; ELSE SET temp = original; END IF; RETURN temp; END$$ DELIMITER ;</pre> |
Archive for the 'MySQL' Category
为了给高并发情况下的mysql进行更好的优化，有必要了解一下mysql查询更新时的锁表机制。
一、概述
MySQL有三种锁的级别：页级、表级、行级。
MyISAM和MEMORY存储引擎采用的是表级锁（table-level locking）；BDB存储引擎采用的是页面锁（page-level
locking），但也支持表级锁；InnoDB存储引擎既支持行级锁（row-level locking），也支持表级锁，但默认情况下是采用行级锁。
Continue reading ‘MySQL锁表机制分析’
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 | [mysqld] user=mysql bind-address=127.0.0.1 datadir=/var/lib/mysql pid-file=/var/run/mysqld/mysqld.pid socket=/var/run/mysql/mysql.sock port=3306 tmpdir=/tmp language=/usr/share/mysql/english skip-external-locking query_cache_limit=64M query_cache_size=32M query_cache_type=1 max_connections=15 max_user_connections=300 interactive_timeout=100 wait_timeout=100 connect_timeout=10 thread_stack=128K thread_cache_size=128 myisam-recover=BACKUP key_buffer=64M join_buffer=1M max_allowed_packet=32M table_cache=512M sort_buffer_size=1M read_buffer_size=1M read_rnd_buffer_size=768K max_connect_errors=10 thread_concurrency=4 myisam_sort_buffer_size=32M skip-locking skip-bdb expire_logs_days=10 max_binlog_size=100M server-id=1 [mysql.server] user=mysql basedir=/usr [safe_mysqld] bind-address=127.0.0.1 err-log=/var/log/mysqld.log pid-file=/var/run/mysqld/mysqld.pid open_files_limit=8192 SAFE_MYSQLD_OPTIONS=”–defaults-file=/etc/my.cnf –log-slow-queries=/var/log/slow-queries.log” [mysql] [isamchk] key_buffer=64M sort_buffer=64M read_buffer=16M write_buffer=16M [myisamchk] key_buffer=64M sort_buffer=64M read_buffer=16M write_buffer=16M [mysqlhotcopy] interactive-timeout max_heap_table_size = 64 M tmp_table_size = 64 M !includedir /etc/mysql/conf.d/ |
RAND() 函数是 MySQL 提供的产生随机数的函数。没有指定参数时，每次执行会返回 0-1 的浮点数：
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 | mysql> select rand(); +------------------+ | rand() | +------------------+ | 0.56110724207117 | +------------------+ 1 row in set (0.02 sec) mysql> select rand(); +-----------------+ | rand() | +-----------------+ | 0.3343874880433 | +-----------------+ 1 row in set (0.00 sec) |
在某些情况中， MySQL 可以使用一个索引来满足 ORDER BY 子句，而不需要额外的排序。 where 条件和 order by 使用相同的索引，并且 order by 的顺序和索引顺序相同，并且 order by 的字段都是升序或者都是降序。
例如：下列 sql 可以使用索引。
1 2 3 4 5 |
但是以下情况不使用索引：
–order by 的字段混合 ASC 和 DESC
– 用于查询行的关键字与 ORDER BY 中所使用的不相同
– 对不同的关键字使用 ORDER BY 。
默认情况下， MySQL 在执行 GROUP BY col1 ， col2.... 操作的时候，会按照 GROUP BY 字段的顺序进行排序。如果显式包括一个包含相同的列的 ORDER BY 子句，则对 MySQL 的实际执行性能没有什么额外的影响。
如果查询包括 GROUP BY 操作， 但是不需要对结果进行排序，或者对默认的排序结果不满意，希望获得结果后再由程序进一步处理的时候，可以指定 ORDER BY NULL 禁止排序，从而避免排序结果的消耗。 Continue reading ‘MySQL性能优化19 – GROUP BY 操作的优化’
使用 GROUP BY 的 WITH ROLLUP 字句可以检索出更多的分组聚合信息，它不仅仅能像一般的 GROUP BY 语句那样检索出各组的聚合信息，还能检索出本组类的整体聚合信息。 Continue reading ‘MySQL性能优化18 – 使用 GROUP BY WITH ROLLUP 改善统计性能’
执行 INSERT 操作的时候，可以考虑使用以下的方式优化 SQL 的执行效率：
- 如果你同时从同一客户插入很多行，使用多个值表的 INSERT 语句。这比使用分开 INSERT 语句快。
- 如果你从不同客户插入很多行，能通过使用
INSERT DELAYED语句得到更高的速度。Delayed的含义是让 INSERT 语句马上执行，其实数据都被放在内存的队列中，并没有真正写入磁盘；这比每条语句分别插入要快的多；LOW_PRIORITY刚好相反，在所有其他用户对表的读写完后才进行插入； - 将索引文件和数据文件分在不同的磁盘上存放；
- 如果进行批量插入，可以增加 bulk_insert_buffer_size 变量值的方法来提高速度，但是，这只能对 MyISAM 表使用；
- 当从一个文本文件装载一个表时，使用
LOAD DATA INFILE。这通常比使用很多 INSERT 语句快 20 倍； - 根据应用情况使用
REPLACE语句代替INSERT； - 根据应用情况使用
IGNORE关键字忽略重复记录。
对于 InnoDB 类型的表，这种方式并不能提高导入数据的效率。对于 InnoDB 类型的表，我们有以下几种方式可以提高导入的效率：
因为 InnoDB 类型的表是按照主键的顺序保存的，所以将导入的数据按照主键的顺序排列，可以有效的提高导入数据的效率。如果 InnoDB 表没有主键，那么系统会默认创建一个内部列作为主键，所以如果可以给表创建一个主键，将可以利用这个优势提高导入数据的效率。
在导入数据前执行 SET UNIQUE_CHECKS=0 ，关闭唯一性校验，在导入结束后执行 SET UNIQUE_CHECKS=1 ，恢复唯一性校验，可以提高导入的效率。
如果应用使用自动提交的方式，建议在导入前执行 SET AUTOCOMMIT=0 ，关闭自动提交，导入结束后再执行 SET AUTOCOMMIT=1，打开自动提交，也可以提高导入的效率。
Recent Comments