实现效果：通俗的说索引是用来提高查询效率，不需要通过扫描全部表记录，而直接使用索引快速定位需要查询的值。

需求的影响：论坛帖子要求总量的统计，附加要求，实时更新

        功能上非常容易实现，执行select count * from 表名 的query就可以得到结果，如果我们采用不是MyLSAM存储引擎，而是使用Innodb的存储引擎，那么存放帖子的表有上千万条记录，执行这条需要很大的成本。

        没有where的count使用MyLSAM要比InnoDB快的多，因为MyLSAM内置了一个计时器，count时他直接从计数器中读，1而InnoDB必须扫描全表。在InnoDB上执行count是一般要伴随where，且where重要包含主键以外的索引列。

        这样查询不行，我们就专门为这个功能建一个表，就只有一个字段，一条记录，就存放这个统计量，每次有新的帖子产生的时候，都将这个值增加1，我们每次都只需要查询这个表就可以得到结果，效率就能满足要求。查询效率肯定能够满足要求，开始如果帖子产生快，高峰时期可以每秒上百个新增操作。因为锁资源争用严重造成整体性能的大幅度下降。

        通过创建一个统计表，然后通过一个定时任务每隔一定时间段去更新一次里面的统计值，既可以解决统计值查询的效率问题，有保证不影响新发帖的效率。

     系统架构及实现的影响

            所有数据都不是适合在数据库存放。

                二进制多媒体数据：主要包括图片，音频，视频和其他一些相关的二进制文件，将二进制多媒体数据存放在数据库中，数据库空间只有消耗严重，数据存储消耗数据库主机的CPU资源。

                超大文本数据

                    在5.0.3之前的mysql版本，VARCHAR类型的数据最长只能存放255个字节，如果需要存储更长的数据到一个字段，必须使用TEXT类型（最大可存放64k）的字段，甚至是更大的LONGTEXT类型（最大4GB）。而text类型数据的处理性能要远比VARCHAR类型数据的处理性能底下很多。从5.0.3版本，VARCHAR类型的最大长度被调整到64kb了，所以，超大文本数据存放的数据库综合那个不仅会带来性能低下，还带来空间占用浪费。

                对于web应用，活跃数据的数据量总是不会特别大，有些活跃数据更是很少变化，对于这里数据，如果我们能将变化相对较少的部分活跃数据通过应用层的cache机制cache到内存中，对性能提升肯定是成数量级的，而且由于是活跃数据，对系统整体的性能影响也会很大。

                查询语句对性能的影响

                  数据库管理软件中，最大的性能瓶颈是在于磁盘io，就是数据存取操作上面，对同一份数据，以不同方式找到其中的某一点内容的时候，所需的数据量可能会有天壤之别，消耗的资源也区别很多。

                explain来查看执行计划

                profiling来查看实际执行计划

                    通过执行show PROFILE命令获取当前系统中保存的多个query的profile的概要信息。

            数据库Schema设计对性能的影响

            硬件选择对性能的影响

                数据库主机是存取数据的地方，数据库主机的io性能肯定是需要最优先考虑的一个因素，不管什么类型的数据库应用都适用。在主机中决定io性能不仅主要有磁盘和内存所决定，当然也包括各种io相关的板卡

                其次，数据库主机是存取数据的地方，词语要相对集中很多，单台主机上所需要进行的计算量自然也就比较多，所有数据库主机的CPU处理能力也不能忽视

                数据库负责数据的存储，与各应用茨城县的交互中传递的数据比其他各类服务器都要多，所以数据库主机的网络设备的性能也可能会成为系统的瓶颈。

                数据库应用系统的优化，实际上是一个需要多方面配合，多方面优化的才能产生根本性改善的事情。

                可以通过商业需求合理化，系统架构最优化，逻辑实现荆建华，硬件设施理性化。

                mysql性能优化之一-索引

                    mysql索引的好处：对于没有索引的表，单表查询可能几十万数据的瓶颈，而通常大型网站单日就会产生几百万的数据，没有索引查询会变得非常缓慢。

                索引实在存储引擎中实现的，而不是在服务器层中实现的。每种存储的索引都不一定完全相同，并不是所有的存储引擎都支持所有的索引类型。

                什么是索引：

                    是帮助mysql高兴获取数据的数据结构，他的存在形式是文件，缩影能够帮助我们快速定位数据。

                为什么使用索引：

                    索引可以让mysql高效运行，可以提高mysql的插叙效率，数据约束

                好处：

                    提高查询效率，快速定位数据

                索引产生的代价：

                    1，本身以文件形式存放在硬盘，需要的时候才加载至内存，所有添加索引会增加磁盘的开销；

                    2，写数据：需要更新索引，岁数据库是很多的开销，见底表更新，添加和删除的速度。

                不建议使用索引的情况有哪些：

                    表记录少

                    索引的选择性较低。指不重复的索引值与表记录数的比值，取值范围0到1，值越大，选择性越大

                索引的类型：

                    普通索引，基本的索引，没有任何限制

                        create index index_name on tablename（column1）

                    唯一索引，与普通索引类似，不同的就是索引列的值必须唯一，但允许空值，空值值null，组合索引的组合列的值必须唯一

                        create uniaue table_name on tablename（column1）

                    主键索引，一种特殊的唯一索引，不允许有空值，一般在建表的时候同时建立主键索引

                        create table table_name（id int not unll，username varchar（16） not null，primay key（id））；

                    组合索引，建立单列索引，代表有3个单列索引，查询时和上述的组合索引效率也会大不一样，远远低于组合索引。

                    全文索引，只用于mylsam表，对文本域进行索引，字段类型包括char，varchar，text，不过对于大容量的数据表，生成全文索引是一个非常消耗时间硬盘空间的做法

                        create fulltext index index_name on tablename（column）

                索引的数据结构，B-tree索引结构：

                如上图，是一颗b+树，这里只说一些重点，浅蓝色的块我们称之为一个磁盘块，可以看到每个磁盘块包含几个数据项（深蓝色所示）和指针（黄色所示），如磁盘块1包含数据项17和35，包含指针P1、P2、P3，P1表示小于17的磁盘块，P2表示在17和35之间的磁盘块，P3表示大于35的磁盘块。真实的数据存在于叶子节点即3、5、9、10、13、15、28、29、36、60、75、79、90、99。非叶子节点只不存储真实的数据，只存储指引搜索方向的数据项和指针，如17、35并不真实存在于数据表中。

