许博的博客

获取单独的数据库大小

SELECT
  TABLE_NAME AS `Table`,
  ROUND((DATA_LENGTH + INDEX_LENGTH) / 1024 / 1024) AS `Size (MB)`
FROM
  information_schema.TABLES
WHERE
  TABLE_SCHEMA = "db_name"
ORDER BY
  (DATA_LENGTH + INDEX_LENGTH)
DESC;

获取所有数据库的各个表大小

SELECT
  TABLE_SCHEMA AS `Database`,
  TABLE_NAME AS `Table`,
  ROUND((DATA_LENGTH + INDEX_LENGTH) / 1024 / 1024) AS `Size (MB)`
FROM
  information_schema.TABLES
ORDER BY
  (DATA_LENGTH + INDEX_LENGTH)
DESC;

参考 https://chartio.com/resources/tutorials/how-to-get-the-size-of-a-table-in-mysql/

innodb_buffer_pool_size参数

InnoDB用来设置缓存它的数据和索引的内存缓冲区大小，减少I/O。可以设置为服务器内存的70%-80%大小, 注意单位为字节

innodb_log_file_size参数

日志文件的大小，建议设置为innodb_log_file_size = 512M，通常是buffer pool的1/4。

max_connections参数

mysql允许的最大连接数。如果你遇到了Too many connections错误，你就需要考虑调整这个参数了，但是过大的连接数会占用过多的内存导致 系统不可用，你可以考虑在应用层使用连接池或者在MySQL层使用线程池。

slow_query_log参数

设置是否开启慢查询日志，你应当在生产环境开启这个配置slow_query_log=1

slow_query_log_file参数

设置记录慢查询日志的文件路径

long_query_time参数

long_query_time设置慢查询的时间

参考

https://www.percona.com/blog/2014/01/28/10-mysql-performance-tuning-settings-after-installation/

http://www.codingpedia.org/ama/optimizing-mysql-server-settings/

今天无意中查下数据，发现了个奇怪的现象，在一条几十万数据的表中分明已经建了索引，还是使用了全表扫描。

在members表中有一个记录会员手机号的phone字段，由于经常根据手机号来进行查找而且数据量也开始上来了，就在phone表中加了个普通索引, phone的类型是varchar(20), 可是今天查找的时候竟然使用了全表扫描，可是看了表中的索引，确实已经在phone字段上建了索引，很奇怪为什么会出现这种情况。

最后发现这是由于查找写的值类型和字段的类型不一样导致MySQL无法使用索引优化.

看到区别了吗，由于我在查询的时候phone=134321直接是integer类型的，而phone是varchar类型的，就导致最后查询的时候没有用到索引，但是却出现在了possible_keys中，而当使用phone='134321'时，执行查询就可以用到索引，rows便是最明显的区别。

查看当前连接状态信息

STATUS;

查看可用的存储引擎

SHOW ENGINES;

查看是否自动提交事务

SELECT @@autocommit;

查看当前连接和事务的隔离级别

select @@tx_isolation;
select @@global.tx_isolation;

查看当前数据库

SELECT database();

从当前表结构创建出新表

CREATE TABLE new_tbl LIKE orig_tbl;

查询一个表中含有重复的记录数

SELECT id, count(*) FROM users  group by name having count(*) > 1;

删除重复的记录并保留一个最小的ID

DELETE a FROM user a, user b WHERE a.id > b.id AND a.mail = b.mail;

删除重复的记录并保留一个最大的ID

DELETE a FROM user a, user b WHERE a.id < b.id AND a.mail = b.mail;

查看表信息

SHOW TABLE STATUS LIKE TABLE_NAME

查看索引信息

SHOW INDEX FROM TABLE_NAME

查看执行计划

EXPLAIN SELECT * FROM TABLE_NAME

添加索引

1. CREATE INDEX email_idx ON `TABLE_NAME`;
2. ALTER TABLE TABLE_NAME ADD INDEX email_idx (email);

删除索引

1. DROP INDEX email_idx ON `TABLE_NAME`;
2. ALTER TABLE `TABLE_NAME` DROP INDEX INDEX_NAME;

删除有外键约束的记录

InnoDB不允许删除一个被FOREIGN KEY表约束引用的表，除非设置了FOREIGN_KEY_CHECKS=0;
SET FOREIGN_KEY_CHEECKS = 0;

替换

UPDATE tbl_name 
SET 
    field_name = REPLACE(field_name,
        string_to_find,
        string_to_replace)
WHERE
    conditions;