索引及SQL优化
索引:
索引概述 :
索引是一种高效获取数据的数据结构
优势 | 劣势 |
---|---|
提高数据检索的效率,降低数据库的IO成本 | 索引列也是要占用空间的 |
通过索引列对数据进行排序,降低数据排序的成本,降低CPU的消耗 | 索引大大提高了查询的效率,同时却降低更新表的速度,如对表进行Insert,updata,delete时,效率降低 |
索引结构
索引是在存储引擎层实现的,不同的存储引擎有不同的结构
索引结构 | 描述 |
---|---|
B+tree索引 | 最常见的索引类型,大部分引擎都支持B+树索引 |
Hash索引 | 底层数据结构是用哈希表实现的,只有精确匹配索引列的查询才有效,不支持范围查询 |
R-tree(空间索引) | 空间索引是MyISAM引擎的一个特殊索引类型,主要用于地理空间数据类型,通常使用较少 |
Full-Text(全文索引) | 是一种通过建立倒排索引,快速匹配文档的方式。 |
索引分类
索引分为聚集索引和二级索引
分类 | 含义 | 特点 |
---|---|---|
聚集索引(Clusterred Index) | 将数据存储与索引放到一块,索引结构的叶子节点保存了行数据 | 必须有,而且只有一个 |
二级索引(Secondary Index) | 将数据与索引分开存储,索引结构的叶子节点关联的是对应的主键 | 可以存在多个 |
而再对其细化,可以分为主键索引,唯一索引,常规索引和全文索引
分类 | 含义 | 特点 | 关键字 |
---|---|---|---|
主键索引 | 针对与表中主键创建的索引 | 默认字动创建,只能有一个 | PRIMARY |
唯一索引 | 避免同一个表中某数据中的值重复 | 可以有多个 | UNIQUE |
常规索引 | 快速定位特定数据 | 可以有多个 | |
全文索引 | 全文索引查找的是文本中的关键字,而不是比较索引中的值 | 可以有多个 | FULLTEXT |
聚集索引的选取规则
1.如果存在主键,主键索引就是聚集索引
2.如果不存在主键,将使用第一个唯一索引作为聚集索引
3.如果表没有主键和合适的唯一索引,InnoDB会自动生成一个rowid作为隐藏的聚集索引
索引设计及使用:
索引设计原则:
索引设计需要遵从这7点设计原则:
1.针对与数据量较大,且查询比较频繁的表建立索引。
2.针对与常作为查询条件(where)、排序(order by)、分组(group by)操作的字段建立索引。
3.尽量选择区分度高的列作为索引,尽量建立唯一索引,区分度越高,使用索引的效率越高。
4.如果是字符串类型的字段,字段的长度较长,可以针对于字段的特点,建立前缀索引。
5.尽量使用联合索引, 减少单列索引,查询时,联合索引很多时候可以覆盖索引,节省存储空间,避免回表,提高查询效率。
6. 要控制索引的数量,索引并不是多多益善,索引越多,维护索引结构的代价也就越大,会影响增删改的效率。
7.如果索引列不能存储NULL值,请在创建表时使用NOT NULL约束它。当优化器知道每列是否包含NULL值时,它可以更好地确定哪个索引最有效地用于查询。
索引使用:
当使用联合索引时,需要遵守最左前缀法则:
如果索引关联了多列(联合索引),要遵守最左前缀法则,最左前缀法则指的是查询从索引的最左列开始,并且不跳过索引中的列。
如果跳跃某一列,索引将部分失效(后面的字段索引失效)。
在查询时,与索引建立时的字段顺序有关,与查询时的字段位置无关。
当使用联合索引进行范围查询:
联合索引中,出现范围查询(<, >),范围查询右侧的列索引失效。可以用>=或者<=来规避索引失效问题。
当出现下述情况会导致索引失效:
1.在索引列上运行
2.字符串不加引号
3.模糊查询如果仅仅是尾部模糊,索引不会失效,如果是头部模糊匹配,索引失效
4.or连接的条件:用or分隔开的条件,如果or前的条件中的列有索引,而后面没有索引,那么涉及到的索引都会失效
5.数据分布影响:如果mysql评估使用索引比全表扫描慢,则不会使用索引
覆盖索引:
尽量使用覆盖索引(查询使用到索引,并且需要返回的列,在该索引中已经全部找到),减少select *
前缀索引:
create index 索引名 表名(字段名(前几个字))
在创建索引时,根据情况进行单例索引与联合索引选择。
SQL优化:
插入数据
当进行对数据的插入优化,可以选择:
1.insert优化 批量插入: insert into 表名 values (值1,值2,……)
2.手动提交事务
3.主键顺序插入:顺序插入的性能比乱序高
4.大批量插入数据:使用load指令进行插入
主键优化
1.数据组织方式:在inniDB存储引擎中,表数据都是根据主键顺序组织存放的,这种存储方式的表称为索引组织表
2.页分裂:页可以为空,也可以填充一半,也可以填充100%
3.页合并:当页中删除的记录达到MERGE_THRESHOLD(默认为页的50%),innoDB会开始寻找最靠近的页,看看是否可以将两个页合并并以优化空间使用
主键设计原则:
1.满足业务需求的情况下,尽量降低主键的长度
2.插入数据时,尽量选择顺序插入,选择使用AUTO_INCREMENT自增主键
3.尽量不用使用UUID做主键或者其他自然主键,如身份证号
4.业务操作时,避免对主键的修改
order by优化
1.根据排序字段建立合适的索引,多字段排序时,也遵循最左前缀法则
2.尽量使用覆盖索引
3.多字段排序,一个升序一个降序,此时需要注意联合索引在创建时的规则
4.如果不可避免的出现filesort,大数据量排序时,可以适当增大排序缓冲区大小sort_buffer_size
group by 优化
1.在分组查询时,也可以建立分组索引提高效率
2.需要满足最左前缀法则
limiit优化
一般分页查询时,通过创建覆盖索引能够比较好地提高性能,可以通过覆盖索引加子查询形式进行优化
count 优化
尽量使用count(*)
update
innoDB的行锁是针对索引加的锁,不是针对记录加的锁,并且该索引不能失效,否则会从行锁升级为表锁