每日归档： 2017年4月20日

需求分析

创表出来表结构

核心SQL

总结分析: 可以会遇到什么样的性能瓶颈

表

存储过程

触发器

事件

表:字段，　索引(约束)

高并发不允许触发器和存储过程

要背下来：占用空间，每个值的范围

固定小数点类型

Decimal 

salary decimal(8,2)

最大65位

可以用来记录工资

不要用四舍五入

做彩票时: decimal 

每次钱会少个几百块或是多个几百块

交易量5千万左右

int 单位按份来存储

单位: 分

导出乱码

共享表空间转成独立表空间

浮点数

浮点数用二进制表示

小数点后复杂，移位补位，算法符号变法

尽可能减少浮点数出现，运算占开销很大

不能做精确值比较

Floathe（M,D）

    小数点后：0-23位精度，存储占用<=4个字节

DOUBLE（M,D）

    小数点后：24-53位精度，存储占用8个字节

BIT

status&0/10

BIT（M）#M用于表示有多少个二进制位.M可以支持0-64位

对于bit写入会左填充0.如果bit（8）写入b'1001' 查询得到的值 ：00001001

数据类型

数据类型支持属性

    int(4)

    int(4)zerofill 写入数字5，显示：0005

    int(11)auto_increment 每次自增加一

int（1）存100是可以存的 只是有坑，1是宽度

数据溢出 

5.5和5.6有区别 

Set sql_mode = ‘’;
Select cast(0 as UNSIGNED) – 1;

SELECT 9223372036854775807 + 1;
SELECT CAST(9223372036854775807 AS UNSIGNED) + 1;

timestamp占用字节数更少

varchar 

utf8汉字需要占用3个字节

255 byte能存多少个汉字

varchar 低于255 byte需在一个字节 用来记录长度大于255 2个字节

varchar最大长度65535 

如果存的字母就按ascII 2字节 

用记录长度

char 定长的

varchar变长

其他类型能记住存多大就行

Enum

5.6在线表空间定义

减少join

核心sql尽可能不要join

配置好配置文件然后到/usr/local/mysql/bin

执行mysql_multi start 就可以了

#my.cnf
[client]
port            = 3306
socket          = /tmp/mysql.sock

[mysql]
prompt="\\u@\\h:\p  \\R:\\m:\\s [\\d]>"
#tee=/data/mysql/mysql_3306/data/query.log
no-auto-rehash

[mysqld_multi]
mysqld = /usr/local/mysql/bin/mysqld_safe
mysqladmin = /usr/local/mysql/bin/mysqladmin
log = /opt/mysql/mysqld_multi.log

[mysqld]
#misc
user = mysql
basedir = /usr/local/mysql
datadir = /data/mysql/mysql_3306/data
port = 3306
socket = /tmp/mysql.sock
event_scheduler = 0

#timeout
interactive_timeout = 300
wait_timeout = 300

#character set
character-set-server = utf8

open_files_limit = 65535
max_connections = 100
max_connect_errors = 100000

skip-name-resolve = 1
#logs
log-output=file
slow_query_log = 1
slow_query_log_file = slow.log
log-error = error.log
log_warnings = 2
pid-file = mysql.pid
long_query_time = 1
#log-slow-admin-statements = 1
#log-queries-not-using-indexes = 1
log-slow-slave-statements = 1


#binlog
binlog_format = mixed
server-id = 203306
log-bin = /data/mysql/mysql_3306/logs/mybinlog
binlog_cache_size = 4M
max_binlog_size = 1G
max_binlog_cache_size = 2G
sync_binlog = 0
expire_logs_days = 10

#relay log
skip_slave_start = 1
max_relay_log_size = 1G
relay_log_purge = 1
relay_log_recovery = 1
log_slave_updates
#slave-skip-errors=1032,1053,1062

tmpdir = /data/mysql/mysql_3306/tmp

