索引是一种帮助MySQL高效获取数据的有序数据结构,底层为B+树结构,索引有以下作用:
(1)提高运行效率
(2)降低IO成本(对B+树来说,查询千万级别的数据最多只需要3-4次磁盘IO)
(3)索引对数据排序,降低了CPU的消耗
一、B+树
InnoDB存储引擎的索引使用B+树实现。它只在叶子节点存储数据(注意: InnoDB的主键索引的叶子节点存储行数据,非主键索引的叶子节点存储主键和其他带索引的列数据),且叶子节点之间用指针连接,而非叶子节点只存储指针(索引)。B+树有以下优点:
(1)读写B+树的代价更低
千万级的数据只需要3-4层高度就可以,且B+树有大量冗余节点,删除一个节点的时候可以从叶子节点删除,可以不用改动非叶子节点。
(2)查询B+树更加稳定
每次查询都必须查询到叶子节点,而B-树的数据可能在叶子节点,也可能在中间节点,或者都有
(3)便于扫库和区间查询
B+树的叶子节点之间是双向链表连接
二、索引有哪些
1. 单值索引:一个索引只包含单个列,一个表可以有多个单列索引
- 建表时,加上 key(列名) 指定
- 单独创建,create index 索引名 on 表名(列名)
- 单独创建,alter table 表名 add index 索引名(列名)
2. 唯一索引:索引列的值必须唯一,但允许有 null 且 null 可以出现多次
- 建表时,加上 unique(列名) 指定
- 单独创建,create unique index idx 表名(列名) on 表名(列名)
- 单独创建,alter table 表名 add unique 索引名(列名)
3. 主键索引:设定为主键后数据库会自动建立索引,innodb 为聚簇索引,值必须唯一且不能为null
- 建表时,加上 primary key(列名) 指定
4. 复合索引:即一个索引包含多个列,如(name,password)
- 建表时,加上 key(列名列表) 指定
- 单独创建,create index 索引名 on 表名(列名列表)
- 单独创建,alter table 表名 add index 索引名(列名列表)
5.前缀索引:对字符类型字段的前几个字符建立的索引,而不是在整个字段上建立的索引,前缀索引可以建立在字段类型为 char、 varchar、binary、varbinary 的列上。使用前缀索引的目的是为了减少索引占用的存储空间,提升查询效率
三、索引创建原则
1.什么时候需要创建
(1)数据量较大,且查询比较频繁的表建立索引
(2)常作为查询条件的字段创建索引,如where、group by、分组
(3)尽量使用联合索引,减少单列索引
(4)字符串类型的字段,且字段较长的情况下建立索引——前缀索引
2.创建索引需要注意什么
(1)表数据太少,不需要创建索引
(2)索引不是越多越好,控制索引数量
(3)选择区分度高的列,建立唯一索引(如性别就不合适)
(4)频繁变更的字段不适合创建索引,频繁变更会更改B+树,影响数据库性能
四、索引失效
如有一个联合索引(name,age,address),在以下几种情况下会失效
(1)违反最左前缀原则,即跳过某一列查询
如 name,address,跳过了age;使用age,address查询,跳过了name,= 和 in可以乱序
(2)范围查询右边的列会造成索引失效
如 where name = ' Mike' and id >= 1 and address = '上海' , 会造成address不能命中
(3)like的前导模糊查询
如 like %小米
(4)字符串不加单引号
如 borad = 小米,正确 borad = '小米'
(5)在索引列上进行运算操作
如 where subString(name,3,2)= '小米'
(6)全表查询比使用索引快时,索引失效
(7)索引不会包含有NULL值的列,IS NULL,IS NOT NULL无法使用索引:
只要列中包含有NULL值都将不会被包含在索引中,复合索引中只要有一列含有NULL值,那么这一列对于此复合索引就是无效的。所以在数据库设计时,尽量使用NOT NULL约束以及默认值。
五、索引优化
1. 避免使用导致索引失效的用法
(1)like语句的前导模糊查询不能使用索引
(2)范围条件右边的列(范围列可以用到索引),范围列之后列的索引全失效:
(3)不要在索引列上面做任何操作(计算、函数),否则会导致索引失效而转向全表扫描
范围条件有:<、<=、>、>=、between等
(4)强制类型转换会全表扫描:
字符串类型不加单引号会导致索引失效,因为mysql会自己做类型转换,相当于在索引列上进行了操作。
(5)负向条件查询不能使用索引:
负向条件有:!=
、<>
、not in
、not exists
、not like
等,优化案例:
2. 其他优化方式
(1)union、in、or 都能够命中索引,但是建议使用 in:
因为in的综合效率最高。
(2)联合索引最左前缀原则:
如果在(a,b,c)三个字段上建立联合索引,那么他会自动建立 a| (a,b) | (a,b,c) 组索引。
- 建立联合索引的时候,区分度最高的字段在最左边
- 存在非等号和等号混合判断条件时,在建立索引时,把等号条件的列前置。如 where a>? and b=?,那么即使a 的区分度更高,也必须把 b 放在索引的最前列
- 最左前缀查询时,并不是指SQL语句的where顺序要和联合索引一致
六、SQL优化
1. 对表的设计优化
分库分表:进行水平分割或垂直分割
2. SQL语句优化
(1)避免使用select *
(2)避免索引失效的写法
(3)用union all 代替union,因为后者多一次过滤
(4)能用inner join就不用left join、right join,因为内连接会对两个表进行优化,默认以小标为驱动,而左右连接不会调整连接顺序
(5)使用between and代替in
(6)对枚举类型的字段,如性别、星期等建议使用enum而不是varchar
(7)字段设计尽可能使用not null
3. 定位慢查询
(1)开源工具调试:如skywalking等,可以查看接口的执行情况
(2)使用MySQL自带的慢日志
在配置文件中 使用 slow-query-log = 1爱妻,设置值为2s
4. 优化慢sql
采用EXPLAIN或者DESC命令,可以展示sql的执行情况,如
EXPLAIN SELECT * FROM user WHERE id = 1,该命令有以下几个字段
(1)possible-key:可能会用的索引
(2)key:实际命中的索引
(3)key-len:索引占用的大小
(4)Extra:额外的优化建议,是否存在回表等
(5)type:这条sql的连接类型,是否有优化空间,类型有以下几种:
const:表示通过索引一次就找到了,const用于比较primary key 或者 unique索引。因为只需匹配一行数据,所有很快。
system:表只有一行记录(等于系统表),这是const类型的特例,平时不会出现,可以忽略不计
eq_ref:唯一性索引扫描,对于每个索引键,表中只有一条记录与之匹配。常见于主键 或 唯一索引扫描。
ref:非唯一性索引扫描,其他索引
range:只检索给定范围的行,一般就是在where语句中出现了between、<、>、in等的查询。这种索引列上的范围扫描比全索引扫描要好。只需要开始于某个点,结束于另一个点,不用扫描全部索引
index:扫描全部索引,index与ALL区别为index类型只遍历索引树。这通常为ALL块,应为索引文件通常比数据文件小。(Index与ALL虽然都是读全表,但index是从索引中读取,而ALL是从硬盘读取)
ALL:全表扫描,遍历全表以找到匹配的行
参考:MySQL高级 之 explain执行计划详解_走慢一点点的博客-CSDN博客、CSView