【mysql学习第12天】索引的优化示例
根据存储介质的不同，可以将数据库分为基于磁盘的数据库系统、基于内存的数据库系统，以及混合型数据库系统。
基于磁盘的数据库系统（disk-base database）是最为常见的一种关系型数据库，比如MySQL、Oracle、SQL Server、DB2数据库。
基于磁盘的数据库的缺点毫无疑问，基于内存的数据库系统是最快的，因为数据库不需要对磁盘进行操作。磁盘的速度要远慢于内存的速度。
改进方法因此基于磁盘的数据库系统一般都有缓冲池，即一块内存区域，其作用是将从磁盘上读取的指定大小数据——称为页（或块），放入缓冲池。当再次读取时，数据库首先判断该页是否在缓冲池中，如果在则直接读取缓冲池中的页，如果不在则读取磁盘上的页。对于写操作，数据库将页读入缓冲池，然后在缓冲池中对页进行修改，修改完成后的页一般被异步地写入磁盘上。
对于缓冲池的维护一般采用最近最少使用（Least Recently Used, LRU）算法。由此可见，缓冲池的大小决定了数据库的性能。若数据库中的数据可以完全存放于缓冲池中，则可以认为这时数据库的性能是最优的。除了同步/异步的写磁盘操作外，所有其他操作都可以在内存中完成。
对于MySQL数据库系统，由于其有着各种不同的存储引擎，因此其缓冲池是基于存储引擎的，也就是说每个存储引擎都有自己的缓冲池。
缓存池的大小决定因素对于MyISAM存储引擎来说，变量key_buffer_size决定了缓冲池的大小。
对于InnoDB存储引擎来说，变量innodb_buffer_pool_size决定了缓冲池的大小。
应用索引进行查询具体示例 1、索引的应用 2、利用索引进行排序MySQL 中，有两种方式生成有序结果集：一是使用 filesort，二是按索引顺序扫描。
当索引的顺序与 ORDER BY 中的列顺序相同且所有的列是同一方向(全部升序或者全部降序)时，可以使用索引来排序。
如果查询是连接多个表，仅当ORDER BY 中的所有列都是第一个表的列时才会使用索引，其它情况都会使用 filesort。
当查查询排序次数非常多，建议不再MySQL中做排序，可以放到Java中。
例：
create table index2(
id int unsigned NOT NULL AUTO_INCREMENT,
name varchar(16) NOT NULL DEFAULT '',
password varchar(16) NOT NULL DEFAULT '',
PRIMARY KEY(id),
INDEX (name)
) ENGINE=InnoDB;
insert into index2(name,password) values('cat01','1234567')
insert into index2(name,password) values('cat02','1234567');
insert into index2(name,password) values('ddddd','1234567');
insert into index2(name,password) values('aaaaa','1234567');
检测是否使用索引
explain select id from actor order by id;
explain select id from actor order by password;
explain select id from actor order by name;
注：不是索引越多越好。
优化索引 1、为索引列选择合适的数据类型越小的数据类型通常更好：越小的数据类型通常在磁盘、内存和 CPU 缓存中都需要更少的空间，处理起来更快。
VARCHAR(1000)
int(10)
简单的数据类型更好：整型数据比起字符，处理开销更小，因为字符串的比较更复杂。IP的存储问题。
主键整型最好。
尽量避免 NULL，含有NULL的列做统计和查询优化很麻烦。
IFNULL函数
2、一般原则（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′。
虽然建了索引，但是不会走索引的情况在某些情况下，当执行EXPLAIN时，会发现优化器并没有选择索引去查找数据，而是通过扫描聚集索引，也就是直接进行全表的扫描来得到数据。这种情况多发生于范围查找、JOIN操作等
示例如下：
1、存在NULL值
select * from test where id is not null;
2、NOT条件
包括：、NOT、in、not exists
select * from test where id in (1,2,3,4,5);
3、LIKE通配符的后匹配
4、函数运算
select * from test where upper(name)='SUNYANG';
可以改为：
select * from test where name=upper('sunyang');
5、数据类型的转换
当查询条件存在隐式转换时，索引会失效。比如在数据库里id存的int类型，但是在查询时，却用了下面的形式：
select * from sunyang where id='123';
6、复合索引前导列区分不大
参考
https://www.cnblogs.com/ggjucheng/archive/2012/11/11/2765237.html
强制使用某个索引因此对于不能进行索引覆盖的情况，优化器选择辅助索引的情况是，通过辅助索引查找的数据是少量的，这是由当前传统机械硬盘的特性所决定，即利用顺序读取来替换随机读取的查找。若用户使用的磁盘是固态硬盘，随机读取操作非常快，同时有足够的自信来确认使用辅助索引可以带来更好的性能，那么可以使用关键字FORCE INDEX来强制使用某个索引
SELECT*FROM orderdetails FORCE INDEX（OrderID） WHERE orderid＞10000 and orderid＜102000；
作者：星痕落雪
