【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);
与普通索引用法类推
