【Mysql调优】(四) :索引

序言

     之前我一直认为对于Java开发而言,只要学会SQL的增删改查(包括复杂查询)语句就可以了,然而在一次对于几千万的数据进行复杂查询时,数据库居然崩溃了。当时领导催得紧,没办法自己乱操作一顿,结果可想而知,电脑死机了。当时唯一能庆幸的是服务器是UAT,最后只能去问百度爸爸了,经过多方面的百度我知道了还有SQL调优这个骚操作,接着又了解了 索引<一数据引擎<一B+Treem<一.......<一二叉树,当时我是从左往右学习,走了 很多弯路,当时真想一句妈卖批。

一、索引的优点和缺点以及使用原则

1、优点

  • 类似于大学图书馆建书目索引,提高数据检索的效率,降低数据库的IO成本
  • 通过索引列对数据进行排序,降低数据排序的成本,降低了CPU的消耗

2、缺点

  •   虽然索引大大提高了查询速度,同时却会降低更新表的速度,如对表进行INSERT、UPDATE和DELETE。因为更新表时,MySQL不仅要保存数据,还要保存一下索引文件每次更新添加了索引列的字段,都会调整因为更新所带来的键值变化后的索引信息。
  • 索引只是提高效率的一个因素,如果你的MySQL有大数据量的表,就需要花时间研究建立最优秀的索引,或优化查询语句

3、使用原则

         应该在这些列上创建索引

  • 在经常需要搜索的列上,可以加快搜索的速度;
  • 在作为主键的列上,强制该列的唯一性和组织表中数据的排列结构;
  • 在经常用在连接的列上,这些列主要是一些外键,可以加快连接的速度;
  • 在经常需要根据范围进行搜索的列上创建索引,因为索引已经排序,其指定的范围是连续的;
  • 在经常需要排序的列上创建索引,因为索引已经排序,这样查询可以利用索引的排序,加快排序查询时间;
  • 在经常使用在 WHERE 子句中的列上面创建索引,加快条件的判断速度。

        不应该在这些列上创建索引

  • 对于那些在查询中很少使用或者参考的列不应该创建索引。这是因为,既然这些列很少使用到,因此有索引或者无索引,并不能提高查询速度。相反,由于增加了索引,反而降低了系统的维护速度和增大了空间需求。
  • 对于频繁更新的字段不适合创建索引(因为每次更新都需要更新索引),对频繁作为查询条件的字段应该创建索引
  • 对于那些只有很少数据值的列也不应该增加索引。这是因为,由于这些列的取值很少,例如人事表的性别列,在查询的结果中,结果集的数据行占了表中数据行的很大比例,即需要在表中搜索的数据行的比例很大。增加索引,并不能明显加快检索速度。
  • 对于那些定义为 text, image 和 bit 数据类型的列不应该增加索引。这是因为,这些列的数据量要么相当大,要么取值很少。
  • 当修改性能远远大于检索性能时,不应该创建索引。这是因为,修改性能和检索性能是互相矛盾的。当增加索引时,会提高检索性能,但是会降低修改性能(每次修改都会重新维护索引)。当减少索引时,会提高修改性能,降低检索性能

     通过上面说的,我们应该可以知道,并不是每个字段度设置索引就好,也不是索引越多越好,而是需要自己合理的使用。

 

二、索引分类

1、主键索引

           主键是一种唯一性索引,但它必须指定为“PRIMARY KEY”。如果你曾经用过AUTO_INCREMENT类型的列,你可能已经熟悉主键之类的概念了。主键一般在创建表的时候指定,例如

CREATE TABLE table_name ( [...], PRIMARY KEY (columnName)); 

但是,我们也可以通过修改表的方式加入主键,例如

ALTER TABLE table_name ADD PRIMARY KEY (columnName);

2、普通索引

  普通索引(由关键字KEY或INDEX定义的索引)的唯一任务是加快对数据的访问速度。因此,应该只为那些最经常出现在查询条件(WHERE  column=)或排序条件(ORDERBY column)中的数据列创建索引。只要有可能,就应该选择一个数据最整齐、最紧凑的数据列(如一个整数类型的数据列)来创建索引。

  • 建表同时创建索引
CREATE TABLE table_name ( [...], PRIMARY KEY (name),INDEX index_columnName (columnName)); 
  • 直接创建索引
CREATE INDEX index_columnName ON table_name(columnName)
  • 修改表结构的方式添加索引
ALTER TABLE table_name ADD INDEX index_columnName(columnName)
  • 删除索引
