mysql 学习

整理了下mysql在优化方面相关的命令：

show session| global status like 'Com_%'  默认是查看session级别的状态参数
表示某个语句执行的次数,包含所有的存储引擎的操作记录
Com_select
Com_update
Com_insert
Com_delete

下面这些参数仅显示Innodb的操作记录
Innodb_rows_read
Innodb_rows_inserted
Innodb_rows_updated
Innodb_rows_deleted

show status like 'Connections';
show status like 'Uptime';
show status like 'Slow_queries';

--log-slow-queries=[file_name]
时间超过long_query_time
show procssslist;

show variables like '%' 查看某个变量的值
mysqld --verbose --help | more 查看mysql各个变量的解析


myisam 表的数据文件和日志文件是分开存储的，独立成文件
Innodb  表的数据和索引存储在同一个表空间里，可以由多个文件组成

Handler_read_key （一行被索引读取的次数） 如果正在使用索引，这个值会高
Handler_read_rnd_next （在数据文件中读取下一行的请求次数） 如果这个值高，需要建立索引来优化性能

mysql> show status like 'Handler_read%';
+-----------------------+-------+
| Variable_name         | Value |
+-----------------------+-------+
| Handler_read_first    | 0     |
| Handler_read_key      | 0     |
| Handler_read_last     | 0     |
| Handler_read_next     | 0     |
| Handler_read_prev     | 0     |
| Handler_read_rnd      | 0     |
| Handler_read_rnd_next | 0     |
+-----------------------+-------+
7 rows in set (0.00 sec)

analyze table 分析和存储关键字分布，分析后将会得到准确的统计信息
mysql> analyze table boss_info;
+---------------+---------+----------+----------+
| Table         | Op      | Msg_type | Msg_text |
+---------------+---------+----------+----------+
| doh.boss_info | analyze | status   | OK       |
+---------------+---------+----------+----------+
1 row in set (0.05 sec)

check table 检测一个或多个表是否存在错误
mysql> check table boss_info;
+---------------+-------+----------+----------+
| Table         | Op    | Msg_type | Msg_text |
+---------------+-------+----------+----------+
| doh.boss_info | check | status   | OK       |
+---------------+-------+----------+----------+
1 row in set (0.01 sec)

optimize table 优化表，可以对表空间的磁盘碎片进行整理
mysql> optimize table boss_info;
+---------------+----------+----------+----------+
| Table         | Op       | Msg_type | Msg_text |
+---------------+----------+----------+----------+
| doh.boss_info | optimize | status   | OK       |
+---------------+----------+----------+----------+
1 row in set (0.08 sec)

上面的命令会进行表锁定，应在系统不繁忙的时候执行。

show index from boss_info;查看表中的索引信息

使用hint

使用指定的索引
mysql> explain select * from boss_info use index(PRIMARY) where id =3;
+----+-------------+-----------+-------+---------------+---------+---------+-------+------+-------+
| id | select_type | table     | type  | possible_keys | key     | key_len | ref   | rows | Extra |
+----+-------------+-----------+-------+---------------+---------+---------+-------+------+-------+
|  1 | SIMPLE      | boss_info | const | PRIMARY       | PRIMARY | 4       | const |    1 |       |
+----+-------------+-----------+-------+---------------+---------+---------+-------+------+-------+
1 row in set (0.02 sec)

不使用指定的索引
mysql> explain select * from boss_info ignore index(PRIMARY) where id =3;
+----+-------------+-----------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table     | type | possible_keys | key  | key_len | ref  | rows | Extra       |
+----+-------------+-----------+------+---------------+------+---------+------+------+-------------+
|  1 | SIMPLE      | boss_info | ALL  | NULL          | NULL | NULL    | NULL |   16 | Using where |
+----+-------------+-----------+------+---------------+------+---------+------+------+-------------+
1 row in set (0.00 sec)

强制使用指定的索引
mysql> explain select * from boss_info force index(PRIMARY) where id =3;
+----+-------------+-----------+-------+---------------+---------+---------+-------+------+-------+
| id | select_type | table     | type  | possible_keys | key     | key_len | ref   | rows | Extra |
+----+-------------+-----------+-------+---------------+---------+---------+-------+------+-------+
|  1 | SIMPLE      | boss_info | const | PRIMARY       | PRIMARY | 4       | const |    1 |       |
+----+-------------+-----------+-------+---------------+---------+---------+-------+------+-------+
1 row in set (0.00 sec)