mysql--知识点整理

mysql–知识点整理

mysql 慢查询

开启 mysql 慢查询日志

mysql> show variables like 'long%';
long_query_time | 10.000000
mysql> set long_query_time=1;
mysql> show variables like 'slow%';
slow_launch_time    | 2
slow_query_log      | ON
slow_query_log_file | /tmp/slow.log
mysql> set global slow_query_log='ON'

long_query_time :当SQL语句执行时间超过此数值时，就会被记录到日志中，建议设置为1或者更短。
slow_query_log :这个参数设置为ON，可以捕获执行时间超过一定数值的SQL语句。
slow_query_log_file :记录日志的文件名。一旦slow_query_log变量被设置为ON，MySQL会立即开始记录。
/etc/my.cnf: 里面可以设置上面MySQL全局变量的初始值。

慢日志分析工具Mysqldumpslow

mysqldumpslow能将相同的慢SQL归类，并统计出相同的SQL执行的次数，每次执行耗时多久、总耗时，每次返回的行数、总行数，以及客户端连接信息等。

mysqldumpslow语法说明：
-s ，按照什么方式起来排序。默认at，也就是按照平均查询时间来排序。都是按照倒序排列。
al: average lock time 平均锁定时间
ar: average rows sent 平均返回行数
at: average query time 平均查询时间
c: count 总执行次数
l: lock time 总锁定时间
r: rows sent 总返回行数
t: query time 总查询时间
-t ，show the top n queries，显示前多少名的记录
-a ，默认不开启这个选项。mysqldumpslow将相似的SQL的值（字符串或者数字）替换为N，开启该选项，则显示真实值。不开启该选项，有点类似于Oracle的绑定变量的记录。
-g ，类似于grep命令，过滤出需要的信息。如，只查询A表的慢查询记录。
-l ，总时间中包含锁定时间

举例说明：

-- 平均执行时间最长的前5条SQL
mysqldumpslow -s at -t 5 dev_121_21-slow.log
--执行结果如下：
[root@dev_121_21 data]# mysqldumpslow -s at -t 10 dev_121_21-slow.log 
Reading mysql slow query log from dev_121_21-slow.log
Count: 1  Time=14348.68s (14348s)  Lock=0.00s (0s)  Rows=182897.0 (182897), super_dev[super_dev]@[192.168.120.103]
  SELECT * FROM `MOVIE_INTERFACE_LOG`

Count: 1  Time=6763.39s (6763s)  Lock=0.00s (0s)  Rows=11751450.0 (11751450), super_dev[super_dev]@[192.168.120.103]
  SELECT * FROM `access_log`

Count: 7  Time=5123.90s (35867s)  Lock=0.00s (0s)  Rows=0.0 (0), root[root]@localhost
  call test.pro_test_data_1(N,N)

Count: 2  Time=3449.94s (6899s)  Lock=0.00s (0s)  Rows=2114376.5 (4228753), super_dev[super_dev]@[192.168.120.103]
  SELECT * FROM `UC_USER`

Count: 1  Time=2410.83s (2410s)  Lock=0.00s (0s)  Rows=0.0 (0), dev_user[dev_user]@[192.168.120.223]
  show create table `user_db`.`access_log`

--平均锁定时间最长的前10条SQL
mysqldumpslow -s al -t 10 dev_121_21-slow.log

--执行次数最多的前10条SQL
mysqldumpslow -s c -t 10 dev_121_21-slow.log

--显示SQL的具体值，而不是由N代替。便于查看执行计划并优化
mysqldumpslow -a slow.log

--显示所有occ_sys表相关的慢SQL的具体值
mysqldumpslow -a -g 'occ_sys' dev_121_21-slow.log

show processlist 命令

