mysql之优化-索引

一、优化思路

a.表的设计合理化（符合3NF）
b.添加适当索引（index[4种：普通索引主键索引唯一索引unique 全文索引]）
c.分表技术（水平分割，垂直分割）
d.读写[写：update/delete/add]分离
e.存储过程[模块化编程可以提高速度] ：执行操作，然后缓存结果，但是编译很费时间，直接编译耗时严重，所以可以直接编程写存储过程(分页存储/触发器)

f.对MySQL配置优化[配置最大并发数 my.ini]
g.定时的去清除不需要的数据，并且定时进行碎片整理(尤其对搜索引擎是MyISAM)

本章讨论的高性能索引策略主要属于结构优化范畴。本章的内容完全基于上文的理论基础，实际上一旦理解了索引背后的机制，那么选择高性能的策略就变成了纯粹的推理，并且可以理解这些策略背后的逻辑。

三、SQL语句优化的一般步骤

3.1 SQL语句优化的一般步骤:

① 通过 show status 命令了解各种SQL的执行频率
② 定位执行效率较低的SQL语句（重点select）
③ 通过 explain 分析低效率的SQL语句的执行情况
④ 确定问题并采取相应的优化措施

3.2 SQL语句本身的优化

问题：如果从一个大型项目中，迅速的定位执行速度慢的语句

3.2.1 查询数据库的运行状态

首先了解MySQL数据库的一些运行状态如何查询 show status

show status 语法： show [session|global] status like ‘’;
如果不写 [session|global] 表示默认是 session 指取出当前窗口的执行情况,如果想看所有(mysql启动到现在)的情况加上 global

show status like 'com_select|com_insert|com_update|com_delete';
#显示当前mysql运行时间，一共执行了多少次的查询|添加|更新|删除 连接

show status like 'uptime' #当前MySQL运行时间 

show global status like 'com_insert'; 
show status like 'connections';试图连接MySQL的连接数 

show status like 'slow_queries'; #显示慢查询次数

如何去定位慢查询
构建一个大表(400万数据)->存储过程构建

默认情况下，MySQL认为 10秒钟才是一个慢查询

修改MySQL的慢查询时间

   show variables like 'long_query_time'; #显示慢查询时间 
   set long_query_time=2;// 即修改慢查询时间为2秒

构建大表->大表中记录有要求，记录是不同才有用，否则测试效果与真实的相差很大

为了存储过程能正常执行，需要修改命令执行结束符修改一下
语法 delimiter 表示修改后的结束符

当你想使用函数时，可以指向一个dual表，这个表是亚元表，就是个空表
select rand_string(6) from dual;

这时我们如果出现一条语句执行时间超过1秒，就会被统计到call insert_emp 执行存储过程这个也会被记录
如果把慢查询的 SQL 语句记录到我们的一个日志中 ,在默认情况下，我们的MySQL不会记录慢查询，需要启动MySQL时，指定记录慢查询才可以
```
bin/mysqld.exe --safe-mode --slow-query-log mysql5.5在my.ini指定 
bin/mysqld.exe -log-slow-queries=d:/abc.log 低版本mysql5.0可以在my.ini指定 
```
先关闭MySQL 再重新启动
如果启用了慢查询日志，默认存放在 my.ini 文件中记录的位置即 datadir设置的目录
测试，可以看到在日志中就记录下我们的MySQL慢sql语句

四、优化分析工具-explain

4.1 explain 语句分析

4.1.1 explain 语句

语句告诉我们MySQL将使用怎样的执行计划来优化query

#用法
desc 或者 explain 加上你的sql
extended explain加上你的sql，然后通过show warnings可以查看实际执行的语句，这一点也是非常有用的，很多时候不同的写法经过sql分析之后实际执行的代码是一样的

id:1 查询序列号
select_type:SIMPLE 查询类型
table:dept 查询的表名
type:ALL 扫描的方式 all表示全表扫描
possible_keys:null 可能使用到的索引
key:null 实际使用的索引
key_len:null
ref:null
rows:10 该SQL语句扫描了多少行，可能得到结果数
Extra:Using where SQL语句的额外信息，比如排序方式filesort等等

