数据库索引优化查询速度

最近做一个向交警那边推送违章过车数据的项目，但是我们这边的元数据的数据量都是很大的，几亿的数据，所以查询速度不理想，但是加上索引，效果就不一样了。

查看某张表的索引：

1.show index from 表名称;
  show index from attach;
2.在navicate中点击设计表，然后点击索引选项

添加单列索引

alter table 表名称 add index 索引名称 (字段)；
例如：在attach表中向TYPE字段中添加一个索引，索引的名称为type_index
 alter table attach add index type_index(TYPE);

添加组合索引

使用场景：如果在where 后面查询条件很多，但是我们为了提高查询速度把where条件中的字段添加组合索引
例如：在traffic_record表中向TOTAL_WEIGHT和PUSH_STATE中建立一个组合索引，名称为totalweight_pushstatus_index
alter table traffic_record add index totalweight_pushstatus_index(TOTAL_WEIGHT,PUSH_STATE);

使用索引

一般如果指定的话，就直接使用use index(索引名称即可)如下：
SELECT traffic_record.RECORD_ID,traffic_record.CAPTURE_TIME,traffic_record.LANE,traffic_record.CAR_NO,traffic_record.CAR_NO_COLOR,
        traffic_record.CAR_NO_TYPE,traffic_record.SPEED,traffic_record.CAR_TYPE,attach.ATTA_ID,attach.URL
        FROM traffic_record
        use INDEX(totalweight_pushstatus_index)
        INNER JOIN  attach  ON traffic_record.RECORD_ID=attach.DOC_ID 
        WHERE  traffic_record.PUSH_STATE=0  AND traffic_record.TOTAL_WEIGHT > 49 AND attach.TYPE=0 
也可以不指定 ，默认也可以走索引

注意

1.如果是多表查询，例如inner join查询，但是在查询条件中涉及到另外一张表，这时候我们只要把设计到另外一张表的那个字段
  建立一个索引就可以了，不能跨表建立索引。例如以上语句中AND attach.TYPE=0这个语句，不能用TYPE这个字段和其他字段建立一个组合索引，因为不能跨表，那就算了，直接在attach表的TYPE字段建立一个单列索引也是可以的，至少可以提高速度。
2.一般where条件后面的条件是有执行顺序的，mysql从上到下，从左到右的执行。所以一般我们在写语句的时候筛选性很大的条件
  写在最左边，然后依次。
3.索引和数据库的引擎有关系，数据库的引擎有innodb和myism引擎。

以下部分来源：https://www.cnblogs.com/ldms/p/3708906.html
索引是快速搜索的关键。MySQL索引的建立对于MySQL的高效运行是很重要的。下面介绍几种常见的MySQL索引类型。

在数据库表中，对字段建立索引可以大大提高查询速度。假如我们创建了一个 mytable表：

CREATE TABLE mytable( ID INT NOT NULL, username VARCHAR(16) NOT NULL ); 我们随机向里面插入了10000条记录，其中有一条：5555, admin。

在查找username="admin"的记录 SELECT * FROM mytable WHERE username='admin';时，如果在username上已经建立了索引，MySQL无须任何扫描，即准确可找到该记录。相反，MySQL会扫描 所有记录，即要查询10000条记录。

索引分单列索引和组合索引。单列索引，即一个索引只包含单个列，一个表可以有多个单列索引，但这不是组合索引。组合索引，即一个索包含多个列。

MySQL索引类型包括：

（1）普通索引

这是最基本的索引，它没有任何限制。它有以下几种创建方式：

◆创建索引

CREATE INDEX indexName ON mytable(username(length)); 如果是CHAR，VARCHAR类型，length可以小于字段实际长度；如果是BLOB和TEXT类型，必须指定 length，下同。

◆修改表结构

ALTER mytable ADD INDEX [indexName] ON (username(length))
◆创建表的时候直接指定

CREATE TABLE mytable(   ID INT NOT NULL,    username VARCHAR(16) NOT NULL,   INDEX [indexName] (username(length))   );  删除索引的语法：

DROP INDEX [indexName] ON mytable;

（2）唯一索引

它与前面的普通索引类似，不同的就是：索引列的值必须唯一，但允许有空值。如果是组合索引，则列值的组合必须唯一。它有以下几种创建方式：

◆创建索引

CREATE UNIQUE INDEX indexName ON mytable(username(length)) 
◆修改表结构

ALTER mytable ADD UNIQUE [indexName] ON (username(length))
 ◆创建表的时候直接指定

CREATE TABLE mytable(   ID INT NOT NULL,    username VARCHAR(16) NOT NULL,   UNIQUE [indexName] (username(length))   ); 

