MySQL索引&锁介绍

聚集和非聚集索引

• 非聚集索引（MyISAM）
  叶子节点只存储数据行（数据文件中）指针，即数据和索引不在一起。
  主键索引和辅助索引都会存储指向对应的数据的指针的值。
• 聚集索引（InnoDB）
  Innodb必须有主键（Myisam可以没有）；
  主键索引（聚集索引）的叶子节点会存储数据行，即数据和索引在一起；
  辅助索引只会存储主键的值，因此如果是非主键查询会走两次树，一次是辅助索引树，找出主键对应的值，一次是主键索引树，按前一次出来的值显示数据；InnoDB建议为大部分表使用默认自增的主键作为主索引。
  若没有主键，则使用唯一索引建立聚集索引。

MySQL索引

不同存储引擎索引存储方式不同：innodb -> b+ tree索引、myisam -> b tree索引

B tree和B+ Tree的特点和区别

B树是为了磁盘而设计的一种多叉平衡查找树，树高度一般都是2-4。
B+树与之区别在于非叶子节点不存储数据，只有叶子才存储。存储数据都在一行上。

优劣

• 优点：提高查询效率，降低数据库的io成本；通过索引对数据排序，降低cpu消耗；
• 缺点：索引占据磁盘空间，也会降低更新表的效率（每次更新都要更新索引文件)；

最左前缀原则

where子句中一些条件或表达式中出现的列的顺序要保持和复合索引的列一致或以复合索引顺序出现，只要出现非顺序出现，就无法用到复合索引。

假设你在表的state、city和zip数据列上建立了复合索引。索引中的数据行按照state/city/zip次序排列，因此它们也会自动地按照state/city和state次序排列。这意味着，即使你在查询中只指定了state值，或者指定state和city值，MySQL也可以使用这个索引。因此，这个索引可以被用于搜索如下所示的数据列组合：

state, city, zip
state, city
state

假如查询语句所查询的字段在这几个之中，但是顺序打乱，如city/state，还是可以符合最左匹配，因为有查询优化器的存在，可以使语句达到最高的效率。

如果我们在lname,fname,age上分别建索引,执行查询时，只能使用一个索引，mysql会选择一个最严格(获得结果集记录数最少)的索引。

MySQL不能利用这个索引来搜索没有包含在最左前缀的内容。例如，如果你按照city或zip来搜索，就不会使用到这个索引。如果你搜索给定的state和具体的zip代码（索引的1和3列），该索引也是不能用于这种组合值的，尽管MySQL可以利用索引来查找匹配的state从而缩小搜索的范围。

如果你考虑给已经索引过的表添加索引，那么就要考虑你将增加的索引是否是已有的多列索引的最左前缀。

如果是这样的，不用增加索引，因为已经有了(例如，如果你在state、city和zip上建立了索引，那么没有必要再增加state的索引)。

索引是否建立越多越好？

答案是否定的，索引建立适当才会发挥更好的效果。

• 数据量小的表不需要索引，建立会增加额外的索引开销
• 数据变更需要维护索引，因此更多的索引意味着更多的维护成本
• 更多的索引意味着也需要更多的空间

其他注意事项

• 尽量创建组合索引
• 在mysql中执行一次查询，只能使用一个索引
• 覆盖索引：SELECT语句中的列（即要查询的列），最好都创建为索引
• like语句：以%开头（即前模糊查询）不会使用索引
• is null/is not null 不会使用索引

MySQL性能优化之查看执行计划

MySQL查询优化

从效果上第一条影响最大，后面越来越小。
① SQL语句及索引的优化
② 数据库表结构的优化
③ 系统配置的优化
④ 硬件的优化

如何定位并优化慢查询sql

• 根据慢日志定位慢查询sql
• 使用explain等工具分析sql
• 修改sql或者尽量让sql走索引

慢查询日志