4.2 explain 详解

4.2.1 id

4.2.2 select_type

primary : 子查询中最外层查询
subquery : 子查询内层第一个select，结果不依赖于外部查询
dependent subquery : 子查询内层第一个select，依赖于外部查询
union　: union语句中第二个select开始后面所有select
simple : 简单模式
union result : union中合并结果

4.2.3 table

4.2.3 type

all : 完整的表扫描通常不好
system : 表仅有一行记录(系统表)，这是const类型的特例，平时不会出现
const : 表最多有一个匹配行，如果将主键置于where列表中，MySQL就能将该查询转换为一个常量

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

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

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

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

4.2.4 possible_keys

4.2.5 key

4.2.6 key_len

4.2.7 ref

4.2.8 rows

4.2.9 extra 类型

no table : query语句中使用 from dual 或不含任何from子句

Using filesort : 说明MySQL会对数据使用一个外部的索引排序，而不是按照表内的索引顺序进行读取。MySQL中无法利用索引完成的排序操作称为“文件排序” ，其实不一定是文件排序，内部使用的是快排，当query中包含 order by 操作，而且无法利用索引完成排序

Using temporary : 某些操作必须使用临时表，常见 group by ,order by

using index：表示相应的SELECT操作中使用了覆盖索引（Covering Index），避免访问了表的数据行，效率不错。

Using where : 不用读取表中所有信息，仅通过索引就可以获取所需数据

impossible WHERE ：using index：表示相应的SELECT操作中使用了覆盖索引（Covering Index），避免访问了表的数据行，效率不错。

select tables optimized away：在没有GROUP BY子句的情况下基于索引优化MIN/MAX操作或者对于MyISAM存储引擎优化COUNT(*)操作，不必等到执行阶段再进行计算，查询执行计划生成的阶段即完成优化

distinct：优化distinct操作，在找到第一匹配的元祖后即停止找同样值的操作

注：using filesort,using temporary这两项出现时需要注意下，这两项是十分耗费性能的，在使用group by的时候，虽然没有使用order by，如果没有索引，是可能同时出现using filesort,using temporary的，因为group by就是先排序在分组，如果没有排序的需要，可以加上一个order by NULL来避免排序，这样using filesort就会去除，能提升一点性能。

explain可以帮助我们在不真正执行某个SQL语句时，就知道MySQL怎样执行，利于我们去分析SQL指令

查看索引的使用情况
show status like ‘Handler_read%’;
handler_read_key:这个值越高越好，代表使用索引查询到的次数
handler_read_rnd_next:这个值越高，说明查询低效

4.3 提高性能的特性

4.3.1、索引覆盖(covering index)：

需要查询的数据在索引上都可以查到不需要回表 EXTRA列显示using index

4.3.2、ICP特性(Index Condition Pushdown)

本来index仅仅是data access的一种访问模式，存数引擎通过索引回表获取的数据会传递到MySQL server层进行where条件过滤,5.6版本开始当ICP打开时，如果部分where条件能使用索引的字段，MySQL server会把这部分下推到引擎层，可以利用index过滤的where条件在存储引擎层进行数据过滤。EXTRA显示using index condition。需要了解mysql的架构图分为server和存储引擎层

4.3.3、索引合并(index merge)

对多个索引分别进行条件扫描，然后将它们各自的结果进行合并(intersect/union)。一般用OR会用到，如果是AND条件，考虑建立复合索引。EXPLAIN显示的索引类型会显示index_merge，EXTRA会显示具体的合并算法和用到的索引

四、优化分析工具-profiling

4.3 profiling 语句分析

Profiling是从 mysql5.0.3版本以后才开放的。

4.3.1 profiling 的作用

启动profile之后，所有查询包括错误的语句都会记录在内。

