MySQL索引

索引

# 基本概念

-   索引：优化查询效率的数据结构
-   缺点：
    1.  维护索引耗费数据库资源
    2.  占用磁盘空间
    3.  增删改的时候要维护索引
-   原则:常用的搜索字段建立索引
-   索引分类
    -   InnoDB（必会）  主键  唯一  单列  复合
        1.  主键索引（聚簇索引）
        2.  单列索引（单值，普通）
        3.  唯一索引（列的值唯一，可以为null）
        4.  复合索引（一个索引多个列）
    -   MyISAM（不支持事务）
        -   Full Text  : 全文索引

# 索引的基本操作：
- 查询索引
	show index from tableName
- 普通索引的创建 key()
    1. 建表时创建  
        create table t user(id varchar(20) primary key, name varchar(20), key(column));
    2. 建表后创建
        create index indexName on table(tableColumnName)
- 唯一索引
	1. 建表时创建 
	create table t user(id varchar(20) primary key, name varchar(20), unique(column));
	2. 建表后创建
    create unique index indexName on table(tableColumnName)
- 复合索引
	1. 建表时创建 
	create table t user(id varchar(20) primary key, name varchar(20), key(column1,column2));
	2. 建表后创建
    create index indexName on table(col1,col2)
    3. 原则 ：（面试）
    	1. 最左前缀原则（左包含原则）
    	创建顺序比如(name,age),那么name age bir可以，age name bir不可以()
    	2. 动态调整查询顺序，以便更好利用查询	    
 ## 建表时创建无法指定索引名，建表后创建可以指定索引名字   

# 底层数据结构（B+ Tree）  一般都是2-4层
## 现象
1. 插入id无序
2. 查询时候自动排序了（主键索引排序了）
## 解释
1. 索引自动排序，方便以后快速查询
## 其他
1. 基于页管理，加上索引会自动排序的原则
2. 默认每一页都是16KB
3. 页表（存储每一页的第一个指针，可以直接链到对应页）
4. 顶层页常驻内存

# 聚簇索引和非聚簇索引
- 聚簇索引：（默认是主键，没有主键会去找一个唯一且非空的索引）
	将数据与索引放到了一块，索引结构的叶子节点保存了行数据（B+）
- 非聚簇索引：
	将数据与索引分开存储，索引结构的叶子节点指向了数据对应的位置
	查找的是数据的主键，需要再次查找才是真正的数据
	不存地址的原因是：存地址需要改辅助索引的地址，使用主键就不用考虑去修改地址。
## 主键索引一定是聚簇索引，反之不成立。
## 一个表只能有一个聚簇索引，其他都是非聚簇或者辅助索引。

## InnoDB 聚簇优点
	1. 存的是主键值，可以防止修改地址的时候对非聚簇树的维护，也可以减少磁盘的消耗
	2. 聚簇索引存的是主键和行数据，找到叶子节点就可以返回数据了
## MyISAM非聚簇索引，存的是地址

# 为什么建议主键自增？
1. 假如此时一页  1 2 3 4 5 6 7 8 10 11,如果来个主键9的数据插入，按照主键排序的原则，会插到8后面，但是如果此页已经满了，那就会造成页面分裂和记录移位，导致性能损耗。

# 索引失效的场景
1. 最佳左前缀法则（重要）
2. 不等于(!= 或者<>)导致索引失效
3. 主键插入顺序 （为什么建议主键自增？）
4. 计算、函数、类型转换导致索引失效
5. 范围条件右边的列索引失效（多个复合索引顺序不同）
6. is null可以使用索引，is not null无法使用索引
7. like以通配符%开头索引失效（重要）
8. OR 前后只要存在非索引的列，都会导致索引失效 
9. 数据库和表的字符集统一使用utf8mb4

## INNODB的最大ID记录在内存中，重启数据库或者OPTIMIZE操作会丢失。
	- optimize可以把分散(fragmented)存储的数据重新挪到一起(defragmentation)，清除碎片，回收闲置的数据库空间
	- 该操作会锁定表

# HEAP 表存在于内存中，用于临时高速存储。
# CHAR_LENGTH 是字符数，而 LENGTH 是字节数。

#  SELECT VERSION();用于获取当前 Mysql 的版本。
#  创建表时 TIMESTAMP 列用 Zero 更新。只要表中的其他字段发生更改，
#  UPDATE CURRENT_TIMESTAMP 修饰符就将时间戳字段更新为当前时间
#  shell登录MySQL
#  [mysql dir]/bin/mysql -h hostname -u <UserName> -p <password>
#  MySQL 单表超过500万行或者单表容量超过2GB进行分库或者分表
#  Like 模糊查询：％对应于 0 个或更多字符，_只是 LIKE 语句中的一个字符。
#  Mysql在V5.1之前默认存储引擎是MyISAM;在此之后默认存储引擎是InnoDB
#  任何标准表最多可以建16个索引列
# NUMERIC 和 DECIMAL用于保存进度比较高的数据，比如金融行业

MySQL事务

• MySQL默认是autocommit模式的，默认不支持事务，所有的数据库更新操作都会即时提交。
• 如果引擎为InnoDB,那么可以设置支持事务。Set AutoCommit = 0 ，该模式下更新操作都需要手动commit才会生效，使用Rollback进行事务回滚。

• # 开启事务
  START TRANSACTION;
  SELECT @A:=SUM(salary) FROM table1 WHERE type=1;
  UPDATE table2 SET summmary=@A WHERE type=1;
  COMMIT;
  

MySQL锁