pptsmysql chapter 3

1、MySQL Administration2018/10/9RHEL Clustering & Storage Managementpage:1v 解析：解析树2018/10/9RHEL Clustering & Storage Managementpage:2v Differential2018/10/9RHEL Clustering & Storage Managementpage:3v 事务日志重做日志redo log撤消日志undo log2018/10/9RHEL Clustering & Storage Managementpage:4日志组2018/10/9RHEL Cluster

2、ing & Storage Managementpage:52018/10/9RHEL Clustering & Storage Managementpage:6v MySQL StorageEnginev Transactionv UserManagementv Backup and Recoveryv Caching and MySQL2018/10/9RHEL Clustering & Storage Managementpage:7MySQL Architacture andStorage Engines2018/10/9RHEL Clustering & Storage Manage

3、mentpage:8MySQL Arch2018/10/9RHEL Clustering & Storage Managementpage:9MySQLs Logical Architecture2018/10/9RHEL Clustering & Storage Managementpage:10MySQLs Logical Architecturev The topmost layer contains the services that arentunique to MySQLServices most network-based client/server tools or serve

4、rs need: connection handling, authentication, security, and so forthv The second layerMuch of MySQLs brains are here, including the code for query parsing, analysis, optimization, caching, and all the built-in functions (e.g., dates, times, math, and encryption)Any functionality provided across stor

5、age engines lives at thislevel: stored procedures, triggers, and views, for examplev The third layer contains the storage enginesThey are responsible for storing and retrieving all data stored“in” MySQLThe server communicates with them through the storage engine API2018/10/9RHEL Clustering & Storage

6、 Managementpage:11Connection Management andSecurityv Each client connection gets its own thread within the server processThe connections queries execute within that single thread,which in turn resides on one core or CPUThe server caches threads, so they dont need to be created anddestroyed for each

7、new connectionv When clients (applications) connect to the MySQL server, the server needs to authenticate themAuthentication is based on username, originating host, andpasswordX.509 certificates can also be used across an Secure Sockets Layer (SSL) connectionOnce a client has connected, the server v

8、erifies whether the client has privileges for each query it issues2018/10/9RHEL Clustering & Storage Managementpage:12Optimization and ExecutionMySQL parses queries to create an internal structure (the parse tree), and then applies a variety of optimizations, such asrewriting the querydetermining th

9、e order in which it will read tableschoosing which indexes to useYou can pass hints to the optimizer through special keywords in the query, affecting its decision-making processYou can also ask the server to explain various aspects of optimizationvvv2018/10/9RHEL Clustering & Storage Managementpage:

10、13v Beforeeven parsing the query, though, the server consults the query cache, which can store only SELECT statements, along with their result setsIf anyone issues a query thats identical to one already in the cache, the server doesnt need to parse, optimize, or execute the query at allit can simply

11、 pass back the stored result setv The optimizer does not really care what storage engine a particular table uses, but the storage engine does affect how the server optimizes query2018/10/9RHEL Clustering & Storage Managementpage:14Concurrency Controlv Anytime more than one query needs to change data

12、 at the same time, the problem of concurrency control arisesMySQL has to do this at two levels: the server level and the storage engine levelLike email box on Unix system2018/10/9RHEL Clustering & Storage Managementpage:15Read/Write Locksv The solution to this classic problem of concurrency control

13、is rather simple Systems that deal with concurrent read/write access typically implement a locking system that consists of two lock types These locks are usually known as shared locks and exclusive locks, or read locks and write locks2018/10/9RHEL Clustering & Storage Managementpage:16Lock Granulari

14、tyv One way to improve the concurrency of a shared resource is to be more selective about what you lockRather than locking the entire resource, lock only the part that contains the data you need to changev The problem is locks consume resourcesEvery lock operationgetting a lock, checking to see whet

15、her a lock is free, releasing a lock, and so onhas overheadv A locking strategy is a compromise between lock overhead and data safety, and that compromise affects performanceLock management is a very important decision in storage engine design; fixing the granularity at a certain level can give bett

16、er performance for certain uses, yet make that engine less suited for other purposes2018/10/9RHEL Clustering & Storage Managementpage:17Table locksv The most basic locking strategy available in MySQL, and the one with the lowest overhead, is table locksv Table locks have variations for good performa

17、nce in specific situationsFor example, READ LOCAL table locks allow some types of concurrent write operationsWrite locks also have a higher priority than read locks2018/10/9RHEL Clustering & Storage Managementpage:18Row locksv The locking style that offers the greatest concurrency (and carries the g

18、reatest overhead) is the use of row locksRow-level locking, as this strategy is commonly known, is available in the InnoDB and Falcon storage engines, among othersv Row locks are implemented in the storage engine, not the server2018/10/9RHEL Clustering & Storage Managementpage:19Transactionsv A tran

19、saction is a group of SQL queries that are treated atomically, as a single unit of workv Transactions arent enough unless the systempasses the ACID testAtomicityA transaction must function as a single indivisible unit of work so that the entire transaction is either applied or rolled backConsistency

