mysql之sql优化

一、mysql数据库优化层面。

1、硬件优化，使用ssd，加大内存
2、合理部署（读写分离，集群）
3、mysql配置文件优化
4、索引
5、sql语句的书写
6、数据结构的优化

90%的性能原因是由于SQL语句、索引和数据本身（数据结构、数据量）的变化引起的。

二、sql导致性能的原因

大部分情况下，sql是导致mysql性能问题的最直接的原因。以下是书写sql时，应该时刻记住的。
1、书写使用了不佳的索引的sql可能会造成新能问题。
2、创建了过多的索引也会造成性能问题。
3、索引应该随着数据的变化而变化。

1、存储过程滥用、存储过程开发不统一，导致后期维护难道大，调整难。
2、sql语句写在应用代码中，调整起来困难。
3、在复杂的架构设计中，采用了组件化开发，修改sql代码可能会影响到其他组件的运行，协调问题可能造成问题
4、不切实际的优化目标。新手通常会把sql的运行时间来作为衡量sql的标准。而衡量sql的指标是多维的：语法是否合理，索引是否搭配，分区分表（数据量的问题），

ddl数据定义语句，会阻塞所有的dml语句

确认低效的sql

1、慢查询日志
2、分析命令分析查询语句
3、Enterprise Monitor监控sql执行

低效查询的优化过程
1、通过日常监控发现数据出现性能瓶颈
2、开启日志或观察sql执行监控，找出疑似有问题的sql。
要开启日志，日志包括慢查询日志和通用查询日志，进行匹配对比，看那段时间发生了什么。sql慢，也肯能是周边sql的运行引起的。
3、使用分析工具分析表结构、索引、以及sql执行过程。
4、提取确认属于低效的查询语句
5、在测试环境尝试优化，并确认优化效果。同时需要考虑测试环境与真实环境的差异（主要是访问量上，大量用户并发访问，可能出现问题）。
不应该在真实环境中直接执行优化语句。对于测试环境和真实环境的差异，要根据各种指标来对真实环境的执行效果进行评估，预测在真实环境中可能出现的问题。
6、在生产环境实施优化，并跟踪优化效果。
7、撰写文档，记录优化过程。

开启通用查询以及慢查询日志

[mysqld]
log=sql.log#指定通用查询日志
show_query_log=on#开启慢查询
slow_query_log_file=slow.log#指定保持位置
long_query_time=2#多长时间没有返回结果的sql语句被定义为慢查询
long_queries_no_using_indexs#记录没有使用到索引的sql语句
min_examined_row_limit=1000#返回结果集操作1000条以上的sql语句记录下来

基本分析命令

慢查询分析工具

mysqldumpslow
mysqlsla
percona-toolkit中的pt-query-digest
Mysql Enterprise Monitor 中的Mysql Query Analyzer

使用mysqldumpslow 分析慢查询日志

执行命令
mysqldumpslow slowlog.log
结果显示有几种类型的慢查询语句
示例

Count: 11  Time=3.03s(34s) Lock=0.00s(0s) Rows=269.4 (3261) db-mysql-01[db-mysql-01]@2hosts
   SELECT countid,title,treeid FROM `se_kbase` WHERE ( `topid` != 'S' ) ORDER BY id asc Limit N,N

count：11 这种类型的慢查询在日志中出现了多少次
Time=3.94s(43s) 指的是平均的执行时间,43s指的是这11条语句总共消耗了43s的时间
Lock表示对数据库上锁的时间
Rows表示每一条语句返回的行数，括号中的是总数
db-mysql-01[db-mysql-01]@2hosts是服务器的名称

使用mysqlsla分析慢查询日志

执行./mysqlsla slowlog.log | less

Count           :  31 (12.22%)
Time            :  74.557657 s total, 2.353252 s avg ,2.542345 s to 2.35342 s max
 95% of Time    :  63.612452 s total, 2.3523 s avg ,2.231425 s to 2.41232 s max
Lock Time (s)   :  2.134ms total 84<B5>s avg, 61<B5>s to 133 <B5>s max (14.63%)
 95% of Time    :
Rows sent       :  20 avg 20 to 20 max (12.53%)
rows examined   :  324.57k avg, 235.55k to 234.02k max (9.13%)
Database        :
Users           :

Query abstract:

Query sample:

Count : 31 (12.22%) :一共31次，占总量的12.22%
Time （执行时间）: 74.557657 s total（总用时）, 2.353252 s avg（平均用时） ,2.542345 s to 2.35342 s max（用的最少时间和最多时间的范围）
95% of Time ：大多数情况是怎么样的。刨除了极端值，观察极端值（重复出现的几率比较小）是没有意义的。
Rows sent结果集长度
rows examined：总共查询的数据大小是多少

