MySQL学习笔记

一.常用指令

#展示表结构
desc tbl_sys_option_log;

#展示主键和索引指令
show index from tbl_sys_option_log;

#增加索引
alter table table_name ADD INDEX index_name (column_list);

#删除索引
alter table table_name user_index drop KEY name;

#展示主键和索引指令
show index from tbl_sys_option_log;
    
#增加索引
alter table table_name ADD INDEX index_name (column_list);

#删除索引
alter table table_name user_index drop KEY name;

    

    
二.基本概念

• 创建索引：

    alter table 表名 add index(字段名)    

• 索引概念：

1. 索引的实现通常使用B tree 或者 B+ tree,是数据库中排序的数据结构，以协助快速查询、更新数据。

2. 索引有点：可以大大加快数据的检索熟读，这是创建索引的最主要原因。

3. 索引缺点：创建索引和维护索引比较耗时，当对表的数据增加、删除、修改时索引也是要动态的维护，会降低增删改的执行效率。
4. 总结:需要合理的使用索引，在优缺点中有一个取舍。

• Order By：

1. 使用 order by 将查询结果按照某个字段排序，如果字段没有创建索引，那么执行计划是:将数据从硬盘分批读到内存内部内部排序，最后合并排序结果，非常影响性能。

• 索引覆盖：

1. 如果要查询的字段都建立过索引，那么引擎会直接在索引表中查询而不访问原始数据（否有一个字段没有建立索引就会做全表扫描），这叫索引覆盖。
2. 总结：在select后尽可能只写必要的查询字段，以增加索引覆盖的几率。

• 索引算法：

1. 索引算法有BTree算法和Hash算法。
2. 可以被用在=,>,>=,<,<=和between这些比较操作符上，而且还可以用于like操作符，只要它的查询条件是一个不以通配符开头的常量：
       

   #（1）只要它的查询条件是一个不以通配符开头的常量
   select * from user where name like 'jack%'; 
   
   #（2）-- 如果一通配符开头，或者没有使用常量，则不会使用索引，例如： 
   select * from user where name like '%jack';

    

• 创建索引的原则：

1. 最左前缀匹配原则，是组合索引非常重要的原则，mysql会一直匹配直到遇见范围查询（>、<、between、like）就停止匹配，比如a = 1 and b = 2 and c > 3 and d = 4 如果建立(a,b,c,d)顺序的索引，d是用不到索引的，如果建立(a,b,d,c)的索引则都可以用到，a,b,d的顺序可以任意调整。
2. 频繁作为查询条件的字段才创建索引。
3. 更新频繁的字段不适合创建索引。
4. 尽量的扩展索引，不要新建索引。比如表中已经有a的索引，现在要加(a,b)的索引，那么只需要修改原来的索引即可。
5. 定义有外键的数据列一定要建立索引。
6. 对于那些查询中很少涉及的列，重复值比较多的列不要建立索引。
7. 对于定义为text、image和bit的数据类型的列不要建立索引。

    

• 创建索引要注意什么？

1. 非空字段，应该制定为NOT NULL, 含有空值的列很难进行查询优化，因为使得索引、索引信息以及比较运算更复杂，应该使用0，特殊值，或者空串代替。
2.  取值离散越大越好( 比如各个值之间的差异程度 )，这样可以降低排序难度。
3. 索引字段越小越好，数据库的数据存储以页为单位一页存储的数据越多一次IO操作获取的数据越大效率越高。  (以页为单位是什么意思？)

• 百万级别及以上的数据如何删除？

1. 先删除索引。
2. 删除无用的数据。
3. 删除完成后，再重新创建索引。
4. 删除如果中断，数据会自动回滚。

 

三.防止数据脏读

 

1. UPDATE，且where中的过滤条件列需要用到索引，