第4课:索引的奥义

数据库索引

索引的目的在于提高查询效率

1.什么情况下哪些字段加索引
直接条件查询的字段
查询中与其它表关联的字段
查询中排序的字段
查询中统计或分组统计的字段
唯一索引可以保证字段值唯一,而且能提高针对本字段的查询效率

2.什么情况下应不建或少建索引
表记录太少
经常插入、删除、修改的表
数据重复且分布平均的表字段
经常和主字段一块查询但主字段索引值比较多的表字段

3.什么情况下索引会失效
数据值包含有NULL
如果条件中有or,即使其中有条件带索引也不会使用
对于聚合索引,不是使用的第一部分,则不会使用索引(比如有个a,b的聚合索引,查询时候如果只查b或者先查b后查a都会导致索引失效)
like查询是以%开头
如果列类型是字符串,那一定要在条件中将数据使用引号引用起来,否则不使用索引
在列上使用函数进行计算
使用NOT IN

4.索引类型与选择
主键索引   表中主键使用
普通索引   无需特殊处理的普通索引
唯一索引   保证数据表中列值唯一的索引
全文索引   针对长字符串搜索用
聚合索引   多列索引
针对聚合索引多说两句:比如有个a,b两列的聚合索引,使用中遵循最优左前缀原则,即查询时对索引使用有严格顺序要求
查a或a,b都会走索引查b或b,a则不会走索引,即建立索引时左边a是优先匹配的,查询时a不在前面就不走索引
alter table t add index idx_a_b(a,b);
聚合索引的好处:对于类似select * from t where a = 1 and b = 'www.godeye.org'的查询和select * from t where a = 1这样的也可以使用(a,b)索引,但是对于b就不能使用了,还有个好处是可以对第2键值进行排序

同时索引方法还分为btree和hash 一般都是用btree方式

5.索引的缺点
虽然索引大大提高了查询速度,同时却会降低更新表的速度,如对表进行INSERT、UPDATE和DELETE。因为更新表时,MySQL不仅要保存数据,还要保存一下索引文件
建立索引会占用磁盘空间的索引文件

6.索引的设置
[1]先找出要进行优化的慢查询
先运行看看是否真的很慢,注意设置SQL_NO_CACHE
where条件单表查,锁定最小返回记录表。这句话的意思是把查询语句的where都应用到表中返回的记录数最小的表开始查起,单表每个字段分别查询,看哪个字段的区分度最高
explain查看执行计划,是否与1预期一致(从锁定记录较少的表开始查询)
order by limit 形式的sql语句让排序的表优先查
了解业务方使用场景
加索引时参照建索引的几大原则
观察结果,不符合预期继续从头分析
explain结果实例
+----+-------------+------------+-------+---------------------------------+-----------------------+---------+-------------------+-------+--------------------------------+
| id | select_type | table      | type  | possible_keys                   | key                   | key_len | ref               | rows  | Extra                          |
+----+-------------+------------+-------+---------------------------------+-----------------------+---------+-------------------+-------+--------------------------------+
|  1 | PRIMARY     | cl         | range | cm_log_cls_id,idx_last_upd_date | idx_last_upd_date     | 8       | NULL              |   379 | Using where; Using temporary   |
|  1 | PRIMARY     | <derived2> | ALL   | NULL                            | NULL                  | NULL    | NULL              | 63727 | Using where; Using join buffer |
|  2 | DERIVED     | emp        | ALL   | NULL                            | NULL                  | NULL    | NULL              | 13317 | Using where                    |
|  2 | DERIVED     | emp_cert   | ref   | emp_certificate_empid           | emp_certificate_empid | 4       | meituanorg.emp.id |     1 | Using index                    |
+----+-------------+------------+-------+---------------------------------+-----------------------+---------+------------------------------------------------------------+
type显示的是访问类型,是较为重要的一个指标,结果值从好到坏依次是:
system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL
一般来说,得保证查询至少达到range级别,最好能达到ref

[2]有针对性的对表字段增加合适的索引
比如字段唯一就用唯一索引,用的最多的还是普通索引
alter table test add index idx_status(result, status);

当然也可以借助客户端图形界面添加索引,因为比较简单,这里不再多说

打赏  如对你有帮助,请我喝杯咖啡吧!