DROP INDEX index_columnName ON table_name

直观示例:

 

mysql> show index from emp;
+-------+------------+----------+--------------+-------------+-----------+-------------+------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Index_type | 
+-------+------------+----------+--------------+-------------+-----------+-------------+------------+
| emp   |          0 | PRIMARY  |            1 | id          | A         |      498341 | BTREE      | 
1 row in set (0.00 sec)

mysql> explain select *  from  emp where deptno=110;
+----+-------------+-------+------+---------------+------+---------+------+--------+-------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows   | Extra       |
+----+-------------+-------+------+---------------+------+---------+------+--------+-------------+
|  1 | SIMPLE      | emp   | ALL  | NULL          | NULL | NULL    | NULL | 498341 | Using where |
+----+-------------+-------+------+---------------+------+---------+------+--------+-------------+
1 row in set (0.00 sec)

mysql> create index index_deptno on emp(deptno);
Query OK, 0 rows affected (1.61 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> show index from emp;
+-------+------------+--------------+--------------+-------------+-----------+-------------+------------+
| Table | Non_unique | Key_name     | Seq_in_index | Column_name | Collation | Cardinality | Index_type | 
+-------+------------+--------------+--------------+-------------+-----------+-------------+------------+
| emp   |          0 | PRIMARY      |            1 | id          | A         |      498341 | BTREE      | 
| emp   |          1 | index_deptno |            1 | deptno      | A         |          18 | BTREE      |
+-------+------------+--------------+--------------+-------------+-----------+-------------+------------+
2 rows in set (0.00 sec)
mysql
> explain select * from emp where deptno=110; +----+-------------+-------+------+---------------+--------------+---------+-------+------+-------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+------+---------------+--------------+---------+-------+------+-------+ | 1 | SIMPLE | emp | ref | index_deptno | index_deptno | 3 | const | 1 | NULL | +----+-------------+-------+------+---------------+--------------+---------+-------+------+-------+ 1 row in set (0.05 sec) mysql> DROP INDEX index_deptno ON emp; Query OK, 0 rows affected (0.32 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> show index from emp; +-------+------------+----------+--------------+-------------+-----------+-------------+------------+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Index_type | +-------+------------+----------+--------------+-------------+-----------+-------------+------------+ | emp | 0 | PRIMARY | 1 | id | A | 498341 | BTREE | +-------+------------+----------+--------------+-------------+-----------+-------------+------------+ 1 row in set (0.00 sec) mysql> show index from emp; +-------+------------+----------+--------------+-------------+-----------+-------------+------------+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Index_type | +-------+------------+----------+--------------+-------------+-----------+-------------+------------+ | emp | 0 | PRIMARY | 1 | id | A | 498341 | BTREE | +-------+------------+----------+--------------+-------------+-----------+-------------+------------+ 1 row in set (0.00 sec) mysql> ALTER TABLE emp ADD INDEX index_deptno(deptno); Query OK, 0 rows affected (1.97 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> show index from emp; +-------+------------+--------------+--------------+-------------+-----------+-------------+------------+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Index_type | +-------+------------+--------------+--------------+-------------+-----------+-------------+------------+ | emp | 0 | PRIMARY | 1 | id | A | 498341 | BTREE | | emp | 1 | index_deptno | 1 | deptno | A | 18 | BTREE | +-------+------------+--------------+--------------+-------------+-----------+-------------+------------+ 2 rows in set (0.00 sec)

 

3、唯一索引

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

  • 建表同时创建索引
CREATE TABLE table_name ( [...], PRIMARY KEY (name),  UNIQUE index_columnName (columnName)); 
  • 创建唯一索引
CREATE UNIQUE INDEX index_columnName ON table_name(columnName);
  • 修改表结构的方式添加索引
ALTER TABLE table_name ADD UNIQUE index_columnName(columnName);
  • 删除索引与直观示例

        与普通索引类似

4、复合索引

    复合索引指多个字段上创建的索引,只有在查询条件中使用了创建索引时的第一个字段,索引才会被使用。使用复合索引时遵循最左前缀集合

  • 建表同时创建索引
CREATE TABLE table_name ( [...], PRIMARY KEY (name),INDEX index_columnName (columnName1,columnName2)); 
  • 接创建索引
CREATE INDEX index_columnName ON table_name(columnName1,columnName2);

与普通索引用法类推

5、全文索引

posted @ 2019-09-26 16:43  空留意  阅读(202)  评论(0编辑  收藏  举报