1、设计索引原则：
1）、适合索引的列是出现在where子句中的列，或者连接子句中指定的列。
2）、索引列基础越大越好
3）、使用短索引。对字符串列进行索引，应该指定一个前缀长度。例如有一个varchar(200)的列，在前10或20个字符多数值唯一，就对前10或20个字符进行索引
4）、最左前缀。。。。？
5）、不要过度索引
6）、对于InnoDB存储引擎的表，默认会按照一定的顺序。
有主键，按照主键顺序
没主键，按照 唯一索引顺序
没主键，没唯一索引，自动生成顺序。
2、BTREE索引和HASH索引
HASH索引：
1）、只用于=或者<=>
2）、不能使用Hash索引优化加速ORDER BY操作
3）、只能使用整个关键字来索引一行
4）、不能确定在两个值大约有多少行
BTREE索引：
用>、<、>=、<=、BETWEEN、!=、<>、LIKE
3、explain sql语句结果中的type属性
ALL index range ref eq_ref const,system NULL
ALL：全表扫描
index：索引全表扫描
range：索引范围扫描
ref：使用非唯一索引或唯一索引的前缀扫描
eq_ref：对于每个索引值，表中只有一条记录匹配，多表连接中使用primary_key或者unique index作为关联条件
const/system：表中最多有一个匹配行
NULL：不访问表或者索引
explain sql语句结果中的Extra属性:using where 利用索引加速访问，还根据索引回表查询数据
4、MySQL搜索引擎对索引的支持情况
MYISAM引擎 InnoDB引擎 Memory引擎
BTREE索引： T T T
HASH索引： F F T
RTREE索引： T F F
FULL-TEXT索引： T F F
5、能够使用索引的典型场景
1）、匹配全值
2）、匹配值的范围查询
3）、匹配最左前缀：
联合索引（col1, col2, col3, col4），该索引能被 能被含有col1字段的组合查询利用到
6、索引存在但不能使用的典型场景
1）、以%开头的LIKE查询
2）、数据类型出现隐式转换
3）、复合索引情况，查询条件不包含索引列最左部分
4）、MYSQL预估使用索引比全表扫描更慢，则不使用索引
5）、用or分割开的条件，or前的条件有索引，or后的条件没有索引，那么涉及的索引都不会用到
7、常用sql优化
1）、大批量Load数据
对于MyISAM的表：
Alter table tabl_name DISABLE KEYS;
load the data
Alter table tabl_name ENABLE KEYS;
对于InnoDB表;
A、对导入文本按主键进行排序
B、导入数据前关闭唯一性校验，set unique_checks=0，导入之后执行 set unique_checks=1恢复唯一性校验
C、导入数据前关闭自动提交，set autocommit=0，导入之后执行 set autocommit=1恢复自动提交
2）、Insert语句
一个客户端同时插入很多行，用insert into tabl_name values(1,2),(1,3),(1,4)....
不同客户端插入很多行，可以使用insert delayed 语句
建表的时候将索引文件和数据文件放在不同的磁盘上
3）、优化order by
排序方式：using Index、using filesort(符合索引时候，还是会出现filesort)
filesort排序，不是通过索引直接返回排序结果的排序都叫Filesort排序
故：尽量减少额外的排序，通过索引直接返回有序数据。where条件和order by使用相同的索引，并且order by的顺序和索引顺序相同，order by的字段都是升序或者都是降序。否则肯定会出现额外的排序操作，出现Filesort.
下列sql可以使用索引：
select * from tablename order by key_part1, key_part2,...;
select * from tablename where key_part1=1 order by key_part1, key_part2,...;
select * from tablename order by key_part1 desc, key_part2 desc,...;
下列sql不可以使用索引：
select * from tablename order by key_part1 desc, key_part2 asc;
-- order by的字段混合asc和desc
select * from tablename where key2=constant order by key1
-- 查询关键字和order by所使用的不相同
select * from tablename order by key_part1, key_part2；
-- 对不同的关键字使用order by
优化FileSort操作：
比较max_length_for_sort_data和查询语句取出字段总大小 判断使用两次扫描算法和一次扫描算法
4）、优化group by
添加 order by null 避免order by排序的消耗
5）、优化嵌套语句
子查询用join代替
6）、优化or
or之间的每个条件都必须用到索引
7）、优化分页查询
在索引上完成排序分页的操作，最后根据主键关联回原表查询所需要的其他内容。
（
SELECT
a.id,
type
FROM
casetest a
INNER JOIN (
SELECT
id
FROM
casetest
ORDER BY
id
LIMIT 1,
5
) b ON a.id = b.id
）
将limit m,n 转换成limit n，只适合在排序字段不会出现重复值得情况。
8）、使用sql提示
A、use index 提供希望MYSQL去参考的索引列表
B、ignore index 让MYSQL忽略一个或多个索引
C、force index 强制MySQL使用特定的索引
8、事务（对于InnoDB引擎）
1）、4个特性：
Automicity：要么全部完成，要么全部不完成
Correspodence：完整性约束没有被破坏
Isolation：两个事务之间互不影响
Durability：事务执行完的结果保存在数据库中，不会被回滚
2）、并发事务带来的问题
更新丢失：两个事务，对同一条记录进行修改，后提交的事务会将先提交的事务覆盖，前一次事务的修改就丢失了。
脏读：事务A对数据进行修改，未提交，此时事务B对这部分数据进行的读操作，就会对这部分数据产生依赖。
不可重复读：事务A第一次读数据和第二次读数据之间，事务B对该数据做了修改，导致事务A两次读取的数据不同或记录已经被删除。
幻读：事务A两次按照相同条件检索数据时候，第一次检索没有满足的数据，但在第二次检索之前，事务B并发插入了满足查询条件的数据，这种情况称为幻读。
3）、隔离级别
Read_Uncommit
Read_commit：防止脏读、不能防止不可重复读和幻读
Repeatable read：防止脏读、不可重复读，不能防止幻读
Read_serializable：都可防止
9、锁
悲观锁：多个事务共享数据加锁
乐观锁：共享数据不加锁，提交操作时检查是否违反数据的完整性
10、MyISAM和InnoDB区别
InnoDB支持事务，MyISAM不支持
InnoDB支持行级锁，MyISAM不支持
InnoDB支持外键，MyISAM不支持
11、数据库三大范式
第一范式：数据库属性不可分
第二范式：在满足第一范式条件下，非主属性完全依赖主键属性。要有主键，其他属性要和主键有关系。
第三范式：在满足第二范式条件下，非主属性无传递依赖。主键和其他属性要有直接关系。
网友评论