关闭会话或者set profiling=0 就关闭了。（如果将profiling_history_size参数设置为0，同样具有关闭MySQL的profiling效果。）

此工具可用来查询SQL执行状态，System lock和Table lock 花多少时间等等，对定位一条语句的I/O消耗和CPU消耗非常重要。(SQL 语句执行所消耗的最大两部分资源就是IO和CPU)

–在mysql5.7之后，profile信息将逐渐被废弃，mysql推荐使用performance schema

#查看profiling是否开启
show variables like '%profili%'
# profiling on #分析是否开启
# profiling_history_size 15 #记录历史条数

4.3.2简易流程大概如下：

set profiling=1;  				//打开分析

run your sql1;
run your sql2;
show profiles;					//查看sql1,sql2的语句分析

show profile for query 1;		//查看sql1的具体分析
show profile ALL for query 1;	//查看sql1相关的所有分析【主要看i/o与cpu,下边分析中有各项意义介绍】
set profiling=0;  				//关闭分析

4.3.3 含义分析

上图中横向栏意义

“Status”: “query end”, 状态
“Duration”: “1.751142”, 持续时间
“CPU_user”: “0.008999”, cpu用户
“CPU_system”: “0.003999”, cpu系统
“Context_voluntary”: “98”, 上下文主动切换
“Context_involuntary”: “0”, 上下文被动切换
“Block_ops_in”: “8”, 阻塞的输入操作
“Block_ops_out”: “32”, 阻塞的输出操作
“Messages_sent”: “0”, 消息发出
“Messages_received”: “0”, 消息接受
“Page_faults_major”: “0”, 主分页错误
“Page_faults_minor”: “0”, 次分页错误
“Swaps”: “0”, 交换次数
“Source_function”: “mysql_execute_command”, 源功能
“Source_file”: “sql_parse.cc”, 源文件
“Source_line”: “4465” 源代码行

上图中纵向栏意义

starting：开始
checking permissions：检查权限
Opening tables：打开表
init ：初始化
System lock ：系统锁
optimizing ：优化
statistics ：统计
preparing ：准备
executing ：执行
Sending data ：发送数据
Sorting result ：排序
end ：结束
query end ：查询结束
closing tables ：关闭表／去除TMP 表
freeing items ：释放物品
cleaning up ：清理

一般情况下，常用以下语句也就够了：

mysql->SHOW profile CPU,BLOCK IO io FOR query 2;

五、索引优化处理

5.1 添加

添加索引，四种索引主键索引唯一索引全文索引普通索引

5.1.1 主键索引的添加

当一张表中，把某个列设为主键的时候，则该列就是主键索引
如果你创建表时，没有指定主键索引，也可以在创建表后添加索引
语句：alter table 表名 add primary key (列名);

5.1.2 普通索引

一般来说，普通索引的创建，是先创建表，然后再创建索引
语句：create index 索引名 on 表名 (列名);

5.1.3 全文索引

全文索引：主要是针对文件，文本的索引，比如文章，全文索引针对MyISAM有用
如何使用全文索引

错误用法：
select * from articles where body like ‘%mysql%’;
这种方法是不会用到全文索引的
正确用法：
// title,body是全文索引，匹配database的句子
select * from articles where match(title,body) against(‘database’);
说明：

1.在MySQL中fulltext索引只针对myISAM生效
2.针对英文生效，对中文需要sphinx(coreseek)技术处理
3.使用方法是match(字段名) against(‘关键字’)
4.全文索引有一个停止词概念：
因为在一个文本中，创建索引是一个无穷大的数，因此，对一些常用词和字符，
就不会创建，这些词，称为停止词。

5.1.4 唯一索引

当表的某列被指定为unique约束，这列就是一个唯一索引
唯一索引的列可以为null，并且可以有多个
在创建表后，再去创建唯一索引
创建语法：create unique index 索引名 on 表名 (列名);

5.2 查询

① desc 表名该方法缺点：不能够显示索引的名字
② show index(es) from 表名
③ show keys from 表名

5.3 删除

