MySQL索引优化

一、应用索引进行查询

　　1、索引的应用

　　2、利用索引进行排序

　　　　① MySQL中，有两种方式生成有序结果集：一是使用filesort（慢查询），二是按索引顺序扫描

　　　　② 当索引的顺序与order by中的列顺序相同且所有的列是同一方向（全部升序或全部降序）时，可以使用索引来排序

　　　　③ 如果查询是连接多个表，仅当order by中的所有列都是第一个表的列时才会使用索引，其他情况都会使用filesort。

　　　　④ 当查询排序次数非常多，建议不在MySQL中做排序，可以放到Java中

　　3、数据准备：

　　4、检测是否使用索引：

　　5、注：不是索引越多越好

二、为索引列选择合适的数据类型

　　1、越小的数据类型通常更好：越小的数据类型通常在磁盘、内存和CPU缓存中都需要更少的空间，处理起来更快

　　　　VARCHAR(1000)　　INT(10)  ✔

　　2、简单的数据类型更好：整型数据比起字符，处理开销更小，因为字符串的比较复杂。IP的存储问题

　　3、主键整型最好

　　4、尽量避免NULL，含有NULL的列做统计和查询优化很麻烦

三、一般原则

　　1、有大量重复值、经常进行范围查询（=、>、<、>=、<=、between、in）和order by、group by发生的列，可考虑建立聚簇索引。主键的范围查询有走索引，非主键的范围查询不走索引

　　2、经常同时存取多列，且每列都含有重复值可考虑建立组合索引，其前导列一定是使用最频繁的列

　　3、索引不会包含有NULL值的列。只要列中包含有NULL值都将不会被包含在索引中，复合索引中只要有一列含有NULL值，那么这一列对于此复合索引就是无效的。所以在数据库设计时不要让字段的默认值为NULL

　　4、尽量使用短索引。对字符串列进行索引，如果可能应该指定一个前缀长度。例如有一个CHAR(255)的列，如果在前10个或20个字符内，多数值时惟一的，那么就不要对整个列进行索引。短索引不仅可以提高查询速度，而且可以节省磁盘空间和I/O操作

　　5、索引列排序。MYSQL查询只使用一个索引，因此如果where子句中已经使用了索引的话，那么order by中的列是不会使用索引的。因此数据库默认排序可以符合要求的情况下不要使用排序操作；尽量不要包含多个列的排序，如果需要最好给这些列创建复合索引

　　6、like语句操作。一般情况下不鼓励使用like操作，如果非使用不可，如果使用也是一个问题。like “%aaa%”不会使用索引，而like “aaa%”可以使用索引

　　7、尽量不要在列上进行运算。如select * from users where YEAR(adddate) < 2007，将在每个行上进行运算，这将导致索引失效而进行全表扫描，因此可以改成：select * from users where adddate < ‘2007-01-01’

 四、虽然建了索引，但是不会走索引的情况

　　1、存在NULL值

　　2、NOT条件：包括<>、NOT、IN、not exists

　　3、like通配符的后匹配

　　4、函数运算

　　　　select * from test where upper(name) = 'ZHANGSHAN'

　　可以改为：select * from test where name =upper( 'ZHANGSHAN')

　　5、数据类型的转换：当查询条件存在隐式转换时，索引会失效，比如在数据库里id村的int类型，但在查询时，却用了下面的形式：

　　　　select * from test where id = ‘123’

　　6、复合索引前导列区分不大，如性别，取值只有两个