江苏龙网

  1. 主页 > 生活百科 > >

MySQL DBA必读：万字归总表设计与SQL编写技巧( 五 )

MySQL DBA


索引使用规范
根据MySQL的索引使用经验相结合，对索引使用做出如下的建议 。
1、根据表数据量评估索引
详细评估和分析建立索引所在表的实际数据量，数据量达到GB级别、记录数达到百万级别、访问频繁的表，需要建立合适的索引 。相反，在数据量较少且访问频率不高的情况下，如只有一百行记录以下的表不需要建立索引 。因为在数据量少的情况下，使用全表扫描效果比走索引更好 。
2、选择适当的索引字段
索引字段的选择需要结合业务需求，评估出应用中作为查询条件出现比较频繁的字段，在此字段上建立单独或者复合索引 。选择建立索引的字段，应该遵循以下的原则：
1）高选择性，选择性是指通过索引字段查询返回结果集占表总数据量的百分比，结果集占表总数据量的百分比越小选择性越高，反之越低 。选择性越高，通过索引查询返回的结果集越少，索引更为高效 。在OLTP应用系统中，选择性应高于1，也就是结果集占表总数据量的百分比应<1% 。
2）空值少，避免在空值很多的字段上建立B-tree索引，大量空值会降低索引效率，索引字段中的空值占总数据量的百分比应少于10% 。
3）数据分布均匀，索引字段中，个别数据值占总数据量的百分率明显比其它数据值占总数据量的百分率高，表明该字段数据值分布不均，容易引起数据库选择错误索引，生成错误的查询执行计划 。应该避免在数据值分布不均的字段上建立索引 。
3、避免过度索引
每个额外的索引都要占用额外的磁盘空间，并降低写操作的性能，这一点我们前面已经介绍过 。在修改表的内容时，索引必须进行更新，有时可能需要重构，因此，索引越多，所花的时间越长 。如果有一个索引很少利用或从不使用，那么会不必要地减缓表的修改速度 。此外，MySQL在生成一个执行计划时，要考虑各个索引，这也要费时间 。创建多余的索引给查询优化带来了更多的工作 。索引太多，也可能会使 MySQL 选择不到所要使用的最好索引 。只保持所需的索引有利于查询优化 。如果想给已索引的表增加索引，应该考虑所要增加的索引是否是现有多列索引的最左索引 。如果是，则就不要费力去增加这个索引了，因为已经有了 。
4、使用唯一索引
考虑某列中值的分布 。对于唯一值的列，索引的效果最好，而具有多个重复值的列，其索引效果最差 。例如，存放年龄的列具有不同值，很容易区分各行 。而用来记录性别的列，只含有“ 男”和“女”，则对此列进行索引没有多大用处（不管搜索哪个值，都会得出大约一半的行） 。
5、使用短索引
如果对串列进行索引，应该指定一个前缀长度，只要有可能就应该这样做 。例如，如果有一个 CHAR(200) 列，如果在前10个或20个字符内，多数值是惟一的，那么就不要对整个列进行索引 。对前10个或20个字符进行索引能够节省大量索引空间，也可能会使查询更快 。较小的索引涉及的磁盘 I/O 较少，较短的值比较起来更快 。更为重要的是，对于较短的键值，索引高速缓存中的块能容纳更多的键值，因此，MySQL也可以在内存中容纳更多的值 。这增加了找到行而不用读取索引中较多块的可能性 。
6、利用符合索引前置列
在创建一个 n 列的索引时，实际是创建了 MySQL 可利用的 n 个索引 。多列索引可起几个索引的作用，因为可利用索引中最左边的列集来匹配行 。这样的列集称为最左前缀 。（这与索引一个列的前缀不同，索引一个列的前缀是利用该的前 n 个字符作为索引值 。） 例如：(a,b,c)、(a,b),后者为冗余索引 。当SQL的where条件包含a，b时，能正确的走前一索引，后者作为冗余没有建立的必要 。关键在于找到适合的前置列，可以避免建冗余的索引 。
7、考虑在列上进行的比较类型
索引可用于“ <”、“ < = ”、“ = ”、“ > =”、“ >”和 BETWEEN 运算 。在模式具有一个直接量前缀时，索引也用于 LIKE 运算 。如果只将某个列用于其他类型的运算时（如 STRCMP( )），对其进行索引没有价值 。
高效SQL编写规范建议
1、大批量插入数据
如果同时执行大量的插入，建议使用多个值的INSERT语句(方法二) 。这比使用分开INSERT语句快（方法一），一般情况下批量插入效率有几倍的差别 。
方法一：
insert into tablename values(1,2);
insert into tablename values(1,3);
insert into tablename values(1,4);


推荐阅读


上一篇：利用树莓派搭建个人服务器

下一篇：上海检验检疫局保障进出口茶叶质量安全综述