SQL语句优化、mysql不走索引的原因、数据库索引的设计原则

SQL语句优化                                                       

1 企业SQL优化思路

　　1、把一个大的不使用索引的SQL语句按照功能进行拆分

　　2、长的SQL语句无法使用索引，能不能变成2条短的SQL语句让它分别使用上索引。

　　3、对SQL语句功能的拆分和修改

　　4、减少“烂”SQL由运维（DBA）和开发交流（确认），共同确定如何改，最终由DBA执行

　　5、制定开发流程

2 不适合走索引的场景

　　1、唯一值少的列上不适合建立索引或者建立索引效率低。例如：性别列

　　2、小表可以不建立索引，100条记录。

　　3、对于数据仓库，大量全表扫描的情况，建索引反而会慢

3 查看表的唯一值数量

select count(distinct user) from mysql.user;
select count(distinct user,host) from mysql.user;

4 建立索引流程

　　1、找到慢SQL。

show processlist;

　　　　记录慢查询日志。

　　2、explain select句,条件列多。

　　3、查看表的唯一值数量：

select count(distinct user) from mysql.user;
select count(distinct user,host) from mysql.user;

　　　　条件列多。可以考虑建立联合索引。

　　4、建立索引(流量低谷)

force index

　　5、拆开语句（和开发）。

　　6、like '%%'不用mysql

　　7、进行判断重复的行数

查看行数:

mysql> select count(*) from city;
+----------+
| count(*) |
+----------+
|     4079 |
+----------+
1 row in set (0.00 sec)

查看去重后的行数：

mysql> select count(distinct countrycode) from city;
+-----------------------------+
| count(distinct countrycode) |
+-----------------------------+
|                         232 |
+-----------------------------+
1 row in set (0.00 sec)

 

mysql不走索引的原因                                

1 一些常见的原因

　　1) 没有查询条件，或者查询条件没有建立索引

　　2) 在查询条件上没有使用引导列

　　3) 查询的数量是大表的大部分，应该是30％以上。

　　4) 索引本身失效

　　5) 查询条件使用函数在索引列上，或者对索引列进行运算，运算包括(+，-，*，/，! 等)

　　　　错误的例子：select * from test where id-1=9; 正确的例子：select * from test where id=10;

　　6) 对小表查询

　　7) 提示不使用索引

　　8) 统计数据不真实

　　9) CBO计算走索引花费过大的情况。其实也包含了上面的情况，这里指的是表占有的block要比索引小。

　　10)隐式转换导致索引失效.这一点应当引起重视.也是开发中经常会犯的错误.

　　　　由于表的字段tel_num定义为varchar2(20),但在查询时把该字段作为number类型以where条件传给数据库,这样会导致索引失效.

　　　　　　错误的例子：select * from test where tel_nume=13333333333;

　　　　　　正确的例子：select * from test where tel_nume='13333333333';

　　11) 注意使用的特殊符号

    　　　　1,<>  ,!=

  　　　　  2,单独的>,<,(有时会用到，有时不会)

　　12)like "%_" 百分号在前.

　　　　　　select * from t1  where name like 'linux培训%';

　　13) not in ,not exist.

　　14)  in  尽量改成 union 。

　　15)当变量采用的是times变量，而表的字段采用的是date变量时.或相反情况。

　　16)B-tree索引is null不会走,is not null会走,位图索引 is null,is not null 都会走 。

　　17)联合索引 is not null 只要在建立的索引列（不分先后）都会走,

　　in null时 必须要和建立索引第一列一起使用,当建立索引第一位置条件是is null 时,其他建立索引的列可以是is null（但必须在所有列 都满足is null的时候）,或者=一个值；

　　当建立索引的第一位置是=一个值时,其他索引列可以是任何情况（包括is null =一个值）,以上两种情况索引都会走。其他情况不会走。

2 需要注意的一些

1)    MyISAM 存储引擎索引键长度总和不能超过1000 字节；
2)    BLOB 和TEXT 类型的列只能创建前缀索引；
3)    MySQL 目前不支持函数索引；
4)    使用不等于（!= 或者<>）的时候MySQL 无法使用索引；
5)    过滤字段使用了函数运算后（如abs(column)），MySQL 无法使用索引；
6)    Join 语句中Join 条件字段类型不一致的时候MySQL 无法使用索引；
7)    使用LIKE 操作的时候如果条件以通配符开始（ '%abc...'）MySQL 无法使用索引；
8)    使用非等值查询的时候MySQL 无法使用Hash 索引；
9)    在我们使用索引的时候，需要注意上面的这些限制，尤其是要注意无法使用索引的情况，因为这很容易让我们因为疏忽而造成极大的性能隐患。

 

---

 

 

 数据库索引的设计原则                                                   

　　为了使索引的使用效率更高，在创建索引时，必须考虑在哪些字段上创建索引和创建什么类型的索引。

1 那么索引设计原则又是怎样的

1．选择唯一性索引

　　唯一性索引的值是唯一的，可以更快速的通过该索引来确定某条记录。

　　例如，学生表中学号是具有唯一性的字段。为该字段建立唯一性索引可以很快的确定某个学生的信息。如果使用姓名的话，可能存在同名现象，从而降低查询速度。

2．为经常需要排序、分组和联合操作的字段建立索引

　　经常需要ORDER BY、GROUP BY、DISTINCT和UNION等操作的字段，排序操作会浪费很多时间。

如果为其建立索引，可以有效地避免排序操作。

3．为常作为查询条件的字段建立索引

　　如果某个字段经常用来做查询条件，那么该字段的查询速度会影响整个表的查询速度。因此，

　　为这样的字段建立索引，可以提高整个表的查询速度。

4．限制索引的数目

　　索引的数目不是越多越好。每个索引都需要占用磁盘空间，索引越多，需要的磁盘空间就越大。修改表时，对索引的重构和更新很麻烦。越多的索引，会使更新表变得很浪费时间。

5．尽量使用数据量少的索引

　　如果索引的值很长，那么查询的速度会受到影响。例如，对一个CHAR（100）类型的字段进行全文检索需要的时间肯定要比对CHAR（10）类型的字段需要的时间要多。

6．尽量使用前缀来索引

　　如果索引字段的值很长，最好使用值的前缀来索引。例如，TEXT和BLOG类型的字段，进行全文检索会很浪费时间。如果只检索字段的前面的若干个字符，这样可以提高检索速度。

7．删除不再使用或者很少使用的索引

　　表中的数据被大量更新，或者数据的使用方式被改变后，原有的一些索引可能不再需要。数据库管理员应当定期找出这些索引，将它们删除，从而减少索引对更新操作的影响。

8.小表不应建立索引

　　包含大量的列并且不需要搜索非空值的时候可以考虑不建索引