目录
4.全文索引(myisam和最新版mysql的innodb支持)
1.查询优化
2.索引优化
3.表结构优化
自己总结了下索引相关知识,不擅表达,望海涵,如有错误,望指正。
一.索引定义
存储引擎中用于快速找到记录的一种数据结构。
二.索引优点
1.提高查询效率(1.减少服务器所需扫描的数据量2.帮助服务器避免排序和临时表3可以将随机IO变为顺序IO)
2.使用索引过程中可以优化隐藏器,提高系统性能
3.唯一索引还可以保证某个字段唯一
缺点:
1构建索引增加空间消耗
2插入数据需要调整或重建索引,时间消耗。
三.Mysql支持的索引类型
1.普通索引(b树索引)
B树索引所有的值是是按顺序存储的,非常适用于全键值(所有的列),键值范围,或根据最左前缀查找(即索引中的第一列)。
B树索引不适用于:
1.跳列查找,即假设索引中包含三个列,不能查询满足第一列和第三列条件的数据。
2.非最左前缀查找。
3.若某一列范围查找,则其右边的列不能使用B树索引优化查找。
2.哈希索引
哈希索引查找速度很快,但是它不是按照索引值顺序排序的,不适合范围查找,也不支持部分索引列查找。如果在A,和B两列建立哈希索引,索引值是由A和B共同哈希得到的,因此不能只查询A或B单独一列。创建哈希索引的时候,所创建列若哈希冲突多,则维护代价比较大。
3.空间索引(R树)
可以任意多键组合查询,无须前缀查询。
4.全文索引(myisam和最新版mysql的innodb支持)
比较复杂,不是简单的where匹配,多用于查找文本中的关键词。
四.索引的基本原理
索引用来快速地寻找那些具有特定值的记录。如果没有索引,一般来说执行查询时遍历整张表。
索引的原理很简单,就是把无序的数据变成有序的查询
五.创建索引的原则
索引虽好,但也不是无限制的使用,最好符合一下几个原则
1.B树索引
1) 最左前缀匹配原则,组合索引非常重要的原则,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的顺序可以任意调整,同时对每列的查询也是一样,可以快速某列的查询开头部分。
2)较频繁作为查询条件的字段才去创建索引
3)更新频繁字段不适合创建索引
4)若是不能有效区分数据的列不适合做索引列(如性别,男女未知,最多也就三种,区分度实在太低)
5)尽量的扩展索引,不要新建索引。比如表中已经有a的索引,现在要加(a,b)的索引,那么只需要修改原来的索引即可。
6)定义有外键的数据列一定要建立索引。
7)对于那些查询中很少涉及的列,重复值比较多的列不要建立索引。
8)对于定义为text、image和bit的数据类型的列不要建立索引
2.哈希索引
哈希索引是由创建索引的列共同哈希出来得到的哈希值,不同键值的行计算出来的哈希码不一样,哈希索引将所有哈希码保存在索引中,同时在哈希表中保存指向每个数据行的指针。注意,在mysql中只有memory引擎支持哈希索引,innodb支持自适应哈希索引。注意哈希索引是将所有创建索引的列进行哈希得到一个哈希值,由哈希值找到对应的行,对于等值查询非常快和高效。
1)哈希数据对于单个数据查询速度非常快,但是不适合范围查找,只支持等值比较查询。
2)无法用于排序
3)不支持部分索引列匹配。
4)哈希冲突多的话,索引维护代价很高。
3.全文索引和空间数据索引。
MyIsam支持全文索引和空间数据索引,全文索引主要用来查找文本中的管检测。空间数据索引可以从多个维度来查询数据,而不需要像普通索引那样,只支持最左前缀。
六.百万级别或以上的数据如何删除
关于索引:由于索引需要额外的维护成本,因为索引文件是单独存在的文件,所以当我们对数据的增加,修改,删除,都会产生额外的对索引文件的操作,这些操作需要消耗额外的IO,会降低增/改/删的执行效率。所以,在我们删除数据库百万级别数据的时候,查询MySQL官方手册得知删除数据的速度和创建的索引数量是成正比的。
所以我们想要删除百万数据的时候可以先删除索引(此时大概耗时三分多钟)
然后删除其中无用数据(此过程需要不到两分钟)
删除完成后重新创建索引(此时数据较少了)创建索引也非常快,约十分钟左右。
与之前的直接删除绝对是要快速很多,更别说万一删除中断,一切删除会回滚。那更是坑了。
七.Mysql优化
包括库表结构优化,索引优化,以及查询优化。
1.查询优化
优化数据
1.优化数据访问,避免筛选大量数据
2.避免向数据库请求不需要的数据。有的人习惯select大量结果,然后获取前面的n行。正确做法应该是只获取需要的n行,比如使用limit。
3.每次用select*时候都考虑自己是否需要全部的列。
是否扫描了额外记录:
4在索引中使用where过滤不匹配的记录,可以使用索引覆盖扫描,将所有需要查询的列都放到索引中,这样存储引擎就无须回表获取对应行就可以获得记录了,可以减少额外记录的扫描。如果对于select*的情况无法构造覆盖索引,可以使用延迟关联进行优化。例子如下:
Select * from A where a_name =’x’;
可以优化为:
Select * from A join(select f_id from A where a_name = ‘x’) as t on t.f_id = A.f_id;
对于括号内就可以使用覆盖索引了。
或者该变库的结构,使用单独的汇总表。
重构查询的方式
5.有时候可以将一个大查询分解为多个小查询。或者切分查询,将大查询切分成小查询,每个查询功能一样但是只完成一部分,如定期清理大量数据,如果用个大查询会同时锁住很多数据,占满整个事务日志,耗尽资源,可以每次删除一部分。
比如对于语句delete from message where created <data_sub(now(),interval, 3 month);
可以改成
Row_affected=0
Do{
Row_affected=do_query(
“delete from message where created < data_sub(now(),interval,3 month) limit 10000)
}while rows_affected > 0
6.分解和优化关联查询。分解关联查询,可将多个关联查询缓存多个小查询。优化关联查询,尽量确保on或者using子句中的列有索引,注意顺序,要是优化器的关联顺序是B、A,B就没必要加索引。确保group by和order by表达值只涉及到一个表中的列,这样mysql才可能使用索引优化整个过程。
7.尽可能使用关联查询替代子查询。
8.没有指定where条件的count(*)非常快,可以利用这个特性,用总行数减去不需要获取得行数就得到我们需要的数据了。
2.索引优化
1.对索引列进行计算(即将索引作为独立的列)
例如,我们想要将表tb_test中id大于100的数据记录中的age和name查找出来。
正确的SQL语句是:
select age,name from tb_test where id > 1*100;
要优于:
select age,name from tb_test where id/100 > 1;
下面的不会使用索引优化。
2.对索引列进行拼接
例如,我们想要将表tb_test中name为“zhou”、addr为“CQ”的记录中的id和age查找出来。
正确的SQL语句是:
select id,age from tb_test where name='zhou' and addr='CQ';
要优于:
select id,age from tb_test where concat(name,' ‘,addr) = ‘zhou CQ';
3.在索引列上is null或is not null的使用
例如,我们想要将表tb_test中id大于等于“0”的记录中的age查找出来。
正确的SQL语句是:
select age from tb_test where id >= 0;
要优于:
select age from tb_test where id is not null;
4.应该尽量避免在索引列中使用or连接条件,将导致引擎放弃索引而使用全表扫描。
例如,我们想要将表tb_test中id等于101或102的记录中的age和name查找出来。
正确的SQL语句(使用union)是:
select age,name from tb_test where id = 10 union select age,name from tb_test where id = 11;
要优于:
select age,name from tb_test where id = 10 or id = 11;
5.尽可能避免索引列在like的首字符使用通配符
例如,我们想要将表tb_test中name匹配“zho”的记录中的id和age查找出来。
正确的SQL语句是:
select id,age from tb_test where name like ‘zho%';
要优于:
select id,age from tb_test where name like ‘%ho%';
6.在索引比较长时候可选择前缀索引,创建合适的前缀长度。
7.对多个索引列做and操作时候,通常意味着需要一个包含所有相关列的索引,而不是给每个列单独建一个索引。同时,选择合适的索引列的顺序对于性能的提升也非常重要。有一个经验法则:将选择性最高的列放到索引的最前列(同时避免随机IO和范围查找)。
8.in和not in也要慎用否则将导致全表扫描。
如select name from t where id between 1 and 3;
要优于:
select name from t where id in(1,2,3);
9.在where子句中对索引用函数操作也将使表放弃使用索引优化。
10.索引不要太多,尽量避免更新索引的数据列。
3.表结构优化
可以通过建立汇总表等方式来优化查询效率。