一、索引
1.什么是索引
索引是对数据库表中一列或多列的值进行排序的一种结构，使用索引可快速访问数据库表中的特定信息。简而言之，数据库索引是排好序的数据结构，为了提高数据的查询效率。
在数据库中，索引也允许数据库程序迅速地找到表中的数据，而不必扫描整个数据库。
MySQL常用的索引就是B+树。
2.索引的分类
索引分为聚簇索引和非聚簇索引两种。
聚簇索引是按照数据存放的物理位置为顺序的，能提高多行检索的速度。
注意：一个表中只能有一个聚集索引。
非聚簇索引不对数据进?物理排序，对于单行的检索很快。
注意：一个表中可以有多个非聚集索引。
单值索引（最通用、最简单）：一个索引只包含单个列，一个表中可以有多个单值索引
唯一索引：索引列的值必须唯一，可为空
复合索引(又叫联合索引，功能最强大)：一个索引包括多个列
3.索引的优势
1）提高数据检索效率，降低磁盘IO成本
2）通过对数据的排序，降低排序成本
4. 索引的劣势
1）索引虽提高了查询效率，但同时降低了更新、修改、删除的效率，因为MySQL不仅要保存数据，还要维护数据和索引的关系。
2）需要成本去维护索引。一个性能良好的索引需要不断的去尝试，以找到最优解。
5. 什么情况下适合建立索引
1）主键自动建立唯一索引
2）频繁作为查询条件的字段（where后面的字段）
3）查询中与其他表关联的字段（各种join on后面的字段）
4）单值/复合索引选择？（高并发下倾向选择复合索引）
5）查询中排序的字段
6）查询中统计或分组的字段
6. 什么情况下不适合建立索引
1）表数据太少
2）频繁更新的字段
3）where后面用不到的字段
7. 什么时候会出现索引失效
1）like以通配符开头(‘%abc’)会导致索引失效，违反最左前缀法则
最左前缀法则：最左优先，以最左边的为起点任何连续的索引都能匹配上。同时遇到范围查询(>、<、between、like)就会停止匹配。
2）在索引列上做任何操作(计算、函数、类型转换)，会导致索引失效而转向全表扫描
3）存储引擎不能使用索引中范围条件右边的列，举例：select id，name from student where id > 50 and name = ‘张三’，会导致name索引失效
4）尽量使用覆盖索引，不要select *
5）MySQL在使用不等于(!=或<>)的时候无法使用索引会导致全表扫描，理由也很简单，B+Tree叶子节点用指针相连且是排好序的，这种数据结构只能解决有序的定值查询，像不等于这种无法利用索引查询。
6）IS NULL、IS NOT NULL无法使用索引，理由同上
7）字符串不加单引号索引失效
? 隐式转换–>函数操作
8）用or连接时会导致索引失效
8. 为什么建议InnoDB必须建主键
因为这样能降低数据库开销，对于InnoDB来说，如果不手动建主键索引，MySQL底层依然会帮我们创建一个聚集索引来维护整张表的所有数据，因为B+Tree必须依靠索引才能建立。
9. 为什么推荐使用整型主键
因为比较两个int型更具有优势，字符串需要逐位的去比较。
10. 为什么推荐使用自增主键
因为如果主键已经插入了1、2、3、4、6、7，这时候我们插入了5，MySQL在维护索引的时候就会打破原有链表顺序，导致链表节点分裂重排，从而消耗性能。而使用自增主键就不会产生这种问题。
11. 为什么InnoDB非主键索引存储的是主键值
保持一致性，当数据库表进行DML操作时，同一行记录的页地址会发生改变，因非主键索引保存的是主键的值，无需进行更改。同时还可以节省存储空间，因为Innodb数据本身就已经汇聚到主键索引所在的B+树上了， 如果普通索引还继续再保存一份数据，就会导致有多少索引就要存多少份数据。
二、MySQL优化
1、选择最合适的字段属性
2、尽量把字段设置为NOT NULL
3、使用连接（JOIN）来代替子查询（Sub-Queries）
4、使用联合（UNION）来代替手动创建的临时表
5、使用事务
6、使用外键
7、锁定表
8、使用索引
9、优化查询语句
- 9.1 不使用子查询
- 9.2 避免函数索引
- 9.3 用IN来替换OR
- 9.4 LIKE双百分号无法使用到索引
- 9.5 读取适当的记录LIMIT M,N
- 9.6 避免数据类型不一致
- 9.7 分组统计可以禁止排序
- 9.8 避免随机取记录
- 9.9 禁止不必要的ORDER BY排序
- 9.10 批量INSERT插入
|