Tag Archives: database

MySQL的基本优化

Leave a reply

1、查询执行速度慢的sql语句

  • 查看当前“慢语句”标准：show variables like ‘%long%’ 
    mysql> show variables like '%long_query_time%';
+-----------------+----------+
| Variable_name   | Value    |
+-----------------+----------+
| long_query_time | 10.00000 |
+-----------------+----------+
1 row in set (0.00 sec)

mysql> show variables like '%slow%';
+---------------------------+--------------------------------+
| Variable_name             | Value                          |
+---------------------------+--------------------------------+
| log_slow_admin_statements | OFF                            |
| log_slow_slave_statements | OFF                            |
| slow_launch_time          | 2                              |
| slow_query_log            | ON                             |
| slow_query_log_file       | /var/lib/mysql/ubuntu-slow.log |
+---------------------------+--------------------------------+
5 rows in set (0.00 sec)
    SQL
  • 开启日志：set global slow_query_log = on;
  • 修改时间标准：set long_query_time = 0.5;
  • slow_query_log_file为日志文件所在路径；
  • 查看日志
    [……]

    继续阅读

索引

Leave a reply

0、索引类型

  • 普通索引：index、key
  • 唯一索引：uniqe
  • 主键索引：primary key
  • 全文索引：fulltext（MyISAM引擎或InnoDB引擎v5.7及以上版本支持）

1、查看当前表创建

  • show create table user;
mysql> show[......]继续阅读
SQL

DTL 事务控制语言

Leave a reply

1、一个简单的事务

  • 开启事务：start transaction
  • 回滚事务：rollback
  • 提交事务：commit
mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)


mysql> update user set fee = 1200 where id = 1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0


mysql> select * from user;
+----+-----------+-----+----------------------------------+-----------------+---------+---------+-----+---------------------+
| id | user_name | sex | password                         | email           | address | fee     | age | create_at           |
+----+-----------+-----+----------------------------------+-----------------+---------+---------+-----+---------------------+
|  1 | sian      | 男  | e10adc3949ba59abbe56e057f20f883e | yusian@163.com  | NULL    | 1200.00 |  32 | 2019-04-23 14:45:36 |
|  2 | 余西安    | 男  | c4ca4238a0b923820dcc509a6f75849b | yusian@163.com  | NULL    |    1.00 |  33 | 2019-04-21 17:12:55 |
|  3 | Jack      | 男  | c81e728d9d4c2f636f067f89cc14862c | jack@yusian.com | NULL    |    0.00 |  22 | 2019-04-21 20:11:59 |
|  4 | Rose      | 女  | eccbc87e4b5ce2fe28308fd9f2a7baf3 | rose@yusian.com | NULL    |    0.00 |  18 | 2019-04-22 10:46:41 |
|  5 | Sim       | 女  | a87ff679a2f3e71d9181a67b7542122c | sim@yusian.com  | NULL    |    0.00 |  28 | 2019-04-22 10:46:41 |
|  6 | Tony      | 男  | c4ca4238a0b923820dcc509a6f75849b | tony@yusian.com | NULL    |    0.00 |  22 | 2019-04-21 20:41:26 |
+----+-----------+-----+----------------------------------+-----------------+---------+---------+-----+---------------------+
6 rows in set (0.00 sec)


mysql> rollback;
Query OK, 0 rows affected (0.00 sec)


mysql> select * from user;
+----+-----------+-----+----------------------------------+-----------------+---------+---------+-----+---------------------+
| id | user_name | sex | password                         | email           | address | fee     | age | create_at           |
+----+-----------+-----+----------------------------------+-----------------+---------+---------+-----+---------------------+
|  1 | sian      | 男  | e10adc3949ba59abbe56e057f20f883e | yusian@163.com  | NULL    | 1100.00 |  32 | 2019-04-23 14:38:53 |
|  2 | 余西安    | 男  | c4ca4238a0b923820dcc509a6f75849b | yusian@163.com  | NULL    |    1.00 |  33 | 2019-04-21 17:12:55 |
|  3 | Jack      | 男  | c81e728d9d4c2f636f067f89cc14862c | jack@yusian.com | NULL    |    0.00 |  22 | 2019-04-21 20:11:59 |
|  4 | Rose      | 女  | eccbc87e4b5ce2fe28308fd9f2a7baf3 | rose@yusian.com | NULL    |    0.00 |  18 | 2019-04-22 10:46:41 |
|  5 | Sim       | 女  | a87ff679a2f3e71d9181a67b7542122c | sim@yusian.com  | NULL    |    0.00 |  28 | 2019-04-22 10:46:41 |
|  6 | Tony      | 男  | c4ca4238a0b923820dcc509a6f75849b | tony@yusian.com | NULL    |    0.00 |  22 | 2019-04-21 20:41:26 |
+----+-----------+-----+----------------------------------+-----------------+---------+---------+-----+---------------------+
6 rows in set (0.00 sec)
SQL

[……]

继续阅读