- 索引概述
- 索引结构
- 索引分类
- 索引语法
- SQL性能分析
- 索引使用
- 索引设计原则
索引概述:
索引(index)是帮助MySQL高效获取数据的数据结构(有序)。
在数据库之外,数据库系统还维护着满足特定查找算法的数据结构,这些数据结构以某种方式引用(指向)数据,
这样就可以在这些数据结构上实现高级查找算法,这种数据结构就是索引。
无索引:效率慢,查询--全表扫描
有索引:高效(例如,二叉树)
索引优劣点:
优势 | 劣势 |
提高数据检索的效率,降低数据库的IO成本 | 索引列需要占用资源空间 |
通过索引列对数据进行排序,降低数据的排序成本,降低CPU的消耗 | 索引大大提高了查询效率,同时也降低更新表的数据,例如,对表进行INSERT、UPDATE、DELETE时,效率降低。 |
索引结构:
MySQL的索引是在存储引擎层实现的,不同的存储引擎有不同的结构,主要分为:
索引结构 | 描述 |
B+Tree索引 | 最常见的索引类型,大部分引擎都支持B+树索引 |
Hash索引 | 底层数据结构是用哈希表实现的,只有精确匹配索引列的查询才有效,不支持范围查询 |
R-tree(空间索引) | 空间索引是MyISAM引擎的一个特殊索引类型,主要用于地理空间数据类型,通常使用较少 |
Full-txt(全文索引) | 是一种通过建立倒排索引,快速匹配文档的方式。类似于Lucene,Solr,ES |
索引分类:
注:二级索引,也被称为非聚集索引。
聚集索引选取规则:
- 如果存在主键,主键索引就是聚集索引
- 如果不存在主键,将使用第一个唯一索引,作为聚集索引
- 如果表没有主键,或没有合适的唯一索引,则InnoDB会自动生成一个rowid作为隐藏的聚焦索引。
索引语法:
-- 查询指定表的索引信息
show INDEX from tb_users;
-- 新增索引
create index id_user_name on tb_users(name);
-- 查询指定表的索引信息
show INDEX from tb_users;
-- 添加唯一索引
-- create UNION index idx_indexname on tb_users(name);
-- 删除索引
drop index id_user_name on tb_users;
注:在创建联合索引时,需注意字段的顺序
SQL性能分析:
- SQL执行频率
- Mysql客户端连接成功后,通过show [session|global]status命令可提供服务器状态信息。
- 通过如下指令,可查看当前数据库的Insert、Update、Delete、Select的访问频次:show global status like 'Com_______'(下划线 7 个)
-
- 慢查询日志
- 慢查询日志记录了所有执行时间超过指定参数(long_query_time,单位:秒,默认10秒)的所有SQL语句的日志。
- MySQl的慢查询日志默认没有开启,需要在MySql的配置文件(/etc/my/.cnf)中配置如下信息:
-
- 慢查询日志状态查询——show VARIABLES like 'slow_query_log'; 如下所示:
-
-
配置完之后,通过以下指令重新启动MySQL服务器进行测试,查看慢日志文件中记录的信息/var/lib/mysql/localhost-slow.log
- profile详情
- show profile(查看sql执行时长)能够在优化时帮助我们了解时间都耗费到哪里了。通过have_profiling参数,能够看到当前Mysql是否支持profile操作
-
-- 查看是否支持 PROFILE:select @@have_profiling; -- YES支持,如下图:
-
-
注:默认profiling是关闭的,可通过set语句在session/global级别开启profiling:
-
-- 0,表示关闭,1表示打开 select @@profiling; 设置方式: set @@profiling =1;
-
-
- explain执行计划
- explain或者desc命令获取mysql如何执行select语句的信息,包括在select语句执行过程中表如何连接和连接的顺序
- 语法:直接在select语句之前添加关键字 explain或desc—— explan/desc select sql语句,如下所示:
- EXPLAIN SELECT t.* from tb_users t;
- desc SELECT t.* from tb_users t;
-
desc select id,NAME,age,gender,phone from tb_users t;
-
-
EXPLAIN select * from tb_user_edu where userid in(SELECT userid FROM tb_users where id<4);
-
-
explain执行计划个字段含义解释说明:
- id:select查询的序列号,表示查询中执行select子句或者是操作表的顺序——id相同,执行顺序从上到下;id不同,值越大,越优先执行。
- select_type:
- 表示select的类型,常见的取值有slmple——简单表,即不使用表连接或子查询、primary——注查询,即外层的查询、
- Union——union中的第二个或者后面的查询语句、subquery——select/where之后包含了子查询等
- type:表示连接类型,性能由好到差的连接类型为——null、system、const、eq_ref、ref、range、index、all。
- possible_key:显示可能应用在这张表上的索引、一个或多个
- key:实际使用的索引,如果为null,则没有使用索引
- key_len:表示索引中使用的字节数,该值为索引字段最大可能长度,并非实际使用长度,在不损失精确度的前提下,长度越短越好
- rows:mysql任务必须要执行查询的行数,在innodb引擎的表中,是一个估计值,可能并不是精确的
- filtered:表示返回结果的行数占需读取行数的百分比,filtered值越大越好
- extra:额外的信息
索引的使用:
索引——用于提升查询效率,索引会影响,即降低insert、update、delete效率。
对表创建索引,就是构建一种数据结构(B+Tree),数据量越大,对表创建耗时越长。
- 最左前缀法则
- 如果索引多列--联合索引,要遵守最左前缀法则。
- 最左前缀法则是指查询从索引的最左列开始,并且不跳过索引中的列。如果跳过某一列,索引将部分实现——后面的字段索引失效。
- 即,查询时需包含查询最左侧列,否则索引失效
- 范围查询
- 联合索引中,出现范围查询(>,<),范围查询右侧的列索引失效
- eg:select * from tb_users where id>10 and age=18—— age =18索引失效
- 注,在业务允许的情况下,联合查询,尽量使用 >= 或 <= 实现范围查询 —— 可规避索引失效
- 索引列运算
- 不要对索引列上进行运算操作,否则索引将失效
- 字符串不加引号
- 字符串类型字段使用时,不加引号,索引将失效
- 模糊查询
- 如果仅仅是尾部模糊匹配,索引不会失效;如果是头部模糊匹配,索引将失效。
- eg: name like '软件%' ——索引不失效; name like '%软件'——失效; name like '%软%'——失效
- 注:只要头部添加 % 匹配,索引将失效
- or连接的条件
- 用 or 分割开的条件,如果 or 前的条件中的列有索引,而后面的列中没有索引,那么涉及的索引都不会被用到。
- 数据分布影响
- 如果mysql评估使用索引比全表扫描更慢,则不使用索引
- SQL 提示
- 是优化数据库的一个重要手段,简单来说,就是在SQL语句中加入一些人为的提示来达到优化操作的目的。例如:
- use index:EXPLAIN select * from tb_users use INDEX(idx_user_name) where name='张三';
- ignore index:EXPLAIN select * from tb_users IGNORE INDEX(idx_user_name) where name='张三';
- force index:EXPLAIN select * from tb_users FORCE INDEX(idx_user_name) where name='张三';
- 覆盖索引
- 尽量使用覆盖索引(查询使用了索引,并且需要返回的列,在该索引中已经全部能够找到),减少select * ——用select 字段列表代替* (尽量避免回表查询)
- 注:
- using index condition:查找使用了索引,但是需要回表查询数据
- using where;using index:查找使用了索引,但是需要的数据都在所有列中能找到,所以不需要回表查询数据
- 回表查询:即查询的字段信息在索引中不存在,则另需要使用id列查询出行数据(对于单表,如果存在联合索引,查询列都是索引列,则会避免回表查询,否则会出现回表查询)
- 前缀索引——降低索引体积,提高效率
- 当字段类型为字符串(varchar\text等)时,有时需要索引很长的字符串,这会让索引变得很大,查询时,浪费大量的磁盘IO,
- 影响查询效率。此时可以只将字符串的一部分前缀,建立索引,这样可以大大节约索引空间,从而提高索引效率。
- 语法:
- create index idx_indexname on tablename(column(n))——n,表示字段内容,字符串的前 n 个字符串作为索引。
- 前缀 n 长度:可根据索引的选择性决定,而选择性是指不重复的索引值(基数)和数据表的记录总数的比值,索引选择性
- 越高则查询效率越高,唯一索引的选择性是1,这是最好的索引选择性,性能也是最好的。
- 单例&联合索引
- 单例索引:即一个索引只包含单个列
- 联合索引:即一个索引包含多个列
- 注:在业务场景中,如果存在多个查询条件,考虑针对于查询字典建立索引时,建立联合索引,而非单列索引。
- 注:多条件联合查询时,mysql优化器会评估那个字段的索引效率更高,会选择该索引完成本次查询。
- 索引设计原则
- 针对数据量大,且查询比较频繁的表建立索引
- 针对于长作为查询条件where、排序order by、分组group by操作的字段建立索引
- 尽量选择区分度高的列作为索引,尽量建立唯一索引,区分度越高,使用索引的效率就越高
- 如果是字符串类型的字段,字段的长度较长,可针对于字段的特点,建立前缀索引
- 尽量使用联合索引,减少单例索引,查询时,联合索引很多时候可以覆盖索引,节省存储空间,避免回表,提高查询效率
- 要控制索引的数量,索引并不是越多越好,索引越多,维护索引结构的代价也就越大,会影响增删改的效率
- 如果索引列不能存NULL值,请在创建表时使用NOT NULL约束列。当优化器知道每列是否包含NULL值时,它可以更改地确定那个索引最有效地用于查询