mysql索引和锁机制简介及SQL优化

2020年4月21Mysql索引简介4Question3Mysql锁机制2SQL语句优化目录2020年4月3什么是索引select*fromScorewherescore=“77”；id,name,class,…,…,…,score,desc,date,id,name,class,…,…,…,score,desc,date,id,name,class,…,…,…,score,desc,date,让你实现在1,000,000行文本文件中查找你会怎么做？for(Stringline:lines){String[]words=line.split(,);for(Stringword:words){if(word.equals(77)){System.out.println(line);}}}一行一行扫描（全表扫描）？太慢，黄花菜都凉了。2020年4月4什么是索引二叉查找树（binarytree）？2020年4月5二叉查找树特点左边是数据表，一共有两列七条记录，最左边的是数据记录的物理地址（注意逻辑上相邻的记录在磁盘上也并不是一定物理相邻的）。为了加快Col2的查找，可以维护一个右边所示的二叉查找树，每个节点分别包含索引键值和一个指向对应数据记录物理地址的指针，这样就可以运用二叉查找在O(log2n)的复杂度内获取到相应数据。虽然这是一个货真价实的索引，但是实际的数据库系统几乎没有使用二叉查找树或其进化品种红黑树（red-blacktree）实现的，原因会在下文介绍。2020年4月6BTREE特点特点：多路搜索树，出度大，所有关键字在整颗树中出现，适合外部排序和查找。BTree渐进复杂度为O(h)=O(logdN)。一般实际应用中，出度d是非常大的数字，通常超过100，因此h非常小（通常不超过3）。2020年4月7B+TREE特点特点：一般在数据库系统或文件系统中使用的B+Tree结构都进行了优化，增加了顺序访问指针，所有关键字都在叶子结点中出现，非叶子结点作为叶子结点的索引；B+树总是到叶子结点才命中；2020年4月8B*TREE特点特点：非叶子节点也有链表；2020年4月9为什么使用B-TREE（B+TREE）红黑树等数据结构也可以用来实现索引，但是文件系统及数据库系统普遍采用B-/+Tree作为索引结构，一般来说，索引本身也很大，不可能全部存储在内存中，因此索引往往以索引文件的形式存储的磁盘上。这样的话，索引查找过程中就要产生磁盘I/O消耗，相对于内存存取，I/O存取的消耗要高几个数量级，所以评价一个数据结构作为索引的优劣最重要的指标就是在查找过程中磁盘I/O操作次数的渐进复杂度。2020年4月10为什么使用B-TREE（B+TREE）根据B-Tree的定义，可知检索一次最多需要访问h个节点。数据库系统的设计者巧妙利用了磁盘预读原理，将一个节点的大小设为等于一个页，这样每个节点只需要一次I/O就可以完全载入。（Innodb的数据页是16K，1.2.x支持8K,4K压缩页）。每次新建节点时，直接申请一个页的空间，这样就保证一个节点物理上也存储在一个页里，加之计算机存储分配都是按页对齐的，就实现了一个node只需一次I/O。B-Tree中一次检索最多需要h-1次I/O（根节点常驻内存），渐进复杂度为O(h)=O(logdN)。2020年4月11B+Tree页结构2020年4月12MYISAM主键索引2020年4月13MYISAM非主键索引2020年4月14INNODB主键索引第一个重大区别是InnoDB的数据文件本身就是索引文件。从上文知道，MyISAM索引文件和数据文件是分离的，索引文件仅保存数据记录的地址。而在InnoDB中，表数据文件本身就是按B+Tree组织的一个索引结构，这棵树的叶节点data域保存了完整的数据记录。这个索引的key是数据表的主键，因此InnoDB表数据文件本身就是主索引。第二个与MyISAM索引的不同是InnoDB的辅助索引data域存储相应记录主键的值而不是地址2020年4月15INNODB主键索引2020年4月16INNODB非主键索引2020年4月17B+TREE的插入插入282020年4月18B+TREE的插入插入702020年4月19B+TREE的插入插入952020年4月20建索引策略表的主键、外键必须有索引，innodb会自动给外键加索引，避免死锁。；数据行超过1000的表应该有索引；经常与其他表进行连接的表，在连接字段上应该建立索引；经常出现在Where子句中的字段，特别是大表的字段，应该建立索引；索引应该建在选择性高的字段上Cardinality/rows尽可能等于1。Showindex命令查看Cardinality。索引应该建在小字段上，整数字段尤其适合，对于大的文本字段甚至超长字段，不要建索引，或者建立前缀索引，如createindex索引名on表名(列名1(指定长度)，。。。。)频繁进行数据操作的表，不要建立太多的索引，数据的插入，更新和删除会对索引产生影响，太多的索引会导致插入更新删除操作缓慢；删除无用的索引，避免对执行计划造成负面影响；2020年4月21建索引策略复合索引的建立需要进行仔细分析；尽量考虑用单字段索引代替：A、正确选择复合索引中的主列字段，一般是选择性较好的字段；B、复合索引的几个字段是否经常同时以AND方式出现在Where子句中？单字段查询是否极少甚至没有？如果是，则可以建立复合索引；否则考虑单字段索引；C、如果复合索引中包含的字段经常单独出现在Where子句中，则分解为多个单字段索引；D、如果复合索引所包含的字段超过3个，那么仔细考虑其必要性，考虑减少复合的字段；E、如果既有单字段索引，又有这几个字段上的复合索引，一般可以删除复合索引；2020年4月22全文索引Mysql5.6innodb1.2.x支持全文索引，不过不支持unicode和中文字符集。2020年4月23自适应Hash索引2020年4月24自适应Hash索引限制只能用于等值比较，例如=，in,=.无法用于排序有冲突可能Mysql自动管理，人为无法干预。2020年4月251Mysql索引简介4Question3Mysql锁机制2SQL优化目录2020年4月26表结构设计原则选择合适的数据类型：如果能够定长尽量定长，只要能满足你的需求，应尽可能使用更小的数据类型：例如使用MEDIUMINT代替INT，但要考虑业务扩展。不要使用无法加索引的类型作为关键字段，比如text类型为了避免联表查询，有时候可以适当的数据冗余，比如邮箱、姓名这些基本不变的数据选择合适的表引擎，有时候MyISAM适合，有时候InnoDB适合为保证查询性能，最好每个表都建立有auto_increment字段，建立合适的数据库索引最好给每个字段都设定default值根据业务适当分区（partition）数据2020年4月27表结构设计原则尽量把所有的列设置为NOTNULL，如果你要保存NULL，手动去设置它，而不是把它设为默认值。尽量少用VARCHAR、TEXT、BLOB类型2020年4月28分析SQL效率方法Explain分析SQL的效率，观察表的执行顺序，使用了哪列索引，MySQL认为在查询中应该检索的记录数，一定要避免Usingfilesort和Usingtemporary使用profile剖析SQL执行具体过程使用SHOWFULLPROCESSLIST来查看当前MySQL服务器线程执行情况，是否锁表，和查看相应的SQL语句打开慢查询日志，找出执行效率慢的SQL语句。SelectSQL_NO_CACHE*from2020年4月29最左前缀原理与相关优化SHOWINDEXFROMemployees.titles;+--------+------------+----------+--------------+-------------+-----------+-------------+------+------------+|Table|Non_unique|Key_name|Seq_in_index|Column_name|Collation|Cardinality|Null|Index_type|+--------+------------+----------+--------------+-------------+-----------+-------------+------+------------+|titles|0|PRIMARY|1|emp_no|A|NULL||BTREE||titles|0|PRIMARY|2|title|A|NULL||BTREE||titles|0|PRIMARY|3|from_date|A|443308||BTREE|+--------+------------+----------+--------------+-------------+-----------+-------------+------+------------+2020年4月30全列匹配EXPLAINSELECT*FROMemployees.titlesWHEREemp_no='1'ANDtitle='SeniorEngineer'ANDfrom_date='1986-06-26';+----+-------------+--------+-------+---------------+---------+---------+-------------------+------+-------+|id|select_type|table|type|possible_keys|key|key_len|ref|rows|Extra|+----+-------------+--------+-------+---------------+---------+---------+-------------------+------+-------+|1|SIMPLE|titles|const|PRIMARY|PRIMARY|59|const,const,const|1||+----+-------------+--------+-------+---------------+---------+---------+-------------------+------+-------+2020年4月31首列匹配EXPLAINSELECT*FROMtitlesWHEREemp_no='1';+----+-------------+--------+------+---------------+---------+---------+-------+------+-------+|id|select_type|table|type|possible_keys|key|key_len|ref|rows|Extra|+----+-------------+--------+------+---------------+---------+---------+-------+------+-------+|1|SIMPLE|titles|ref|PRIMARY|PRIMARY|4|const|1||+----+-------------+--------+------+---------------+---------+---------+-------+------+-------+2020年4月32第二列未匹配EXPLAINSELECT*FROMtitlesWHEREemp_no='1'ANDfrom_date='1986-06-26';+----+-------------+--------+------+---------------+---------+---------+-------+------+