普通mysql运行,数据量和访问量不大的话,是足够快的,但是当数据量和访问量剧增的时候,那么就会明显发现MySQL很慢,甚至down掉,那么就要考虑优化我们的mysql了。其中优化mysql的一个重要环节就是为数据库建立正确合理的索引。如果没有索引,执行查询时mysql必须从第一个记录开始扫描整个表的所有记录,直至找到符合要求的记录。表里面的记录数量越多,这个操作的代价就越高。如果作为搜索条件的列上已经创建了索引,mysql无需扫描任何记录即可迅速得到目标记录所在的位置。也就是说索引可以大大减少数据库管理系统查找数据的时间。索引有哪些优点?1、通过创建唯一性索引,可以保证数据库表中每一行数据的唯一性。2、可以大大加快数据的检索速度,这也是创建索引的最主要原因。3、可以加速表和表之间的连接,这在实现数据的参考完整性方面特别有意义。4、在使用分组和排序子句进行数据检索时,同样可以显著减少查询中分组和排序的时间。索引有哪些缺点?1、创建索引和维护索引要耗费时间,这种时间随着数据量的增加而增加。2、除了数据表占数据空间之外,每一个索引还要占一定的物理空间,如果要建立聚簇索引,需要的空间就会更大。3、当对表中的数据进行增加、删除和修改的时候,索引也要动态的维护,这样就降低了数据的维护速度。建立和使用索引有哪些注意事项:1、索引要建立在经常进行select操作的字段上。这是因为,如果这些列很少用到,那么有无索引并不能明显改变查询速度。相反,由于增加了索引,反而降低了系统的维护速度和增大了空间需求。2、索引要建立在值比较唯一的字段上。这样做才是发挥索引的最大效果。,比如主键的id字段,唯一的名字name字段等等。如果索引建立在唯一值比较少的字段,比如性别gender字段,寥寥无几的类别字段等,刚索引几乎没有任何意义。3、对于那些定义为text、p_w_picpath和bit数据类型的列不应该增加索引。因为这些列的数据量要么相当大,要么取值很少。4、当修改性能远远大于检索性能时,不应该创建索引。修改性能和检索性能是互相矛盾的。当增加索引时,会提高检索性能,但是会降低修改性能。当减少索引时,会提高修改性能,降低检索性能。因此,当修改性能远远大于检索性能时,不应该创建索引。5、在WHERE和JOIN中出现的列需要建立索引。6、在以通配符%和_开头作查询时,mysql索引是无效的。但是这样索引是有效的:select*fromtbl1wherenamelike'xxx%',所以mysql正确建立索引是很重要的。
假如你有一个表,
SQL> CREATE TABLE test_tab (
2 id INT,
3 name VARCHAR(10),
4 age INT,
5 val VARCHAR(10)
6 );
你的业务,有一个查询,是
SELECT * FROM test_tab WHERE name = 一个外部输入的数据
刚开始,数据不多的时候,执行效果还不错。
随着数据量的增加,这个查询,执行起来,越来越慢了。
然后在 name 上面 建立了索引
CREATE INDEX idx_test4_name ON test_tab (name );
这样, 可以加快前面那个查询的速度。
但是,某天,你执行了下面这个SQL, 发现速度又慢了
SELECT * FROM test_tab WHERE age = 25
为啥呢? 因为 age 字段上面,没有索引
索引只在 name 上面有
换句话说, 也就是 WHERE 里面的条件, 会自动判断,有没有 可用的索引,如果有, 该不该用。
多列索引,就是一个索引,包含了2个字段。
例如:
CREATE INDEX idx_test_name_age ON test_tab (name, age);
那么
SELECT * FROM test_tab
WHERE
name LIKE '张%'
AND age = 25
这样的查询,将能够使用上面的索引。
多列索引,还有一个可用的情况就是, 某些情况下,可能查询,只访问索引就足够了, 不需要再访问表了。例如:
SELECT
AVG( avg ) AS 平均年龄
FROM
test_tab
WHERE
name LIKE '张%'
这个时候, name 与 age 都包含在索引里面。 查询不需要去检索表中的数据。