mysqllong类型_千万级MySQL数据库建⽴索引，提⾼性能的
秘诀
差⼀点
我们就擦肩⽽过了
有趣
有⽤
有态度
实践中如何优化MySQL
实践中，MySQL的优化主要涉及SQL语句及索引的优化、数据表结构的优化、系统配置的优化和硬件的优化四个⽅⾯，如下图所⽰：
SQL语句及索引的优化
SQL语句的优化
SQL语句的优化主要包括三个问题，即如何发现有问题的SQL、如何分析SQL的执⾏计划以及如何优化SQL，下⾯将逐⼀解释。
1. 怎么发现有问题的SQL?(通过MySQL慢查询⽇志对有效率问题的SQL进⾏监控)
MySQL的慢查询⽇志是MySQL提供的⼀种⽇志记录，它⽤来记录在MySQL中响应时间超过阀值的语
句，具体指运⾏时间超过
long_query_time值的SQL，则会被记录到慢查询⽇志中。
long_query_time的默认值为10，意思是运⾏10s以上的语句。慢查询⽇志的相关参数如下所⽰：
通过MySQL的慢查询⽇志，我们可以查询出执⾏的次数多占⽤的时间长的SQL、可以通过pt_query_disgest(⼀种mysql慢⽇志分析⼯具)分析Rows examine(MySQL执⾏器需要检查的⾏数)项去出IO⼤的SQL以及发现未命中索引的SQL，对于这些SQL，都是我们优化的对象。
通过explain查询和分析SQL的执⾏计划
使⽤ EXPLAIN 关键字可以知道MySQL是如何处理你的SQL语句的，以便分析查询语句或是表结构的性能瓶颈。通过explain命令可以得到表的读取顺序、数据读取操作的操作类型、哪些索引可以使⽤、哪些索引被实际使⽤、表之间的引⽤以及每张表有多少⾏被优化器查询等问题。当扩展列extra出现Using filesort和Using temporay，则往往表⽰SQL需要优化了。
优化SQL语句
优化insert语句：⼀次插⼊多值；
应尽量避免在 where ⼦句中使⽤!=或<>操作符，否则将引擎放弃使⽤索引⽽进⾏全表扫描；
应尽量避免在 where ⼦句中对字段进⾏null值判断，否则将导致引擎放弃使⽤索引⽽进⾏全表扫描；
优化嵌套查询：⼦查询可以被更有效率的连接(Join)替代；
很多时候⽤ exists 代替 in 是⼀个好的选择。
索引优化mysql语句分类
建议在经常作查询选择的字段、经常作表连接的字段以及经常出现在order by、group by、distinct 后⾯的字段中建⽴索引。但必须注意以下⼏种可能会引起索引失效的情形：
以“%(表⽰任意0个或多个字符)”开头的LIKE语句，模糊匹配；
OR语句前后没有同时使⽤索引；
数据类型出现隐式转化(如varchar不加单引号的话可能会⾃动转换为int型)；
对于多列索引，必须满⾜最左匹配原则(eg，多列索引col1、col2和col3，则 索引⽣效的情形包括col1或col1，col2或
col1，col2，col3)。
数据库表结构的优化
数据库表结构的优化包括选择合适数据类型、表的范式的优化、表的垂直拆分和表的⽔平拆分等⼿段。
选择合适数据类型
使⽤较⼩的数据类型解决问题；
使⽤简单的数据类型(mysql处理int要⽐varchar容易)；
健身图片特效
尽可能的使⽤not null 定义字段；
尽量避免使⽤text类型，⾮⽤不可时最好考虑分表；
表的范式的优化
⼀般情况下，表的设计应该遵循三⼤范式。
表的垂直拆分
把含有多个列的表拆分成多个表，解决表宽度问题，具体包括以下⼏种拆分⼿段：
把不常⽤的字段单独放在同⼀个表中；
把⼤字段独⽴放⼊⼀个表中；
把经常使⽤的字段放在⼀起；
这样做的好处是⾮常明显的，具体包括：拆分后业务清晰，拆分规则明确、系统之间整合或扩展容易、数据维护简单。
表的⽔平拆分
表的⽔平拆分⽤于解决数据表中数据过⼤的问题，⽔平拆分每⼀个表的结构都是完全⼀致的。⼀般地，将数据平分到N张表中的常⽤⽅法包括以下两种：
对ID进⾏hash运算，如果要拆分成5个表，mod(id,5)取出0~4个值；
针对不同的hashID将数据存⼊不同的表中；
表的⽔平拆分会带来⼀些问题和挑战，包括跨分区表的数据查询、统计及后台报表的操作等问题，但也带来了⼀些切实的好处：
表分割后可以降低在查询时需要读的数据和索引的页数，同时也降低了索引的层数，提⾼查询速度；
表中的数据本来就有独⽴性，例如表中分别记录各个地区的数据或不同时期的数据，特别是有些数据常⽤，⽽另外⼀些数据不常⽤。
需要把数据存放到多个数据库中，提⾼系统的总体可⽤性(分库，鸡蛋不能放在同⼀个篮⼦⾥)。
系统配置的优化
操作系统配置的优化：增加TCP⽀持的队列数
计算机系毕业设计成品
mysql配置⽂件优化：Innodb缓存池设置(innodb_buffer_pool_size，推荐总内存的75%)和缓存池的个数
htmlradio是什么意思(innodb_buffer_pool_instances)
硬件的优化
CPU：核⼼数多并且主频⾼的 内存：增⼤内存 磁盘配置和选择：磁盘性能
MySQL中的悲观锁与乐观锁的实现
悲观锁与乐观锁是两种常见的资源并发锁设计思路，也是并发编程中⼀个⾮常基础的概念。
悲观锁
悲观锁的特点是先获取锁，再进⾏业务操作，即“悲观”的认为所有的操作均会导致并发安全问题，因此要先确保获取锁成功再进⾏业务操作。
通常来讲，在数据库上的悲观锁需要数据库本⾝提供⽀持，即通过常⽤的select … for update操作来实现悲观锁。当数据库执⾏select …for update时会获取被select中的数据⾏的⾏锁，因此其他并发执⾏
的select … for update如果试图选中同⼀⾏则会发⽣排斥(需要等待⾏锁被释放)，因此达到锁的效果。select for update获取的⾏锁会在当前事务结束时⾃动释放，因此必须在事务中使⽤。 
这⾥需要特别注意的是，不同的数据库对select… for update的实现和⽀持都是有所区别的，例如oracle⽀持select for update no wait，表⽰如果拿不到锁⽴刻报错，⽽不是等待，mysql就没有no wait这个选项。
另外，mysql还有个问题是: select… for update语句执⾏中所有扫描过的⾏都会被锁上，这⼀点很容易造成问题。因此，如果在mysql中⽤悲观锁务必要确定使⽤了索引，⽽不是全表扫描。
乐观锁
乐观锁的特点先进⾏业务操作，只在最后实际更新数据时进⾏检查数据是否被更新过，若未被更新过，则更新成功；否则，失败重试。乐观锁在数据库上的实现完全是逻辑的，不需要数据库提供特殊的⽀持。⼀般的做法是在需要锁的数据上增加⼀个版本号或者时间戳，然后按照如下⽅式实现：
SELECT data AS old_data, version AS old_version FROM …;
//根据获取的数据进⾏业务操作，得到new_data和new_version
UPDATE SET data = new_data, version = new_version WHERE version = old_version
if (updated row > 0) {
// 乐观锁获取成功，操作完成
} else {
// 乐观锁获取失败，回滚并重试
}
乐观锁是否在事务中其实都是⽆所谓的，其底层机制是这样：在数据库内部update同⼀⾏的时候是不允许并发的，即数据库每次执⾏⼀条update语句时会获取被update⾏的写锁，直到这⼀⾏被成功更新后才释放。
因此在业务操作进⾏前获取需要锁的数据的当前版本号，然后实际更新数据时再次对⽐版本号确认与之前获取的相同，并更新版本号，即可确认这其间没有发⽣并发的修改。如果更新失败，即可认为⽼版本的数据已经被并发修改掉⽽不存在了，此时认为获取锁失败，需要回滚整个业务操作并可根据需要重试整个过程。
悲观锁与乐观锁的应⽤场景
⼀般情况下，读多写少更适合⽤乐观锁，读少写多更适合⽤悲观锁。乐观锁在不发⽣取锁失败的情况下开销⽐悲观锁⼩，但是⼀旦发⽣失败回销则⽐较⼤，因此适合⽤在取锁失败概率⽐较⼩的场景，可以提升系统并发性能。
MySQL存储引擎中的MyISAM和InnoDB区别详解
在MySQL 5.5之前，MyISAM是mysql的默认数据库引擎，其由早期的ISAM(Indexed Sequential Access Method：有索引的顺序访问⽅法)所改良。虽然MyISAM性能极佳，但却有⼀个显著的缺点：不⽀持事务处理。不过，MySQL也导⼊了另⼀种数据库引擎InnoDB，以强化参考完整性与并发违规处理机制，后来就逐渐取代MyISAM。
InnoDB是MySQL的数据库引擎之⼀，其由Innobase oy公司所开发，2006年五⽉由甲⾻⽂公司并购。与传统的ISAM、MyISAM相
⽐，InnoDB的最⼤特⾊就是⽀持ACID兼容的事务功能，类似于PostgreSQL。⽬前InnoDB采⽤双轨制授权，⼀是GPL授权，另⼀是专有软件授权。具体地，MyISAM与InnoDB作为MySQL的两⼤存储引擎的差异主要包括：
存储结构：每个MyISAM在磁盘上存储成三个⽂件：第⼀个⽂件的名字以表的名字开始，扩展名指出
⽂件类型。.frm⽂件存储表定义，数据⽂件的扩展名为.MYD (MYData)，索引⽂件的扩展名是.MYI (MYIndex)。InnoDB所有的表都保存在同⼀个数据⽂件中(也可能是多个⽂件，或者是独⽴的表空间⽂件)，InnoDB表的⼤⼩只受限于操作系统⽂件的⼤⼩，⼀般为2GB。
存储空间：MyISAM可被压缩，占据的存储空间较⼩，⽀持静态表、动态表、压缩表三种不同的存储格式。InnoDB需要更多的内存和存储，它会在主内存中建⽴其专⽤的缓冲池⽤于⾼速缓冲数据和索引。
可移植性、备份及恢复：MyISAM的数据是以⽂件的形式存储，所以在跨平台的数据转移中会很⽅便，同时在备份和恢复时也可单独针对某个表进⾏操作。InnoDB免费的⽅案可以是拷贝数据⽂件、备份 binlog，或者⽤ mysqldump，在数据量达到⼏⼗G的时候就相对痛苦了。
事务⽀持：MyISAM强调的是性能，每次查询具有原⼦性，其执⾏数度⽐InnoDB类型更快，但是不提供事务⽀持。InnoDB提供事务、外键等⾼级数据库功能，具有事务提交、回滚和崩溃修复能⼒。
AUTO_INCREMENT：在MyISAM中，可以和其他字段⼀起建⽴联合索引。引擎的⾃动增长列必须是索引，如果是组合索引，⾃动增长可以不是第⼀列，它可以根据前⾯⼏列进⾏排序后递增。InnoDB中必须包含只有该字段的索引，并且引擎的⾃动增长列必须是索引，如果是组合索引也必须是组合索引的第⼀列。
表锁差异：MyISAM只⽀持表级锁，⽤户在操作MyISAM表时，select、update、delete和insert语句都会给表⾃动加锁，如果加锁以后的表满⾜insert并发的情况下，可以在表的尾部插⼊新的数据。InnoDB⽀持事务和⾏级锁。⾏锁⼤幅度提⾼了多⽤户并发操作的新能，但是InnoDB的⾏锁，只是在WHERE的主键是有效的，⾮主键的WHERE都会锁全表的。
全⽂索引：MyISAM⽀持 FULLTEXT类型的全⽂索引；InnoDB不⽀持FULLTEXT类型的全⽂索引，但是innodb可以使⽤sphinx插件⽀持全⽂索引，并且效果更好。
表主键：MyISAM允许没有任何索引和主键的表存在，索引都是保存⾏的地址。对于InnoDB，如果没有设定主键或者⾮空唯⼀索引，就会⾃动⽣成⼀个6字节的主键(⽤户不可见)，数据是主索引的⼀部分，附加索引保存的是主索引的值。
表的具体⾏数：MyISAM保存表的总⾏数，select count() from table；会直接取出出该值；⽽InnoDB没有保存表的总⾏数，如果使⽤select count() from table；就会遍历整个表，消耗相当⼤，但是在加了wehre条件后，myisam和innodb处理的⽅式都⼀样。
CURD操作：在MyISAM中，如果执⾏⼤量的SELECT，MyISAM是更好的选择。对于InnoDB，如果你的数据执⾏⼤量的INSERT或UPDATE，出于性能⽅⾯的考虑，应该使⽤InnoDB表。DELETE从性能上InnoDB更优，但DELETE FROM table时，InnoDB不会重新建⽴表，⽽是⼀⾏⼀⾏的删除，在inno
db上如果要清空保存有⼤量数据的表，最好使⽤truncate table这个命令。
外键：MyISAM不⽀持外键，⽽InnoDB⽀持外键。
通过上述的分析，基本上可以考虑使⽤InnoDB来替代MyISAM引擎了，原因是InnoDB⾃⾝很多良好的特点，⽐如事务⽀持、存储过程、视图、⾏级锁、外键等等。尤其在并发很多的情况下，相信InnoDB的表现肯定要⽐MyISAM强很多。
另外，必须需要注意的是，任何⼀种表都不是万能的，合适的才是最好的，才能最⼤的发挥MySQL的性能优势。如果是不复杂的、⾮关键的Web应⽤，还是可以继续考虑MyISAM的，这个具体情况具体考虑。
MyISAM：不⽀持事务，不⽀持外键，表锁；插⼊数据时锁定整个表，查⾏数时⽆需整表扫描。主索引数据⽂件和索引⽂件分离；与主索引⽆区别；
InnoDB：⽀持事务，外键，⾏锁，查表总⾏数时，全表扫描；主索引的数据⽂件本⾝就是索引⽂件；辅助索引记录主键的值；MySQL锁类型 
subjective怎么记根据锁的类型分，可以分为共享锁，排他锁，意向共享锁和意向排他锁。
根据锁的粒度分，⼜可以分为⾏锁，表锁。
对于mysql⽽⾔，事务机制更多是靠底层的存储引擎来实现，因此，mysql层⾯只有表锁，⽽⽀持事务的innodb存 储引擎则实现了⾏锁(记录锁(在⾏相应的索引记录上的锁))，gap锁(是在索引记录间歇上的锁)，next-key锁(是记录锁和在此索引记录之前的gap上的锁的结合)。
Mysql的记录锁实质是索引记录的锁，因为innodb是索引组织表；gap锁是索引记录间隙的锁，这种锁只在RR隔离级别下有效；next-key 锁是记录锁加上记录之前gap锁的组合。mysql通过gap锁和next-key锁实现RR隔离级别。
说明：对于更新操作(读不上锁)，只有⾛索引才可能上⾏锁；否则会对聚簇索引的每⼀⾏上写锁，实际等同于对表上写锁。 
若多个物理记录对应同⼀个索引，若同时访问，也会出现锁冲突；
指针和函数的关系当表有多个索引时，不同事务可以⽤不同的索引锁住不同的⾏，另外innodb会同时⽤⾏锁对数据记录(聚簇索引)加锁。
MVCC(多版本并发控制)并发控制机制下，任何操作都不会阻塞读操作，读操作也不会阻塞任何操作，只因为读不上锁。 
共享锁：由读表操作加上的锁，加锁后其他⽤户只能获取该表或⾏的共享锁，不能获取排它锁，也就
是说只能读不能写
排它锁：由写表操作加上的锁，加锁后其他⽤户不能获取该表或⾏的任何锁，典型是mysql事务中的更新操作。
意向共享锁(IS)：事务打算给数据⾏加⾏共享锁，事务在给⼀个数据⾏加共享锁前必须先取得该表的IS锁。
意向排他锁(IX)：事务打算给数据⾏加⾏排他锁，事务在给⼀个数据⾏加排他锁前必须先取得该表的IX锁。
数据库死锁概念
多数情况下，可以认为如果⼀个资源被锁定，它总会在以后某个时间被释放。⽽死锁发⽣在当多个进程访问同⼀数据库时，其中每个进程拥有的锁都是其他进程所需的，由此造成每个进程都⽆法继续下去。
简单的说，进程A等待进程B释放他的资源，B⼜等待A释放他的资源，这样就互相等待就形成死锁。
虽然进程在运⾏过程中，可能发⽣死锁，但死锁的发⽣也必须具备⼀定的条件，死锁的发⽣必须具备以下四个必要条件：
1)互斥条件：指进程对所分配到的资源进⾏排它性使⽤，即在⼀段时间内某资源只由⼀个进程占⽤。如果此时还有其它进程请求资源，则请求者只能等待，直⾄占有资源的进程⽤毕释放。
2)请求和保持条件：指进程已经保持⾄少⼀个资源，但⼜提出了新的资源请求，⽽该资源已被其它进程占有，此时请求进程阻塞，但⼜对⾃⼰已获得的其它资源保持不放。
3)不剥夺条件：指进程已获得的资源，在未使⽤完之前，不能被剥夺，只能在使⽤完时由⾃⼰释放。
4)环路等待条件：指在发⽣死锁时，必然存在⼀个进程——资源的环形链，即进程集合{P0，P1，P2，···，Pn}中的P0正在等待⼀个P1占⽤的资源；P1正在等待P2占⽤的资源，……，Pn正在等待已被P0占⽤的资源。 

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。