mysql优化和类型

MySQL还支持选择在该类型关键字后面的括号内指定整数值的显示宽度(例如,INT(4))。int(M) 在 integer 数据类型中,M 表示最大显示宽度,该可选显示宽度规定用于显示宽度小于指定的列宽度的值时从左侧填满宽度。

 

显示宽度并不限制可以在列内保存的值的范围,也不限制超过列的指定宽度的值的显示。

 

在 int(M) 中,M 的值跟 int(M) 所占多少存储空间并无任何关系。和数字位数也无关系, int(3)、int(4)、

 

int(8) 在磁盘上都是占用 4 btyes 的存储空间。

 

当结合可选扩展属性ZEROFILL使用时, 默认补充的空格用零代替。例如,对于声明为INT(5) ZEROFILL的列,

 

值4检索为00004。

 

 bigint 用于某些特殊的情况,当整数值超过 int 数据类型支持的范围时,就可以采用 bigint。
 

使用同类型进行比较

尽量避免在WHERE子句中使用!=或<>操作符,否则将引擎放弃使用索引而进行全表扫描

少用JOIN

不用SELECT *

可通过开启慢查询日志来找出较慢的SQL

不用外键

值分布很稀少的字段不适合建索引

索引并不是越多越好,要根据查询有针对性的创建,考虑在WHERE和ORDER BY命令上涉及的列建立索引,可根据EXPLAIN来查看是否用了索引还是全表扫描

选择合适引擎:

MyISAM

MyISAM引擎是MySQL 5.1及之前版本的默认引擎,它的特点是:

不支持行锁,读取时对需要读到的所有表加锁,写入时则对表加排它锁

不支持事务

不支持外键

不支持崩溃后的安全恢复

在表有读取查询的同时,支持往表中插入新纪录

支持BLOB和TEXT的前500个字符索引,支持全文索引

支持延迟更新索引,极大提升写入性能

对于不会进行修改的表,支持压缩表,极大减少磁盘空间占用

InnoDB

InnoDB在MySQL 5.5后成为默认索引,它的特点是:

支持行锁,采用MVCC来支持高并发

支持事务

支持外键

支持崩溃后的安全恢复

不支持全文索引

总体来讲,MyISAM适合SELECT密集型的表,而InnoDB适合INSERT和UPDATE密集型的表

 

系统调优参数

可以使用下面几个工具来做基准测试:

sysbench:一个模块化,跨平台以及多线程的性能测试工具

iibench-mysql:基于 Java 的 MySQL/Percona/MariaDB 索引进行插入性能测试工具

tpcc-mysql:Percona开发的TPC-C测试工具

具体的调优参数内容较多,具体可参考官方文档,这里介绍一些比较重要的参数:

back_log:back_log值指出在MySQL暂时停止回答新请求之前的短时间内多少个请求可以被存在堆栈中。也就是说,如果MySql的连接数据达到max_connections时,新来的请求将会被存在堆栈中,以等待某一连接释放资源,该堆栈的数量即back_log,如果等待连接的数量超过back_log,将不被授予连接资源。可以从默认的50升至500

wait_timeout:数据库连接闲置时间,闲置连接会占用内存资源。可以从默认的8小时减到半小时

max_user_connection: 最大连接数,默认为0无上限,最好设一个合理上限

thread_concurrency:并发线程数,设为CPU核数的两倍

skip_name_resolve:禁止对外部连接进行DNS解析,消除DNS解析时间,但需要所有远程主机用IP访问

key_buffer_size:索引块的缓存大小,增加会提升索引处理速度,对MyISAM表性能影响最大。对于内存4G左右,可设为256M或384M,通过查询show status like 'key_read%',保证key_reads/key_read_requests在0.1%以下最好

innodb_buffer_pool_size:缓存数据块和索引块,对InnoDB表性能影响最大。通过查询show status like 'Innodb_buffer_pool_read%',保证 (Innodb_buffer_pool_read_requests – Innodb_buffer_pool_reads)/Innodb_buffer_pool_read_requests越高越好

innodb_additional_mem_pool_size:InnoDB存储引擎用来存放数据字典信息以及一些内部数据结构的内存空间大小,当数据库对象非常多的时候,适当调整该参数的大小以确保所有数据都能存放在内存中提高访问效率,当过小的时候,MySQL会记录Warning信息到数据库的错误日志中,这时就需要该调整这个参数大小

innodb_log_buffer_size:InnoDB存储引擎的事务日志所使用的缓冲区,一般来说不建议超过32MB

query_cache_size:缓存MySQL中的ResultSet,也就是一条SQL语句执行的结果集,所以仅仅只能针对select语句。当某个表的数据有任何任何变化,都会导致所有引用了该表的select语句在Query Cache中的缓存数据失效。所以,当我们的数据变化非常频繁的情况下,使用Query Cache可能会得不偿失。根据命中率(Qcache_hits/(Qcache_hits+Qcache_inserts)*100))进行调整,一般不建议太大,256MB可能已经差不多了,大型的配置型静态数据可适当调大.null

mysql优化和类型