select ……into outfile ……
load data infile
mysqlimport -u -root -p [--local] 数据库名 表名 指定格式的文本文件名 [参数选项]
mysqldump
MyISAM引擎
frm文件 MYD文件 MYI文件
InnoDB引擎
frm文件 表空间文件(独立表空间文件或共享表空间文件),ib_logfile0和ib_logfile1
mysqlhotcopy -u -p [参数选项] 数据库.[表名正则表达式] 新数据库名或备份目录
影响数据库性能组件
CPU，内存，硬盘，I/O,网络环境，SQL语句，索引，链接connection , 表结构设计，锁竞争等
Show status
Show variables
Show global status;
Show session status;
Flush status 初始化当前会话状态变量
缓存局部性特征分为空间局部性和时间局部性
时间局部性：是指刚刚访问过的数据近期可能被再次访问。
空间局部性：是指某个位置的数据被访问后，其相邻位置的数据可能很快被访问。
MySQl缓存分为：Cache缓存和Buffer缓存
Cache缓存用于加速读，把硬盘读取过来的数据保存到Cache缓存中。
Buffer缓存用于缓冲写，把分散的写操作集中进行，较少硬盘I/O次数，硬盘碎片，以及硬盘的反复寻道，从而提高I/O的整体性能。
MySQL缓存又分为全局缓存，会话缓存，临时缓存。
按存储引擎又分为MySQL缓存，MyISAM缓存，InnoDB缓存。
show variables like '%timeout%';
show variables like '%connection%';
show status like '%connections%';
show variables like 'thread%';
show status like 'thread%';
show status like 'Slow%';
show status like 'Connection_error%';
show variables like '%query_cache%';
开启缓存，设置查询缓存大小为98M;
Set global query_cache_size = 102760448;
Show status like 'Qchache%';
Flush query chache;命令会对缓存若干碎片进行整理
show status like 'Com_select';
show variables like 'net_buffer_length';
show variable like '%binlog%cache%';
show variable like 'preload_buffer_size';预加载索引缓存大小
show variables like '%heap%';
show status like '%tmp%';
show variables like 'sql_buffer_result';
show variables like 'innodb%pool%';
show variables like 'innodb_old';
优化表
1:尽量将字段定义成NOT NULL约束。
2：考虑使用enum,set等复合数据类型，因为它内部使用数值表示。
3：尽量不使用blob类型。
4：数值型字段比字符串的比较效率高。
如IP，可用inet_aton()，inet_ntoa()函数转换。
垂直拆分：
有点在于使得列数据变小，一个block块就可以存放更多行记录。对应select语句，是I/O次数变少。
缺点：关系需要冗余字段进行维护，访问到不经常使用的字段会引起表join。
水平拆分：
水平拆分按照记录行进行拆分，其实就是把一个表分成几个表，这些表具有相同的列，但是存放更少的数据。拆分原则通常是按照日期时间维度，地区维度或者特殊业务维度进行表拆分。
优点：select语句查询维度范围小，可以有效降低需要扫描的数据和索引的数据块，加快查询速度。
缺点：会增加应用复杂度，它通常在查询时需要多个表。查询所有数据要union操作，有时这种复杂度会超过它带来的优点。
MySQL支持4中分区模式：
Rang(范围)分区：将数据划分不同的范围。
List(预定义列表)分区：定义离散的列表进行分区。
Hash(哈希)分区：通过对表的一个或多个列的hash值进行分区。
Key(键值)分区：hash分区的一种延伸，这里的key有MYSQL自动生成。
analyze table 表名; 表分析
show index; 查询表索引的离散度
optimize table 表名; 可以整理MyISAM及InnoDB表的碎片，从而提升性能。
check table; 检查表
check table view1; 检查视图view1;
repair tabel;
checksum table;
show variables like '%stats_method%';
show table status;
show table status like '表名';
Show status like 'questions';
Show global status like 'Corn_%';
Show status like 'Innodb_rows%';
Show status like 'uptime';
Show status like 'slow_queries';
Explain select 语句;
索引与select语句
1：索引关键字使用函数，导致索引无法使用
2：对索引字段进行模糊查询，模式第一个字母是通配符，导致索引无法使用
3：违反最左前缀
4：使用不等式not , !=, <=>作为调节查询
5：索引值得数据类型与索引字段不用，MySQL能进行数据转换，但不会使用索引。
在进行分页查询时，如果使用聚簇索引(或主键索引)进行范围扫描，性能更好。
SQL语句注意事项：
1：不要获取不需要的字段，如select * 查询
2：同一功能的SQL语句，书写格式尽量保持统一。因为查询缓存一个区分大小写的hash查询，缓存中两个相似的查询，哪怕只有一个字母，也不会匹配。
3：使用存储过程。
存储过程在解析的时候，自动检测语法错误，权限，，，效率更高。
4：使用预处理prepared statement
预处理只解析一次，效率更高
5：根据齿轮原则，多个表进行join连接时，使用小结果集启动大结果集。
6：当查询只有一条语句时使用limit 1,可以提前终止查询语句
7：使用join来替代子查询
连接join效率更高，是因为join不需要在内存中使用临时表，而子查询需要在内存中创建临时表来完成，逻辑上需要两个步骤的查询工作。
profiling性能分析工具
1:set命令开启profiling
set profiling=1;
2: 查询语句，如： select * from student where stuent_namne like '张%';
3:show profiles 查看所有的SQL语句执行时间
4：show profile for query 1; 查询query_ID值为1的SQL语句执行时间。
5：测试完毕后，关闭profiling.
set profiling=0;
show status like 'table_locks%';
show status like 'innodb_row_lock%';
有时太多的间隙锁会影响事务之间的并发性能。
有两种方法关闭InnoDB的间隙锁。
1：把事务的隔离级别设置为READ_COMMITTED.
2:将全局变量innodb_locks_unsafe_for_binlog设置成ON或1，禁用间隙锁。
show processlist; 查看当前MYSQL服务实例的所有线程。
网友评论