20、The database should always move from one consistent state to the nextIsolationThe results of a transaction are usually invisible to other transactions until the transaction is complete Durability2018/10/9RHEL Clustering & Storage Managementpage:20Once committed, a transactions changes are permanentI

21、solation Levelsv The SQL standarddefinesfour isolation levels, with specific rules for which changes are and arent visible inside and outside a transactionREAD UNCOMMITTEDTransactions can view the results of uncommitted transactionsREAD COMMITTEDA transaction will see only those changes made by tran

22、sactions that were already committed when it began, and its changes wont be visible to others until it has committedREPEATABLE READIt guarantees that any rows a transaction reads will “look the same” in subsequent reads within the same transaction, but in theory it still allows another tricky proble

23、m: phantom readsSERIALIZABLESolves the phantom read problem by forcing transactions to beordered so that they cant possibly conflict2018/10/9RHEL Clustering & Storage Managementpage:21部分提交提交活动失败终止2018/10/9RHEL Clustering & Storage Managementpage:22Transaction life cycle2018/10/9RHEL Clustering & Sto

24、rage Managementpage:23Savepointsuser-defined points that can be used to mark substages within a transactionSavepoints within a transaction are set with the SAVEPOINT command, which accepts a user-defined identifierThe ROLLBACK TO SAVEPOINT command can then be used to roll an in-progress transaction

25、back to the named savepoint, reversing all changes made after the savepointvvv2018/10/9RHEL Clustering & Storage Managementpage:24READ UNCOMMITTED2018/10/9RHEL Clustering & Storage Managementpage:25READ COMMITTED2018/10/9RHEL Clustering & Storage Managementpage:26REPEATABLE READ2018/10/9RHEL Cluster

26、ing & Storage Managementpage:27SERIALIZABLE2018/10/9RHEL Clustering & Storage Managementpage:28ANSI SQL isolation levels2018/10/9RHEL Clustering & Storage Managementpage:29Modifying the TransactionIsolation Level2018/10/9RHEL Clustering & Storage Managementpage:30Pseudo-Transactionsv MySQL still ena

27、bles users to implement a primitive form of transactions through the use of table locksTable locksPage locksA lock applied to a portion of a table known as a pageBDB (Berkeley DB) tables use page-level locking on 8-KB pagesRow locks2018/10/9RHEL Clustering & Storage Managementpage:31Locking models a

28、ndconcurrency in MySQL2018/10/9RHEL Clustering & Storage Managementpage:32DeadlocksA deadlock is when two or more transactions are mutually holding and requesting locks on the same resources, creating a cycle of dependenciesDeadlocks occur when transactions try to lock resources in a different order

29、To combat this problem, database systems implement various forms of deadlock detection and timeoutsLock behavior and order are storage engine-vvvspecific, so some storage engines might deadlock on a certain sequence of statements201e8/10v/9en though otRhHEeL Crlussterinwg & SotonragetManagementpage:

30、33Transaction Loggingv Transaction logging helps make transactions more efficientInstead of updating the tables on disk each time a change occurs, the storage engine can change its inmemory copy of the dataThe storage engine can then write a record of the change to the transaction log, which is on d

31、isk and therefore durableThis is also a relatively fast operation, because appending log events involves sequential I/O in one small area of the disk instead of random I/O in many placesThen, at some later time, a process can update the table on disk2018/10/9RHEL Clustering & Storage Managementpage:

32、34Transactions in MySQLv MySQL AB provides three transactional storage engines: InnoDB, NDB Cluster, and Falcon, xtraDBv Several third-party engines are also available; the best-known engines right now are solidDB and PBXTAUTOCOMMITMixing storage engines in transactionsImplicit and explicit locking2

33、018/10/9RHEL Clustering & Storage Managementpage:35tables, which essentially alw AUTOCOMMIT modeAUTOCOMMITv MySQL operates in AUTOCOMMIT mode by defaultv You can enable or disable AUTOCOMMIT for the current connection by setting a variableChanging the value of AUTOCOMMIT has no effect on nontransact

34、ional tables, such as MyISAM or Memoryays operate in2018/10/9RHEL Clustering & Storage Managementpage:36Mixing storage engines intransactionsIf you mix transactional and nontransactional tables (for instance, InnoDB and MyISAM tables) in a transaction, the transaction will work properly if all goes

35、wellHowever, if a rollback is required, the changes to the nontransactional table cant be undoneMySQL will not usually warn you or raise errors if you do transactional operations on a nontransactional tablevvv2018/10/9RHEL Clustering & Storage Managementpage:37Implicit and explicit lockingv InnoDB u

36、ses a two-phase locking protocol. It can acquire locks at any time during a transaction, but it does not release them until a COMMIT or ROLLBACKv It releases all the locks at the same time.v The locking mechanisms described earlier are all implicitv InnoDB handles locks automatically, according to y

