innodb mysql的锁和索引

最新推荐文章于 2023-11-11 21:46:19 发布

fox1987

最新推荐文章于 2023-11-11 21:46:19 发布

阅读量2.6k

点赞数

分类专栏： Database- mysql 文章标签： mysql

本文链接：https://blog.csdn.net/lovemdx/article/details/17683647

版权

Database- mysql 专栏收录该内容

15 篇文章 0 订阅

订阅专栏

记录下工作中使用到的关于mysql的一些问题。待完善

InnoDB行锁

InnoDB行锁是通过给索引上的索引项加锁来实现的，这一点MySQL与Oracle不同，后者是通过在数据块中对相应数据行加锁来实现的。InnoDB这种行锁实现特点意味着：只有通过索引条件检索数据，InnoDB才使用行级锁，否则，InnoDB将使用表锁！

联合索引

联合索引指的是可以在多个列上添加索引

alter table t add index idx_a_b(a,b);

联合索引的好处是：

1）对于类似select * from t where a = 1 and b = 'aaaaaaaaaaaaaaaaaaaaaaaaa...'的查询和select * from t where a = 1这样的也可以使用（a，b）索引，但是对于select * from t where b = ‘xxxx’ 时 b就不能使用了(即如果查询条件只有一个时，只有是位于联合索引第一个位置的查询可以使用索引)

2）可以对第2键值进行排序

联合索引可以用在如下场景：

如对对A B C建立联合索引，则下面的使用场景可以使用到

select d from table where A = “x” and B= "y” and C = "z” ：此时对A B C均能使用索引
select d from table where A = “x” and B >= "Y" and c = "z" : 此时对A可以使用索引，对B也能使用索引，而C则不能使用索引。是因为如果联合索引中某个字段使用了范围索查询，则后面的字段则不能再使用索引
联合索引ABC最多只能满足A，AB， ABC三种查询，如果需要B、C单独使用索引则需要另外建立索引。通过explain的key_len则可以看出使用了联合索引的哪些部分。AC的查询则只能使用联合索引的A部分

联合索引使用时的注意事项

联合索引中使用范围查询的，应该尽量将范围查询放在后面，因为前面的字段使用了范围查询，其后的索引将不在有效(可以考虑使用in or代替，但是in内的内容太多的话，也会导致查询优化慢和消耗内存，因为in内内容多会导致查询计划的组合数多)
同等情况下应该优先使用联合索引，而不是一个一个的单一索引，因为联合索引将多个索引建立在一个btree上面，比多个单一的btree省空间，并且可以在一次查询使用多个索引。(使用场景为查询时多个条件的)
不建议联合索引的字段过多，因为联合索引的使用有限(必须符合最左前缀)，过多会造成联合索引中索引的利用率不高。同时联合索引的字段数最多不超过16(系统限制)

order by 和索引

order by A desc, B asc 时不能使用索引，使用索引的话必须要求顺序一致。即如果有此类需求，建议处理为A 和B 顺序一致，比如B的值都取负。

索引的tips

mysql的一张表一次查询最多使用一个索引
索引上不能有任何运算，有运算将使用不到索引：比如select * from table where id+1 = 5；就不行，而改为select * from table where id = 4 才可以
写操作多的应用不一定要有需要索引，需要考虑读写比例
数字型索引比字符串索引更高效，查询更快，占用空间更小
使用！= 或者<>的时候mysql 无法使用索引
MyISAM中索引长度不能超过1000
可以枚举的字段使用枚举查询和索引的效率会更高：需要考虑枚举的扩展性，因为增加一种枚举需要alter表

refer：http://hudeyong926.iteye.com/blog/1490687

refer：http://www.cnblogs.com/hitwtx/archive/2011/09/13/2174952.html