1、SQL性能下降的原因
数据太多：考虑分库分表
关联了太多的表：SQL优化
没有充分利用到索引：建立索引
服务器调优及各个参数设置：调整my.cnf
2、索引简介
除了数据本身之外，数据库还维护着一个满足特定查找算法的数据结构，这些数据结构以某种方式指向数据，这样就可以在这些数据结构的基础上实现高级查找算法，这种数据结构就是索引。
优势：提高数据检索的效率，降低数据库的IO成本；通过索引列对数据进行排序，降低数据排序的成本，降低了CPU的消耗。
劣势：虽然索引大大提高了查询速度，同时却会降低更新表的速度，更新表时，MySQL不仅要保存数据，还要保存一下索引文件每次更新添加了索引列的字段。实际上索引也是一张表，该表保存了主键与索引字段，并指向实体表的记录，所以索引列也是要占用空间的。
2.1、MySQL索引结构
2.2、MySQL索引分类
单值索引：即一个索引只包含单个列，一个表可以有多个单列索引。
语法：
create index idx_name on user(name);
drop index idx_name on user;
唯一索引：索引列的值必须唯一，但允许有空值
语法：
create unique index idx_name on customer(name);
drop index idx_name on customer;
主键索引：设定为主键后，数据库会自动建立索引，innodb为聚簇索引。
语法：
alter table user add primary key user(id);
alter table user drop primary key;
修改主键索引：必须先删掉原索引，再新建索引。
复合索引：即一个索引包含多个列
语法：
create index idx_id_name on user(id,name);
drop index idx_id_name on user;
2.3、哪些情况需要创建索引
1、主键自动建立唯一索引。
2、频繁作为查询条件的字段应该创建索引。
3、查询中与其他表关联的字段，外键关系建立索引。
4、单值/组合索引的选择问题，组合索引性价比更高。
5、查询中排序的字段，排序字段若通过索引去访问，将大大提高排序速度。
6、查询中统计或者分组字段。
2.4、哪些情况不要创建索引
1、表记录太少。
2、经常增删改的表或者字段（提高了查询速度，同时却会降低更新表的速度）。
3、where条件里用不到的字段不创建索引。
4、过滤性不好的字段，不适合建立索引。
3、性能分析
Explain：使用Explain关键字可以模拟优化器执行SQL查询语句，从而知道MySQL是如何处理SQL语句的。进而分析查询语句或是表结构的性能瓶颈。
3.1、Explain的作用
1、查看表的读取顺序。
2、有哪些索引可以使用。
3、数据读取操作的操作类型。
4、哪些索引被实际引用。
5、表之间的引用。
6、每张表有多少行被物理扫描。
3.2、Explain字段解释
3.2.1、id
select查询的序列号，包含一组数字，表示查询中执行select子句或操作表的顺序。id的每个号码，表示一趟独立的查询，一条SQL的查询趟数越少越好。
1、如果id相同，执行顺序由上至下。
id1.jpg
2、id不同，如果是子查询，id的序号会递增，id值越大，优先级越高，越线被执行。
id2.jpg
3、id相同和不相同的同时存在，id相同的认为是一组，从上往下执行，在所有组中，id值越大，优先级越高，越先执行。
id3.jpg
3.2.2、select_type
查询的类型，主要是用于区别普通查询、联合查询、子查询等的复杂查询。
1、SIMPLE：简单的select查询，查询中不包含子查询或者UNION。
2、PRIMARY：查询中若包含任何复杂的字部分，最外层查询被标记为Primary
3、DERIVED：在FROM列表中包含的子查询被标记为DERIVED（衍生），MySQL会递归执行这些子查询，把结果放在临时表里。
type2.jpg
4、SUBQUERY：在SELECT或者WHERE列表中包含了子查询
type4.jpg
5、DEPENDENT SUBQUERY：在SELECT或者WHERE列表中包含了子查询，子查询基于外层
type5.jpg
6、UNCHACHEABLE SUBQUERY：不可缓存的子查询
7、UNION：若第二个SELECT出现在UNION之后，则被标记为UNION
8、UNION RESULT：从UNION表获取结果的SELECT
type7.jpg
3.2.3、table
显示这一行数据是关于哪张表的
3.2.4、partitions
代表分区表中的命中情况，非分区表，该项为null
3.2.5、type
type显示的是访问类型，是较为重要的一个指标，结果值从好到坏依次是：system>const>eq_ref>ref>range>index>ALL
一般来说，得保证查询至少达到range级别，最好能达到ref。
1、system：表只有一行记录（等于系统表），这是const类型的特例，平时不会出现，这个可以忽略不计。
2、const：表示通过索引一次就找到了，const用于比较primary key或者unique索引。因为只匹配一行数据，所以很快。如将主键置于where列表中，MySQL就能将该查询转换为一个常量
const.jpg
3、eq_ref：唯一性索引扫描，对于每个索引键，表中只有一条记录与之匹配，常见于主键或唯一索引扫描
4、ref：非唯一性索引扫描，返回匹配某个单独值的所有行。
5、range：只检索给定范围的行，使用一个索引来选择行。这种范围扫描索引比全表扫描要好，因为它只需要开始于索引的某一点，结束于另一点，不用扫描全部索引。
6、index：出现index是SQL使用了索引，但是没有通过索引进行过滤，一般是使用了覆盖索引或者是利用索引进行了排序分组。
index.jpg
7、all：遍历全表以找到所有的行
8、index_merge：在查询过程中需要多个索引组合使用，通常出现在有or的关键字的sql中。
index_merge.jpg
9、ref_or_null：对于某个字段，既需要关联条件，也需要null值的情况下，查询优化器会选择使用ref_or_null连接查询。
10、index_subquery：利用索引来关联子查询，不用全表扫描。
11、unique_subquery：子查询中有唯一索引。
3.2.6、possible_keys
查询涉及到的字段上若存在索引，则该索引将被列出，但不一定被实际用到。
3.2.7、key
实际使用的索引，如果为null，则没有使用索引。
3.2.8、key_len
表示索引中使用的字节数，可通过该列计算查询中使用的索引的长度。
3.2.9、ref
显示索引的哪一列被使用了，如果可能的话，是一个常数。哪些列或常量被用于查找索引列上的值。
ref.jpg
3.2.10、rows
显示MySQL认为它执行查询时必须检查的行数（越少越好）。
3.2.11、filtered
表示存储引擎返回的数据在Server层过滤后，剩下多少满足查询的记录数量的比例，是百分比。
3.2.12、extra
包含不适合在其他列中显示但十分重要的额外信息
1、Using filesort：说明MySQL会对数据使用一个外部的索引排序，而不是按照表内的索引顺序进行读取。MySQL中无法利用索引完成的排序操作称为“文件排序”。
filesort.jpg
2、Using temporary：使用了临时表保存中间结果，MySQL在对查询结果排序时使用临时表。常见于排序order by和分组查询group by。
temporary.jpg
3、Using index：表示相应的select操作中使用了覆盖索引，避免访问了表的数据行，效率不错。如果同时出现Using where，表明索引被用来执行索引键值的查找；如果没有，表明索引只是用来读取数据，而非利用索引执行查找。
4、Using where：表明使用了where过滤。
5、Using join buffer：使用了连接缓存。
buffer.jpg
6、impossible where：where子句的值总是false，不能用来获取任何元组。
4、查询优化
4.1、单表使用索引及常见索引失效
4.1.1、全值匹配
系统中经常出现的SQL语句如下：
select * from emp where emp.age = 30;
select * from emp where emp.age = 30 and deptid = 4;
select * from emp where emp.age = 30 and deptid = 4 and name='abcd';
全值匹配.jpg
4.1.2、最佳左前缀法则
如果建立了组合索引，要遵守最左前缀法则，指的是查询从索引的最左前列开始，并且不跳过索引中的列。
最左前缀法则.jpg
4.1.3、不在索引列上做任何操作
计算、函数、类型转换等操作，会导致索引失效而转向全表扫描
操作1.jpg
操作2.jpg
4.1.4、存储引擎不能使用索引中范围条件右边的列
范围条件1.jpg
范围条件2.jpg
4.1.5、MySQL在使用不等于的时候，无法使用索引会导致全表扫描
不等于.jpg
4.1.6、is not null也无法使用索引，但是is null是可以使用索引的
isNull.jpg
4.1.7、like以通配符开头，MySQL索引失效
通配符.jpg
4.1.8、字符串不加单引号索引失效
单引号.jpg
4.1.9、一般性建议
1、对于单键索引，尽量选择针对当前query过滤性更好的索引。
2、选择组合索引的时候，当前query中过滤性最好的字段在索引字段顺序中，位置越靠前越好。
3、在选择组合索引的时候，尽量选择可以能够包含当前query中的where子句中更多字段的索引。
4、在选择组合索引的时候，如果某个字段可能出现范围查询时，尽量把这个字段放在索引次序的最后面。
5、书写SQL语句时，尽量避免造成索引失效的情况。
4.2、关联查询优化
关联优化.jpg
在右表建立class索引以后，第二行的type变为了ref，所以右边时我们的关键点，一定要建立索引。
建议：
1、保证被驱动表（被关联的表）的join字段已经被索引。
2、left join时，选择小表作为驱动表，大表作为被驱动表。
3、inner join时，MySQL会自己把小结果集的表选为驱动表
4、子查询尽量不要放在被驱动表，有可能使用不到索引。
5、能够直接多表关联的尽量直接关联，不要用子查询。
4.3、子查询优化
尽量不要使用not in或者not exists
4.4、排序分组优化
4.4.1、ORDER BY子句
创建索引
create index idx_age_deptId_name on emp(age,deptId,name);
1、无过滤，不索引，order by想要用上索引，必须要有过滤条件
排序1.jpg
如果Extra字段里面没有Using filesort，就证明用上了索引。
2、顺序错，必排序，如果条件里面字段的顺序和创建索引的顺序不一致，则会出现手工排序Using filesort。
排序2.jpg
3、方向反，必排序，如果排序字段里面，一个升序，一个降序，也会出现手工排序。
排序3.jpg
4.4.2、索引的选择
当范围条件和group by或者order by的字段出现二选一时，优先观察条件字段的过滤数量，如果过滤的数据足够多，而需要排序的数据并不多时，优先把索引放在范围字段上。反之，已然。
例如：查询年龄为30岁，员工编号小于100000，按名字排序
explain select * from emp where age = 30 and empno<100000 order by name;
建立索引age_empno_name：
create index idx_age_empno_name on emp(age,empno,name);
explain一下：
排序4.jpg
Using filesort依然存在，所以name并没有用到索引，原因是empno是一个范围过滤，所以索引后面的字段不会再使用索引了。
首先删掉原来的索引
drop index idx_age_empno_name on emp;
为了去掉Using filesort，把索引建成idx_age_name
排序5.jpg
删除idx_age_name索引，选择排序上的索引
排序6.jpg
实际上，选择哪个索引，是要根据条件过滤掉的数据来选择的，如果过滤了大部分的数据，排序并不是很耗性能，选择包含过滤字段的索引。但是如果没有过滤掉大部分数据，则选择包含排序字段的索引。
4.4.3、group by关键字优化
group by使用索引的原则几乎跟order by一致，唯一区别是group by即便没有过滤条件，也可以直接使用索引。
4.5、最后使用索引的手段，覆盖索引
当无法使用索引时，比如where 后面出现不等于或者like '%abc'的情况，不要使用select *
覆盖索引.jpg
网友评论