语法：alter table 表名 drop index 索引名;
主键索引删除：alter table 表名 drop primary key;

5.4 修改

先删除，再重新创建

六、mysql索引使用策略及优化

6.1索引基数：

基数是数据列所包含的不同值的数量。例如，某个数据列包含值1、3、7、4、7、3，那么它的基数就是4。索引的基数相对于数据表行数较高（也就是说，列中包含很多不同的值，重复的值很少）的时候，它的工作效果最好。如果某数据列含有很多不同的年龄，索引会很快地分辨数据行。如果某个数据列用于记录性别（只有"M"和"F"两种值），那么索引的用处就不大。如果值出现的几率几乎相等，那么无论搜索哪个值都可能得到一半的数据行。在这些情况下，最好根本不要使用索引，因为查询优化器发现某个值出现在表的数据行中的百分比很高的时候，它一般会忽略索引，进行全表扫描。惯用的百分比界线是"30%"。

6.2 索引失效原因

对索引列运算，运算包括（+、-、*、/、！、<>、%、like’%_’（%放在前面）、
类型错误，如字段类型为varchar，where条件用number。
对索引应用内部函数，这种情况下应该建立基于函数的索引
如select * from template t where ROUND(t.logicdb_id) = 1
此时应该建ROUND(t.logicdb_id)为索引，mysql8.0开始支持函数索引，5.7可以通过虚拟列的方式来支持，之前只能新建一个ROUND(t.logicdb_id)列然后去维护
如果条件有or，即使其中有条件带索引也不会使用（这也是为什么建议少使用or的原因），如果想使用or，又想索引有效，只能将or条件中的每个列加上索引
如果列类型是字符串，那一定要在条件中数据使用引号，否则不使用索引；
B-tree索引 is null不会走,is not null会走,位图索引 is null,is not null 都会走
组合索引遵循最左原则

6.3 索引的建立

最重要的肯定是根据业务经常查询的语句
尽量选择区分度高的列作为索引，区分度的公式是 COUNT(DISTINCT col) / COUNT(*)。表示字段不重复的比率，比率越大我们扫描的记录数就越少
如果业务中唯一特性最好建立唯一键，一方面可以保证数据的正确性，另一方面索引的效率能大大提高

6.4 联合索引及最左前缀原理

6.4.1 联合索引（复合索引）

首先介绍一下联合索引。联合索引其实很简单，相对于一般索引只有一个字段，联合索引可以为多个字段创建一个索引。它的原理也很简单，比如，我们在（a,b,c）字段上创建一个联合索引，则索引记录会首先按照A字段排序，然后再按照B字段排序然后再是C字段，因此，联合索引的特点就是：

第一个字段一定是有序的
当第一个字段值相等的时候，第二个字段又是有序的，比如下表中当A=2时所有B的值是有序排列的，依次类推，当同一个B值得所有C字段是有序排列的

| A | B | C |
| 1 | 2 | 3 |
| 1 | 4 | 2 |
| 1 | 1 | 4 |
| 2 | 3 | 5 |
| 2 | 4 | 4 |
| 2 | 4 | 6 |
| 2 | 5 | 5 |

其实联合索引的查找就跟查字典是一样的，先根据第一个字母查，然后再根据第二个字母查，或者只根据第一个字母查，但是不能跳过第一个字母从第二个字母开始查。这就是所谓的最左前缀原理。

6.4.2 最左前缀原理

我们再来详细介绍一下联合索引的查询。还是上面例子，我们在（a,b,c）字段上建了一个联合索引，所以这个索引是先按a 再按b 再按c进行排列的，所以：

以下的查询方式都可以用到索引

select * from table where a=1；
select * from table where a=1 and b=2；
select * from table where a=1 and b=2 and c=3；

上面三个查询按照 （a ）, （a，b ）,（a，b，c ）的顺序都可以利用到索引，这就是最左前缀匹配。

如果查询语句是：

select * from table where a=1 and c=3； 那么只会用到索引a。

如果查询语句是：

