mysql数据库正确建立索引及使用

ny3259

　　普通mysql运行，数据量和访问量不大的话，是足够快的，但是当数据量和访问量剧增的时候，那么就会明显发现MySQL很慢，甚至down掉，那么就要考虑优化我们的mysql了。其中优化mysql的一个重要环节就是为数据库建立正确合理的索引。
　　
    如果没有索引，执行查询时mysql必须从第一个记录开始扫描整个表的所有记录，直至找到符合要求的记录。表里面的记录数量越多，这个操作的代价就越高。如果作为搜索条件的列上已经创建了索引，mysql无需扫描任何记录即可迅速得到目标记录所在的位置。也就是说索引可以大大减少数据库管理系统查找数据的时间。
　　
索引有哪些优点？
　　
    1、  通过创建唯一性索引，可以保证数据库表中每一行数据的唯一性。
　　
    2、  可以大大加快数据的检索速度，这也是创建索引的最主要原因。
　　
    3、  可以加速表和表之间的连接，这在实现数据的参考完整性方面特别有意义。
　　
    4、  在使用分组和排序子句进行数据检索时，同样可以显著减少查询中分组和排序的时间。
　　
索引有哪些缺点？
　　
    1、  创建索引和维护索引要耗费时间，这种时间随着数据量的增加而增加。
　　
    2、  除了数据表占数据空间之外，每一个索引还要占一定的物理空间，如果要建立聚簇索引，需要的空间就会更大。
　　
    3、  当对表中的数据进行增加、删除和修改的时候，索引也要动态的维护，这样就降低了数据的维护速度。
　　
      建立和使用索引有哪些注意事项：
　　
     1、 索引要建立在经常进行select操作的字段上。这是因为，如果这些列很少用到，那么有无索引并不能明显改变查询速度。相反，由于增加了索引，反而降低了系统的维护速度和增大了空间需求。
　　
     2、 索引要建立在值比较唯一的字段上。这样做才是发挥索引的最大效果。，比如主键的id字段，唯一的名字name字段等等。如果索引建立在唯一值比较少的字段，比如性别gender字段，寥寥无几的类别字段等，刚索引几乎没有任何意义。
　　
     3、 对于那些定义为text、image和bit数据类型的列不应该增加索引。因为这些列的数据量要么相当大，要么取值很少。
　　
     4、 当修改性能远远大于检索性能时，不应该创建索引。修改性能和检索性能是互相矛盾的。当增加索引时，会提高检索性能，但是会降低修改性能。当减少索引时，会提高修改性能，降低检索性能。因此，当修改性能远远大于检索性能时，不应该创建索引。
　　
     5、 在WHERE和JOIN中出现的列需要建立索引。
　　
     6、 在以通配符% 和_ 开头作查询时，mysql索引是无效的。但是这样索引是有效的：select * from tbl1 where name like 'xxx%'，所以mysql正确建立索引是很重要的。
　　

　　
假如你有一个表，
　　

　　
SQL> CREATE TABLE test_tab (

　　
2   >　　
3    name VARCHAR(10),
　　
4    age  INT,
　　
5    val  VARCHAR(10)
　　
6  );
　　

　　
你的业务，有一个查询，是
　　
SELECT * FROM test_tab WHERE name = 一个外部输入的数据
　　

　　
刚开始，数据不多的时候，执行效果还不错。
　　
随着数据量的增加，这个查询，执行起来，越来越慢了。
　　

　　
然后在 name 上面 建立了索引

　　
CREATE INDEX>　　
这样， 可以加快前面那个查询的速度。
　　

　　
但是，某天，你执行了下面这个SQL， 发现速度又慢了
　　
SELECT * FROM test_tab WHERE age = 25
　　
为啥呢？ 因为 age 字段上面，没有索引
　　
索引只在 name 上面有
　　

　　
换句话说， 也就是 WHERE 里面的条件， 会自动判断，有没有 可用的索引，如果有， 该不该用。
　　

　　

　　
多列索引，就是一个索引，包含了2个字段。
　　

　　
例如：

　　
CREATE INDEX>　　

　　
那么
　　
SELECT * FROM test_tab
　　
WHERE
　　
name LIKE '张%'
　　
AND age = 25
　　
这样的查询，将能够使用上面的索引。
　　

　　
多列索引，还有一个可用的情况就是， 某些情况下，可能查询，只访问索引就足够了， 不需要再访问表了。例如：
　　

　　
SELECT
　　
AVG( avg ) AS 平均年龄
　　
FROM
　　
test_tab
　　
WHERE
　　
name LIKE '张%'
　　

　　
这个时候， name 与 age 都包含在索引里面。 查询不需要去检索表中的数据。