系列文章目录
第一章:sql_mode模式
第二章:optimize table、analyze table、alter table、gh-ost
第三章:InnoDB MVCC原理
第四章:sql语句执行过程
第五章:Percona Toolkit工具简介
第六章:MySQL索引
索引作为类似书籍目录,可以快速定位查找项的位置。MySQL的索引可以根据数据结构、物理存储、字段特性、字段个数进行分类,具体如下:
一、 索引下推
可以在索引遍历过程中,对索引中包含的字段先做判断,直接过滤掉不满足条件的记录,减少回表次数。
二、 唯一索引VS普通索引
由于查询,使用普通索引和唯一索引相差无几,普通索引可以通过change buffer提高速度,特别是写多读少,机械硬盘的情况,写入有提升。所以在保证业务的前提下,优先使用普通索引。
三、建索引的原则
- 最左前缀匹配原则,mysql会一直向右匹配直到遇到范围查询(>、<、between、like)就停止匹配,比如a = 1 and b = 2 and c > 3 and d = 4 如果建立(a,b,c,d)顺序的索引,d是用不到索引的,如果建立(a,b,d,c)的索引则都可以用到,a,b,d的顺序可以任意调整。
- .=和in可以乱序,比如a = 1 and b = 2 and c = 3 建立(a,b,c)索引可以任意顺序,mysql的查询优化器会帮你优化成索引可以识别的形式。
- 尽量选择区分度高的列作为索引,区分度的公式是count(distinct col)/count(*),表示字段不重复的比例,比例越大我们扫描的记录数越少,唯一键的区分度是1,而一些状态、性别字段可能在大数据面前区分度就是0。
- 索引列不能参与计算,保持列“干净”,比如from_unixtime(create_time) = ’2014-05-29’就不能使用到索引,原因很简单,b+树中存的都是数据表中的字段值,但进行检索时,需要把所有元素都应用函数才能比较,显然成本太大。所以语句应该写成create_time = unix_timestamp(’2014-05-29’)。
- 尽量的扩展索引,不要新建索引。比如表中已经有a的索引,现在要加(a,b)的索引,那么只需要修改原来的索引即可。
四、索引优化
- 全值匹配
- 最佳左前缀法则,比如建立了一个联合索引(a,b,c),那么其实可利用的索引就有(a) (a,b)(a,c)(a,b,c)
- 不在索引列上做任何操作(计算、函数、(自动or手动)类型转换),会导致索引失效而转向全表扫描
- 存储引擎不能使用索引中范围条件右边的列
- 尽量使用覆盖索引(只访问索引的查询(索引列和查询列一致)),减少 select *
- is null ,is not null 也无法使用索引
- like “xxxx%” 是可以用到索引的,like “%xxxx” 则不行(like “%xxx%” 同理)。like 以通配符开头(’%abc…’)索引失效会变成全表扫描的操作
- 字符串不加单引号索引失效
- 少用or,用它来连接时会索引失效(这个其实不是绝对的,or 走索引与否,还和优化器的预估有关,5.0之后出现的index merge技术就是优化这个的)
- <,<=,=,>,>=,BETWEEN,IN 可用到索引,<>,not in ,!= 则不行,会导致全表扫描