MySQL优化分析

MySQL

##1.索引 ###1.概念：索引（Index）是帮助MySQL高效获取数据的**数据结构**。因而可以得到索引的*本质：索引是数据结构。* 简而言之就是“排好序的快速查找数据结构”。因而，索引决定两部分内容，*排序*和*查找*。

在数据之外，数据库系统还维护着满足特定查找算法的数据结构，这些数据结构以某种方式引用（指向）数据，这样就可以在这些数据结构上实现高级查找算法。这种数据结构，就是索引。

2.索引的优劣势：

优势：①类似大学图书馆建书目索引，提高数据检索的效率，降低数据库的IO成本；②通过索引列对数据进行排序，降低数据排序的成本，降低了CPU的消耗

劣势：①虽然索引大大提高了查询速度，同时却会降低更新表的速度，如对表进行INSERT、UPDATE和DELETE。因为更新表时，MySQL不仅要保存数据，还要保存一下索引文件每次更新添加了索引列的字段，都会调整因为更新所带来的键值变化后的索引信息；②实际上索引也是一张表，该表保存了主键与索引字段，并指向实体表的记录，所以索引列也是要占用空间的

3.索引的分类

单值索引：即一个索引只包含单个列，一个表可以有多个单列索引
唯一索引：索引列的值必须唯一，但允许有空值
主键索引：设定为主键后数据库会自动建立索引，innodb为聚簇索引
复合索引：即一个索引包含多个列

4.数据库慢的原理分析

数据库慢主要体现在两方面：检索慢、排序慢

检索慢：大规模io，可以用linux命令iostat -d 2 3来查看。
排序慢：排序慢导致CPU消耗，可以使用linux命令iostat -c 2 3来查看。

故而，数据库慢，要么是IO慢，要么是CPU慢。（导致数据库慢的硬件层次上就这两个因素）

建立索引一定要注意：一张表的索引最多最多建立5个。

单值索引：
create index idx_user_name on user(name);
create index idx_user_weixin on user(weixin);
唯一索引：
索引列的值必须唯一，但允许有空值。
create unique index unique_idx_user_weixin on user(weixin);
复合索引：
create index idx_user_name_age on user(name,age);

5.Explain

Explain是用来查看sql语句的执行计划的。即机器如何来解析执行我们输入的SQL语句。具体用法就是：explain sql语句。

explain sql语句之后会出现一个表，该表共有10个字段，分别为：
id,select_type,table,type,possible_keys,key,key_len,ref,rows,Extra。

id

概念：select查询的序列号，包含一组数字，表示查询中的先后顺序。
id相同：执行顺序由上至下。
id不同：按照id的大小顺序执行，即先执行id大的。如果是子查询，id的序号越大优先级越高，越先被执行。
id相同又不同：数字大的先执行，数字相同的按从上到下顺序执行。

select_type

概念：查询类型，主要用于区别普通查询、联合查询、子查询等复杂的查询。共有以下6种不同的查询类型：

(1) SIMPLE:简单的select查询，查询中不包含子查询或UNION

(2) PRIMARY：查询中若包含任何复杂的子部分，最外层查询则被标记为PRIMARY。(一般发生在子查询里面)，

(3) SUBQUERY：在select或where列表中包含了子查询。

(4) DERIVED：在from列表中包含的子查询被标记为DERIVED（衍生），MySQL会递归执行这些子查询，把结果放在临时表里。(sql语句查询的原则：小表驱动大表）

(5) UNION：如果第二个select出现在union之后，则被标记为UNION；若UNION包含在FROM子句的子查询中，外层的select将被标记为DERIVED。

(6) UNION RESULT：从UNION表中获取结果的select。

table

显示这一行数据是关于哪张表的。

type

概念：访问类型排列。显示查询使用了何种类型。从最好到最差依次是：
system>const>eq_ref>ref>range>index>ALL。
一般来说，得保证查询至少达到range级别，最好能达到ref。

（1）system:表只有一行记录（等于系统表），这是const类型的特列，平时不会出现，这个也可以忽略不计

（2）const:表示通过索引一次就找到了,const用于比较primary key或者unique索引。因为只匹配一行数据，所以很快

如将主键置于where列表中，MySQL就能将该查询转换为一个常量

（3）eq_ref:唯一性索引扫描，对于每个索引键，表中只有一条记录与之匹配。常见于主键或唯一索引扫描

（4）ref:非唯一性索引扫描，返回匹配某个单独值的所有行.

本质上也是一种索引访问，它返回所有匹配某个单独值的行，然而，
它可能会找到多个符合条件的行，所以他应该属于查找和扫描的混合体

（5）range:只检索给定范围的行,使用一个索引来选择行。key 列显示使用了哪个索引一般就是在你的where语句中出现了between、<、>、in等的查询这种范围扫描索引扫描比全表扫描要好，因为它只需要开始于索引的某一点，而结束语另一点，不用扫描全部索引。

（6）index:Full Index Scan,index与ALL区别为index类型只遍历索引树，这通常比ALL快，因为索引文件通常比数据文件小。也就是说，虽然all和index都是读全表，但是index是从索引中读取的，而all是从硬盘中读的。

（7）ALL：Full Table Scan，将遍历全表以找到匹配的行。

possible_keys

显示可能应用在这张表中的索引，一个或多个。查询涉及到的字段上若存在索引，则该索引将被列出，但不一定被查询实际使用。

打个比方：比如高德地图上规划出来的路线，可能有一条，也可能有多条，这些路线都是理论上能够达到目的地的路线，即可能有多种搜索数据的途径，但是你只能走一条路线，实际搜索的路径，也就是实际走的路线就是key。

key（用来表示有没有用到索引)