select * from table where b=2 and c=3； 因为没有用到最左前缀a，所以这个查询是用户到索引的。

如果用到了最左前缀，但是顺序颠倒会用到索引码？

比如：

select * from table where b=2 and a=1；
select * from table where b=2 and a=1 and c=3；

如果用到了最左前缀而只是颠倒了顺序，也是可以用到索引的，因为mysql查询优化器会判断纠正这条sql语句该以什么样的顺序执行效率最高，最后才生成真正的执行计划。但我们还是最好按照索引顺序来查询，这样查询优化器就不用重新编译了。

6.4.3 前缀索引

除了联合索引之外，对mysql来说其实还有一种前缀索引。前缀索引就是用列的前缀代替整个列作为索引key，当前缀长度合适时，可以做到既使得前缀索引的选择性接近全列索引，同时因为索引key变短而减少了索引文件的大小和维护开销。

一般来说以下情况可以使用前缀索引：

字符串列(varchar,char,text等)，需要进行全字段匹配或者前匹配。也就是=‘xxx’ 或者 like ‘xxx%’
字符串本身可能比较长，而且前几个字符就开始不相同。比如我们对中国人的姓名使用前缀索引就没啥意义，因为中国人名字都很短，另外对收件地址使用前缀索引也不是很实用，因为一方面收件地址一般都是以XX省开头，也就是说前几个字符都是差不多的，而且收件地址进行检索一般都是like ’%xxx%’，不会用到前匹配。相反对外国人的姓名可以使用前缀索引，因为其字符较长，而且前几个字符的选择性比较高。同样电子邮件也是一个可以使用前缀索引的字段。
前一半字符的索引选择性就已经接近于全字段的索引选择性。如果整个字段的长度为20，索引选择性为0.9，而我们对前10个字符建立前缀索引其选择性也只有0.5，那么我们需要继续加大前缀字符的长度，但是这个时候前缀索引的优势已经不明显，没有太大的建前缀索引的必要了。

一些文章中也提到：

MySQL 前缀索引能有效减小索引文件的大小，提高索引的速度。但是前缀索引也有它的坏处：MySQL 不能在 ORDER BY 或 GROUP BY 中使用前缀索引，也不能把它们用作覆盖索引(Covering Index)。

6.5 索引优化策略

最左前缀匹配原则，上面讲到了
主键外检一定要建索引
对 where,on,group by,order by 中出现的列使用索引
尽量选择区分度高的列作为索引,区分度的公式是count(distinct col)/count(*)，表示字段不重复的比例，比例越大我们扫描的记录数越少，唯一键的区分度是1，而一些状态、性别字段可能在大数据面前区分度就是0
对较小的数据列使用索引,这样会使索引文件更小,同时内存中也可以装载更多的索引键
索引列不能参与计算，保持列“干净”，比如from_unixtime(create_time) = ’2014-05-29’就不能使用到索引，原因很简单，b+树中存的都是数据表中的字段值，但进行检索时，需要把所有元素都应用函数才能比较，显然成本太大。所以语句应该写成create_time = unix_timestamp(’2014-05-29’);
为较长的字符串使用前缀索引
尽量的扩展索引，不要新建索引。比如表中已经有a的索引，现在要加(a,b)的索引，那么只需要修改原来的索引即可
不要过多创建索引, 权衡索引个数与DML之间关系，DML也就是插入、删除数据操作。这里需要权衡一个问题，建立索引的目的是为了提高查询效率的，但建立的索引过多，会影响插入、删除数据的速度，因为我们修改的表数据，索引也需要进行调整重建
对于like查询，”%”不要放在前面。
SELECT * FROMhoudunwangWHEREunameLIKE'后盾%' -- 走索引
SELECT * FROMhoudunwangWHEREunameLIKE "%后盾%" -- 不走索引
查询where条件数据类型不匹配也无法使用索引
字符串与数字比较不使用索引;
CREATE TABLEa(achar(10));
EXPLAIN SELECT * FROMaWHEREa="1" – 走索引
EXPLAIN SELECT * FROM a WHERE a=1 – 不走索引
正则表达式不使用索引,这应该很好理解,所以为什么在SQL中很难看到regexp关键字的原因

