【MySQL】数据库优化

数据库优化方案

1. 合理的表设计 （符合3NF）

2. 添加索引 （普通索引，主键索引，唯一索引，全文索引）

3. 分表技术 （水平分割，垂直分割）

4. 读写分离 （读写分离）

5. 存储过程 （模块化编程）

6. 对MySQL配置优化 （配置最大并发数，调整缓存大小）

7. 硬件升级

 

show status;

查询数据库状态

 

show status like 'uptime';  # 数据库运行时间

show stauts like 'com_select/com_update/com_delete/com_insert';  #查看数据库指定命令执行次数

show status like 'connections'; # 查看当前连接数

show status like 'slow_queries';   #查看慢查询条数

 

SQL慢查询

show variables like 'long_query_time’; #查看慢查询设置

 

默认慢查询时间为10秒

mysql> show variables like 'long_query_time';

+-----------------+-----------+

| Variable_name   | Value     |

+-----------------+-----------+

| long_query_time | 10.000000 |

+-----------------+-----------+

1 row in set (0.00 sec)

 

1. 启动MySQL数据库时加上启动参数 --slow-query-log

 

2. set long_query_time = 1; # 设置慢查询为1秒

 

3. 数据库配置文件中，查看慢查询文件 --slow-query-log-file=file_name

slow-query-log-file            = /usr/local/mysql/data/mysql-slow.log

 

4. 当SQL语句执行时间超过所设置的时间，SQL会被记录到慢查询日志当中

 ============================================

MySQL 索引相关操作

创建索引

1. 普通索引 create index indexName on table(column)

2. 唯一索引 create unique index indexName on table(column)

 

mysql> create unique index userIndex on users(username);

Query OK, 6425817 rows affected (3 min 10.38 sec)

Records: 6425817  Duplicates: 0  Warnings: 0

 

删除索引

alter table tableName drop index indexName;

 

查询索引

desc table

show index from table

show keys from table

 

修改索引

先删除，再创建

 

—————————————

 

使用Explain来查看SQL语句信息

mysql> explain select * from users where username = 'jinbuhuan' \G

*************************** 1. row ***************************

                   id: 1                         【查询序列号】

    select_type: SIMPLE              【查询类型】

              table: users                  【表名】

                type: ref                      【扫描方式 (ALL/Range/Ref) ref是最理想的】

possible_keys: userIndex           【可能用到的索引】

                 key: userIndex           【实际使用到得索引】

          key_len: 768                    

                  ref: const

               rows: 2                          【执行SQL语句需要查询的行数】

              Extra: Using index condition

1 row in set (0.00 sec)

 

当使用like关键字进行模糊查询，并且%在关键字之前的时候会用不到索引

mysql> explain select * from users where username like '%siclj' \G

*************************** 1. row ***************************

           id: 1

  select_type: SIMPLE

        table: users

         type: ALL

possible_keys: NULL

          key: NULL

      key_len: NULL

          ref: NULL

         rows: 6425817

        Extra: Using where

1 row in set (0.00 sec)