mysql-索引的使用及sql的一些优化

MySQL优化

一，查看SQL的执行效率

MySQL 客户端连接成功后，通过一下语句查看服务器相关状态

show [session|global] status 命令可以提供服务器状态信息

show status like 'Com_______';

show status like 'Innodb_rows_%';

参数	含义
Com_select	执行 select 操作的次数，一次查询只累加 1。
Com_insert	执行 INSERT 操作的次数，对于批量插入的 INSERT 操作，只累加一次。
Com_update	执行 UPDATE 操作的次数。
Com_delete	执行 DELETE 操作的次数。
Innodb_rows_read	select 查询返回的行数。
Innodb_rows_inserted	执行 INSERT 操作插入的行数。
Innodb_rows_updated	执行 UPDATE 操作更新的行数。
Innodb_rows_deleted	执行 DELETE 操作删除的行数。
Connections	试图连接 MySQL 服务器的次数。
Uptime	服务器工作时间。
Slow_queries	慢查询的次数。

二，如何定位执行效率低的SQL语句

• show processlist：show processlist命令查看当前MySQL在进行的线程，包括线程的状态、是否锁表等，可以实时地查看 SQL 的执行情况，同时对一些锁表操作进行优化。

列	含义
id	用户登录mysql时，系统分配的"connection_id"，可以使用函数connection_id()查看
user	显示当前用户。如果不是root，这个命令就只显示用户权限范围的sql语句
host	显示这个语句是从哪个ip的哪个端口上发的，可以用来跟踪出现问题语句的用户
db	显示这个进程目前连接的是哪个数据库
command	显示当前连接的执行的命令，一般取值为休眠（sleep），查询（query），连接（connect）等
time	显示这个状态持续的时间，单位是秒
state	显示使用当前连接的sql语句的状态
info	显示sql语句

• 慢查询日志
  慢查询日志记录了所有执行时间超过参数long_query_time设置值并且扫描记录数不小于 min_examined_row_limit 的所有的SQL语句的日志

  慢日志的设置

  # 该参数用来控制慢查询日志是否开启， 可取值： 1 和 0 ， 1 代表开启， 0 代表关闭
  slow_query_log=1 
  
  # 该参数用来指定慢查询日志的文件名
  slow_query_log_file=slow_query.log
  
  # 该选项用来配置查询的时间限制， 超过这个时间将认为值慢查询， 将需要进行日志记录， 默认10s
  long_query_time=10
  
  

三，explain分析执行计划

通过以上步骤查询到效率低的 SQL 语句后，可以通过 EXPLAIN或者 DESC命令获取 MySQL如何执行 SELECT 语句的信息，包括在 SELECT 语句执行过程中表如何连接和连接的顺序。

查询SQL语句的执行计划 ：

字段	含义
id	select查询的序列号，是一组数字，表示的是查询中执行select子句或者是操作表的顺序。
select_type	表示 SELECT 的类型，常见的取值有 SIMPLE（简单表，即不使用表连接或者子查询）、PRIMARY（主查询，即外层的查询）、UNION（UNION 中的第二个或者后面的查询语句）、SUBQUERY（子查询中的第一个 SELECT）等
table	输出结果集的表
type	表示表的连接类型，性能由好到差的连接类型为( system —> const -----> eq_ref ------> ref -------> ref_or_null----> index_merge —> index_subquery -----> range -----> index ------> all )
possible_keys	表示查询时，可能使用的索引
key	表示实际使用的索引
key_len	索引字段的长度
rows	扫描行的数量
filtered	实际显示行数占扫描rows的比例
extra	执行情况的说明和描述

• id

  id 字段是 select查询的序列号，是一组数字，表示的是查询中执行select子句或者是操作表的顺序

  • id相同，表示加载表的顺序是从上到下

  EXPLAIN select * from t_role r, t_user u, user_role ur where r.id = ur.role_id and u.id = ur.user_id ;
  

• id不同，id的值越大，优先级越高，越先被执行

  EXPLAIN SELECT * FROM t_role WHERE id = (SELECT role_id FROM user_role WHERE user_id = (SELECT id FROM t_user WHERE username = 'stu1'))

• id有相同，有不同，同时存在的时候，id相同的认为是一组，从上到下顺序执行，在所有的组中，id值越大优先级越高越先执行

 EXPLAIN SELECT * FROM t_role r , (SELECT * FROM user_role ur WHERE ur.`user_id` = '2') a WHERE r.id = a.role_id ; 

• select_type

  select_type表示select的类型

  select_type	含义
  SIMPLE	简单的select查询，查询中不包含子查询或者UNION
  PRIMARY	查询中若包含任何复杂的子查询，最外层查询标记为该标识
  SUBQUERY	在SELECT 或 WHERE 列表中包含了子查询
  DERIVED	在FROM 列表中包含的子查询，被标记为 DERIVED（衍生） MYSQL会递归执行这些子查询，把结果放在临时表中
  UNION	若第二个SELECT出现在UNION之后，则标记为UNION ； 若UNION包含在FROM子句的子查询中，外层SELECT将被标记为 ： DERIVED
  UNION RESULT	从UNION表获取结果的SELECT

• table

  table表示展示的数据属于哪个表

• type
  type表示连接类型

  性能由好到差：
  NULL > system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_