（3）主键索引

它是一种特殊的唯一索引，不允许有空值。一般是在建表的时候同时创建主键索引：

CREATE TABLE mytable( ID INT NOT NULL, username VARCHAR(16) NOT NULL, PRIMARY KEY(ID) ); 当然也可以用 ALTER 命令。记住：一个表只能有一个主键。

（4）组合索引

为了形象地对比单列索引和组合索引，为表添加多个字段：

CREATE TABLE mytable(   ID INT NOT NULL,    username VARCHAR(16) NOT NULL,   city VARCHAR(50) NOT NULL,   age INT NOT NULL  );  为了进一步榨取MySQL的效率，就要考虑建立组合索引。就是将 name, city, age建到一个索引里：

ALTER TABLE mytable ADD INDEX name_city_age (name(10),city,age); 建表时，usernname长度为 16，这里用 10。这是因为一般情况下名字的长度不会超过10，这样会加速索引查询速度，还会减少索引文件的大小，提高INSERT的更新速度。

如果分别在 usernname，city，age上建立单列索引，让该表有3个单列索引，查询时和上述的组合索引效率也会大不一样，远远低于我们的组合索引。虽然此时有了三个索引，但MySQL只能用到其中的那个它认为似乎是最有效率的单列索引。

建立这样的组合索引，其实是相当于分别建立了下面三组组合索引：

usernname,city,age   usernname,city   usernname  为什么没有 city，age这样的组合索引呢？这是因为MySQL组合索引“最左前缀”的结果。简单的理解就是只从最左面的开始组合。并不是只要包含这三列的查询都 会用到该组合索引，下面的几个SQL就会用到这个组合索引：

SELECT * FROM mytable WHREE username="admin" AND city="郑州"  SELECT * FROM mytable WHREE username="admin" 而下面几个则不会用到：

SELECT * FROM mytable WHREE age=20 AND city="郑州"  SELECT * FROM mytable WHREE city="郑州"

（5）建立索引的时机

到这里我们已经学会了建立索引，那么我们需要在什么情况下建立索引呢？一般来说，在WHERE和JOIN中出现的列需要建立索引，但也不完全如此， 因为MySQL只对<，<=，=，>，>=，BETWEEN，IN，以及某些时候的LIKE才会使用索引。例如：

SELECT t.Name FROM mytable t LEFT JOIN mytable m ON t.Name=m.username WHERE m.age=20 AND m.city='郑州' 此时就需要对city和age建立索引，由于mytable表的userame也出现在了JOIN子句中，也有对它建立索引的必要。

刚才提到只有某些时候的LIKE才需建立索引。因为在以通配符%和_开头作查询时，MySQL不会使用索引。例如下句会使用索引：

SELECT * FROM mytable WHERE username like'admin%' 而下句就不会使用：

SELECT * FROM mytable WHEREt Name like'%admin' 因此，在使用LIKE时应注意以上的区别。

（6）索引的不足之处

上面都在说使用索引的好处，但过多的使用索引将会造成滥用。因此索引也会有它的缺点：

◆虽然索引大大提高了查询速度，同时却会降低更新表的速度，如对表进行INSERT、UPDATE和DELETE。因为更新表时，MySQL不仅要保存数据，还要保存一下索引文件。

◆建立索引会占用磁盘空间的索引文件。一般情况这个问题不太严重，但如果你在一个大表上创建了多种组合索引，索引文件的会膨胀很快。

索引只是提高效率的一个因素，如果你的MySQL有大数据量的表，就需要花时间研究建立最优秀的索引，或优化查询语句。

（7）使用索引的注意事项

使用索引时，有以下一些技巧和注意事项：

◆索引不会包含有NULL值的列

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

◆使用短索引

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

◆索引列排序

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

◆like语句操作

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

◆不要在列上进行运算

select * from users where YEAR(adddate)<2007; 将在每个行上进行运算，这将导致索引失效而进行全表扫描，因此我们可以改成

select * from users where adddate<‘2007-01-01’; 

◆不使用NOT IN和<>操作

以上，就对其中MySQL索引类型进行了介绍。

1.前言
在web开发中， 页面模板，业务逻辑(包括缓存、连接池)和数据库这三个部分，数据库在其中负责执行SQL查询并返回查询结果，是影响网站速度最重要的性能瓶颈。本文主要 针对MySql数据库，双十一的电商大战，引发了淘宝技术热议，而淘宝现在去IOE(I代表IBM的缩写，即去IBM的存储设备和小型机;O是代表 Oracle的缩写，也即去Oracle数据库，采用MySQL和Hadoop替代的解决方案，;E是代表EMC2,即去EMC2的设备性,用PC Server替代EMC2)，大量采用MySql集群！让MySql再次成为耀眼的明星！而优化数据的重要一步就是索引的建立，对于mysql中出现的慢 查询，我们可以通过使用索引来提升查询速度。索引用于快速找出在某个列中有一特定值的行。不使用索引，MySQL将进行全表扫描，从第1条记录开始然后读 完整个表直到找出相关的行。

