首页 / MYSQL / MYSQL 索引 性能优化
MYSQL 索引 性能优化
内容导读
互联网集市收集整理的这篇技术教程文章主要介绍了MYSQL 索引 性能优化，小编现在分享给大家，供广大互联网技能从业者学习和参考。文章包含10086字，纯文字阅读大概需要15分钟。
内容图文
Mysql常见瓶颈
查询语句写的烂、索引失效、关联查询太多join、服务器调优及各个参数设定（缓冲线程数等）
explain用法和结果的含义
explain select t.id,t.loan_order_no,t.loan_amt,t.success_time as loan_success_time from t_ssj_loan_order t where t.state=1 and t.success_time < '2019-07-16'
and t.success_time > '2019-07-14' AND t.loan_status =2 and not EXISTS (select 1 from t_ssj_repay_order r where r.state=1 and t.loan_order_no = r.loan_order_no
and r.repay_status=2 and r.repay_type =3) and id > 1 LIMIT 10;
t_ssj_loan_order：
t_ssj_repay_order：
id：查询中执行select字句或者操作表的顺序，id相同执行顺序由上至下，id不同 id值越大优先级越高越先被执行
select_type: SIMPLE:简单SELECT(不使用UNION或子查询) PRIMARY:最外面的SELECT SUBQUERY:子查询中的第一个SELECT DERIVED:导出表的SELECT(FROM子句的子查询) UNION:UNION中的第二个或后面的SELECT语句 UNION RESULT:UNION 的结果
table: 输出的行所引用的表
type：system:表仅有一行(=系统表)。这是const联接类型的一个特例。const:表最多有一个匹配行,它将在查询开始时被读取。因为仅有一行,在这行的列值可被优化器剩余部分认为是常数。const表很快,因为它们只读取一次! eq_ref:对于每个来自于前面的表的行组合,从该表中读取一行。这可能是最好的联接类型,除了const类型。ref:对于每个来自于前面的表的行组合,所有有匹配索引值的行将从这张表中读取。range:只检索给定范围的行,使用一个索引来选择行。
index:该联接类型与ALL相同,除了只有索引树被扫描。这通常比ALL快,因为索引文件通常比数据 ALL:对于每个来自于先前的表的行组合,进行完整的表扫描
possible_keys：MySQL能使用哪个索引在该表中找到行
key：显示MySQL实际决定使用的键(索引)。如果没有选择索引,键是NULL
key_len：显示MySQL决定使用的键长度。如果键是NULL,则长度为NULL
ref：显示使用哪个列或常数与key一起从表中选择行
rows：显示MySQL认为它执行查询时必须检查的行数。多行之间的数据相乘可以估算要处理的行数。
Extra：Using where:WHERE 子句用于限制哪一个行匹配下一个表或发送到客户。 Using index:从只使用索引树中的信息而不需要进一步搜索读取实际的行来检索表中的列信息。Using filesort:MySQL需要额外的一次传递,以找出如何按排序顺序检索行。Using temporary:为了解决查询,MySQL需要创建一个临时表来容纳结果。
需要建立索引：
1、主键自动建立唯一索引
2、频繁作为查询条件的字段应该建立索引 如：customerId、loanOrderNo
3、查询中与其他表关联的字段建立索引 如：customerId、loanOrderNo
4、频繁更新的字段不适合建立索引（不单单更新数据还会更新索引增加了Io）
5、where里面用不到的字段不建立索引
7、查询中的排序字段字段若通过索引去访问将大大提升排序速度（即覆盖索引）
8、查询中统计或者分组字段
不需要建立索引：
1、表记录少
2、数据重复且分布平均的字段 如：status（索引越是唯一查询速度越快 索引越高效）
索引中常见问题分析
现在以随手记相关表为例分析索引在使用中的常见问题：
建立复合索引：KEY `idx_name_idno_ph_card` (`customer_name`,`id_no`,`bind_phone`,`card_no`) USING BTREE（注意索引字段顺序）
索引建立注意点：
1、 复合索引遵循索引顺序，中间不能跳过
n 当查询列和索引列字段相同且排序次序和索引字段次序相同时命中索引（效率最高），否则文件排序
explain select customer_name,id_no,bind_phone,card_no from t_ssj_sign
ORDER BY customer_name,id_no,bind_phone,card_no;
n 当查询列和索引列字段相同且排序次序和索引字段次序不相同时 文件排序
n 顺序使用复合索引中的前两个字段，顺序几个有效几个
select * from t_ssj_sign
where customer_name = '张飞'
and id_no= 'bWvZXJDvIBNesaYx4cjky1tCXoeKk2aQnjOmLrIepMI';
n 非顺序使用复合索引中的字段(使用了三个却只命中两个)，顺序几个有效几个
select * from t_ssj_sign
where customer_name = '张飞'
and id_no= 'bWvZXJDvIBNesaYx4cjky1tCXoeKk2aQnjOmLrIepMI'
and card_no = '6222081510001755903';
2、索引上列上不能做任何操作（计算、函数、类型转换）会导致索引失效转而全表扫面
explain select * from t_ssj_loan_order t where left(t.loan_order_no,3) ='181';
3、范围右边的索引会失效
explain select * from t_ssj_sign
where customer_name = '张飞'
and id_no> 'bWvZXJDvIBNesaYx4cjky1tCXoeKk2aQnjOmLrIepMI'
and bind_phone='13243211234'
4、尽量使用覆盖索引（索引列和查询列一致）减少select*（不论出于覆盖索引考虑还是性能考虑）
n 查询列和索引列不相同时
select * from t_ssj_sign
where customer_name = '张飞'
and id_no= 'bWvZXJDvIBNesaYx4cjky1tCXoeKk2aQnjOmLrIepMI'
and bind_phone='13243211234'
and card_no = '6222081510001755903';
n 查询列和索引列相同时 直接访问索引（效率最高，比使用*效率高）
select customer_name,id_no,bind_phone,card_no from t_ssj_sign where customer_name = '张飞'
and id_no='bWvZXJDvIBNesaYx4cjky1tCXoeKk2aQnjOmLrIepMI'
and bind_phone='13243211234'
and card_no = '6222081510001755903';
5、！= 和 <> 无法使用索引会导致全表扫描
select * from t_ssj_loan_order t where t.loan_order_no !='18122657915176190131';
6、is null is not null 也无法使用索引
explain select * from t_ssj_loan_order t where t.ssj_order is NOT NULL;
7、like以通配符开头的索引会导致全表扫描 （没有利用索引）
explain select * from t_ssj_loan_order t where t.loan_order_no like '%18122657915176190131%';
如果业务逻辑允许可改为：
explain select * from t_ssj_loan_order t where t.loan_order_no like '18122657915176190131%';
也可以采用覆盖索引的方式，如果查询出的字段不满足业务可以把这个业务拆分两次执行。
8、字符串不加单引号索引失效
explain select * from t_ssj_loan_order t where t.loan_order_no =18122657915176190131;
查询优化：小表驱动大表（连接耗时）
Exist:左边数据少，in：右边数据少
explain select * FROM t_ssj_repay_order t where t.loan_order_no in(select loan_order_no from t_ssj_loan_order o where o.create_time>'2019-01-01');
explain select * FROM t_ssj_repay_order t where EXISTS (select 1 from t_ssj_loan_order o where o.loan_order_no=t.loan_order_no and o.create_time>'2019-01-01');
对数据进行更新时如果字符串条件未带单引号或双引号时会导致锁全表（mysql底层做了类型转换）
登陆mysql: .\mysql.exe -u root –p
事务手动提交：set autocommit=0;
use test;
创建测试表：
CREATE TABLE `t_cust` (
`id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '主键',
`cid` bigint(20) NOT NULL COMMENT '客户ID',
`no` varchar(50) COLLATE utf8_bin NOT NULL COMMENT '客户编号',
PRIMARY KEY (`id`),
KEY `idx_cid` (`cid`),
KEY `idx_no` (`no`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin COMMENT='客户表';
录入测试数据：
insert into t_cust values(1,11,'001'),(2,22,'002'),(3,33,'003');
两个窗口：默认行锁 分别更新两条记录（字符串字段字符串格式）没有锁表
update t_cust set cid=1 where no='001';
update t_cust set cid=2 where no='002';
两个窗口：默认行锁 分别更新两条记录（字符串字段非字符串格式）锁表
update t_cust set cid=222 where no=002;
update t_cust set cid=111 where no='001';
Show open tables;
show OPEN TABLES;
mysql日志相关分析
查询是否锁表：show OPEN TABLES where In_use > 0;
查询到相对应的进程：show processlist;
查看正在锁的事务：SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCKS;
查看等待锁的事务：SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCK_WAITS;
慢查询日志：show variables like ‘%slow_query_log%’
set global slow_query_log=1 开启了慢查询日志只对当前数据库生效
也可以修改my.cnf 假如运行时间大于long_query_time会被记录下来
Select sleep(5)
日志分析工具：Mysqldumpslow:mysqldumpslow –s –r –t 10 /var /lib/mysql/.log 返回记录数最多的十条记录
还可以借助Show profiles:mysql 执行明细
show variables like '%profiling%';
Show profiles:mysql 执行明细 set profiling=on
show profile cpu,block io for query 10;
瓶颈：cpu运算，io
线上坑之并发、线程安全问题
线程安全性问题满足以下三个条件：多线程 多个线程共享一个资源 对资源进行非原子性操作（如a++读写操作）
单服务器场景
对于多个线程共享且修改一个资源时发生的并发问题我们一般通过Synchronize,threadLocal,select for update,数据库锁等处理并发安全性问题。Synchronize在获取锁时会强制从主内存中读取对应资源的最新值到工作内存中，在释放锁时前会强制更新主内存中的对应值。这样子就保证了共享资源的可见性。ThreadLocal则通过对于每个线程维护其单独的变量副本避免线程安全性问题。
1、jvm执行程序的时候进行指令重排，避免一些非原子性操作。
private volatile int count=0;
public static void main(String[] args) {
final ThreadTest test = new ThreadTest();
for (int i =0 ; i<1000 ; i++){
new Thread(new Runnable() {
@Override
public void run() {
try {
Thread.sleep(100);
} catch (InterruptedException e) {
e.printStackTrace();
}
test.addNum();
}
}).start();
}
try {
Thread.sleep(1000);
} catch (InterruptedException e) {
e.printStackTrace();
}
System.out.println(">>>>>>>>>>>count="+ test.count);
}
public void addNum(){
count++;
}
分布式场景
我们一般通过分布式锁机制，通过redis获取分布式锁。利用redis.SETNX命令。把要锁定资源的某个标识作为key，并设置全局唯一的值作为redis的key的值，如果SETNX key value返回1那么我们认为获取锁成功，否则认为获取锁失败。但是使用SETNX可能会存在如下问题：
a如果对于所加的锁没有设置有效时间，某个服务成功获取了某个分布式锁，此时该服务崩溃，那么该分布式锁将因锁持有者崩溃而无法释放。所以一般加锁后会紧接着设置锁的有效时间。
b极端场景设置key有效期异常则可以修改获取锁的方法同时根据业务合理设置过期时间：
public boolean getLock(String key, int value) {
try {
if (cacheClient.setnx(key,value) == 1) {
cacheClient.expire(key, timeOut);
return value;
}
if (cacheClient.ttl(key) < 0) {
cacheClient.expire(key, timeOut);
}
} catch (Exception e) {
return false;
}
}
c这里设置的超时时间时还有一个可能存在的问题，假如我超过超时时间都还没有完成业务逻辑的情况下，key会过期，其他线程有可能会获取到锁。这样一来的话，第一个线程还没执行完业务逻辑，第二个线程进来了也会出现线程安全问题。这个除了合理设置超时时间外还可以通过redisson实现：
Config config = new Config();
config.useClusterServers()
.addNodeAddress("redis://address1")
.addNodeAddress("redis://address2")
.addNodeAddress("redis://address3");
RedissonClient redisson = Redisson.create(config);
RLock lock = redisson.getLock("anyLock");
lock.lock();
lock.unlock();
redisson中有一个watchdog的概念，翻译过来就是看门狗，它会在你获取锁之后，每隔10秒帮你把key的超时时间设为30s。这样的话，就算一直持有锁也不会出现key过期了，其他线程获取到锁的问题了。
总结：关键处进行事务控制，减少非原子性操作
内容总结
以上是互联网集市为您收集整理的MYSQL 索引 性能优化全部内容，希望文章能够帮你解决MYSQL 索引 性能优化所遇到的程序开发问题。 如果觉得互联网集市技术教程内容还不错，欢迎将互联网集市网站推荐给程序员好友。
内容备注
版权声明：本文内容由互联网用户自发贡献，该文观点与技术仅代表作者本人。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容， 请发送邮件至 gblab@vip.qq.com 举报，一经查实，本站将立刻删除。
内容手机端
扫描二维码推送至手机访问。