Mysql优化

Mysql variables和status:
mysql提供variables参数配置变量和status参数状态变量：

所有的mysql配置信息都在variables中，这些值可以修改
所有的mysql状态值都在status表中，这些值不能修改，只能查看

通过show status命令了解各种sql的执行频率。
mysql > show [session|global] status;

其中：session(默认)表示当前连接connection       global 表示自数据库启动至今(一般要加参数)

mysql> show status;
mysql> show global status;
mysql> show status like 'com_%';
mysql> show global status like 'com_%';

--查询增删改查语句的执行次数
mysql> show (global) status like 'com_insert%';
mysql> show (global) status like 'com_update%';
mysql> show (global) status like 'com_delete%';
mysql> show (global) status like 'com_select%';

--针对innodb存储引擎：
innodb_rows_read 执行select操作的行数
innodb_rows_updated 执行update操作的影响行数
innodb_rows_inserted 执行update操作的影响行数
innodb_rows_deleted 执行update操作的影响行数

--比如：
myql>delete from t1 where id < 10 ;  
--如果删除10条，那么innodb_rows_deleted的值+10

其他：
connections 连接mysql的数量
uptime 服务器已经工作的秒数
show_queries: 慢查询的次数

mysql> show status like 'slow_queries';    --查看慢查询是否开启
mysql> show variables like 'long_query_time';  --查看多少秒才认定为慢查询的配置

索引优化和sql优化：
索引是数据库优化中最常见也是最重要的手段之一，通过索引通常可以帮助用户解决大多数的sql性能问题。

1、复合索引的使用：
对于创建的多列索引，只要查询的条件中用到最左边的列，索引一般就会被使用，如下创建一个复合索引

mysql>create index in_sale_com_mon on sales ( company_id, moneys);

按照company_id(左边的列)就会使用到复合索引。如果按照moneys查询就不会用到复合索引

2、like查询索引的使用：

只有%不在第一个字符，name索引才会被使用。所以对于大的文本进行搜索，尽量避免使用like '%...%';

mysql>explain select * from company where name like '%clou%';

3、如果列名是索引，所用column_name is null 将使用索引，使用is not null将不使用索引。

mysql>explain select * from company where name is not null;

4、mysql估计使用索引比全表扫描更慢，就不使用索引---- > possible key有值，key没值
用or分割的条件，如果or前面的条件中有索引，后面的条件没有索引，那么涉及的索引都不会用到

mysql>explain select * from company where id= 1 and name = 'clou' \G;

5、如果name字段是索引，注意值是字符串：

mysql>explain select * from company where name = 'clou' \G; --使用索引
mysql>explain select * from company where name = 123 \G;    --不使用索引

6、插入数据优化：
这样比写一条一条的insert快，一次性提交事务。

mysql>insert into t1(name) values('aaa'),('bbb'),('ccc');

7、优化group by 语句：
如果查询包含group by 但用户想要避免排序结果的损耗，则可以使用order by null来严禁排序
（默认排序）

mysql>explain select id,sum(money) from sale group by id \G;

可以看到Extra:USING filesort;

8、尽量避免语句嵌套，而使用join关联

mysql>desc select * from user where id in (select uid from order) \G;

嵌套语句使用到索引，但是id不使用索引

应该更多使用连接join(left,right,inner)

mysql>desc select user.* from user left join order on order.uid = user.id where order.uid is not null \G;

9、导入导出优化：

mysql>select name from t into outfile '/tmp/test.txt';
mysql>load data infile '/tmp/test.txt' into table t(name);

因为innodb表的是按照主键顺序保存的，所以将导入的数据主键顺序排列，可以有效提高导入数据的效率
关闭唯一性校验可以提高效率，但要保证数据没有重复

mysql>set unique_checks =0 ;
mysql>set unique_checks =1;

关闭自动提交可以提高导入效率

mysql>set autocommit= 0;
mysql>set autocommit= 1;

10、使用中间表优化
使用中间表提供查询速度
中间表使用视图更好，因为视图可以动态的自动维护
比如把热门分享放入到视图，当分享表变动时，会根据动态维护视图。
当业务层调用时，可以直接从视图中查询

两种常用的简单优化方法：
定期分析表和检查表

mysql>analyze table sales;

本语句用于分析和存储表的关键字分别，分析的结果将可以使得系统得到精准的统计信息，
使得sql能够生产正确的行计划

定期优化表：
如果已经删除了表的一大部分，后者如果已经对含有可变长度行进行了很多的改动，则需要做定期优化，
这个命令可以将表中的空间碎片进行合并，但是此命令只对MyIsam,BDB,InnoDB表起作用。

mysql>optimize table sales;

锁：

mysql>lock table t1 read;  --加了读锁，写操作不能执行

加入读锁，再增删改这张表，你会发现所有的数据都停留在终端上没有真正的去操作。
读锁定对我们在做备份大量数据时非常有用。

mysqldump -uroot -p123 test > test.sql;

mysql>lock table t1 write;  --读锁不知道有啥用
mysql>unlock tables;

慢查询使用：

mysql> show variables like '%slow%';
+---------------------+-----------------------------------+
| Variable_name       | Value                             |
+---------------------+-----------------------------------+
| log_slow_queries    | OFF                               |
| slow_launch_time    | 2                                 |
| slow_query_log      | OFF                               |
| slow_query_log_file | /home/mysql/iZ94mvb4k9rZ-slow.log |
+---------------------+-----------------------------------+
4 rows in set (0.00 sec)

mysql> show variables like '%long%';
+---------------------------------------------------+-----------+
| Variable_name                                     | Value     |
+---------------------------------------------------+-----------+
| long_query_time                                   | 10.000000 |
| max_long_data_size                                | 16777216  |
| performance_schema_events_waits_history_long_size | 10000     |
+---------------------------------------------------+-----------+

添加慢查询日志：

vi /etc/my.cnf
log_slow_queries=slow.log
long_query_time =5

重启数据库

#> service mysql restart;

mysql问题排查思路：
定位执行效率比较低的语句(两种写法一样)

mysql > desc select * from t1 where id = 10000;
mysql > explain select * from t1 where id = 10000;

mysql> desc select * from user where name like 'dhh' \G;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE  SIMPLE->单表 PRIMARY->主查询(外层的查询) UNION->union中的第二个或者后面的查询语句  SUBQUERY->子查询的第一个SELECT
        table: user 
         type: ALL     性能由好到差：system(表仅一行)--> const(只一行匹配)--> eq_ref(对于前面的每一行就是主键和唯一) -->ref(同上,但没使用主键和唯一)-->
		       index_merge(索引合并优化)--> unique_subquery(主键子查)-->range(表单中的范围查询)--> index(通过查询索引得数据)-->all全表扫描)
possible_keys: NULL    表查询可能使用的索引
          key: NULL    实际使用的索引
      key_len: NULL    索引字段的长度
          ref: NULL    
         rows: 3937631 扫描行的数量（关键）
        Extra: Using where
1 row in set (0.00 sec)

总结：如果一个系统，有查询时间非常长的sql语句，可能导致服务器CPU占用率过高，从而导致服务器宕机
所以为了方便排查问题，一定要开启bin-log日志和慢查询日志，通过慢查询日志找到问题sql，再用desc查看
语句是否要优化，主要看rows参数，如果过高，可能是全表扫描，考虑建索引，或者其他优化。