每日归档： 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 就可以了

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 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136

#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

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 62 63 64 65 66 67 68 69 70 71 72 73

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