37、our isolation level2018/10/9RHEL Clustering & Storage Managementpage:38v However, InnoDB also supports explicit locking, which the SQL standard does not mention at all:SELECT . LOCK IN SHARE MODESELECT . FOR UPDATEv MySQL also supports the LOCK TABLES and UNLOCK TABLES commandsImplemented in the ser

38、ver, not in the storage engines.These have their uses, but they are not a substitute for transactionsIf you need transactions, use a transactional storage engine2018/10/9RHEL Clustering & Storage Managementpage:39Multiversion ConcurrencyControlMost of MySQLs transactional storage engines, use row-le

39、vel locking in conjunction with a technique for increasing concurrency known as multiversion concurrency control (MVCC)It avoids the need for locking at all in many cases and can have much lower overheadMVCC works by keeping a snapshot of the data as it existed at some point in timeThis means transa

40、ctions can see a consistent view of the data, no matter how long they runIt also means different transactions can see differentvv data in the same tables at the same time2018/10/9RHEL Clustering & Storage Managementpage:40Understanding Storage Enginesv A storage engine is a subsystem that manages ta

41、blesMost database management systems have one subsystem to manage tables; MySQL Server can use different subsystems. Because a storage engine is applied at the table level, it is sometimes called table typev The MySQL pluggable storage engine is an architectural design that separates the code that m

42、anages the tables from the database server core code.This core code manages the components such as the query cache, the optimizer, and the connection2018/10/9handlerRHEL Clustering & Storage Managementpage:41MySQL Server Storage EngineOverview2018/10/9RHEL Clustering & Storage Managementpage:422018/

43、10/9RHEL Clustering & Storage Managementpage:43MyISAM storage enginev MyISAM is the default storage engine in mysqld, and is the storage engine used by the system tablesv MyISAM provides a good compromise between performance and useful features, such asfull-text indexing, compression, and spatial (G

44、IS) functionsv MyISAM doesnt support transactions or row- level locks2018/10/9RHEL Clustering & Storage Managementpage:44Feature summary:vNon-transactionalNo foreign key supportFULLTEXT indexes for text matching No data cacheIndex caches can be specified by nameImplements both HASH and BTREE indexes

45、 (BTREE by default;)Table-level lockingVery fast read activity, suitable for data warehouses Compressed data (with myisampack)Online backup with mysqlhotcopyMaximum of 64 indexes per table2018/10/9RHEL Clustering & Storage Managementpage:45MyISAM ConfigurationOptions2018/10/9RHEL Clustering & Storag

46、e Managementpage:46MyISAM utilitiesv Three utility programs are designed for working with MyISAM tables:myisamchkUsed to analyze, optimize, and repair MyISAM tablesmyisampackUsed to create compressed, read-only MyISAM tables2018/10/9RHEL Clustering & Storage Managementpage:47myisamchkv The myisamchk

47、 program has four different modes of operationv It can be used to analyze, optimize, check, and repair MyISAM tablesv The default mode of operation is the check mode where it checks for possible corruption2018/10/9RHEL Clustering & Storage Managementpage:48myisampackTo create compressed read-only ve

48、rsions of tables can provide for a goodvperformance increase for data that is no longer being updated but still needs to be accessedA compressed MyISAM table is read-only, and cannot have new rows inserted like an Archive table canvvmyisampacktable_name.MYIOnce the compression is done you have to ru

49、n the myisamchk program to rebuild indexesvmyisamchk-rq-sort-index analyzetable_name_MYI2018/10/9RHEL Clustering & Storage Managementpage:49Merge storage engineThe Merge storage engine is actually a sort of wrapper tablethat wraps around MyISAM tables with the same schemasAll the underlying tables c

50、an be queried at once by querying the Merge tableMerge tables will use the same buffers and configuration options as for the underlying MyISAM tablesWhen you create a Merge table, two files are always created in the file systemThere is one file containing the table format that has a filename of the

51、table followed by a suffix of .frmThe second file also has a filename of the table but ends with a suffix of .MRGvvvvYou can also put the individual MyISAM tables onv different hard drives to help increase performance2018/10/9RHEL Clustering & Storage Managementpage:50InnoDB storage engineInnoDB was

52、 designed for transaction processingspecifically, processing of many short-lived transactions that usually complete rather than being rolled backInnoDB stores its data in a series of one or more data files that are collectively known as a tablespaceA tablespace is essentially a black box that InnoDB

53、 manages all by itselfInnoDBs index structures are very differentvvvvfrom those of most other MySQL storage engines. As a result, it provides very fast201p8/1r0/9imary key looRHkELuClupstesring & Storage Managementpage:51v Feature summary:Transactional support provided by MVCC (Multi Version Concurr

54、ency Control)Row-level lockingForeign key supportIndexing using clustered B-tree indexesConfigurable buffer caching of both indexes and dataOnline non-blocking backup through separate commercial backup program2018/10/9RHEL Clustering & Storage Managementpage:52Tablespace configurationvariablesv A tablespace is a logical group of one or more data files in a databasev The full path to each shared tablespace is formed by adding innodb_data_home_dir to each path specified in the innodb_data_file_pathv By default if innodb_d