SQL优化

MYSQL 优化SQL 的一般步骤
一：SQL 语句
1. 通过 show status 命令了解各种 SQL 的执行频率
show status like 'Com_%';
关心的参数:
Com_select: 执行SELECT 操作的次数
Com_insert: 执行INSERT 操作的次数
Com_update: 执行UPDATE 操作的次数
Com_delete: 执行DELETE 操作的次数
针对InnoDB存储引擎的参数:
Innodb_rows_read: SELECT 查询返回的行数
Innodb_rows_inserted: 执行INSERT操作插入的行数
Innodb_rows_update: 执行UPDATE操作更新的行数
Innodb_rows_delete: 执行DELETE操作删除的行数

通过以上参数可以了解应用是以插入更新为主，还是以查询为主
事务：
Com_commit 和 Com_rollback 可以了解事务提交和回滚的情况
数据库基本情况：
Connection: 试图连接数据库服务器的次数
Uptime: 服务器工作时间
Slow_queries: 慢查询的次数

2.定位执行效率较低的SQL语句
通过慢查询日志定位执行效率较低的SQL语句 TODO
使用 show processlist 查看当前mysql在进行中的线程的状态和是否锁表


3. 通过 explain 分析低效sql的执行计划
关注的参数:
select_type: 表示SELECT 的类型，常见的取值有
SIMPLE(简单，未使用连接或子查询)
PRIMARY(主查询，外层的查询)
UNION(UNION 中的第二个或者后面的查询语句)
SUBQUERY(子查询中的第一个select)
table: 输出结果集的表
type: 表示访问的类型
ALL、index、range、ref、eq_ref、const,system、null 从左到右性能由差到好
type = ALL 全表扫描
type = index 索引全扫描
type = range 索引范围扫描
type = ref 使用非唯一索引扫描或者唯一索引的前缀扫描
type = eq_ref 使用唯一索引扫描
type = const/system 单表中最多有一个匹配行
type = NULL 不用访问表或者索引就能拿到结果
possible_keys: 表示查询中可能用到的索引
key: 表示实际使用的索引
key_len: 使用索引字段的长度
rows: 扫描行的数量
Extra: 执行情况和描述
4. 使用 explain extended + show warnings 可以迅速获取一个更清晰易读的sql
5. 使用 show profile 可以看到执行过称中的消耗时间和query_id
可以先使用 show profile 获取query_id,再使用 show profile for quey query_id 查询执行过程中线程的每个状态和消耗时间

二. 索引问题
1.能够使用索引的场景
1.匹配全值，对索引中所有列都指定具体的值
2.匹配值的范围查询，对索引的值能够进行范围查找
3.匹配最左前缀，仅仅使用索引中最左边列进行查找
4.仅仅对索引进行查询
5.匹配列前缀，仅仅使用索引中的第一列
6.能够实现索引匹配部分精确而其他部分进行范围匹配
7.如果列名是索引，那么使用 column_name is null 就会使用索引 (区别Oracle)
2.存在索引但不能使用索引的场景
1.以%开头的like不能够利用 B-Tree 索引
2.数据类型出现隐式转换的时候也不会使用索引，比如 字符串 不加引号
3.复合索引，不满足最左前缀原则
4.如果使用索引比全表扫描更慢，就不使用索引
5.用 or 分割开的条件，如果用or有索引，而后面没有索引，那么索引就不会用到
3. 查询索引的使用情况
show status like 'Handler_read%';
三、SQL 优化
INSERT:
1. 如果同事从同一客户插入很多行，应使用多个值表的 insert 语句，缩短客户端与数据库之间的连接、关闭等消耗
insert into emp values(),(),()..;
ORDER BY:
1. 通过有序索引扫描直接返回有序数据 explain 显示 Using Index
2. 通过对返回的数据进行排序，Filesort
建议:尽量减少额外的排序，通过索引直接返回有序数据
排序不能使用索引的情况
1)order by 的字段混合 ASC 和 DESC
select * from tabname order by key1 desc, ke2 asc;
2)用于查询的关键字与 order by 中所使用的的不同
select * from tabname where key2 = xxx order by ky;
3)对于不同的关键字使用order by
select * from tabname order by key1,key2；
尽量使用必要的字段，select 具体的字段名称，而不是select * 所有的字段
GROUP BY:
默认情况下，mysql 会对group by col1,col2... 的字段进行排序，这与在查询中指定 order by col1，col2... 类似
所以如果Group by 想要避免排序消耗，可以使用 order by null 禁止排序

or:
对于含有or的查询子句，如果想要利用索引，那么 or 之间的每个条件列都必须用到索引 用到了union操作

limit:
翻页过程中增加一个参数 last_page_record 用来记录上一页最后一行的租赁编号 rental_id
select payment_id.rental_id from payment where rental_id < 15640 order by rental_id desc limit 10;