Study_Database

[Home] [TOC]

Study_Database_MySQL

MySQL Sudy

Updated Dec 16, 2013 by jht5...@gmail.com

安装

// 安装
rpm -ivh MySQL_server_X.X.XX.linux2.6.x86_64.rpm
// 启动
service mysql start
// 停止
service mysql stop

service mysql {start|stop|restart|reload|force-reload|status}

/var/log/mysqld.log

mysql -u root
mysql -h <IP> -u <USERNAME> -p

查看版本: select version();

存储引擎

引擎	说明
MyISAM	MyISAM was the default storage engine for the MySQL relational database management system versions prior to 5.5
InnoDB	InnoDB is the default storage engine for MySQL as of MySQL 5.5
TokuDB	TokuDB is a storage engine for MySQL and MariaDB that is specifically designed for high performance on write-intensive workloads

维护

查看有哪些用户 select * from mysql.user

SQL

DATE_ADD DATE_ADD(date,INTERVAL expr type) , DATE_SUB(date,INTERVAL expr type) , date + expr type
expr type sample: INTERVAL 1 SECOND, INTERVAL 1 DAY etc.
GROUP CONCAT select type, group_concat(id) from table group by type;

create database databasename;

drop table if exists tablename;

锁

当update or delete，where条件中为非索引条件时，锁全表。这是为什么在项目上线时大量死锁的原因。
当update or delete，where条件中为非主键索引时，根据非主键索引找到对应的主键索引，并锁主建对应的记录。虽为行锁，但锁了多行。并且，mysql的行锁还有一个很隐蔽的问题，即使用主键索引，在一定条件下，可能会锁相邻记录。也就是说，锁的范围超出真正需要update的行。对这样的情形，使用where id=888的样式，逐行更新记录，则是非常安全的行为。
MySQL drop table可能会导致全库hang住，所以删除大表，最好是通过硬链接的方式，为物理文件建立一个硬链接，然后从数据库中删除后，最后再删除物理文件。
并发插入数据导致死锁 ^[3]

事务隔离级别 ^[4]

隔离级别	是否脏读	是否不可重复读	是否幻读
未提交读 (Read uncommit )	Y	Y	Y
提交读 (Read Committed)	N	Y	Y
可重复读 (Repeatable Read)	N	N	Y
序列化读 (Serializable Read)	N	N	N

脏读：还未提交的内容可以被读取到
不可重复读：就是假如A查上来一个数据叫张三，另一个人把张三改成了李四，A刷新，数据变成了李四，重复读取数据不对了，张三变成李四了，这就是不可重复读
幻读：比如A查询age<20的记录，查询到5条，再次刷新，记录变成了10条，这是因为期间别人又录入了5条

MySQL 默认是可重复读。

// 查看事务隔离级别
select @@tx_isolation;

// 设置事务隔离级别
set session transaction isolation level READ UNCOMMITTED;

性能

MySQL INSERT性能优化，将多条INSERT语句合并成一条INSERT语句，在事务中处理也会有性能提升。

INSERT INTO `insert_table` (`datetime`, `uid`, `content`, `type`) VALUES ('0', 'userid_0', 'content_0', 0);
INSERT INTO `insert_table` (`datetime`, `uid`, `content`, `type`) VALUES ('1', 'userid_1', 'content_1', 1);

合并为：

INSERT INTO `insert_table` (`datetime`, `uid`, `content`, `type`) VALUES 
('0', 'userid_0', 'content_0', 0), 
('1', 'userid_1', 'content_1', 1);

MySQL特殊点

MySQL在执行UPDATE时，如果更新前和更新后数据一样时不会产生binlog日志，Oracle会产生日志

用户/权限

CREATE USER 'username'@'%' IDENTIFIED BY 'password';

SHOW GRANTS FOR 用户名@域名或IP
  show grants for username;

GRANT 权限列表 ON 数据库名.表名 TO 用户名@来源地址 [IDENTIFIED BY '密码']
  grant select,insert,update,delete on schema.* to username@'%' identified by 'password';

REVOKE 权限列表 ON 数据库名.表名 FROM 用户名@域名或IP
  revoke ALL on schema.* from username@'%';

grant ALL PRIVILEGES on *.* to username@'%' identified by 'password' [WITH GRANT OPTION].

FLUSH PRIVILEGES;

show [full] processlist;

show variables; //  查看MySQL参数

函数

STR_TO_DATE('2013-04-10 00:00:00', '%Y-%m-%d %H:%i:%s')

String 转数字：
cast(table_field as signed)

自增长ID

MySQL中可以通过环境变量设置自增长ID的生成步长及起始值：

SHOW VARIABLES LIKE 'auto_inc%';
auto_increment_increment
auto_increment_offset

独立表空间

innodb_file_per_table=1

The TIMESTAMP data type has a range of '1970-01-01 00:00:01' UTC to '2038-01-09 03:14:07' UTC.

Google MySQL: https://code.google.com/p/google-mysql/

http://www.mysqlperformanceblog.com/

参考资料

[1]. http://firedragonpzy.iteye.com/blog/1388562
[2]. http://blog.jobbole.com/29432/
[3]. http://thushw.blogspot.com/2010/11/mysql-deadlocks-with-concurrent-inserts.html
[4]. http://www.zhurouyoudu.com/index.php/archives/735/
[5]. http://dev.mysql.com/doc/refman/5.5/en/innodb-parameters.html

安装