实际使用的索引。如果为NULL，则没有使用索引。
查询中若使用了覆盖索引，则该索引和查询的select字段重叠。

但是如果索引失效，或者建立了索引但是没有用上，那么key这个字段里面的内容是null。

key_len

表示索引中使用的字节数，可通过该列计算查询中使用的索引的长度。在不损失精度的情况下，长度越短越好。

key_len显示的值为索引字段的最大可能长度，并非实际使用长度，即key_len是根据表定义计算而得，不是通过表内检索出的。

决定key_len的数值的因素：数据库版本+存储引擎+定义的字段类型+是否可以null+字符集编码都会影响到ken_len的使用和计算参考值。

key_len字段能够帮你检查是否充分的利用上了索引。

ref（用来表示怎么用的索引）

显示索引的哪一列被使用了，如果可能的话，是一个常数。哪些列或常量被用于查找索引列上的值

rows

rows列显示MySQL认为它执行查询时必须检查的行数。
当然了，行数是越少越好。

Extra

包含不适合在其他列中显示但十分重要的额外信息
常见常用的一般是以下6个：

（1）Using filesort

（2）using temporary

（3）using index

（4）using where

（5）using join buffer

（6）impossible where

1.出现第一种情况，using filesort，文件类排序，出现这种情况一般是因为你写得sql的order by的顺序没有服用索引的建立顺序，没有保持一致。如果建立索引的顺序为abc，index(a,b,c),那么写sql的时候也要按abc的顺序，order by a,b,c。否则就会出现using filesort。

因为我们知道，索引就是搜索+排序，where之后order by之前是搜索，order by之后是排序。那么我们要考虑的问题就是我们能否用order by的顺序复用索引的顺序，因为：查询中排序的字段，排序字段若通过索引去访问将大大提高排序速度。

2.出现第二种情况using temporary,使了用临时表保存中间结果,MySQL在对查询结果排序时使用临时表。常见于排序 order by 和分组查询 group by。

3.出现第三中情况using index,表示相应的select操作中使用了覆盖索引(Covering Index)，避免访问了表的数据行，效率不错！
如果同时出现using where，表明索引被用来执行索引键值的查找;
如果没有同时出现using where，表明索引只是用来读取数据而非利用索引执行查找。
覆盖索引
第一种理解方式：就是select的数据列只从索引中就能够取得，不必读取数据行，MySQL可以利用索引返回select列表中的字段，而不必根据索引再起读取数据文件，话句话说查询列要被所建的索引覆盖。

第二种理解方式：索引是高效找到行的一个方法，但是一般数据库也能使用索引找到一个列的数据，因此它不必读取整个行。毕竟索引叶子节点存储了他们索引的数据，当能通过读取索引既可以得到想要的数据，那就不需要读取行了。一个索引包含了（或覆盖了）满足查询结果的数据就叫做覆盖索引。

4.using where
表明使用了where过滤

5.using join buffer
使用了连接缓存

6.impossible where
where子句的值总是false，不能用来获取任何元组

6.避免索引失效

（1）全值匹配我最爱
（2）最佳左前缀法则
如果索引了多列，要遵守最左前缀法则。指的是查询从索引的最左前列开始并且不跳过索引中的列。
（3）不要在索引列上做任何操作（计算、函数、(自动or手动)类型转换），否则会导致索引失效而转向全表扫描
即：等号左边无计算。
（4）存储引擎不能使用索引中范围条件右边的列
即：范围之后全失效
（5）尽量使用覆盖索引（只访问索引的查询（索引列和查询列一致）），不要使用select*

（6）mysql 在使用不等于(!= 或者<>)的时候无法使用索引会导致全表扫描
(7)如果建表的时候字段定义为NOT NULL，那么sql语句使用is null和is not null 都可能导致索引失效，
如果建表时候字段定义没有not null，那么sql语句使用is null 或者is not null是可以使用索引的。
is not null 也无法使用索引,但是is null是可以使用索引的
（8）like以通配符、下划线开头('%abc...','_abc...')mysql索引失效会变成全表扫描的操作。
即：like百分挂右边。（like的%不能写在两边）。但是，如果检索的时候，搜索的关键字正好覆盖了索引的话，两边都加百分号也是可以不让索引失效的。
（9）字符串不加单引号索引失效
即：varchar引号不可丢。
（10）少用or，用它来连接时会索引失效。如果实际需求中需要用到or，那就用UNION ALL来替代。比如：
select * from table where name = 'abc' or age = 13;
替换成：
select * from table where name = 'abc'
union all
select * from table where age = 13
这个时候既不影响搜索结果又可以利用索引。Tips：这里的union all不可以替换成union，因为UNION会去重，这里要的是不去重的结果。