6.6 查看索引

mysql> show index from tblname;
mysql> show keys from tblname;
· Table
表的名称。
· Non_unique
如果索引不能包括重复词，则为0。如果可以，则为1。
· Key_name
索引的名称。
· Seq_in_index
索引中的列序列号，从1开始。
· Column_name
列名称。
· Collation
列以什么方式存储在索引中。在MySQL中，有值‘A’（升序）或NULL（无分类）。
· Cardinality
索引中唯一值的数目的估计值。通过运行ANALYZE TABLE或myisamchk -a可以更新。基数根据被存储为整数的统计数据来计数，所以即使对于小型表，该值也没有必要是精确的。基数越大，当进行联合时，MySQL使用该索引的机会就越大。
· Sub_part
如果列只是被部分地编入索引，则为被编入索引的字符的数目。如果整列被编入索引，则为NULL。
· Packed
指示关键字如何被压缩。如果没有被压缩，则为NULL。
· Null
如果列含有NULL，则含有YES。如果没有，则该列含有NO。
· Index_type
用过的索引方法（BTREE, FULLTEXT, HASH, RTREE）。
· Comment

关于网站的图片和视频的存放：
我们的数据表中，一般只是存放图片或者视频的路径，真正的资源是放在文件系统上的，往往会配合独立的服务器

七、索引注意事项：

7.1 索引占用磁盘空间

对dml(insert/update/delete)语句效率有影响

7.2 在哪些列上适合添加索引？

较频繁的作为查询条件字段创建索引
例如 select * from emp where empno=1;
唯一性太差的字段不适合单独创建索引，即使频繁作为查询条件
例如 select * from emp where sex=‘男’;
更新非常频繁的字段不适合创建索引
例如 select * from emp where logincount=1;
不会出现在where子句中字段不该创建索引

7.3 索引长度：

varvhar等字段建立索引长度计算语句：

select count(distinct left(test,5))/count(*) from table; #越趋近1越好

7.4 总结

满足以下条件的字段，才能创建索引

a.肯定在where条件中经常使用的
b.该字段的内容不是唯一的几个值
c.字段内容变化不能太频繁

7.5 使用索引的注意事项

alter table dept add index myind (dname,loc); // dname就是左边的列，loc是右边的列

7.5.1 下列情况有可能使用到索引

a.对于创建的多列索引，只要查询条件使用了最左边的列，索引一般就会被使用
explain select * from dept where dname=‘aaa’;
b.对于使用like的查询，查询条件如果是’%aaa’则不会使用到索引，'aaa%'会使用到索引

7.5.2 下列情况不会使用索引

a.如果条件中有or，即使其中有条件带索引也不会使用
换言之，就是要求使用的所有字段都创建索引，建议：尽量避免使用or关键字
b.对于多列索引，不是使用的第一部分，则不会使用索引
explain select * from dept where loc=‘aaa’;// 多列索引时，loc为右边列，索引不会使用到
c.like查询是以%开头
如果一定要使用，则使用全文索引去查询
d.如果列类型是字符串，那一定要在条件中将数据使用引号引起来，否则不使用索引
e.如果MySQL估计使用全表扫描要比使用索引块，则不使用索引

explain select * from dept where loc=‘aaa’;

七、SQL语句的小技巧

① 优化 group by 语句
默认情况下，MySQL对所有的group by col1,col2 进行排序，这与在查询中指定 order by col1,col2 类似
如果查询中包括 group by 但用户想尽量避免排序结果的消耗，则可以使用 order by null 禁止排序

② 有些情况下，可以使用连接来替代子查询
因为使用 join MySQL不需要在内存中创建临时表

③ 如果想要在含有 or 的查询语句中利用索引，则 or 之间的每个条件列都必须用到索引，
如果没有索引，则应该考虑增加索引。