b+树的查找过程

                如图所示，如果要查找数据项29，那么首先会把磁盘块1由磁盘加载到内存，此时发生一次IO，在内存中用二分查找确定29在17和35之间，锁定磁盘块1的P2指针，内存时间因为非常短（相比磁盘的IO）可以忽略不计，通过磁盘块1的P2指针的磁盘地址把磁盘块3由磁盘加载到内存，发生第二次IO，29在26和30之间，锁定磁盘块3的P2指针，通过指针加载磁盘块8到内存，发生第三次IO，同时内存中做二分查找找到29，结束查询，总计三次IO。真实的情况是，3层的b+树可以表示上百万的数据，如果上百万的数据查找只需要三次IO，性能提高将是巨大的，如果没有索引，每个数据项都要发生一次IO，那么总共需要百万次的IO，显然成本非常非常高。

               有什么要求：只有某些时候的like才需建立索引，因为在以通配符%和_开头做查询时，mysql不会使用索引。

                不要在列上进行运算

                将在每个行上进行运算，将导致索引失效而进行全表扫描

                选择索引列：

                    a，使用索引的主要有两种类型的列：在where子句中出现的列，在join子句中出现的列

                    b，考虑列综合那个值的分布，如果对字符串列进行索引，应该指定一个前缀长度，可节省大量索引空间，提升查询速度。

                    c，使用短索引，可节省大量索引空间，提升查询速度。

                    d，利用最左前缀

                    e，不要过度索引，值保持所需的索引，每个额外的索引都要占用额外的磁盘空间，并降低写操作的性能，在修改表的内容时，索引必须进行更新，有时可能需要重构，因此，索引越多，所花的时间越长。

                 mysql性能优化-慢查询分析，优化索引，优化配置

                    性能瓶颈定位

                        show命令

                        慢查询日志

                        explain分析查询

                        profiling分析查询

                    索引即查询优化

                    配置优化

                        最常见的两个瓶颈是cpu和i/o的瓶颈，cpu在饱和的时候一般发生子数据装入内存或从磁盘上读取数据时候，磁盘i/o瓶颈发生在装入数据远大雨内存容量的时候，如果应用分布在网络上，查询量相当大的时候那么瓶颈就会出现在网络上，可以用mpstat，iostat，sar，vmstat来查看系统的性能状态。

                    查询与索引优化分析

                            优化mysql时，需要分析数据库。有慢查询日志，explain分析查询，profiling分析以及show命令查询系统状态即系统变量，通过定位分析性能的瓶颈，才能更好的优化数据库系统的性能。

                    show命令查看mysql状态即变量，找到系统的瓶颈。

                        查看mysql服务器配置信息mysql > show variables;

                        查看mysql服务器运行的各自状态值mysq > show global status;

                        mysqladmin variables -u username -ptanhong 显示系统变量

                        mysqladmin extended-status -u username -ptanhong 显示状态信息

                     慢查询日志开启。

                        配置文件my.cnf中的{mysqld}一行下面加入3个配置参数，并重启mysql服务

                            show query log = 1    1：开启 0：关闭

                            show_query_log_file = /usr/local/mysql/data/slow-query.log    慢查询日志存放地点

                           long_query_time = 1    表示查询超过1秒才记录

                使用mysqldumpslow命令可以非常明确的得到各种我们需要的查询语句，对mysql查询语句的监控，分析，优化是mysql优化非常重要的，开启慢查询日志后，日志记录操作，在一定程度上会占用cpu资源影响mysql的性能，但是可以阶段性开启来定位性能瓶颈

                explain分析查询，可以模拟优化器执行sql查询语句，从而知道mysq是如何醋栗sql语句的，可以分析你的查询语句或是表结构的性能瓶颈

                    explain select * from test1.tb1 where stuname=‘admin‘\G;

                        id:1 

               select_type:SIMPLE

                     table:tb1            显示是哪个表

                partitions:NULL           

                      type:ALL            插叙使用了何种类型，重要字段。

             possible_keys:NULL           显示可能应用在表中的索引

                       key:NULL           实际使用的索引

                   key_len:NULL           使用的索引的长度

                       ref:NULL           显示索引哪一列被使用

                      rows:19986          mysql认为必须检索返回请求数据的行数

                  filtered:10.00          

                     Extra:Using where    关于mysql模拟优化器执行sql语句来看是没有使用索引查询的，而是全表扫描

                1 row in set,1 warning(0.00 sec)

                profiling分析查询

                    通过慢日志查询可以知道哪些sql语句执行效率低下，通过explain可以得知sql语句的具体执行情况，索引使用等，可以结合show命令查看执行状态，如果决定explain的信息不够详细，可以通过profiling命令得到更准确的sql执行消耗资源的信息。

                    profiling默认是关闭的，通过set profiling=1开启，执行需要测试的sql语句。

本文出自 “11853028” 博客，请务必保留此出处http://tanhong.blog.51cto.com/11853028/1905236

mysql索引