mysql开访问策略_mysql-sql分析策略及优化

tranlation

事务：

从失败中回复到正常状态的方法

多个应用并发访问数据库时，提供隔离方法

acid

原子性：要么成功、要么失败

一致性：数据保持“合理性”

隔离型：多个事务同时并发执行，每个事务就像各自独立执行一样

持久性：数据持久化到硬盘

使用事务的话表的引擎为innodb引擎

默认是开启自动提交事务

engine:

存储引擎是基于表的

myisam: 不支持事务、表级锁、全文索引、奔溃恢复不好

innodb: 支持事务、行级锁、全文索引(5.6+)、奔溃恢复好

update table set id = 3 where name like 'a%';

更新行数不确定、此时采用表级锁

总结：一般来说myisam是适合不需要事务的时候：做很多count计算

innodb是适合要去事务，可靠性要求高的

推荐使用innodb

index

sql优化方案：

一、开启慢查询日志

第一步：show variables like '%slow%';

第二步：set slow_query_log = on;

slow_query_log_file:慢日志文件，只存放慢查询sql

show variables like '%long%';

long_query_time 10.00000s

第三步：set long_query_time = 0.4; 设置慢查询时间标准

注：重启mysql服务，配置会被恢复到默认。

永久生效方法，要在配置文件my.cnf中进行配置

执行sql,查找slow_query_log_file中执行慢的sql

分析：

mysql> explain select * from test where username = 'user799999';

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

| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |

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

| 1 | SIMPLE | test | NULL | ALL | NULL | NULL | NULL | NULL | 798401 | 10.00 | Using where |

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

mysql> explain select * from test where id = '0799999';

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

| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |

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

| 1 | SIMPLE | test | NULL | const | PRIMARY | PRIMARY | 4 | const | 1 | 100.00 | NULL |

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

possible_keys:可能用到的索引

key:实际用到的索引

rows:扫描的行数

mysql> show variables like '%profiling%';

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

| Variable_name | Value |

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

| have_profiling | YES |

| profiling | OFF |

| profiling_history_size | 15 |

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

3 rows in set (0.00 sec)

mysql> set profiling = on;

Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> show profiles;

Empty set, 1 warning (0.00 sec)

mysql> select * from test where username = 'user799999';

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

| id | username | password | servnumber | createtime |

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

| 0799999 | user799999 | | 18111818911 | 2020-01-13 22:31:18 |

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

1 row in set (0.31 sec)

mysql> select * from test where id = '0799999';

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

| id | username | password | servnumber | createtime |

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

| 0799999 | user799999 | | 18111818911 | 2020-01-13 22:31:18 |

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

1 row in set (0.00 sec)

mysql> show profiles;

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

| Query_ID | Duration | Query |

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

| 1 | 0.31259775 | select * from test where username = 'user799999' |

| 2 | 0.00039600 | select * from test where id = '0799999' |

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

2 rows in set, 1 warning (0.00 sec)

mysql> show profile cpu,block io for query 1;

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

| Status | Duration | CPU_user | CPU_system | Block_ops_in | Block_ops_out |

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

| starting | 0.000084 | 0.000050 | 0.000027 | 0 | 0 |

| checking permissions | 0.000010 | 0.000006 | 0.000003 | 0 | 0 |

| Opening tables | 0.000029 | 0.000019 | 0.000010 | 0 | 0 |

| init | 0.000035 | 0.000023 | 0.000012 | 0 | 0 |

| System lock | 0.000010 | 0.000006 | 0.000003 | 0 | 0 |

| optimizing | 0.000011 | 0.000008 | 0.000004 | 0 | 0 |

| statistics | 0.000020 | 0.000012 | 0.000007 | 0 | 0 |

| preparing | 0.000018 | 0.000013 | 0.000006 | 0 | 0 |

| executing | 0.000005 | 0.000003 | 0.000002 | 0 | 0 |

| Sending data | 0.312217 | 0.307165 | 0.000000 | 0 | 0 |

| end | 0.000026 | 0.000017 | 0.000000 | 0 | 0 |

| query end | 0.000014 | 0.000013 | 0.000000 | 0 | 0 |

| closing tables | 0.000012 | 0.000012 | 0.000000 | 0 | 0 |

| freeing items | 0.000028 | 0.000028 | 0.000000 | 0 | 0 |

| logging slow query | 0.000064 | 0.000065 | 0.000000 | 0 | 8 |

| cleaning up | 0.000015 | 0.000015 | 0.000000 | 0 | 0 |

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

查看性能详情是否开启

show variables like '%profiling%';

开启性能记录功能

set profiling = on;

查看性能的记录

show profiles;

show profile cpu,block io for query 4;

优化小建议：

查询* 与查询单个字段 查看性能

1、尽量避免使用select * from,尽量精确到想要的结果字段

查询两个条件 用or连接 查看执行计划

2、尽量避免使用or

3、加上limit 限制行数

4、使用like时，%放在前面是会使索引失效 查看执行计划

查询条件字段类型varchar,但条件值类型int时，会进行类型转换 查看执行计划

5、注意条件字段类型的转换会使索引失效