数据库优化
高效使用索引
尽可能避免全表扫描
减少无效数据的查询
索引创建规则
idx_表名_字段名（字段名可以是多个，eg. idx_table_name，idx_table_nameAge）
Mysql所认为的执行顺序
FROM <left_table>
ON <join_condition>
<join_type>JOIN <right_table>
WHERE <where_condition>
GROUP BY <group_by_list>
HAVING <having_codition>
SELECT
DISTINCT <select_list>
ORDER BY <order_by_condition>
LIMIT <limit_number>
哪些情况需要建索引
1.主键自动建立唯一索引
2.频繁作为查询条件的字段应该创建索引
3.查询中与其他表关联的字段
4.where条件里用不到的字段不创建索引
5.统计分组排序字段
哪些情况不需要建索引
1.表记录太少（百万以下不用考虑建索引，一般数据够300万就要建索引）
2.频繁更新的字段不适合建索引
3.数据重复且分布平均的字段不建索引，比如性别，不是男就是女。索引是区别越大，效率越高
SQL优化
1.生产上出现慢SQL，首先要做的就是复现
2.设置阈值，开启慢日志查询(慢日志影响性能，生产上一定要关闭。比如超过5秒就是慢查询要这么抓出来)
3.explain + 慢日志分析
4.show profile分析（本地Navicat就可以看到，生产上只能用命令）
5.专业人士进行SQL服务器参数调优
Explain
首先要知道explain有哪些信息。id，select_type，table，partitions，type，possible_keys，key，key_len，ref，rows，filtered，Extra这些是基本的，还有些后来加的
id
id是用来表示执行顺序的东西。id相同就按顺序执行，id不同值越大；优先级越高，越先执行
一般出现id不同就是有子查询
select_type
就是select查询的类型
SIMPLE
最简单的查询语句，不包含子查询或union
PRIMARY
当查询语句中包含任何复杂的子部分，最外层查询则被标记为PRIMARY
SUBQUERY
当select或where列表中包含了子查询，该子查询被标记为SUBQUERY
DERIVED
表示包含在from子句中的子查询的select，在我们的from列表中的包含的子查询会被标记为DERIVED
UNION
如果union后边又出现的select语句，则会被标记为union；若union包含在from子句的子查询中。外层select被标记为DERIVED
UNION RESULT
代表从union的临时表中读取的数据，table列的<union1,3>表示用第一个结果和第三个select的结果进行union操作
table
并不一定是真实的表，有别名，也有临时表
partitions
查询时匹配到的分区信息，对于非分区表值为NULL，查询的是分区表时，partitions显示分区表命中的分区情况
type
system>const>eq_ref>ref>ref_or_null>index_merge>unique_subquery>index_subquery>range>index>All
system就是表中只有一行记录，相当于系统表，不需要磁盘IO，速度非常快
const表示查询时命中primary key主键或unique唯一索引，或者被连接的部分是一个常量const值，查询速度非常快
eq_ref对于每一个索引，表中只有一条记录与之匹配
ref返回匹配某个单独值的所用行，也算是一种索引访问
ref_or_null类似于ref，会额外搜索null的行
index_merge使用了索引合并，查询使用了两个以上的方法
unique_subquery该类型替换了下面形式的IN子查询的ref：value IN (SELECT primary_key FROM single_table WHERE some_expr)，返回不重复值
index_subquery和unique_subquery类似，返回重复值
range使用索引检索范围内的行betwee，>，<，in
index遍历索引树，也很慢但比All强
All全表扫描，性能最差但驱动表避免不了
possible_keys
可能使用到的索引，如果是覆盖索引，他可以为null
key
实际用到的索引，使用到则标记出索引，没有使用到则为NULL
key_len
越短当然越好，越长就越精准。需要注意的是key_len只计算where条件中用到的索引长度，而排序和分组即便用到了索引也不会计算到key_len中
ref
常见的有null，func，const，字段名
使用常量等值查询，显示const
当关联查询时，会显示相应关联表的关联字段
如果查询条件使用了表达式，函数或者内部隐式转换可能显示为func
其他情况为null
rows
rows以表的统计信息和索引使用情况，计算需要读取多少行，rows越小代表扫描的行数越少
filtered
代表表中符合条件的记录数
Extra
一些重要的额外信息
using filesort，部分使用到了索引，一般是order by的字段没有索引，需要优化
using temporary，使用中间表保存中间结果，一般和group by，order by没用到索引，需要优化
using index，使用到了覆盖索引，使用了覆盖索引，覆盖索引就是索引列和查询列一致，顺序不同无所谓。这样就不需要查询数据行直接查索引就好，推荐使用
using where，查询时未找到可用索引，进而通过where条件过滤获取
using join buffer，在我们连表查询如果连接条件没有用到索引，需要一个连接缓冲区来存储中间结果，join太多了，可以考虑加索引或调大join buffer的参数
impossible where ，where后面的条件不正确
No tables used ，没有表或者用了虚表
单表优化
1.最左前缀匹配法则
2.不在索引列上做任何操作（计算，函数，显式或者隐式转换）
3.范围条件in，>，<等索引会失效，考虑把范围放最后或者使用between
4.尽量使用覆盖索引，不使用select *
5.使用!=或<>无法使用索引，这个看版本高版本的都是range，低版本建议不要在相关字段上设置索引
6.is null，is not null不走索引
7.like只有用 xx%可以走索引，这个也看版本的，数据实在过大（过亿了这种）考虑走搜索引擎Es，Solr。如果数据量很少刚刚几千条别想了直接%xx%。如果数据量也不算小，建议使用内置函数INSTR（str，substr）
8.字符串不加单引号不走索引，MySQL会帮你隐式转换但是不走索引
9.or使索引失效，低版本会，高版本肯定不会失效
10.拼装where后的条件少用1=1，在mybatis用where标签，好多项目都用这个，不用的也有类似解决方案
11.极端情况下，可以考虑使用hint（ignore index，force index加在from后面）优化语句。所谓极端可以是某个语句他不走索引了，查询慢到开花，强制让他走索引，留时间出来分析语句。这个慎用，搞不好会影响到其他业务，数据库版本升级也会对他造成很大打击
12.别用select *，不可能一张表里所有字段都是索引的
13.能用where，不用having，因为where能提前筛选出来，就会少很多数据
多表优化
LEFT JOIN索引加在从（右）表的关联字段上，RIGHT JOIN索引加在主（左）表的关联字段上，为什么这么说呢？就比如说左连接会把主（左）表的所有关联数据都拿去做匹配。如果索引加在主（左）表就会导致要扫描一些没必要查的行，导致效率下降，右连接也是同理。但还是要以小表驱动大表，不然得出来的结果还是有很大差距的
阿里手册开发手册也提过join的次数最好不要超过三次，超过了非常影响效率（想起我以前写SQL，5表查询我写了5个left join我还很自豪.....），关于这个我有两种解决方案：1.根据业务在表中设计冗余字段，你想要的某个字段已经存好放在同一张表里了，那就不需要联表了 2.设计中间表，中间表只存其他表对应记录的id，做业务时顺便往中间表里插入其他表的id确保他的id不会为空，让需要多次JOIN的表跟中间表关联上，这样可以根据中间表id查出中间表上的其他表的id字段，再根据各个id字段查出所需要的对应信息，也就是说分两步走
索引生效图鉴
我们的索引长这样idx_abc（a，b，c），是个复合索引，一般实际使用都是用的复合索引
单用where
where及order
慢日志分析
使用慢日志的分析工具mysqldumpslow
以下是几条用的较多的命令
得到返回记录集最多的10条SQL
mysqldumpslow -s -r -t 10 慢日志地址具体到文件（/var/lib/mysql/slow.log）| more
得到访问次数最多的10条SQL
mysqldumpslow -s c -t 10 慢日志地址具体到文件 | more
得到按照时间排序的前10条里面包含有左连接的SQL
mysqldumpslow -s t -t 10 -g "left join" 慢日志地址具体到文件 | more
show profile
其实只要注意几个点就好了
converting to HEAP to MyISAM 查询结果太大，内存不够用往磁盘上搬
creating tmp table 创建临时表，拷贝数据到临时表用完了再删除，这个非常的影响效率
copying to tmp table on disk 把内存中临时表复制磁盘，这个就是必须要优化了
locked 锁表了，这个要看是为什么锁，众所周知DML加了各种锁，锁了正常那就不用管，不正常就看是不是常用的业务，不是常用的kill，常用的看看是kill好还是等到满足解锁条件自动解锁好
网友评论