使用慢查询日志去发现慢查询。

使用执行计划去判断查询是否正常运行。

总是去测试你的查询看看是否他们运行在最佳状态下 –久而久之性能总会变化。

避免在整个表上使用count(*),它可能锁住整张表。

使查询保持一致以便后续相似的查询可以使用查询缓存。

在适当的情形下使用GROUP BY而不是DISTINCT。

在WHERE, GROUP BY和ORDER BY子句中使用有索引的列。

保持索引简单,不在多个索引中包含同一个列。

有时候MySQL会使用错误的索引,对于这种情况使用USE INDEX。

检查使用SQL_MODE=STRICT的问题。

对于记录数小于5的索引字段，在UNION的时候使用LIMIT不是是用OR.

为了避免在更新前SELECT，使用INSERT ON DUPLICATE KEY或者INSERT IGNORE ,不要用UPDATE去实现。

不要使用 MAX,使用索引字段和ORDER BY子句。

避免使用ORDER BY RAND().

LIMIT M，N实际上可以减缓查询在某些情况下，有节制地使用。

在WHERE子句中使用UNION代替子查询。

对于UPDATES（更新），使用 SHARE MODE（共享模式），以防止独占锁。

在重新启动的MySQL，记得来温暖你的数据库，以确保您的数据在内存和查询速度快。

使用DROP TABLE，CREATE TABLE DELETE FROM从表中删除所有数据。

最小化的数据在查询你需要的数据，使用*消耗大量的时间。

考虑持久连接，而不是多个连接，以减少开销。

基准查询，包括使用服务器上的负载，有时一个简单的查询可以影响其他查询。

当负载增加您的服务器上，使用SHOW PROCESSLIST查看慢的和有问题的查询。

在开发环境中产生的镜像数据中测试的所有可疑的查询。

八、SQL语句总结

8.1 常用的但容易忘的：

1、如果有主键或者唯一键冲突则不插入：insert ignore into
2、如果有主键或者唯一键冲突则更新,注意这个会影响自增的增量：INSERT INTO room_remarks(room_id,room_remarks) VALUE(1,“sdf”) ON DUPLICATE KEY UPDATE room_remarks=“234”
3、如果有就用新的替代，values如果不包含自增列，自增列的值会变化：REPLACE INTO room_remarks(room_id,room_remarks) VALUE(1,“sdf”)
4、备份表：CREATE TABLE user_info SELECT * FROM user_info
5、复制表结构：CREATE TABLE user_v2 LIKE user
6、从查询语句中导入：INSERT INTO user_v2 SELECT * FROM user或者INSERT INTO user_v2(id,num) SELECT id,num FROM user
7、连表更新：UPDATE user a, room b SET a.num=a.num+1 WHERE a.room_id=b.id
8、连表删除：DELETE user FROM user,black WHERE user.id=black.id

8.2 锁相关(作为了解，很少用)

1、共享锁： select id from tb_test where id = 1 lock in share mode;

2、排它锁： select id from tb_test where id = 1 for update

8.3 优化时用到：

1、强制使用某个索引： select * from table force index(idx_user) limit 2;

2、禁止使用某个索引： select * from table ignore index(idx_user) limit 2;

3、禁用缓存(在测试时去除缓存的影响)： select SQL_NO_CACHE from table limit 2;

8.4 查看状态

1、查看字符集 SHOW VARIABLES LIKE ‘character_set%’;

2、查看排序规则 SHOW VARIABLES LIKE ‘collation%’;

8.5 SQL编写注意

1、where语句的解析顺序是从右到左，条件尽量放where不要放having

2、采用延迟关联(deferred join)技术优化超多分页场景，比如limit 10000,10,延迟关联可以避免回表

3、distinct语句非常损耗性能，可以通过group by来优化

4、连表尽量不要超过三个表

九、踩坑

1、如果有自增列，truncate语句会把自增列的基数重置为0，有些场景用自增列作为业务上的id需要十分重视

