mysql小记

1.tinyint只能存储1字节(有符号-127~+128,无符号0~255),tinyint<2>中括号中2表示存放的数字不足两位的,在最前面填充0(如:1 -> 01)

2.当表中某一个字段存放数据较大时,应该用主键映射关系单独分离一张表出来(一张表数据太大会影响检索速度),这种垂直分离(读写分离)能起到提高速度作用

3.一张表数据条数不易太多,应进行水平分离.如TX的QQ号会根据城市进行分表.查询时只需根据实际业务查询对应城市的表,而不用检索大表去花费超大时间

4.当一个int型数据太大以致BIGINT也放不下,应该用varcha存放,分段取出数据通过特殊算法应用超大数值

5.MySQL的最大连接数(max_connections=100)默认100,实际环境注意更改(一般1000),差不多能支撑最大2000的并发极限(由于静态页面缓存,大概能支撑10W人在线)...像query_cache_size(查询缓存根据情况调整影响查询速度)

6.innodb调节参数
                       innodb_additional_mem_pool_size = 64M
　　                   innodb_buffer_pool_size =1G

7.myisam调节参数
                       key_buffer_size一般调节为原来的10倍有明显效果

8.MySQL增量备份会记录(SQL[dml,create], 操作时间, 操作位置)..在my.ini中[mysqld]下方添加log-bin=D:/mylog以开启增量备份(重启MySQL).MySQL会在d盘生成index文件(记录有哪些文件)和默认第一个文件000001文件(存放对数据库的操作)

9.可以通过mysqlbinlog D:/mylog.000001查看增量备份文件(里面记录了操作时间和位置),可以通过操作时间和位置有选择的恢复

10.通过 mysqlbinlog --start-datetime="2017-02-28 22:37:20" d:/mylog.000001 | mysql -uroot -p  进行恢复(会恢复时间点之后的所有操作)
--stop-position="123" (会恢复从最开始到位置123的所有数据)
--start-datetime="" --stop-datetime="" (会对start到stop区间进行恢复)
当数据发生灾难性损坏,一般先进行全备份的恢复,在根据增量备份时间点进行恢复

11.可用reset master; 清空增量备份文件,也可在[mysqld]下配置-EXPIRE_LOGS_DAYS=7 进行每7天清空一次

12.MySQL 5大索引:普通索引,主键索引(主键自带),唯一索引(唯一键自带),全文索引(fulltext,仅myisam支持),复合索引(两列以上作为一个索引)

13.表的设计尽量满足3F(原子性,唯一性,没有冗余),具体还是要根据实际业务情况而定

14.SHOW STATUS;可查看数据库状态列表. show [global] status like '状态列表中命令';可查看具体状态情况(如:show status like 'uptime'查看MySQL启动了多长时间).
默认为当前线程,添加global后为整个应用范围

15.show status like 'slow_queries';可查看低于10秒的慢查询(MySQL默认10秒以下为慢查询).showstatus like 'Long_query_time';可查看慢查询时间(默认10秒),可通过
set [global] long_query_time=1;进行修改...默认情况下MySQL不会记录慢查询,可通过cmd输入mysqld --safe-mode --slow-query-log;开启. 使用mysqld -log-slow-queries=d:/slow.log;指定慢查询保存日志路径(默认存放于datadir的路径)

16.可使用explain分析一条查询语句来优化慢查询

17.全文索引使用select * from table where match(field,field...) against(words);可使用select match(field,field...) against(words) from table;显示命中率.tips:全文索引只会对生僻词建立索引(想想就明白的道理),此外MySQL自带的全文索引不支持中文

18.唯一索引能够重复的存储null

19.索引注意事项:a,更新频繁的字段不宜建立索引.b,差异化小的字段不宜建立索引c,对dml的执行有负影响

20.会导致索引失效的情况:like 条件左边为通配符(like '%条件' 或 like '_条件')
组合索引中,没有使用最左边的条件(add index myIndex(column1,column2)-> select from table where column2='xxx')
条件中有or,那么or两边的条件都必须在各自单独的条件下索引有效才会使用索引(如上面列:where column1='aaa' or column2='bbb' 这条不会使用索引,因为在单独的条件下column2得索引会被忽略)
列类型为字符串,索引的条件需用引号包裹(varcha column -> where column=123 不会使用索引,正确使用索引应为 where column='123' )

21.group by语句会默认对结果进行排序.可跟上order by null来阻止

22.myisam和innodb:批量插入(高,低),事物支持(否,是),全文索引(是,否),锁机制(表,行),btree索引支持(是,是),hash索引(否,是),集群索引(否,是),数据缓存(否,是),数据可压缩(是,否),空间占用(低,高),内存占用(低,高),外键支持(否,是)

23.如果引擎是myisam,必须进行定期碎片整理(myisam即使在删除数据后也会留下大量的碎片).可使用optimize table xxx;命令对xxx表进行碎片清理

24.mysqldump -u root -proot 数据库[table1, table2] > 文件路径; 指令可进行数据库备份. source 路径; 可进行根据备份文件恢复.

25.可把备份命令写入bat文件中,然后添加定时任务进行自动备份.注意:bat中的mysqldump命令必须使用全路径,环境变量无效,若路径中有空格,应用双引号包裹(c:\mt work\mysql\bin\mysqldump -u root... -> "c:\mt work\mysql\bin\mysqldump" -u root...)

26.对于数据过多批量插入，应使用存储过程进行，存储过程不会返回执行结果（静默执行），效率比普通高。在插入之前应先关闭MySQL的自动提交事务，在插入的最后一条语句之后进行人为控制提交事务