mysql sql注意事项_MySQL 数据库索引使用场景&注意事项

目录

1.何种查询支持索引？

2.注意事项和建议

一 何种查询支持索引？

1 MySQL 目前支持前导列

就目前来说，mysql 暂时只支持最左前缀原则进行筛选。

例子：创建复合索引

create index idx_a_b_c on tb1(a,b,c)

只有使用如下条件才可能应用到这个复合索引

1.where a=?

2.where a = ? and b = ?

3.where a = ? and b = ? and c = ?

但

4.where a = ? and c ＝ ？

只会使用到mysql 索引 a 列的信息

2.索引列上的范围查找

对于某个条件进行范围查找时，如果这个列上有索引，且使用 where ... between

and ... > ,< 等范围操作，那么可能用到索引范围查找，如果索引范围查找的成本太高，数据库可能会选择全表扫描的方式

。

注意 in 不属于范围查找的范畴

3.join 列

在联合查询两个表时，比如查询语句为 select a.col1,b.col2 from a join b on a.id = b.id,

其中id 为两个表的主键，如果a是小表，那么a 就被视为驱动表，那么数据库可能全表扫描a 表，

并用 a表的每个id 去探测b表的索引查询匹配的记录。

4.where 子句

形如：

where a = ? and b = ? and c>1000

where a = ? and b = ? and c = ? and d>1000

where 子句的条件列是复合索引前面的索引列＋另一个列的范围查找

create index idx_a_b_c_d on tb1(a,b,c,d);

形如：

where a = ? and b = ? and c>1000

where a = ? and b = ? and c = ? and d>1000

才会用到这个索引

下面两个查询：

where a = ? and b =? and c>10000 and d< 10000

这个例子中d

d <10000这个操作不会走索引

where a >? and b =? and c>10000 and d< 10000

这个例子中a列上有范围查找，那么b、c、d列上的索引信息都不能被利用

原则，创建索引，考虑把复合索引的范围查找放到最后。

5.mysql 优化器

mysql 优化器会做一些特殊优化，比如对于索引查找max(索引列)可以直接进行定位。遇到max，min 是可以在列上做索引。

二 注意事项和建议

1.where 条件中的索引列不能是表达式的一部分，mysql 不支持函数索引

2.InnoDB 二级索引底层叶子极点存储的是索引+主键值

InnoDB 的非主键索引存储的不是实际的记录的指针，而是主键的值，所以主键最好是整数型，如自增ID ,基于主键存取数据是最高效的，使用二级索引存取数据则需要进行二次索引查找。

3.索引尽量是高选择性的

而且要留意基数值，基数值指的是一个列中不同值的个数，显然，

最大基数意味着该列中的每个值都是唯一的，最小基数意味着该列中的所有值都是相同的，索引列的基数相对于表的行数较高时，

也就重复值更少，索引的工作效果更好。

有种情况虽然基数很小，但由于数据分布很不均匀因此也会导致某些记录数很小，

那么这种情况也适合建立索引加速查找这部分数据。

4.使用更短的索引

可以考虑前缀索引，但应确保选择的前缀的长度可以保证大部分值是唯一的。

如：alter table test add key(col(6))

衡量不同前缀索引唯一值比例。

select count(distinct left(col_name,5))/count(*) As sele5，

select count(distinct left(col_name,6))/count(*) As sele6，

select count(distinct left(col_name,7))/count(*) As sele7，

select count(distinct left(col_name,8))/count(*) As sele8，

select count(distinct left(col_name,9))/count(*) As sele9

from table_name;

5.避免创建过多的索引

索引过多可能会浪费大量空间

尤其本身字段量较大的字符串，索引过多可能会浪费空间，且降低修改数据的速度，

所以，不要创建过多的索引，也不要创建重复的索引。

6.如果是唯一值得列，创建唯一索引会更佳，也可以确保不会出现重复数据.

7.使用覆盖索引能大大提高性能

覆盖索引：所有数据都可以从索引中得到，而不需要去读物理记录。例如某个复合索引idx_a_b_c 建立在表tb1 的 a、b、c 列上，

那么对于如下的sql 语句

select a,b from tb1 where a = ? and b = ? and c =?

mysql可以直接从索引idx_a_b_c 中获取数据。使用覆盖索引也可以避免二次索引查找。

使用explain 命令输出查询计划，如果extra列是“using index ” 那就表示使用的是覆盖索引。

8.利用索引来排序

mysql 有两种方式可以产生有序结果，一种是使用文件排序，另一种是扫描有序的索引，我们尽量使用索引来排序

注意事项：

1. 尽量保证索引列和order by 的列相同，且各列按照相同的顺序排序。

比如在表table1 的复合索引idx_a_b_c(创建在a,b,c上)；

如：select * from table1 order by a,b,c;

select * from table1 where a=? and b =? order by c

以上查询都可以利用有序索引来加速检索顺序。

2.如果连接多张表，那么order by 引用的列需要再表连接顺序的首张表内。

9 添加冗余索引需要权衡:

如果一个索引column A 那么一个新的索引(columnA,columnB)就是冗余索引

一般情况下不论是新增冗余索引，还是扩展原索引为冗余索引，都会导致索引文件的增大，并且增加了维护索引的开销。

比如更改了列值，并且在此列上建立了索引，那么这个列值更改之后，索引是要进行重新排序的。