2.mysql索引类型及创建
常用的索引类型有

(1)主键索引
它是一种特殊的唯一索引，不允许有空值。一般是在建表的时候同时创建主键索引：

复制代码 代码如下:

CREATE TABLE user(
id int unsigned not null auto_increment,
name varchar(50) not null,
email varchar(40) not null,
primary key (id)
);

(2)普通索引
这是最基本的索引，它没有任何限制。创建方式：

复制代码 代码如下:

create index idx_name on user(
name(20)
);

mysql支持前缀索引，一般姓名不会超过20个字符，所以我们这里建立索引的时候限定了长度20，这样可以节省索引文件大小

(3)唯一索引
它与前面的普通索引类似，不同的就是：索引列的值必须唯一，但允许有空值。如果是组合索引，则列值的组合必须唯一。创建方式：

复制代码 代码如下:

CREATE UNIQUE INDEX idx_email ON user(
email
);

(4)全文索引
MySQL支持全文索引和搜索功能。MySQL中的全文索引类型为FULLTEXT的索引。 FULLTEXT 索引仅可用于 MyISAM表；

复制代码 代码如下:

CREATE TABLE articles (
   id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,
   title VARCHAR(200),
   body TEXT,
   FULLTEXT (title,body)
    );

mysql> SELECT * FROM articles WHERE MATCH (title,body) AGAINST ('database');

查询结果：
+----+-------------------+------------------------------------------+
| id | title             | body                                     |
+----+-------------------+------------------------------------------+
|  5 | MySQL vs. YourSQL | In the following database comparison ... |
|  1 | MySQL Tutorial    | DBMS stands for DataBase ...             |
+----+-------------------+------------------------------------------+
2 rows in set (0.00 sec)
MATCH() 函数对于一个字符串执行资料库内的自然语言搜索。一个资料库就是1套1个或2个包含在FULLTEXT内的列。搜索字符串作为对AGAINST()的参数 而被给定。对于表中的每一行, MATCH() 返回一个相关值，即, 搜索字符串和 MATCH()表中指定列中该行文字之间的一个相似性度量。

(5)复合索引

复制代码 代码如下:

CREATE TABLE test (
    id INT NOT NULL,
    last_name CHAR(30) NOT NULL,
    first_name CHAR(30) NOT NULL,
    PRIMARY KEY (id),
    INDEX name (last_name,first_name)
);

name索引是一个对last_name和first_name的索引。索引可以用于为last_name，或者为last_name和first_name在已知范围内指定值的查询。因此，name索引用于下面的查询：
SELECT * FROM test WHERE last_name='Widenius';
SELECT * FROM test WHERE last_name='Widenius' AND first_name='Michael';
但是不能用于SELECT * FROM test WHERE first_name='Michael';这是因为MySQL组合索引为“最左前缀”的结果,简单的理解就是只从最左面的开始组合。

3.在什么情况下使用索引
(1)为搜索字段建索引,如果在你的表中，某个字段你经常用来做搜索，那么，请为其建立索引吧。一般来说，在WHERE和JOIN中出现的列需要建立索引以提高查询速度。
例如从fps表（表中有name字段）中检索姓名为"李武"的人，
下面用explain来解释执行建立索引和未建立索引的区别：

a.未建立索引前

复制代码 代码如下:

explain select name from fps where name="李武";

[SQL] select name from fps where name="李武";
影响的数据栏: 0
时间: 0.003ms
b.建立索引后

复制代码 代码如下:

create index idx_name on fps(
name
);

explain select name from fps where name="李武";

[SQL] select name from fps where name="李武";

影响的数据栏: 0
时间: 0.001ms

