数据库查询注意事项及索引使用

一、Navicat数据库查询注意事项

由于需要统计生产环境的数据，按月份统计的话大概是几十万一条，全部月份加起来几百万条数据，被我搞挂了。。。原因就是在关联查询时候，查询字段没有用索引字段，导致数据一直处于查询状态，无数次的点击停止按钮，却发现根本停不下来。最后不得已把进程关了，然后悲剧就来了，我改了sql，加了索引字段，也查不出数据了。只好让同事帮忙重启数据库服务。

此次事故让我了解到索引的重要性。同时还要注意以下几点：

多表关联使用小表关联大表
查询条件要使用加了索引的字段(查看一张表哪些字段加了索引：show index from table_name)
不要使用select *
使用join来代替子查询，join的效率更高，因为MySql不需要再内存中创建临时表。

二、索引

索引是对数据库表中一列或多列的值进行排序的一种结构，使用索引可快速访问数据库表中的特定信息。
其实是根据我们的数据库结构抽取的另外一个映射我们数据类型的数据结构，一般实现了B-Tree或B+查询算法来快速定位到我们数据库的信息的结构，速度快，但并不是所有的查询都用索引，这个需要根据实际场景业务需求而定！

1. 索引的种类

普通索引(INDEX)
唯一索引(UNIQUE)
主键索引(PRIMARY)
组合索引
全文索引(FULLTEXT)

1)普通索引

首先介绍一下普通索引
普通索引就是一般的索引，没有什么特殊的地方。创建语句如下：

//直接创建索引
create index index_name ON tableName(column(length))

//修改表结构的时候创建索引
ALTER TABLE table_name ADD INDEX index_name ON (column(length))

//创建表的时候同时创建索引
CREATE TABLE `table` (
`id` int(11) NOT NULL AUTO_INCREMENT ,
`name` char(255) CHARACTER NOT NULL ,
PRIMARY KEY (`id`),
INDEX index_name (name(length))
)

单列索引无法存储null值，因为索引是有序的，将null放入索引，无法确定其位置在哪

2)唯一索引

唯一索引与普通索引大致相同，但是唯一索引可以允许null值，列值必须唯一，如果是组合索引的话，则列值的组合必须唯一。创建语句与普通索引一直，只要在index前面加上unique。

create unique index index_name ON tableName(column(length))

3)主键索引

一种特殊的唯一索引，一个表只能有一个主键，不允许有控制，一般在创建表的时候同时创建索引。

CREATE TABLE `table` (
`id` int(11) NOT NULL AUTO_INCREMENT ,
`title` char(255) NOT NULL ,
PRIMARY KEY (`id`)
);

4)组合索引

指多个字段上创建的索引

create index idx_name_age ON `user`(name, age)

只有在查询条件中使用了创建索引时的第一个字段，索引才会被使用。使用组合索引时遵循最左前缀集合。
如果没有使用首个字段作为创建索引字段，则不会使用索引。

explain select * from user where and age = '10' and detail = '详情'

数据库查询注意事项及索引使用

5)全文索引

主要用来查找文本中的关键字，而不是直接与索引中的值相比较。fulltext索引跟其它索引大不相同，它更像是一个搜索引擎，而不是简单的where语句的参数匹配。fulltext索引配合match against操作使用，而不是一般的where语句加like。它可以在create table，alter table ，create index使用，不过目前只有char、varchar，text 列上可以创建全文索引。值得一提的是，在数据量较大时候，现将数据放入一个没有全局索引的表中，然后再用CREATE index创建fulltext索引，要比先为一张表建立fulltext然后再将数据写入的速度快很多。

//创建标的时候添加全文索引
CREATE TABLE `table` (
`id` int(11) NOT NULL AUTO_INCREMENT ,
`content` text CHARACTER NULL ,
PRIMARY KEY (`id`),
FULLTEXT (content)
);

//修改表结构添加全文索引
ALTER TABLE article ADD FULLTEXT index_content(content)

//直接创建索引
CREATE FULLTEXT INDEX index_content ON article(content)

2.索引的查看与销毁

查看某一张表的索引信息，可以使用

show index from table_name

数据库查询注意事项及索引使用

查看一条语句使用了索引，只需要在select前面加explain关键字就行

explain select * from user where name = '张三' and age = '10'

数据库查询注意事项及索引使用

销毁一个索引使用命令：

drop index idx_name_age ON table_name

3.索引的缺点

虽然索引大大提高了查询速度，同时却会降低更新表的速度，如对表进行insert、update和delete。因为更新表时，不仅要保存数据，还要保存一下索引文件。
建立索引会占用磁盘空间的索引文件。一般情况这个问题不太严重，但如果你在一个大表上创建了多种组合索引，索引文件的会增长很快。
索引只是提高效率的一个因素，如果有大数据量的表，就需要花时间研究建立最优秀的索引，或优化查询语句。

4.索引的失效情况

绝大多数情况下，使用索引可以提高查询的速度，但是sql使用不恰当的话，索引将无法发挥作用。具体涉及以下几个方面：

索引不会包含有null值的列
只要列中包含有null值都将不会被包含在索引中，复合索引中只要有一列含有null值，那么这一列对于此复合索引就是无效的。所以我们在数据库设计时不要让字段的默认值为null。还有一点需要主要的是，如果使用count统计，会忽略null值，可能导致计数出现误差，应该将null设为0或者空字符串保存。
使用短索引
对串列进行索引，如果可能应该指定一个前缀长度。例如，如果有一个char(255)的列，如果在前10个或20个字符内，多数值是惟一的，那么就不要对整个列进行索引。短索引不仅可以提高查询速度而且可以节省磁盘空间和I/O操作。
索引列排序
查询只使用一个索引，因此如果where子句中已经使用了索引的话，那么order by中的列是不会使用索引的。因此数据库默认排序可以符合要求的情况下不要使用排序操作；尽量不要包含多个列的排序，如果需要最好给这些列创建复合索引。
like语句操作
一般情况下不推荐使用like操作，如果非使用不可，如何使用也是一个问题。like “%aaa%” 不会使用索引而like “aaa%”可以使用索引。
不要在列上进行运算
这将导致索引失效而进行全表扫描，例如:

SELECT * FROM table_name WHERE YEAR(column_name)<2017;
不使用not in和<>操作
使用组合索引的时候遵循最左前缀集合，要以首字段开始，不能中断，否则索引无效。
s