explicit_defaults_for_timestamp=1
#buffers & cache
table_open_cache = 2048
table_definition_cache = 2048
table_open_cache = 2048
max_heap_table_size = 96M
sort_buffer_size = 2M
join_buffer_size = 2M
thread_cache_size = 256
query_cache_size = 0
query_cache_type = 0
query_cache_limit = 256K
query_cache_min_res_unit = 512
thread_stack = 192K
tmp_table_size = 96M
key_buffer_size = 8M
read_buffer_size = 2M
read_rnd_buffer_size = 16M
bulk_insert_buffer_size = 32M

#myisam
myisam_sort_buffer_size = 128M
myisam_max_sort_file_size = 10G
myisam_repair_threads = 1

#innodb
innodb_buffer_pool_size = 100M
innodb_buffer_pool_instances = 1
innodb_data_file_path = ibdata1:1G:autoextend
innodb_flush_log_at_trx_commit = 2
innodb_log_buffer_size = 64M
innodb_log_file_size = 500M
innodb_log_files_in_group = 3
innodb_max_dirty_pages_pct = 50
innodb_file_per_table = 1
innodb_rollback_on_timeout
innodb_status_file = 1
innodb_io_capacity = 2000
transaction_isolation = READ-COMMITTED
innodb_flush_method = O_DIRECT

#端口号为3307的实例特殊配置
[mysqld3307]
port =3307 
server-id=203307
#指定本实例相应版本的basedir和datadir
basedir= /usr/local/mysql
datadir = /data/mysql/mysql_3307/data
log-bin = /data/mysql/mysql_3307/logs/mybinlog
socket  = /tmp/mysql_3307.sock
#重新配置这几个选项，不与全局配置一样，会直接覆盖上面的全局设置
innodb_buffer_pool_size = 100m
innodb_data_file_path = ibdata1:100M:autoextend
#transaction_isolation = REPEATABLE-READ

#端口号为3306的实例特殊配置
[mysqld3306]
port =3306
server-id=203306
#指定本实例相应版本的basedir和datadir
basedir= /usr/local/mysql
datadir = /data/mysql/mysql_3306/data
log-bin = /data/mysql/mysql_3306/logs/mybinlog
socket  = /tmp/mysql_3306.sock
#重新配置这几个选项，不与全局配置一样，会直接覆盖上面的全局设置
innodb_buffer_pool_size = 100m
#transaction_isolation = REPEATABLE-READ

之前3307启动不起来是因为 手动配置3307的时候修改过3307配置文件里innodb的一些大小，然后用/etc/my.cnf的时候没有按一样大小 就出错了

创建多实例目录并修改权限

vim /data/mysql/mysql_3307/my.cnf

修改配置文件

:%s/3306/3307/g

socket          = /tmp/mysql_3307.sock

要是想启用多版本mysql就修改basedir使用

basedir = /usr/local/mysql

修改socket

执行编译

./scripts/mysql_install_db –user=mysql –datadir=/data/mysql/mysql_3307/data/ –default-file=/data/mysql/mysql_3307/my.cnf

/usr/local/mysql/bin/mysqld_safe –defaults-file=/data/mysql/mysql_3306/my3306.cnf &

3306启动成功了

启动3307

./mysqld_safe –defaults-file=/data/mysql/mysql_3307/my3307.cnf &

启动成功

我发现这个netstat的这个参数这样记比较好记“按你老婆” 啊哈哈哈哈哈

可以看到3306，和3307都监听了

这个时候想要登录到3306

可以使用： mysql -S /tmp/mysql_3306.sock

关闭的话直接用

mysqladmin -S /tmp/mysql_3306.sock shutdown

就可以了

看错误日志

xtrabackup

./scripts/mysql_install_db

mysql -S /tmp/mysql.sock #进入mysql

root@localhost:mysql.sock  08:06:41 [(none)]>show databases
    -> ;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| test               |
+--------------------+
4 rows in set (0.03 sec)

root@localhost:mysql.sock  08:07:41 [(none)]>select user,host,password from mysql.user;
+------+-----------------------+----------+
| user | host                  | password |
+------+-----------------------+----------+
| root | localhost             |          |
| root | localhost.localdomain |          |
| root | 127.0.0.1             |          |
| root | ::1                   |          |
|      | localhost             |          |
|      | localhost.localdomain |          |
+------+-----------------------+----------+
6 rows in set (0.00 sec)