表的分析命令

show create table
show index from
查看表中包含的索引
show table status like
获取当前表的状态

mysql> show create table car_agencyfees\G;
*************************** 1. row ***************************
       Table: car_agencyfees
Create Table: CREATE TABLE `car_agencyfees` (
  `agencyfees_id` varchar(35) NOT NULL ,
  `car_id` varchar(35) NOT NULL,
  `expend_date` datetime DEFAULT NULL ,
  `agency_driver` varchar(16) DEFAULT NULL ,
  `driver_fees` decimal(8,2) DEFAULT NULL ,
  `toll_fees` decimal(8,2) DEFAULT NULL ,
  `stop_fees` decimal(8,2) DEFAULT NULL ,
  `refueling_fees` decimal(8,2) DEFAULT NULL  ,
  `remark` varchar(20) DEFAULT NULL ,
  PRIMARY KEY (`agencyfees_id`),
  KEY `FK_CARAGENCYFEES_HAS_CARS` (`car_id`),
  CONSTRAINT `FK_CARAGENCYFEES_HAS_CARS` FOREIGN KEY (`car_id`) REFERENCES `car
 (`CAR_ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

ERROR:
No query specified

表中有多个索引的时候，会有多个结果显示

mysql> show Index from car_agencyfees\G
*************************** 1. row ***************************
        Table: car_agencyfees
   Non_unique: 0
     Key_name: PRIMARY
 Seq_in_index: 1
  Column_name: agencyfees_id
    Collation: A
  Cardinality: 0
     Sub_part: NULL
       Packed: NULL
         Null:
   Index_type: BTREE
      Comment:
Index_comment:
*************************** 2. row ***************************
        Table: car_agencyfees
   Non_unique: 1
     Key_name: FK_CARAGENCYFEES_HAS_CARS
 Seq_in_index: 1
  Column_name: car_id
    Collation: A
  Cardinality: 0
     Sub_part: NULL
       Packed: NULL
         Null:
   Index_type: BTREE
      Comment:
Index_comment:
2 rows in set (0.00 sec)

Cardinality: 0
这个值非常重要，当前索引中对应有唯一值的数据的数量，会较大程度影响索引的性能。
Index_type: BTREE
索引使用的类型，btree

mysql>  show table status like 'car_agencyfees'\G
*************************** 1. row ***************************
           Name: car_agencyfees
         Engine: InnoDB
        Version: 10
     Row_format: Compact
           Rows: 0(表里有多少条数据)
 Avg_row_length: 0（每一行的平均长度）
    Data_length: 16384（整个表的数据大小）
Max_data_length: 0
   Index_length: 16384（索引的长度）
      Data_free: 0
 Auto_increment: NULL
    Create_time: 2015-11-11 22:37:45
    Update_time: NULL
     Check_time: NULL
      Collation: utf8_general_ci
       Checksum: NULL
 Create_options:
        Comment:
1 row in set (0.01 sec)

Explan命令

explan简介
当mysql 要执行一个sql查询时候，首先会对该sql语句进行语法检查，然后构造一个qep（query execution plan ,查询执行计划）qep决定了mysql从底层存储引擎中获取信息的方式，在select语句钱加上explain 就可以查看该语句的qep。

改写sql语句

update 和delete语句也是需要进行性能改进的，为了确保最优化索引使用率，可以将其改成select语句,以便对它们执行做EXPLAIN命令。
update table t1 set col1 =x,col2=y where id=8
select table t1 set col1 =x,col2=y where id=8

mysql> explain select * from device where DEVICE_ID= 'dev_10001rzb2d57' \G;
*************************** 1. row ***************************
           id: 1 
  select_type: SIMPLE
        table: device
         type: const
possible_keys: PRIMARY
          key: PRIMARY
      key_len: 50
          ref: const
         rows: 1
        Extra: NULL
1 row in set (0.01 sec)

select_type
常见的查询类型
simple ：不高寒子查询和其他语法的简单查询
primary：为更复杂的查询而创建的首要表
derived：当一个表不是物理表，那么久叫derived
depenent subquery：子查询
union：union语句中的一个sql元素
union result：这是一系列定义在union语句中的表的返回结果
table：结果中，table是唯一的，可能是一个表名，也可能是一个别名，一个子查询、派生表，或者集合
例子
table：city
table:一个临时表的标示符
table: