High Performance MySQL

count total row number

  • 使用count(*)会计算总行数，而不在乎该行是否有值，通常效率更高
  • 使用count(<FieldName>)会计算该字段有值的条目数量，通常会慢一些。若确实需要知道该字段是否有值，使用该方法。
  • 在myisam下，使用count(*)能够快速得到总条目数，因为myisam已经存储了该值 
     On MyISAM, doing a query that does SELECT COUNT(*) FROM {some_table}, is very fast, since MyISAM keeps the information in the index
  • 在innodb下，使用count(*)会触发全表扫描，相对较慢，在大表时需要谨慎使用
  • 在innodb，大表下，如何高效获取总行数？ mysql的information_schema数据库中INFODB_SYS_TABLESTATS表,它记录了innodb类型每个表大致的数据行数 
    use information_schema;
select NUM_ROWS from INNODB_SYS_TABLESTATS where name=‘{DB_NAME}/{TABLE_NAME}’;

    MySQL大表优化方案

show (full) processlist

show processlist;
show full processlist;
kill <PID>;

explain SQL statement

explain <SOME SQL>;
# avoid `using filesort, using temporary`

Basic

mysql create user and grant permission

CREATE USER 'newuser'@'localhost' IDENTIFIED BY 'password';
GRANT type_of_permission ON database_name.table_name TO ‘username’@'localhost’;
GRANT ALL PRIVILEGES ON * . * TO 'newuser'@'localhost';

backup & export

mysqldump --user=admin_backup --password --lock-tables --databases db1 > /data/backup/db1.sql

mysqldump -h YOUR_HOST -u YOUR_USER --password=YOUR_PWD DB_NAME TABLE_NAME --where "start_time >= 2018-10-22" > data_20181001.sql

restore

mysql -u username -p database_name < file.sql

mysql alter field

ALTER TABLE tablename MODIFY COLUMN new-column-definition
e.g.
ALTER TABLE test MODIFY COLUMN locationExpect VARCHAR(120);

mysql insert

INSERT INTO table_name (column1, column2, column3, ...)
VALUES (value1, value2, value3, ...);
INSERT INTO table_name
VALUES (value1, value2, value3, ...);

check db disk size

SELECT table_schema AS "Database", 
ROUND(SUM(data_length + index_length) / 1024 / 1024, 2) AS "Size (MB)" 
FROM information_schema.TABLES 
GROUP BY table_schema;

set password

mysql> UPDATE mysql.user SET Password=PASSWORD('your_new_password')
       WHERE User='root'; 
ERROR 1820 (HY000): You must reset your password using ALTER USER statement before executing this statement.

mysql> SET PASSWORD = PASSWORD('your_new_password');
Query OK, 0 rows affected, 1 warning (0.01 sec)