root@localhost:mysql.sock  08:08:10 [(none)]>delete from mysql.user where user!='root' or host!='localhost'
    -> ;
Query OK, 5 rows affected (0.02 sec)

root@localhost:mysql.sock  08:09:32 [(none)]>select user,host,password from mysql.user;
+------+-----------+----------+
| user | host      | password |
+------+-----------+----------+
| root | localhost |          |
+------+-----------+----------+
1 row in set (0.00 sec)

root@localhost:mysql.sock  08:09:55 [(none)]>flush privileges;
Query OK, 0 rows affected (0.00 sec)

root@localhost:mysql.sock  08:10:38 [(none)]>select * from db
    -> ;
ERROR 1046 (3D000): No database selected
root@localhost:mysql.sock  08:11:03 [(none)]>use mysql;
Database changed
root@localhost:mysql.sock  08:11:11 [mysql]>select * from db;
+------+---------+------+-------------+-------------+-------------+-------------+-------------+-----------+------------+-----------------+------------+------------+-----------------------+------------------+------------------+----------------+---------------------+--------------------+--------------+------------+--------------+
| Host | Db      | User | Select_priv | Insert_priv | Update_priv | Delete_priv | Create_priv | Drop_priv | Grant_priv | References_priv | Index_priv | Alter_priv | Create_tmp_table_priv | Lock_tables_priv | Create_view_priv | Show_view_priv | Create_routine_priv | Alter_routine_priv | Execute_priv | Event_priv | Trigger_priv |
+------+---------+------+-------------+-------------+-------------+-------------+-------------+-----------+------------+-----------------+------------+------------+-----------------------+------------------+------------------+----------------+---------------------+--------------------+--------------+------------+--------------+
| %    | test    |      | Y           | Y           | Y           | Y           | Y           | Y         | N          | Y               | Y          | Y          | Y                     | Y                | Y                | Y              | Y                   | N                  | N            | Y          | Y            |
| %    | test\_% |      | Y           | Y           | Y           | Y           | Y           | Y         | N          | Y               | Y          | Y          | Y                     | Y                | Y                | Y              | Y                   | N                  | N            | Y          | Y            |
+------+---------+------+-------------+-------------+-------------+-------------+-------------+-----------+------------+-----------------+------------+------------+-----------------------+------------------+------------------+----------------+---------------------+--------------------+--------------+------------+--------------+
2 rows in set (0.00 sec)

root@localhost:mysql.sock  08:11:19 [mysql]>truncate table db;
Query OK, 0 rows affected (0.02 sec)

root@localhost:mysql.sock  08:11:42 [mysql]>select * from db;
Empty set (0.00 sec)

root@localhost:mysql.sock  08:11:50 [mysql]>flush privileges;
Query OK, 0 rows affected (0.00 sec)

root@localhost:mysql.sock  08:11:56 [mysql]>grant all privileges on *.* to 'root'@'%' identified by 'you-password';
ERROR 2006 (HY000): MySQL server has gone away
No connection. Trying to reconnect...
Connection id:    2
Current database: mysql

Query OK, 0 rows affected (0.00 sec)

root@localhost:mysql.sock  08:18:13 [mysql]>flush privileges;
Query OK, 0 rows affected (0.00 sec)

/etc/init.d/mysql stop

 mysqladmin -S /tmp/mysql.sock shutdown -uroot -p

mysqladmin -S /tmp/mysql_3306.sock -uroot -p shutdown

mysqld_safe方式启动

/usr/local/mysql/bin/mysqld_safe –defaults-file=/etc/my.cnf &

mysqld方式启动

/usr/local/mysql/bin/mysqld –defaults-file=/etc/my.cnf &

这种方式一定要申明 data和user

5.6初始化ibdata

查询mysql进程

netstat -nalp | grep mysql

ps aux | grep mysql