mysql学习笔记(二）

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.表结构优化

可以通过建立汇总表等方式来优化查询效率。

追老虎的鹿

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
mysql学习笔记(二）

目录一.索引定义二.索引优点三.Mysql支持的索引类型1.普通索引（b树索引）2.哈希索引3.空间索引（R树）4.全文索引(myisam和最新版mysql的innodb支持)四.索引的基本原理五.创建索引的原则1.B树索引2.哈希索引3.MyIsam支持全文索引和空间数据索引。六.百万级别或以上的数据如何删除七.Mysql优化1.查询优...
复制链接

扫一扫