2、聚合函数会自动滤空，比如a列的类型是int且全部是NULL，则SUM(a)返回的是NULL而不是0

3、mysql判断null相等不能用“a=null”,这个结果永远为UnKnown，where和having中,UnKnown永远被视为false，check约束中，UnKnown就会视为true来处理。所以要用“a is null”处理

十、千万大表在线修改

mysql在表数据量很大的时候，如果修改表结构会导致锁表，业务请求被阻塞。mysql在5.6之后引入了在线更新，但是在某些情况下还是会锁表，所以一般都采用pt工具( Percona Toolkit)

如对表添加索引：

如下：

pt-online-schema-change --user='root' --host='localhost' --ask-pass --alter "add index idx_user_id(room_id,create_time)" 
D=fission_show_room_v2,t=room_favorite_info --execute

十一、慢查询日志

有时候如果线上请求超时，应该去关注下慢查询日志，慢查询的分析很简单，先找到慢查询日志文件的位置，然后利用mysqldumpslow去分析。查询慢查询日志信息可以直接通过执行sql命令查看相关变量，常用的sql如下：

-- 查看慢查询配置
-- slow_query_log  慢查询日志是否开启
-- slow_query_log_file 的值是记录的慢查询日志到文件中
-- long_query_time 指定了慢查询的阈值
-- log_queries_not_using_indexes 是否记录所有没有利用索引的查询
SHOW VARIABLES LIKE '%quer%';

-- 查看慢查询是日志还是表的形式
SHOW VARIABLES LIKE 'log_output'

-- 查看慢查询的数量

mysqldumpslow的工具十分简单，我主要用到的是参数如下：

-t:限制输出的行数，我一般取前十条就够了
-s:根据什么来排序默认是平均查询时间at，我还经常用到c查询次数，因为查询次数很频繁但是时间不高也是有必要优化的，还有t查询时间，查看那个语句特别卡。
-v:输出详细信息

例子：mysqldumpslow -v -s t -t 10 mysql_slow.log.2018-11-20-0500

十二、查看sql进程和杀死进程

如果你执行了一个sql的操作，但是迟迟没有返回，你可以通过查询进程列表看看他的实际执行状况，如果该sql十分耗时，为了避免影响线上可以用kill命令杀死进程，通过查看进程列表也能直观的看下当前sql的执行状态，如果当前数据库负载很高，在进程列表可能会出现，大量的进程夯住，执行时间很长。命令如下：

--查看进程列表
SHOW PROCESSLIST;
--杀死某个进程
kill 183665

十三、一些数据库性能的思考

在对公司慢查询日志做优化的时候，很多时候可能是忘了建索引，像这种问题很容易解决，加个索引就行了。但是有两种情况就不是简单能加索引能解决了：

1、业务代码循环读数据库： 考虑这样一个场景，获取用户粉丝列表信息加入分页是十个其实像这样的sql是十分简单的，通过连表查询性能也很高，但是有时候，很多开发采用了取出一串id，然后循环读每个id的信息，这样如果id很多对数据库的压力是很大的，而且性能也很低

**2、统计sql：**很多时候，业务上都会有排行榜这种，发现公司有很多地方直接采用数据库做计算，在对一些大表的做聚合运算的时候，经常超过五秒，这些sql一般很长而且很难优化，像这种场景，如果业务允许（比如一致性要求不高或者是隔一段时间才统计的），可以专门在从库里面做统计。另外我建议还是采用redis缓存来处理这种业务

**3、超大分页：**在慢查询日志中发现了一些超大分页的慢查询如limit 40000,1000，因为mysql的分页是在server层做的，可以采用延迟关联在减少回表。但是看了相关的业务代码正常的业务逻辑是不会出现这样的请求的，所以很有可能是有恶意用户在刷接口，所以最好在开发的时候也对接口加上校验拦截这些恶意请求。

posted @ 2019-05-10 11:58 南山道士阅读(108) 评论(0) 编辑收藏举报

刷新页面返回顶部

南山道士