数据库性能问题-索引

一、现象

　　tps很低，响应时间很长，数据库服务器CPU很高（接近100%），应用服务器负载比较低

 

二、开启慢查询配置

1、在mysql配置文件/etc/my.cnf中增加：

　　slow_query_log=1

       long_query_time=0.1

Slow_query_log 这是一个布尔型变量，默认为真。没有这变量，数据库不会打印慢查询的日志。

long_query_time=0.1：打印查询时间超过100ms的日志

备注：配置完成后，重新启动mysql服务才能生效，默认情况下，慢查询日志生成在/var/lib/mysql 目录下，日志名称为{hostname}_slow-query.log

 

2、查看慢查询是否开启：

2.1 打开数据库命令列界面：

 

 2.2 执行命令show variables like '%slow_query_log%'

 3、慢查询日志分析

在/usr/bin 目录下，使用 mysql 自带命令 mysqldumpslow ，

mysqldumpslow –s at -t 10 mycentos-slow.log，显示耗时最长的10条SQL语句的执行信息

-s：排序的意思

at：average time，平均响应时间

-t：top，即为返回前面多少条数据

以Count: 3979  Time=1.14s (4551s)  Lock=0.00s (2s)  Rows=1.0 (3979)为例：

Count:3979  该 SQL 总共执行 3979 次

Time = 1.14 (4551s) 平均每次执行该 SQL 耗时 1.14 秒，总共耗时 3979（次）*1.14（秒）=4551秒

Lock=0.00s(2s) lock 时间 2秒

Rows =1.0(3979) 每次执行 SQL 影响数据库表中的 1 行记录，总共影响 1（行）*3979（次）=3979 行记录

4、执行计划：

4.1 把上一步骤获取的sql语句复制到Navicat查询界面

4.2 在sql语句前加上explain，可以分析这条sql语句的执行情况

4.3 Type列可能的值：

　　Const：表中只有一个匹配行，用到primary key或unique key

　　Eq_ref：唯一性索引扫描，key的所有部分被连接联接查询使用，且key是unique或primary key

　　ref：非唯一性索引扫描，或只使用了联合索引的最左前缀

　　Range：索引范围扫描，在索引列上进行给定范围内的检索，如between，in（1,100）

　　Index：遍历索引...

　　All：全表扫描

从上到下，性能越来越差

 

三、增加索引：

1、设计表-->索引

2、索引名：idx_字段名

3、索引类型，一般选择UNIQUE或NORMAL，如果查询结果只有一个，选UNIQUE；如果查询结果有多个，选NORMAL

4、索引方法：一般选BTREE

5、一般表中的索引不能超过4个，因为索引会占用磁盘空间，而且索引会引起更新数据的性能变差

 

四、联合索引

联合索引：一个索引同时作用于多个字段

 

联合索引最左原则：

联合索引检索数据时，会从最左边开始匹配（忽略sql字段顺序），如果匹配不到，就不使用索引

如：User表有联合索引 my_index(A字段，B字段，C字段)

 

 五、分析

数据库服务器CPU高，一般都是因为SQL执行效率低导致的，可能有三方面原因：

1、数据库表缺少必要的索引；

2、索引不生效

3、SQL不够优化