MySQL的慢查询日志是MySQL提供的一种日志记录，它用来记录在MySQL中响应时间超过阈值的语句，具体指运行时间超过 long_query_time 值的SQL，则会被记录到慢查询日志中。long_query_time的默认值为10，意思是运行10S以上的语句。默认情况下，Mysql数据库并不启动慢查询日志，需要我们手动来设置这个参数，当然，如果不是调优需要的话，一般不建议启动该参数，因为开启慢查询日志会或多或少带来一定的性能影响。慢查询日志支持将日志记录写入文件，也支持将日志记录写入数据库表。

slow_query_log ：是否开启慢查询日志，1表示开启，0表示关闭。
slow-query-log-file：新版（5.6及以上版本）MySQL数据库慢查询日志存储路径。可以不设置该参数，系统则会默认给一个缺省的文件host_name-slow.log
long_query_time ：慢查询阈值，当查询时间多于设定的阈值时，记录日志。
log_queries_not_using_indexes：未使用索引的查询也被记录到慢查询日志中（可选项）。
log_output：日志存储方式。log_output=‘FILE’表示将日志存入文件，默认值是’FILE’。log_output='TABLE’表示将日志存入数据库，这样日志信息就会被写入到mysql.slow_log表中。MySQL数据库支持同时两种日志存储方式，配置的时候以逗号隔开即可，如：log_output=‘FILE,TABLE’。日志记录到系统的专用日志表中，要比记录到文件耗费更多的系统资源，因此对于需要启用慢查询日志，又需要能够获得更高的系统性能，那么建议优先记录到文件。

在实际生产环境中，如果要手工分析日志，查找、分析SQL，显然是个体力活，MySQL提供了日志分析工具mysqldumpslow。

s, 是表示按照何种方式排序

c: 访问计数

l: 锁定时间

r: 返回记录

t: 查询时间

al:平均锁定时间

ar:平均返回记录数

at:平均查询时间

-t, 是top n的意思，即为返回前面多少条的数据；

-g, 后边可以写一个正则匹配模式，大小写不敏感的；

比如:
得到返回记录集最多的10个SQL。
mysqldumpslow -s r -t 10 /database/mysql/mysql06_slow.log

得到访问次数最多的10个SQL
mysqldumpslow -s c -t 10 /database/mysql/mysql06_slow.log

得到按照时间排序的前10条里面含有左连接的查询语句。
mysqldumpslow -s t -t 10 -g “left join” /database/mysql/mysql06_slow.log

另外建议在使用这些命令时结合 | 和more 使用 ，否则有可能出现刷屏的情况。
mysqldumpslow -s r -t 20 /mysqldata/mysql/mysql06-slow.log | more

explain介绍

• EXPLAIN命令，对select语句进行分析
• 有10列信息，重要的几个有：
  • id：每个select语句自动分配一个id，表示操作表的顺序，为null就表示这是一个结果集，不需要使用它来查询
  • select-type：查询类型。主要用区别普通查询、联合查询(union、union all)、子查询
    • simple：简单查询
    • primary：最外层查询
    • union：union连接的两个select查询，第一个是dervied，第二个及之后的都是union
    • subquery：子查询
    • …
  • table：显示查询表名，若使用别名，则显示别名
  • type：访问类型。性能从好到差为：system、const、eq_ref、ref、fulltext、ref_or_null、unique_subquery、index_subquery、range、index_merge、index、all。
  • key：实际使用到的索引。没有为null；
  • key_len：索引长度。单列索引：索引列使用长度；组合索引：不一定，可能用到所有列，也可能用部分列。
  • ref：显示缩印的哪一列被用了。用到了多少个字段上的索引，ref就会有几个（大部分情况）；
  • rows：估算的扫描行数。innodb不是精确值(mvcc并发机制)
  • extra
    • using filesort：没有用索引排序
    • using index：用到覆盖索引。直接用索引就查到数据，不用再去表里查询了。同时出现using where，说明索引被用来执行查找索引键值，反之被用来读取数据。
    • using where：在表中进行过滤
    • using temporary：用到临时表，常见于order by和group by；