• Id列一个标识，你要kill一个语句的时候很有用，用命令杀掉此查询 /*/mysqladmin kill 进程号。

• User列显示单前用户，如果不是root，这个命令就只显示你权限范围内的sql语句。

• Host列显示这个语句是从哪个ip的哪个端口上发出的。用于追踪出问题语句的用户。

• db列显示这个进程目前连接的是哪个数据库。

• Command列显示当前连接的执行的命令，一般就是休眠（sleep），查询（query），连接（connect）。

• Time列此这个状态持续的时间，单位是秒。

• Info列显示这个sql语句，因为长度有限，所以长的sql语句就显示不全，但是一个判断问题语句的重要依据。

• State列显示使用当前连接的sql语句的状态，很重要的列，后续会有所有的状态的描述，请注意，state只是语句执行中的某一个状态，一个 sql语句，以查询为例，可能需要经过copying to tmp table，Sorting result，Sending data等状态才可以完成
  这个命令中最关键的就是state列，MySQL列出的状态主要有以下几种：

  • Checking table正在检查数据表（这是自动的）。

  • Closing tables正在将表中修改的数据刷新到磁盘中，同时正在关闭已经用完的表。这是一个很快的操作，如果不是这样的话，就应该确认磁盘空间是否已经满了或者磁盘是否正处于重负中。

  • Connect Out复制从服务器正在连接主服务器。

  • Copying to tmp table on disk由于临时结果集大于tmp_table_size，正在将临时表从内存存储转为磁盘存储以此节省内存。

  • Creating tmp table正在创建临时表以存放部分查询结果。

  • deleting from main table服务器正在执行多表删除中的第一部分，刚删除第一个表。

  • deleting from reference tables服务器正在执行多表删除中的第二部分，正在删除其他表的记录。

  • Flushing tables正在执行FLUSH TABLES，等待其他线程关闭数据表。

  • Killed发送了一个kill请求给某线程，那么这个线程将会检查kill标志位，同时会放弃下一个kill请求。MySQL会在每次的主循环中检查kill标志位，不过有些情况下该线程可能会过一小段才能死掉。如果该线程程被其他线程锁住了，那么kill请求会在锁释放时马上生效。

  • Locked被其他查询锁住了。

  • Sending data正在处理SELECT查询的记录，同时正在把结果发送给客户端。

  • Sorting for group正在为GROUP BY做排序。

  • Sorting for order正在为ORDER BY做排序。

  • Opening tables这个过程应该会很快，除非受到其他因素的干扰。例如，在执ALTER TABLE或LOCK TABLE语句行完以前，数据表无法被其他线程打开。正尝试打开一个表。

  • Removing duplicates正在执行一个SELECT DISTINCT方式的查询，但是MySQL无法在前一个阶段优化掉那些重复的记录。因此，MySQL需要再次去掉重复的记录，然后再把结果发送给客户端。

  • Reopen table获得了对一个表的锁，但是必须在表结构修改之后才能获得这个锁。已经释放锁，关闭数据表，正尝试重新打开数据表。

  • Repair by sorting修复指令正在排序以创建索引。

  • Repair with keycache修复指令正在利用索引缓存一个一个地创建新索引。它会比Repair by sorting慢些。

  • Searching rows for update正在讲符合条件的记录找出来以备更新。它必须在UPDATE要修改相关的记录之前就完成了。

  • Sleeping正在等待客户端发送新请求.

  • System lock正在等待取得一个外部的系统锁。如果当前没有运行多个mysqld服务器同时请求同一个表，那么可以通过增加–skip-external-locking参数来禁止外部系统锁。

  • Upgrading lockINSERT DELAYED正在尝试取得一个锁表以插入新记录。

  • Updating正在搜索匹配的记录，并且修改它们。

  • User Lock正在等待GET_LOCK()。

  • Waiting for tables该线程得到通知，数据表结构已经被修改了，需要重新打开数据表以取得新的结构。然后，为了能的重新打开数据表，必须等到所有其他线程关闭这个表。以下几种情况下会产生这个通知：FLUSH TABLES tbl_name, ALTER TABLE, RENAME TABLE, REPAIR TABLE, ANALYZE TABLE,或OPTIMIZE TABLE。

  • waiting for handler insertINSERT DELAYED已经处理完了所有待处理的插入操作，正在等待新的请求。

explain

explain select * from activity_flow
        where  uid = 3 and act_sub_id = 12 and  type ='bargain'  and status = 20

• id: 按照sql语法解析后分层后的编号，可能重复
• select_type：
  • SIMPLE，简单的select查询,不使用union及子查询
  • PRIMARY，最外层的select查询
  • UNION，UNION 中的第二个或随后的 select 查询,不依赖于外部查询的结果集
  • DEPENDENT UNION，UNION 中的第二个或随后的 select 查询,依赖于外部查询的结果集
  • SUBQUERY，子查询中的第一个 select 查询,不依赖于外部查询的结果集
  • DEPENDENT SUBQUERY，子查询中的第一个 select 查询,依赖于外部查询的结果集
  • DERIVED，用于 from子句里有子查询的情况。 MySQL会递归执行这些子查询, 把结果放在临时表里。
  • UNCACHEABLE SUBQUERY，结果集不能被缓存的子查询,必须重新为外层查询的每一行进行评估。
  • UNCACHEABLE UNION，UNION 中的第二个或随后的 select 查询,属于不可缓存的子查询
• table：涉及的表，如果SQL中表有赋别名，这里出现的是别名
• type：
  • system，从系统表读一行。这是const联接类型的一个特例。
  • const，表最多有一个匹配行,它将在查询开始时被读取。因为仅有一行,在这行的列值可被优化器剩余部分认为是常数。const表很快,因为它们只读取一次!
  • eq_ref，查询条件为等于
  • ref，条件查询不等于
  • ref_or_null，同ref(条件查询),包含NULL值的行。
  • index_merge，索引联合查询
  • unique_subquery，利用唯一索引进行子查询
  • index_subquery，用非唯一索引进行子查询
  • range，索引范围扫描
  • index，索引全扫描
  • ALL，全表扫描。
• possible_keys：可能使用的索引
• key：sql中使用的索引
• key_len：索引长度
• ref：使用哪个列或常数与key一起从表中选择行。
• rows：显示MYSQL执行查询的行数，简单且重要，数值越大越不好，说明没有用好索引
• Extra：该列包含MySQL解决查询的详细信息。
  • Distinct,去重，返回第一个满足条件的值
  • Not exists 使用not exists查询
  • Range checked for each record,有索引，但索引选择率很低
  • Using filesort,有序查询
  • Using index,索引全扫描
  • Using index condition,索引查询
  • Using temporary,临表表检索
  • Using where,where条件查询
  • Using sort_union,有序合并查询
  • Using union,合并查询
  • Using intersect,索引交叉合并
  • Impossible WHERE noticed after reading const tables,读取const tables,查询结果为空
  • No tables used,没有使用表
  • Using join buffer (Block Nested Loop),使用join buffer(BNL算法)
  • Using MRR(Multi-Range Read ) 使用辅助索引进行多范围读

sql优化

• 应尽量避免在 where 子句中使用!=或<>操作符，否则将引擎放弃使用索引而进行全表扫描

• 应尽量避免在where子句中使用 is null 或 is not null 对字段进行判断，否则将导致引擎放弃使用索引而进行全表扫描

• 尽量避免在 where 子句中使用 or 来连接条件，否则将导致引擎放弃使用索引而进行全表扫描，可以使用union all代替

• 尽量避免在 like 子句中使用 like ‘%test’ (前置百分号)来连接条件，否则将导致引擎放弃使用索引而进行全表扫描

• in 和 not in 也要慎用，对于连续的数值，能用 between 就不要用 in

• 在使用索引字段作为条件时，如果该索引是复合索引，那么必须使用到该索引中的第一个字段作为条件时才能保证系统使用该索引，否则该索引将不会被使 用，并且应尽可能的让字段顺序与索引顺序相一致

• 避免在 where 子句中对字段进行表达式操作，where id/2 = 100

• 避免在 where 子句中对字段进行函数操作,where substring(name,1,8) = 'UncleToo'

• 避免 SELECT*，需要什么数据，就查询对应的字段

• 在数据库设计的时候，尽量将可能会出现 null 值的字段设置默认值

添加索引

适合使用索引的场景

• 主键自动创建唯一索引
• 频繁作为查询条件的字段
• 查询中与其他表关联的字段
• 查询中排序的字段
• 查询中统计或分组字段

不适合使用索引的场景

• 频繁更新的字段
• where 条件中用不到的字段
• 表记录太少
• 经常增删改的表
• 字段的值的差异性不大或重复性高