(2)下面我们就来看看这个EXPLAIN分析结果的含义。
table：这是表的名字。
type：连接操作的类型。下面是MySQL文档关于ref连接类型的说明：
“对于每个来自于前面的表的行组合，所有有匹配索引值的行将从这张表中读取。如果联接只使用键的最左边的前缀，或如果键不是
UNIQUE或PRIMARY KEY（换句话说，如果联接不能基于关键字选择单个行的话），则使用ref。如果使用的键仅仅匹配少量行，该联接
类 型是不错的。” 在本例中，由于索引不是UNIQUE类型，ref是我们能够得到的最好连接类型。 如果EXPLAIN显示连接类型是“ALL”，而且你并不想从表里面选择出大多数记录，那么MySQL的操作效率将非常低，因为它要扫描整个表。你可以加 入更多的索引来解决这个问题。预知更多信息，请参见MySQL的手册说明。
possible_keys：
可能可以利用的索引的名字。这里的索引名字是创建索引时指定的索引昵称；如果索引没有昵称，则默认显示的是索引中第一个列的名字
（在本例中，它是“idx_name”）。
Key：
它显示了MySQL实际使用的索引的名字。如果它为空（或NULL），则MySQL不使用索引。
key_len：
索引中被使用部分的长度，以字节计。
ref：
它显示的是列的名字（或单词“const”），MySQL将根据这些列来选择行。在本例中，MySQL根据三个常量选择行。
rows：
MySQL所认为的它在找到正确的结果之前必须扫描的记录数。显然，这里最理想的数字就是1。 本例中未索引前遍历的记录数为1041，而建立索引后为1
Extra：
这里可能出现许多不同的选项，其中大多数将对查询产生负面影响。在本例中，MySQL只是提醒我们它将用using where，using index子句限制搜索结果集。

4.最常用的存储引擎：
(1)Myisam存储引擎：每 个Myisam在磁盘上存储成三个文件。文件名都和表名相同，扩展名分别为.frm（存储表定义）、.MYD（存储数据）、.MYI（存储索引）。数据文 件和索引文件可以放置在不同目录，平均分布io，获得更快的速度。对存储大小没有限制，MySQL数据库的最大有效表尺寸通常是由操作系统对文件大小的限 制决定的,
(2)InnoDB存储引擎：具有提交、回滚、奔溃恢复能力的事务安全。与Myisam相比，InnoDB的写效率差一些并且会占用更多的磁盘空间以保留数据和索引。
(3)如何选择合适的引擎
下面是常用存储引擎适用的环境：
Myisam：它是在Web、数据仓储和其他应用环境下最常使用的存储引擎;
InnoDB：用于事务处理应用程序，具有更多特性，包括ACID事务特性。

EXPLAIN

先看一下arena_match_index的表结构，大家注意表的索引结构
CREATE TABLE arena_match_index (
tid int(10) unsigned NOT NULL DEFAULT '0',
mid int(10) unsigned NOT NULL DEFAULT '0',
group int(10) unsigned NOT NULL DEFAULT '0',
round tinyint(3) unsigned NOT NULL DEFAULT '0',
day date NOT NULL DEFAULT '0000-00-00',
begintime datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
UNIQUE KEY tm (tid,mid),
KEY mid (mid),
KEY begintime (begintime),
KEY dg (day,group),
KEY td (tid,day)
) ENGINE=MyISAM DEFAULT CHARSET=utf8

接着看下面的sql：
SELECT round FROM arena_match_index WHERE day = '2010-12-31' AND group = 18 AND begintime < '2010-12-31 12:14:28' order by begintime LIMIT 1;
这条sql的查询条件显示可能使用的索引有begintime和dg，但是由于使用了order by begintime排序mysql最后选择使用begintime索引，explain的结果为：

mysql> explain SELECT round FROM arena_match_index WHERE day = '2010-12-31' AND group = 18 AND begintime < '2010-12-31 12:14:28' order by begintime LIMIT 1;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	arena_match_index	range	begintime,dg	begintime	8	NULL	226480	Using where

explain的结果显示使用begintime索引要扫描22w条记录，这样的查询性能是非常糟糕的，实际的执行情况也是初次执行（还未有缓存数据时）时需要30秒以上的时间。

实际上这个查询使用dg联合索引的性能更好，因为同一天同一个小组内也就几十场比赛，因此应该优先使用dg索引定位到匹配的数据集合再进行排序，那么如何告诉mysql使用指定索引呢？使用use index语句：

mysql> explain SELECT round FROM arena_match_index use index (dg) WHERE day = '2010-12-31' AND group = 18 AND begintime < '2010-12-31 12:14:28' order by begintime LIMIT 1;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	arena_match_index	ref	dg	dg	7	const,const	757	Using where; Using filesort

explain结果显示使用dg联合索引只需要扫描757条数据，性能直接提升了上百倍，实际的执行情况也是几乎立即就返回了查询结果。

在最初的查询语句中只要把order by begintime去掉，mysql就会使用dg索引了，再次印证了order by会影响mysql的索引选择策略！
mysql> explain SELECT round FROM arena_match_index WHERE day = '2010-12-31' AND group = 18 AND begintime < '2010-12-31 12:14:28' LIMIT 1;