mysql 碎片化学习(持续更新中)

show variables like '':显示数据库配置相关信息;
set session transaction isolation level read committed :设置隔离级别,相关概念,四种隔离级别,read uncommitted(未提交读)、read committed(提交读)、repeatable read(可重复读)、serializable(可串行化)如下图:

MySQL能认识所有的4个ANSI隔离级别,InnoDB引擎也支持所有的隔离级别。
ACSI:原子性,一致性,隔离性,持久性


show table status like 'user%':查看表状态

相关参数:Name:表名、engine:存储引擎类型、row_format:行的格式、rows:表中的行数、avg_row_length:平均每行包含的字节数、data_length:表数据的大小、max_data_length:表数据的最大容量、index_length:索引的大小、data_free:表示已分配但未使用的空间、create_time:创建时间、update_time:表数据的最后修改时间、check_time:使用check time命令或者myisamchk工具最后一次检查表时间、collation:表的默认字符集、create_options:创建表时指定的其他选项、comment:一些其他的信息

alert table mytable engine=InnoDB:将表引擎修改为InnoDB
mysql 导入导出数据:



maatkit:数据库性能分析工具
new relic:性能监控工具

  • 慢查询日志
  • pt-query-digest:查询日志分析工具
  • flexviews:MySQL物化视图工具
  • show global status
  • show processlist:查看线程状态
    show profile、show status:结合可以查看查询时间、查询计数

关于数据库设计:

  1. 更小的通常更好:一般情况下,应尽量使用可以正确存储数据的最小数据类型。
  2. 简单就好:简单数据类型的操作通常需要更少的CPU周期,例如(应该使用MySQL内建的类型而不是字符串来存储日期和时间,应该使用整形存储IP地址)
  3. 尽量避免null,而是not null,因为包含null,对MySQL来说更难优化(对索引这块优化)
  4. 整形类型有可选的unsigned属性,表示不允许负值,这大致可以使正数的上限提高一倍(tinyint unsigned 0~255、tinyint -128~127)
  5. char和varchar,char是定长的,适合存储很短的字符串,或者所有值都接近于同一个长度,(char适合存储密码的md5值,因为这是一个定长的值),对于经常变更的数据,char也比varchar更好,因为定长的char类型不容易产生碎片。对于非常短的列char比varchar也更有效率,char(1)1个字节,varchar(1)两个字节,另外一个记录长度。存储char时会默认删除后边所有的空格。
  6. 二进制比较比字符比较快的多。与varchar、char类似的类型还有binary、varbinary


  7. 使用枚举代替字符串常量,枚举类型保存的为整数,而不是字符串,所以避免使用数字作为枚举常量。所以存储的顺序跟存入顺序可能不一致(不能保证varchar/char与枚举列关联比直接关联char/varchar快)
  8. 太多的列:存储引擎API工作时需要在服务器和存储引擎层之间通过缓冲格式拷贝数据,然后在服务层将缓冲数据转换成列,这个过程代价非常过
  9. 太多的关联:如果希望查询执行的快速且并发性好,单个查询最好在12个表以内做关联
  10. 别乱使用枚举


关于索引:

我们谈论的索引,如无特别说明,则指的都是B-Tree索引,B-Tree所有的的值都是按顺序存储的,并且每一个叶子页到根的距离相同。B-Tree能加快访问数据的速。:

有效部分:

  • 全值匹配:全值匹配指的是和索引中的所有列进行匹配;
  • 匹配最左前缀:即只使用索引的第一列。
  • 匹配列前缀:也可以只匹配某一列的值的开头部分。这里也只使用了索引的第一列。
  • 匹配范围值:只使用了第一列
  • 精确匹配某一列并范围匹配另外一列:即第一列精确匹配,第二列前缀匹配
  • 只访问索引的查询:即查询只需要访问访问索引,无需访问数据行。

限制部分:

  • 如果不是按照索引的最左部列开始查找,则无法使用索引
  • 不能跳过索引中的列:跳过索引中的列则只能使用索引的第一列
  • 如果查询中有某个列的范围查询,则其右边所有的列都无法使用索引优化查找
    PS:这部分限制在未来的版本中可能就不再是限制了。
  1. 索引大大减少了服务器需要扫描的数据量
  2. 索引可以帮助服务器避免排序和临时表
  3. 索引可以将所及I/O变成有序I/O。
高性能的索引策略:
  • 独立的列:如果查询中的列不是独立的,则MySQL就不会使用索引,指索引列不能是表达式的一部分,也不能是函数的参数。我们应该养成简化where条件的习惯,始终将索引列单独放在比较符号的一侧。
  • 前缀索引:前缀索引是一种能使索引更小、更快的有效办法,但是缺点是:MySQL无法使用前缀索引做order by和group by,也无法使用前缀索引做覆盖扫描。

    后缀索引:
  • 当不考虑排序和分组时,将选择性更高的列放在前面通常是很好的,当然也和值得分布有关(考虑到选择前缀的长度)

推荐阅读更多精彩内容